Announcement

  •  » Engine
  •  » Database improvements

#61 2009-11-24 23:07:22

VDigital
Former Piwigo Team
Paris (FR)
2005-05-04
17680

Re: Database improvements

VDigital wrote:

if (!defined('..._TABLE')) could be introduced.

What is your idea behind?

January_history, February_history, March...
video_users, picture_users, postcard_users, forum_users, ...

Think about Astat and history cleaning.

grum? and nicolas?
I see only inconvenients.


Piwigo.com: Start and run your own photo gallery. Signup and get 30 days to try for free, no commitment.
8-)

Offline

 

#62 2009-11-24 23:59:19

grum
Piwigo Team
Nantes
2007-09-10
1371

Re: Database improvements

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...


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

Offline

 

#63 2009-11-25 10:21:36

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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

Offline

 

#64 2009-11-25 14:27:04

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

Two kinds of queries must be defined!

Example in regular used of Piwigo:

Code:

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

Example in upgrade, install of Piwigo:

Code:

$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 :-/

Code:

  // 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

Code:

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

=> Read internal or external

Queries in upgrade, install of Piwigo become

Code:

$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

Code:

$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

Code:

$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.

Offline

 

#65 2009-11-29 20:39:27

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

With last commits ([Subversion] r4385[Subversion] r4387 and [Subversion] r4388) I started using a new parameter in include/config_database.inc.php :

Code:

$conf['dblayer'] = 'mysql';

That parameter will change when database engine will change. It allows include specifics functions depending on the database :

Code:

// line 95 from common.inc
include(PHPWG_ROOT_PATH .'include/dblayer/functions_'.$conf['dblayer'].'.inc.php');

My problem : I don't know how to migrate from old conf to new one ? Any idea ?
The migration process will rename include/cmysql.inc.php to include/config_database.inc.php and add $conf['dblayer'] = 'mysql'; to that new file. Must we use a install/db/NN-database.php file to make the migration ?

Offline

 

#66 2009-11-29 22:48:09

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

Re: Database improvements

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


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

Offline

 

#67 2009-11-30 10:50:23

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

plg wrote:

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

Ok I will write a migration script but with old/actual way (install/db/NN-database.php)

plg wrote:

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

Of course. Thanks for remember me.

Offline

 

#68 2009-11-30 22:17:02

nicolas
Former Piwigo Team
2004-12-30
1176

Re: 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.

Offline

 

#69 2009-12-02 15:04:57

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

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.

Offline

 

#70 2009-12-02 20:19:56

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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)

Offline

 

#71 2009-12-03 09:56:19

rub
Piwigo Team
Lille
2005-08-26
5019

Re: Database improvements

nicolas wrote:

...but the redirection is not in index.php but common.inc.php...

Of course... ;-)

Offline

 

#72 2009-12-04 20:01:14

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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)

Offline

 

#73 2010-02-08 16:29:30

jochen
Guest

Re: Database improvements

plg wrote:

First I would say that supporting PostgreSQL doesn't not seem that relevant for Piwigo, in my opinion. It's interesting to work with PostgreSQL (I've done it on other projects) and it's a really nice database BUT for Piwigo I see a big work in the code for only a technical challenge that is not useful for users (In 8 years, I think we've had 5 requests for supporting PostgreSQL, not more, that's a very low number).

That is because most people see this is yet another MySQL rather than SQL based project and won't even ask. I have PgSQL running on my server and won't have MySQL running next to it just for an album. I understand that MySQL may (or may have been, at least) faster than other DBs, but what is the point of having the SQL standard and all those nice cross DB apis' in PHP, Perl, Python, etc. if the user can't plug in any DB they require for various reasons. MySQL is common indeed, but so is MS Office. Why not use Access then?

You already have your codebase, so I understand. But this is something anyone starting any new project should keep in mind. Your product looks very good from what I've seen upon the first few clicks (and a nice front page design for your project!) but I'll never really know...

Thanks,

Jochen

 

#74 2010-02-08 23:19:03

nicolas
Former Piwigo Team
2004-12-30
1176

Re: Database improvements

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.

Offline

 

#75 2010-02-08 23:50:02

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

Re: Database improvements

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.


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

Offline

 
  •  » Engine
  •  » Database improvements

Board footer

Powered by FluxBB

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