Custom Database-Script -interface

[Lamperi*]

The Problem

Due to biggest PO servers already having hundreds of players, running scripts is taking more and more time. We need more efficient scripts.

A good start is to stop using {sys.saveVal, sys.removeVal, sys.getVal} functions to speed up execution of the server. However, manually reading and writing files in limited javascript library is kind of pain.

Using databases has been flashed on forums and servers couple of times already, and that is what I think we should start developing further.

In order to make people really use databases in their scripts instead of sys.*Val-functions, the interface needs to be clear. We brainstormed a bit already with Mystra on server chat about possible functions.

(20:54) Lamperi: Hi
(20:54) Mystra: hi
(20:54) Lamperi: we have nowhe getValKeys
(20:54) Mystra: when Coyo will merge it
(20:54) Lamperi: Would it be faster to have a function to lookup many keys at the same time?
(20:55) Lamperi: If has to create QSettings only once
(20:55) Lamperi: It*
(20:55) Lamperi: And it probably does all the reading and writing in memory (not sure though)
(20:55) Mystra: wait a min. fighting ^^
(20:55) Lamperi: kk
(20:55) Lamperi: good luck
(20:56) Mystra: it's CC (^ ^)
(20:56) Lamperi: well
(20:56) Lamperi: you sure need luck
(20:56) Lamperi: ^^
(20:57) Mystra: yeah XD
(21:05) Mystra: Won O.o
(21:05) Mystra: Hmm...
(21:07) Lamperi: Since they are stored in the ini-file
(21:07) Lamperi: It's kinda easy to parse it
(21:07) Lamperi: Without Qt's help
(21:07) Lamperi: I just though it would be cleaner way
(21:08) Lamperi: And the thing I'm converting, is to have all mutes / mafiabans in memory, and only read the file on script's init and write only on correspond event
(21:08) Mystra: There is an idea to dump it all into DB
(21:08) Lamperi: I know
(21:09) Lamperi: In memory it would still be faster... it would it?
(21:09) Lamperi: If javascript's object were used as hashes (don't know what datastructure they are using, but probably at least log(n))
(21:10) Lamperi: If we convert them into SQL(lite)-databases later
(21:10) Lamperi: Only those parts which write and read those values need to be rewritten
(21:10) Mystra: Hmm...
(21:11) Mystra: Well, you could read data from files manually. Writing them is still suboptimal if done often
(21:11) Mystra: Reading once on start I mean
(21:12) Lamperi: You would like a cache?
(21:12) Mystra: It would be like a cache, yes
(21:12) Mystra: However I'm not sure what to do with writing
(21:12) Lamperi: Say, 10 things on cache before writing, or hourly writing
(21:12) Mystra: Hmm... that could work
(21:13) Lamperi: but even then using saveVal defeats the purpose
(21:13) Lamperi: Because it opens the file every time the function is called
(21:14) Mystra: You'll need to overwrite a file anyway. Although If you do reading manually you can just as well write manually too
(21:14) Lamperi: Yeah...
(21:14) Lamperi: Besides, I already did it to rangebans (lol why?), so I can just use more general approach
(21:14) Lamperi: writeArray(); readArrayFromFile
(21:15) Mystra: It would be nice to have those things in DB soon. I'm not terribly familiar with databases though...
(21:16) Lamperi: The best thing would be to have automatic function to convert javascript objects into SQL-rows
(21:16) Lamperi: The ruby-on-fails-way
(21:16) Lamperi: Active object
(21:16) Lamperi: ruby-on-rails* lol
(21:17) Mystra: I love Ruby :3 Too bad QtScript can only do JS :|
(21:18) Mystra: There is a Qross library (non-KDE version of Kross) but I don't know it's current status. And it's way too late anyway
(21:18) Lamperi: sys.db.ensureTable('mutes'. {'ip': 'char(13)', 'time': 'integer'})
(21:19) Lamperi: sys.db.getValues('mutes') // retuns a list of object with parameters ip and time
(21:19) Lamperi: lol
(21:19) Lamperi: one can always dream about
(21:19) Mystra: That would be nice indeed :3
(21:20) Lamperi: Would ease developping a lot :P
(21:20) Mystra: It shouldn't be that hard, actually. Although someone need to do that.
(21:21) Lamperi: We should come up with the interface first
(21:21) Mystra: Yeah
(21:21) Lamperi: Or use some existing interface to active objects
(21:21) Mystra: Are you sure getValues() should be done? As DBs can query more or less fine
(21:22) Lamperi: Well, at least the version with WHERE in there
(21:22) Lamperi: sys.db.select({'ip': '84.20.150.21'})
(21:22) Lamperi: maybe allow empty select too
(21:22) Mystra: Hmm... maybe
(21:22) Lamperi: which would select all
(21:23) Mystra: Write this thing somewhere in dev forum, so we'll not forget it XD
(21:23) Lamperi: Ok ^^, maybe I'll put it into my dev topic first
(21:23) Lamperi: Or..
(21:23) Lamperi: Separate topic?
(21:23) Mystra: Hmm... make separate one. It will be easier to discuss if needed.
(21:24) Lamperi: Ok, I'll make one

