Author: phd
Date: 2007-03-27 08:59:52 -0600 (Tue, 27 Mar 2007)
New Revision: 2454
Modified:
SQLObject/docs/SQLObject.txt
Log:
Added a new section "Workaround for primary keys made up of multiple columns".
Modified: SQLObject/docs/SQLObject.txt
===================================================================
--- SQLObject/docs/SQLObject.txt 2007-03-27 14:17:58 UTC (rev 2453)
+++ SQLObject/docs/SQLObject.txt 2007-03-27 14:59:52 UTC (rev 2454)
@@ -1514,6 +1514,59 @@
(case-insensitive). This restriction will probably be removed in the
next release.
+Workaround for primary keys made up of multiple columns
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+If the database table/view has ONE NUMERIC Primary Key then sqlmeta - idName
+should be used to map the table column name to SQLObject id column.
+
+If the Primary Key consists only of number columns it is possible to create a
+virtual column "id" this way:
+
+Example for Postgresql:
+
+ select '1'||lpad(PK1,max_length_of_PK1,'0')||lpad(PK2,max_length_of_PK2,'0')||...||lpad(PKn,max_length_of_PKn,'0') as "id",
+ column_PK1, column_PK2, .., column_PKn, column... from table;
+
+Note:
+
+* The arbitrary '1' at the beginning of the string to allow for leading zeros
+ of the first PK.
+
+* The application designer has to determine the maximum length of each Primary
+ Key.
+
+This statement can be saved as a view or the column can be added to the
+database table, where it can be kept up to date with a database trigger.
+
+Obviously the "view" method does generally not allow insert, updates or
+deletes. For Postgresql you may want to consult the chapter "RULES" for
+manipulating underlying tables.
+
+For an alphanumeric Primary Key column a similar method is possible:
+
+Every character of the lpaded PK has to be transfered using ascii(character)
+which returns a 3digit number which can be concatenated as shown above.
+
+Caveats:
+
+* this way the "id" may become a very large integer number which may cause
+ troubles elsewhere.
+
+* no performance loss takes place if the where clauses specifies the PK
+ columns.
+
+Example: CD-Album
+* Album: PK=ean
+* Tracks: PK=ean,disc_nr,track_nr
+
+The database view to show the tracks starts:
+
+ SELECT ean||lpad("disc_nr",2,'0')||lpad("track_nr",2,'0') as id, ...
+ Note: no leading '1' and no padding necessary for ean numbers
+
+Tracks.select(Tracks.q.ean==id) ... where id is the ean of the Album.
+
Changing the Naming Style
-------------------------
|
