NAME

XML::Template::Element::DB - XML::Template module that implements the SQL tagset.

SYNOPSIS

This XML::Template module implements the SQL tagset. XML::Template plugin modules that query SQL databases should be derived from this module. The database and table to query are associated with the namespace of the tags and is specified in the XML::Template configuration file (see XML::Template::Config).

CONSTRUCTOR

XML::Template::Element::Block inherits its constructor method, new, from XML::Template::Element.

SQL TAGSET METHODS

select

This method implements a SELECT SQL query on the database and table associated with the tag's namespace. If this tag is nested in a related namespace, the column will be selected via an intermediate mapping database table, defined in the XML::Template configuration file. For instance, suppose the following XML is parsed:

<xml xmlns:group="http://syrme.net/xml-template/group/v1"
     xmlns:item="http://syrme.net/xml-template/item/v1">
  <group:select name="group1">
    <item:select fields="*" name="item1">
      ...
    </item:select>
  </group:select>
</xml>

The relationship table that maps items to groups should be defined in the XML::Template configuration file. If the relation table is group2item, the following SQL would be generated for the item tag:

SELECT * FROM items,group2item
         WHERE items.itemname=group2item.itemname
               AND group2item.groupname='group1'
               AND group2item.itemname='item1'

The following attributes are used:

name

A comma-separated list of the names of the primary keys of the database column to select. The primary keys and their order is specified in the XML::Template configuration file. This attribute is not required.

fields, field

A comma separated list of the database table fields to return. To return all fields, use '*'. For each field returned a variable will be set with the field's name and value. These variables will be available in the content of the select element.

Remaining attributes will be used to constrain the selection. For instance, the element

<block:select name="block1" fields="*"
              title="Title" description="Desc"/>

will result in the following SQL query

SELECT * FROM blocks
         WHERE blockname='block1'
               AND title='Title'
               AND description='Desc'

The value of a remaining attribute may be a comma-separated list, in which case, each element in the list is combined into an OR clause. So the following element:

<block:select name="block1" fields="*" title="Title,Title2"/>

would produce the following SQL query:

SELECT * FROM blocks
         WHERE blockname='block1'
         AND (title='Title' OR title='Title2')

In addition, if the value of a remaining attribute contains a '%', the LIKE comparison will be used rather than =. For instance,

<block:select fields="blockname,body" title="%Title%"/>

would produce the following SQL query:

SELECT blockname,body FROM blocks
                      WHERE title LIKE '%Title%'

update

This method implements an UPDATE SQL query on the database and table associated with the tag's namespace. If this tag is nested in a related namespace, the column to be updated will be determined via an intermediate mapping database table, defined in the XML::Template configuration file. See select for more details on related namespaces.

The children of this element should be tags with names of the database table columns and their new values. For instance:

<xml xmlns:item="http://syrme.net/xml-template/block/v1">
  <item:update name="item1">
    <item:title>Title</item:title>
    <item:description>Description</item:description>
  </item:update>
</xml>

These children tags are handled by the AUTOLOAD subroutine.

The following attributes are used:

name

A comma-separated list of the names of the primary keys of the database column to select. The primary keys and their order is specified in the XML::Template configuration file. This attribute is required for updating.

insert

If true, insert a new column in the database table if the one named by the attribute name is not found. The default value is false.

Remaining attributes will be used to constrain the selection of which column to update. See select for more details.

insert

This method implements an INSERT SQL query on the database and table associated with the tag's namespace. If this tag is nested in a related namespace, the relation table defined in the XML::Template configuration file that maps rows between the two related tables will be updated. See select for more details on related namespaces.

The children of this element should be tags with names of the database table columns and their new values. See update for more details.

The following attributes are used:

name

A comma-separated list of the names of the primary keys of the database column to insert. The primary keys and their order is specified in the XML::Template configuration file. This attribute is required for inserting.

delect

This method implements a DELETE SQL query on the database and table associated with the tag's namespace. If this tag is nesterd in a related namespace, the column to be updated will be determined via an intermediate maping database table, defined in the XML::Template configuration file. See select for more details on related namespaces.

The following attributes are used:

name

A comma-separated list of the names of the primary keys of the database column to delete. The primary keys and their order is specified in the XML::Template configuration file. This attribute is not required.

Remaining attributes will be used to constrain the selection of which column to delete. See select for more details.

alter

This method implements an ALTER SQL query on the database and table associated with the tag's namespace. The following attributes are used:

values

action

columns, column

new_column

type

length

decimals

unsigned

zerofull

binary

null

def_default

auto_increment

def_primary_key

position

index

primary_key

unique

fulltext

default

new_table

foreach

XML::Template::Element::DB is a subclass of XML::Template::Element::Iterator, so it inherits the foreach method, which in conjunction with the iterator methods defined in this module, implements iteration through the rows in a database. For example,

<item:foreach xmlns:item="http://syrme.net/xml-template/item/v1"
              fields="*">
  ${title}: ${description}
</item:foreach>

iterates through each column in the items table and prints the title and description.

The following attributes are used:

fields, field

A comma separated list of the database table fields to return. To return all fields, use '*'. For each field returned a variable will be set with the field's name and value. These variables will be available in the content of the select element.

orderby

A comma-separated list of the fields used to order the list of rows being iterated through.

limit

Constrains the number of rows being iterated through. If one integer, it specifies the number of rows to iterate through, starting at the beginning. If two integers separated by a comma, the first specifies the offset at which to start iterating, and the second specifies the number of rows to iterate through.

AUTHOR

Jonathan Waxman <jowaxman@bbl.med.upenn.edu>

COPYRIGHT

Copyright (c) 2002-2003 Jonathan A. Waxman All rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

cpanm XML::Template

perl -MCPAN -e shell
install XML::Template