About

The progressive release of Corrade has built-in support for SQLite databases such that each configured group can manipulate their own database. Corrade allows for scripts (such as LSL scripts, or out-of-world scripts written in other languages) to directly send SQL commands that will run against a database configured for the calling group.

The template presented here makes use of the database command, given database permissions, in order to create a table in which visitors on a region will be recorded and queried.

Marketplace Item

Requirements

  • database permission granted for a group.

Setup

The script uses a notecard called configuration that is stored in the same primitive as the script.

File: http://svn.grimore.org/corrade-lsl-templates/source/visitor-track-and-record/configuration.txt -

############################# START CONFIGURATION ############################
 
# This is a configuration notecard based on the key-value data Wizardry and Steamworks reader. 
# Everything following the "#" character is ignored along with blank lines. Values can be set for various 
# parameters in a simple and understandable format with the following syntax: 
# KEY = "VALUE" 
# Every time you change this configuration notecard, the script will reset itself and the new configuration 
# will be read from this notecard.
 
# The "corrade", "group" and "password" settings must correspond to your settings in Corrade.ini
 
# This is the UUID of the Corrade bot.
corrade = "68505484-0699-4bc4-8f61-d74cd0c1146c"
 
# The name of the group - it can also be the UUID of the group.
group = "My Group"
 
# The password for the group.
password = "mypassword"
 
############################# END CONFIGURATION #############################

The configuration notecard will have to be changed appropriately to reflect the settings of the Corrade scripted agent.

Code

File: http://svn.grimore.org/corrade-lsl-templates/source/visitor-track-and-record/visitor-track-and-record.lsl -

///////////////////////////////////////////////////////////////////////////
//  Copyright (C) Wizardry and Steamworks 2014 - License: CC BY 2.0      //
///////////////////////////////////////////////////////////////////////////
 
///////////////////////////////////////////////////////////////////////////
//    Copyright (C) 2015 Wizardry and Steamworks - License: CC BY 2.0    //
///////////////////////////////////////////////////////////////////////////
string wasKeyValueGet(string k, string data) {
    if(llStringLength(data) == 0) return "";
    if(llStringLength(k) == 0) return "";
    list a = llParseStringKeepNulls(data, ["&", "="], []);
    integer i = llListFindList(llList2ListStrided(a, 0, -1, 2), [ k ]);
    if(i != -1) return llList2String(a, 2*i+1);
    return "";
}
 
///////////////////////////////////////////////////////////////////////////
//    Copyright (C) 2013 Wizardry and Steamworks - License: GNU GPLv3    //
///////////////////////////////////////////////////////////////////////////
string wasKeyValueEncode(list data) {
    list k = llList2ListStrided(data, 0, -1, 2);
    list v = llList2ListStrided(llDeleteSubList(data, 0, 0), 0, -1, 2);
    data = [];
    do {
        data += llList2String(k, 0) + "=" + llList2String(v, 0);
        k = llDeleteSubList(k, 0, 0);
        v = llDeleteSubList(v, 0, 0);
    } while(llGetListLength(k) != 0);
    return llDumpList2String(data, "&");
}
 
///////////////////////////////////////////////////////////////////////////
//    Copyright (C) 2015 Wizardry and Steamworks - License: GNU GPLv3    //
///////////////////////////////////////////////////////////////////////////
// escapes a string in conformance with RFC1738
string wasURLEscape(string i) {
    string o = "";
    do {
        string c = llGetSubString(i, 0, 0);
        i = llDeleteSubString(i, 0, 0);
        if(c == "") jump continue;
        if(c == " ") {
            o += "+";
            jump continue;
        }
        if(c == "\n") {
            o += "%0D" + llEscapeURL(c);
            jump continue;
        }
        o += llEscapeURL(c);
@continue;
    } while(i != "");
    return o;
}
 
///////////////////////////////////////////////////////////////////////////
//    Copyright (C) 2015 Wizardry and Steamworks - License: GNU GPLv3    //
///////////////////////////////////////////////////////////////////////////
// unescapes a string in conformance with RFC1738
string wasURLUnescape(string i) {
    return llUnescapeURL(
        llDumpList2String(
            llParseString2List(
                llDumpList2String(
                    llParseString2List(
                        i, 
                        ["+"], 
                        []
                    ), 
                    " "
                ), 
                ["%0D%0A"], 
                []
            ), 
            "\n"
        )
    );
}
 
