Welcome to the Second Life Forums Archive

These forums are CLOSED. Please visit the new forums HERE

SL-SQL or SL-PATH or ... something

Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-24-2004 15:36
With the advent of the two RPC interfaces, and the hope that one day either e-mail-RPC will be fast enough to be useful, or XML-RPC will be fully implemented, I'm beginning work on a generic, standardized external data storage/retrieval system.

Among the specifications I need to define is the language specification. (Actually, two specifications, which I'll get to in a minute.)

My initial thought was to use a modified subset of SQL for the language. It then occured to me that XPATH can also be used for at LEAST data retrieval, if not manipulation.

So, I figured I'd come to the people most likely to use such a service and start a dialog (multilog?) on the language specification.

The two specifications that I need to solidify are a Data Definition Language (DDL) and a Data Manipulation Language (DML.)

So, what I'd like here is some discussion on what elements of DDL and DML would be necessary for such a service to be useful to a third party. Also I'd like to discuss specifics such as syntax, which will have to be modified from whatever standard the language ends up being based on.

I'm personally leaning heavily towards a variation on SQL, that I'll call SL-SQL.

Obvious DDL requirements:
- CREATE TABLE tablename (column1 integer, column2 string, column3 key, column4 rotation, column5 vector)
- DROP TABLE tablename

- need a standard for defining primary keys on tables
- debating on a standard for defining foreign keys between tables
- debating on a standard for creating indexes on tables.

Obvious DML requirements:
- SELECT
- INSERT
- UPDATE
- DELETE
- TRUNCATE TABLE tablename

For SELECT, UPDATE, and DELETE, we obviously need a WHERE clause. For SELECT, we also need ORDER BY. Not sure yet whether it would be worth the hassle to support joins, grouping, or unions.

I want to retrict this to simple queries. No full-featured SQL here. (No cursors, variables, dynamic SQL, object ownership, UDT's, UDF's, etc.)

Now, while we're debating over what we would need, and what would be nice-to-have, here's what I want everyone to keep in the back of your minds:

If you were given a script that you could drop into an object, and communicate via llMessageLinked() and link_message(), how would we make this work? I'm thinking the integer value and/or key value should be reserved as a query "handle." Probably the int, since I don't think there's currently any way to generate a random key, but we can generate random integers.

So, dicuss away. Please. The language will become an open standard, so that I won't necessarily be the only such service in the long run. But before in order to have a useful open standard, we need participation in this discussion.
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-24-2004 15:39
Also, we need a security implementation of some kind. Username/password is what comes to mind as the obvious method, but we certainly need a way to keep these relatively secure.
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Adam Zaius
Deus
Join date: 9 Jan 2004
Posts: 1,483
06-24-2004 19:59
Install MySQL.

Make the login part of the request. Process queries directly.

Setup a new database for each user, as part of signup process. :)

Done.

-Adam
_____________________
Co-Founder / Lead Developer
GigasSecondServer
Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-25-2004 06:00
a) My implementation is going to be MS SQL Server 2000 for reasons that I'm not going to debate.

b) No way in hell I'm giving unfettered, pure SQL access to my database server by ANYONE. The point here is to create a compact language set for use in LSL, but for security reasons there is a requirement that the queries be PARSED and re-built in pure SQL, to prevent people from trying to sneak hacking code into the queries.
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-25-2004 06:53
Oh, and another thought, Adam.

Using direct SQL access, as you suggested, how exactly would you recommend that a recordset be returned from the database to SL?

This is a question that needs to be resolved regardless of whether I were to allow direct SQL access or not.

And finally, in my previous post, I guess ANYONE is a bit strong. It would be more appropriate to say that I'm not going to give that kind of database access to just anyone that's willing to pay a minimal service fee. Possibly, at some point, I would allow direct access for people for a significantly higher fee, or in cases where I knew and trusted the individual.
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Tcoz Bach
Tyrell Victim
Join date: 10 Dec 2002
Posts: 973
06-25-2004 17:49
Why break the XML model at all? XPath or just DOM walking/SAX for retrievals, XSL/T for manipulation and transformation.

Package your data as XML from the git go and you can just use XML everything.

This is referring to (what I think you mean) the ability to work with data externally (i.e., after it has left SL), as internally we have no XML model...what we do have is a pseudo document structure called "lists", which we can search scan move around etc.
_____________________
** ...you want to do WHAT with that cube? **
Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-25-2004 18:55
I'd thought about that. But, I have a real database server, so there's no reason not to leverage it.

Storing XML data in files has intrinsic concurrency problems. We're theoretically talking about a significant number of concurrent data requests, many attempting to access the same data, or at least the same table.

A real database platform can handle this cleanly with little or no wait-time involved. Locked files, OTOH, creates a whole new set of problems that have to be programmed around.
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-26-2004 07:47
Well, it would seem that there isn't as much interest in an SL-specific SQL standard as I'd thought. I'm assuming this means that there isn't as much interest in generic persistent data storage applications as I'd thought, either.

