Home

#Flunotette usage Flunorette mirrors usage patterns of Nette Database [Tables], so more detailed info is covered there.

Here i will try cover features that Flunorette differs from Nette Database.

Download

Composer

require: {
    "icaine/flunerete": "~1.0"
}

Download

Here.

Creating Connection:

//The Flunorette\Connection class is a wrapper around the PDO and represents a connection to the database
$connection = new Flunorette\Connection($dsn, [$user], [$password], [$options]);

Connection options:

[
    'driverClass' => string, 
    'lazy' => bool, //lazy connection?
    'transactionCounter' => bool, //use Flunorette\TransactionCounter?
    'delimiteMode' => SqlPreprocessor::DELIMITE_MODE_DEFAULT,
    [driverSpecificOptions]
]

Note: Transaction counter allows you to nest multiple transactions.

Via NetteExtension:

extensions:
    flunorette: Flunorette\Bridges\Nette\FlunoretteExtension

flunorette:
    default:
        dsn:        "mysql:host=127.0.0.1;dbname=test"
        user:       "root"
        password:   "password"
        options:    [PDO::MYSQL_ATTR_COMPRESS = true]
        debugger:   true        # debugger bar panel
        explain:    true        # explain queries in debugger bar
        reflection: discovered  # or conventional or classname, default is discovered
        autowired:  true
    anotherConnection:
        dsn: ...
        autowired: false

New features (not in NDBT)

Common

• Preprocessor slighty improved so delimiting won't delimite things within sql strings.
• Added an ability to set how to delimite (none, replace + add).

Query builder

Standalone query builder has been added. It is heavily inspired by FluentPDO so creation of queries is as simply as we are used in NDBT (even with automatic table joining).

Features

• Same API as Selections (actually Selections use this query builder within) but with more freedom.
• IQueryObject (SqlLiteral, *Query, Selection) can be passed to any clause (select, join, order, etc.) as parameter: e.g. ->select('?', new SqlLiteral(...)).
• Any clause can be reset by setting it to NULL (e.g. ->select(null)->select('id')) and thus modified.
• Named parameters can be used. (As in PDO - that means either annonymous or named params can be used).
• Auto table joining like in Selections (e.g. $bookQuery->select('book.id, GROUP_CONCAT(book_tag:tag.name)')).
• JOIN clauses can be explicitly set. (e.g. $users->leftJoin('article ON user.id = article.user_id'))
• Ability to simply hint which column use to join tables (e.g. ->select('COUNT(article#approver:id)') or ->leftJoin('user#approver AS approver'))

Examples:

//NOTE: Much more usecase examples can be found in tests/Queries/*

// basic usage
$query = $connection->createSelect('user'); // 'user' is table name
$query->where('FOO(?)', 1)->where('FOO(id, ?)', 1);
$query->where('id & ? = ?', 1, 1);
$query->where('?', 1);
$query->where('NOT ? OR ?', 1, 1);
$query->where('? + ? - ? / ? * ? % ?', 1, 1, 1, 1, 1, 1);

// to retrieve what you need
var_dump($query->getQuery()); // returns complete sql with placeholders
var_dump($query->getParameters()); // returns merged parameters
var_dump($query->getQueryExpaned()); // returns sql with placeholders expanded (e.g. good for debugging)
$rows = $connection->fetchAll($query);


// aliases can be used as well
$query = $connection->createSelect('user AS u'); 
$query->select('u.*');
$query->wherePrimary(1);


// auto joins are available too
$query = $connection->createSelect('user');
$query->select('user.*, COUNT(comment:id) AS comment_count');


// but joins can be set/aletered manually
$query = $connection->createSelect('article');
$query->leftJoin('user AS approver');

$query = $connection->createSelect('article');
$query->leftJoin('user AS approver ON article.approver_id = approver.id');

$query = $connection->createSelect('article');
$query->leftJoin('user#approver AS approver'); //note the '#approver' which is hint which column/key use to go through


// need by hand written sql in query? no problem
$query->order('name IN (?)', new SqlLiteral('SELECT name FROM user WHERE name IN (?)', array('Marek', 'Dan)));

// or another query
$inner = $connection->createSelect('user');
$inner->select('name');
$inner->where('name', array('Marek'));
$query = $connection->createSelect('user');
$query->order('name IN (?)', $inner);

// or even Selection
$inner = $connection->table('user');
$inner->select('name');
$inner->where('name', array('Marek'));
$query = $connection->createSelect('user');
$query->order('name IN (?)', $inner);


// there are query builders for insert, update and delete too
$query = $connection->createInsert('user');
$query->values(array( // multi insert
	array(
		'name' => 'Catelyn Stark',
		'web' => 'http://example.com',
		'born' => new DateTime('2011-11-11'),
	),
	array(
		'name' => 'Sansa Stark',
		'web' => 'http://example.com',
		'born' => new DateTime('2021-11-11'),
	),
));


// moreover you can transform one query to another
$select = $connection->createSelect('user');
$select->select('user.*')->wherePrimary(1);
$delete = DeleteQuery::fromQuery($select);

Selections

Features

• Were refactored to use the query builder within their guts and thus provide more freedom to create/modify complex selections.
• That includes specifying join type, creating conditions in joins and more (added ->join(), ->leftJoin methods).
• The query builder can be accessed through ->getSqlBuilder()
• Column usage prediction was left behind (since it was kinda buggy).

Example

if ($userIds) {
    $ratingJoin = array(
        'ratings ON branches_categories.id = ratings.branch_category_id AND ' .
        'ratings.created_by IN (?)'
    , $userIds);
} else {
    $ratingJoin = array('ratings ON branches_categories.id = ratings.branch_category_id');
}

$selection = $this->getBaseSelection()
    ->select('branches_categories.category_id AS category_id')
    ->select('criterion_ratings.criterion_id')
    ->select('criterion_ratings.value AS rating_value')
    ->select('ratings.created_on AS rated_on')
    ->select('ratings.created_by AS rated_by')
    ->leftJoin('branches_categories:')
    ->leftJoin($ratingJoin)
    ->leftJoin('criterion_ratings ON ratings.id = criterion_ratings.rating_id')
    ->group('branches.id')
;

ActiveRows

Features

• Ability to create an empty row via $selection->createRow().
• In contrary of NDBT2.1+ i decided to keep methods update(), insert().
• Added save() method that automatically choose between update() or insert().

DiscoveredReflection

• Build cache only once (needs empty cache after structure is changed)
• Hints when wrong direction of relation is used (e.g. $articles->where('article_tag.tag_id', $tagIds) will tell you that wrong direction is used - correct is $articles->where('article_tag:tag_id', $tagIds)).

Hydrators

    // a callback can be used
    $rows = $connection->query('SELECT * FROM table')->hydrate(function (Flunorette\Statement $statement) {
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    });
    // or directly
    $rows = $connection->hydrate($callback, 'SELECT * FROM table');
    
    // or any of Flunorette\Hydrators\Hydrator*
    $arrayHashes = $connection->hydrate(new HydratorArrayHash(), 'SELECT * FROM table WHERE id', $ids);

Selection factory

Selections and ActiveRows are created by ISelectionFactory, so you can freely override it and use your own classes.

Transaction counter

TransactionCounter adds ability to simply nest transactions without errors.

$connection->beginTransaction();
... 
    function some_function_that_needs_transaction_as_well() use ($connection) {
        $connection->beginTransaction();
        ...
        $connection->commit();
    }
...
$connection->commit();

Tracy - DebugPanel

Similar to NDB one but with filter table added: