plg wrote:
nicolas wrote:
Thanks for your message. I'm not sure to really understand what you really want to say.
Maybe because jochen was quoting me. I have edited his message now.
Ok. I understand now ! Thanks.
Offline
There are always 4 kind of database usable on Piwigo (great!) but I am a doubt about management of several database.
My doubt is about creation/modification of new tables.
At moment, there are on file by type on database.
My questions are:
o One file by database is not good for maintainability, isn't it? In order to modify a table, it's necessary to change x files!
o How plugin developers will work? They will be obliged to write x different syntaxes on their plugin in order to create/modify table?
But, I don't have really a good solution.
Perhaps, we can do a engine witch will translate create/modify instructions on good database language.
In this case, only one file with table description but x engines.
And plugin developers will maintain only one version of table file.
Offline
rub wrote:
There are always 4 kind of database usable on Piwigo (great!)
I think so but it's just the beginning !
but I am a doubt about management of several database.
rub wrote:
My doubt is about creation/modification of new tables.
At moment, there are on file by type on database.
My questions are:
o One file by database is not good for maintainability, isn't it? In order to modify a table, it's necessary to change x files!
o How plugin developers will work? They will be obliged to write x different syntaxes on their plugin in order to create/modify table?
But, I don't have really a good solution.
Perhaps, we can do a engine witch will translate create/modify instructions on good database language.
In this case, only one file with table description but x engines.
And plugin developers will maintain only one version of table file.
I hope someone ask for that. Thanks ! :-)
I have no direct solution. If you see creation table files they are quite similars for posgresql and sqlite (sqlite and pdo-sqlite are the same). I think it's not really a big problem to have one creation file for each database engine. Each file is really specific and improve for his database engine and we only (in theory) install piwigo once.
For upgrade, there's only one file to create (install/NN-database.php), but 4 files to update. But we don't change our data model every morning !
I think the real problem is for plugins. You're right : plugins developer don't care of database engine abstraction. They will write only one version.
Discussion is opened !
the maintenance for creation
Offline
I don't take the time to take a look, but to manage different kind of database with the same code, do Piwigo provide some generic functions ?
pwg_query is the most used.
but, there is an equivalent for all the "mysql_" function ?
I found this on the plugins depository :
mysql_fetch_assoc
mysql_fetch_row
mysql_num_rows
mysql_insert_id
mysql_query
mysql_version
mysql_errno
mysql_error
mysql_real_escape_string
mysql_num_fields
mysql_list_tables
mysql_affected_rows
mysql_pconnect
mysql_select_db
Last edited by grum (2010-03-01 20:06:59)
Offline
nicolas wrote:
For upgrade, there's only one file to create (install/NN-database.php), but 4 files to update. But we don't change our data model every morning !
Yes, it's true. It's also a problem.
nicolas wrote:
Discussion is opened !
I am for a single solution to apply to all the contexts (install, upgrade, plugin, etc.)
Suggestion 1:
Write DDL queries on MySql. Functions or triggers allows to translate to used database language.
Suggestion 2:
Write DDL queries with an new internal structure. Functions allows to compute this data to used database language.
Suggestion 3:
All DDL orders are managed by a new php object.
With object's method, DDL orders are submitted. And the object computes order on used database language.
Example:
DBObject.CreateTable(#table_name, array od columns);
DBObject.DropTable(#table_name, array od columns);
Offline
grum wrote:
I don't take the time to take a look, but to manage different kind of database with the same code, do Piwigo provide some generic functions ?
pwg_query is the most used.
but, there is an equivalent for all the "mysql_" function ?
Yes see [Subversion] r4325
But it's just a partial solution. What is your idea about tables creations ?
Offline
rub wrote:
nicolas wrote:
For upgrade, there's only one file to create (install/NN-database.php), but 4 files to update. But we don't change our data model every morning !
Yes, it's true. It's also a problem.
But the problem is smaller and is solve by actual upgrade method.
We know that DDL is a (good ?) solution but do you know about one ? Abstraction solution are slow and we cannot improve queries for a specific database. To be clear and t o an example, look at difference between mysql and postgresql to copy a table to a temporary table for mass_insert(). Theses improvements aren't possible with an abstraction layer.
Offline
nicolas wrote:
But it's just a partial solution. What is your idea about tables creations ?
we can provide a pwg_db_createTable($tableName, $tableFields, $tableKeys)
with :
$tableName : String
$tableFields : an array like
Array( 'name' => 'fielName', type => PWG_DB_INTEGER, options => Array(PWB_DB_OPTIONS_UNSIGNED, PWG_DB_OPTIONS_NOT_NULL) )
$tableKeys : an array like
Array( 'primary' => Array('fieldName1', 'fieldName2'), 'index2' => Array('fieldName3') )
the createTable function build the SQL queries with the parameters according with the specific SQL syntax of each database system.
Offline
nicolas wrote:
Theses improvements aren't possible with an abstraction layer.
I am really surprise by this answer!
I do not see how a abstract method can prevent execute code specific to each type of database.
The core of Piwigo specifies actions and the layers perform actions with the best in their database.
Offline
nicolas wrote:
Abstraction solution are slow
Perhaps... but DDL command are used rarely and for specific actions.
And I'am not sure that was very more slow.
Last edited by rub (2010-03-02 09:53:00)
Offline
grum wrote:
nicolas wrote:
But it's just a partial solution. What is your idea about tables creations ?
we can provide a pwg_db_createTable($tableName, $tableFields, $tableKeys)
with :
$tableName : String
$tableFields : an array likeCode:
Array( 'name' => 'fielName', type => PWG_DB_INTEGER, options => Array(PWB_DB_OPTIONS_UNSIGNED, PWG_DB_OPTIONS_NOT_NULL) )$tableKeys : an array like
Code:
Array( 'primary' => Array('fieldName1', 'fieldName2'), 'index2' => Array('fieldName3') )the createTable function build the SQL queries with the parameters according with the specific SQL syntax of each database system.
It looks like my Suggestion 2:
Write DDL queries with an new internal structure. Functions allows to compute this data to used database language.
Offline
I'm actually looking for some mysql function reported in the dblayer.
And I see many functions with name starting with pwg_db_
And some functions like :
- pwg_get_db_version => why not pwg_db_get_version ?
- pwg_query => why not pwg_db_query ? for this one, I think it can be a good thing to keep an alias to the pwg_query for compatiblity, but having pwg_db_query in the next piwigo release seems to be better
- mass_insert => why not pwg_db_mass_insert ?
- and there is some other function I didn't look what the use but the idea is here
Piwigo 2.2 => having function name modified, but keep aliases on the actually name
Piwigo 2.3 => no more aliases
What do you think ?
Offline
grum wrote:
What do you think ?
I think you're right.
Offline
Hi;
I have read the first posts of this topic rapidly, and its last posts a little bit slower ;-) , and I have the following questions:
rub wrote:
How plugin developers will work? They will be obliged to write x different syntaxes on their plugin in order to create/modify table?
Piwigo can now use three database engines : MySQL, PostgreSQL and SQLite (and pdo-SQLite seems to be equivalent to SQLite). Is the compatibility of these three engines with SQL generic language, too weak? I mean, should we indeed test for which engine is used, to write different queries statements?
If there are differencies between the engines, is it possible to build a map of the differences, so to see quickly which instructions should not be used with each engine?
I did not perform a search on the following question, but it seems interesting to me that the answer is in the current topic anyway: is there feedback from Piwigo 2.1.x users, who chose PostgreSQL or SQLite, so to see if some part of the code doesn't work because of differences between these engines and MySQL?
grum wrote:
there is an equivalent for all the "mysql_" function ?
Well, here is a partial answer to one of my above questions:
looking into "dblayer" folder in 2.1 branch, we can see the following "simple functions" in the following files:
functions_mysql.inc.ph: 14 functions
pwg_db_connect($host, $user, $password, $database) pwg_db_check_charset() pwg_db_check_version() pwg_get_db_version() pwg_query($query) pwg_db_nextval($column, $table) pwg_db_changes($result) pwg_db_num_rows($result) pwg_db_fetch_assoc($result) pwg_db_fetch_row($result) pwg_db_fetch_object($result) pwg_db_free_result($result) pwg_db_real_escape_string($s) pwg_db_insert_id($table=null, $column='id')
functions_sqlite.inc.php: 6 functions
pwg_db_connect($host, $user, $password, $database) pwg_db_check_version() pwg_db_check_charset() pwg_get_db_version() pwg_query($query) pwg_db_nextval($column, $table)
functions_pdo-sqlite.inc.php: 6 functions
pwg_db_connect($host, $user, $password, $database) pwg_db_check_version() pwg_db_check_charset() pwg_get_db_version() pwg_query($query) pwg_db_nextval($column, $table)
functions_pgsql.inc.php: 6 functions
pwg_db_connect($host, $user, $password, $database) pwg_db_check_version() pwg_db_check_charset() pwg_get_db_version() pwg_query($query) pwg_db_nextval($column, $table)
So actually the next question is: is there any plan to extend these functions sets, so to have the same set of functions (is this the meaning of "abstraction layer"? Sorry, I'm actually not a developer :-/ ...) for all dblayer/functions_<db-engine>.inc.php libraries?
(Sorry, this makes a lot of questions for one single post :-/ ...)
Offline
No answer :-( ?...
I have another question : I'm currently using the test
if (mysql_errno() == 0) { /* ... */ }
By which type of code should I replace it, to make the code fully compatible with other DB types ?
Offline