///////////////////////////////////////////////////////////////////////////
//    Copyright (C) 2015 Wizardry and Steamworks - License: GNU GPLv3    //
///////////////////////////////////////////////////////////////////////////
list wasCSVToList(string csv) {
    list l = [];
    list s = [];
    string m = "";
    do {
        string a = llGetSubString(csv, 0, 0);
        csv = llDeleteSubString(csv, 0, 0);
        if(a == ",") {
            if(llList2String(s, -1) != "\"") {
                l += m;
                m = "";
                jump continue;
            }
            m += a;
            jump continue;
        }
        if(a == "\"" && llGetSubString(csv, 0, 0) == a) {
            m += a;
            csv = llDeleteSubString(csv, 0, 0);
            jump continue;
        }
        if(a == "\"") {
            if(llList2String(s, -1) != a) {
                s += a;
                jump continue;
            }
            s = llDeleteSubList(s, -1, -1);
            jump continue;
        }
        m += a;
@continue;
    } while(csv != "");
    // postcondition: length(s) = 0
    return l + m;
}
 
 
 
///////////////////////////////////////////////////////////////////////////
//    Copyright (C) 2015 Wizardry and Steamworks - License: CC BY 2.0    //
///////////////////////////////////////////////////////////////////////////
string wasListToCSV(list l) {
    list v = [];
    do {
        string a = llDumpList2String(
            llParseStringKeepNulls(
                llList2String(
                    l, 
                    0
                ), 
                ["\""], 
                []
            ),
            "\"\""
        );
        if(llParseStringKeepNulls(
            a, 
            [" ", ",", "\n", "\""], []
            ) != 
            (list) a
        ) a = "\"" + a + "\"";
        v += a;
        l = llDeleteSubList(l, 0, 0);
    } while(l != []);
    return llDumpList2String(v, ",");
}
 
///////////////////////////////////////////////////////////////////////////
//                  SQL STATEMENT DEFINITIONS                            //
///////////////////////////////////////////////////////////////////////////
 
string SQL_INSERT_VISITOR = "INSERT OR REPLACE INTO visitors (firstname, lastname, lastseen, time, memory) VALUES(:firstname, :lastname, :time, COALESCE((SELECT time FROM visitors WHERE firstname=:firstname AND lastname=:lastname), 0) + 1, :memory)";
string SQL_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS visitors ('firstname' TEXT NOT NULL, 'lastname' TEXT NOT NULL, 'lastseen' TEXT NOT NULL, 'time' INTEGER NOT NULL, 'memory' INTEGER NOT NULL, PRIMARY KEY ('firstname', 'lastname'))";
string SQL_COUNT_VISITORS = "SELECT COUNT(*) AS 'Visits', AVG(time) AS 'Time', AVG(memory) AS 'Memory' FROM visitors";
string SQL_SELECT_VISITOR = "SELECT * FROM visitors ORDER BY lastseen DESC LIMIT 1 OFFSET :offset";
string SQL_DROP_TABLE = "DROP TABLE IF EXISTS visitors";
 
///////////////////////////////////////////////////////////////////////////
 
// corrade data
key CORRADE = NULL_KEY;
string GROUP = "";
string PASSWORD = "";
 
// for holding the callback URL
string callback = "";
 
// for notecard reading
integer line = 0;
 
