Simple SQL Data Slice
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-03-2008 15:51
This is my latest attempt at usable storage within SL without the need for external servers, I will update this thread as I make changes to the script. In Light of all the recent discussions regarding using scripts as storage vs. external storage or a proper solution from LL. I have decided to release a copy of my work in progress. This is my proof of concept code and is not the intended end product, but I feel it has reached a stage where others may find it useful as much as I can benefit from feedback. This code is tested, but is not optimized. In addition it is using chat on channel 0 as the interface, this is purely for ease of testing in my personal skybox, I strongly suggest you set the channel to something else, or if you intend to interface from other scripts then implement Link Messages instead. In a nutshell it is the latest iteration of my data slice concept, previous generations of which were published in the Library and here in this forum. The main feedback I got on those earlier iterations was the complexity of the interface. For this version I decided to use a simplified SQL interface. As a result this version has a bit less spare capacity for the actual data storage, but provides some powerful data handling capability that may well offload more than just data storage from many scripts. Later versions will extend the language and the storage available but will use the basic concepts demonstrated here. This may well be one of the longest posts on the forum, so I will split it over 3 additional posts. The next post is a description of the Simplified SQL implementation, Followed by the code and finally a tutorial exercise for those who wish to test the script, the language and/or assess its capabilities. All feed back gratefully appreciated, no matter how harsh  as I am trying to hone my LSL skills whilst contributing to the community and we all learn by pointing out mistakes or improvements. I am also open to suggestions of language features / requests that I can try and include as the project evolves. |
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-03-2008 15:52
Simplified SQL Command structure
CREATE (FieldNames)
Creates a Table of n Columns defined by FieldNames
FileldNames is a comma Separated list of Field Names that define the data Table Structure
i.e. CREATE(City, Sales, Date)
Creates a Table with 3 column's named City, Sales and Date respectively. Note the first column is assumed to be the Primary key.
INSERT (ValueList)
Creates a record in the table, where ValueList is a list of values to be inserted in Column order. ValueList must contain a value for each column
i.e. INSERT Cape Town, 100, 1990
UPDATE (AssigmentList) WHERE Condition [INSERT (ValueList)]
Updates the specified fields of one or more records in the table, where the existing record(s) match the Condition specified and can optionally INSERT a record if the specified condition is not met. For a detailed list of Conditions see the WHERE Clause.
The Assignment list is a comma-separated list of Field / Value pairs delimited by the “=” sign, and the value portion may contain a single operator, valid operaters are + - * / and ^.
i.e. UPDATE Sales = Sales + 1, Date = 2008 WHERE City = Cape Town
or UPDATE Sales=200, Date=2008 WHERE City=Cape Town INSERT Cape Town,200,2008
the second example will create the record if it did not exist.
DELETE WHERE Condition
Delete one or more Records from the table where the condition is satisfied
i.e. DELETE WHERE City = Cape Town
SELECT (FieldList) [WHERE Condition]
Returns one or more values from one or more records where the condition is satisfied. If several Fields are specified the values are returned in a comma separated list in the order specified by FieldList. If No WHERE clause is specified the values from all rows are returned
i.e. SELECT Sales, City
Returns a list of sales by City with each on a new line.
If * is used in place of the FieldList all values for the matching record(s) are returned
i.e. SELECT * WHERE Sales >100
WHERE Condition
Condition may be any of the following numerical conditions
>, <, >=, <=
The following operate on String or Numeric values
=, ==, <>, !=
and the following Special Operators are available
[NOT] BETWEEN value1, value2
returns TRUE is the value falls between or is equal to the specified values
[NOT] IN item1, item2, item3, ...
returns TRUE if the value is in the list of values specified
LIKE mask
Returns true is the mask falls within the string being tested at the position specified by the mask
Valid masks are as follows:
mask returns TRUE if the mask exactly matches the string
mask% returns TRUE if the mask is at the beginning of the string
%mask returns TRUE if the mask is at the end of the string
%mask% returns TRUE if the string contains the mask
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-03-2008 15:55
// Indexed Sequential Access Method / Direct Access Storage Device
// SQL data slice - A subscript of the ISAM/DASD componant of VK-Net
// Very Keynes - February 2008 tested in MONO and LSL2
// version 1.2 - 080306
list sql;
list data;
list tmp2;
list match;
list fields;
string adr;
integer cols;
integer rows;
integer row; integer col;
integer x; integer y;
float tmpx; float tmpy;
integer condition(string arg1, string Op, string arg2)
{
if(Op=="LIKE")
{
if(llGetSubString(arg2,0,0)=="%"){arg2=llGetSubString(arg2,1,-1);tmpy=1;}
if(llGetSubString(arg2,llStringLength(arg2)-1,-1)=="%")
{arg2=llGetSubString(arg2,0,llStringLength(arg2)-2);tmpy=tmpy+2;}
if(-1!=(tmpx=llSubStringIndex(arg1,arg2)))
{
if((tmpy==0 && llStringLength(arg1)==llStringLength(arg2))
||(tmpy==1 && tmpx == (llStringLength(arg1)- llStringLength(arg2)))
||(tmpy==2 && tmpx == 0)
||(tmpy==3))
{return TRUE;}
}
return FALSE;
}
list lst=llCSV2List(arg2); integer num;
if(Op=="NOT BETWEEN")
{if(!((float)arg1 >= llList2Float(lst,0) && (float)arg1 <= llList2Float(lst,1)))return TRUE;}
else if(Op=="BETWEEN")
{if( (float)arg1 >= llList2Float(lst,0) && (float)arg1 <= llList2Float(lst,1)) return TRUE;}
else if(Op=="IN") {if(llListFindList(lst,[arg1])>=0) return TRUE;}
else if(Op=="NOT IN"){if(!(llListFindList(lst,[arg1])>=0))return TRUE;}
if(num=(isnum(arg1) & isnum(arg2))){tmpx=(float)arg1;tmpy=(float)arg2;}
if(Op=="<>" || Op=="!=") {if((num && tmpx != tmpy)||(arg1 != arg2))return TRUE;}
else if(Op=="=" || Op=="=="){if((num && tmpx == tmpy)||(arg1 == arg2))return TRUE;}
else if(Op=="<="){if(num && tmpx <= tmpy)return TRUE;}
else if(Op==">="){if(num && tmpx >= tmpy)return TRUE;}
else if(Op=="<") {if(num && tmpx < tmpy) return TRUE;}
else if(Op==">") {if(num && tmpx > tmpy) return TRUE;}
return FALSE;
}
list where(string val)
{
match=[];
list tmp=llParseString2List(val,[],["LIKE","BETWEEN","NOT BETWEEN","IN","NOT IN"]);
if(llGetListLength(tmp)<3)tmp=llParseString2List(val,[],["==","!=","<=",">=","<",">","<>","="]);
col = getcol(llList2String(tmp,0));
for(row=0;row<rows;row++)
{
if("ALL"==val)match = [row] + match;
else if(condition(llList2String(data,col+(row*cols)),llList2String(tmp,1),llStringTrim(llList2String(tmp,2),STRING_TRIM)))
{
match = [row] + match;
}
}
return match;
}
integer getcol(string field){return llListFindList(fields,[llStringTrim(field,STRING_TRIM)]);}
integer isnum(string arg)
{
for(x=0;x<llStringLength(arg);x++)
{if(!~llSubStringIndex("-0123456789.",llGetSubString(arg,x,x)))
{x=llStringLength(arg);arg="";}}if(arg=="")return FALSE; else return TRUE;
}
calc(integer x)
{
tmpx = llList2Float(data,getcol(llList2String(tmp2,1))+x*cols);
tmpy = (float)llList2String(tmp2,3);
if(llList2String(tmp2,2)=="+")tmp2=llListReplaceList(tmp2,[(string)(tmpx+tmpy)],1,-1);
else if(llList2String(tmp2,2)=="-")tmp2=llListReplaceList(tmp2,[(string)(tmpx-tmpy)],1,-1);
else if(llList2String(tmp2,2)=="*")tmp2=llListReplaceList(tmp2,[(string)(tmpx*tmpy)],1,-1);
else if(llList2String(tmp2,2)=="/")tmp2=llListReplaceList(tmp2,[(string)(tmpx/tmpy)],1,-1);
else if(llList2String(tmp2,2)=="^")tmp2=llListReplaceList(tmp2,[(string)(llPow(tmpx,tmpy))],1,-1);
}
default
{
state_entry()
{
llListen(0,"",NULL_KEY,"");
}
listen(integer channel, string name, key id, string message)
{
sql=llParseString2List(message,["(",")","WHERE"],["CREATE","INSERT","UPDATE","SELECT","DELETE","UPSERT"]);
//CREATE(City,Sales,Date)
if(llList2String(sql,0)=="CREATE")
{
fields=llCSV2List(llList2String(sql,1));
cols=llGetListLength(fields);
data=[];
rows=0;
// adr=llList2String(sql,1);
}
//INSERT(Cape Town, 100, 2007)
if(llList2String(sql,0)=="INSERT")
{
data=llCSV2List(llList2String(sql,1))+data;
rows++;
}
//UPDATE Sales = 200, Date = 2008 WHERE City = Cape Town INSERT (Cape Town, 200, 2008)
if(llList2String(sql,0)=="UPDATE")
{
match=where(llList2String(sql,2));
if([]==match && "" != llList2String(sql,4))
{
data=llCSV2List(llList2String(sql,4))+data;
rows++;
}
else
{
list tmp=llCSV2List(llList2String(sql,1));
for(y=llGetListLength(match)-1;y>=0;y--)
{
row = llList2Integer(match,y);
for(x=llGetListLength(tmp);x>0;x--)
{
tmp2=llParseString2List(llList2String(tmp,x-1),["="],["+","-","*","/","^"]);
if(llGetListLength(tmp2)>3)calc(row);
col = getcol(llList2String(tmp2,0));
data=llListReplaceList(data,[llStringTrim(llList2String(tmp2,1),STRING_TRIM)],col+(row*cols),col+(row*cols));
}
}
}
}
//SELECT City WHERE Sales < 500
if(llList2String(sql,0)=="SELECT")
{
list result;
if(llStringTrim(llList2String(sql,1),STRING_TRIM)=="*")
sql=llListReplaceList(sql,[llList2CSV(fields)],1,1);
if(llGetListLength(sql)>2)
match=where(llList2String(sql,2));
else match=where("ALL");
for(y=llGetListLength(match)-1;y>=0;y--)
{
row = llList2Integer(match,y);
list tmp=llCSV2List(llList2String(sql,1));
for(x=0;x<llGetListLength(tmp);x++)
{
col = getcol(llList2String(tmp,x));
result = result + [llList2String(data,col+(row*cols))];
}
llOwnerSay(llList2CSV(result));result=[];
}
}
//DELETE WHERE City = Cape Town
if(llList2String(sql,0)=="DELETE")
{
@Repeat;
if(where(llList2String(sql,2))!=[])
{
row=llList2Integer(match,0);
data=llDeleteSubList(data,row*cols,cols+(row*cols)-1);
rows--;jump Repeat;
}
}
}
//llOwnerSay((string)llGetFreeMemory());
}
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-03-2008 15:55
Example Tutorial Table and operations:
//Create a table and put in some data
CREATE City, Sales, Date
INSERT Cape Town, 100, 1990
INSERT Johanesburg, 300, 1990
INSERT Durban, 50, 1990
INSERT Port Elizabeth, 10, 1990
//Lets examine the table we created above
SELECT *
--> Port Elizabeth, 10, 1990
--> Durban, 50, 1990
--> Johanesburg, 300, 1990
--> Cape Town, 100, 1990
//Now update the sales and the date for Cape town
//and lets correct the spelling of Johannesburg
UPDATE Sales=200, Date = 2000 WHERE City = Cape Town
UPDATE City = Johannesburg WHERE City = Johanesburg
SELECT *
--> Port Elizabeth, 10, 1990
--> Durban, 50, 1990
--> Johannesburg, 300, 1990
--> Cape Town, 200, 2000
//Find which City’s are on target
SELECT City WHERE Sales > 100
--> Johannesburg
--> Cape Town
//Lets change the date for all records except Cape Town
UPDATE Date = 2008 WHERE Date == 1990
//and view the changes showing the date first
SELECT Date, City
--> 2008, Port Elisabeth
--> 2008, Durban
--> 2008, Johannesburg
--> 2000, Cape Town
//Find the City’s above and below target
SELECT * WHERE Sales NOT BETWEEN 50, 200
--> Port Elizabeth, 10, 2008
--> Johannesburg, 300, 2008
//Add a new City
INSERT Petermarizburg, 75, 2008
//Lets test the LIKE condition
SELECT City WHERE City LIKE %burg
--> Petermarizburg
--> Johannesburg
//If we have Information for a City but do not know if it exists in the Table
UPDATE Sales=150 WHERE City=Blumfontain INSERT Blumfontain,150,2008
SELECT *
--> Blumfontain, 150, 2008
--> Petermarizburg, 75, 2008
--> Port Elizabeth, 10, 1990
--> Durban, 50, 1990
--> Johannesburg, 300, 1990
--> Cape Town, 200, 2000
//Find the under performing City’s
SELECT * WHERE Sales <=50
--> Port Elizabeth, 10, 2008
--> Durban, 50, 2008
//And Remove them from the table
DELETE WHERE Sales <=50
SELECT *
--> Blumfontain, 150, 2008
--> Petermarizburg, 75, 2008
--> Johannesburg, 300, 2008
--> Cape Town, 200, 2000
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-04-2008 06:41
I have just updated the SQL Slice to include a function that is NOT a standard part of SQL, however, it is an often requested feature on SQL forums and will simplify the use of SQL with LSL scripts. Namely "UPSERT" as it is widely known. Every SQL implementation I have looked at has a different way of implementing this function, so I decided to stick close to the MySQL implementation of:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
However, as I have no UNIQUE index or PRIMARY KEY defined and the object of this exercise is to simplify SQL, to fit the platform, I have reversed the implementation to:
UPDATE (AssigmentList) WHERE Condition INSERT (ValueList)
The INSERT clause is optional and will be executed only if the WHERE Condition fails, otherwise a normal UPDATE is performed. The restrictions pertaining to INSERT still apply.
i.e. ValueList must contain a value for each column.
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-04-2008 13:13
A Second Update has been posted, adding simple arithmatic to the UPDATE command.
Valid operators are + - * / and ^.
To provide a more SL centric example, imagine a visitor tracker that scans every 5 min and says the following to a SQL slice defined as Name, Duration, Visits:
UPDATE Duration = Duration + 5 WHERE Name = Very Keynes INSERT Very Keynes, 0, 1
On its first pass it will create the record Very Keynes, 0, 1
Each subsequent pass it will add 5 to the duration, so after 3 scans the record reads:
Very Keynes, 15.000000, 1
Multiple assignments are also valid such as:
UPDATE Sales = Sales + 1, Data = 20080305 WHERE City = Cape Town
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-07-2008 13:27
getting closer to a realease version of the code here is an Embedded version allong with a scanner Script that uses it, as an example of the application of SQL in LSL. This version corrects a few bugs found in the earlyer version and supports both LinkMessage and chat on channel /10. It also provides greater flexability in that it recognises the llGetDate format and allows one column to be assigned to another in the UPDATE clause. The Example apllication will hopefully show what I mean. Embedded SQL First:
//SQL-Embedded Slice - Version 1
//Very Keynes - March 2008
//
list sql;
list fields;
list data;
list match;
list tmp1;
list tmp2;
//string adr;
integer cols;
integer rows;
integer row; integer col; //Table Index
integer x; integer y; //arithmatic
integer a; integer b; //loop control
//float tmpx; float tmpy;
integer CHAT=FALSE;key chatkey;
integer condition(string arg1, string Op, string arg2)
{
if(Op=="LIKE")
{
if(llGetSubString(arg2,0,0)=="%"){arg2=llGetSubString(arg2,1,-1);y=1;}
if(llGetSubString(arg2,llStringLength(arg2) - 1, -1)=="%")
{arg2=llGetSubString(arg2,0,llStringLength(arg2)- 2);y+=2;}
if(-1!=(x=llSubStringIndex(arg1,arg2)))
{
if((y==0 && llStringLength(arg1)==llStringLength(arg2))
||(y==1 && x == (llStringLength(arg1)- llStringLength(arg2)))
||(y==2 && x == 0)
||(y==3))
{return TRUE;}
}
return FALSE;
}
tmp1=llCSV2List(arg2); integer num;
if(Op=="NOT BETWEEN")
{if(!((integer)arg1 >= llList2Integer(tmp1,0) && (integer)arg1 <= llList2Integer(tmp1,1)))return TRUE;}
else if(Op=="BETWEEN")
{if( (integer)arg1 >= llList2Integer(tmp1,0) && (integer)arg1 <= llList2Integer(tmp1,1)) return TRUE;}
else if(Op=="IN") {if(llListFindList(tmp1,[arg1])>=0) return TRUE;}
else if(Op=="NOT IN"){if(!(llListFindList(tmp1,[arg1])>=0))return TRUE;}
if((num=(isnum(arg1)) & isnum(arg2))){x=(integer)arg1;y=(integer)arg2;}
if(Op=="<>" || Op=="!=") {if((num && x != y)||(arg1 != arg2))return TRUE;}
else if(Op=="=" || Op=="=="){if((num && x == y)||(arg1 == arg2))return TRUE;}
else if(Op=="<="){if(num && x <= y)return TRUE;}
else if(Op==">="){if(num && x >= y)return TRUE;}
else if(Op=="<") {if(num && x < y) return TRUE;}
else if(Op==">") {if(num && x > y) return TRUE;}
return FALSE;
}
list where(string val)
{
match=[];
tmp2=llParseString2List(val,[],["LIKE","BETWEEN","NOT BETWEEN","IN","NOT IN"]);
if(llGetListLength(tmp2)<3)tmp2=llParseString2List(val,[],["==","!=","<=",">=","<",">","<>","="]);
col = getcol(llList2String(tmp2,0));
for(row=0;row<rows;row++)
{
if("ALL"==val)match = [row] + match;
else if(condition(llList2String(data,col+(row*cols)),llList2String(tmp2,1),llStringTrim(llList2String(tmp2,2),STRING_TRIM)))
{
match = [row] + match;
}
}
return match;
}
integer getcol(string field){return llListFindList(fields,[llStringTrim(field,STRING_TRIM)]);}
integer isnum(string arg)
{
for(x = 0; x < llStringLength(arg); x++)
{
if(!~llSubStringIndex("-0123456789.",llGetSubString(arg,x,x)))
{x=llStringLength(arg);arg="";}
}
if(arg=="")return FALSE; else return TRUE;
}
integer isdate(string arg)//YYYY-MM-DD
{
// llOwnerSay(arg+(string)llStringLength(arg)+(string)llSubStringIndex(arg,"-"));
if(llStringLength(arg)==10 && llSubStringIndex(arg,"-")==4 && llGetSubString(arg,7,7)=="-")
return TRUE; else return FALSE;
}
calc()
{
x = llList2Integer(data,getcol(llList2String(tmp2,1))+row*cols);
y = (integer)llList2String(tmp2,3);
if(llList2String(tmp2,2)=="+")tmp2=llListReplaceList(tmp2,[(string)(x+y)],1,-1);
else if(llList2String(tmp2,2)=="-")tmp2=llListReplaceList(tmp2,[(string)(x-y)],1,-1);
else if(llList2String(tmp2,2)=="*")tmp2=llListReplaceList(tmp2,[(string)(x*y)],1,-1);
else if(llList2String(tmp2,2)=="/")tmp2=llListReplaceList(tmp2,[(string)(x/y)],1,-1);
else if(llList2String(tmp2,2)=="^")tmp2=llListReplaceList(tmp2,[(string)(llPow(x,y))],1,-1);
}
SQLExec(string SQLcmd)
{
sql=llParseString2List(SQLcmd,["(",")","WHERE"],["CREATE","INSERT","UPDATE","SELECT","DELETE","UPSERT"]);
//CREATE(City,Sales,Date)
if(llList2String(sql,0)=="CREATE")
{
fields = llCSV2List(llList2String(sql,1));
cols = llGetListLength(fields);
data = [];
rows = 0;
// adr=llList2String(sql,1);
}
//INSERT(Cape Town, 100, 2007)
else if(llList2String(sql,0)=="INSERT")
{
data=llCSV2List(llList2String(sql,1))+data;
rows++;
llOwnerSay(llList2CSV(data));
}
//UPDATE Sales = 200, Date = 2008 WHERE City = Cape Town INSERT (Cape Town, 200, 2008)
else if(llList2String(sql,0)=="UPDATE")
{
match=where(llList2String(sql,2));
if([] == match && "" != llList2String(sql,4))
{
data = llCSV2List(llList2String(sql,4)) + data;
rows++;
}
else
{
tmp1 = llCSV2List(llList2String(sql,1));
for(a =llGetListLength(match) - 1; a >= 0; a--)
{
row = llList2Integer(match,a);
for(b = llGetListLength(tmp1); b > 0; b--)
{
tmp2 = llParseString2List(llList2String(tmp1, b - 1),["="],[]);
if(!(isdate(llStringTrim(llList2String(tmp2,1),STRING_TRIM))))
{
if(-1 == (col=getcol(llList2String(tmp2,1))))
{
tmp2 = llParseString2List(llList2String(tmp1, b - 1),["="],["+","-","*","/","^"]);
if(llGetListLength(tmp2) == 4)calc();
}
else
{
tmp2=llListReplaceList(tmp2,llList2List(data,col+(row*cols),col+(row*cols)),1,-1);
}
}
col = getcol(llList2String(tmp2,0));
data=llListReplaceList(data,[llStringTrim(llList2String(tmp2,1),STRING_TRIM)],col+(row*cols),col+(row*cols));
}
}
}
}
//SELECT City WHERE Sales < 500
else if(llList2String(sql,0)=="SELECT")
{
list result;
if(llStringTrim(llList2String(sql,1),STRING_TRIM)=="*")
sql=llListReplaceList(sql,[llList2CSV(fields)],1,1);
if(llGetListLength(sql)>2)
match=where(llList2String(sql,2));
else match=where("ALL");
if(CHAT){llInstantMessage(chatkey,llList2CSV(llList2List(sql,1,1)));}
for(a = llGetListLength(match)- 1; a >= 0; a--)
{
row = llList2Integer(match,a);
list tmp=llCSV2List(llList2String(sql,1));
for(b = 0; b < llGetListLength(tmp); b++)
{
col = getcol(llList2String(tmp,b));
result = result + [llList2String(data,col+(row*cols))];
}
if(CHAT){llInstantMessage(chatkey,llList2CSV(result));}
else llMessageLinked(LINK_THIS,0,llList2CSV(result),"sql");
result=[];
}
CHAT=FALSE;
}
//DELETE WHERE City = Cape Town
else if(llList2String(sql,0)=="DELETE")
{
@Repeat;
if(where(llList2String(sql,2))!=[])
{
row=llList2Integer(match,0);
data=llDeleteSubList(data,row*cols,cols+(row*cols)- 1);
rows--;jump Repeat;
}
}
}
default
{
state_entry()
{
llListen(10,"",NULL_KEY,"");
}
listen(integer channel, string name, key id, string message)
{
if("sqldump"==message)llOwnerSay(llList2CSV(data));
if("sqlmem"==message)llOwnerSay((string)llGetFreeMemory());
else
{
CHAT=TRUE; chatkey = id;
llMessageLinked(LINK_THIS,0,message,"sql>");
}
}
link_message(integer sender_number, integer number, string message, key id)
{
if((string)id=="sql>")SQLExec(message);
}
}
and the Scanner code, to be placed in the same prim.
//Guest List - SQL Based Visitor Tracker
//Very Keynes -March 2008 Version 1.0
//
integer DEBUG = FALSE;
float Range = 35.0;
//
list LastScan;
list ThisScan;
list tmp;
list ListXandY(list lx, list ly) // return a list of elements common to both lists
{
list lz = []; integer x;
for (x = 0; x < llGetListLength(ly); x++)
{
if (~llListFindList(lx,llList2List(ly,x,x))){lz = lz + llList2List(ly,x,x);}
else {;}
}
return lz;
}
list ListXnotY(list lx, list ly) // return elements in X that are not in Y
{
list lz = []; integer x;
for (x = 0; x < llGetListLength(lx); x++)
{
if (~llListFindList(ly,llList2List(lx,x,x))){;}
else {lz = lz + llList2List(lx,x,x);}
}
return lz;
}
ExecSQL(string Statement){llMessageLinked(LINK_THIS,0,Statement,"sql>");}
default
{
state_entry()
{
//ExecSQL("CREATE Name,Date1st,DateLast,TimeLast,here,TimeNow,Visits,TimeTotal");
llSensorRepeat("",NULL_KEY,AGENT,Range,PI,60.0);
}
touch_start(integer total_number)
{
if(llDetectedKey(0)==llGetOwnerKey(llGetKey()))DEBUG=!DEBUG;
if(DEBUG)llInstantMessage(llGetOwnerKey(llGetKey()),"Debug is on");
}
sensor(integer total_number) // total_number is the number of avatars detected.
{
integer i; LastScan = ThisScan; ThisScan = []; string name;
for (i = 0; i < total_number; i++){ThisScan = llDetectedName(i) + ThisScan;}
tmp = ListXnotY(ThisScan, LastScan); //arrivals
if(DEBUG && [] != tmp)llInstantMessage(llGetOwnerKey(llGetKey()),"Arrivals ="+llList2CSV(tmp));
for (i = 0; i < llGetListLength(tmp); i++)
{
name=llList2String(tmp,i);
ExecSQL(
"UPDATE TimeNow = 1, here = 1, Visits = Visits + 1,TimeTotal = TimeTotal + 1,"+
"DateLast = "+llGetDate()+ "WHERE Name =" + name +
" INSERT "+ name +","+llGetDate()+","+llGetDate()+",0,1,1,1,1");
}
tmp = ListXandY(LastScan, ThisScan); //Current
if(DEBUG && [] != tmp)llInstantMessage(llGetOwnerKey(llGetKey()),"Current ="+llList2CSV(tmp));
for (i = 0; i < llGetListLength(tmp); i++)
{
name=llList2String(tmp,i);
ExecSQL("UPDATE TimeNow = TimeNow + 1, TimeTotal = TimeTotal + 1 WHERE Name=" + name);
}
tmp = ListXnotY(LastScan, ThisScan); //departures
if(DEBUG && [] != tmp)llInstantMessage(llGetOwnerKey(llGetKey()),"Departures ="+llList2CSV(tmp));
for (i = 0; i < llGetListLength(tmp); i++)
{
name=llList2String(tmp,i);
ExecSQL("UPDATE TimeLast = TimeNow, here = 0 WHERE Name=" + name);
}
}
no_sensor()
{
integer i;
for (i = 0; i < llGetListLength(LastScan); i++)//last person turn off the lights
{
if(DEBUG && [] != LastScan)llInstantMessage(llGetOwnerKey(llGetKey()),"No one Present");
ExecSQL("UPDATE TimeLast = TimeNow, here = 0 WHERE Name="+llList2String(LastScan,i));
}
LastScan=[];
}
}
|
|
Very Keynes
LSL is a Virus
Join date: 6 May 2006
Posts: 484
|
03-07-2008 13:42
A few notes on the above:
I have left the DEBUG code in the script so that you can see what is happening at run time. To activate DEBUG, touch the prim, to deactivate touch it a second time.
The Scanner is an example of an input device, it has no inherent reporting capability. This is intentional as it shows the power on the SQL slice to offload the reporting to the user or a second device.
To Query the database use /10 and your query.
Example Queries:
/10 SELECT * WHERE Name LIKE %Very%
//will find any visitor with Very as part of the name
/10 SELECT Name, DateLast WHERE TimeLast >60
// Will list the name and date of last visit of any visitor who spent than one hour there.
/10 SELECT Name, TimeTotal WHERE DateLast == 2008-03-07
//will list the name and the total time for all visits of any one who visited on the 7th of this month.
Remember this is a test script and a proof of concept, it has no error trapping and not a lot of storage capability, it is, however great for low trafic venues like Private houses, and you can always free memory by saying somthing like:
/10 DELETE WHERE TimeTotal < 10 to get rid of casual visitors or flush the whole table with /10 DELETE WHERE DateLast LIKE 2% to delete anyone who visited this Century.
I also forgot to mention 2 basic DEBUG commands:
/10 sqldump will display the entier table as a single CSV string
/10 sqlmem will dispaly an approximation of the free memory
if your script stops responding on /10 reset the object, it is out of memory.
|