Solutions & Suggestions

One thing that appeals to me are active objects. You can fetch them from the database, read their properties and you can write them into database with ease.

This thread is made to discuss about database interface before actually rushing into implementing one.

Random suggestions:

• sys.db.useDatabase(sqlitefile), which selects a database
• sys.db.ensureTable(tablename, properties), which would create if missing a table with shown properties
• sys.db.select(tablename, properties), where properties would include where-clause, limits, order
• select would return a list of objects, which have common .prototype in ActiveObject. ActiveObject would have member-function save() to save data back to database.
• New! sys.db.insert(tablename, properties)
• New! sys.db.update(tablename, properties) Even though returned object would have .save
• New! sys.db.delete(tablename, properties)
• sys.db.save(objectlist) would allow saving many objects at the same time

In .ensureTable properties should contain Javascript hash, which has keys as field names and values as field types. Currently fieldnames are database specific.

.ensureTable should use "create table if not exists", for it to be able to be called everytime script is initialized.

In .select and .delete, properties could be like:

{
  'where':
  {
    'somefield': 'has_value',
    'somenumber': ['<', 'othernumber'],
  },
  'limit': 5,
  'order by': [['somefield', 'asc'], ['otherfield', 'desc']]
}

How to allow other operators

Any input is appreciated!

[coyotte508**]

Sounds real cool. Remember that PO doesn't support only sqlite, there's PostGreSQL and MySQL if that matters.

[Mystra]

Maybe no useDatabase() just prefix those tables with something like "poscript_"? Thay way DB would be the same as PO itself.

[Lamperi*]

Sounds sensible. If we have database already opened we can use it!

[Mystra]

Hmm… clear table may be needed too.

[Mystra]

Soooo… if no one is going to add anything someone should start doing it.

Maybe I'll even try to do it when I have free time although my SQL knowledge is pretty average.

[Lamperi*]

I guess the interface will grow when we have more needs ^^

[Mystra]

Started. Tried doing ensureTable(). Does not quite work… "Parameter count mismatch" :\ Tried with SQLite. Any ideas?

#ifndef SCRIPTDB_H
#define SCRIPTDB_H

#include <QObject>
#include <QtScript>
#include "server.h"

class ScriptDB : public QObject
{
Q_OBJECT
public:
    explicit ScriptDB(Server *s);
    Q_INVOKABLE void ensureTable(const QString &tableName, const QScriptValue &properties);

signals:

public slots:

private:
    Server *myserver;
    QString makeFields(const QScriptValue &properties);
};

#endif // SCRIPTDB_H

#include <QSqlQuery>
#include "scriptdb.h"
#include "sql.h"
#include <QRegExp>

ScriptDB::ScriptDB(Server *s) : myserver(s)
{
}

// Properties should be a JS hash. This function will iterate over all properties
// and will use property name is field name and property valus as type.
// Type used as is so you must use SQL types, like varchar(255), int, etc.
void ScriptDB::ensureTable(const QString &tableName, const QScriptValue &properties)
{
    QSqlQuery q;
    QString fields = makeFields(properties);
    if (fields.isEmpty()) {
        myserver->print("ensureTable: cannot create table without any fields.");
        return;
    }
    switch (SQLCreator::databaseType) {
    case SQLCreator::SQLite:
        q.prepare("create table :tablename (" + fields + "id integer primary key autoincrement)");
        break;
    case SQLCreator::MySQL:
        q.prepare("create table :tablename (" + fields + "id integer auto_increment, primary key(id))");
        break;
    case SQLCreator::PostGreSQL:
        q.prepare("create table :tablename (" + fields + "id serial, primary key(id))");
        break;
    default:
        throw QString("Using a not supported database");
    }
    q.bindValue(":tablename", "poscript_" + tableName);
    if (!q.exec()) {
        myserver->print(QString("ensureTable: creation failed. %1").arg(q.lastError().text()));
    }
}

QString ScriptDB::makeFields(const QScriptValue &properties)
{
    if (!properties.isObject()) return QString();
    QString result = "";
    QScriptValueIterator it(properties);
    QRegExp rx("[a-z_]+");
    int pos = 0;

    while (it.hasNext()) {
        it.next();
        QString name = it.name().toLower();
        QString value = it.value().toString();
        if ((name != "id") && (rx.exactMatch(name))) {
            result += QString("%1 %2, ").arg(name, value);
        }
    }
    return result;
}

