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.

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)

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 ?

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.

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)

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 ?

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 :-/ ...)