Graal Forums - SQLite

Page 1 of 6

Show 40 post(s) from this thread on one page

Graal Forums (https://forums.graalonline.com/forums/index.php)

- Future Improvements (https://forums.graalonline.com/forums/forumdisplay.php?f=10)

- - SQLite (https://forums.graalonline.com/forums/showthread.php?t=84995)

Inverness

04-05-2009 12:14 AM

SQLite

SQLite is a serverside only update that should have been added months ago, why don't we have it?

Though I've just about lost all interest in scripting due to lack of updates.

LoneAngelIbesu

04-05-2009 01:12 AM

SQLite would be a great help when it comes to data storage. It's more efficient than flat-file storage, and doesn't have all the problems that flat-file systems run in to (and isn't a pain to script!).

There are tutorials for SQLite integration with Torque on the GarageGames resources web site. I would definitely prefer this being priority number one more than anything else.

Inverness

04-05-2009 02:39 AM

Fun facts about the mud account files on Valikorlia:

Total count: 9,149
Total file contents size: 6.27 MB
Total file size including metadata (on Vista): 36.1 MB
And I compressed them into a 180 kB 7-Zip archive.

Would love to be using a database instead.

Admins

04-08-2009 05:52 PM

Ok added SQLite support, when using the requestsql-commands then the default database is now a sqlite database

PHP Code:


		
			
requestsql("CREATE TABLE test (account varchar not null default '' primary key, age int not null default 1)", false);

requestsql("INSERT INTO test VALUES ('Stefan', 33)", false);
requestsql("INSERT INTO test VALUES ('Graal', 11)", false);
requestsql("INSERT INTO test VALUES ('Unixmad', 42)", false);

temp.req = requestsql("SELECT * FROM test", true);
if (!temp.req.completed)
  waitfor(temp.req,"onReceiveData",60);
  
echo("Accounts in database: " @ temp.req.rows.size());
for (temp.row: temp.req.rows)
  echo("  Account: " @ temp.row.account @ ", age: " @ temp.row.age);

Right now it's experimental (not checking for flooding etc.) so I can enable it on request.
To build the query string use string.escape() to avoid SQL injection.
The full list of supported SQL statements is available at http://www.sqlite.org/lang.html

fragman85

04-08-2009 06:12 PM

Quote:

Originally Posted by Inverness (Post 1481036)

SQLite is a serverside only update that should have been added months ago, why don't we have it?

Though I've just about lost all interest in scripting due to lack of updates.

afk

Tigairius

04-08-2009 10:03 PM

Quote:

Originally Posted by Stefan (Post 1482284)

Ok added SQLite support, when using the requestsql-commands then the default database is now a sqlite database

Fantastic. I know a lot of people have been waiting for this :D

I can't wait to try it out.

Inverness

04-08-2009 10:05 PM

Quote:

Originally Posted by Stefan (Post 1482284)

<snip>

I'd like it enabled for Valikorlia and the Testbed Server.

So where is the database file stored? I would like to be able to back that up easily.

Also: what is the boolean value for, what are the parameters for requestsql2(), and what are the details on the object that the two functions return.

cbk1994

04-08-2009 10:50 PM

PHP Code:


		
			
Script help for 'sql':
requestsql(str, bool) - returns object
requestsql2(str, str, bool) - returns object

For new commands, couldn't you add a small description and what the parameters are? It wouldn't take that long :\

LoneAngelIbesu

04-08-2009 10:53 PM

Quote:

Originally Posted by Inverness (Post 1482358)

Also: what is the boolean value for

The boolean in requestsql() is probably for whether or not you want the SQL commands to return a value.

Inverness

04-08-2009 10:57 PM

Quote:

Originally Posted by LoneAngelIbesu (Post 1482369)

The boolean in requestsql() is probably for whether or not you want the SQL commands to return a value.

I suppose I should have said that I already had a good idea.

Edit: After looking up SQLite documentation, I have a few more suggestions.

I suggest instead of having the global requestsql() functions that instead there be an sqlopen(str filename) function that returns an sql database object that can be used to execute sql queries and such. This is so there can be multiple databases opened simultaneously.

PHP Code:


		
			
db = sqlopen("data/factions.db");
req = db.exec("typical sql statement here");
// do stuff with req
db.close();

I don't want the whole server to use a single database, it's too easy for someone to just drop all the tables with a single command. I also don't think it would be too much trouble to add a waitforcompleted() function to the request object, which would return immediately if the request is completed or wait until it is completed.

Along with this, I noticed that before I have been unable to load files from disk during the onInitialized() in the Control-NPC, I'd like that fixed so there is no problem opening databases the instant the server starts.

Admins

04-09-2009 12:45 PM

The requestsql2() command lets you specify which configured database you are using. That way you can easily switch between sqlite or mysql. The database configuration is right now not accessible by server staff though.

requestsql() for sqlite databases is right now synchronous, but it can be possible later to also do it asynchronous like for mysql. In the example I have already shown how to wait for the command to complete:

PHP Code:


		
			
if (!temp.req.completed)
  waitfor(temp.req,"onReceiveData",60);

It would be best to write your own wrapper class to do database commands (like getsql() and setsql()).

Update: the new npcserver is now installed on the private/hosted playerworld computers, restart it to try.

Inverness

04-10-2009 04:05 AM

Quote:

Originally Posted by Stefan (Post 1482478)

PHP Code:


		
			
if (!temp.req.completed)
  waitfor(temp.req,"onReceiveData",60);

Did you enable SQL for Valikorlia like I asked? And I would like it if you could take a look at what I suggested in my previous post.

I'm also not going to be using SQL unless I can back the whole database up at least twice a day.

cbk1994

04-10-2009 04:12 AM

Pardon my ignorance, but what is the advantage of using a database (SQLite) to store information as opposed to text files or database NPCs?

Inverness

04-10-2009 04:18 AM

Quote:

Originally Posted by cbk1994 (Post 1482665)

Pardon my ignorance, but what is the advantage of using a database (SQLite) to store information as opposed to text files or database NPCs?

http://en.wikipedia.org/wiki/SQL

Admins

04-10-2009 12:21 PM

Quote:

Originally Posted by cbk1994 (Post 1482665)

Pardon my ignorance, but what is the advantage of using a database (SQLite) to store information as opposed to text files or database NPCs?

You can easier make queries on the whole dataset, e.g. get all accounts with more than 100 hours online time (if you would have stored that information in the database).
Also you can access information independent from if the player is online or not, so it could be used on servers that currently store special additional attributes in files.

All times are GMT +2. The time now is 09:55 PM.

Page 1 of 6

Show 40 post(s) from this thread on one page