Multiple SQLite Databases

[cbk1994]

It would be awesome to have multiple SQLite databases. An example:

On Era, we use (or will use) SQLite for all logging, including player chat, item drops, trades, staff powers, etc.

We would eventually want to make a daily backup by script (which is possible with the coming NPC-server update) of the "main" database. This "main" database stores data such as the items players have, support center tickets, and bank accounts. The database would probably never get over 5-10 MB in size. We would then want to have a "logging" database, which holds all of our logs. This database could easily get to be 500+ MB, making it impractical to backup routinely.

Right now, our database on Era is approximately 400 MB, most of which is constituted by chat logs for the past 2 months. This makes it nearly impossible for us to back up the database at all. With multiple databases, we could easily contradict this problem.

[fowlplay4]

So requestsql2 doesn't let you do this?

[cbk1994]

Originally Posted by fowlplay4

So requestsql2 doesn't let you do this?

I haven't heard of that, I'll take a look

EDIT: It doesn't seem to work.

[cbk1994]

Bump

[SKJF91]

How's about giving people privacy, and not logging everyone's chat...

[cbk1994]

Originally Posted by SKJF91

How's about giving people privacy, and not logging everyone's chat...

That's not the point. Even without chat logs, it will easily fill with item logs, trade logs, and item data.

[SKJF91]

Originally Posted by cbk1994

That's not the point. Even without chat logs, it will easily fill with item logs, trade logs, and item data.

You're over doing SQL, there is no reason to save all this in sql. Logs should be saved via savelog/2.

[Chompy]

Originally Posted by cbk1994

That's not the point. Even without chat logs, it will easily fill with item logs, trade logs, and item data.

You have some functionality to map out an item's path via trade/item logs? If not, why are you using SQL? :s

[SKJF91]

Originally Posted by Chompy

You have some functionality to map out an item's path via trade/item logs? If not, why are you using SQL? :s

hah, I was going to mention something like that but I figured no one on graal would waste their time to thoroughly think out a way to do this because of stacked items. (where even if you have 25 uzi ammo's, each ammo would have an id 1-25 and although they'd stack, they still could be traceable)

[cbk1994]

There are plenty of good reasons to use SQL for logging, but either way, that's not the purpose of this thread, and I'd appreciate it if this thread isn't derailed (make another thread?) since I'd like to see this request implemented.

[SKJF91]

Originally Posted by cbk1994

There are plenty of good reasons to use SQL for logging, but either way, that's not the purpose of this thread, and I'd appreciate it if this thread isn't derailed (make another thread?) since I'd like to see this request implemented.

(i did see your post before you edited it by the way, learn to navigate through a text file since it is organized by time. Use Ctrl+F to find a time and item id, or player account - and rename the file every week or so to make it less of a hassle to navigate through)

We're simply giving you ways to be more efficient, and remember - Eurocenter doesn't exactly have unlimited hard drive space and although a gigabyte isn't really much, Era also shares it's computer with other servers (and most-likely other applications as well). Logging every item trade, and chats from players is outrageous (especially since chat-logs is evading peoples privacy, and the logs wouldn't be cleared if stored in a database so they would eventually become very large, very quick )

[fowlplay4]

Zodiac's logs are very inefficient, we have a 800MB Masterlog of Item Drops, if they were worried about hard drive space (It's so cheap these days anyway) they would have told us about it, so I'm going to go on a whim and say that it's hardly an issue.

I would love to switch Zodiac's logging system over to SQL, and having a logs database file separate from the actual system's just makes sense especially when combined with easy-to-use GUI, making it easier for Staff as a whole.

[Crow]

Kinda off topic, but somewhat related:
http://forums.graalonline.com/forums...ad.php?t=85997

[cbk1994]

Bump because this is quite important.

[Mark Sir Link]

you can't just backup certain tables based on prefix?

[fowlplay4]

Originally Posted by Mark Sir Link

you can't just backup certain tables based on prefix?

The main purpose is to have a backup of the actual .db file, they're currently stored in the one main.db, obviously we can do this by script (someone should write a database import/export from/to xml) but it'd be easier just to specify the database file and just have to download/upload it into the file manager.

[Admins]

Support for multiple sqlite databases has been added for private and hosted playerworlds. If it's working fine then we can copy it to the other servers. You need to restart the server to enable it.

Server option:
database=dbname,dbfile
First parameter: the name of the database which will be used for requestsql2 (so that you can change the associated file name without breaking scripts)
Second parameter: the filename, cannot contain path names and without extension, will be stored in databases/dbfile.db
Note: you can add several custom databases, it will tell you when a database has been closed because you have removed the configuration (can be used to securely close a database).

