[2.0] Use new database Layer?

Mark threads with "[2.0]" for discussions about features in the longer-term future, "[1.6]" is for short-term. This is not the place for general discussions or plugin or template requests. Only features that are approved to happen by the core team should be listed here for better structuring.
User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

[2.0] Use new database Layer?

Post by onli » Sat Jul 06, 2013 1:28 am

For 2.0, we could use a database wrapper or even an ORM to replace the current Database Layer, as it was suggested by falk. Though the current layer has a few nice options like replacement of vendor-specific SQL, integrating an external solution could work better and help in the long run not doing unnecessary work maintaining our custom solution if that is not necessary.

And the current situation invites plugin-authors to write plain SQL-statements and makes escaping variables a manual process, which is a security risk and also produces code which only works with specific databases (or at least was only tested with them).

Two options i see so far: http://www.notorm.com/ and http://framework.zend.com/manual/2.2/en ... apter.html.

Options, suggestions or opinions?

User avatar
kleinerChemiker
Regular
Posts: 765
Joined: Tue Oct 17, 2006 2:36 pm
Location: Vienna/Austria
Contact:

Re: [2.0] Use new database Layer?

Post by kleinerChemiker » Sat Jul 06, 2013 1:42 pm

Don't forget ADOdb: http://adodb.sourceforge.net/

We also have to have look for the licens. ADOdb is BSG LGPL dual licenced. And it also provides a compiled PHP extansion.

User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: [2.0] Use new database Layer?

Post by onli » Sat Jul 06, 2013 3:25 pm

Thanks. Yes, That is a good candidate as well. The ?-Syntax is exactly what I'm used to

Code: Select all

$DB->Execute("select * from table where key=?",array($key));
Drawback of that solution would be that we would still have to fight with inconsistencies between the SQL-Implementations, correct?

User avatar
kleinerChemiker
Regular
Posts: 765
Joined: Tue Oct 17, 2006 2:36 pm
Location: Vienna/Austria
Contact:

Re: [2.0] Use new database Layer?

Post by kleinerChemiker » Sat Jul 06, 2013 7:04 pm

Right, there are only functions to create the syntax for updates and inserts.

If we use one of your suggested ones, I would prefere zend. Its seems much easier and Zend also has some other interesseting classes (cache, captcha, etc.).

User avatar
garvinhicking
Core Developer
Posts: 30015
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: [2.0] Use new database Layer?

Post by garvinhicking » Mon Jul 08, 2013 9:31 am

Hi!

Ideally we should use a DB layer, not a ORM tool - because in blog context we IMO too often need to execute very specific queries that would be hard to map to objects. Plus, performance is a huge factor, so we should not "waste" time on creating and maintaining object representations of the DB tables.

The DB layer to pick should be able to still allow "hard coded" queries to pass through, so for migration we could map serendipity_db_query("...") to that tool, and over the time change each serendipity_db_* call to the new API, and then in some future version deprecate serendipity_db_* syntax.

Best regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

falk
Regular
Posts: 512
Joined: Tue Sep 27, 2005 10:16 am
Location: DD
Contact:

Re: [2.0] Use new database Layer?

Post by falk » Fri Jul 19, 2013 8:20 pm

The Zend Framework has a very good DB-Layer implementation. That is my first choise. A other way is to use doctrine. It is a ORM, but have nice features like caching and lacy loading. Both have support and a modern coding standard.

If you don't like ORMs, i prefere ZF2.

User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: [2.0] Use new database Layer?

Post by onli » Sat Jul 20, 2013 6:03 pm

Alright. I think I'll try to implement that in a branch. By doing that i should find out how complex it is - I hope it is not that much. Of course, if someone else is faster doing that than I am, I'll happily investigate that implementation.

artodeto
Regular
Posts: 12
Joined: Sat Jul 30, 2011 12:07 am
Contact:

Re: [2.0] Use new database Layer?

Post by artodeto » Sun Jul 28, 2013 11:25 pm

For the db layer i would propose propel.
It is fast, can be used to execute native sql and is quite fast (search for benchmarks and the one you like will win ;-)).

I like the idea of having a configuration file and the generator does all the php code generation. After that, you can adapt your business items with logic.

User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: [2.0] Use new database Layer?

Post by onli » Mon Jul 29, 2013 1:11 pm

Thanks for the suggestion.

Using an ORM for an existing model and codebase is a lof of work though, even with the native capabilites you linked, and some of the queries in the core are damned difficult. So it's likely an simpler layer might be better. But I still didn't have the time to try out some of those options, maybe they will surprise me. It's on my TODO.

User avatar
mattsches
Regular
Posts: 435
Joined: Sat Nov 05, 2005 10:35 pm
Location: Wiesbaden, Germany
Contact:

Re: [2.0] Use new database Layer?

Post by mattsches » Mon Jul 29, 2013 3:15 pm

Just to chip in my 2 cents: The Doctrine2 DBAL is also something to be considered. It's a database abstraction layer and comes with a powerful query builder.

The DBAL can be used without the ORM, of course (and would be my lib of choice in this case).

artodeto
Regular
Posts: 12
Joined: Sat Jul 30, 2011 12:07 am
Contact:

Re: [2.0] Use new database Layer?

Post by artodeto » Mon Jul 29, 2013 10:15 pm

And if the choice should be between doctrine or propel, there can't be a fail in it.
Both are nice orm's and it is only a matter of choice and personal preferences (or the guy who is responsible for the layer) to find the better solution.

(just to finish my second part of the two cents ;-)).

User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: [2.0] Use new database Layer?

Post by onli » Tue Jul 30, 2013 8:06 pm

