Using Tables in Caravan Lanaguage
------------------------------

To access the database caravan uses an object of type table.
A table is identified by the database name and the tablename.

A table object is instantiated using the following declaration:

table <table-objname>=<dbname>.<tablename>

example:
table mytable=wire.stories;//  mytable represents the table file "stories.data" in db "wire.db"


A table object has following properties besides its field values.

recordno : The recordno property determines which record in the table is accessed.
            It can be set to any value greater than 1. If record number set is not valid (record
            does not exist) , it will be set to invalid value 0. 

selected : Total number of records selected. This is equal to the total number of records in the
           table, by default. After a query this value will reflect the result of the selection. If this
           value is equal to zero, it indicates that no records were selected. You cannot set this value.

tableinfo : Information about this table in XML format.

Changing current record.
1.
<table-objname>(recordno)=<new value>;// set a record number
example 
mytable(recordno)="23"

2.
<table-objname>(nextrecord);// goto next record in the selection.
example
mytable(nextrecord)

3.
mytable(recordno)+="20";// skip 20 records in selection



Deleting a record:
==============

<table-objname>(deleterecord)

example:
<caravan>
table mytable=wire.stories
mytable(recordno)="23"
mytable(deleterecord);
</caravan>


Example showing usage of above properties:

<caravan>
table mytable=wire.stories
var myvar; // general  purpose object holds any kind of data 
myvar(recordno)=mytable(recordno)
mytable(recordno)="23";// goto recordno 23
myvar(total)=mytable(selected)
output con;// (output to console) dumps the table information on caravan console.
mytable(tableinfo);
</caravan>


All user defined fields are also properties of the table object. 
Say wire.stories has following fields:

1. Origin : From where this story came.
2. Content : The text of the story
3. Subject : the heading of the story.
4. Time  : the time of filing this story.

Using the above table the following code will illustrate insertion/modifying and retrieval of data
from caravan tables. 


Asume that a new story is posted to the server using http-post method. The following 
code will insert this story into the table. Posted data is available in an object of
name "form". Assume this form has been submitted from a  browser and has a "heading" and a
"text" field.

<caravan>
table mytable=wire.stories
mytable(subject)=form(heading)
mytable(content)=form(text)
mytable(origin)="post";// story has originated through a post operation.
Time t
mytable(time)=t(time);// put current time
mytable(insert);// invokes table's insert method.
output con;// puts message on console
"Inserted record number : ";mytable(recordno);"\r\n"
close output;// close console
</caravan>

Suppose some stories also come through mail. The code that inserts such stories are given below.

<caravan>
table mytable=wire.stories
mytable(subject)=mail(subject)
mytable(content)=mail(text)
mytable(origin)="mail";// story has originated from a mail
Time t
mytable(time)=t(time);// put current time
mytable(insert);// invokes table's insert method.
output con;// puts message on console
if mytable(recordno)="0"
 "error : coold not create record\r\n"
else
"Inserted record number : ";mytable(recordno);"\r\n";// new recordno is displayed.
close output;// close console
</caravan>

Modifying a Record:
==================

Modifying record is similar to inserting a new record except:

1.The modify method is invoked by :
<table-objname)(modify)

2.Only the current record is modified with new values.

<caravan>
table mytable=wire.stories
Time t
mytable(time)=t(time);// modify the time 
mytable(recordno)="23";// set current recordno
mytable(modify);// invokes table's insert method.
mytable(recordno);// recordno is unchanged
</caravan> 



In the above example the content field is of type FILE, because the text of the story is of
some undefined length. TIME variable t has the current time as default. 

Now we have a table in which stories are regularly inserted either through mail or http post.

With this background we can write some examples for selecting stories
according to some criteria.


Formats of select statements:


select from <table-objname> where [<fieldname> <condition> <reference-value> and ....] order by <fieldname> <asc|desc>
-----------multiple conditions are ANDed together 

select from <table-objname> where [<fieldname> <condition> <reference-value> or ....] order by <fieldname> <asc|desc>
 ---------- multiple conditions are ORed together 
 
