Graal Forums - SQLite

Page 2 of 2

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)

Admins

04-13-2009 12:43 PM

Using requestsql2 with an unconfigured database will result in silence, may be can set the error variable. For the future it could be possible to configure additional databases in the server options but right now it's not possible.

LoneAngelIbesu

04-13-2009 05:45 PM

Quote:

Originally Posted by Inverness (Post 1483434)

Stefan said in an earlier post that requestsql2() is for choosing between mysql and sqlite databases, so at the moment it is useless to you and you shouldn't use it.

The /scripthelp entry should be updated as such, then.

As good as all of this information is, none of it helps with my original question of how to create 'sub-tables'. Just a light reminder...

Admins

04-14-2009 12:56 AM

The requestsql2 information is correct, it lets you select the (configured) database. Right now you cannot configure databases, so there is only "default".

Inverness

04-14-2009 01:38 AM

Quote:

Originally Posted by LoneAngelIbesu (Post 1483510)

As good as all of this information is, none of it helps with my original question of how to create 'sub-tables'. Just a light reminder...

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.

Inverness

04-14-2009 03:10 PM

I tried using string.escape() as you suggested, but like I thought, it escaped single quotes using a backslash which is incorrect, you're supposed to escape single quotes using another single quote.

I also suggest adding "parameterized queries." Which is basically when question marks in the query are replaced by function parameters to protect against SQL injection and stuff. Example:

executesql("INSERT INTO options VALUES (?,?)", {"profit", 9001});

LoneAngelIbesu

04-14-2009 11:09 PM

Quote:

Originally Posted by Inverness (Post 1483639)

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

fowlplay4

04-17-2009 02:48 AM

I was mainly referring to using format instead of an array and format2

Think your player account example but with 19 parameters, 18 of which were variables with .escape() on the end.

xXziroXx

04-17-2009 04:08 PM

Quote:

Originally Posted by Stefan (Post 1484351)

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

Have that update been yet? If not, could you please update Maloria's NPC server with it?

Admins

04-17-2009 09:55 PM

Quote:

Originally Posted by fowlplay4 (Post 1484536)

I was mainly referring to using format instead of an array and format2

Think your player account example but with 19 parameters, 18 of which were variables with .escape() on the end.

Well what is the problem then to just loop through the array and call escape() on them?

fowlplay4

04-17-2009 10:28 PM

Yeah that's what I'm doing now.

Admins

04-18-2009 12:48 AM

The npcserver has been uploaded to all machines, restart it manually to get the new escape() command.

xXziroXx

04-18-2009 12:51 AM

Quote:

Originally Posted by Stefan (Post 1484715)

The npcserver has been uploaded to all machines, restart it manually to get the new escape() command.

Cheers. :D

devilsknite1

04-18-2009 09:28 AM

Quote:

Originally Posted by Inverness (Post 1484305)

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

Yes, I've done this. Multiple times...

Inverness

04-19-2009 03:32 AM

Currently NULL in SQL is represented as "" in Graal, this is a problem and there needs to be a way to differentiate between zero, an empty string, and NULL.

I was thinking perhaps a literal like "\\NULL\\" or "\\0" in GScript. c = '\0' just causes a compiler error.

Inverness

04-28-2009 03:24 AM

TSQLRequest.affectedrows doesn't appear to be working with my update statement even though I can see the change in a later query.

Admins

04-28-2009 11:09 AM

affectedrows is currently only used for mysql, is not touched by the sqlite database.

[email protected]

05-09-2009 12:25 PM

Any idea how to use the auto increment option as a primary key? I can't figure it out- thanks

Skyld

05-09-2009 12:30 PM

Quote:

Originally Posted by [email protected] (Post 1490433)

Any idea how to use the auto increment option as a primary key? I can't figure it out- thanks

http://www.sqlite.org/faq.html#q1

Google will serve you well. :)

[email protected]

05-09-2009 12:44 PM

Quote:

Originally Posted by Skyld (Post 1490434)

http://www.sqlite.org/faq.html#q1

Google will serve you well. :)

Thanks beautiful :}

TESTRETIS

07-28-2009 03:28 AM

Sorry for the bump, but is it possible in a future RC Update to have a console specifically for SQL Management, and to place and receive queries, with the ability to view a table, without relying on an Echo to RC, to do possible click sorting, displaying and indicating primary keys, composite keys, surrogate, etc.

cbk1994

07-28-2009 03:59 AM

Quote:

Originally Posted by TESTRETIS (Post 1511031)

Would be useful. I just downloaded a lightweight SQLite database manager, and set it as the default application for 'db' files, so when I double click it in file browser, it opens up.

I also built a basic in-game "SQL Explorer". If it was finished, it could eventually be added to client-RC (would actually be quite a nifty feature).

http://img11.imageshack.us/img11/928...1248746452.png

TESTRETIS

07-28-2009 04:07 AM

Quote:

Originally Posted by cbk1994 (Post 1511044)

Wow, that's awesome.

cbk1994

07-28-2009 04:10 AM

Quote:

Originally Posted by TESTRETIS (Post 1511050)

Wow, that's awesome.

It'd be a lot more awesome if it supported half the stuff I planned. Right now it can only be used for viewing, but eventually I'd like to implement creating tables, inserting/deleting rows, removing/adding columns, adding/removing indexes, etc.

cbk1994

08-07-2009 01:16 AM

Quote:

Originally Posted by xXziroXx (Post 1513489)

I've ran into this problem again.

string.escape(), does - as stated - add another ' infront of ', instead of a backslash.

SQL refuses to accept '', so we really need a command to escape ' with a backslash. Funny enough, I found this:

escapestring(str) - returns string - adds \ in front of ", ' and \, and removes non-printable characters

... which is a lie, or doesn't work. That too escapes ' with a ' :)

str.escape() has been working fine for us.

cbk1994

08-07-2009 01:19 AM

Quote:

Originally Posted by xXziroXx (Post 1513492)

... which SQL refuses to accept.

Like I said, works fine for me.

EDIT: What exactly are you trying to do?

PHP Code:


		
			
temp.str = "God's Angel";
req("INSERT INTO 'bank' VALUES('" @ str.escape() @ "', 1111, 1)");

http://img195.imageshack.us/img195/3618/sql.png

EDIT: I have noticed that it unnecessarily escapes quotations.

xXziroXx

08-07-2009 01:41 AM

Actually, nevermind. Seems another part of the code was parsing it wrong, making me think it wasn't working. I'll go ahead and delete my previous posts. x_x

All times are GMT +2. The time now is 03:33 AM.

Page 2 of 2

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