SQLite CLI wrapper
composer require thipages\sqlitecli
/* index.php */
/* ********* */
$cli=new SqliteCli('./database.db');
$cli->execute(
"CREATE TABLE simple (id INTEGER PRIMARY KEY, name);",
"INSERT INTO simple (name) VALUES ('Paul'), ('Jack'),('Charlie');",
'.mode csv',
'.headers on',
'.separator ,',
'.output data.csv',
'select id,name from simple limit 2;'
);
// OR
$cli-<execute(
Orders::importCsv('simple','data.csv', ',', true),
Orders::exportCsv('data.csv', 'select id,name from simple limit 2;')
);
// run it on server
php index.php
- with functions only
/* index.php */
/* ********* */
$cli=new SqliteCli('./database.db');
$cli->execute(
"CREATE TABLE simple (id INTEGER PRIMARY KEY, name);",
"INSERT INTO simple (name) VALUES ('Paul'), ('Jack'),('Charlie');",
"SELECT name FROM simple WHERE id=1;",
function($res) {
// Set 'Paul' to all records
return "UPDATE simple SET name='$res'";
}
);
- with functions and registry
/* index.php */
/* ********* */
$cli=new SqliteCli('./database.db');
$cli->execute(
"CREATE TABLE simple (id INTEGER PRIMARY KEY, name);",
"INSERT INTO simple (name) VALUES ('Paul'), ('Jack'),('Charlie');",
"SELECT name FROM simple WHERE id=1;",
Orders::registerAs('QUERY_ID=1'),
function($res, $registry) {
$newName=$registry->get(''QUERY_ID=1'');
return "UPDATE simple SET name='$newName'";
}
);
Same result for both.
SqliteCli class
SqliteCli($dbPath)
execute(...$orders):[boolean,array]
executes sqlite commands (list of [array of] commands). This method adds a final .quit
command
getRegistry($key=null)
get the registry ouput value of $key
or if null the registry as an associated array.
Orders class
addPrimary($table,$primaryName):[boolean,array]
adds a primary field to an existing table (first position)
addField($table,$definition):[boolean,array]
adds a field to an existing table (first position)
importCsv($table, $csvPath, $separator=',', $headers='on'):array
returns an array of commands for csv import
exportCsv($csvPath, $separator=',', $headers='on'):array
returns an array of commands for csv export
mergeCsvList($table,$csvPaths, $delimiter=',')
merges csv Files into $table
. Files need to have the same fields. $delimiter
can be an array matching $csvPaths
getFieldList($table)
returns an array of the fields of $table
registerAs($key)
associates the previous output command to $key
in the registry
unregister(...$keys)
associates the previous output command to $key
in the registry