[SQL-CVS] r2454 - SQLObject/docs

[<sub...@co...>]

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
 -------------------------