[2.0] Use new database Layer?
[2.0] Use new database Layer?
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?
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?
-
- Regular
- Posts: 765
- Joined: Tue Oct 17, 2006 2:36 pm
- Location: Vienna/Austria
- Contact:
Re: [2.0] Use new database Layer?
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.
We also have to have look for the licens. ADOdb is BSG LGPL dual licenced. And it also provides a compiled PHP extansion.
Re: [2.0] Use new database Layer?
Thanks. Yes, That is a good candidate as well. The ?-Syntax is exactly what I'm used to
Drawback of that solution would be that we would still have to fight with inconsistencies between the SQL-Implementations, correct?
Code: Select all
$DB->Execute("select * from table where key=?",array($key));
-
- Regular
- Posts: 765
- Joined: Tue Oct 17, 2006 2:36 pm
- Location: Vienna/Austria
- Contact:
Re: [2.0] Use new database Layer?
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.).
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.).
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: [2.0] Use new database Layer?
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
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/
# 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/
Re: [2.0] Use new database Layer?
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.
If you don't like ORMs, i prefere ZF2.
Re: [2.0] Use new database Layer?
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.
Re: [2.0] Use new database Layer?
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.
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.
Re: [2.0] Use new database Layer?
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.
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.
Re: [2.0] Use new database Layer?
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).
The DBAL can be used without the ORM, of course (and would be my lib of choice in this case).
Re: [2.0] Use new database Layer?
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 ).
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 ).
Re: [2.0] Use new database Layer?
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
(via) and adding
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:
Or (not working):
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.*"
}
}
Code: Select all
require_once("vendor/autoload.php")
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'];
}
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);
}
Re: [2.0] Use new database Layer?
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:
And, though that would bind us further to zend, one could use the sql-builder by accessing the adapter:
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.
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))
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'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.
Re: [2.0] Use new database Layer?
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:
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
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.
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'],
),
)
);
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.
Re: [2.0] Use new database Layer?
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.
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.