PHP code example of fulfillment / picodb

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

fulfillment / picodb example snippets

use PicoDb\Database;

// Sqlite driver
$db = new Database(['driver' => 'sqlite', 'filename' => ':memory:']);

// Optional attributes:
// "schema_table" (the default table name is "schema_version")

$db = new Database([
    'driver' => 'mssql',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'my_db_name',
]);

$db = new Database([
    'driver' => 'mysql',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'my_db_name',
    'ssl_key' => '/path/to/client-key.pem',
    'ssl_cert' => '/path/to/client-cert.pem',
    'ssl_ca' => '/path/to/ca-cert.pem',
]);

$db = new Database([
    'driver' => 'postgres',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'my_db_name',
]);

use PicoDb\UrlParser;
use PicoDb\Database;

$db = new Database(UrlParser::getInstance()->getSettings());

use PicoDb\UrlParser;
use PicoDb\Database;

$db = new Database(UrlParser::getInstance()->getSettings('postgres://user:pass@hostname:6212/db'));

$db->execute('CREATE TABLE mytable (column1 TEXT)');

$db->table('mytable')->save(['column1' => 'test']);

$db->table('mytable')->insert(['column1' => 'test']);

$db->getLastId();

$db->transaction(function ($db) {
    $db->table('mytable')->save(['column1' => 'foo']);
    $db->table('mytable')->save(['column1' => 'bar']);
});

$db->startTransaction();
// Do something...
$db->closeTransaction();

// Rollback
$db->cancelTransaction();

$records = $db->table('mytable')->findAll();

foreach ($records as $record) {
    var_dump($record['column1']);
}

$db->table('mytable')->eq('id', 1)->save(['column1' => 'hey']);

$db->table('mytable')->eq('id', 1)->update(['column1' => 'hey']);

$db->table('mytable')->lt('column1', 10)->remove();

$db->table('mytable')->asc('column1')->findAll();

$db->table('mytable')->desc('column1')->findAll();

$db->table('mytable')->orderBy('column1', 'ASC')->findAll();

$db->table('mytable')->asc('column1')->desc('column2')->findAll();

$db->table('mytable')->limit(10)->offset(5)->findAll();

$db->table('mytable')->columns('column1', 'column2')->findAll();

$db->table('mytable')->findAllByColumn('column1');

$db->table('mytable')->findOneColumn('column1');

$db->table('mytable')->select(1)->eq('id', 42)->findOne();

$db->table('mytable')->distinct('columnA')->findOne();

$db->table('mytable')->groupBy('columnA')->findAll();

$db->table('mytable')->count();

$db->table('mytable')->sum('columnB');

$db->table('mytable')->sumColumn('mycolumn', 42)->update();

$db->table('mytable')->eq('another_column', 42)->increment('my_column', 2);

$db->table('mytable')->eq('another_column', 42)->decrement('my_column', 1);

$db->table('mytable')->eq('column1', 12)->exists();

// SELECT * FROM mytable LEFT JOIN my_other_table AS t1 ON t1.id=mytable.foreign_key
$db->table('mytable')->left('my_other_table', 't1', 'id', 'mytable', 'foreign_key')->findAll();

// SELECT * FROM mytable LEFT JOIN my_other_table ON my_other_table.id=mytable.foreign_key
$db->table('mytable')->join('my_other_table', 'id', 'foreign_key')->findAll();

$db->table('mytable')
   ->eq('column1', 'hey')
   ->findAll();

$db->table('mytable')
       ->in('column1', ['hey', 'bla'])
       ->findAll();

$subquery = $db->table('another_table')->columns('column2')->eq('column3', 'value3');

$db->table('mytable')
       ->columns('column_5')
       ->inSubquery('column1', $subquery)
       ->findAll();

$db->table('mytable')
   ->like('column1', '%Foo%')
   ->findAll();

$db->table('mytable')
   ->ilike('column1', '%foo%')
   ->findAll();

$db->table('mytable')
   ->lt('column1', 2)
   ->findAll();

$db->table('mytable')
   ->lte('column1', 2)
   ->findAll();

$db->table('mytable')
   ->gt('column1', 3)
   ->findAll();

$db->table('mytable')
    ->gte('column1', 3)
    ->findAll();

$db->table('mytable')
   ->isNull('column1')
   ->findAll();

$db->table('mytable')
   ->notNull('column1')
   ->findAll();

$db->table('mytable')
    ->like('column2', '%mytable')
    ->gte('column1', 3)
    ->findAll();

$db->table('mytable')
    ->beginOr()
    ->like('column2', '%mytable')
    ->gte('column1', 3)
    ->closeOr()
    ->eq('column5', 'titi')
    ->findAll();

$db->getStatementHandler()->withLogging();

$db->getStatementHandler()->withStopWatch();

echo $db->getStatementHandler()->getNbQueries();

print_r($db->getLogMessages());

$db->largeObject('my_table')->insertFromFile('blobColumn', '/path/to/file', array('id' => 'something'));

$db->largeObject('my_table')->insertFromStream('blobColumn', $fd, array('id' => 'something'));

$fd = $db->largeObject('my_table')->eq('id', 'something')->findOneColumnAsStream('blobColumn');

echo $db->largeObject('my_table')->eq('id', 'something')->findOneColumnAsString('blobColumn');

$db->execute(
     'CREATE TABLE mytable (
         column1 TEXT NOT NULL UNIQUE,
         column2 TEXT default NULL
     )'
);

$db->table('mytable')->insert(['column1' => 'option1', 'column2' => 'value1']);

$db->hashtable('mytable')
   ->columnKey('column1')
   ->columnValue('column2')
   ->put(['option1' => 'new value', 'option2' => 'value2']));

$result = $db->hashtable('mytable')->columnKey('column1')->columnValue('column2')->get();
print_r($result);

Array
(
    [option2] => value2
    [option1] => new value
)

$result = $db->hashtable('mytable')->getAll('column1', 'column2');

$db->hashtable('mytable')
   ->columnKey('column1')
   ->columnValue('column2')
   ->put(['option3' => 'value3']);

$result = $db->hashtable('mytable')
             ->columnKey('column1')
             ->columnValue('column2')
             ->get('option1', 'option3');

print_r($result);

Array
(
    [option1] => new value
    [option3] => value3
)

namespace Schema;

function version_1($pdo)
{
    $pdo->exec('
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE,
            email TEXT UNIQUE,
            password TEXT
        )
    ');
}


function version_2($pdo)
{
    $pdo->exec('
        CREATE TABLE tags (
            id INTEGER PRIMARY KEY,
            name TEXT UNIQUE
        )
    ');
}

$last_schema_version = 5;

$db = new PicoDb\Database(array(
    'driver' => 'sqlite',
    'filename' => '/tmp/mydb.sqlite'
));

if ($db->schema()->check($last_schema_version)) {

    // Do something...
}
else {

    die('Unable to migrate database schema.');
}

PicoDb\Database::setInstance('myinstance', function() {

    $db = new PicoDb\Database(array(
        'driver' => 'sqlite',
        'filename' => DB_FILENAME
    ));

    if ($db->schema()->check(DB_VERSION)) {
        return $db;
    }
    else {
        die('Unable to migrate database schema.');
    }
});

PicoDb\Database::getInstance('myinstance')->table(...)