Most Up To Date Pokémon Simulator

Pokémon Online is the most popular, accurate, and complete Pokémon battle simulator out there. Open source, it's currently available in 11 languages, on your computer or your Android device. It hosts more than a million battles per month. Download the latest version and enjoy competitive battles across all six generations in real time against trainers from around the world!

Download
v 2.4.1


Custom Database-Script -interface

Discussion in 'Developer's Den' started by Lamperi, Mar 11, 2011.

  1. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    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.

    [secret](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
    [/secret]

    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:
    Code (text):
    1.  
    2. {
    3.   'where':
    4.   {
    5.     'somefield': 'has_value',
    6.     'somenumber': ['<', 'othernumber'],
    7.   },
    8.   'limit': 5,
    9.   'order by': [['somefield', 'asc'], ['otherfield', 'desc']]
    10. }
    11.  
    How to allow other operators

    Any input is appreciated!
    Last edited: Mar 15, 2011
  2. coyotte508

    coyotte508 Administrator Administrator

    Messages:
    6,304
    Sounds real cool. Remember that PO doesn't support only sqlite, there's PostGreSQL and MySQL if that matters.
  3. Mystra

    Mystra New Member

    Messages:
    1,389
    Maybe no useDatabase() just prefix those tables with something like "poscript_"? Thay way DB would be the same as PO itself.
  4. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    Sounds sensible. If we have database already opened we can use it!
  5. Mystra

    Mystra New Member

    Messages:
    1,389
    Hmm… clear table may be needed too.
  6. Mystra

    Mystra New Member

    Messages:
    1,389
    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.
  7. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    I guess the interface will grow when we have more needs ^^
  8. Mystra

    Mystra New Member

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

    Code (cpp):
    1.  
    2. #ifndef SCRIPTDB_H
    3. #define SCRIPTDB_H
    4.  
    5. #include <QObject>
    6. #include <QtScript>
    7. #include "server.h"
    8.  
    9. class ScriptDB : public QObject
    10. {
    11. Q_OBJECT
    12. public:
    13.     explicit ScriptDB(Server *s);
    14.     Q_INVOKABLE void ensureTable(const QString &tableName, const QScriptValue &properties);
    15.  
    16. signals:
    17.  
    18. public slots:
    19.  
    20. private:
    21.     Server *myserver;
    22.     QString makeFields(const QScriptValue &properties);
    23. };
    24.  
    25. #endif // SCRIPTDB_H
    26.  
    Code (cpp):
    1.  
    2. #include <QSqlQuery>
    3. #include "scriptdb.h"
    4. #include "sql.h"
    5. #include <QRegExp>
    6.  
    7. ScriptDB::ScriptDB(Server *s) : myserver(s)
    8. {
    9. }
    10.  
    11. // Properties should be a JS hash. This function will iterate over all properties
    12. // and will use property name is field name and property valus as type.
    13. // Type used as is so you must use SQL types, like varchar(255), int, etc.
    14. void ScriptDB::ensureTable(const QString &tableName, const QScriptValue &properties)
    15. {
    16.     QSqlQuery q;
    17.     QString fields = makeFields(properties);
    18.     if (fields.isEmpty()) {
    19.         myserver->print("ensureTable: cannot create table without any fields.");
    20.         return;
    21.     }
    22.     switch (SQLCreator::databaseType) {
    23.     case SQLCreator::SQLite:
    24.         q.prepare("create table :tablename (" + fields + "id integer primary key autoincrement)");
    25.         break;
    26.     case SQLCreator::MySQL:
    27.         q.prepare("create table :tablename (" + fields + "id integer auto_increment, primary key(id))");
    28.         break;
    29.     case SQLCreator::PostGreSQL:
    30.         q.prepare("create table :tablename (" + fields + "id serial, primary key(id))");
    31.         break;
    32.     default:
    33.         throw QString("Using a not supported database");
    34.     }
    35.     q.bindValue(":tablename", "poscript_" + tableName);
    36.     if (!q.exec()) {
    37.         myserver->print(QString("ensureTable: creation failed. %1").arg(q.lastError().text()));
    38.     }
    39. }
    40.  
    41. QString ScriptDB::makeFields(const QScriptValue &properties)
    42. {
    43.     if (!properties.isObject()) return QString();
    44.     QString result = "";
    45.     QScriptValueIterator it(properties);
    46.     QRegExp rx("[a-z_]+");
    47.     int pos = 0;
    48.  
    49.     while (it.hasNext()) {
    50.         it.next();
    51.         QString name = it.name().toLower();
    52.         QString value = it.value().toString();
    53.         if ((name != "id") && (rx.exactMatch(name))) {
    54.             result += QString("%1 %2, ").arg(name, value);
    55.         }
    56.     }
    57.     return result;
    58. }
    59.  
  9. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    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
    Code (text):
    1.  
    2. QRegExp rx("[a-z_]+");
    3. if(!rx.exactMatch(tablename) return;
    4. ...
    5. q.prepare("create table if not exists %1 (" ... ).arg(tablename);
    6.  
    Last edited: Mar 15, 2011
  10. Mystra

    Mystra New Member

    Messages:
    1,389
    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.
    Code (javascript):
    1.  
    2. ({
    3.     serverStartUp: function() {
    4.         sys.db.ensureTable("test", { "name": "varchar(255)", "rating": "int" });
    5.     }
    6. })
    7.  
  11. coyotte508

    coyotte508 Administrator Administrator

    Messages:
    6,304
    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)
  12. Mystra

    Mystra New Member

    Messages:
    1,389
    Hmm… should I do startTransaction() and endTransaction()? It would be good to know how do I do that with current code we have.
  13. coyotte508

    coyotte508 Administrator Administrator

    Messages:
    6,304
    Code (text):
    1.         QSqlDatabase::database().transaction();
    Code (text):
    1.  
    2.         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)
  14. Mystra

    Mystra New Member

    Messages:
    1,389
    Hmm… maybe I'll ditch it for now then. If it will be needed I'll think about it later.
  15. Mystra

    Mystra New Member

    Messages:
    1,389
    Pushed sys.db.insert(table_name, properties) into my branch. Since I haven't found any escaping function I did
    Code (cpp):
    1.  
    2. valueData.replace("'", "''");
    3.  
    Will this be enough I wonder.

    To test:
    Code (javascript):
    1.  
    2. ({
    3.     serverStartUp: function() {
    4.         sys.db.ensureTable("test", { "name": "varchar(255)", "rating": "int" });
    5.         sys.db.insert("test", { "name": "KIIIING COMBO!", "rating": 5 });
    6.         sys.db.insert("test", { "name": "AWESOME COMBO!", "rating": 4.5 });
    7.         sys.db.insert("test", { "name": "1\\n2", "rating": 3 });
    8.     }
    9. })
    10.  
    Code (text):
    1.  
    2. : sqlite3 pokemon
    3. SQLite version 3.7.3
    4. Enter ".help" for instructions
    5. Enter SQL statements terminated with a ";"
    6. sqlite> .dump poscript_test
    7. PRAGMA foreign_keys=OFF;
    8. BEGIN TRANSACTION;
    9. CREATE TABLE poscript_test (id integer primary key autoincrement, name varchar(255), rating int);
    10. INSERT INTO "poscript_test" VALUES(1,'KIIIING COMBO!',5);
    11. INSERT INTO "poscript_test" VALUES(2,'AWESOME COMBO!',4.5);
    12. INSERT INTO "poscript_test" VALUES(3,'1\n2',4.5);
    13. COMMIT;
    14.  
    If anyone is willing to do any function drop message here… VOLUNTEEERSSS!
  16. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    http://doc.qt.nokia.com/latest/qsqldriver.html#escapeIdentifier

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

    Mystra New Member

    Messages:
    1,389
    Well, it says "identifier", while I'm escaping data, so I don't know :|
  18. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    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.
    Last edited: Mar 16, 2011
  19. Mystra

    Mystra New Member

    Messages:
    1,389
    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.
  20. Mystra

    Mystra New Member

    Messages:
    1,389
    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
    Code (text):
    1. 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.
  21. Mystra

    Mystra New Member

    Messages:
    1,389
    Any progress? I'm not feeling well enough to write code yet. Always sleepy at evening %)
  22. Mystra

    Mystra New Member

    Messages:
    1,389
    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).
    Code (cpp):
    1.  
    2. class PORecordPrototype : public QObject, public QScriptable
    3. {
    4.     Q_OBJECT
    5. public:
    6.     PORecordPrototype(QObject *parent = 0) : QObject(parent) {};
    7.     Q_INVOKABLE void save();
    8. private:
    9. };
    10.  
    11. void PORecordPrototype::save()
    12. {
    13.     // thisObject().blahblah
    14. }
    15.  
    Last edited: Apr 16, 2011
  23. Blastcore

    Blastcore Developer

    Messages:
    763
    I hate you, i ahd this idea from some weeks ago! :(
  24. Lamperi

    Lamperi I see what you did there Super Moderator

    Messages:
    2,646
    Why not contribute? There is a lot to do for this project.
  25. Blastcore

    Blastcore Developer

    Messages:
    763
    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).
  26. Mystra

    Mystra New Member

    Messages:
    1,389
    Base is there. Look into Server/scriptdb.h and .cpp. Although what needs to be done isn't exactly easiest ones to do.
  27. Blastcore

    Blastcore Developer

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

    Code (CPP):
    1. QString queryString = QString("INSERT INTO poscript_") + tableName + QString("(") + fields
    2.                           + QString(") VALUES (") + values + QString(")");
    3.  
    will have a "poscript_" at start? Sorry, i suck on MySQL :D
  28. Mystra

    Mystra New Member

    Messages:
    1,389
    It is a table prefix.
  29. Pokemonexperte-Martin

    Pokemonexperte-Martin New Member

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

  30. Mystra

    Mystra New Member

    Messages:
    1,389
    That was already done.
  31. Mystra

    Mystra New Member

    Messages:
    1,389
    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.
    Last edited: Apr 16, 2011
  32. Mystra

    Mystra New Member

    Messages:
    1,389
    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:
    Code (text):
    1.  
    2. ({
    3.   serverStartUp: function() {
    4.     sys.db.ensureTable("test", { "name": "varchar(255)", "rating": "int" });
    5.     //sys.db.insert("test", { "name": "KIIIING COMBO!", "rating": 5 });
    6.     //sys.db.insert("test", { "name": "AWESOME COMBO!", "rating": 4.5 });
    7.     var x = sys.db.findBy("test", "name", "AWESOME COMBO!");
    8.     var d = x.data;
    9.     for (var i = 0; i < d.length; ++i) {
    10.       var hash = d[i];
    11.       for (var k in hash) {
    12.         sys.sendAll(k + " - " + hash[k]);
    13.       }
    14.     }
    15.   }
    16. })
    17.  
    Check and try code before merging.
  33. Mystra

    Mystra New Member

    Messages:
    1,389
    Pushed protections to properties and 'id'. Should work…
  34. Blastcore

    Blastcore Developer

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

    Mystra New Member

    Messages:
    1,389
    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).
  36. Mystra

    Mystra New Member

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

    Blastcore Developer

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

    Mystra New Member

    Messages:
    1,389
    Nobody needs it, so it's stall.
  39. Mystra

    Mystra New Member

    Messages:
    1,389
    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).
  40. Mystra

    Mystra New Member

    Messages:
    1,389
    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.
    Last edited: Dec 3, 2011

Share This Page