dynamic queries
Tomasz Rakowski <mourawi-/[email protected]> Sun, 12 Nov 2006 09:50:31 -0800 (PST)
| Newsgroups | gmane.text.xml.o-xml |
|---|---|
| Message-ID | <[email protected]> |
--===============0707065197==
Content-Type: multipart/alternative; boundary="0-1545088740-1163353831=:10277"
Content-Transfer-Encoding: 7bit
--0-1545088740-1163353831=:10277
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Hi,
I'm having problems with dynamic queries I create on the fly in oml.
I really would be gratefull for any help.
In my application I allow user to define the logical structure of databas=
e . All that information (entities/attributes definitions, inluding my ow=
n types: locations, styles, colors, symbols) are stored in meta-data part=
of my database.
Data iself are stored in such a way that there is one table which stores =
ids of all entitites (and of course type of entity), but all attributes o=
f entities are stored in seperate tables for each specific type (there is=
seperate table to store all 'int' attributes, seperate to store 'locatio=
ns' attribute, seperate to store 'strings'...).
Then based on that metadata I build queries to retrieve entity data
1. based on entity definition I construct single query which is supposed =
to retrieve entity data
Simple example of Person entity:
SELECT entity.id,
firstname.value AS firstname,
lastname.value AS lastname,
birthyear.value AS birthyear
FROM data_entity as entity
LEFT JOIN data_attr_string as firstname
ON firstname.data_id =3D entity.id AND firstna=
me.attr_id =3D 'firstname'
LEFT JOIN data_attr_string as lastname=20
ON lastname.data_id =3D entity.id AND lastname.=
attr_id =3D 'lastname'
LEFT JOIN data_attr_int as birthyear=20
ON birthyear.data_id =3D entity.id AND birthyea=
r.attr_id =3D 'birthyear'
WHERE entity.entity_id =3D 'Person'
Notice that firstname,lastname,birthyear are attributes defined by user a=
nd are stored in meta-data.
3. Now having above query and entity definition I would like to retrieve =
data but I'm not able to do this=20
<db:execute connection=3D"wisdom" sql=3D"{$viewQuery}">
<data>
HOW TO ACCESS 'firstname' ,'lastname' attributes here since these na=
me are stored in some variables and are not know at OML compilation time?
=20
</data>
</db:execute>
'firstname' name is know in runtime not in compilation time, so I can't u=
se {$firstname} statement.=20
I need to use something like {$<o:eval select=3D"attributeName"/>} which=
of course doesn't work.
=20
Thanks for any hints !
=20
Tomasz Rakowski
PS.=20
I thought about generating and compiling oml on fly but above aproach see=
ms to me be little more simple (of course if it would be feasible).
I could also retrieve entity data using several seperate queries (for eac=
h seperate attribute) but it would be much slower (specially in case of e=
ntity lists...)
=20
---------------------------------
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
--0-1545088740-1163353831=:10277
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Hi,<br><br>I'm having problems with dynamic queries I create on the fly i=
n oml.<br>I really would be gratefull for any help.<br><br>In my applicat=
ion I allow user to define the logical structure of database . All that i=
nformation (entities/attributes definitions, inluding my own types: locat=
ions, styles, colors, symbols) are stored in meta-data part of my databas=
e.<br><br>Data iself are stored in such a way that there is one table whi=
ch stores ids of all entitites (and of course type of entity), but all at=
tributes of entities are stored in seperate tables for each specific type=
(there is seperate table to store all 'int' attributes, seperate to stor=
e 'locations' attribute, seperate to store 'strings'...).<br><br><br>Then=
based on that metadata I build queries to retrieve entity data<br><br>1.=
based on entity definition I construct single query which is supposed to=
retrieve entity data<br>Simple example of Person entity:<br><br> &n=
bsp;
SELECT entity.id,<br> &=
nbsp; firstname.value AS fir=
stname,<br>  =
; lastname.value AS lastname,<br> &n=
bsp; birthyear.v=
alue AS birthyear<br> &=
nbsp; FROM data_entity as entity<br> =
&nb=
sp; LEFT JOIN data_attr_string as firstname<br>  =
; ON=
firstname.data_id =3D entity.id AND firstname.attr_id =3D 'firstname'<br=
> &n=
bsp; LEFT JOIN data_attr_string as lastname <br>=
&n=
bsp; ON lastname.data_id =3D entity.id AND lastname.attr_id =3D 'la=
stname'<br>  =
; LEFT JOIN data_attr_int as birthy=
ear <br> &=
nbsp; ON birthyear.data=
_id =3D entity.id AND birthyear.attr_id =3D 'birthyear'<br> &n=
bsp; WHERE entity.ent=
ity_id =3D 'Person'<br><br>Notice that firstname,lastname,birthyear are a=
ttributes defined by user and are stored in meta-data.<br><br><br>3=
. Now having above query and entity definition I would like to retrieve d=
ata but I'm not able to do this <br> =
<db:execute connection=3D"wisdom" sql=3D"{$viewQuery}"><br> &=
nbsp;
<data><br><br> HOW TO ACCESS 'firstname' ,'lastn=
ame' attributes here since these name are stored in some variables =
and are not know at OML compilation time?<br> <br> =
; </data><br>&nbs=
p; </db:execute><br><br>'firstname' =
name is know in runtime not in compilation time, so I can't use <span sty=
le=3D"font-weight: bold;">{$firstname} </span>statement. <br> I need to u=
se something like<span style=3D"font-weight: bold;"> {$<o:eval select=3D=
"attributeName"/>}</span> which of course doesn't work.<br> <br><br>Th=
anks for any hints !<br> <br><br>Tomasz Rakowski<br><br><br>PS. <br>I tho=
ught about generating and compiling oml on fly but above aproach seems to=
me be little more simple (of course if it would be feasible).<br><br>I c=
ould also retrieve entity data using several seperate queries (for each s=
eperate attribute) but it would be much
slower (specially in case of entity lists...)<br><br><p> 
<hr size=3D1>Cheap Talk? <a href=3D"http://us.rd.yahoo.com/mail_us/taglin=
es/postman8/*http://us.rd.yahoo.com/evt=3D39663/*http://voice.yahoo.com">=
Check out</a> Yahoo! Messenger's low PC-to-Phone call rates.
--0-1545088740-1163353831=:10277--
--===============0707065197==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
o-xml mailing list
o-xml-zRfLyl9bSvv/[email protected]
http://lists.pingdynasty.com/mailman/listinfo/o-xml
--===============0707065197==--