Announcement

  •  » Engine
  •  » Database improvements

#76 2010-02-09 11:32:23

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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

 

#77 2010-03-01 07:39:18

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

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

 

#78 2010-03-01 10:04:43

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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

 

#79 2010-03-01 20:06:38

grum
Piwigo Team
Nantes
2007-09-10
1371

Re: Database improvements

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)


My pictures with Piwigo, of course !
[ www.grum.fr ] [ photos.grum.fr ]

Offline

 

#80 2010-03-01 20:12:32

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

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

 

#81 2010-03-01 20:21:30

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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

 

#82 2010-03-01 20:26:44

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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

 

#83 2010-03-01 21:55:07

grum
Piwigo Team
Nantes
2007-09-10
1371

Re: Database improvements

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

Code:

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.


My pictures with Piwigo, of course !
[ www.grum.fr ] [ photos.grum.fr ]

Offline

 

#84 2010-03-02 09:48:18

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

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

 

#85 2010-03-02 09:50:57

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

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

 

#86 2010-03-02 09:52:07

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

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 like

Code:

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

 

#87 2010-07-27 18:51:59

grum
Piwigo Team
Nantes
2007-09-10
1371

Re: Database improvements

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 ?


My pictures with Piwigo, of course !
[ www.grum.fr ] [ photos.grum.fr ]

Offline

 

#88 2010-07-27 18:55:55

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13182

Re: Database improvements

grum wrote:

What do you think ?

I think you're right.


Latest blog post (November 9th 2018) Why Flickr could not remain free for ever

Offline

 

#89 2010-10-01 08:32:58

LucMorizur
Member
Vienne (Isère) - France
2009-04-30
168

Re: Database improvements

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

Code:

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

Code:

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

Code:

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

Code:

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


Our gallery : Le Site à Nous (a silly name in french, but here I don't care ;-) ! )
An event, a new gallery ? Plugin Event Cats
My test gallery : Tests Piwigo de Luc
Thanksalot for this beautiful project.

Offline

 

#90 2010-10-06 08:57:24

LucMorizur
Member
Vienne (Isère) - France
2009-04-30
168

Re: Database improvements

No answer :-( ?...

I have another question : I'm currently using the test

Code:

if (mysql_errno() == 0) { /* ... */ }

By which type of code should I replace it, to make the code fully compatible with other DB types ?


Our gallery : Le Site à Nous (a silly name in french, but here I don't care ;-) ! )
An event, a new gallery ? Plugin Event Cats
My test gallery : Tests Piwigo de Luc
Thanksalot for this beautiful project.

Offline

 
  •  » Engine
  •  » Database improvements

Board footer

Powered by FluxBB

github twitter facebook google+ newsletter Donate Piwigo.org © 2002-2019 · Contact