![]() |
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. |
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. |
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. |
Ok added SQLite support, when using the requestsql-commands then the default database is now a sqlite database
PHP Code:
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 |
Quote:
|
Quote:
I can't wait to try it out. |
Quote:
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. |
PHP Code:
|
Quote:
|
Quote:
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:
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. |
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:
Update: the new npcserver is now installed on the private/hosted playerworld computers, restart it to try. |
Quote:
I'm also not going to be using SQL unless I can back the whole database up at least twice a day. |
Pardon my ignorance, but what is the advantage of using a database (SQLite) to store information as opposed to text files or database NPCs?
|
Quote:
|
Quote:
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. |
*Simmering*
|
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.
|
Quote:
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. |
Quote:
|
Quote:
|
Quote:
And no, SQLite has not been added to Valikorlia... and 2 database backups per day is a bit unnecessary. |
I see. Good to know, thanks.
|
Quote:
|
Quote:
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. |
Quote:
Quote:
|
Quote:
Quote:
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? :( |
Ok the new npcserver has been uploaded to all machines, need to be restarted manually though.
|
Quote:
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. |
The database is databases/main.db
Use "as" for renaming columns of the result. You can also access the result as array, like rows[][] |
Quote:
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:
|
Well for errors I gave the suggestion to write a wrapper function:
PHP Code:
|
Quote:
Also, I don't think you specified what the boolean value was for in requestsql() and requestsql2(). |
Quote:
Quote:
|
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:
Quote:
|
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. |
Inverness, string.escape() is made for that. It escapes ' and \ according to the SQL standard / pascal.
|
Quote:
http://sqlite.org/omitted.html More specifically, this bit on foreign-key constraints: Quote:
|
Quote:
|
Quote:
PHP Code:
PHP Code:
|
Quote:
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.