Database

SQL Database

Before using this library, you must modify the database configuration on /config/database.php.
Usage is almost similar with SFDatabase-js.

Get database connection

$myDatabase = Scarlets\Library\Database::connect(databaseName='{default}');
$myDatabase->connection; // PDO Class
$myDatabase->debug = 'log'; // Log to error.log
$myDatabase->lastQuery; // Will have value if debug === true

This library can be used with Redis as a structured database.
But if you want to do data searching, I recommend using SQL instead of Redis.

Some feature may not available/different on Redis.

Using Redis as database may not work on some update.
This is because further performance optimization that need to change database structure.
You can safely use Redis as your database after this notice was removed.

Transaction

Start a database transaction

$myDatabase->transaction(function($db){
    $db->select(...);
    $db->insert(...);
    ...
    return true; // Rollback if set to true
});

onTableMissing

Do an action if an table was missing

$myDatabase->onTableMissing('users', function(){
    $myDatabase->createTable(...);
});

For every parameter with $where

Options	Details
!	Not Equal to
~	Like
!~	Not Like
&~	Like (With AND)
!&~	Not Like (With AND)
>	More than
>=	More than or Equal
<	Less than
<=	Less than or Equal
><	Between 2 value
<>	Not between 2 value
,	(single,array) True if a value exist in comma separated list
!,	(single,array) True if a value not exist in comma separated list
&,	(array) True if all value exist in comma separated list
!&,	(array) True if all value not exist in comma separated list
LENGTH(<, >, <=, >=)	Return row that have some text length in the column
REGEXP	Use regex search ['name[REGEXP]'=>'alex|jason|loki']
LIMIT	Limit returned value ['LIMIT'=>1] or ['LIMIT'=>[$page, $length]]
ORDER	Order rows based on column ['ORDER'=>['time'=>'DESC']]
AND	And condition ['AND'=>['name'=>'alex', 'age'=>[34, 29]]]
OR	Or condition ['OR'=>['AND'=>['type'=>'human', 'name'=>'alex']], 'type'=>'animal']

Select table rows

$myDatabase->select(tableName, $columns, $where=[]);

$myDatabase->select('test', ['name', 'data'], {
    'OR'=>['id'=>123, 'words[~]'=>'hello'],
    'LIMIT'=>1
});
// SELECT name, data FROM test WHERE (id = ? OR (words LIKE ?)) LIMIT 1

/// hashtag column value: ,2,3,6,7,8,
/// This query will match above row because it's have ",3," 
$myDatabase->select('test', ['name', 'data'], {
    'hashtag[,]'=>[3,4]
});

Count Matching Rows

Count rows where the data was matched by query

$integer = $myDatabase->count($tableName, $where=[]);

Get single row

Get a single row where the data was matched

$data = $myDatabase->get($tableName, $column='*', $where=[]);

If $column was defined with string, it will return string of that column data. But if it's defined with array, it will return associative array.

Check if table has matched row

$boolean = $myDatabase->has($tableName, $where);

Check if column has missing index

Find missing index from 1 to rows length and return array of number. If the $offset is out of bound this will return false.

$list = $myDatabase->holes($tableName, $column, $length = 0, $offset = 0);

Predict/Suggestion search

Predict possible similar text on a column and return percentage while the highest percentage are on first index.

$array = $myDatabase->predict($tableName, $id = 'id', $where, &$cache);

$cache = null; // This will greatly improve performance on Interactive CLI
$scores = $myDatabase->predict('users', 'user_id', ['username[%]'=>'anything'], $cache);

/* Return: Array
 *    id       Score
    [2006] => 75.4323%
    [1009] => 66.6666%
    [49]   => 60%
    [5218] => 57.2574%
    [71]   => 54%
*/

For better performance, the $id should be the row_id, Primary key, or Unique key. After you got the scores, you can obtain another data from the database by it's ID that returned after the prediction.

$ids = array_keys($scores);
$data = $neko->select('users', ['user_id', 'username'], ['user_id'=>$ids]);

// Sort the received data from database
Scarlets\Extend\Arrays::sortWithReference($data, 'user_id', $ids);
// Do something with $data

Insert row

Insert row to table

$myDatabase->insert($tableName, $object, $getInsertID = false);

$primary_id = $myDatabase->insert('users', [
    'name'=>'Alex Andreas',
    'username'=>'alexan',
    ...
], true);

Bulk insert is available when you put indexed array into $object parameter.

Update row

Update some matched row in a table

$myDatabase->update($tableName, $object, $where = false);

$myDatabase->update('posts', [
    'name[replace]'=>['Water', 'Fire'], // from 'Clean the water' to 'Clean the Fire'
    'author'=>'Brian', // from 'any' to 'Brian'
    ...
], ['LIMIT'=>1]);

Addional option when updating row

Options	Details
replace	Replace needle with text
wrap	Wrap text between text ['name[wrap]'=>['maria', 'william']] will result maria ... william
append	Append text
prepend	Prepend text
* / + - %	Do a math equation ['counter[+]'=>1]
,++	(single,array) add number into a list separated by comma
,--	(single,array) remove a number from list separated by comma

Delete row

Delete row from table where some condition are true. If $where is set to false, this will truncate the table itself.

$myDatabase->delete($tableName, $where = false);

Drop table

Drop a table

$myDatabase->drop($tableName);

The other database library documentation is almost similar with SFDatabase-js