Free database for OpenSim scripts

Late last summer, I had an idea for a hypergate script that would automatically calculate the distance to your destination’s address and, if it was too far away, send you to a mid-way transit gate on Hyperica, and then that gate would send you on to your destination.

However, this required a way for gates to communicate with one another, even if they were located on different grids. Now, there is a way for objects to send messages to one another via LSL, but it requires knowing the object’s HTTP address — and this HTTP address can change.  So I needed a way for the hypergates to look up the HTTP addresses of the mid-point transit gates, in order to find out whether they were available, and to tell them the final destination the avatar wants to go to.

I needed a permanent way to save data.

Many scripts in OpenSim need to store variables, someplace where they won’t disappear.

Say, for example, you have a house that changes colors. And you want it to remember the color it was set to last, so you don’t have to keep setting it each time you rezz the house. You can add the color — say, “blue,” to the description line, but if you change the description, you lose the data.

If you had some permanent way to store the variable, then you could, say, change the color of that same house everywhere — even on other grids — when you change any one instance of it.

Okay, maybe that’s not so useful, but you could also use the same method to, say, send messages across grids, or update the location of hypergates, like what I needed — or set high scores for a particular game.

Earlier, I used a Google Spreadsheet to store data. Here’s my sample script for logging visitors to a Google Spreadsheet, and another one for displaying the data from a Google Spreadsheet.

The downside to using Google Spreadsheets is that you can only add rows of data — you can’t edit existing rows. Unless you can figure out the Google API and authentication mechanism, which I couldn’t. If anyone can, and can write a sample script to edit existing rows of data, please please let me know! And you also can’t search through data — you can only load the entire sheet and then search through it inside the script. And there’s only so much text you can load at once. I assume — I don’t know what the limits are here.

So I was looking for a way to save data into some database somewhere — ideally, a free database. A way to update that data. And a way to find that data.

I turned to Latif Khalifa’s Grid URL Persister for inspiration. It uses a Python script running on the Google App Engine, a free, cloud-based place to deploy applications and databases. Well, free for small projects — if you’re running something big and commercial on it, you’d have to pay some dough. But the limits on the free account are pretty good — you get ten free applications, 1 gigabytes for data storage, and 3,000 free calls a minute — more than half a million a day.

So I’ve been tinkering around with the Google App Engine ever since then, going through their tutorials, setting up the launcher on my desktop, deploying their sample Guestbook application, trying to figure out what the Go language was.

Finally, early this week, I decided to buckle down and just get it done. Based on advice from InWorldz CTO David Daeschler and InWorldz software developer Jim Tarber, I switched from Go to Python, the language Khalifa’s application was written in.

And … ta da! … here it is: the Hypergrid Business Easy Free Database.

Here’s the way it works.

First, you need to get a unique identifier for your LSL script. You can use a different one for each script, or, if you want to share data across several scripts, you can use the same identifier for all of them. You can get an identifier here. Don’t lose this — if you do, you will lose all your data!

Second, load the following URL to save data:

http://hbeasydatabase3.appspot.com/store?service=<service id>&keyfield=<name for the data>&datafield=<data to be stored>

The bold items are the ones you replace your own own data. Say, for example, you want to save high scores for two games, Bingo, and Blackjack. You get a new service ID, and invent key names for the data — BingoHighScore and BlackjackHighScore. Store the data by going to the following URLs:

http://hbeasydatabase3.appspot.com/store?service=2b307c02-2133-4dba-bcc5-3d8f7db53eee&keyfield=BingoHighScore&datafield=786763

http://hbeasydatabase3.appspot.com/store?service=2b307c02-2133-4dba-bcc5-3d8f7db53eee&keyfield=BlackjackHighScore&datafield=234873

Later on, you want to get these high scores back. You can look them up again by pulling up the following URLs:

http://hbeasydatabase3.appspot.com/load?service=2b307c02-2133-4dba-bcc5-3d8f7db53eee&keyfield=BingoHighScore

http://hbeasydatabase3.appspot.com/load?service=2b307c02-2133-4dba-bcc5-3d8f7db53eee&keyfield=BlackjackHighScore

Go ahead, give it a try.

If you also want to save the avatars who had the high scores, you can create two new key names, such as BingoHighScoreAvatar, and BlackjackHighScoreAvatar.

