I did not understand everything...


I think the use of external tables must be reserved for advanced users, aware with PHP, SQL, Piwigo, ...

Allowing if (!defined('..._TABLE')) ... in the config_local file is a door for bugs, and how we can give answers to users wich have used this kind of tricks ?

And using external tables other than the users tables is probably not a good thing for 2 reasons :
- users can make mistakes with the table name
- it's not sure that the external table structure is defined as piwigo needs
In these 2 cases, the user could have a fatal SQL error, or data could be corrupted...

About Astat (thx VDigital :)) it use the HISTORY_TABLE constant.
So, if behind this constant the user set a january_history or a february_history table, it will work. Just, the stat gived by AStat did not have sense anymore (but this is the user problem).
After, if we have something like this implemented in piwigo (berk!), AStat must be adapted...

Two kinds of queries must be defined!

Example in regular used of Piwigo:

$query = '
SELECT id, name, uppercats, global_rank
  FROM '.CATEGORIES_TABLE.'
;';

Example in upgrade, install of Piwigo:

$query = '
ALTER TABLE '.CATEGORIES_TABLE.'
  DROP COLUMN date_last,
  DROP COLUMN nb_images
  ';

Response:

plg wrote:

rub wrote:

Props:
1) with new constants INTERNAL_<NAME>_TABLE, EXTERNAL_<NAME>_TABLE, USED_<NAME>_TABLE

can you give an example of resulting SQL query? I don't see the point :-/

  // NO! if (!defined('INTERNAL_CATEGORIES_TABLE'))
  define('INTERNAL_CATEGORIES_TABLE', $prefixeTable.'categories');

...

  // Define by plugin, etc.. Empty by default
  define('EXTERNAL_CATEGORIES_TABLE', ??);

...

if (defined('EXTERNAL_CATEGORIES_TABLE'))
  define('USED_CATEGORIES_TABLE', EXTERNAL_CATEGORIES_TABLE);
else
  define('USED_CATEGORIES_TABLE', INTERNAL_CATEGORIES_TABLE);

...

Queries in regular used of Piwigo become

$query = '
SELECT id, name, uppercats, global_rank
  FROM '.USED_CATEGORIES_TABLE.'
;';

=> Read internal or external

Queries in upgrade, install of Piwigo become

$query = '
ALTER TABLE '.INTERNAL_CATEGORIES_TABLE.'
  DROP COLUMN date_last,
  DROP COLUMN nb_images
  ';

=> upgrade only tables maintains by Piwigo

plg wrote:

rub wrote:

2) or perhaps with an other method? With substitution!
In $conf, you defined a map of external tables and fields and when the query is executed the SQL text is rewritten.
Example: select #_field_1 from #_config
#_* will be replaced.
Constant are filled by "#_" only for plugins compatibility.

=> with this method (and added of plugin events), it's easy to have Piwigo on multi-database, etc.

That is an interesting solution. I wonder if it doesn't make things too complex. On tablenames, I think it would be "understandable", but on column names, I have a doubt.

Two king substitution is possible:
  o regular substitution
  o upgrade substitution
It's necessary to have this notion because for example, it's dangerous to alter a external table.
Generally, plugin will do only regular substitution.

With this way, external authentification must be done by a new plugin.
This plugin witch can used $conf.
This plugin does not substute on upgrade.

Queries in regular used of Piwigo become

$query = '
SELECT #_categories_id, #_categories_name, #_categories_uppercats, #_categories_global_rank
  FROM #_CATEGORIES
;';

=> #_??? is replaced.
Substitution could be overrided by plugin.

Queries in upgrade, install of Piwigo become

$query = '
ALTER TABLE #_CATEGORIES
  DROP COLUMN #_categories_date_last,
  DROP COLUMN #_categories_nb_images
  ';

Substitution could be overrided by plugin but generally this subtitution is desabled.

nicolas wrote:

I didn't want theses reactions. My only problem is the strange message in include/config_default.inc.php (around line 500) :

Code:

// Other tables can be changed, if you define associated constants
// Example:
//   define('USER_INFOS_TABLE', 'pwg_main'.'user_infos');

It's a message in order to help develloper not realy like method for config file.
But, you noted with that a part of code that can be improved.

nicolas wrote:

My problem : I don't know how to migrate from old conf to new one ? Any idea ? [...] Must we use a install/db/NN-database.php file to make the migration ?

Yes. I t was designed for database migration tasks but we use it for any migration task (if I could rename it today, I would rather write install/migration/NN-task.php).

nicolas wrote:

The migration process will rename include/cmysql.inc.php to include/config_database.inc.php and add $conf['dblayer'] = 'mysql'; to that new file.

And also rename the parameters $cfgXXX to the equivalent $conf['db_name'], $conf['db_user'], $conf['db_password'], see [Forum, post 110078 by plg in topic 15500] Database improvements

I just made my last commit about calendar functions. Is was a bit difficult but I hope I didn't introduce regression.

I think the migration script for include/mysql.inc.php to include/config_database.inc.php is not so simple.
After [Subversion] r4280, the file include/common.inc.php try to load the new config file. But that new file doesn't exists yet. You must upgrade before it exists. But you are not redirect to upgrade.php but install.php. :-(

I didn't find a smarty and beautiful way to do that. Before migration if we could redirect to upgrade.php it will be better but not to the trick because that script work with database !

If anyone have a good idea, I will apreciate.

I also have to prepare upgrade_2.1.0.php to make migration from 2.0.N to 2.1.0 but it simple I think.

rub wrote:

nicolas wrote:

If anyone have a good idea, I will apreciate.

In order to not pertubate the user.

  index.php:  redirect on install.php if config_database.inc.php dont' exist
  install.php:
    if mysql.inc.php exists
      config_database.inc.php is created with  mysql.inc.php data
      mysql.inc.php is renamed or deleted
      redirect to index.php
  else
    classic install

After that upgrade_feed or upgrade can be apply.

This solution prevents the utiilsateur do again  the installation by mistake.

I think also it's the better solution but the redirection is not in index.php but common.inc.php. Let's go !

Last edited by nicolas (2009-12-02 20:24:50)

I think it's ok now. If anyone want to test and give feedback it can be a good idea to generate a nightly build.

Things to test :
- postgresql installation
- upgrade (configuration file from mysql.inc.php to config_database.inc.php)

Thanks for your message. I'm not sure to really understand what you really want to say. I have to admit that give piwigo alternate database engines was a technical challenge as you say. But the idea was also to improve sql code. And the other idea was to have the ability to use sqlite !

I know that things can have be done quickly and in a more robust manner with database abstraction tools like pdo but it was too difficult to implement with our core code.