SuperSQL - Overview

Setup

<?php
// MySql setup
$host = "localhost";
$db = "test";
$user = "root";
$pass = "1234";

$dsn = "mysql:host=$host;port=3306;dbname=$db;charset=utf8";
$SuperSQL = new SuperSQL($dsn,$user,$pass);
?>

Setup with helper

<?php
$SuperSQL = SQLHelper::connect($host,$db,$user,$pass);
?>

SlickInject and Medoo on steroids - The most advanced and lightweight library of it's kind.

Purpose

To provide a very fast and efficient way to edit sql databases
To provide a easy method of access

Main Features

Very small - 28.5KB one file (Unminified, dist/SuperSQL.php. Minified version: 12KB)
Simple and easy - Very easy to lean. We also provide a simple and advanced API
Compatability - Supports major SQL databases
Customisability - We offer multiple files for your needs
Efficiency - This module was built with speed in mind.
Complexity - This module allows you to make all kinds of complex queries
Security - This module prevents SQL injections.
Availability - This module is FREE. Licensed under the MIT license.

Usage

<?php
new SuperSQL($dsn,$user,$pass);
?>

You may either

Use the built file (/dist/SuperSQL*.php)
Use the library (include index.php)

Build

To build this library, do

node builder.js

It will build to /dist/SuperSQL.php

Basics

These are the basic functionalities of SuperSQL.

Responses

Error handling

<?php
$Response = $SuperSQL->select("test",[],[
    "#a" => "WHERE SELECT INSERT LOL" // raw
]); // SELECT * FROM `test` WHERE `a` = WHERE SELECT INSERT 

echo json_encode($Response->getData()); // NULL

echo json_encode($response->error()); // ["42000",1064,"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE SELECT INSERT LOL' at line 1"]
?>

Iterator usage

<?php
$Response = $SuperSQL->select("test",[],[
    "a" => "WHERE SELECT INSERT LOL"
]); // SELECT * FROM `test` WHERE `a` = 'WHERE SELECT INSERT' 

echo $response->error(); // FALSE

while ($row = $response->next()) { // Use the iterator to iterate through rows easily

.... // Do some stuff

}

$response->reset(); // Reset iterator so you can do the above code again
?>

When you make a query, SuperSQL will return a SQLResponse object.

Response->getData($dontFetch = false)

Get all rows. If dontfetch is true, then it will only return the results that have already been fetched

Response->error()

Returns error object if there is one. False otherwise

Response->getAffected()

Get number of rows affected by the query

Response->next()

Get next row

Response->reset()

Reset iterator.

Conditionals

Conditionals (WHERE & JOIN)

<?php
$where = array(
 "arg1" => "val1", // AND arg1 = val1
 "[>>]arg2" => "val2", // AND arg2 > val2
 "[<<]arg3" => "val3", // AND arg3 < val3
 "[>=]arg4" => "val4", // AND arg4 >= val4
 "[<=]arg5" => "val5", // AND arg5 <= val5
 "[!=]arg6" => "val6", // AND arg6 != val6
 "[||]" => [ // Bind ||.
     "arg7" => "val7"
 ],
 "[&&][>>]" => [ // Bind >.
     "arg8" => "val8"
 ],
 "arg9" => ["val9a","val9b"],

 "arg10[~~]" => "%arg10%",
 "arg11[!~]" => "%arg11%"
);
?>

Conditional statements are extremly customisable. WHERE and JOIN clauses are conditional statements.

Multi-queries

Multi-Querying

<?php
// Way 1

array(
array( // NOTE: While the all the arrays dont have to be identical, the first one should have the most items
"arg1"=> "val1",
"arg2"=> "val2"
),
array(
"arg2"=> "val3"
}
); // -> [["val1","val2"],["val1","val3"]] - Two queries

// Way 2 (only works with the data argument in INSERT and UPDATE)

array(
"arg1" => "val1",
"arg2" => array("val2","val3")
); // -> [["val1","val2"],["val1","val3"]] - Two queries


// Binds
array(
    array(
        "hello" => "world"
        "[||]" => array(
            "foo" => "bar",
            "num" => 123
        )
    ),
    array( // this also works
    "hello" => "hi!",
    "foo" => "lol"
    )
);

array(
    array( // Uh-oh - collision
        "[>>]lol" => 3
        "[||]bind1" => array(
            "foo" => "bar",
            "lol" =>  5
        ),
        "[>>]bind2" => array(
            "lols" => 231
        )
    ),
    array( // args will be preserved
    "lol" => 2,
    "bind1" => array(
        "foo" => "lol"
    )
    )
);
?>