There might be a limit on how long you can make the key names, and how long the data strings are, but I don’t know what it is. If you have key names or data that has spaces or other characters in it, that’s fine, but you will need to encode them for the URL.

So, for example, if “John Smith” got the high score for Bingo, you would save it with the following URL:

http://hbeasydatabase3.appspot.com/store?service=2b307c02-2133-4dba-bcc5-3d8f7db53eee&keyfield=BingoHighScoreAvatar&datafield=John%20Smith

Note that the space between “John” and “Smith” is replaced with %20.

You don’t have to just call these URLs from in-world scripts, of course. You can also call them from a website.

Say, for example, you want to be able to control the color of your house from a webpage.

You can create a key name HouseColor and use clickable links to set the house different colors, like this:

http://hbeasydatabase3.appspot.com/store?service=###&keyfield=HouseColor&datafield=blue

http://hbeasydatabase3.appspot.com/store?service=###&keyfield=HouseColor&datafield=red

http://hbeasydatabase3.appspot.com/store?service=###&keyfield=HouseColor&datafield=green

Hint: if doing this from a website, use the “target=” link property so that you’re not sending your users to a blank page that just says OK. For example, you can load the “OK” into a small frame on the page.

I understand that many of my readers are really good Javascript programmers, or know MySQL, or whatever, and will ask, “Why not just write a little code to do the same thing, set up a MySQL database on your server?” And the answer is: There are people who want to add functionality to their scripts without having to learn JavaScript or Python or MySQL. Is that too much to ask?

Creating your scripts

To demonstrate the use of this tool — and to find out whether it works! — I created two LSL scripts. One turns a box a random color when it’s touched, and then saves that color. The second script, when touched, looks up the color and sets the box that color.

The box on my right — the pink one — turns a new random color when touched, then saves that color to the database. The box on my left — the orange one — is waiting for me to touch it, When I do, it will go to the database to find out what color it’s supposed to be, and then turn that color. That is, pink.

The LSL script to save the color is here in the form of a text file — copy and paste in-world.

The key lines are these:

string URL = “http://hbeasydatabase3.appspot.com/store?service=” + serviceid + “&keyfield=colors&datafield=” + colors;
key httpkey=llHTTPRequest(URL, [] ,””);

 In the first line, serviceid is a string variable, defined at top of the script, as the unique identifier. Change this for your own script. The name of the key field is “colors” and the datafield is the string variable colors, defined just above to hold a string composed of three floating point numbers, separated by commas. Since the string has commas in it, I used the llEscapeURL function so that it would work in a URL.

Once you define all the parts of the URL, you just use the llHTTPRequest function and that’s it. The data is stored into the database. Click on this link to see what’s stored there now: http://hbeasydatabase3.appspot.com/load?service=b1e5e346-b831-47fa-9930-6ca5d4cd774b&keyfield=colors

That’s it. Just two lines to save any data you want into the database. Again, remember — don’t forget the service ID, and don’t forget your key field names!

So how do you get the data back out of the database?

It’s a little trickier — it takes an extra subroutine (or whatever they’re called in LSL).

You can get the script, as a text file, here.

The first part is the same as before — an llHTTPRequest:

string URL = “http://hbeasydatabase3.appspot.com/load?service=” + serviceid + “&keyfield=colors”;
key httpkey=llHTTPRequest(URL, [] ,””);

Again, the serviceid variable is defined as a string at the top of the script. Make sure that it matches the serviceid in your other script. Note that the datafield is missing — you’re not storing data here, you’re trying to get it. The other change is that instead of hbeasydatabase3.appspot.com/store?, you’ve got hbeasydatabase3.appspot.com/load? at the start of the URL.

But it’s not enough just to send the command out there — you have to actually get data back. That’s the function of the http_response subroutine:

http_response(key id, integer status, list meta, string body)
{
list colors = llParseString2List(body,[“,”],[” “]);
llSetColor(<llList2Float(colors,0), llList2Float(colors,1), llList2Float(colors,2)>,ALL_SIDES);
}

The data comes back into the body variable, in the form of a string. I had to unpack it a little bit to turn it into something I could use — first, parsing the string into a list, then, when I set the box color, turning each of the three elements of the list into a floating-point number.

If the command fails for some reason — the service ID or the key field name are wrong, for example, the body string will hold the error message.

