1. euEDB / euSQL primary key problem
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
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
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
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
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
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
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
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
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
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
-
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
-
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
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
-
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
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
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/
