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

[Paweł Cesar Sanjuan <pawel szklarz cc com pl>]

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.