Graal Forums

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.rowtemp.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(strbool) - returns object
requestsql2
(strstrbool) - 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)
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.

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.

Inverness 04-10-2009 02:30 PM

*Simmering*

Crow 04-10-2009 03:12 PM

Great new addition. And while we are talking about great new addition, could you please add Lua as a secondary, optional scripting language to Graal? I know this would be time consuming and lots of work/effort, but it would be so much worth it. Torque Engine Script (or whatever it is called) is a pile of poo compared to Lua. Lua is rated the fasted scripting language availabe.

Inverness 04-10-2009 03:35 PM

Quote:

Originally Posted by Crow (Post 1482743)
Great new addition. And while we are talking about great new addition, could you please add Lua as a secondary, optional scripting language to Graal? I know this would be time consuming and lots of work/effort, but it would be so much worth it. Torque Engine Script (or whatever it is called) is a pile of poo compared to Lua. Lua is rated the fasted scripting language availabe.

Graal's scripting language was made by Stefan, it has nothing to do with Torque (other than the special GUI control syntax), it is merely compliant with some standard that I forget the name of.

Lua would be an improvement because it is a stable and well developed, and compact language. Of course we can't simply use Lua as it is because with Graal, security needs to be considered so Stefan would have to modify Lua to add security measures before he could use it.

It's too much work at this point.

I'm also obligated to say that I think Stackless Python is a better choice than Lua, but would be harder to modify for Graal-level security I think.

Crow 04-10-2009 04:58 PM

Quote:

Originally Posted by Inverness (Post 1482744)
Graal's scripting language was made by Stefan, it has nothing to do with Torque (other than the special GUI control syntax), it is merely compliant with some standard that I forget the name of.

Lua would be an improvement because it is a stable and well developed, and compact language. Of course we can't simply use Lua as it is because with Graal, security needs to be considered so Stefan would have to modify Lua to add security measures before he could use it.

It's too much work at this point.

I'm also obligated to say that I think Stackless Python is a better choice than Lua, but would be harder to modify for Graal-level security I think.

As far as I know, GScript2 is Torque Engine Script with quite a lot of Graal modifications to it. Correct me if I'm wrong though.

Inverness 04-10-2009 05:01 PM

Quote:

Originally Posted by Crow (Post 1482753)
As far as I know, GScript2 is Torque Engine Script with quite a lot of Graal modifications to it. Correct me if I'm wrong though.

You are wrong, GScript2 is GScript2, its syntax is just made to conform to a standard.

LoneAngelIbesu 04-10-2009 05:07 PM

Quote:

Originally Posted by Inverness (Post 1482754)
You are wrong, GScript2 is GScript2, its syntax is just made to conform to a standard.

