<?xml version="1.0" ?>

<resources>

  <collector use="IAO.SP.hib.Collector merge(map,session)"  param="appid" />
  <fetcher   use="IAO.SP.hib.Fetcher getPage(map,session)"  param="appid" />

  <resource name="SYSTEM">

    <!-- data paging. these are needed for handling temporary table creation and deletion -->

    <sql name='getresultpage'   query='select * from [temptable] [orderclause] limit {startrow}, {rowsperpage}' />
    <sql name='getresultcount'  query='select count(*) as rowcount from [temptable]' />
    <sql name='jointemptables'  query='create table [temptable] [selectclause]' />
    <sql name='indextemptable'  query='create index [indexname] on [temptable] ([keydef])' />
    <sql name='droptemptable'   query='drop table if exists [temptable]' />
    <sql name='loadtemptable'   query='insert into [temptable] ([fieldlist]) values ([valuelist])' />
    <sql name='puttableref'     query='insert into temptables (temptable, action, userid, whencreated, status) values ({temptable}, {action}, {userid}, {whencreated}, {status})' />
    <sql name='gettableref'     query='select * from temptables' />
    <sql name='usetableref'     query='select * from temptables where userid={userid} and action={action}' />
    <sql name='deltableref'     query='delete from temptables where temptable={temptable}' />
    <sql name='createtable'     query='create table [temptable] ([fieldlist])' />
    <sql name='getfulltable'    query='select * from [temptable] order by [keyfield] desc' />
    <sql name='checkForTable'   query='SELECT TABLE_SCHEMA as tableschema, TABLE_NAME as tablename FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA={dbname} AND TABLE_NAME={temptable}' />
    <action name='drilldown' collector='default' fetcher='default'>
      <sql name='dd'     query='select * from [tabletouse] [wheredown]' />
    </action>

    <!-- table columns. this is better than using jdbc meta data -->
    <sql name='getTableCols'    query='SELECT COLUMN_NAME AS column_name, COLUMN_TYPE AS column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA={DB} AND TABLE_NAME={tablename}' />

    <!-- partition manangement -->

    <sql name='getTableInfo'    query='SELECT PARTITION_NAME as part,PARTITION_DESCRIPTION as partdesc, PARTITION_ORDINAL_POSITION as ord FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA={dbname} AND TABLE_NAME={tablename} ORDER BY ord' />

    <sql name='getPartInfo'     query='select * from partitions where tablename={tablename}' />
    <sql name='addPartInfo'     query='replace into partitions (tablename,minvalue,maxvalue,pcount,minpno,maxpno) values ({tablename},{minvalue},{maxvalue},{pcount},{minpno},{maxpno})' />
    <sql name='addPartition'    query="alter table [tablename] add partition (partition [pname] values less than (to_days('[plimit]')))" />
    <sql name='delPartition'    query='alter table [tablename] drop partition [pname]' />
    <sql name='getMaxDatestamp' query='select max(dstamp) as maxdstamp from [tablename]' />

    <!-- backups -->

    <sql name='copyTable'       query='create table [temptable] as select * from [tablename] where [keyname] &gt; {minkey} and [keyname] &lt; {maxkey}' />
    <sql name='readFromFile'    query='load data infile [filename] into [temptable]' />
    <sql name='writeToFile'     query="select * into outfile '[filename]' fields terminated by ',' optionally enclosed by '&quote;' lines terminated by '\n' from [tablename]" />

  </resource>

</resources>