Multiqueries can be done too. This allows for highly efficient repetative queries. Note: Only the values of WHERE and INSERT work with this. VALUES, not KEYS.

Key-collisions

Since SuperSQL uses associative arrays, key collisions can occur. To solve this problem, add "#id (replace id with something)" to the key.

Multi-Table support

Multi-Table queries

<?php
$SuperSQL->SELECT(["table1","table2"],[],[]);
?>

If you want to query multiple tables at once, put the tables in as an array

Type Casting

Type Casting

<?php
$SuperSQL->INSERT("sensitive_data",[ // NOTE: Also works with any other query. ALSO NOTE: Types are case-insensitive
   "nuclear_codes[int]" => 138148347734, // Integer (Use [int] or [integer]
   "in_state_of_emergency[bool]" => false, // Boolean (Use [bool] or [boolean]
   "secret_files[lob]" => $file // Large Objects/Resources (Use [lob] or [resource])
   "fake_data[null]" => null // Null values (use [null])
]);
?>

If you want to set the type of the input, you can set it by adding [type] (replace type with type).

SQL Functions/raw

SQL functions/Raw

<?php
$SuperSQL->INSERT("times",[
    "#time" => "NOW()"
]);
?>

If you want to use SQL functions such as NOW() or want use insert raw, unescaped data, add # at the beginning of the key

Alias

Alias

<?php
$SuperSQL->SELECT("users",["user_id[id]"]);
?>

You can use an alias for columns and/or tables by adding [aliasname].

Simple

Simple queries

<?php
$SuperSQL->sSELECT($table,$columns,$where);

$SuperSQL->sINSERT($table,$data);

$SuperSQL->sUPDATE($table,$data,$where);

$SuperSQL->sDELETE($table,$where);
?>

If you are making simple queries, you may use simple functions to boost performance. Use simple functions by attatching an s in front of the function. The syntax is very similar to SlickInject.

Custom Queries

Custom queries can be made using $SuperSQL->query($query).

Advanced Functions

