Jump to content
MrSnoozles

Better database abstraction

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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. 

  • Like 2

Share this post


Link to post
Share on other sites

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.

  • Like 6

Share this post


Link to post
Share on other sites

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)

 

  • Like 1

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

@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']);

 

  • Like 5

Share this post


Link to post
Share on other sites
$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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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;
}

 

  • Like 13

Share this post


Link to post
Share on other sites
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 MedooPropel3 and lessql.

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...