On Monday 19 March 2007 18:15:15 Oleg Broytmann wrote:
> On Mon, Mar 19, 2007 at 06:13:16PM +0100, Ferdinand Gassauer wrote:
> > I am far away from being able to modify complex python scripts.
> > So what I could do is to send you a documentation for the workaround, if
> > you want to add this to http://sqlobject.org/SQLObject.html
>
>    Please do.
>
> Oleg.

see attachment

-- 
cu
ferdinand
SQLObject requirements:

All tables that you want to turn into a class need to have an integer primary 
key.

workaround for primary keys made up of multiple columns:

SQLObject needs a unique column called "id".

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 statment 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 manipulationg 
underlying tables of views.
http://www.postgresql.org/docs/8.1/interactive/rules-update.html

For a 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.

Caveat: 
* 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.
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Reply via email to