<?php
$SuperSQL->SELECT($table, $columns, $where[,$join[, $limit/$append);

$SuperSQL->INSERT($table, $data);

$SuperSQL->UPDATE($table, $data, $where);

SuperSQL->DELETE($table, $where);

?>

Advanced functions are for doing advanced queries. Advanced query features include:

Multi-query
Multi-table querying
Type Casting
Aliasing
Use of operators other than =
Use of "OR"
Joins

SELECT

<?php
$SuperSQL->SELECT("horizon", [], [], array(
    // [>>] - Right join
    // [<<] - Left join
    // [><] - Inner join (Default)
    // [<>] - Full join
    "[><]meteors" => array("horizon.object" => "meteors.object"), // JOIN
),5); // only 5 rows

$SuperSQL->SELECT("table", [
    "DISTINCT", // Distinct items only
    "col1",
    "col2[alias][int]", // alias and type-casting
    "col3[alias2]", // alias
    "col4[json]" // type casting
]); // SELECT DISTINCT `col1`, `col2` AS `alias`, `col3` AS `alias2`, `col4` FROM `table`
?>

SuperSQL->SELECT($table, $columns, $where[,$join[, $limit/$append);

(String|Array)table - Table(s) to query
(Array)columns - Array of columns to return. [] will query using the * selector.
(Array)where - Array of conditions for WHERE (See above for documentation on WHERE)
(Array|Null)join - Array of conditions for JOIN. Usage below
(Int|String)limit - Number of rows to retrieve. if string, will be treated as an append - it will be appended to the sql query.

INSERT

<?php
$SuperSQL->INSERT("table",array(
"hello" => "world",
"SuperSQL" => "rocks"
));
?>

SuperSQL->INSERT($table, $data);

(String|Array)table - Table(s) to insert to
(Array)data - Data to insert

UPDATE

<?php
$SuperSQL->UPDATE("citizens",array(
"SuperSQL" => "To the rescue!"
),array(
"needs_help" => 1
));
?>

SuperSQL->UPDATE($table, $data, $where);

(String|Array)table - Table(s) to insert to
(Array)data - Data to update
(Array)where - Conditional statements

DELETE

<?php
$SuperSQL->DELETE("persons",
"is_villain" => 1
));
?>

SuperSQL->DELETE($table, $where);

(String|Array)table - Table(s) to insert to
(Array)where - Conditional statements

Simple Functions

<?php
// Gets data
$SuperSQL->SELECT($table, $columns, $where);
// Inserts/adds data to database
$SuperSQL->sINSERT($table, $data);
// Updates data in database
$SuperSQL->sUPDATE($table, $data, $where);
// Deletes data in database
$SuperSQL->DELETE($table, $where);
?>

Simple API is for basic querying. It allows of lightning-fast, simple and easy querying. Unlike the advanced api, you cannot:

You cannot use other opererators besides = (Equal to)
No binds - Only AND is used
No multi-querying
No type casting

sSELECT

sSELECT

<?php
$SuperSQL->sSELECT("citizens",["name","age"],[ // SELECT `name`, `age` FROM `citizens` WHERE `in_trouble` = 1
    "in_trouble" => 1
]);
?>

SuperSQL->SELECT($table, $columns, $where[, $append);

(String)table - Table to query
(Array)columns - Columns to return. [] is *
(Array)where - Conditional statements
(String)append - Append to sql (Optional)

sINSERT

sINSERT

<?php
$SuperSQL->sINSERT("message_board",array( // INSERT INTO `message_board` (`title`, `SuperSQL`) VALUES ('SuperSQL Saves The Day', 'SuperSQL rocks!')
    "title" => "SuperSQL Saves The Day",
    "message" => "SuperSQL rocks!"
));
?>

SuperSQL->sINSERT($table, $data);

(String)table - Table to insert to
(Array)data - Data to insert

sUPDATE

sUPDATE

<?php
$SuperSQL->sUPDATE("developers",[ // UPDATE `developers` SET `is_happy` = 1, `reason` = 'Becaz SuperSQL is awesome!' WHERE `is_happy` = 0
"is_happy" => 0,
],[
"is_happy" => 1,
"reason" => "Becaz SuperSQL is awesome!"
]);
?>

SuperSQL->sUPDATE($table, $data, $where);

(String)table - Table to insert to
(Array)data - Data to update
(Array)where - Conditional statements

sDELETE

sDELETE

<?php
$SuperSQL->sDELETE("hackers",[ // DELETE FROM `hackers` WHERE `status` = 'Tried to SQL Inject attack a site' AND `encountered` = 'SuperSQL'
    "status" => "Tried to SQL Inject attack a site",
    "encountered" => "SuperSQL"
]);
?>

SuperSQL->DELETE($table, $where);

(String)table - Table to insert to
(Array)where - Conditional statements

Helper Functions

SuperSQL provides some helper functions to allow for easier access. The helper functions allow you to:

Connect easily
Manage multiple database connections
Do queries more efficiently

SQLHelper::connect

<?php
$SuperSQL = SQLHelper::connect("localhost","mydb","root","1234"); // mysql

$SuperSQL = SQLHelper::connect("localhost","mydb","root","1234", $dbtype); // others
?>

Connect easily to any database.

connect($host,$db,$user,$pass,$options)

(String)host - Host to connect to
(String)db - DB name
(String)user - Username
(String)pass - Password
(Array)options - Options (Optional)

connect($host,$db,$user,$pass,$dbtype)

(String)host - Host to connect to
(String)db - DB name
(String)user - Username
(String)pass - Password
(String)dbtype - Database type (mysql,pgsql,sybase,oracle)

connect($host,$db,$user,$pass,$dsn)

(String)host - Host to connect to
(String)db - DB name
(String)user - Username
(String)pass - Password
(String)dbtype - DSN string

new SQLHelper()

<?php
$Helper = new SQLHelper("localhost","test","root","1234"); // mysql

$Helper = new SQLHelper("localhost","test","root","1234", $dbtype); // others

$Helper = new SQLHelper($array); // array of connections

$Helper = new SQLHelper(array( // array of connection configs
    array(
    "host"=>"localhost",
    "db"=>"test",
    "user"=>"root",
    "password"=> "1234"
    ),
    array(
    "host"=> "192.168.1.2",
    "db"=>"test2",
    "user"=>"root",
    "password"=> "1234",
    "options" => "pgsql" // dbtype
    ),
));
?>

Initialise the helper

new SQLHelper($SuperSQL)

(SuperSQL)SuperSQL - SuperSQL object

new SQLHelper($host,$db,$user,$pass,$options)

(String)host - Host to connect to
(String)db - DB name
(String)user - Username
(String)pass - Password
(Array)options - Options (Optional)

new SQLHelper($connect)

(Array)connect - Array of connection data - Uses Helper::connect

Change

$SQLHelper->change($id)

Changes the selected connection

(Int)id - Connection id

getCon

$SQLHelper->getCon($all = false)

(Bool)all - if true, will return all connections. If not, then will only return the selected one

SELECT

$SQLHelper->SELECT($table,$columns,$where,$join,$limit/$append)

The SELECT query. The api is the same as normal SELECT or sSELECT. The helper will choose the most efficient way. (It will choose simple or advanced api based on certain conditions)

SELECTMAP

<?php
$SQLHelper->SELECTMAP("test",array(
    "name[username]",
    "id",
    "data" => [
        "admin[is_admin]",
        "user_posts[posts][json]",
    ]
));

/*
SELECT `name` AS `username`, `id`, `admin` AS `is_admin`, `user_posts` AS `posts` FROM `test`

Output:

[
    {
    "username": "admin",
    "id": 1,
    "data": [
        "is_admin": 1,
        "posts": [
            {
            "msg": "DA BAN HAMMER HAS SPOKEN!",
            "timestamp": 1503011190
            }
        ]
    ]
    },
    {
    "username": "testuser",
    "id": 2,
    "data": [
        "is_admin": 0,
        "posts": [
            {
            "msg": "hello world",
            "timestamp": 1503011186
            }
        ]
    ]
    }
]
*/
?>

$SQLHelper->SELECTMAP($table,$map,$where,$join,$limit/$append)

The SELECT query for source-mapping.

INSERT

$SQLHelper->INSERT($table,$data)

The INSERT query. The api is the same as normal INSERT or sINSERT. The helper will choose the most efficient way. (It will choose simple or advanced api based on certain conditions)

UPDATE

$SQLHelper->UPDATE($table,$data,$where)

The UPDATE query. The api is the same as normal UPDATE or sUPDATE. The helper will choose the most efficient way. (It will choose simple or advanced api based on certain conditions)

DELETE

$SQLHelper->DELETE($table,$where)

The DELETE query. The api is the same as normal DELETE or sDELETE. The helper will choose the most efficient way. (It will choose simple or advanced api based on certain conditions)

REPLACE

<?php
$SQLHelper->REPLACE("luggage",[
    "items"=>array("bomb"=>"pillow","poison"=>"perfume")
]); // UPDATE `luggage` SET `items` = REPLACE(REPLACE(`items`,'bomb','pillow'),'poison','perfume');
?>

$SQLHelper->REPLACE($table,$data,$where)

(String|Array)table - table(s) to replace in
(Array)data - columns to replace
(Array)where - conditional statements

get

$SQLHelper->get($table,$columns,$where,$join)

Gets the first row

count

<?php
echo $SQLHelper->count("table",array( // returns row count (int)
"condition" => 1
);

$SQLHelper->count($table,$where,$join)

Get num of rows

max

<?php
echo $SQLHelper->max("table","column"); // Returns biggest value for column

$SQLHelper->max($table,$column,$where,$join)

Get the maximum value of a column

min

<?php
echo $SQLHelper->min("table","column"); // Returns smallest value for column

$SQLHelper->min($table,$column,$where,$join)

Get the minimum value of a column

avg

<?php
echo $SQLHelper->avg("table","column"); // Returns average value for column

$SQLHelper->avg($table,$column,$where,$join)

Get the average value of a column

sum

<?php
echo $SQLHelper->min("table","column"); // Returns sum of values in the column

$SQLHelper->sum($table,$column,$where,$join)

Get the sum of values in a column

create

$SQLHelper->create($table,$data)

Creates a table

(String)table - Table name to create
(Array)data - Array of keys and types

drop

$SQLHelper->drop($table)

Removes a table

(String)table - Table name to delete

Super Advanced

Transactions

<?php
$SuperSQL->transact(function () {

$SuperSQL->DELETE("citizens",[
    "near_explosion" => 1
]);

return false; // SuperSQL to the rescue! He reversed time (the query)
});
?>

SuperSQL->transact($call);

(Callable)call - Transaction Function. Return false to rollback

Logging

Logging

<?php
$SuperSQL->dev(); // Turn on logging

... // do some queries

echo json_encode($a->getLog()); // Get some data
?>

You find something isnt working for your website. You either:

Rage quit, break everything, scream "I #%@&$@! HATE SUPERSQL"
Use the log function to figure out whats wrong - LIKE A CIVILISED PERSON

To enable the logger, do $SuperSQL->dev(). Then make some queries.

Afterwords, do $SuperSQL->getLog() to get the log.

Da log - What does it mean?

<?php
$log = [
    [
    "SELECT * FROM `table` WHERE `test` = ?", // SQL base
    [[24424,1]], // Array of initial values with types. In this case, the value is 24424 and the type is an INT (PDO::PARAM_INT)
    [["0":234]] // Multi-query array
    ]
]
?>

SQL - SQL base. ? are replaced with values
Values - Initial values with types. NOTE: This is bound onto the SQL base string
Insert - Multi-query array

modeLock

<?php
$SuperSQL->modeLock(true);
?>

Modelock locks the fetch mode. By default, fetching is dynamic for performance. However, if you want to have it fetch all rows at the start, then set to true.