SQL File System (Use SQL with ease)

[MysticalDragon]

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 Database: testSQL
function onCreated() {
  temp.row1 = "test1"; //usually your unique ID
  temp.rowData = {"test2", "test3"};
  addTest( temp.row1, temp.rowData); //Adds The Data
}

public function addTest(temp.row1, temp.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:

File: sql/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 Database: sqlTest2 (same as SQL.update)
function onCreated() {
  SQL.update("test/createIfDoesntExist", {"test1"});
  SQL.update("test/add", temp.data);
} 


PHP Code:

File: sql/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 Database: sqlTest3
function onCreated() {
  temp.data = getAll();
  echo(temp.data);
}

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


PHP Code:

File: sql/test/getAll.txt
SELECT
  test1
FROM
  test
ORDER BY
  test1 ASC 


Attached Both Scripts SQL (Database) and functions_sql (Class)