// key-value data will be read into this list
list tuples = [];
list agents = [];
string agentName = "";
 
 
default {
    state_entry() {
        if(llGetInventoryType("configuration") != INVENTORY_NOTECARD) {
            llOwnerSay("Sorry, could not find a configuration inventory notecard.");
            return;
        }
        // DEBUG
        llOwnerSay("Reading configuration file...");
        llGetNotecardLine("configuration", line);
    }
    dataserver(key id, string data) {
        if(data == EOF) {
            // invariant, length(tuples) % 2 == 0
            if(llGetListLength(tuples) % 2 != 0) {
                llOwnerSay("Error in configuration notecard.");
                return;
            }
            CORRADE = llList2Key(
                tuples,
                llListFindList(
                    tuples, 
                    [
                        "corrade"
                    ]
                )
                +1
            );
            if(CORRADE == NULL_KEY) {
                llOwnerSay("Error in configuration notecard: corrade");
                return;
            }
            GROUP = llList2String(
                tuples,
                llListFindList(
                    tuples, 
                    [
                        "group"
                    ]
                )
                +1
            );
            if(GROUP == "") {
                llOwnerSay("Error in configuration notecard: group");
                return;
            }
            PASSWORD = llList2String(
                tuples,
                llListFindList(
                    tuples, 
                    [
                        "password"
                    ]
                )
                +1
            );
            if(PASSWORD == "") {
                llOwnerSay("Error in configuration notecard: password");
                return;
            }
            // DEBUG
            llOwnerSay("Read configuration notecard...");
            tuples = [];
            state url;
        }
        if(data == "") jump continue;
        integer i = llSubStringIndex(data, "#");
        if(i != -1) data = llDeleteSubString(data, i, -1);
        list o = llParseString2List(data, ["="], []);
        // get rid of starting and ending quotes
        string k = llDumpList2String(
            llParseString2List(
                llStringTrim(
                    llList2String(
                        o, 
                        0
                    ), 
                STRING_TRIM), 
            ["\""], []
        ), "\"");
        string v = llDumpList2String(
            llParseString2List(
                llStringTrim(
                    llList2String(
                        o, 
                        1
                    ), 
                STRING_TRIM), 
            ["\""], []
        ), "\"");
        if(k == "" || v == "") jump continue;
        tuples += k;
        tuples += v;
@continue;
        llGetNotecardLine("configuration", ++line);
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
}
 
state url {
    state_entry() {
        // DEBUG
        llOwnerSay("Requesting URL...");
        llRequestURL();
    }
    http_request(key id, string method, string body) {
        if(method != URL_REQUEST_GRANTED) return;
        callback = body;
        // DEBUG
        llOwnerSay("Got URL...");
        state detect;
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
}
 
state detect {
    state_entry() {
        // DEBUG
        llOwnerSay("Detecting if Corrade is online...");
        llSetTimerEvent(5);
    }
    timer() {
        llRequestAgentData(CORRADE, DATA_ONLINE);
    }
    dataserver(key id, string data) {
        if(data != "1") {
            // DEBUG
            llOwnerSay("Corrade is not online, sleeping...");
            llSetTimerEvent(30);
            return;
        }
        state initialize;
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
}
 
state initialize {
    state_entry() {
        // DEBUG
        llOwnerSay("Creating the database if it does not exist...");
        llInstantMessage(
            CORRADE, 
            wasKeyValueEncode(
                [
                    "command", "database",
                    "group", wasURLEscape(GROUP),
                    "password", wasURLEscape(PASSWORD),
                    "SQL", wasURLEscape(SQL_CREATE_TABLE),
                    "callback", wasURLEscape(callback)
                ]
            )
        );
        // alarm 60
        llSetTimerEvent(60);
    }
    timer() {
        // DEBUG
        llOwnerSay("Timeout creating table...");
        llResetScript();
    }
    http_request(key id, string method, string body) {
        llHTTPResponse(id, 200, "OK");
        if(wasKeyValueGet("command", body) == "database") {
            integer success = wasKeyValueGet("success", body) == "True";
            if(!success) {
                // DEBUG
                llOwnerSay("Failed to create the table: " + 
                    wasURLUnescape(
                        wasKeyValueGet(
                            "error", 
                            body
                        )
                    )
                );
                return;
            }
 
            // DEBUG
            llOwnerSay("Table created...");
            state show;
        }
    }
    link_message(integer sender_num, integer num, string str, key id) {
        if(str == "reset") {
            state reset;
        }
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
    state_exit() {
        llSetTimerEvent(0);
    }
}
 
state show {
    state_entry() {
        // DEBUG
        llOwnerSay("Updating display with the number of recorded visitors...");
        llInstantMessage(
            CORRADE, 
            wasKeyValueEncode(
                [
                    "command", "database",
                    "group", wasURLEscape(GROUP),
                    "password", wasURLEscape(PASSWORD),
                    "SQL", wasURLEscape(SQL_COUNT_VISITORS),
                    "callback", wasURLEscape(callback)
                ]
            )
        );
        // alarm 60
        llSetTimerEvent(60);
    }
    timer() {
        // DEBUG
        llOwnerSay("Timeout reading rows from visitors table...");
        llResetScript();
    }
    http_request(key id, string method, string body) {
        llHTTPResponse(id, 200, "OK");
        if(wasKeyValueGet("command", body) == "database") {
            integer success = wasKeyValueGet("success", body) == "True";
            if(!success) {
                // DEBUG
                llOwnerSay("Failed to enumerate visitors: " + 
                    wasURLUnescape(
                        wasKeyValueGet(
                            "error", 
                            body
                        )
                    )
                );
                llResetScript();
            }
            list data = wasCSVToList(
                wasURLUnescape(
                    wasKeyValueGet(
                        "data",
                        body
                    )
                )
            );
 
            // DEBUG
            //llOwnerSay("Data: " + llDumpList2String(data, ","));
 
            integer visits = llList2Integer(
                data, 
                llListFindList(
                    data, 
                    (list)"Visits"
                ) + 1
            );
            integer time = llList2Integer(
                data, 
                llListFindList(
                    data, 
                    (list)"Time"
                ) + 1
            );
            integer memory = llList2Integer(
                data, 
                llListFindList(
                    data, 
                    (list)"Memory"
                ) + 1
            );
 
            // Send message to xyz script.
            llMessageLinked(LINK_ROOT, 204000, "V:" + (string)visits, "0");
            llMessageLinked(LINK_ROOT, 204000, "T:" + (string)time + "m", "1");
            llMessageLinked(LINK_ROOT, 204000, "M:" + (string)memory + "k", "2");
 
            // Get the list of agents.
            agents = llGetAgentList(AGENT_LIST_REGION, []);
            state insert_trampoline;
        }
    }
    link_message(integer sender_num, integer num, string str, key id) {
        if(str == "reset")
            state reset;
        if(str == "display") {
            line = 0;
            state display;
        }
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
    state_exit() {
        llSetTimerEvent(0);
    }
}
 
state insert_trampoline {
    state_entry() {
        llSetTimerEvent(1);
    }
    timer() {
        state insert;
    }
    link_message(integer sender_num, integer num, string str, key id) {
        if(str == "reset")
            state reset;
        if(str == "display") {
            line = 0;
            state display;
        }
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
    state_exit() {
        llSetTimerEvent(0);
    }
}
 
state insert {
    state_entry() {
        // Once the list is empty, go back to display.
        if(llGetListLength(agents) == 0) {
            state show;
        }
 
        key agent = llList2Key(agents, 0);
        agents = llDeleteSubList(agents, 0, 0);
        list name = llParseString2List(llKey2Name(agent), [" "], []);
        if(llGetListLength(name) != 2) {
            return;
        }
        string firstname = llList2String(name, 0);
        string lastname = llList2String(name, 1);
        agentName = firstname + " " + lastname;
        string memory = (string)(
            (integer)(
                llList2Float(
                    llGetObjectDetails(
                        agent,
                        [OBJECT_SCRIPT_MEMORY]
                    ),
                    0
                )
                /
                1024 /*in kib, to mib 1048576*/
            )
 
        );
        // DEBUG
        //llOwnerSay("Memory: " + memory);
        // The command sent to Corrade responsible for adding a visitor
        // or updating the data for the visitor in case the visitor is
        // already entered into the visitors table. This is performed
        // with an INSER OR REPLACE sqlite command given the first name
        // and the last name of the avatar are unique primary keys.
        llInstantMessage(
            CORRADE,
            wasKeyValueEncode(
                [
                    "command", "database",
                    "group", wasURLEscape(GROUP),
                    "password", wasURLEscape(PASSWORD),
                    "SQL", wasURLEscape(SQL_INSERT_VISITOR),
                    "data", wasListToCSV(
                        [
                            "firstname",
                            firstname,
                            "lastname",
                            lastname,
                            "time",
                            llGetTimestamp(),
                            "memory",
                            memory
                        ]
                    ),
                    "callback", wasURLEscape(callback)
                ]
            )
        );
 
        // Command timeout.
        llSetTimerEvent(60);
    }
    http_request(key id, string method, string body) {
        llHTTPResponse(id, 200, "OK");
 
        // DEBUG
        //llOwnerSay(wasURLUnescape(body));
 
        if(wasKeyValueGet("command", body) == "database") {
            integer success = wasKeyValueGet("success", body) == "True";
            if(!success) {
                // DEBUG
                llOwnerSay("Failed to insert visitor " + agentName + " due to: " + 
                    wasURLUnescape(
                        wasKeyValueGet(
                            "error", 
                            body
                        )
                    )
                );
                state insert_trampoline;
            }
 
            // DEBUG
            llOwnerSay("Processed visitor named " + agentName + "...");
 
            state insert_trampoline;
        }
    }
    timer() {
        // DEBUG
        llOwnerSay("Inserting visitors has timed out, resetting...");
 
        state insert_trampoline;
    }
    link_message(integer sender_num, integer num, string str, key id) {
        if(str == "reset") {
            state reset;
        }
        if(str == "display") {
            line = 0;
            state display;
        }
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
    state_exit() {
        llSetTimerEvent(0);
    }
}
 
state display_trampoline {
    state_entry() {
        ++line;
        state display;
    }
    link_message(integer sender_num, integer num, string str, key id) {
        if(str == "reset")
            state reset;
    }
}
 
state display {
    state_entry() {
        llInstantMessage(
            CORRADE, 
            wasKeyValueEncode(
                [
                    "command", "database",
                    "group", wasURLEscape(GROUP),
                    "password", wasURLEscape(PASSWORD),
                    "SQL", wasURLEscape(SQL_SELECT_VISITOR),
                    "data", wasListToCSV(
                        [
                            "offset",
                            (string)line
                        ]
                    ),
                    "callback", wasURLEscape(callback)
                ]
            )
        );
 
        // alarm 60
        llSetTimerEvent(60);
    }
    http_request(key id, string method, string body) {
        llHTTPResponse(id, 200, "OK");
 
        if(wasKeyValueGet("command", body) == "database") {
            integer success = wasKeyValueGet("success", body) == "True";
            if(!success) {
                // DEBUG
                llOwnerSay("Failed to query the visitors table: " + 
                    wasURLUnescape(
                        wasKeyValueGet(
                            "error", 
                            body
                        )
                    )
                );
                return;
            }
 
            // Grab the data key if it exists.
            string dataKey = wasURLUnescape(
                wasKeyValueGet(
                    "data",
                    body
                )
            );
 
            // We got no more rows, so switch back to scanning.
            if(dataKey == "")
                state show;
 
            list data = wasCSVToList(dataKey);
 
            string firstname = llList2String(
                data, 
                llListFindList(
                    data, 
                    (list)"firstname"
                ) + 1
            );
            string lastname = llList2String(
                data, 
                llListFindList(
                    data, 
                    (list)"lastname"
                ) + 1
            );
            string lastseen = llList2String(
                data, 
                llListFindList(
                    data, 
                    (list)"lastseen"
                ) + 1
            );
 
            llOwnerSay(firstname + " " + lastname + " @ " + lastseen);
 
            state display_trampoline;
        }
    }
    link_message(integer sender_num, integer num, string str, key id) {
        if(str == "reset") {
            state reset;
        }
        // If the display button is pressed again, go back to the display and stop.
        if(str == "display") {
            state show;
        }
    }
    timer() {
        // DEBUG
        llOwnerSay("Timeout reading rows from visitors table...");
        llResetScript();
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
    state_exit() {
        llSetTimerEvent(0);
    }
}
 
state reset {
    state_entry() {
        // DEBUG
        llOwnerSay("Resetting all visitors...");
        llInstantMessage(
            CORRADE,
            wasKeyValueEncode(
                [
                    "command", "database",
                    "group", wasURLEscape(GROUP),
                    "password", wasURLEscape(PASSWORD),
                    "SQL", wasURLEscape(SQL_DROP_TABLE),
                    "callback", wasURLEscape(callback)
                ]
            )
        );
 
        // alarm 60
        llSetTimerEvent(60);
    }
    timer() {
        // DEBUG
        llOwnerSay("Timeout deleting database...");
        llResetScript();
    }
    http_request(key id, string method, string body) {
        llHTTPResponse(id, 200, "OK");
        if(wasKeyValueGet("command", body) == "database") {
            integer success = wasKeyValueGet("success", body) == "True";
            if(!success) {
                // DEBUG
                llOwnerSay("Failed to drop the visitors table: " + 
                    wasURLUnescape(
                        wasKeyValueGet(
                            "error", 
                            body
                        )
                    )
                );
                llResetScript();
            }
            // DEBUG
            llOwnerSay("Table dropped...");
            llResetScript();
        }
    }
    on_rez(integer num) {
        llResetScript();
    }
    changed(integer change) {
        if((change & CHANGED_INVENTORY) || (change & CHANGED_REGION_START)) {
            llResetScript();
        }
    }
    state_exit() {
        llSetTimerEvent(0);
    }
}

secondlife/scripted_agents/corrade/projects/in_world/database_visitor_tracker.txt ยท Last modified: 2022/05/16 10:49 by office

Access website using Tor Access website using i2p Wizardry and Steamworks PGP Key


For the copyright, license, warranty and privacy terms for the usage of this website please see the license, privacy, copyright and the plagiarism pages.