Post A Reply

English

Change Language

Post a reply

Write your message and submit

Name

E-mail

Message

[quote=grum][quote=VDigital]config_database.inc.php (Not LocalFiles Editable)
config_default.inc.php (Not LocalFiles Editable)
config_local.inc.php ( LocalFiles Editable and with database config overrides enable ).[/quote]
Ok to rename the mysql.inc.php file into "config_database.inc.php" : if we want to be able to manage other databases engines (SQLite, PostgreSQL) it's a good thing to have a config file name not database oriented.
And Ok to use a $conf[] value

But I don't understand why we want to allow the database config in [b]the config_local.inc.php[/b] ?

I think it's a hole for the security.
You can try to hide database config in the editor, so in this case, I really don't understand the "plus value".
You can try to improve the LocalFileEditor to prevent error, but LocalFileEditor can have a bug, or the user can made mistake not managed by the editor.

About the database improvment, I can read [i]"I mean using functions like pwg_query() but more specific : pwg_db_connect(), pwg_db_insert(), pwg_db_delete(), pwg_db_mass_insert(),..."[/i]
May be we can made a class providing the database functions.
Something like $pwg_db->querie($sql) or $pwg_db->fetch_row() 
By using something like this, it will be easiest to use SQLite or PostgreSQL.
And probably we can use framework dedicated to this..[/quote]

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

LucMorizur: 2010-10-09 23:00:12

plg wrote:
Hi LucMorizur,

LucMorizur wrote:
Is the compatibility of these three engines with SQL generic language, too weak?
Yes it is. We obviously have many SQL queries successul with MySQL that fail in PostgreSQL.

OK, at least do I understand something better :-) .

plg wrote:
LucMorizur wrote:
functions_pgsql.inc.php: 6 functions [...]
I think we're not looking at the same file. I have much more than 6 functions for PostgreSQL (or it would not work at all).

I was talking only about the first functions of each files. Then in the last part of the files, are placed more functions, which are common for the three engines.

Anyway I guess you know this part better than I do ;-) ...

plg: 2010-10-09 22:38:55

Hi LucMorizur,

LucMorizur wrote:
Is the compatibility of these three engines with SQL generic language, too weak?

Yes it is. We obviously have many SQL queries successful with MySQL that fail in PostgreSQL.

LucMorizur wrote:
I mean, should we indeed test for which engine is used, to write different queries statements?

We should care about writting SQL code that works with the 3 engines, with the known incompatibilities in mind. I agree that it would be a good thing to have a wiki page with these known problems, I will ask some help to nicolas.

LucMorizur wrote:
functions_pgsql.inc.php: 6 functions [...]

I think we're not looking at the same file. I have much more than 6 functions for PostgreSQL (or it would not work at all).

LucMorizur: 2010-10-06 08:57:24

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 ?

LucMorizur: 2010-10-01 08:32:58

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

plg: 2010-07-27 18:55:55

grum wrote:
What do you think ?

I think you're right.

grum: 2010-07-27 18:51:59

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 ?

rub: 2010-03-02 09:52:07

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.

rub: 2010-03-02 09:50:57

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.

rub: 2010-03-02 09:48:18

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.

grum: 2010-03-01 21:55:07

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.

nicolas: 2010-03-01 20:26:44

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.

nicolas: 2010-03-01 20:21:30

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 ?

rub: 2010-03-01 20:12:32

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);

grum: 2010-03-01 20:06:38

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

nicolas: 2010-03-01 10:04:43

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

Piwigo.org

Announcement

Topic review (newest first)

plg wrote:

LucMorizur wrote:

plg wrote:

LucMorizur wrote:

LucMorizur wrote:

LucMorizur wrote:

LucMorizur wrote:

Code:

rub wrote:

grum wrote:

Code:

Code:

Code:

Code:

grum wrote:

grum wrote:

nicolas wrote:

Code:

Code:

nicolas wrote:

nicolas wrote:

nicolas wrote:

Code:

Code:

rub wrote:

nicolas wrote:

grum wrote:

nicolas wrote:

nicolas wrote:

rub wrote:

rub wrote:

Board footer