Re: [gnome-db] sql extension to libxslt using libgda.

["Vivien Malerba" <vmalerba gmail com>]

This is an interresting piece of work. Tell me if I'm wrong (I'm no
XSLT expert), but here is how I understand it: the code you show here
is an extension which can be used with the libxslt library, declared
as such to the libxslt library (using xsltRegisterExtModule()).

As you might expect, I have a few questions/remarks:
* (not strictly related to your code) is it related to libxslt or to
Libexslt (http://xmlsoft.org/XSLT/EXSLT/index.html)?
* how should libgda make that extension available (if the libxslt
framework is generic enough, I would prefer not add some extra API
such as sqlxslt_process_xslt_file_ext(), to rely as much as possible
to libxslt's own API)? Would it be possible for example to have only 2
calls like:
   xsltTransformContextPtr *gda_xslt_create_context (GdaConnection *cnc)
   xsltTransformContextPtr *gda_xslt_create_context_with_dict (GdaDict *dict)
  and then let the user use that transform context as he would for any
other tranform context?
* for now the code depends on log4c, that dependency would have to be dropped
* the gda_value_stringify() calls can easily be replaced by other
calls, I can help there!
* Would you agree to licence your code under the LGPL as all libgda is licenced?

Thanks for your work,

Vivien

On 9/10/07, Paweł Cesar Sanjuan Szklarz <paweld2 gmail com> wrote:
> Hi.
>
> I write a small extension to libxslt that use libgda (last version from
> svn) to execute sql and create xml files.
> Of course it is a prove of concept, in particular I am using functions
> like gda_value_stringify, that are only for debug.
>
> The new extensions elements are:
> node
> <sql:query resultset="user" onempty="fault" faultcode="-1">__SQL__</>
>  that execute a sql query and save the results on a hash.
>
> and functions
> sql:getvalue('resultset_name','column_name')
> sql:getnodeset('resultset_name')
>
> getvalue:
>  gets the value of the colum 'column_name' on the first row of the data
> model with name 'resultset_name'.
> getnodeset:
>  makes a nodeset for the data model with name 'resultset_name'.
>
> I thing that the tests on libgda can be made using a similar extension.
> And maybe this extension can be added to libgda as a aditional feature,
> of course after a good discution of the idea.
>
> My real goal is to implement Web Services with this extension.
>
> I put the source code on
> http://ambargroup.pl/sqlxslt/libsqlxslt.tar.bz2
> with the extension, a db definition for Postgres8 and a test program.
>
> Explication of the test program:
>
> The test program makes a connection to a DB from a dns, and register the
> extension adding the conection to the XSLTTransformationContext.
>
> The xml data are this:
> ------------------------------------------------
> <?xml version="1.0" encoding="UTF-8"?>
> <login>
>   <login>admin</login>
>   <password>asdasdasd</password>
> </login>
> ------------------------------------------------
>
> The xsl file:
> ------------------------------------------------
> <?xml version="1.0" encoding="UTF-8" ?>
> <xsl:stylesheet version="1.0"
>   xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
>   xmlns:sql="http://pmsoft.ambargroup.pl/ns/sql-ext";
>   xmlns:ae="http://ambargroup.pl/AEsession/";
> extension-element-prefixes="sql"
> >
>
> <xsl:output method="xml" indent="yes" encoding="utf-8"/>
>
> <xsl:variable name="login" select="/login/login/text()"/>
> <xsl:variable name="password" select="/login/password/text()"/>
>
> <xsl:template match="/">
> <!--this extension element makes a query and saves the result on a hash
> with the name 'user'-->
> <!--the attribute onempty and faultcode are obligatory but not used
> now-->
> <sql:query resultset="user" onempty="fault" faultcode="-1">
> select * from users
>  where
>  login = ## /*type:gchararray name:"login"*/ AND
>  password = ## /*type:gchararray name:"password"*/
> </sql:query>
> <!--here, the parameters for sql are from the xsl:variable-->
> <xsl:variable name="userid" select="sql:getvalue('user','userid')"/>
> <!--a new variable gets its value from the results from the sql-->
> <ae:user>
>   <ae:userData>
> <xsl:call-template name="userData"/>
>   </ae:userData>
>   <ae:rightList>
> <xsl:call-template name="rightList">
>     <xsl:with-param name="userid" select="$userid"/>
> </xsl:call-template>
>   </ae:rightList>
>   <ae:userTagList>
> <xsl:call-template name="userTagList">
>     <xsl:with-param name="userid" select="$userid"/>
> </xsl:call-template>
>   </ae:userTagList>
> </ae:user>
> </xsl:template>
>
> <xsl:template name="rightList">
> <xsl:param name="userid"/>
> <sql:query resultset="rights" faultcode="-2" onempty="ok">
> select
>  SR.name as name
> from
> users_rights as UR
> inner join system_right as SR on (SR.rightid = UR.rightid)
> where
>  userid=## /*type:gint name:"userid"*/
> </sql:query>
> <xsl:variable name="nodeset_rights" select="sql:getnodeset('rights')"/>
> <!--create a nodeset from the result of the sql.->
> <ae:debug>
> <!--debug the nodeset created be the sql extension->
> <xsl:copy-of select="$nodeset_rights"/>
> </ae:debug>
> <xsl:for-each select="$nodeset_rights/row">
>         <ae:rightSimple>
>                 <ae:name><xsl:value-of
> select="column[ name='name']/text()"/></ae:name>
>         </ae:rightSimple>
> </xsl:for-each>
> </xsl:template>
>
> <xsl:template name="userData">
>     <ae:Id><xsl:value-of
> select="sql:getvalue('user','userid')"/></ae:Id>
>     <ae:password><xsl:value-of
> select="sql:getvalue('user','password')"/></ae:password>
>     <ae:login><xsl:value-of
> select="sql:getvalue('user','login')"/></ae:login>
>     <ae:lastName><xsl:value-of
> select="sql:getvalue('user','lastname')"/></ae:lastName>
>     <ae:firstName><xsl:value-of
> select="sql:getvalue('user','firstname')"/></ae:firstName>
> </xsl:template>
>
> <xsl:template name="userTagList">
> <xsl:param name="userid"/>
> <sql:query resultset="tags" faultcode="-2" onempty="ok">
> select UT.tag,UT.value,UT.userid,TT.name as type
> from
>  users_tags as UT
>  inner join tag_type as TT on (UT.typeid = TT.typeid)
>  where
>  userid=## /*type:gint name:"userid"*/
> </sql:query>
> <xsl:variable name="nodeset_tags" select="sql:getnodeset('tags')"/>
> <ae:debug>
> <xsl:copy-of select="$nodeset_tags"/>
> </ae:debug>
> <xsl:for-each select="$nodeset_tags/row">
>         <ae:userTag>
>                 <xsl:attribute name="type">
>                         <xsl:value-of select="column[ name='type']/text()"/>
>                 </xsl:attribute>
>                 <ae:tag><xsl:value-of select="column[ name='tag']/text()"/></ae:tag>
>                 <ae:value><xsl:value-of
> select="column[ name='value']/text()"/></ae:value>
>         </ae:userTag>
> </xsl:for-each>
> </xsl:template>
>
> </xsl:stylesheet>
> ------------------------------------------------
>
> Using the test program you get this output:
> ------------------------------------------------
> <?xml version="1.0" encoding="utf-8"?>
> <!--libxslt:test element test worked-->
> <ae:user xmlns:ae="http://ambargroup.pl/AEsession/";>
>   <ae:userData>
>     <ae:Id>2</ae:Id>
>     <ae:password>asdasdasd                       </ae:password>
>     <ae:login>admin           </ae:login>
>     <ae:lastName>administrator</ae:lastName>
>     <ae:firstName>admin</ae:firstName>
>   </ae:userData>
>   <ae:rightList>
>     <!--libxslt:test element test worked-->
>     <ae:debug>
>       <resultset>
>         <row>
>           <column name="name">usr_create</column>
>         </row>
>         <row>
>           <column name="name">usr_delete</column>
>         </row>
>         <row>
>           <column name="name">adm_modul</column>
>         </row>
>         <row>
>           <column name="name">adm_system</column>
>         </row>
>         <row>
>           <column name="name">sys_debug</column>
>         </row>
>       </resultset>
>     </ae:debug>
>     <ae:rightSimple>
>       <ae:name>usr_create</ae:name>
>     </ae:rightSimple>
>     <ae:rightSimple>
>       <ae:name>usr_delete</ae:name>
>     </ae:rightSimple>
>     <ae:rightSimple>
>       <ae:name>adm_modul</ae:name>
>     </ae:rightSimple>
>     <ae:rightSimple>
>       <ae:name>adm_system</ae:name>
>     </ae:rightSimple>
>     <ae:rightSimple>
>       <ae:name>sys_debug</ae:name>
>     </ae:rightSimple>
>   </ae:rightList>
>   <ae:userTagList>
>     <!--libxslt:test element test worked-->
>     <ae:debug>
>       <resultset>
>         <row>
>           <column name="tag">testtag2</column>
>           <column name="value">testt222222</column>
>           <column name="userid">2</column>
>           <column name="type">system                          </column>
>         </row>
>         <row>
>           <column name="tag">testtag1</column>
>           <column name="value">testtest111111111111</column>
>           <column name="userid">2</column>
>           <column name="type">system                          </column>
>         </row>
>         <row>
>           <column name="tag">testtag2</column>
>           <column name="value">testt3333333332</column>
>           <column name="userid">2</column>
>           <column name="type">option                          </column>
>         </row>
>       </resultset>
>     </ae:debug>
>     <ae:userTag type="system                          ">
>       <ae:tag>testtag2</ae:tag>
>       <ae:value>testt222222</ae:value>
>     </ae:userTag>
>     <ae:userTag type="system                          ">
>       <ae:tag>testtag1</ae:tag>
>       <ae:value>testtest111111111111</ae:value>
>     </ae:userTag>
>     <ae:userTag type="option                          ">
>       <ae:tag>testtag2</ae:tag>
>       <ae:value>testt3333333332</ae:value>
>     </ae:userTag>
>   </ae:userTagList>
> </ae:user>
> ------------------------------------------------
>
>
>
> Saludos.
> Pawel Cesar Sanjuan Szklarz.
>
>
> _______________________________________________
> gnome-db-list mailing list
> gnome-db-list gnome org
> http://mail.gnome.org/mailman/listinfo/gnome-db-list
>