[Lamperi*]

I can't figure out anything else than a bug / oversight in Qt's SQLITE-driver.

Since boundValues are internally QVariant, I guess they can only be used in INSERT-statements or WHERE-clauses, where driver knows the type of what it is binding.

My suggestion is to use

QRegExp rx("[a-z_]+");
if(!rx.exactMatch(tablename) return;
...
q.prepare("create table if not exists %1 (" ... ).arg(tablename);

[Mystra]

Ah… thanks. I've read about it once, but forgot since I haven't done it before.

Ok, I've pushed initial code to my branch. It has only ensureTable() for now so you can't do much besides this.

Testers, comments, suggestions, whateverelsers are welcomed to check or at least read it

This code will create poscript_test table with id (automatic), name, and rating fields.

({
    serverStartUp: function() {
        sys.db.ensureTable("test", { "name": "varchar(255)", "rating": "int" });
    }
})

[coyotte508**]

ok cool

The rest should be quite easy as the base's already here ^^

Thanks. (And from the code in your posts it all seems fine)

[Mystra]

Hmm… should I do startTransaction() and endTransaction()? It would be good to know how do I do that with current code we have.

[coyotte508**]

        QSqlDatabase::database().transaction();

        QSqlDatabase::database().commit();

However due to the sensible nature of this you'd need to have transactions logged, meaning that if someone doesn't close a transaction with scripts the script needs to know it and close the transaction at the end of the event or stack of events (same for webCall and callLater)

[Mystra]

Hmm… maybe I'll ditch it for now then. If it will be needed I'll think about it later.

[Mystra]

Pushed sys.db.insert(table_name, properties) into my branch. Since I haven't found any escaping function I did

valueData.replace("'", "''");

Will this be enough I wonder.

To test:

({
    serverStartUp: function() {
        sys.db.ensureTable("test", { "name": "varchar(255)", "rating": "int" });
        sys.db.insert("test", { "name": "KIIIING COMBO!", "rating": 5 });
        sys.db.insert("test", { "name": "AWESOME COMBO!", "rating": 4.5 });
        sys.db.insert("test", { "name": "1\\n2", "rating": 3 });
    }
})

: sqlite3 pokemon
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump poscript_test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE poscript_test (id integer primary key autoincrement, name varchar(255), rating int);
INSERT INTO "poscript_test" VALUES(1,'KIIIING COMBO!',5);
INSERT INTO "poscript_test" VALUES(2,'AWESOME COMBO!',4.5);
INSERT INTO "poscript_test" VALUES(3,'1\n2',4.5);
COMMIT;

If anyone is willing to do any function drop message here… VOLUNTEEERSSS!

[Lamperi*]

Pushed sys.db.insert(table_name, properties) into my branch. Since I haven't found any escaping function I did

valueData.replace("'", "''");

Will this be enough I wonder.

http://doc.qt.nokia.com/latest/qsqld...capeIdentifier

I guess this could be used? QSqlDriver is used internally by QSqlQuery usually but I think you could do escaping yourself.

[Mystra]

Well, it says "identifier", while I'm escaping data, so I don't know :|

[Lamperi*]

Yeah, should have used it in ensureTable, since it has loads of identifiers :X


// Ok, .insert has now possible SQL injection. I guess we should use .bindValue() on data which can come from users.

[Mystra]

Any examples of this possibility?

Bind value is possible, it's just I'll need to iterate over values twice as number of items in this "hash" is unknown and inaccessible via Qt methods directly. At least I haven't found any. And there must be exact number of "?" for substitutions.

Well, I could go with double iteration if that's ok.

[Mystra]

Ok, updated ensureTable() to use bound values.

Also, keep in mind that:

1) SQLite does not enforce type. It will store 3.5 as 3.5 even if the field is int type. Normal DBs will not do that and rounding will occur.

2) When using PostgreSQL if you get warning such as "nonstandard use of \\ in a string literal ... HINT: Use the escape string syntax for backslashes, e.g., E'\\'." you can either

a) do nothing and take it;

b) write the following in postgresql.conf

standard_conforming_strings = on

and restart your PostgreSQL server. This will escape all "\" characters in queries. This option may be default in future versions (or so they say).

HOWEVER, this will cause (AFAIK) inconsistencies with SQLite as it WILL interpret them as escape characters. So…

c) there is also escape_string_warning option in postgresql.conf. But behavior may change in future versions…

Conclusion: I wonder whether there is a bug in Qt's driver and whether it was fixed in 4.7…

3) When using PostgreSQL calling ensureTable() can print error message that a table already exists. It is expected behavior.

4) PostgreSQL will also warn this VACCUM cannot be done on some special tables that aren't own by current DB user. It is normal. Whether I can do anything about it is currently unknown. PostgreSQL also has auto-vacuum daemon, so doing this may not be needed at all. Maybe I should add an option to turn off auto-vacuum.