In a way, this is a good thing. I can dump the whole generic data storage project which was one of the more complex projects I had planned, and move on to one of my proprietary data projects, instead.

No problem for me. I can charge more for custom proprietary implementations, and they're not really portable to other uses or data services, so it encourages client retention and improves my bargaining leverage with existing clients.
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Cadroe Murphy
Assistant to Mr. Shatner
Join date: 31 Jul 2003
Posts: 689
06-26-2004 11:59
Grim - I'm interested in seeing how your project goes but didn't have much to contribute because for any project of my own I'm likely to build a custom API through RPC and do any direct DB access off-world. Maybe other people with something to contribute are in the same position, and that's why you haven't gotten a lot of response. Anyway, if you keep us updated I'll at least be reading.
_____________________
ShapeGen 1.12 and Cadroe Lathe 1.32 now available through
SLExchange.
Adam Zaius
Deus
Join date: 9 Jan 2004
Posts: 1,483
06-26-2004 14:13
Dont need unfettered SQL access. If you r running a good SQL server, you should be able to make new user accounts, then limit which SQL commands they have access to, as well as which tables/databases. :)

-Adam
_____________________
Co-Founder / Lead Developer
GigasSecondServer
Adam Zaius
Deus
Join date: 9 Jan 2004
Posts: 1,483
06-26-2004 14:21
I've got a similar system with unfettered access to a MySQL database, based on the username submitted.

The format is as follows:

The reply:

[encapsulated in 3DES encryption]

#queryID

[repeat for all rows]
FIELD^^^FIELD^^^FIELD^^^FIELD
[/repeat]

[/encapsulation]

The request:

[encapsulated]

#queryID (specified)

query::<sqlquery>::
username::<username>::
password::<password>::

[/encapsulated]
_____________________
Co-Founder / Lead Developer
GigasSecondServer
Grim Lupis
Dark Wolf
Join date: 11 Jul 2003
Posts: 762
06-26-2004 15:48
From: someone
Originally posted by Cadroe Murphy
Grim - I'm interested in seeing how your project goes but didn't have much to contribute because for any project of my own I'm likely to build a custom API through RPC and do any direct DB access off-world. Maybe other people with something to contribute are in the same position, and that's why you haven't gotten a lot of response. Anyway, if you keep us updated I'll at least be reading.


Well, that's sort of the point. If the data access is an open standard, and adopted by many scripters, then it doesn't matter if I quit SL 6 months from now and take my database with me. Anyone containing a script I wrote with data access capability would be able to simply transfer their database to another provider.

But if it's proprietary, then if I shut my database down, everyone gets screwed over if they're using any script that was based on my proprietary database API.

I was just under the impression that there were several SL developers hanging around that would actually support the concept of an open standard.

Hell, I'd be happy if a couple of scripters (*cough* Hank *cough*) that want data storage but don't want to support the necessary off-world resources would simply pipe up with a loose idea of how they'd like it to work from a user's perspective.

The plan was to develop this open standard, then write some scripts that use it. The end user that buys the script can get a database account with anyone that supports the open standard. Rather than having each in-world script be dependent on a specific RL data servers survival.

Adam, I'm not familiar with the non-standard quirks of mySQL, because I use primarily MS SQL (T-SQL). But, if you allowed end users to write their own SQL statements, what kind of effect would this have on your server? (assume the dummy table has been created and has at least one record in it.)

CODE

WHILE 1=1 INSERT INTO dummy SELECT * FROM dummy
_____________________
Grim

"God only made a few perfect heads, the rest of them he put hair on." -- Unknown
Carnildo Greenacre
Flight Engineer
Join date: 15 Nov 2003
Posts: 1,044
06-26-2004 22:58
From: someone
Originally posted by Grim Lupis
Adam, I'm not familiar with the non-standard quirks of mySQL, because I use primarily MS SQL (T-SQL). But, if you allowed end users to write their own SQL statements, what kind of effect would this have on your server? (assume the dummy table has been created and has at least one record in it.)

CODE

WHILE 1=1 INSERT INTO dummy SELECT * FROM dummy


On mine, what should happen is that the process responsible for the query will quickly go over its CPU limit and be terminated, returning a "500 Internal Service Error" to the XML query server.
_____________________
perl -le '$_ = 1; (1 x $_) !~ /^(11+)\1+$/ && print while $_++;'
Rhysling Greenacre
Registered User
Join date: 15 Nov 2003
Posts: 132
06-27-2004 00:25
to prevent malicious queries you could write a validator that only allows certain queries to hit the database. xpath might also be an option because i dont think you can do crazy things like while statements in that.
Adam Zaius
Deus
Join date: 9 Jan 2004
Posts: 1,483
06-27-2004 04:27
That would give an invalid query response from mySQL, as I have 'WHILE' disabled', (and subqueries for that matter too.)

-Adam
_____________________
Co-Founder / Lead Developer
GigasSecondServer