SQLite

[LoneAngelIbesu]

Originally Posted by Inverness

No such thing as sub-tables in this case. The syntax you're thinking of is for creating a table in a database other than the default, which is useless in this case since there is only one database. Just create a normal table.

Simply because the syntax is incorrect doesn't mean that what I'm trying to do is impossible, mind you. I've considered using a separate table, but would favor my original plan to avoid overly-complicated referencing.

I've looked in to creating VIEWS, but I'm not sure if I'm doing it correctly.

PHP Code:

  sql.execute(
    "CREATE TABLE IF NOT EXISTS shops (
      title TEXT NOT NULL DEFAULT 'Shop', 
      stype TEXT NOT NULL DEFAULT 'Server', 
      owner TEXT NOT NULL DEFAULT 'Unknown', 
      arch TEXT NOT NULL DEFAULT 'Unknown', 
      price INT DEFAULT 0, 
      stock INT DEFAULT 0, 
      kerou INT NOT NULL DEFAULT 5000, 
      desc TEXT NOT NULL DEFAULT 'Description text.', 
      createtime INT NOT NULL DEFAULT CURRENT_TIMESTAMP)", false);
  sql.execute(
    "CREATE VIEW shopitems AS SELECT arch, price, stock FROM shops", false);
  sql.execute(
    "CREATE TRIGGER insert_item INSTEAD OF INSERT ON shopitems
      BEGIN
        INSERT INTO shops (arch, price, stock) VALUES (arch, price, stock);
      END;
     CREATE TRIGGER update_item INSTEAD OF UPDATE ON shopitems
      BEGIN
        UPDATE shops SET arch = new.arch;
        UPDATE shops SET price = new.price;
        UPDATE shops SET stock = new.stock;
      END;", false); 


[devilsknite1]

Would using this in an onPlayerLogin() still work? I tried Stefan's sample age code thing, and I get the working output, but if i try to edit it the slightest bit and use findPlayer( p.account ); (p is a param in the onPlayerLogin() function) I get this:

NPC Code:

---

Accounts in database: 0

---

Here's what I'm using:

PHP Code:

function onPlayerLogin( p )
{
  p = findPlayer( player.account );
  requestsql("CREATE TABLE IF NOT EXISTS test (account varchar not null default '' primary key, id int not null default 1)", false);
  requestsql("INSERT INTO test VALUES (" @ p.account @ "," @ p.id @ ")", false);
  temp.req = requestsql("SELECT * FROM test", true);
  if (!temp.req.completed)
    waitfor(temp.req,"onReceiveData",60);
  echo("Accounts in database: " @ temp.req.rows.size());
  for (temp.row: temp.req.rows)
    echo("  Account: " @ temp.row.account @ ", ID: " @ temp.row.id);  
} 


Like I said, editing the code SLIGHTLY just gives me an output of 0. I didn't go through the whole SQLite language either, which I plan on doing later tomorrow.

[napo_p2p]

Originally Posted by devilsknite1

PHP Code:

function onPlayerLogin( p )
{
  p = findPlayer( player.account );
  requestsql("CREATE TABLE IF NOT EXISTS test (account varchar not null default '' primary key, id int not null default 1)", false);
  requestsql("INSERT INTO test VALUES (" @ p.account @ "," @ p.id @ ")", false);
  temp.req = requestsql("SELECT * FROM test", true);
  if (!temp.req.completed)
    waitfor(temp.req,"onReceiveData",60);
  echo("Accounts in database: " @ temp.req.rows.size());
  for (temp.row: temp.req.rows)
    echo("  Account: " @ temp.row.account @ ", ID: " @ temp.row.id);  
} 


It looks like there's an error in one of your queries (you forgot the single quotes). Try this:

PHP Code:

requestsql("INSERT INTO test VALUES ('" @ p.account @ "'," @ p.id @ ")", false); 


Let me know the result, as I don't have a means to test it right now .

Also, you don't really need the first sql query if you have already created the table (but I guess it doesn't really hurt to include it). And, you don't need to do the findplayer(), since the 'p' passed in is a player object (so you could use temp.p.account and temp.p.id right off the bat).

[Codein]

Originally Posted by devilsknite1

Would using this in an onPlayerLogin() still work? I tried Stefan's sample age code thing, and I get the working output, but if i try to edit it the slightest bit and use findPlayer( p.account ); (p is a param in the onPlayerLogin() function) I get this:

NPC Code:

---

Accounts in database: 0

---

Here's what I'm using:

PHP Code:

function onPlayerLogin( p )
{
  p = findPlayer( player.account );
  requestsql("CREATE TABLE IF NOT EXISTS test (account varchar not null default '' primary key, id int not null default 1)", false);
  requestsql("INSERT INTO test VALUES (" @ p.account @ "," @ p.id @ ")", false);
  temp.req = requestsql("SELECT * FROM test", true);
  if (!temp.req.completed)
    waitfor(temp.req,"onReceiveData",60);
  echo("Accounts in database: " @ temp.req.rows.size());
  for (temp.row: temp.req.rows)
    echo("  Account: " @ temp.row.account @ ", ID: " @ temp.row.id);  
} 


Like I said, editing the code SLIGHTLY just gives me an output of 0. I didn't go through the whole SQLite language either, which I plan on doing later tomorrow.

If you log on Dev Astrid, you can view my testing NPC. I managed to get it working, eventually, haha.

[Admins]

It's better to format queries like this:

PHP Code:

temp.query = format("SELECT * FROM test WHERE account='%s'", player.account.escape());
temp.req = requestsql(temp.query, true); 


Makes it easier to avoid SQL-injection.

[Inverness]

Originally Posted by Stefan

<snip>

You're ignoring the problem with string.escape(), it's supposed to escape single quotes with another single quote in SQL and string.escape() does not do this. So have an sqlescape() global to do this.

[devilsknite1]

Originally Posted by napo_p2p

It looks like there's an error in one of your queries (you forgot the single quotes). Try this:

PHP Code:

requestsql("INSERT INTO test VALUES ('" @ p.account @ "'," @ p.id @ ")", false); 


Output:

NPC Code:

---

Accounts in database: 0

---

Although, not sure since it could have been Stefan's suggestion that broke it ;o

[Inverness]

Originally Posted by devilsknite1

Output:

NPC Code:

---

Accounts in database: 0

---

Although, not sure since it could have been Stefan's suggestion that broke it ;o

You restarted the NPC-Server already to enable the SQL right?

Also, SQLite only has a few column types: TEXT, NUMERIC, INTEGER, REAL, NONE. Other types work for compatibility but they're just aliases for one of these types, I suggest these be used for clarity.

http://www.sqlite.org/datatype3.html

[Admins]

Originally Posted by Inverness

You're ignoring the problem with string.escape(), it's supposed to escape single quotes with another single quote in SQL and string.escape() does not do this. So have an sqlescape() global to do this.

Hmmm I've tested and seen there are some problems yes, it's possible that mysql accepted the format but not sqlite. Will try if it can be modified and made working for both databases.
Update: fixed it, will work fine with the next npcserver update.

[fowlplay4]

Well until the next update I've been using this as a work around.

PHP Code:

function sqlescape(str) {
  if (str.pos("'") >= 0) {
    temp.result = str.escape().tokenize("\\\'");;
    for (temp.piece: result) 
      temp.nresult @= piece @ "''";
    return temp.nresult.substring(0,nresult.length() - 2);
  }
  else return str.escape();
} 


As well as format2 to accomplish that "parameterized query" request.

[Inverness]

Originally Posted by fowlplay4

As well as format2 to accomplish that "parameterized query" request.

Parameterized queries are distinct in the fact that they use the question marks for ambiguity.

[Inverness]

Originally Posted by Stefan

Update: fixed it, will work fine with the next npcserver update.

Well please post when Valikorlia should restart to have the npcserver update.

Also I noticed that you seem to have changed the behavior of trigger(), if this was intentional than I'm very annoyed because that's not something you should change without warning. Example:

#1. trigger onSpecial() with trigger("Special", ...): FAIL
#2. trigger onSpecial() with trigger("onSpecial", ...): SUCCEED
#3. trigger Special() with trigger("Special", ...): FAIL

#1 is how it used to work, #2 is how it works now, and #3 shouldn't work either way, but you would think it would if #2 does.

I assume you've changed this to eliminate the inconsistency with functions that use event names and whether the "on" prefix is part of the event name.

Note: This is all on the same object, if it behaves differently when triggering other objects then I'd like to know.

Edit: #1 works fine when triggering to a different object, I guess doing #1 on self makes the event catchable by catchevent()?

[fowlplay4]

Originally Posted by Inverness

Parameterized queries are distinct in the fact that they use the question marks for ambiguity.

Oh i was not aware of this, hope it gets added then! Using just format made things get veryyy ugly.

[Admins]

Originally Posted by Inverness

I guess doing #1 on self makes the event catchable by catchevent()?

I'm not sure what exactly you were doing, but a few months ago a way has been added to write custom GUI controls (in v6) which allows you do emulate stuff like onMouseDown events with trigger("mousedown") if you trigger it on yourself.

[Admins]

Originally Posted by fowlplay4

Oh i was not aware of this, hope it gets added then! Using just format made things get veryyy ugly.

Why? It's basicly a different way of doing the same, format works fine