Inverness is correct. (http://graal.net/index.php/TorqueScript) GS2 just borrows from its syntax.

And no, SQLite has not been added to Valikorlia... and 2 database backups per day is a bit unnecessary.

Crow 04-10-2009 06:47 PM

I see. Good to know, thanks.

Inverness 04-11-2009 01:48 AM

Quote:

Originally Posted by LoneAngelIbesu (Post 1482756)
And no, SQLite has not been added to Valikorlia... and 2 database backups per day is a bit unnecessary.

Backups as a whole seem to be unnecessary to you, since you haven't made any backups yourself :). Though, I think once per day is a good interval.

LoneAngelIbesu 04-11-2009 05:10 PM

Quote:

Originally Posted by Inverness (Post 1482837)
Backups as a whole seem to be unnecessary to you, since you haven't made any backups yourself :). Though, I think once per day is a good interval.

No need to get snippy, Inverness. I don't back anything up because downloading files more than 1mb crashes my RC, 9 times out of 10.

In the rest of the scripting world, the number of times you back up data is proportionate to when the most changes occur (if data is updated weekly, there's no need to make daily backups), and sometimes how likely it is that you will need to restore that data (if you're not concerned with frequent server crashes). The only times Valikorlia has ever needed to restore back ups are when a staff member deletes everything in a hissy fit, and when the NPC server crashes. The first one happens more often than the latter, and the latter is only a problem because we're not using SQLite databases.

If you want to back-up all the information twice a day, or once a day, go ahead. I don't see the need for such paranoia, and am fine with weekly backups.

Inverness 04-11-2009 05:39 PM

Quote:

Originally Posted by LoneAngelIbesu (Post 1482981)
No need to get snippy, Inverness. I don't back anything up because downloading files more than 1mb crashes my RC, 9 times out of 10.

Excuses, I sure haven't heard about this problem of yours so I guess you didn't think it important enough to ask about.
Quote:

Originally Posted by LoneAngelIbesu (Post 1482981)
If you want to back-up all the information twice a day, or once a day, go ahead. I don't see the need for such paranoia, and am fine with weekly backups.

Whether or not you are fine with weekly backups is irrelevant. You are staff and can easily manipulate your inventory, players can't and it's much easier to back up their inventories every day than to traverse logs to find out how their inventory has changed since the last backup in the event of a problem.

LoneAngelIbesu 04-11-2009 05:47 PM

Quote:

Originally Posted by Inverness (Post 1482989)
Excuses, I sure haven't heard about this problem of yours so I guess you didn't think it important enough to ask about.

What can you do about it?
Quote:

Originally Posted by Inverness (Post 1482989)
Whether or not you are fine with weekly backups is irrelevant. You are staff and can easily manipulate your inventory, players can't and it's much easier to back up their inventories every day than to traverse logs to find out how their inventory has changed since the last backup in the event of a problem.

The point I made was that the likelihood of that being anything close to common occurrence is little to none. Let me break it down even further: the main reason why the NPC server has crashed is because of errors when using the INI file system.

Like I said, though. If you want back everything up that often, go ahead. I'm sure you could even script something that would do it automatically; some GS2 equivalent to a cron job.

EDIT: Could the new NPC server please be put on Valikorlia, already? :(

Admins 04-11-2009 08:07 PM

Ok the new npcserver has been uploaded to all machines, need to be restarted manually though.

Inverness 04-11-2009 08:35 PM

Quote:

Originally Posted by Stefan (Post 1483015)
Ok the new npcserver has been uploaded to all machines, need to be restarted manually though.

Ok, first, what folder is the database file in? I would like if the one on Valikorlia could be placed somewhere visible to RC so it can be backed up easily. I don't feel too comfortable using it otherwise considering everything could be deleted with little effort.

Second, if the table has a column named "name" or like some other static variable then the row object has two variables named "name" in it, I was thinking that when setting the variables in the row object, if it conflicts with static variables then append an underscore.

Admins 04-11-2009 09:50 PM

The database is databases/main.db
Use "as" for renaming columns of the result. You can also access the result as array, like rows[][]

Inverness 04-12-2009 08:20 AM

Quote:

Originally Posted by Stefan (Post 1483044)
The database is databases/main.db
Use "as" for renaming columns of the result. You can also access the result as array, like rows[][]

Ah good, thanks.

Could you perhaps make a function for converting arrays into a format suitable for SQL statements? Basically making it into a large string, using single quotes around strings, and escaping the single quotes in the string. I figure that would be better if not done by script.

And, I'd also like a server option for reporting SQLite errors and such on RC.

This is the equivalent script:
PHP Code:

public function sqlarray(array) {
  
temp.out "";
  
temp.sz = array.size();
  
  if (
temp.sz 1)
    return 
"";
  if (
temp.array[0].type() == 0)
    
temp.out @= temp.array[0];
  else
    
temp.out @= this.sqlescape(temp.array[0]);
  for (
temp.1temp.temp.array.size(); temp.++) {
    if (
temp.array[i].type() == 0)
      
temp.out @= "," temp.array[i];
    else
      
temp.out @= "," this.sqlescape(temp.array[i]);
  }
  return 
temp.out;
}
public function 
sqlescape(rstr) {
  
temp.temp.rstr.positions("'");
  if (
temp.p.size() > 0)
    for (
temp.temp.p.size() - 1temp.> -1temp.--)
      
temp.rstr replacesubstring(temp.rstrtemp.p[temp.e], 1"''");
  return 
"'" temp.rstr "'";
}
// replacesubstring from util_string
public function replacesubstring(stringpositionlengthnewstring) {
  if (
temp.position 0) {
    return;
  }
  return 
temp.string.substring(0temp.position) @
         
temp.newstring @
         
temp.string.substring(temp.position temp.length, -1);



Admins 04-12-2009 03:07 PM

Well for errors I gave the suggestion to write a wrapper function:

PHP Code:

function getsql(query) {
  
temp.req requestsql(query,true);
  if (!
temp.req.completed && !waitfor(temp.req,"onReceiveData",60))
    return 
NULL;
  if (
temp.req.error!="")
    echo(
temp.req.error);
  return 
temp.req;


For escaping use the string.escape() function.

Inverness 04-12-2009 04:11 PM

Quote:

Originally Posted by Stefan (Post 1483239)
For escaping use the string.escape() function.

I don't believe that is the same thing as what I'm talking about. For example, if a have a graal string "It's alive!" and I want to stick it in as a string value in an SQL statement string then it needs to be changed to "'It''s alive!'". the SQL strings have the single quotes around them plus any single quotes inside the string need to have a second single quote after it to be escaped.

Also, I don't think you specified what the boolean value was for in requestsql() and requestsql2().

napo_p2p 04-12-2009 06:16 PM

Quote:

Originally Posted by Inverness (Post 1483246)
Also, I don't think you specified what the boolean value was for in requestsql() and requestsql2().

It's in /scripthelp now:
Quote:

requestsql(str, bool) - returns object - executes a sql query for the default database, parameters are the query string and if you expect a result (e.g. from a select-query); the result is an event object, call waitfor(requestobj,"onReceiveData",60) and then use requestobj.rows, error and lastinsertid

requestsql2(str, str, bool) - returns object - like requestSQL(), but allows you to select the database, parameters are database identifier, query, expect result (true/false)

LoneAngelIbesu 04-12-2009 06:31 PM

Is there a way to create 'sub-tables'? It's been a while since I've using any SQL, and the one I did use was MySQL.

What I'm trying to do is create a sub-table of 'shops' to store item data.
PHP Code:

  sql.execute(
    
"CREATE TABLE IF NOT EXISTS shops ("
    
"title TEXT NOT NULL DEFAULT 'Shop', "
    
"stype TEXT NOT NULL DEFAULT 'Server', "
    
"owner TEXT NOT NULL DEFAULT 'Unknown', "
    
"kerou INT NOT NULL DEFAULT 5000, "
    
"desc TEXT NOT NULL DEFAULT 'Description text.', "
    
"createtime INT NOT NULL DEFAULT CURRENT_TIMESTAMP)"false);
  
sql.execute (
    
"CREATE TABLE IF NOT EXISTS shops.items ("
    
"arch TEXT NOT NULL DEFAULT 'Unknown', "
    
"price INT DEFAULT 0, "
    
"stock INT DEFAULT 0)"false); 

However, I get the following error:
Quote:

SQL Error: unknown database shops
Query: CREATE TABLE IF NOT EXISTS shops.items (arch TEXT NOT NULL DEFAULT 'Unknown', price INT DEFAULT 0, stock INT DEFAULT 0)

Damix2 04-12-2009 08:30 PM

Just in general a 'sub-table' in a ERD is just 2 tables in a parent-child relationship. The child will always hold the foreign key to point towards the parent, not entirely sure why you are getting an error.

Sorry, my help was fairly useless.

Admins 04-12-2009 09:44 PM

Inverness, string.escape() is made for that. It escapes ' and \ according to the SQL standard / pascal.

LoneAngelIbesu 04-13-2009 02:10 AM

Quote:

Originally Posted by Damix2 (Post 1483324)
Just in general a 'sub-table' in a ERD is just 2 tables in a parent-child relationship. The child will always hold the foreign key to point towards the parent, not entirely sure why you are getting an error.

Sorry, my help was fairly useless.

Does the following link give any valuable information?

http://sqlite.org/omitted.html

More specifically, this bit on foreign-key constraints:
Quote:

FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers. The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.

Damix2 04-13-2009 02:51 AM

Quote:

Originally Posted by LoneAngelIbesu (Post 1483389)
Does the following link give any valuable information?

http://sqlite.org/omitted.html

More specifically, this bit on foreign-key constraints:

As for your error? No, not really, at least not that I can see. The fact that SQLite doesn't support referential integrity is surprising I'd say. Also no outer joins (granted, that can be accomplished in other ways.)

LoneAngelIbesu 04-13-2009 03:10 AM

Quote:

Originally Posted by Damix2 (Post 1483393)
As for your error? No, not really, at least not that I can see. The fact that SQLite doesn't support referential integrity is surprising I'd say. Also no outer joins (granted, that can be accomplished in other ways.)

Using requestsql2() pointing to the "sqlite_master" database results in no error. However, when trying to access the data, nothing is outputted.
PHP Code:

  sql.execute(
    
"CREATE TABLE IF NOT EXISTS shops ("
    
"title TEXT NOT NULL DEFAULT 'Shop', "
    
"stype TEXT NOT NULL DEFAULT 'Server', "
    
"owner TEXT NOT NULL DEFAULT 'Unknown', "
    
"kerou INT NOT NULL DEFAULT 5000, "
    
"desc TEXT NOT NULL DEFAULT 'Description text.', "
    
"createtime INT NOT NULL DEFAULT CURRENT_TIMESTAMP)"false);
  
requestsql2("sqlite_master",
    
"CREATE TABLE IF NOT EXISTS shops.items ("
    
"arch TEXT NOT NULL DEFAULT 'Unknown', "
    
"price INT DEFAULT 0, "
    
"stock INT DEFAULT 0)"false);
  
sql.execute(format(
    
"INSERT INTO shops VALUES('%s','%s','%s',%s,'%s',%s)",
    
"Test Shop""Server""NPCServer"5000"Description text.""CURRENT_TIMESTAMP"), false);
  
requestsql2("sqlite_master",
    
"INSERT INTO shops.items VALUES('arch1',1000,10)"false);
  
temp.sqltest sql.execute("SELECT * FROM shops WHERE title='Test Shop'"true);
  for(
temp.rowtemp.sqltest.rows) {
    echo(
"Title: " temp.row.title);
    for(
temp.irowtemp.row.items) { //I've also tried temp.row.items.rows
      
echo(temp.irow.arch ": " temp.irow.stock);
    }
  } 

The execute() command is a simple error checker:
PHP Code:

public function execute(queryisreq) {
  
temp.req requestsql(temp.querytemp.isreq);
  if (
temp.req.error != "") {
    echo(
"SQL Error: " temp.req.error);
    echo(
"      Query: " temp.query);
  }
  if (
temp.isreq && !temp.req.completed &&
      !
waitfor(temp.req"onReceiveData"5))
    return 
null;
  return 
temp.req;



Inverness 04-13-2009 07:22 AM

Quote:

Originally Posted by LoneAngelIbesu (Post 1483395)
Using requestsql2() pointing to the "sqlite_master" database results in no error.

sqlite_master is a table not a database. Stefan said in an earlier post that requestsql2() is for choosing between mysql and sqlite databases, so at the moment it is useless to you and you shouldn't use it.

select tbl_name from sqlite_master where type='table' order by tbl_name

That would list all tables in the SQLite database.


All times are GMT +2. The time now is 01:54 PM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Copyright (C) 1998-2019 Toonslab All Rights Reserved.