Piwigo.org

rub · 2008-06-22 09:41:15

z0rglub wrote:
VDigital wrote:
Will you provide the migration tool/phase/task/code? Without it could be a key blocker!
I will provide something, but as the system will change, it won't be possible to make something equally functionnal. The only solution to avoid any problem (ie a private photo publicly displayed) a solution could be to add a "privacy rule" where by default all photos have the highest privacy level.

Backup of old permissions will be necessary in order to provide plugins witch can do complicated migration.

plg · 2008-07-23 23:34:59

I've performed a benchmark to know if "where level <= 4" was faster or slower than "where level in (0,1,2,4)". I have filled the images table with 100k records (using Talend Open Studio, a tRowGenerator and tMysqlOutput), as the data are random, I have 20k lines for each privacy level.

Code:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Time::HiRes qw(gettimeofday tv_interval);
use List::Util qw(sum);

my $sth;
my $query;

my $dbh = DBI->connect(
    sprintf(
        'DBI:mysql:database=%s;host=%s;port=%s',
        'talend',
        'localhost',
        '3306',
    ),
    'root',
    'conway'
)
    or die "can't connect to database";

my @queries = (
    'select %rand%, 1',
    'select %rand%, count(*) from images where level in (0)',
    'select %rand%, count(*) from images where level <= 0',
    'select %rand%, count(*) from images where level in (0,1)',
    'select %rand%, count(*) from images where level <= 1',
    'select %rand%, count(*) from images where level in (0,1,2)',
    'select %rand%, count(*) from images where level <= 2',
    'select %rand%, count(*) from images where level in (0,1,2,4)',
    'select %rand%, count(*) from images where level <= 4',
    'select %rand%, count(*) from images where level in (0,1,2,4,8)',
    'select %rand%, count(*) from images where level <= 8',
);

foreach my $query (@queries) {
    my @times = ();
    for (1..10) {
        my $rand = int rand 10_000;
        my $query_local = $query;
        $query_local =~ s{%rand%}{$rand};

        my $start = [gettimeofday];
        $dbh->do($query_local);
        my $stop = [gettimeofday];

        push @times, tv_interval($start, $stop);
    }

    printf(
        "time: %.3f seconds => %s\n",
        sum(@times) / scalar(@times),
        $query
    );
}

I use the %rand% trick to avoid MySQL query cache. The result is:

Code:

$ perl /home/pierrick/dev/piwigo/bench/queries.pl
time: 0.000 seconds => select %rand%, 1

time: 0.130 seconds => select %rand%, count(*) from images where level in (0)
time: 0.125 seconds => select %rand%, count(*) from images where level <= 0

time: 0.141 seconds => select %rand%, count(*) from images where level in (0,1)
time: 0.133 seconds => select %rand%, count(*) from images where level <= 1

time: 0.143 seconds => select %rand%, count(*) from images where level in (0,1,2)
time: 0.123 seconds => select %rand%, count(*) from images where level <= 2

time: 0.129 seconds => select %rand%, count(*) from images where level in (0,1,2,4)
time: 0.122 seconds => select %rand%, count(*) from images where level <= 4

time: 0.125 seconds => select %rand%, count(*) from images where level in (0,1,2,4,8)
time: 0.121 seconds => select %rand%, count(*) from images where level <= 8

(lines breaks added manually) conclusion: same speed for both methods.

Now I had the obvious index:

Code:

mysql> create index images_level on images(level);
Query OK, 100000 rows affected (3.15 sec)

Code:

$ perl /home/pierrick/dev/piwigo/bench/queries.pl
time: 0.000 seconds => select %rand%, 1

time: 0.009 seconds => select %rand%, count(*) from images where level in (0)
time: 0.011 seconds => select %rand%, count(*) from images where level <= 0

time: 0.020 seconds => select %rand%, count(*) from images where level in (0,1)
time: 0.018 seconds => select %rand%, count(*) from images where level <= 1

time: 0.030 seconds => select %rand%, count(*) from images where level in (0,1,2)
time: 0.028 seconds => select %rand%, count(*) from images where level <= 2

time: 0.040 seconds => select %rand%, count(*) from images where level in (0,1,2,4)
time: 0.037 seconds => select %rand%, count(*) from images where level <= 4

time: 0.050 seconds => select %rand%, count(*) from images where level in (0,1,2,4,8)
time: 0.047 seconds => select %rand%, count(*) from images where level <= 8

Same speed for both. Index makes the query 3 to 10 times faster.

Conclusion : index for speed, "where level <= 4" for simplicity of coding.

VDigital · 2008-11-23 07:38:50

For me, offering only "image privacy level" is a real major regression.

- A website with 10% or 90% of private categories could be after migration with 100% of public images.
Migration is really complex with 5000 categories.

- If I have several group of friends "Image privacy level" does not provide a solution to authorize only some different pictures for each groups.

For me, "Image privacy level" is definitively not THE solution to manage authorizations.

rub · 2008-11-23 09:20:26

+1

plg · 2008-11-23 10:23:45

As you have seen, I have not implemented the removal of "private categories", because it would have taken too much time, and I thought we could work with both systems in the same time. But I still think that using categories to set privacy on photos is a bad thing, it's just a workaround because it was complicated to set permissions at image level.

Anyway, I'll come back to this topic once 2.0 is available, in 2.0 the 2 systems are working together.

Piwigo.org

Announcement

#16 2008-06-22 09:41:15

Re: permissions : "image privacy level" Versus "private category"

z0rglub wrote:

VDigital wrote:

#17 2008-07-23 23:34:59

Re: permissions : "image privacy level" Versus "private category"

Code:

Code:

Code:

Code:

#18 2008-11-23 07:38:50

Re: permissions : "image privacy level" Versus "private category"

#19 2008-11-23 09:20:26

Re: permissions : "image privacy level" Versus "private category"

#20 2008-11-23 10:23:45

Re: permissions : "image privacy level" Versus "private category"

Board footer