Re: composite primary keys
Hi Alex, On Wed, Mar 16, 2011 at 4:44 PM, Alexander Burger wrote: > > In my mind, I distinguish between "objects" and "indexes" in the DB. The > objects are the payload data, and also have direct links between each > other (the +Link and +Joint relations). uhm. this helps a lot. thank you. Henrik, thanks to you too. those articles helped. /e -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
Hi Edwin, > > You mean for database indexes? > > not just for indexes but for a composite primary key. i'm not sure tho > if my head is right on this. Yes. I'm using "key" and "index" for the same thing, I think. In my mind, I distinguish between "objects" and "indexes" in the DB. The objects are the payload data, and also have direct links between each other (the +Link and +Joint relations). To find a given object by some key, an "index" is used. These indexes may be uniqe (the +Key relation) or non-unique (+Ref, +Idx etc.). With +Aux, you can combine several indexes into a single one. Henrik explained that in more detail. > whack me with a clue stick anytime. :) Surely no desire for that ;-) Cheers, - Alex -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
Hi Edwin,
> checking the docs out, +Aux is a "Prefix class maintaining auxiliary
> keys for +relations, in addition to +Ref or +Idx indexes. Expects a
> list of auxiliary attributes of the same object, and combines all keys
> in that order into a single index key."
>
> which means that there must exist a primary key for the +relation in question.
>
> however, i'm looking for a +Key with multiple columns.
Yes, that's what +Aux is doing.
If you look at the example below the above cited text:
(rel nr (+Ref +Number))# Normal, non-unique index
(rel nm (+Aux +Ref +String) (nr txt)) # Combined name/number/text index
(rel txt (+Aux +Sn +Idx +String) (nr)) # Text/number plus tolerant text index
Then you see that the two combined indexes for 'nm' and 'txt' consist of
several keys which are by themselves not unique.
Let's use this example for a stand-alone program:
(pool "aux.db")
(push '*Bye '(call 'rm "-f" "aux.db"))
(class +A +Entity)
(rel nr (+Ref +Number))
(rel nm (+Aux +Ref +String) (nr txt))
(rel txt (+Aux +Sn +Idx +String) (nr))
(mapc
'((N S B)
(new T '(+A) 'nr N 'nm S 'txt B) )
'(3 6 2 4 1 5)
'("a" "a" "a" "b" "b" "b")
'("Abcd" "Efgh" "Ijkl" "Mnop" "Qrst" "Uvwx") )
(commit)
If you load this, and then look at the indexes:
: (scan (tree 'nm '+A))
("a" 2 "Ijkl" . {9}) {9}
("a" 3 "Abcd" . {2}) {2}
("a" 6 "Efgh" . {8}) {8}
("b" 1 "Qrst" . {;}) {;}
("b" 4 "Mnop" . {:}) {:}
("b" 5 "Uvwx" . {A}) {A}
: (scan (tree 'txt '+A))
("AFST" {2} . T) {2}
("Abcd" 3 . {2}) {2}
("EFS" {8} . T) {8}
("Efgh" 6 . {8}) {8}
("ISL" {9} . T) {9}
("Ijkl" 2 . {9}) {9}
("MNF" {:} . T) {:}
("Mnop" 4 . {:}) {:}
("QRST" {;} . T) {;}
("Qrst" 1 . {;}) {;}
("UFS" {A} . T) {A}
("Uvwx" 5 . {A}) {A}
("bcd" {2}) {2}
("fgh" {8}) {8}
("jkl" {9}) {9}
("nop" {:}) {:}
("rst" {;}) {;}
("vwx" {A}) {A}
You see that the actual indexes are lists of values. So while "a" is not
a unique name, you can access a specific object with
: (aux 'nm '+A "a" 6)
-> {8}
: (aux 'nm '+A "a" 5 "Uvwx")
-> NIL
: (aux 'nm '+A "b" 5 "Uvwx")
-> {A}
Does this help?
Cheers,
- Alex
--
UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
Hi Alex, On Wed, Mar 16, 2011 at 3:54 PM, Alexander Burger wrote: > Hi Edwin, > >> anyone encountered creating a composite primary key for Pilog? > > You mean for database indexes? not just for indexes but for a composite primary key. i'm not sure tho if my head is right on this. > > If so, there is the '+Aux' prefix class which does this, and there is > also a function 'aux' for direct access. The Pilog rules know about > '+Aux' and handle it accordingly. thank you. hope you've read my response to Henrik's. whack me with a clue stick anytime. :) > > Cheers, > - Alex > -- > UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe > -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
A litte bit of extra documentation in addition to the reference: http://www.prodevtips.com/2008/10/09/pilog-solve-and-the-aux-relation/ On Wed, Mar 16, 2011 at 2:54 PM, Alexander Burger wrote: > Hi Edwin, > >> anyone encountered creating a composite primary key for Pilog? > > You mean for database indexes? > > If so, there is the '+Aux' prefix class which does this, and there is > also a function 'aux' for direct access. The Pilog rules know about > '+Aux' and handle it accordingly. > > Cheers, > - Alex > -- > UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe > -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
Hi Henrik, On Wed, Mar 16, 2011 at 3:34 PM, Henrik Sarvell wrote: > Might the +Aux relation be what you're looking for? i'm approaching this from with a SQL background so forgive me for my naivete. checking the docs out, +Aux is a "Prefix class maintaining auxiliary keys for +relations, in addition to +Ref or +Idx indexes. Expects a list of auxiliary attributes of the same object, and combines all keys in that order into a single index key." which means that there must exist a primary key for the +relation in question. however, i'm looking for a +Key with multiple columns. i'm not sure if this is applicable at all, please note. so if +Aux is indeed the right direction, i'll give it a swing. thank you. /e > > > On Wed, Mar 16, 2011 at 2:19 PM, Edwin Eyan Moragas wrote: >> Hi List, >> >> anyone encountered creating a composite primary key for Pilog? >> >> or it is applicable in the Pilog context? how would one go about using >> composite primary keys? >> >> thank you. >> >> /e >> -- >> UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe >> > -- > UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe > -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
Hi Edwin, > anyone encountered creating a composite primary key for Pilog? You mean for database indexes? If so, there is the '+Aux' prefix class which does this, and there is also a function 'aux' for direct access. The Pilog rules know about '+Aux' and handle it accordingly. Cheers, - Alex -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
Re: composite primary keys
Might the +Aux relation be what you're looking for? On Wed, Mar 16, 2011 at 2:19 PM, Edwin Eyan Moragas wrote: > Hi List, > > anyone encountered creating a composite primary key for Pilog? > > or it is applicable in the Pilog context? how would one go about using > composite primary keys? > > thank you. > > /e > -- > UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe > -- UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
