[cbk1994]

Originally Posted by sage_chaozu

Then what would one use a SQL lite database for? It's starting to seem like a lost cause due to performance issues. Then again this does guarantee secure data storage and manipulation. I believe this is due to SQL transactions actually locking the DB table before writing to avoid overwriting.

SQLite offers many benefits over using flatfile or DBNPCs for storage.

For example, if you have a bank, and you want to find every account with over $5000 in the bank, you can run a query "SELECT * FROM bank WHERE money > 5000 ORDER BY money DESC". The equivalent code using a DBNPC would be something like

PHP Code:

temp.found = null;
for (temp.account : this.bank.getDynamicVarNames()) {
  temp.balance = this.bank.(@ account);
  
  if (balance > 5000) {
    found.add(account);
    found[found.size() - 1].balance = balanace;
  }
}

found = found.sortByVar("balance", "float", false); 


There are many reasons to use SQLite but also as many reasons not to use it. It really depends on your usage case.

The performance issues are not that big, either.

Originally Posted by cbk1994

It depends a lot more on how you structure your system than the limits of SQL.

A quick example:

PHP Code:

function r(str) {
  echo("+" @ (timevar2 - this.start) @ ": " @ str);
}

function onCreated() {
  this.join("func_sql");
  
  req("DROP TABLE IF EXISTS test_2");
  
  // this only has to be done once
  sqliteEnableFileSynchronization("default", false);
  this.start = timevar2;
  
  // begin transaction
  
  r("Beginning transaction");
  req("BEGIN");
  
  r("Creating table");
  req("CREATE TABLE IF NOT EXISTS test_2 (account TEXT NOT NULL DEFAULT '', itemid TEXT NOT NULL DEFAULT '', quantity INT NOT NULL DEFAULT 0)");
  
  r("Creating index");
  req("CREATE INDEX IF NOT EXISTS idx_test ON test_2 (account, itemid)");
  
  temp.items = {"uzi", "handgun", "shotgun", "ak47", "medpack", "skateboard", "ammo"};
  
  r("Adding 70,000 rows to table");
  temp.c = 0;
  
  // add 70,000 rows to the table
  for (temp.item : items) {
    for (temp.i = 0; i < 10000; i ++) {
      req("INSERT INTO test_2 (account, itemid, quantity) VALUES ('" @ i @ "', '" @ item @ "', " @ int(random(1, 1000)) @ ")");
      
      if (c % this.maxlooplimit == 0) {
        sleep(0.1); // avoid max loop limit
      }
      
      c ++;
    }
  }
  
  r("Done adding items");
  req("COMMIT");
  r("Transaction closed");
  
  // reset time
  echo("--time reset--");
  this.start = timevar2;
  
  temp.num = req("SELECT sum(quantity) FROM test_2 WHERE itemid = 'ak47'", true)[0][0];
  r("Total number of AK47s: " @ num);
  
  temp.num = req("SELECT * FROM test_2 WHERE itemid = 'ak47' AND account = '2653'", true)[0].quantity;
  r("How many AK47s account '2653' has: " @ num);
  
  req("UPDATE test_2 SET quantity = quantity + 200 WHERE account = '2653' AND itemid = 'ak47'");
  r("Added 200 AK47s to account '2653'");
  
  temp.num = req("SELECT * FROM test_2 WHERE itemid = 'ak47' AND account = '2653'", true)[0].quantity;
  r("How many AK47s account '2653' has: " @ num); 


outputs:

See also this thread.