Better database abstraction

[MrSnoozles]

Better database abstraction is something I would like to see in ProcessWire in the future. Everytime I have to work with raw database queries, I always have to look up the documentation for PDO. exec, query, prepare, ... I never know how they are called and when to use which, since database abstraction libraries in frameworks make the developers life so much easier.

This is something I could very well imagine in  ProcessWire as well.

Since most of the time you're working with the pages API anyway, I don't think it has to a full blown ORM. But having just the query method and there you can insert parameters would be a great help. I'm thinking of something like a light version of https://github.com/dg/dibi.

[Martijn Geerts]

ProcessWire's database abstraction is the best of all CMS systems I know. 

 

 

[Zeka]

Hi @MrSnoozles

Have you seen this thread 

 

[MrSnoozles]

57 minutes ago, Martijn Geerts said:

ProcessWire's database abstraction is the best of all CMS systems I know. 

Definitely, as long as you are working with the pages api. There are cases though where you have to query external tables. And in that case it could be a bit more  developer friendly than standard PDO. 

@Zekathanks, will look into that.

 

What I was suggesting is simply an abstraction to always use the same api, no matter if you are inserting, querying, using prepared statements etc. 

[abdus]

Using Database class is actually quite straightforward. It's nothing but a wrapper around PHP's PDO class with some PW specific features.

You prepare a SQL statement using prepare() method, then bind input values, execute() it and fetch() the results.

<?php namespace ProcessWire;

// sanitization
$tableName = inputPost()->text('table');
$columnName = inputPost()->text('column');
$id = inputPost()->int('id');

// this is where you'd sanitize user input to prevent SQL injection
$table = database()->escapeTable($tableName); 
$column = database()->escapeCol($columnName);
$q = database()->prepare("SELECT `$column` FROM `$table` WHERE id = :id");
$q->bindValue(':id', $id); // always bind values instead of concatenation
// 
$success = false;
$values = null;
try {
    $success = $q->execute();
    $values = $q->fetchAll();
} catch (\Exception $e) {
    wire()->log($e->getMessage(), Notice::log);
}
if (!$success) {
    // handle error
}

if ($values) {
    // use values 
}

To see how PW utilizes this class, check out Fieldtype.php, FieldtypeMulti.php, PagesEditor.php and PagesLoader.php from the core.

[MrSnoozles]

Jupp, but I generally think PDO does not have a nice Api, and is cumbersome to work with. That's why I was suggesting to unify it and make it easier to work with.

It definitely should stay conform with pdo.

But I think concatenating strings to create a query is just not a great developer experience. If you could pass arrays and the library would do the query concatenation would be amazing. 

(i'm on the phone now so I can't post code, but I will try to add an example of how this feature could improve code cleanliness compared to pdo)

 

[abdus]

Yeah I agree. It's too verbose for starters. But you're free to use any PDO / ORM library over ProcessWire's. You can get database credentials with $config->db<key>, and use it anyway you'd like.

But unless you're using custom tables, you'll have to join multiple tables to get a meaningful output from pages/templates/fields, all of which PW provides out of the box for you.

[MrSnoozles]

Do you think it's a too rare use case to include some simplifications in the core? 

[abdus]

@ryan is usually pretty adamant about extending the core for a feature that very few people will use. So I doubt he'll agree on that. Because it would mean rewriting a good chunk of the core and breaking many 3rd party modules, or having to maintain a separate set of underutilized classes. Feel free to open up an issue, though.

If you study PagesLoader class you can figure out how PW fetches fields from their respective tables and maybe develop a module that works aside the current implementation.

I, for one, wouldn't mind using something along the lines of:

$list = $db->pages->posts->where('title.length>5')->fetch(['title', 'name', 'body']);

 

[Mike Rockett]

$list = $db->pages->posts->where('title.length>5')->fetch(['title', 'name', 'body']);

I do like this, interestingly enough. Looks very fluid, and there's a fetch command in there too, which does save memory, yes?

[abdus]

Yes, it's for getting only the desired fields. Normally PW fetches them lazily (to save memory) with multiple queries but with this method everything can be done in a single query. A lazy method can still be in there somewhere

[BitPoet]

11 hours ago, MrSnoozles said:

I'm thinking of something like a light version of https://github.com/dg/dibi.

My personal opinion is that database layers like dibi tend to make some things a little easier while making corner cases complicated and taking clarity away by re-using existing keywords in a sometimes non-intuitive way. Then, they don't support application features like PW's multi-language fields or status flags, so one has to reign in expectations. And, often overlooked, PW already has a nice extension to the DB layer in the DatabaseQuery* classes. Perhaps that should be put into the spotlight a bit more (and added to the API docs)?

A working example:

<?php

$q = new DatabaseQuerySelect();

$result = $q->select('templates.name')
  ->select('count(*) as inuse')
  ->from('pages')
  ->join('templates on templates.id = pages.templates_id')
  ->where('templates.name != :tplname')
  ->groupby('templates.name')
  ->bindValue(':tplname', 'admin')
  ->execute();

echo $q->getQuery() . PHP_EOL;

foreach($result->fetchAll(\PDO::FETCH_ASSOC) as $row) {
	echo $row["name"] . " => " . $row["inuse"] . PHP_EOL;
}

 

[pwFoo]

On 10/5/2017 at 8:51 PM, Martijn Geerts said:

ProcessWire's database abstraction is the best of all CMS systems I know. 

Yes! ? I would love to see such a DB class standalone for small not PW projects! Best ever!

In the last time I found some nice frameworks like Flight PHP micro-framework (router, hooks, extendable, views, ...) or db frameworks Medoo, Propel3 and lessql.