Graal Forums - SQLite

Page 4 of 6

Show 40 post(s) from this thread on one page

Graal Forums (https://forums.graalonline.com/forums/index.php)

- Future Improvements (https://forums.graalonline.com/forums/forumdisplay.php?f=10)

- - SQLite (https://forums.graalonline.com/forums/showthread.php?t=84995)

LoneAngelIbesu

04-14-2009 11:09 PM

Quote:

Originally Posted by Inverness (Post 1483639)

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

04-15-2009 03:44 AM

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

04-15-2009 06:44 AM

Quote:

Originally Posted by devilsknite1 (Post 1484034)

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 :p.

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

04-15-2009 07:02 AM

Quote:

Originally Posted by devilsknite1 (Post 1484034)

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

04-15-2009 11:33 AM

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

04-16-2009 01:54 AM

Quote:

Originally Posted by Stefan (Post 1484084)

<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

04-16-2009 02:42 AM

Quote:

Originally Posted by napo_p2p (Post 1484060)

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

04-16-2009 03:36 AM

Quote:

Originally Posted by devilsknite1 (Post 1484291)

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

04-16-2009 12:54 PM

Quote:

Originally Posted by Inverness (Post 1484284)

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

04-16-2009 04:56 PM

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

04-16-2009 07:22 PM

Quote:

Originally Posted by fowlplay4 (Post 1484392)

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

04-16-2009 09:33 PM

Quote:

Originally Posted by Stefan (Post 1484351)

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

04-16-2009 09:52 PM

Quote:

Originally Posted by Inverness (Post 1484426)

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

04-16-2009 11:21 PM

Quote:

Originally Posted by Inverness (Post 1484475)

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

04-16-2009 11:26 PM

Quote:

Originally Posted by fowlplay4 (Post 1484479)

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 :)

All times are GMT +2. The time now is 02:12 PM.

Page 4 of 6

Show 40 post(s) from this thread on one page