Ok. I now tried out some of the stuff. I created a include/db/generic.inc.php which gets loaded instead of the db-specific files, added the same functions (but empty) and tried to call the db-wrappers from there, only connect and query for now.

Doctrine didn't fit because it seems to only supports PDO-driver. I want the option to have people who upgrade reuse their mysqli. And I'm not convinced the query-builder would be usable for us.

Propel seems to have the same issue, only PDO. Besides, the whole "Now we generate a xml-file specifying the database" seems wrong to me, even though I noticed the reverse-engineering approach.

Same with Notorm, PDO.

ADOdb seemed great at first. Mature, wide support of drivers, including but not limited to PDO. Installing is as easy as coyping the files where they belong and including adodb.inc.php - great. But while trying it out, it seems like there is no way to reliable connect with one piece of code to all databases. Something like adodb->connect(Database-data)... The connect-method varies from driver to driver, and the one generic way, dsn (the driver://user:pw@host/dbname-syntax) is neither really generic (e.g. sqlite expecting no hostname, user or password, it can't be called with those being empty) and is simply bugged on my testsystem, prepending ";dbname=" when initialiting a sqlite-database.

So Zend\Db remains. Honestly, my first impression isn't that good. It is no fun to use composer when it's not even in the repositories, and it seems impossible to use it without composer, manually. I feel like I'm trying to get opaque java-bs running, simply because there is no complete guiding configuration for my case.

But fetching composer from http://getcomposer.org/, executing 'php composer.phar install' for this composer.json

Code: Select all

{
    "repositories": [
        {
            "type": "composer",
            "url": "http://packages.zendframework.com/"
        }
    ],
    "require": {
        "php": ">=5.3.3",
        "zendframework/zend-db": "2.*"
    }
}
(via) and adding

Code: Select all

require_once("vendor/autoload.php")
to serendipity_config.inc.php, it seems like the wrapper is starting to work. Of course it displays nothing but errors, but that i hopefully can work with.

The resulting code seems good to me:

Code: Select all

function serendipity_db_connect() {
    global $serendipity;

    if (isset($serendipity['dbConn'])) {
        return $serendipity['dbConn'];
    }

    $serendipity['dbConn'] = new Zend\Db\Adapter\Adapter(
        array(
            'driver' => $serendipity['dbType'],
            'database' => $serendipity['dbName'] . '.db',
            'username' => $serendipity['dbUser'],
            'password' => $serendipity['dbPass']
        )
    );


    return $serendipity['dbConn'];
}
Or (not working):

Code: Select all

function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
    global $serendipity;
    return $serendipity['dbConn']->query($sql);
}

User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: [2.0] Use new database Layer?

Post by onli » Fri Aug 02, 2013 1:19 am

Well, I continued. That is how my current working implementation looks like: https://gist.github.com/onli/6136122

The current API would remain working. Zend\Db generates the sql used by the different db-engines, most of the time. A new approach for queries could be used:

Code: Select all

serendipity_db_query2("SELECT * FROM entries WHERE e.id=?", array(1))
And, though that would bind us further to zend, one could use the sql-builder by accessing the adapter:

Code: Select all

$sql = new Zend\Db\Sql\Sql($serendipity['dbConn']);
$select = new Select;
$select->from('foo');
$select->where(array('id' => 2));
serendipity_db_query2($sql->getSqlStringForSqlObject($select));
I'm not perfectly happy with some of the hacks I had to use to make this work, and that CONCAT doesn't seem to be supported by zend. But because of the parameter insertion it would still be an improvement, I think.

I'd like to commit this to 2.0 if no objections occur in the next days - the old system could stay while this is developed. To switch between the two systems, one just has to either include this one or one of the current db/{dbType}.inc.php-files in db.inc.php.

User avatar
mattsches
Regular
Posts: 435
Joined: Sat Nov 05, 2005 10:35 pm
Location: Wiesbaden, Germany
Contact:

Re: [2.0] Use new database Layer?

Post by mattsches » Sat Aug 10, 2013 12:33 pm

Ok, I tested it and it looks good so far (using the Mysqli driver), but I've already found some problems.

There was a typo in your Gist in the parameters for the Zend\Db\Adapter, and I also had to add the charset, too, because otherwise my Umlauts were wrong ;)

See my fork of your Gist here.

Apart from that, everything is looking fine at first sight. Yet my gut feeling tells me that we need to test this more. There might be some edge cases, e.g. with different drivers.

I also tried the Pdo_Mysql driver, and now the Adapter must be created like this in my case:

Code: Select all

    $serendipity['dbConn'] = new Adapter(
        array(
            'driver' => 'Pdo_Mysql',
            'database' => $dbName,
            'username' => $serendipity['dbUser'],
            'password' => $serendipity['dbPass'],
            'hostname' => $serendipity['dbHost'],
            'charset'  => $serendipity['dbCharset'],
            'driver_options' => array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . $serendipity['dbCharset'],
            ),
        )
    );
So, while most of it seems to work pretty fine .. if you add it do 2.0, make sure it isn't the default setting for now. It might need some more work :wink:

PS: Is there a way to tell Composer to install the external libs to 'bundled-libs' instead of vendor? I tried the package consumer solution mentioned here, but it did not work for me.

User avatar
onli
Regular
Posts: 2163
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: [2.0] Use new database Layer?

Post by onli » Sat Aug 10, 2013 2:17 pm

Thanks. (We already talked about this, I'll just protocol it in short for the others)

I'll push it as an option into 2.0. Using bundled_libs instead of vendors would be great, I saw no configuration for that though. Hope you find something. The additional driver-specific options needed suck, I hope we find a better way, things like that defeat the purpose of the whole thing.

Locked