1. euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 17, 2007
- 550 views
hey.... I need to have both keys in a 2-key table form a "composite" primary key. Can't seem to be able to convince euEDB to accept that scenario. Anybody know if this feature is supported in euEDB / euSQL ? Example: Students Table Participants Table Activities Table Name | ID* ID* | Activity* Activity* | Cost You see how the fields in the "Participants" table are really "foreign" keys that _together_ constitutes the table's unique key? TIA... -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
2. Re: euEDB / euSQL primary key problem
- Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Feb 18, 2007
- 527 views
duke normandin wrote: > > hey.... > > I need to have both keys in a 2-key table form a "composite" primary key. > Can't > seem to be able to convince euEDB to accept that scenario. Anybody know if > this > feature is supported in euEDB / euSQL ? > > Example: > > Students Table Participants Table Activities Table > > Name | ID* ID* | Activity* Activity* | Cost > > You see how the fields in the "Participants" table are really "foreign" keys > that _together_ constitutes the table's unique key? TIA... You need to put ID and Activity into a sequence, and make that the key. For eusql, you'd need to make them 'sub-fields.' PK.ID PK.Activity Matt
3. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 18, 2007
- 548 views
Matt Lewis wrote: > > duke normandin wrote: > > > > hey.... > > > > I need to have both keys in a 2-key table form a "composite" primary key. > > Can't seem to be able to convince euEDB to accept that scenario. Anybody > > know if this feature is supported in euEDB / euSQL ? > > > > Example: > > > > Students Table Participants Table Activities Table > > > > Name | ID* ID* | Activity* Activity* | Cost > > > > You see how the fields in the "Participants" table are really" foreign" > > keys that _together_ constitutes the table's unique key? TIA... > > You need to put ID and Activity into a sequence, and make that the key. > For eusql, you'd need to make them 'sub-fields.' > > PK.ID > PK.Activity > > Matt Thanks for clueing me in, Matt. I haven't gotten into *any* Euphoria /euSQL coding yet. I'm just fooling around with your "edb" widget. Very cool! Good way to brush up on my SQL as well! So, as I'm writing this, I'm also looking at the "table definition" tab for my "Participants" table. Am I correct in assuming that all I have to do is rename ID => PK.ID and ACTIVITY => PK.ACTIVITY _and_ click the Primary Key box for both? I'll try it at any rate, but I thought that I'd ask while I was thanking you for your reply. L8r... -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
4. Re: euEDB / euSQL primary key problem
- Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Feb 18, 2007
- 513 views
duke normandin wrote: > > Thanks for clueing me in, Matt. I haven't gotten into *any* Euphoria /euSQL > coding yet. I'm just fooling around with your "edb" widget. Very cool! Good > way to brush up on my SQL as well! > > So, as I'm writing this, I'm also looking at the "table definition" tab for my > "Participants" table. Am I correct in assuming that all I have to do is rename > ID => PK.ID and ACTIVITY => PK.ACTIVITY _and_ click the Primary Key box > for both? I'll try it at any rate, but I thought that I'd ask while I was > thanking > you for your reply. L8r... Actually, the primary key is determined by whatever the first listed field is. The check mark is more to communicate to you what the primary key is. So you'd have to move them (or at least one of them) to the top of the list. Matt
5. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 18, 2007
- 524 views
Matt Lewis wrote: > > duke normandin wrote: > > > > hey.... > > > > I need to have both keys in a 2-key table form a "composite" primary key. > > Can't > > seem to be able to convince euEDB to accept that scenario. Anybody know if > > this > > feature is supported in euEDB / euSQL ? > > > > Example: > > > > Students Table Participants Table Activities Table > > > > Name | ID* ID* | Activity* Activity* | Cost > > > > You see how the fields in the "Participants" table are really "foreign" keys > > that _together_ constitutes the table's unique key? TIA... > > You need to put ID and Activity into a sequence, and make that the key. > For eusql, you'd need to make them 'sub-fields.' > > PK.ID > PK.Activity > > Matt Hey Matt... I tried this query (it's valid!) SELECT STUDENTS.NAME, PARTICIPANTS.PK.ACTIVITY FROM STUDENTS, PARTICIPANTS.PK (see note below) WHERE STUDENTS.ID=PARTICIPANTS.PK.ID No joy! Note: The above query passes your "parse" tab test -- but no data is returned. If I change the FROM statement to -> PARTICIPANTS only, edb spits out an error message. Did I create the "sub-fields" correctly by simply *naming* them "??dot??" -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
6. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 18, 2007
- 542 views
- Last edited Feb 19, 2007
Matt Lewis wrote: > > duke normandin wrote: > > > > hey.... > > > > I need to have both keys in a 2-key table form a "composite" primary key. > > Can't > > seem to be able to convince euEDB to accept that scenario. Anybody know if > > this > > feature is supported in euEDB / euSQL ? > > > > Example: > > > > Students Table Participants Table Activities Table > > > > Name | ID* ID* | Activity* Activity* | Cost > > > > You see how the fields in the "Participants" table are really "foreign" keys > > that _together_ constitutes the table's unique key? TIA... > > You need to put ID and Activity into a sequence, and make that the key. > For eusql, you'd need to make them 'sub-fields.' > > PK.ID > PK.Activity > > Matt hey.... This query passed as well AND worked....... SELECT STUDENTS.NAME FROM STUDENTS INNER JOIN PARTICIPANTS ON STUDENTS.ID=PARTICIPANTS.PK.ID WHERE PARTICIPANTS.PK.ACTIVITY='Swimming' Go figure! I must have set it up correctly ;) I should crank out a mini-HOWTO eventually on database normalizing and setting up euSQL via "edb" and manually as well I suppose. Thanks! -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
7. Re: euEDB / euSQL primary key problem
- Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Feb 19, 2007
- 513 views
duke normandin wrote: > > hey.... > > This query passed as well AND worked....... > > SELECT STUDENTS.NAME > FROM STUDENTS > INNER JOIN PARTICIPANTS > ON STUDENTS.ID=PARTICIPANTS.PK.ID > WHERE PARTICIPANTS.PK.ACTIVITY='Swimming' > > Go figure! > > I must have set it up correctly ;) I should crank out a mini-HOWTO eventually > on database normalizing and setting up euSQL via "edb" and manually as well > I suppose. Thanks! I'm not sure why the previous query didn't work, though I can tell you that JOINs work better than an implicit join using a WHERE clause. JOINs are better optimized, especially if there are indices created on the joined fields. Matt
8. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 19, 2007
- 509 views
Matt Lewis wrote: > > duke normandin wrote: > > > > hey.... > > > > This query passed as well AND worked....... > > > > SELECT STUDENTS.NAME > > FROM STUDENTS > > INNER JOIN PARTICIPANTS > > ON STUDENTS.ID=PARTICIPANTS.PK.ID > > WHERE PARTICIPANTS.PK.ACTIVITY='Swimming' > > > > Go figure! > > > > I must have set it up correctly ;) I should crank out a mini-HOWTO > > eventually on database normalizing and setting up euSQL via "edb" and > > manually as well I suppose. Thanks! > > I'm not sure why the previous query didn't work, though I can tell you that > > JOINs work better than an implicit join using a WHERE clause. JOINs are > better optimized, especially if there are indices created on the joined > fields. > > Matt Makes sense! Thanks for getting back to me. BTW, how hard would it be to add username/password feature to euSQL in order to "connect" to a database? I realize that the "authentication" phase could be done quite seperately, but I'm interested in the "builtin" server/client approach. L8r... -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
9. Re: euEDB / euSQL primary key problem
- Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Feb 19, 2007
- 507 views
duke normandin wrote: > > Makes sense! Thanks for getting back to me. BTW, how hard would it be to add > username/password feature to euSQL in order to "connect" to a database? > > I realize that the "authentication" phase could be done quite seperately, but > I'm interested in the "builtin" server/client approach. L8r... I've thought about that, and I think that it would be something that would sit on top of the normal eusql code. It's not really something that I know much about, however. If I were to do it, I'd probably use the sockets stuff in wxEuphoria. Matt PS Just the other day, I recompiled wxEuphoria under win32 using wxWidgets 2.8.0. Still lots of bugs, and very little actually wrapped, but it basically seems to work.
10. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 19, 2007
- 506 views
- Last edited Feb 20, 2007
Matt Lewis wrote: > > duke normandin wrote: > > > > Makes sense! Thanks for getting back to me. BTW, how hard would it be to > > > > add username/password feature to euSQL in order to "connect" to a > > database? I realize that the "authentication" phase could be done quite > > separately, but I'm interested in the "builtin" server/client approach. > > I've thought about that, and I think that it would be something that would > sit on top of the normal eusql code. It's not really something that I know > much about, however. If I were to do it, I'd probably use the sockets > stuff in wxEuphoria. > I'll have to take a look at the wxEuphoria code. BTW, I'm almost certain that there is a problem with the way eusql "parses" a SELECT/FROM/WHERE query when more than one table is involved. For example, the following bombs: SELECT NAMES.LASTNAME, ADDRESSES.EMAIL_ADDRESS FROM NAMES, ADDRESSES WHERE NAMES.ID=ADDRESSES.ID AND ADDRESSES.SKYPE_ID='perlster' with the error message: EuSQL error: INVALID TABLE ADDRESSES but this query is successful: SELECT ADDRESSES.EMAIL FROM ADDRESSES WHERE ADDRESSES.SKYPE_ID='perlster' So there's nothing wrong with the table; and the query is textbook valid ;) What am I _not_ seeing here? -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
11. Re: euEDB / euSQL primary key problem
- Posted by c.k.lester <euphoric at cklester.com> Feb 19, 2007
- 502 views
- Last edited Feb 20, 2007
duke normandin wrote: > > I'll have to take a look at the wxEuphoria code. BTW, I'm almost certain that > there is a problem with the way eusql "parses" a SELECT/FROM/WHERE query when > more than one table is involved. For example, the following bombs: You probably need a JOIN command in there. SELECT NAMES.LASTNAME, ADDRESSES.EMAIL_ADDRESS FROM NAMES INNER JOIN ADDRESSES ON NAMES.ID = ADDRESSES.ID WHERE NAMES.ID=ADDRESSES.ID
12. Re: euEDB / euSQL primary key problem
- Posted by c.k.lester <euphoric at cklester.com> Feb 19, 2007
- 497 views
- Last edited Feb 20, 2007
c.k.lester wrote: > > duke normandin wrote: > > > > I'll have to take a look at the wxEuphoria code. BTW, I'm almost certain > > that > > there is a problem with the way eusql "parses" a SELECT/FROM/WHERE query > > when > > more than one table is involved. For example, the following bombs: > > You probably need a JOIN command in there. > > SELECT NAMES.LASTNAME, ADDRESSES.EMAIL_ADDRESS > FROM NAMES > INNER JOIN ADDRESSES > ON NAMES.ID = ADDRESSES.ID > WHERE NAMES.ID=ADDRESSES.ID <-- OOPS!!!! Should be: WHERE ADDRESSES.SKYPE_ID='perlster'
13. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 19, 2007
- 505 views
- Last edited Feb 20, 2007
c.k.lester wrote: > > duke normandin wrote: > > > > I'll have to take a look at the wxEuphoria code. BTW, I'm almost certain > > that > > there is a problem with the way eusql "parses" a SELECT/FROM/WHERE query > > when > > more than one table is involved. For example, the following bombs: > > You probably need a JOIN command in there. > > SELECT NAMES.LASTNAME, ADDRESSES.EMAIL_ADDRESS > FROM NAMES > INNER JOIN ADDRESSES > ON NAMES.ID = ADDRESSES.ID > WHERE NAMES.ID=ADDRESSES.ID If you read several msgs back you'll see that I'm aware of using "INNER JOIN" etc for multi-table queries. My point is that multi-table queries can also be done with SELECT/FROM/WHERE queries as well. ;) I'm having trouble getting that to happen using eusql/edb. -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/
14. Re: euEDB / euSQL primary key problem
- Posted by cklester <cklester at yahoo.com> Feb 19, 2007
- 503 views
- Last edited Feb 20, 2007
duke normandin wrote: > c.k.lester wrote: > > duke normandin wrote: > > > I'll have to take a look at the wxEuphoria code. BTW, I'm almost certain > > > that > > > there is a problem with the way eusql "parses" a SELECT/FROM/WHERE query > > > when > > > more than one table is involved. For example, the following bombs: > > You probably need a JOIN command in there. > > SELECT NAMES.LASTNAME, ADDRESSES.EMAIL_ADDRESS > > FROM NAMES > > INNER JOIN ADDRESSES > > ON NAMES.ID = ADDRESSES.ID > > WHERE NAMES.ID=ADDRESSES.ID > If you read several msgs back you'll see that I'm aware of using "INNER JOIN" > etc for multi-table queries. Sorry. I just thought maybe you forgot in this instance. :) -=ck "Programming in a state of Euphoria." http://www.cklester.com/euphoria/
15. Re: euEDB / euSQL primary key problem
- Posted by Matt Lewis <matthewwalkerlewis at gmail.com> Feb 20, 2007
- 540 views
duke normandin wrote: > > If you read several msgs back you'll see that I'm aware of using "INNER JOIN" > etc for multi-table queries. My point is that multi-table queries can also be > done with SELECT/FROM/WHERE queries as well. ;) I'm having trouble getting > that > to happen using eusql/edb. Yeah, eusql isn't exactly standard sql... :( That's definitely something that it should be able to handle, however. Matt
16. Re: euEDB / euSQL primary key problem
- Posted by duke normandin <dnormandin at bsdrocksperlrolls.com> Feb 20, 2007
- 506 views
Matt Lewis wrote: > > duke normandin wrote: > > > > If you read several msgs back you'll see that I'm aware of using "INNER > > JOIN" etc for multi-table queries. My point is that multi-table queries > > can also be done with SELECT/FROM/WHERE queries as well. ;) I'm having > > trouble getting that to happen using eusql/edb. > > Yeah, eusql isn't exactly standard sql... :( > > That's definitely something that it should be able to handle, however. No worries! Now that i know, I'll use INNER JOIN exclusively whenever I use euSQL. Thx everybody for the input! -- duke SW of Calgary - near the Rockies http://www.rootshell.be/~perlster/euphoria/