Library: Storage Cube

As we have had a resurgence of interest in data storage, Databases in particular, in recent weeks, I thought I would offer a storage cube based on my vk-dbms script.

so what is a Storage Cube?

It is a simple, though not simplistic, data storage device that acts as a small, multi user, multi table database to store and retrieve information, and uses chat channels to interact with users or other objects.

to try it out:
Create a cube, or any other shape for that matter.

Set the object description to show the default operating Channels, in the format, NetCh, PubCh, OutCh.
Where:
NetCh is the network channel your Objects will talk on.
PubCh is the Channel that Avatars will talk on.
OutCh is The channel used in response to a command on the PubCh.
example: -123, 99, 0
will listen for commands from other objects on -123 and will reply on -123
will listen for commands from Avatars or objects on /99 and will respond in open Chat.
NOTE: if either NetCh or PubCh are set to 0, no listen will be created for that channel.

Drop this script into the cube (or object).

The forums are refusing to accept code from me, yet again, so please use this link to get the code. I will update here later if I can.
http://docs.google.com/Doc?id=d79kx35_94f6c2sndd



The Following commands are supported:

new( table, column list )
Example: /99 New(test, col1, col2, col3)
will create a table called "test" with 3 columns called "col1", "col2" and "col3" respectively.
You may create as many tables as you wish to in a single cube so one cube can store data for several objects.

ins( table, data list )
Example: //ins(test, a,b,c)
will inset the values a, b and c into columns "col1", "col2" and "col3" respectively.

find(table, condition list)
Example: find(test, col1, ==, a)
will find and return the row number of the first row that contains "a" as the value of "col1".

