OVERVIEW
This set of scripts is designed to run on your own webserver, in order to allow you to easily store and retrieve information from Second Life using the llHTTPRequest() function. It allows you to store data per Second Life user in field/data pairs. You don’t have to use it this way, but that’s what it was designed for.
IMPORTANT NOTE
Please remember that this is a free beta. If you run across minor bugs, please feel free to report them here, and we’ll keep an eye on this project.
DOWNLOAD
CHANGELOG
- Version 1.02
- Minor code optimizations
- Version 1.01
- Added “secret” parameter for more secure requests
- Added support for “POST” HTTP Request method
- Version 1.0
- Release version
HOW THIS SYSTEM STORES DATA
Each record stored has three variables: KEY, FIELD, VALUE.
- KEY - The key variable is generally designed to store data by SL user keys or uuids. You can use any unique string as a key value, but that’s what it was designed for.
- FIELD - This is an identifier for the data you’re storing (such as “length”)
- VALUE - And this is the actual information you want to store (such as “21.5″)
For instance, if you were looking to store a particular object’s length, width, and height, (21.5, 10, 0.1) you would store your data as three records:
- <object uuid>, length, 21.5
- <object uuid>, width, 10
- <object uuid>, height, 0.1
where <object uuid> is the Second Life UUID of that particular object.
If you were looking to store a Second Life user’s favorite color, favorite movie, and favorite singer, you might store it like this:
- <user key>, color, <1,0,0>
- <user key>, movie, Boondock Saints
- <user key>, singer, Neil Diamond
where <user key> is the Second Life key for that particular user.
This is about as flexible a storage solution as I could come up with without getting into your individual needs. If you need something more flexible, you should write your own script (or hire somebody else to).
WHAT YOU’LL NEED
You will need your own hosting account with PHP/MySQL. If you don’t know what those are, go learn and come back when you do. Typically speaking, you can find PHP/MySQL hosting on the cheap (less than $5/mo) all over the place (I recommend http://www.site5.com).
A good rule of thumb is this: if it will run Wordpress, it should run SLDB just fine.
Most of the detailed instructions here assume you’re running a hosting account with cPanel, but that’s just because I’m lazy and can’t be bothered to learn about other shared hosting setups. As long as your account has PHP and MySQL, you’re good to go - read the documentation on your hosting account to learn how to set up your MySQL database.
You’ll also need an FTP client of some kind (or cPanel’s File Manager) and know how to use it. It probably wouldn’t hurt to learn how to use phpMyAdmin either, but you don’t need it to set up this script.
You don’t need your own domain name, but this guide assumes you have one, so you’ll need to adjust the instructions accordingly. Also, domain names are only $10, so cough up and get one, you cheap bastard.
INSTALLATION
This part looks really long, but that’s because there’s a lot of extra details added in for folks who are new to this stuff. If you’ve ever installed Wordpress, it’s mostly the same stuff, only with less files to upload and less stuff to fill out.
STEP 1: CREATE A MYSQL DATABASE AND USER
You’ll need to create a MySQL database and a user account to manage that database. If you’re on a cPanel account, this is best accomplished by clicking the “MySQL Database Wizard” icon. Otherwise, follow your hosting service’s instructions.
You should give your user full privileges to the database you create (at the very least SELECT, INSERT, UPDATE, and DELETE). Then make a note of the database name, the user name, and the password.
IMPORTANT: If you have a cPanel account, you should know that your database name is actually a combination of your login name and the name you chose. The same is true of your database username. So if your username is “dimwit” and you create a database named “sldb” and a user named “sldbadmin” the actual names are “dimwit_sldb” and “dimwit_sldbadmin,” respectively.
Let’s assume your database details are as follows:
Database Name: dimwit_sldb
Database User: dimwit_sldbadmin
Database Pass: sekret
Okay, you’re ready. Let’s move on to…
STEP 2: EDIT THE FILES
Unzip the files in this package (though chances are you’ve already done that if you’re reading this README) and open “config.php” in your fave plain text editor (if you open it in Microsoft Word, I’ll punch you in the mouth).
Fill out the values listed in that file with the database details you just created. For the most part, you don’t need to change “localhost.”
You can also select a database table name, though it’s really not necessary. You can leave the default of “sldb_data.” In fact, if you know enough about PHP/MySQL to NEED to change that value, you should probably be writing your own solution rather than using my crappy one.
The last value in config.php is called “secret.” This is a passphrase you will define and use in your requests to prevent others from easily posting/fetching data in your database simply by knowing the location of the installation. Note that this is not exactly military-grade security; if you’re going to be storing sensitive information, you should probably use a more secure custom solution.
STEP 3: UPLOAD THE FILES
Upload the three files (config.php, data.php, and install.php) to a folder on your webserver. Note the location of this. For the purposes of this guide, we’ll assume it’s this:
http://www.example.com/sldb/
You don’t have to call the folder sldb, but remember the name of it.
Typically speaking, this folder should go in the /www/ or /public_html/ folder (different for some hosts).
STEP 4: RUN INSTALL.PHP
Visit install.php on your server (in our example, it should be at http://www.example.com/sldb/install.php).
Confirm that you want to install the table.
IMPORTANT: Don’t install twice. If data already exists in that table, running install.php will wipe it clean.
Once you’re done installing the table, delete the install.php file, like, immediately.
STEP 5: HAVE A MOJITO. YOU’RE DONE.
BASIC USAGE
Now that you’re all uploaded and stuff, you can store and retrieve data from http://www.example.com/sldb/data.php using llHTTPRequest() (or cURL, but if you knew cURL you probably wouldn’t need this, so I’ll skip that stuff).
The fastest way to use this is by using the example.lsl script included in the package. This script includes three custom functions you can call to store/retrieve/delete data in various ways. Just copy the functions into the top of your LSL script (along with a couple of required variables) and you’ll be ready to go, or use the example to make your own adjustments. However, it’s good, on the whole, to know how the system works, so we’re including a guide here.
NOTE: The rest of this section is going to look really daunting, but remember that this is only really necessary if you want to use the system for more advanced applications. If you’re only looking to store/save a few user preferences, the LSL example is much simpler and easy to use. Read on once you’ve given the example a shot if you think you might need a more involved understanding of how this works.
UNDERSTANDING HTTP GET REQUESTS
The LSL example handles most of this for you, but it’s a good idea to understand just what this system is doing.
Essentially, when you send an HTTP request, you’re reading a really simple web page created by the web app. In order for the web app to know what kind of data to put on the page, it uses certain bits of data included in the URL of the page you’re requesting.
A simple GET request URL looks like this:
http://example.com/page.php?parameter1=value1¶meter2=value2
The web app grabs the parameter1 = value1 part and the parameter2 = value2 part and uses those values to create the page with the data you need. So a sample request for this particular app may look like this:
http://example.com/sldb/data.php?key=a27b84f0-2757-4176-9579-43a181d4a5a0&fields=length|width|height&action=get&secret=mypassphrase
This requests tells the web app to “get” (action=get) the values for length, width, and height (fields=length|width|height) assigned to the user key a27b84f0-2757-4176-9579-43a181d4a5a0 (key=a27b84f0-2757-4176-9579-43a181d4a5a0) and authenticates the request with your passphrase (secret=mypassphrase).
Once you get how this works, it’s just a matter of knowing what parameters to include in your http request.
PARAMETERS
You can do one of three things with these requests: add/update data (put), retrieve values (get), or remove data (del). You can update, retrieve, or delete multiple fields at once.
The SLDB web app accepts the following parameters:
- KEY (key) - Each record is assigned to a “key,” a unique string (no more than 36 characters long) that identifies the “owner” of that data.This key can be an avatar’s UUID (most common), an object UUID (less useful), or even a random value determined by you to identify that set of values.The key allows you to store multiple field/value pairs in a single context (such as field/value pairs for each user of an object).
- SEPARATOR (separator) - When sending fields/values to the web app, you need a character (or combination of characters) to separate values in a list. For instance, this string: length|width|height
is a list of fields (length, width, and height) separated by the character “|”.The default separator is the pipe (”|”) and the web app will use this character if you have not defined a separator. We use the pipe as the default because your data may already contain commas, which would make parsing results difficult. However, if you’d like to use a different separator, such as “||” (double pipe) or “*” (asterisk) you may do so. The web app will parse your list using your custom separator and return it using the separator, so it’s best to define your separator as a global variable to avoid confusion (as seen in the LSL example). Use a separator character that will NOT appear in your field names or values. - FIELDS (fields) - These are the names of the fields you’re looking to update, delete, or retrieve, separated by the separator (for instance: length|width|height).If you’re retrieving data, it will be returned in the same order as the given request.
- VALUES (values) - These are the values you’re looking to assign to the fields, in the same order as the field names. Like the fields parameter, the values should be sent in a list separated by the same character you have defined as your separator (for instance: 21.5|15|0.1).Note that all data will be typecasted to strings, so you’ll need to re-cast your retrieved data to use it. In other words, if you’re storing the vector <1,2,3> it will return as a string “<1.000, 2.000, 3.000>” so you’ll want to use the (vector) typecast to read it when retrieving it from the server.
- ACTION (action) - The action parameter defines what it is you want the web app to do with the data it’s being sent. There are three possible values for this:
- action=put - The “put” action stores your field/value pair. If a value already exists for any given field, it will be overwritten with the new value.
- action=get - The “get” action retrieves the values for a given list of fields, and will return the list of values in the same order as the fields requested.
- action=del - The “del” action deletes the the values for a given list of fields. Note that the actions that write to/delete from the database are, by nature, irreversible; you will need to manually re-write values or delete written values to undo these actions.
- REVERSE (reverse) - If set to “reverse=true”, “reverse=yes”, or “reverse=1″, the action you’re taking (either get or del) will be reversed. For instance, if you want to retrieve the field names of certain values (instead of the values for certain field names), use “reverse=true” and a list of values instead of fields in order to return the set of field names. The same goes for “del;” if you want to delete certain records based on the values and NOT the field names, use “reverse=true” and provide values instead of field names. Note that when using reverse, you’ll need to provide exact values, as the web app uses “value=<data>” instead of “CONTAINS %<data>%.” Again, this web app is designed for simplicity, so if you need a more flexible solution you’ll probably need to write one yourself.
- VERBOSE (verbose) - The verbose parameter (”verbose=true” or “verbose=yes” or “verbose=1″) will return more verbose results from your queries. For instance, if you send a request to update these fields: length|width|height with these values: “21.5|10,0.1″ you will by default get the message “SUCCESS:3″ which indicates you have successfully updated 3 records. If verbose is enabled, the response will be “SUCCESS: UPDATED 3 RECORDS.” In this way, verbose is good for debugging (more user-readable) but harder to parse.When requesting data, however, the verbose option becomes more important. By default, the server returns only the values you’ve requested in the same order by which they were requested (for instance, requesting “length|width|height” would return “21.5|10|0.1″). With verbose enabled, it returns a list with the field names included (for instance: “length|21.5|width|10|height|0.1″). This is handy if you don’t want to hold the current list of fields you’re fetching, and would rather be able to parse the values directly from the http_response.
- SECRET (secret) - The secret parameter is a required parameter and should match the one set in your config.php file. This authenticates the request.
ALL_DATA AND NO_DATA
There are two custom strings used by the web app: ALL_DATA and NO_DATA. ALL_DATA is something you can send to the server in your get and del requests to instruct the web app to either get or delete all fields for a given key. Send the list ["ALL_DATA"] from your LSL script to accomplish this.
Note that if you’re using the get action, it’s better to use verbose when requesting ALL_DATA, as there will be no other way to figure out which value corresponds to which field name otherwise. Also, if you have a lot of fields stored for each key, it’s probably a bad idea to use ALL_DATA to fetch; LSL can only accept strings so long, and you might only get a part of your data back because of this.
NO_DATA is something the server returns if there is no data set for a particular field you’re requesting data from. For instance, if you have nothing set in the “width” field and you request “length|width|height” you’ll get something like this: “21.5|NO_DATA|1.0″.
RESPONSES AND ERROR MESSAGES
When retrieving data, the server will respond with either the data you’ve requested or (if something is afoul) an “ERROR:” message. When storing or deleting data, the server will respond with a SUCCESS:” message, followed by the number of records affected by your request. There are only a handful of error messages you can get, and these are as follows:
- ERROR: INSTALL FILE NOT DELETED - You must delete the install.php file after you have installed SLDB. It is so important, in fact, that the rest of the web app has been designed not to work until this has been done. If you get this message, go delete the install.php from your server using your FTP client.
- ERROR: CANNOT CONNECT TO DATABASE - This means that either your MySQL services are offline or (more commonly) the MySQL username/password combination you’ve provided in config.php is not correct.
- ERROR: CANNOT SELECT DATABASE - This indicates that the web app was able to connect to your MySQL services (the user/pass combination is fine) but it cannot find the database you’ve named in config.php.
- ERROR: INSUFFICIENT ARGUMENTS - This indicates that you’ve sent a request that is lacking the necessary parameters. You may have forgotten to put a key value in the request, for instance, or to name your fields.
- ERROR: UNMATCHED PARAMETERS - This message only occurs in storage (put) requests, and indicates that the number of field names you have provided does not match the number of values you’ve provided. For instance, if you tried to update [length,width,height] with [21.5,10] you’ll get this error because you’ve only got two values for three arguments.
- ERROR: SYNTAX - This message indicates some kind of fatal problem with the web app’s MySQL syntax. If you get this, and you haven’t been messing around with the php code, it’s probably my fault, so tell me in the comments. If you have been messing around with the php code, go back and check your adjustments, because MySQL is getting syntax commands it doesn’t understand.
- ERROR: CANNOT CONNECT TO SERVER - This error is actually defined in the example LSL script, so if you’ve changed it, it’ll be different. This indicates that the LSL script could not connect to your server at all, and most commonly means you’ve entered the wrong URL in your HTTP request.
TIPS & TRICKS
Here are a few tips and tricks for using the system to fullest advantage. We’ll add more when we think of some.
- If you submit a float to the PutData() function (the custom function included in the LSL example), remember that LSL will automatically stretch out the decimal places on a vector. In other words, if you submit [1.5] as a value, this will be stored to the database as “1.500000″ because LSL distends this float before converting it to a string. If this is a problem for you (it shouldn’t be unless you’re using the data for text displays), do your own string conversion (["1.5"]) before sending it to the function.
- You don’t have to use UUIDs or SL avatar keys for the “key” parameter. This is the most ubiquitous usage of the “key” parameter, which is why we suggest it as a default, but any unique string will do. You could use an avatar’s name, or a group name, or a product name - the “key” parameter is simply a way of grouping sets of fields together so that you can have multiple instances of the same field for different users, objects, or applications.
- You don’t have to use one database for all your needs; simply install another instance of SLDB (connected to another database) in a different directory. MySQL databases are cheap (most accounts come with unlimited databases), so there’s no discernible reason not to set up multiple databases for different applications.
- BACK UP YOUR DATABASES. It’s easy to back up a MySQL database, and the resulting backup is usually tiny. If your hosting provider has a control panel (like cPanel or Plesk) there’s usually an option right in the control panel. If they don’t, you can use phpMyAdmin. Regular database backups are like colonoscopies; they may be a pain in the ass, but by the time you realize you should have had one, it’s too late.
- BACK UP YOUR DATABASES. This bears repeating.
- When possible, group your requests so as to retrieve multiple fields using the same llHTTPRequest , rather than running one request for each field. These requests aren’t particularly resource-heavy, but it’s just good server hygiene to get the most out of your server traffic.
[...] DOWNLOAD SL»DB [...]
Luc,
I’m using the SLDB, its really good, thanks for the work. As well as using GET to fetch a field,name pair from a unique key, is there a way to return the list of unique keys, e.g. if I have the av UUID’s of customers as the unique key, is there a way to return a list of customer keys?
Thanks in advance
SLDB doesn’t have this feature built-in, and for one reason: fetching all of the unique keys could be an extremely long list, and certainly more than LSL could handle in an HTTPRequest.
That having been said, it wouldn’t be too hard to write a custom function to pull this data if you had some way to paginate it. What kind of use is this for?
I ran into 2 questions while installing your SLDB kit:
1.when I created my database I recieved a ‘database port’ number. Is this important?
(I use a free hosting (freehostia.com) service)
2. when i ran ‘install.php’ it said ‘db formatted. you must delete this file (index.php) - i didnt see any ‘index.php’ should that be ‘install.php’?
thanx:P you ROCK!
PS: I think it ISworking btw… Object: name|NO_DATA|size|NO_DATA sounds better than CAN”T CONNECT TO SERVER:P
Generally, you don’t need to input a database port number, and if the install.php worked, then your database connection details are solid.
If it sez index.php, then yeah, my bad. It’s install.php you need to delete.
If you’re getting name|NO_DATA|size|NO_DATA etc, then it sounds like it’s working (and that you have no data). If it couldn’t connect to the database or the server, or the authentication wasn’t right, or any other kind of fatal error, you’d get an ERROR: BLAH BLAH BLAH THE SKY IS FALLING kind of message.
Thanks for catching the “index.php” thing. I updated it in the download. No need to reinstall or anything; it was just that one text change and won’t affect anything at all.
I’d like to first of all THANK you for giving this away! I’ve been trying to get something like this running for some time now. Secondly in response the first post, I’m also primarily interested in keeping a record of sales and being able to get a range of records would be a most helpfull feature. Perhaps an idea to display the whole list via a webpage instead of sending it back to SL?
SLDB was really designed to be used for one purpose, and that is for storing and retrieving the data via HTTP request (such as the ones used in LSL). Within that context, it’s designed to be as flexible as possible, but if you’ve got a really specific implementation, you’re better off either modding it or setting up something on your own.
The reason that this makes for bad webpage display is that display on a webpage is really application-specific. How many rows/columns in the table? Table header names? Sort orders by specific field? There’s really no way to make a solution for display that serves a wide spectrum of needs.
I’d recommend hopping on ScriptLance and hiring up a better PHP code-slinger to whip you up a solution if this is important. Either that, or using something like hippoVEND for your vendors, which has the features you’re looking for.
Ok I am diving into this a bit deeper now and created an LSL app to store sales data.
so for example, im storing :
llDetectedKey , ["name","product","date","location"], [llkey2name,myprod,llGetTimeStamp,llGetRegionName],0;
and I can retrieve the dat by sending the key and a list of fields i want
My question is. Can i, instead of using the key, use a field+value to retrieve all entries that have the same value in the same field? In other words, can I retrieve all entries with the same date, or all entries with the same product?
First of all, I’d point out that storage model you’ve laid out here will only work if each person only buys one item. You’re using their uuid as the unique key, so if they buy a second item, the details of that purchase will overwrite the first item.
I’d recommend instead using some kind of unique transaction id for the key field, and storing the user’s key as part of the transaction data.
There isn’t a function built in to SLDB to retrieve all entries that have the same field name. The reason for this is that, again, SLDB is designed for HTTP requests from inworld objects. Having an open-ended call for data like that would often result in the server sending back data that is too long for the LSL script to handle.
Ok, I was able to make this thing work, so now, I’m trying to find out where I can use it, but thanks, I was searching something like this for ages
While installing first I got the message value too big and I changed it to 255. Then I got this message:
Error formatting table: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) TYPE=MyISAM D
My MySQL Version is 4.0.25
Any idea what to do?
Thanks in advance
I updated MySQL. - Installation now worked fine
Huh. Don’t remember anything about MySQL 4 limiting the varchar type to 256. That’s really weird.
Sorry I missed your question; was out of town.
To what version did you update your MySQL?
Edited to remove sentence ending in preposition. Sorry, Mrs. Kim.
I’m getting the same SQL errors when I try to install about variable length. — Error formatting table: Too big column length for column ‘value’ (max = 255). Use BLOB instead
Im using MySQL client version: 4.1.22
Just thanks for the great job y’ve done! Very much appreciated!
First and foremost, thanks! This is an excellent product and the price can’t be beat!
I offer this suggestion for optimizing the code of data.php
Replace this:
======
switch($method) {
case ‘GET’:
$key = $_GET['key'];
$action = strtolower($_GET['action']);
$fields = $_GET['fields'];
$values = $_GET['values'];
$verbose = strtolower($_GET['verbose']);
$reverse = strtolower($_GET['reverse']);
$separator = $_GET['separator'];
$password = $_GET['secret'];
break;
case ‘POST’:
$key = $_POST['key'];
$action = strtolower($_POST['action']);
$fields = $_POST['fields'];
$values = $_POST['values'];
$verbose = strtolower($_POST['verbose']);
$reverse = strtolower($_POST['reverse']);
$separator = $_POST['separator'];
$password = $_POST['secret'];
break;
}
$action = strtolower($action);
$verbose = strtolower($verbose);
$reverse = strtolower($reverse);
======
With this:
======
$key = $_REQUEST['key'];
$action = strtolower($_REQUEST['action']);
$fields = $_REQUEST['fields'];
$values = $_REQUEST['values'];
$verbose = strtolower($_REQUEST['verbose']);
$reverse = strtolower($_REQUEST['reverse']);
$separator = $_REQUEST['separator'];
$password = $_REQUEST['secret'];
======
Won’t make a huge performance difference, but should make it just a tad bit easier for those who may use it to follow its logic.
Thanks; that makes much more sense. Made that tweak for 1.02. I’m happy to take any other optimizations people notice; like I said, PHP is still a bit new for me.
Thanks for this great free solution! One quick thing though, are there any known bugs in 1.02? I’m having trouble installing on my server, but 1.01 worked for me. Also, the ALL_DATA custom string does not seem to be working for me on delete requests.
hi looking at the code in PHP top me its seems really bad for xxs using _REQUEST reason beaing as you can get and post you need to filter it better to prevent xxs
for xample i taken the example above that hanover posted
what you want is
$fields = isset($_POST['fields']) ? $_POST['fields'] : (isset($_GET['fields']) ? $_GET['fields'] : ”);
you can also setup default values where i left ” or use null if they are keys
and for improved security use filters ie only allow the secondlife agent post and get for example
Steve
I was getting very frustrated as to why I kept getting a “SYNTAX” error. Seems while i was trying to PUT something i used an apostrophe. I guess the WWW doesnt like apostrophes in it’s URLs?
Thanks for this! Just saved me a few days going from zero with PHP and SQL
Hi
Problem. Retrieving data from MYSQL DB.
Can anyone please help. I am using the Example provided in the DB software. I can store data in my sql DB. Done using the following function.
PutData(key id, list fields, list values, integer verbose)
{
string args;
args += “?key=”+llEscapeURL(id)+”&action=put&separator=”+llEscapeURL(separator);
args += “&fields=”+llEscapeURL(llDumpList2String(fields,separator));
args += “&values=”+llEscapeURL(llDumpList2String(values,separator));
args += “&secret=”+llEscapeURL(secret);
put_id = llHTTPRequest(url+args,[HTTP_METHOD,"GET",HTTP_MIMETYPE,"application/x-www-form-urlencoded"],”");
integer listLength = llGetListLength(fields);
llSay(0, (string)listLength);
llSay(0, “put data =”);
string p = llList2String(values,0);
llSay(0, p);
string q = llList2String(values,1);
llSay(0, q);
}
I get a 200 OK response from llhttpReq. I see the values correctly in SL local chat window.
I try yo receive the values back but only get back the Field names i.e “name” and “size” and not any of the values i.e “tom coba”. Again 200 OK response from server. The function i use is : -
GetData(key id, list fields, integer verbose)
{
string args;
args += “?key=”+llEscapeURL(id)+”&action=get&separator=”+llEscapeURL(separator);
args += “&fields=”+llEscapeURL(llDumpList2String(fields,separator))+”&verbose=”+(string)verbose;
args += “&secret=”+llEscapeURL(secret);
get_id = llHTTPRequest(url+args,[HTTP_METHOD,"GET",HTTP_MIMETYPE,"application/x-www-form-urlencoded"],”");
integer listLength = llGetListLength(fields);
llSay(0, (string)listLength);
llSay(0, “get data =”);
llSay(0, (string)fields);
}
The function calls are made as follows
default
{
state_entry()
{
//In this example, we’re storing the owner’s name and avatar size when the script first runs.
PutData(llGetOwner(),["name","size"],[llKey2Name(llGetOwner()),llGetAgentSize(llGetOwner())],FALSE);
}
touch_start(integer total_number)
{
// In this example, we’re fetching the owner’s name and avatar size (stored above) on touch.
GetData(llGetOwner(),["name","size"],TRUE);
}
Thx in advance.
@Tom Coba… this is an obvious question I know, but are you looking for the results of the GetData in the reply you get to delivered to the http_response event ? Printing it at the end of the GetData won’t show anything other than what you sent to the server, ie calling llHTTPRequest doesn’t modify anything you passed in, you have to wait for the reply to arrive as an http_request event
Now onto the reason I’m here myself… Using 1.01 I’m hitting a problem where I’m seeing duplicate uuid/field entries written in the database where the first uuid/field combination should have got overwritten by the second. It usually seems to occur when two DB writes of the same data are done in close succession (eg when an event-driven write happens close to a timer-driven write of the same data).
I’ve already changed my LSL code so that it waits for one http request to return before firing the next and queues up any waiting to be sent.
I’m writing 10 or so field/value items per call, so it’s possible I guess that the first call is still going on when the second gets started concurrently.
Any tips for avoiding this? This smells like a concurrency / locking / semaphore type problem in the database or the php scripts
Thanks,
–Chorazin
Hi Luc,
I probably shouldn’t be posting my question here, so if it is inappropriate, please forgive me. I am also a total programming/database knucklehead, so I also apologise for this in advance.
I am wondering if your SLDB could be used for a specific function I have in mind for my Chinese language and culture classes in SL. We have a restaurant in which students do a food ordering role play. I have an avatar bot in the restaurant that can carry out part of the conversation in Chinese around ordering food through an AIML application that has been developed for our lessons (certain bot commands have also been built into the AIML). However, I would like the bot (or an object loaded with an LSL script that talks to an external database) to be able to ‘remember’ the dished ordered by a student(s) or at a particular table, then match each dish with a pre-set cost, add up the total, and then via chat or some other (hopefully reasonably simple) mechanism, ‘report’ the total cost back to the students doing the role play.
Is there a simple way to set something like this up using SLDB? have a cPanel account and can set up the requisite MYSQL DATABASE.
Any advice hugely appreciated.
Scott
Scott HI,
I am just a beginner using Luc’s wonderful DB. To answer your question YES the DB will easily be able to store a dish ordered by a customer in the data field. So in your case the BOT would detect the student, get their name (field) and dish (data), and store that info. in the DB. Later this info can be easily read back easily using the methods described by Luc above.
It is simple to setup if u follow Luc’s instructions above.
Tom
Tom’s got the right of it, and thanks Tom for responding to Scotts’ question.
Yeah, with a little creative figuring SLDB should be able to do that just fine. You can store the data a number of ways.
Remember that a unique data set is defined by a key, and can have as many field/data pairs as you need. Tom’s suggestion, from what I can tell, is to have one unique data set (say, key = ‘ORDER’) where each avatar name stores one dish. So each database row would look like this:
key field data
ORDER | Luc Aubret | Lo Mein
ORDER | Tom Coba | Snow Peas
Then you could have a second unique data set where each dish stores one price. Let’s say the key for this set is “PRICE.”
key field data
PRICE | Lo Mein | 52 yuan
PRICE | Snow Peas | 27 yuan
This second set is only necessary if you need to store the price data on the database. If it’s a small menu, it’s better to hardcode those values into the script so that you don’t have to make a second database call to retrieve the price.
Does that answer your question?
Would it be possible to add in a search function to the PHP side of the DB and then call a “search” action where the PHP would search the “field” provided for the “value” requested then return the result. OS instead of trying to request the entire DB and then do a search in SL, have the search done on the PHP side and only send the result to SL.
I would, actually, like to add a search function to SLDB, and probably will in a future revision. I have to think about the best way to make it work for the broadest number of applications.
I’m looking to have a database to keep track of a number of coins that each user has. Is there a way of adding/subtracting from the number that is stored in the database or do I have to do it through a get call then a put call?
I can see how the ability to add/subtract from given fields would be useful, and I’ll consider that in a future revision.
SLDB was designed to be really light and serve the most broad use case scenarios, so it treats all entries as text and allows you to cast them as your own variable type.
What you can do for now is what you mentioned… pull the information, adjust it, then send the new information back. Or if you know a little PHP you can adjust the code yourself by adding a custom function to add/subtract.
A simple web template to display the info would be great.
Hi Luc,
In 1st time i wouldlike to say lot of thanks and give you THE smack you required on data.php
in 2nd i apologize for my bad English, i’m French….
in 3rd…i apologize, again, for my question… Because it’s the same than Tom Coba (if i’m good!)
I wouldlike to get “values” data back in lsl but it’s empty.
You give this solution :
key field data
ORDER | Luc Aubret | Lo Mein
ORDER | Tom Coba | Snow Peas
But… Where i nned to make this changes? because if key is the same all the time, that overwrite data…
In advance, many thanks
PS: If it’s more easy for you to explain me , in fact i wouldlike to make something like a Subscribe o Matic for my shop (not for resell, just for me)
I am required to use this example for a project in school and I am rather new at LSL hence I am very confused with this tutorial. I have set up a cpanel account and followed the instructions step by step but I am confused about the LSL part. I am required to push data collected on second life to mySQL. Can you give me an example or tutorial? ):