Script usage:
requestsql2(dbname, query, wantresult);

Overwriting the default database configuration:
database=default,main_new

The default database will be used for requestsql(query, wantresult).

The system still supports external mysql connections but they cannot be configured this way (needs my help). This is to prevent that the user name and password are shown in the server options.

[LoneAngelIbesu]

Originally Posted by Stefan

Server option:
database=dbname,dbfile
First parameter: the name of the database which will be used for requestsql2 (so that you can change the associated file name without breaking scripts)
Second parameter: the filename, cannot contain path names and without extension, will be stored in databases/dbfile.db
Note: you can add several custom databases, it will tell you when a database has been closed because you have removed the configuration (can be used to securely close a database).

So, would this be correct?
database=db1,db1file,db2,db2file...

[cbk1994]

Excellent, thanks Stefan

I'll try it out later today

Also, would there be any advantages to MySQL over SQLite (besides the extra features it brings, which are mostly irrelevant)? I assume it would be better since it's multi-threaded, but it might be harder to set up or maintain?

[Crow]

Originally Posted by LoneAngelIbesu

So, would this be correct?
database=db1,db1file,db2,db2file...

I assume that you need one line for each new database.

[WhiteDragon]

Originally Posted by cbk1994

Also, would there be any advantages to MySQL over SQLite (besides the extra features it brings, which are mostly irrelevant)? I assume it would be better since it's multi-threaded, but it might be harder to set up or maintain?

The biggest thing would requiring an external server to be up 100% of the time the NPC Server is up to provide a reliable gaming experience (or statistics, whatever you are using it for).

A positive of MySQL would be considerably faster queries, but honestly no application of databases in Graal would require anything past what SQLite offers. Regarding concurrency, SQLite does support that with the coming of v3, its ACIDity and therefore more complex locking structure. MySQL still trumps it, but really not important for Graal.

I wouldn't recommend doing this.

[fowlplay4]

From what I've heard from Stefan there's still an option he can flick on for SQLite to make it faster (multi-threading or something, I don't remember).

[cbk1994]

Originally Posted by WhiteDragon

The biggest thing would requiring an external server to be up 100% of the time the NPC Server is up to provide a reliable gaming experience (or statistics, whatever you are using it for).

A positive of MySQL would be considerably faster queries, but honestly no application of databases in Graal would require anything past what SQLite offers. Regarding concurrency, SQLite does support that with the coming of v3, its ACIDity and therefore more complex locking structure. MySQL still trumps it, but really not important for Graal.

I wouldn't recommend doing this.

I was referring to a locally-hosted MySQL server; from what I understand this is possible on all NPC-servers, but Stefan has to enable it for it to work.

[Admins]

Mysql requires setup work so it can only done by us for special purposes. I was referring to enable external databases like WhiteDragon said.
SQLite is pretty fast, at least with the "PRAGMA synchronous=OFF" query. Also right now it's instantly returning (if we use threads then you need to use waitfor()), and with the new serveroption you can easier use several databases and can easier backup things.
The advantage of mysql for me is that it's easier to maintain, you can easier update the database structure and the queries have more possibilities.

[cbk1994]

At first glance it seems to work fine

(and yes, each database needs its own line)

EDIT:
Is it possible to get a list of all configured databases? serveroptions.database only returns the one configured last in server options.

[cbk1994]

Definitely working, and very well. Only thing I can see it needs is a way to get the name and file name of all configured databases.

I'm in the process of fixing up SQL Explorer to work with multiple databases now

[Inverness]

Nicely done, Stefan. I just wish the client would get the same love.

[Crow]

Originally Posted by Stefan

Also right now it's instantly returning (if we use threads then you need to use waitfor()), and with the new serveroption you can easier use several databases and can easier backup things.
The advantage of mysql for me is that it's easier to maintain, you can easier update the database structure and the queries have more possibilities.

C-c-c-ombo!

[Inverness]

Originally Posted by Crow

C-c-c-ombo!

Quickly everyone, get this man a thesaurus!

[_Zelph]

Works great on Unholy Dev. It would be great to have it on UN as soon as possible, as I'm about to release some new systems that would greatly benefit from this

[Admins]

Will try to update the classic and gold servers end of this week.

[cbk1994]

Originally Posted by cbk1994

Only thing I can see it needs is a way to get the name and file name of all configured databases.

bump

[xAndrewx]

Chris, with your kewl tool it supports multiple databases right, but you're mentioning something in this thread with SO, could you explain for me. thanks