If you want to add error checking to the first script, you can add an http_response section as well. The body string will be “OK” if the data was stored successfully, and will hold an error message if it wasn’t.

Saving more than one variable

In the example above, I save a three-element list. That’s one way of saving multiple data points.

Another option is to use multiple variables — like with the high scores example. But what if you want to save a long list of high scores? A list where you might not know the length of the list ahead of time?

One idea is to save one variable as ListLength, which is the total list of data points you’re saving. As the list gets longer, you can just increment this field. Then have the other variables with names such as ListField1, ListField2, ListField3 … just keep adding numbers to the word “ListField” until you have as many as you need.

Similarly, you can save tables. Have one variable called TableSize, with the number of rows and columns of your table. Then the table data can be saved as TableField1-1, TableField1-2, TableField1-3, TableField2-1, and so on.

Saving list data or table data into individual fields means you can store or load one data point at a time. However, if you need to load the entire list or table at once, you’ll have to send in a new llHTTPRequest for each one. That might take a lot of time. That’s why I saved all three colors as a single list variable in my script, instead of fetching them one at a time. I recommend using lists whenever possible, but there’s a limit to how long a URL can be — I just ran a quick test, and for a keyfield name of “longstring”, the longest data field string I could store was 500 characters even.

Run your own database

Someday, my Easy Free Database will run out of storage. If you’re creating business-critical in-world scripts, you don’t want your stuff to go down.

Sure, you can email me and let me know that the database is out of room, and, if I’m feeling generous, I could pay to add more storage.

Or you can create your own database.

Download my source code from BitBucket and unzip it. Create a free account with Google App Engine. Download and install the Google App Engine Launcher. Open the Launcher, go to File-Add Existing Application, point it to the folder where you unzipped my source code. Then hit the Deploy button and give it the email address and password you set up your Google App Engine account with.

It took me several tries to get this to work, but once it does, you can fiddle around with the application to customize it for your own needs. You can email me if you have questions at [email protected], but keep in mind that, as of this writing, I have exactly three days worth of experience programming in Python.

Again, here’s the code:

 

maria@hypergridbusiness.com'

Maria Korolov

Maria Korolov is editor and publisher of Hypergrid Business. She has been a journalist for more than twenty years and has worked for the Chicago Tribune, Reuters, and Computerworld and has reported from over a dozen countries, including Russia and China.

  • WhiteStar

    Great idea for offsite settings data storage. This could certainly have many other applications as well… progress tracking, task tracking etc for simulators which have tasks to complete (think educational).

    One item though… not everyone could or can use an external system such as this, luckily in OpenSimulator we have http://opensimulator.org/wiki/OsMakeNotecard which allows up to store material on the fly in notecards. Of course this depends on it being enabled & available to use.

    • http://www.hypergridbusiness.com/ Maria Korolov

      I was considering using Notecards — except they don’t solve the issue of cross-grid communication — you still have to do something else for that. Plus, I wanted to track how often individual destinations were up. (A lot of the smaller grids are down most of the time.)

  • casias falta

    Whoo, hoo, we’re having fun now!! Very nice Maria

    • http://www.hypergridbusiness.com/ Maria Korolov

      Thanks!

  • http://www.hypergridbusiness.com/ Maria Korolov

    Another thing I wanted to add is a comment about flow control. For people who are expert LSL programmers, this is probably a no-brainer, but I’m shocked each time I’m reminded that LSL programs don’t go step-by-step the way normal programs do (or the way they did 20 years ago — God knows what programs do these days).

    LSL scripts are state-based. That means that if you want something to happen after some bit of information comes back from the database, you’ll need to put in the command to do it in the http_response subroutine, or tell the script to switch to another state at that point.

    This is the main reason I went with saving the three color values as one list, instead of as three separate variables — I’d either need to load in one color value at a time, or send out for all three at once, then keep track of which ones came back. Pain in the ol’ patootie.

    • Serendipity Seraph

      Well, there are methods available to parse json and if you work in a C# script it is pretty straightforward to get a key value map of arbitrary complexity back from some web services that returns json.

  • Serendipity Seraph

    Nice. I would tend to use a NoSQL solution in the cloud with a Rest http interface to store and retrieve the information. But nice idea and kudos on getting it working.