The advent of MONO on the main grid has opened up the possibilities considerably by enhancing both the performance and memory available to LSL scripts. Whilst I have posted data storage scripts in the past, they were often difficult to use, especially for new LSL scripters so I have taken advantage of the new MONO capability's to combine my data storage scripts into a form that is functional, logical and accessible to scripters of all levels.
VK-DBMS is a Multi-Table database management system written for and in LSL, it has no dependencies on external databases and is totally embedded in the users script. The embedded code is called VK-DBMS-VM and will compile to LSL with 11920 bytes free and to MONO with 46324 Bytes free, the latter is the obvious choice for data storage but it still may prove useful as a applications environment in the former.
Although not fully optimised I have decided to release the code to the community at large in the hope that beginners will benefit from the relative ease of storing and retrieving data and that advanced scripters will improve on my work or indeed find new uses for it as my current beta testers have done.
Living Documentation is posted at:
CODE
//Very Keynes - 2008
//
// 2008-10-31, 22:24 GMT
// Release Candidate
//
// For usage information and examples please see
// http://docs.google.com/Doc?id=d79kx35_26df2pbbd8
//
//------------------Begin VK-dbms-VM----------------------------\\
//--------------------Introduction------------------------------\\
//
// Very Keynes - db - Virtual Machine
//
// Implements a core set of registers and root functions
// to create and manage multi-table database structures as
// an LSL list. Although intended to under pin higher level
// database management tools such as VK-SQL it is usable as
// a small footprint database facility for system level
// applications.
//
//
// Naming Conventions and Code Style
//
// This Code is intended to be included as a header to user generated
// code. As such it's naming convention was selected so that it would
// minimise the possibility of duplicate names in the user code portion
// of the application.
//
// Licencing
//
// The concept and code are hereby released to the public domain on an honer system.
// If expanded upon, or altered in any positive way, please post the update,
// else use it in any way you wish for non commercial applications. If used in a
// commercial application then please be mindful of the time spent to develop this system
// and contact the author about a distribution agreement :)
//
// Table Control Registers
//
integer th_; // Table Handle / Index Pointer
integer tc_; // Columns in Active Table
integer tr_; // Rows in Active Table
integer ts_; // Active Table Start Address
//
list _d_ = []; // Database File
list _i_ = [0]; // Index File
//
// Exposed Variables
//
integer dbIndex; // Active Row Table Pointer
list dbRow; // User Scratch List
string dbError; // System Error String
//
// Temporary / Working Variables
//
integer t_i;
string t_s;
float t_f;
list t_l;
//
// System Functions
//
// string dbCreate( TableName, ColumnList )
//
// Creates a new Table in the database
//
string dbCreate(string tab, list col)
{
if(dbOpen(tab)) // Integrity test
{
dbError = tab + " already exists";
return "";
}
// Create a new index record for the table
_i_ += [tab, tc_ = llGetListLength(col), 0, 0, 0];
th_= 0; // release current handle
dbOpen(tab); // Activate the new table
dbInsert(col); // insert the header record
return tab; // Return the alis
}
//
// Helper function returns Col# for Column Name
//
integer dbCol(string col){return llListFindList(dbGet(0), [_trm(col)]);}
//
// integer dbDelete( Row# )
//
// Deletes the specified Row of the Active Table
//
integer dbDelete(integer ptr)
{
if(ptr > 0 && ptr < tr_) // Bounds test
{
_d_ = llDeleteSubList(_d_, t_i = ts_ + tc_ * ptr, t_i + tc_ - 1);
--tr_;
return tr_ - 1;
}
else {
dbError = (string)ptr+" is outside the Table Bounds";
return FALSE;}
}
//
// integer dbDrop( TableName )
//
// Complement to dbCreate - Removes a Table and its data
//
integer dbDrop(string tab)
{
if(-1 != (t_i = llListFindList(_i_, [tab])))
{ dbOpen(tab);
_d_ = llDeleteSubList(_d_, ts_, ts_ + tc_ * tr_ - 1);
_i_ = llDeleteSubList(_i_, th_, th_ + 4);th_= 0;
return TRUE; }
else {dbError = tab + " : Table name not recognised"; return FALSE;}
}
//
// integer dbExists( condition list )
//
// searches from last row towards row 1 stopping and returning the first match
//
integer dbExists(list cnd)
{ for(dbIndex = tr_ ; --dbIndex > 0 ; ){if(dbTest(cnd))return dbIndex;}
return FALSE;}
//
// list dbGet( row# )
//
// returns the specified row
//
list dbGet(integer ptr)
{
if(ptr < tr_ && ptr >= 0)return llList2List(_d_, t_i = ts_ + tc_ * ptr, t_i + tc_ - 1);
else dbError = (string)ptr+" is outside the Table Bounds";return [];
}
//
// Internal function to calculate a tables start address
integer _idx(integer hdl){return (integer)llListStatistics(6, llList2ListStrided(_i_, 0, hdl, 5));}
//
// integer dbInsert( list Row Data )
//
// Creates a new row at the end of the table, returns rumber of rows
//
integer dbInsert(list val)
{
if(llGetListLength(val) == tc_){_d_ = llListInsertList(_d_, val, ts_ + tc_ * (dbIndex = tr_++));return dbIndex;}
else{dbError = "Insert Failed - too many or too few Columns specified"; return FALSE;}
}
//
// integer dbOpen ( Table Name )
//
// saves current registers and opens a table if it exists
// returns Number of rows in opened table
//
integer dbOpen(string tab)
{
if(th_)_i_ = llListReplaceList(_i_, [tr_, dbIndex, tc_ * tr_], th_ + 2, th_ + 4);
if(-1 == (t_i = llListFindList(_i_, [tab]))) //if tab does not exist abort
{dbError = tab + " : Table name not recognised"; return FALSE;}
else if(th_ != t_i){th_ = t_i++;ts_ = _idx(th_);
tc_ = llList2Integer(_i_, t_i++);
tr_ = llList2Integer(_i_, t_i++);
dbIndex = llList2Integer(_i_, t_i);}
;return tr_ - 1;
}
//
// integer dbPut( list Row Data )
//
// Replaces the currently Active Row of the Table with the new data
integer dbPut(list val){if(llGetListLength(val) == tc_)
{
_d_ = llListReplaceList(_d_, val, t_i = ts_ + tc_ * dbIndex, t_i + tc_ - 1); return dbIndex;}
else {dbError = "Update Failed - too many or too few Columns specified"; return FALSE;}
}
//
// integer dbTest( condition list )
//
// tests condition against current row, returns true or false
//
integer dbTest(list cnd)
{
if(llGetListEntryType(cnd,2) >= 3){
t_s = llList2String(dbGet(dbIndex), dbCol(llList2String(cnd, 0)));
if ("==" == llList2String(cnd, 1)){t_i = t_s == _trm(llList2String(cnd, 2));}
else if("!=" == llList2String(cnd, 1)){t_i = t_s != _trm(llList2String(cnd, 2));}
else if("~=" == llList2String(cnd, 1))
{t_i = !(llSubStringIndex(llToLower(t_s), llToLower(_trm(llList2String(cnd, 2)))));}}
else{
t_f = llList2Float(dbGet(dbIndex), dbCol(llList2String(cnd, 0)));
t_s = llList2String(cnd, 1);
if ("==" == t_s){t_i = t_f == llList2Float(cnd, 2);}
else if("!=" == t_s){t_i = t_f != llList2Float(cnd, 2);}
else if("<=" == t_s){t_i = t_f <= llList2Float(cnd, 2);}
else if(">=" == t_s){t_i = t_f >= llList2Float(cnd, 2);}
else if("<" == t_s){t_i = t_f < llList2Float(cnd, 2);}
else if(">" == t_s){t_i = t_f > llList2Float(cnd, 2);}}
if(t_i)return dbIndex; else return FALSE;
}
// Internal function encapsulates llStringTrim
string _trm(string val){return llStringTrim(val, STRING_TRIM);}
//
// dbTruncate ( table Name )
//
// removes all rows from a table but leaves the header intact
//
dbTruncate(string tab){dbIndex = dbOpen(tab);while(dbIndex > 0)dbDelete(dbIndex--);}
//
// dbSort ( integer Order )
//
// permanently sorts a table by the first column, ascending or descending
//
dbSort(integer dir){_d_ = llListReplaceList(_d_,llListSort(llList2List
(_d_, t_i = ts_ + tc_, t_i + tc_ * tr_ - 2), tc_, dir), t_i, t_i + tc_ * tr_ - 2);
}
//
// dbFn( Function, Column )
//
// Preforms Statistical functions on the Column Specified
//
float dbFn(string fn, string col)
{
t_l = llList2List(_d_, t_i = ts_ + tc_, t_i + tc_ * tr_ - 2);
if(dbCol(col) != 0) t_l = llDeleteSubList(t_l, 0, dbCol(col) - 1);
return llListStatistics(llSubStringIndex("ramimaavmedesusqcoge", llGetSubString(llToLower(fn),0,1)) / 2,
llList2ListStrided(t_l, 0, -1, tc_));
}
//
//--------------------------- End VK-dbms-VM ---------------------------\\
//
Summary of commands in alphabetical order:
string dbCreate ( string TableName, list ColumnNames );
integer dbDelete ( integer Row# );
integer dbDrop ( string TableName );
integer dbExists ( list Condition );
float dbFn ( string Function, string ColumnName );
list dbGet ( integer Row# );
integer dbInsert ( list Values );
integer dbOpen ( string TableName );
integer dbPut ( list Values );
void dbSort( integer Direction );
integer dbTest ( list condition );
integer dbTruncate ( string TableName );
Helpers:
integer dbCol( string ColumnName );
string dbError - Description of last Error encountered
integer dbIndex - Table Index pointer
list dbRow - user variable
;