get( table[, row#])
Will return a CSV list of the data in the specified Row.
Example: get(test, 1) will return "a, b, c"
if no row number is specified it returns all data including the column names one row at a time.

del( table[, row#] )
Example //del(test, 1)
will delete row 1 from the table.
Note: If the Row Number is omitted this command deletes the entire table.

put( table, row#, data list)
will replace the contents of the specified row with the data
Example: put(test, 1, A, B, C)
will replace Row 1.

cat
will dump the Index List to the Output in the format:
start, table name, cols, rows, length
and is provided for information only, the index may not be directly manipulated.

for more extensive documentation see:
http://docs.google.com/Doc?id=d79kx35_26df2pbbd8

Part 1 of 2

//Very Keynes - 2006 - 2009
//__________________________________________________________________
//
//             Version control header
//__________________________________________________________________
//
string product = "Starage Cube";
float version  = 1.00;
string date    = "2009/03/10";
//________________________________________________________________________________________________
//
//    Starage Cube is a simple, though not simplistic, data storage
//	  device that acts as a small, multi table database to store and
// 	  retrieve information, and uses chat channels to interact with users or other objects.
//__________________________________________________________________
//
//------------------Begin VK-DBMS-VM----------------------------\\
//--------------------Introduction------------------------------\\
//
// Version: OpenSimulator Server  0.6.1.7935  (interface version 2)
//
// 2009-01-06, 19:30 GMT
//
// Very Keynes - DBMS - 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. Exposed Functions and Variables are prefixed db.
//
//      A full User Guide and Tutorial is availible at this URL:
//
//          http://docs.google.com/Doc?id=d79kx35_26df2pbbd8
//
//
//    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(string tab, list col)
{
    if(dbOpen(tab))
    {
        dbError = tab + " already exists";
        return "";
    }
    tc_ = llGetListLength(col);
    _i_ += [tab, tc_, 0, 0, 0];
    th_= 0;
    dbOpen(tab);
    dbInsert(col);
    return tab;
}

integer dbCol(string  col)
{
    return llListFindList(dbGet(0), [_trm(col)]);
}

integer dbDelete(integer ptr)
{
    if(ptr > 0 && ptr < tr_)
    {
        t_i = ts_ + tc_ * ptr;
        _d_ = llDeleteSubList(_d_, t_i, t_i + tc_ - 1);
        --tr_;
        return tr_ - 1;
    }
    else
    {
        dbError = (string)ptr + " is outside the Table Bounds";
        return FALSE;
    }
}

integer dbDrop(string tab)
{
    t_i = llListFindList(_i_, [tab]);
    if(-1 != t_i)
    {
        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(list cnd)
{
    for(dbIndex = tr_ - 1 ; dbIndex > 0 ; --dbIndex)
    {
        if(dbTest(cnd)) return dbIndex;
    }
    return FALSE;
}

list dbGet(integer ptr)
{
    if(ptr < tr_ && ptr >= 0)
    {
        t_i = ts_ + tc_ * ptr;
        return llList2List(_d_, t_i, t_i + tc_ - 1);
    }
    else
    {
        dbError = (string) ptr + " is outside the Table Bounds";
        return [];
    }
}

integer _idx(integer hdl)
{
    return (integer)llListStatistics(6, llList2ListStrided(_i_, 0, hdl, 5));
}

integer dbInsert(list val)
{
    if(llGetListLength(val) == tc_)
    {
        dbIndex = tr_++;
        _d_ = llListInsertList(_d_, val, ts_ + tc_ * dbIndex);
        return dbIndex;
    }
    else
    {
        dbError = "Insert Failed - too many or too few Columns specified";
        return FALSE;
    }
}

integer dbOpen(string tab)
{
    if(th_)
    {
        _i_ = llListReplaceList(_i_, [tr_, dbIndex, tc_ * tr_], th_ + 2, th_ + 4);
    }
    t_i = llListFindList(_i_, [tab]);
    if(-1 == t_i)    //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 val)
{
    if(llGetListLength(val) == tc_)
    {
        t_i = ts_ + tc_ * dbIndex;
        _d_ = llListReplaceList(_d_, val, t_i, t_i + tc_ - 1);
        return dbIndex;
    }
    else
    {
        dbError = "Update Failed - too many or too few Columns specified";
        return 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;
}

string _trm(string val)
{
    return llStringTrim(val, STRING_TRIM);
}

dbTruncate(string tab)
{
    dbIndex = dbOpen(tab);
    while(dbIndex > 0) dbDelete(dbIndex--);
}

dbSort(integer dir)
{
    t_i = ts_ + tc_;
    _d_ = llListReplaceList(_d_, llListSort(llList2List(_d_, t_i, t_i + tc_ * tr_ - 2), tc_, dir), t_i, t_i + tc_ * tr_ - 2);
}

float dbFn(string fn, string col)
{
    t_i = ts_ + tc_;
    t_l = llList2List(_d_, t_i, 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 ---------------------------\\
//

part 2 of 2

integer PubCh;
integer NetCh;
integer OutCh;

default
{
	state_entry()
	{
		dbRow = llCSV2List(llGetObjectDesc());
		NetCh = llList2Integer(dbRow, 0);
		PubCh = llList2Integer(dbRow, 1);
		OutCh = llList2Integer(dbRow, 2);
		if(PubCh)llListen(PubCh,"","","");
		if(NetCh)llListen(NetCh,"","","");
		if(!PubCh && !NetCh)llOwnerSay("no Valid Listens set, Please Set the description Field\n"
			+"Format: Network Channel, Public Channel, Output Channel\n"
			+"Example:\n"
			+"-1234, 99, 0");
	}

	listen(integer CurCh, string cmd, key id, string data)
	{
		if(CurCh == PubCh)CurCh = OutCh;
		dbRow = llParseString2List(data, ["(", ")"], []);
		cmd = llToLower(llGetSubString(llList2String(dbRow, 0), 0, 2));
		dbRow = llCSV2List(llList2String(dbRow, 1));
		//new( table, column list )
		if("new" == cmd)
		{
			llSay(CurCh, dbCreate(llList2String(dbRow, 0), llList2List(dbRow, 1, -1)));
		}
		//del( table, row# )
		else if("del" == cmd)
		{
			if(llGetListLength(dbRow) == 1)
			{
				llSay(CurCh, (string)dbDrop(llList2String(dbRow, 0)));
			}
			else
			{
				dbOpen(llList2String(dbRow, 0));
				llSay(CurCh, (string)dbDelete(llList2Integer(dbRow, 1)));
			}
		}
		//find(table, condition list)
		else if("fin" == cmd)
		{
			dbOpen(llList2String(dbRow, 0));
			llSay(CurCh, (string)dbExists(llList2List(dbRow, 1, -1)));
		}
		//get( table, row#)
		else if("get" == cmd)
		{
			if(llGetListLength(dbRow) == 1)
			{
				integer x = dbOpen(llList2String(dbRow, 0));
				dbIndex = -1;
				while(dbIndex++ < x)llSay(CurCh, llList2CSV(dbGet(dbIndex)));
			}
			else
			{
				dbOpen(llList2String(dbRow, 0));
				llSay(CurCh, llList2CSV(dbGet(llList2Integer(dbRow, 1))));
			}
		}
		//ins( table, data list )
		else if("ins" == cmd)
		{
			dbOpen(llList2String(dbRow, 0));
			llSay(CurCh, (string)dbInsert(llList2List(dbRow, 1, -1)));
		}
		//put( table, row#, data list)
		else if("put" == cmd)
		{
			dbOpen(llList2String(dbRow, 0));
			dbIndex = llList2Integer(dbRow, 1);
			llSay(CurCh, (string)dbPut(llList2List(dbRow, 2, -1)));
		}
		//cat
		else if("cat" == cmd)
		{
			llSay(CurCh, llList2CSV(_i_));
		}
	}
}

please concatenate the 2 parts into one script, or just use the copy posted to Google Docs, linked in the first post. That one will be the most up to date if I make any changes.

dude! What is it with you and these huge long awesome data scripts? LOL I'm still trying to figure out vk-dbms

LOL, I guess I just have this thing about systems programming rather than applications
The main intent to this one is just to provide an idea of implementation and a ready made database for those who need a quick fix. I had a need for some shared data between a few objects, that I wanted to test quickly, without adding the dbms system and network code to each, and the ability to query it in chat. This was the result so I thought I would share it.

Now the question I must ask is..at the very end of the script did you use (_i_) on purpose? LOL

Yes, _I_ is the table Index list, Dumping it to CSV, is a good way to see how many tables the database contains and how many columns and rows each table has. It is a bit cryptic as it is formatted for script rather than human use, but it gives a good indication of capacity without using much memory.

lol I meant the text drawing it makes...back to actual business, this is a great little tool! I just tested it with a "real" table, a simple members table with 3 cols (avname, avkey, joindate) and set another script up to loop the ins command to populate the table and it took 125 records before it hit the stack heap collision..so wow, that's awesome!

Okay, is the NetCh there to be used by the application? or is it being used somewhere in the Storage prim here that I'm not seeing?

It allows other objects to talk to it and receive a reply on the same channel.
Say the NetCh is -1234 then another application could store data by llSay(-1234, "ins(guests, " + lldDetectedName(0)) whilst another could read it by using:
llListen(-1234,"","","";
llSay(-1234, "Get(guests)"

yes, I figured that out after playing with it a bit. I really like the storage bin layer..it helped give me a better understanding of the dbms itself, and how to apply it.