Graal Forums  

Go Back   Graal Forums > Development Forums > NPC Scripting > Code Gallery
FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 03-12-2018, 02:54 AM
MysticalDragon MysticalDragon is offline
Global Administration
MysticalDragon's Avatar
Join Date: Oct 2002
Location: Lynn Ma
Posts: 883
MysticalDragon is just really niceMysticalDragon is just really nice
Send a message via AIM to MysticalDragon Send a message via MSN to MysticalDragon
SQL File System (Use SQL with ease)

Before using this system, I would highly recommend you use Chris Vimes SQL Explorer, to create your tables.

How it works?
SQL File System runs your SQL Queries from the file directory sql/*. Novo originally wrote this system when I started to get into heavy development since I had such a learning curve with SQL. And in all honesty working with SQL has never been easier.

-NPC Server Needs these rights
PHP Code:
-Depending on how many sub-folders you use.
rw sql/*
rw sql/*/
*
rw sql/*/*/
It also supports multiple databases.
~Functions~
/**
* This method updates the database
*
* @param query_name the named query
* @param args the array of query arguments
* @param debug echos in RC what SQL is doing
* @param db what SQL Database you want to use /uses main.db if null
*/
update(temp.query_name, temp.args, temp.debug, temp.db)


/**
* This method returns a result set from the database
*
* @param query_name the named query
* @param args the array of query arguments
* @param debug echos in RC what SQL is doing
* @param db what SQL Database you want to use /uses main.db if null
* @return the results (TSQLRequest)
*/
read(temp.query_name, temp.args, temp.debug, temp.db)


/**
* This method returns the first column values as an array
*
* @param query_name the named query
* @param args the array of query arguments
* @param debug echos in RC what SQL is doing
* @param db what SQL Database you want to use /uses main.db if null
* @return the first column results
*/
readFirst(temp.query_name, temp.args, temp.debug, temp.db)

-Examples on how to use the System.
We are going to create a table called test using SQL Explorer with the rows test1, test2, test3 with test1 being unique.
PHP Code:
NPC DatabasetestSQL
function onCreated() {
  
temp.row1 "test1"//usually your unique ID
  
temp.rowData = {"test2""test3"};
  
addTesttemp.row1temp.rowData); //Adds The Data
}

public function 
addTest(temp.row1temp.rowData) {
  
temp.query_args = {};
  
temp.query_args.add(temp.row1);
  
temp.query_args.addarray(temp.rowData);
  
SQL.update("test/add"temp.query_args);
  
  
// Cache SQL here (optional ?)
  
this.(@"test"temp.row1) = temp.rowData;

PHP Code:
Filesql/test/add.txt (same as SQL.update)
INSERT INTO items (
  
test1,
  
test2,
  
test3
)

VALUES (
  
'%s',
  
'%s',
  
'%s'

But what if you want to add to an existing row? First you need to check if the data actually exists. You could use the cache for this or check SQL Directly. We are going to check SQL directly in this example but I would suggest you cache the data.

PHP Code:
NPC DatabasesqlTest2 (same as SQL.update)
function 
onCreated() {
  
SQL.update("test/createIfDoesntExist", {"test1"});
  
SQL.update("test/add"temp.data);

PHP Code:
Filesql/test/createIfDoesntExist.txt (same as SQL.update)
INSERT OR IGNORE INTO test(
  
test1,
  
test2,
  
test3
)

VALUES(
  
'%s',
  
1,
  
2

How to easily get the SQL Data.
PHP Code:
NPC DatabasesqlTest3
function onCreated() {
  
temp.data getAll();
  echo(
temp.data);
}

public function 
getAll() {
  
temp.ids = new[0];
  for (
temp.rowSQL.read("test/getAll").rows) {
    
temp.ids.add(temp.row[0]);
  }
   return 
temp.ids;

PHP Code:
Filesql/test/getAll.txt
SELECT
  test1
FROM
  test
ORDER BY
  test1 ASC 
Attached Both Scripts SQL (Database) and functions_sql (Class)
Attached Files
File Type: txt functions_sql.txt (3.2 KB, 186 views)
File Type: txt SQL.txt (4.3 KB, 246 views)
__________________
~Delteria Support
~Playerworld Support
~PWA Chief
http://support.toonslab.com
[email protected]




Last edited by MysticalDragon; 03-12-2018 at 08:55 PM..
Reply With Quote
  #2  
Old 03-13-2018, 04:34 PM
fowlplay4 fowlplay4 is offline
team canada
fowlplay4's Avatar
Join Date: Jul 2004
Location: Canada
Posts: 5,200
fowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond repute
you have a wasteful 'getcallstack()' call in read() and your sanitize SQL function references a non-existent function.

escapestring2(text); is the built-in function for sanitizing values for SQLite queries.

PHP Code:
public function read(temp.query_nametemp.argstemp.debugtemp.db) {
  return 
execute(temp.query_nametruetemp.argstemp.debugtemp.db);
}

function 
sanitize_sqlite(temp.text) {
  return 
escapestring2(temp.text);

personally I like to do my queries in-line and sanitize the parameters (escapestring2 for strings, int for integers, float for decimals):

PHP Code:
temp.sql "
  SELECT scores 
  FROM leaderboards 
  WHERE acct = '%s' AND score > %s
"
;
temp.sql format(temp.sqlescapestring2(temp.acct), int(temp.min_score)); 
other optimization tips: putting your files under level/sql would let you utilize onlevelfileupdated then you could automatically cache all your SQL queries into memory/variables instead of reading from file.
__________________
Quote:

Last edited by fowlplay4; 03-13-2018 at 04:58 PM..
Reply With Quote
  #3  
Old 03-13-2018, 06:41 PM
MysticalDragon MysticalDragon is offline
Global Administration
MysticalDragon's Avatar
Join Date: Oct 2002
Location: Lynn Ma
Posts: 883
MysticalDragon is just really niceMysticalDragon is just really nice
Send a message via AIM to MysticalDragon Send a message via MSN to MysticalDragon
very resourceful didn't think of that regarding the levels sub-directory.
That getCallStack was a test thing that i forgot to remove, I apologies for that.

Will update it using levels and escapstring2. But you could use decimals and integars inside the textfile with something like this.
PHP Code:
SQL.update("test/test", {"test"timevar2,  timevar2});

UPDATE
  test
SET
  test2 
= %2$i,
  
test3= %3$d,
WHERE
  test 
'%1$s' 
Regarding the non-existent function I forgot to add that not sure who wrote this function, maybe Novo?

PHP Code:
DatabaseString
public function replaceAll(temp.texttemp.matchtemp.replace) {
  
temp.prev 0;
  
temp.output "";
  do {
    
temp.found indexOf(temp.texttemp.matchtemp.prev);
    if (
temp.found == -1) {
      
temp.output @= temp.text.substring(temp.prev, -1);
      return 
temp.output;
    } else {
      
temp.output @= temp.text.substring(temp.prevtemp.found temp.prev) @ temp.replace;
      
temp.prev temp.found temp.match.length();
    }
  } while (
temp.found != -1);
  return 
temp.output;
}

public function 
indexOf(temp.texttemp.matchtemp.offset) {
  for (
temp.temp.offsettemp.text.length(); ++) {
    if (
temp.text.substring(temp.itemp.match.length()) == temp.match) {
      return 
temp.i;
    }
  }
  return -
1;

__________________
~Delteria Support
~Playerworld Support
~PWA Chief
http://support.toonslab.com
[email protected]




Last edited by MysticalDragon; 03-13-2018 at 07:59 PM..
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +2. The time now is 11:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Copyright (C) 1998-2019 Toonslab All Rights Reserved.