[Mystra]

Any progress? I'm not feeling well enough to write code yet. Always sleepy at evening %)

[Mystra]

SO? No one?

To make a prototype object to be set with setPrototype() on JS object in Qt you'll need to do something like this (AFAIU documentation).

class PORecordPrototype : public QObject, public QScriptable
{
    Q_OBJECT
public:
    PORecordPrototype(QObject *parent = 0) : QObject(parent) {};
    Q_INVOKABLE void save();
private:
};

void PORecordPrototype::save()
{
    // thisObject().blahblah
}

[Blastcore]

I hate you, i ahd this idea from some weeks ago!

[Lamperi*]

I hate you, i ahd this idea from some weeks ago!

Why not contribute? There is a lot to do for this project.

[Blastcore]

I will, of course. I need to get the source again, but it is in the source yet or no? Oh, i'm also going to do again the things i had to do before. :P

I hope this will help me with my RPG. :P (Well, not now).

[Mystra]

Base is there. Look into Server/scriptdb.h and .cpp. Although what needs to be done isn't exactly easiest ones to do.

[Blastcore]

No clue of how MySQL works. But for every "Data" or however you want to call it (I suck at SQL ),

QString queryString = QString("INSERT INTO poscript_") + tableName + QString("(") + fields
                          + QString(") VALUES (") + values + QString(")");

will have a "poscript_" at start? Sorry, i suck on MySQL

[Mystra]

It is a table prefix.

[Pokemonexperte-Martin]

Any examples of this possibility?

Bind value is possible, it's just I'll need to iterate over values twice as number of items in this "hash" is unknown and inaccessible via Qt methods directly. At least I haven't found any. And there must be exact number of "?" for substitutions.

Well, I could go with double iteration if that's ok.

Whats the problem exactly? Maybe named parameters are a solution for you?!

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();

[Mystra]

That was already done.

[Mystra]

Pushed PORecordPrototype. Object of this type is created in ScriptDB and now can be used as a prototype for PORecord objects that will appear in future (query result).

PORecordPrototype currently has only save() that does nothing. When I'll do at least 1 query function (or someone else will do it), I'll try to implement it.

EDIT: ahem… I forgot to pass it QScriptEngine instance. Is it accessible through myserver (Script *) variable by any chance?

EDIT 2: Done with QScriptEngine passing so previous edit is now irrelevant.

[Mystra]

Done sys.db.findBy(table, key, value) - limited version of select(). Mostly for testing, but whatever. Prototype is bound to it, but invoking save() on result of sys.db.findBy() currently does nothing. I'll need to disable modification of "id" data later too.

sys.db.findBy() returns an array of hashes. Each item of this array is 1 row of data. Hash is, well, hash. field => value.

If field is not found then it's value is null.

If data cannot be retrieved sys.db.findBy() returns null. Should I change it to be empty array instead?

Also, all data is currently returned as string. Should I leave it this way? Otherwise someone will need to change that somehow. Also considering that SQLite stores only strings... maybe we didn't need to bother with field types (other than strings) at all. Any ideas?

Sample code:

({
  serverStartUp: function() {
    sys.db.ensureTable("test", { "name": "varchar(255)", "rating": "int" });
    //sys.db.insert("test", { "name": "KIIIING COMBO!", "rating": 5 });
    //sys.db.insert("test", { "name": "AWESOME COMBO!", "rating": 4.5 });
    var x = sys.db.findBy("test", "name", "AWESOME COMBO!");
    var d = x.data;
    for (var i = 0; i < d.length; ++i) {
      var hash = d[i];
      for (var k in hash) {
        sys.sendAll(k + " - " + hash[k]);
      }
    }
  }
})

Check and try code before merging.

[Mystra]

Pushed protections to properties and 'id'. Should work…

[Blastcore]

well, where you exactly set all the MySQL Data? The Database, Password, etc it's the same as SQL Options in the Menu?

[Mystra]

Yes. It's the same database, as PO one. However only tables named with "poscript_" prefix are accessible (script itself is not aware of this).

[Mystra]

Drop it if no one need it. Simple key-value storage (with multiple values per key) could be enough anyway :\

[Blastcore]

Works fine now? Anything new? This seems to be, dead. :O It's finished... what?

[Mystra]

Nobody needs it, so it's stall.

[Mystra]

Ok, dropped. New plan: either new DB file with direct SQL commands (easy, no need to code much) or something else that is non-SQL (preferable, but need to plug API to scripts, the easier the better).

[Mystra]

Alternatively, make another field in SQL DB and stuff JSON into it. Just make sure it's not too short.

EDIT: ok, also ip<->other data table too.