select from <table-objname> order by <fieldname> 
---------selects all and  orders by <fieldname>



example:
<caravan>
table mytable=wire.stories
select from mytable where origin = post order by time desc
outpu con
"Total from post :";mytable(selected);"\r\n"
// list all 
loop listloop (mytable(selected));// list first 12 stories
mytable(subject);"\r\n"
mytable(nextrecord);
repeat loop 12
</caravan>

The above code selects those records from mytable which have come through post and
orders the resulting list in reverse chronological order -- most recent ones first.

There are a few more concepts left to be explained:

Using a variable reference value for comparison in select statements:
------------------------------------------------------

When your select statement needs to contain a variable reference-value for comparison it gets a little 
complicated. Value token needs to be  replaced by the property name of an external object 
enclosed within braces as shown below:

select from <tableobject> where <fieldname> <condition> {propertyname}

example :

var sql
time t
t(second)-=3600;// set time back by 1 hour
sql(timefrom)=t(time)
select from mytable where time > {timefrom}

Caravan substitutes the name within the braces with the value of "timefrom" from the object of name
"sql" -- if sql object is not found then it looks for an object with name "form".


Method : "Selectall"

Once a selection is done, all subsequent selections use the current set instead of the set of all
available records. To reset the current selection "selectall" method is used.

<tablename>(selectall)
example:
mytable(selectall);

Following code illustrates all these concepts.


example :

<caravan>
table mytable=wire.stories
select from mytable where origin = mail;// select stories recvd via mail
time t;                                          // get time value for 10 O clock
t(hour)-=t(hour);// set time to 0 hours today
t(hour)+="10";// set time to 10 O clock.
var sql ;// 
sql(timefrom)=t(time)
select from mytable where time > {timefrom} ;// this selection returns a subset from already selected list.
outpu con
"Total from post :";mytable(selected);"\r\n"
// list all 
loop listloop (mytable(selected));// list first 12 stories
mytable(subject);"\r\n"
mytable(nextrecord);
repeat loop 12
</caravan>


Types of conditions in select statement:
---------------------------------

1 Select Greater than: ">"

        example:
        select from mytable where time > {timefrom}

2 Select Less than : "<"

        example:
        select from mytable where time < {timefrom}


3 Select Equal to : "="

        example:
        select from mytable where origin = post

4 Greater than or equal tp ;">="
        example:
        select from mytable where time >= {timefrom}

5 Less than or equal to ;"<="
        example:
        select from mytable where time <= {timefrom}

6 Finding unique values ;"is unqiue"
   
        example :
        select from mytable where origin is unique;// only select where this field has unique values.

Note : The above conditions do not work in FILE fields.

7 Text search using  "like":

Text searches are done in STRING or  FILE type fields using the search engine.

        examples:
        select from mytable where subject like "urgent";// where value is a contant
or
        select from mytable where subject like "{mytoken}";// where value is variable
or 
        select from mytable where subject like poly*;// search for substring (usually slow performance)


It is a good idea to put quotes around the reference-value, specially when it is text.
This is shown below.

example:
        select from mytable where subject like "my token"
or
        select from mytable where subject like "{token}"



Using xml to create a table dynamically:
=================================

We have already seen how to extract the table information in XML:

<table-objname>(tableinfo)

If you study this XML you will also learn how you can create a the tableinfo for a new table.
You can use this file also to create a table dynamically:

<caravan>
file mytablinfo="d:\directory\myfile.xml"
table newtable=xxxx.yyyy;// since its a new table this will create an empty table object.
newtable(tableinfo)=mytableinfo(file);;/ will create the db and table if they dont already exist
</caravan>





I hope this explains the caravan database concepts suficiently so people can write programs.
I suggest that you create a table and insert some values into a few records to try out some of 
these concepts.

Once you know how to create and use tables you will find the caravan database fast,
reliable and very usefull.

All queries are welcome. Suggestions to improve this document is eagerly awaited!