Builtin Database Access

CloudWriter comes with a builtin relational database management system that has the full features of an RDBMS but with minimal administrative requirements.  The database server may be accessed directly from CloudWriter using CloudWriter classes.  There is also an ODBC-based access method from CloudWriter.  The server may also be accessed from C++ using the C++ classes, or from any language that supports ODBC.

The Database Server

When the CloudWriter app server is started, it opens up one or more connections to the database server.  This server may also receive requests from other programs resident on the server or from remote clients using TCP/IP.

Creating a new Database

Before a database may be accessed, it must exist.  To create a new database use the Create global function

Create(string databasename)

Removing a Database

An unwanted database is removed from the server and the database file is deleted by the Remove function.

Remove(string databasename)

Database Classes

There are three main database classes used in CloudWriter scripts or programs: Database, Table, and Column.  For detailed information, you may refer to the underlying C++ Classes in RC21.

class Database

Database(string name)

 Constructor

string Name()

 Returns name of the database

string Release()

 Returns RC21 database release under which this database was constructed

int AddTable(string name, string description, string view)

 Add a table to the database; view, if supplied, defines a view; returns an error code

int DeleteTable(name)

 Deletes the named table;  returns an error code

int CountTables()

 Returns the number of user tables in the database

bool TableExists(string name)

 Returns true if the named table exists in the database

int Commit()

 Commits changes to the database

int RollBack(), int Rollback

 Rolls back changes to the database

class Table

Table(Database d, string name)

Constructor

string Name()

Returns the name of the table

Name(string newname)

Rename this table

stringDescription()

Returns the description of the table

Description(string newdescription)

Gives a new description to the table

string View()

Returns the view of this table (if this table is a view)

View(string newview)

Replaces the view expression of a table that is a view

int ID()

Returns the unique table identifier for the table (mostly for internal use)

int CountColumns()

Returns the number of user columns in the table

int CountRows()

Returns the number of non-deleted rows in the table

int AddColumn(string name, int attributes, string description, string format, string reference)

Adds a new column with the given name to the table;  this member function takes 1 to 5 arguments;  if additional arguments are present, they are:

attributes:  an integer that results from or'ing together the manifest constants for type, indexed/not indexed, etc.

string description

string format: a C printf format

string reference: the name of a reference table and column in the form table.column that contains valid values for this column

int AddKey(string name, string keys, int attributes)

Adds a new key to the table;  a key is logically a concatenation of the values or substings of the values of other columns in the table with ascending and descending segments;  for further information, please see the RC21 documentation

int DeleteColumn(string name)

Delete the named column

int AddRow()

Add a row to the table;  the table will be positioned at the new empty row

int CopyRow(any o)

If o is a table object, then the row is copied into the indicated table;  a new row is added and columns with the same names receive the values;  if o is an integer, then the values are copied to another row in the same table - the other row having the rowid indicated by the integer

int DuplicateRow()

make a copy of the current row and add it to the end of the table;  position to the new row

bool IsaRow()

If the table is currently positioned at a non-deleted row return true otherwise return false

int FinishRow()

This function performs any row-specific operations required due to a change in the value of any column of the current row - like indexing, checking for duplicate keys, etc.

int ResetRow()

Go to the beginning of the table

int NextRow()

Go to the next row in the table;  "next" may be determined by an OrderBy()

int PrevRow()

Go to the previous row;  "previoust" may be determined by an OrderBy()

int CurrentRow()

Return the rowid of the current row;  this may be used as an argument to GotoRow or CopyRow;  it is valid only during the current database session

int GotoRow(long r)

Position to the row with the given rowid

int Erase()

Change the values of all the columns in the current row to null

int OrderBy(string name)

When traversing this table, go in the order of the column or key indicated by name

int Unordered()

When traversing this table, go in the natural order of the table (probably the order the rows were added)

int Filter(string filterexpression ...)

Place a filter on the table;  only rows that satisfy the constraints of the filter will be visible;  the variable argument list contains the values for replaceable elements in the filterexpression;  filters may be stacked

ClearFilters()

Remove the filter constraint

long SerNo()

Return a unique serial number for this table

class Column

Column(Table t, string name)

 Constructor that creates a column object for the column with the indicated name in the table t

string Name()

 Returns the name of this column

int Name(string newname)

 Rename the column

string Description()

 Returns the description of this column

int Description(newDescription)

 Changes the description of the column

string Format()

 Returns the C printf format associated with this column

int Format(newFormat)

 Changes the C printf format for this column

int Attributes()

 Returns the attributes word for this column

int Attributes(newAttributes)

 Changes the attributes for the column if new attributes are permitted

int Index()

 Sets the attribute of the column to "indexed";  indexes the column

int Unindex()

 Removes the "indexed" attribute of the column; discards the index

Table Tab()

 Returns the Table object associated with this column object

int ID()

 Returns the unique column identifier for this column

int Erase()

 Erases all the values in all the rows in the column

bool HasAnyValues()

 Returns true if there are any non-null values in any row in this column

int Copy(Column c)

 Copies all the values in this column to another column in the same table;  column c must be in the same table

int AddRow()

 Adds a row to the table to which this column belongs

int PutInteger(x)

 Stores the integer x in the column

int PutReal(x)

 Stores the real value x in the column

PutString(x)

 Stores the string value x in the column

PutSTRING(x)

 Stores the case-insensitive string x in the column

put(any x)

 Stores an CloudWriter value of any type in the column

put_to(any x)

 Stores an CloudWriter value of any type in the column;  implements the "put_to" operator: <<;  example:  table.col << "hello";

Easy Access Method

You can access data in your tables and columns by using the above functions to instantiate Database, Table, and Column objects, or you can do it the easy way.

The DatabaseTables function automatically defines classes and variables for the tables and columns in your selected database.  Here's how it works.

Suppose you have a database named employees.db.  Suppose there is a table in the database called employees.  The table contains columns named: LastName, FirstName, etc.

So, you do this:

Database db =  DatabaseTables("employees.db", "employees" );

Now, CloudWriter has defined a new object, employees, which contains members LastName and FirstName.  employees is a Table object and LastName and FirstName are Column objects, with all the properties of Table and Column objects.

John Elkins (john@vermontdatabase.com) 26 Oct 2002