Graal Forums

Graal Forums (https://forums.graalonline.com/forums/index.php)
-   Code Gallery (https://forums.graalonline.com/forums/forumdisplay.php?f=179)
-   -   SQL File System (Use SQL with ease) (https://forums.graalonline.com/forums/showthread.php?t=134270607)

MysticalDragon 03-12-2018 02:54 AM

SQL File System (Use SQL with ease)
 
2 Attachment(s)
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)

fowlplay4 03-13-2018 04:34 PM

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.

MysticalDragon 03-13-2018 06:41 PM

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;




All times are GMT +2. The time now is 05:03 PM.

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