diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml index 1e705af..0d55bbd 100644 --- a/.gitlab-ci.yml +++ b/.gitlab-ci.yml @@ -78,7 +78,7 @@ carton: expire_in: 1 week dependencies: [] script: - - carton install --deployment --without=sqlite --without=postgresql + - carton install --deployment --without=sqlite --without=postgresql --without=mysql when: always retry: 2 @@ -88,15 +88,15 @@ carton: carton_sqlite: <<: *carton_bdd_definition script: - - carton install --deployment --without=postgresql + - carton install --deployment --without=postgresql --without=mysql carton_postgresql: <<: *carton_bdd_definition script: - - carton install --deployment --without=sqlite -#carton_mysql: -# <<: *carton_bdd_definition -# script: -# - carton install --deployment --without=sqlite --without=postgresql + - carton install --deployment --without=sqlite --without=mysql +carton_mysql: + <<: *carton_bdd_definition + script: + - carton install --deployment --without=sqlite --without=postgresql ### SQLite tests ## @@ -119,8 +119,8 @@ postgresql: ### MySQL tests ## # -#mysql: -# <<: *mysql_definition -# script: -# - MOJO_CONFIG=t/mysql.conf make test -# - MOJO_CONFIG=t/mysql.conf make cover +mysql: + <<: *mysql_definition + script: + - MOJO_CONFIG=t/mysql.conf make test + - MOJO_CONFIG=t/mysql.conf make cover diff --git a/CHANGELOG b/CHANGELOG index 8b12fc2..bbfc95b 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -15,6 +15,7 @@ Revision history for Lufi - Use a recurrent task to provision shorts - Add a command to migrate data from SQLite to an other database - Add a test suite + - MySQL support 0.02.2 2017-09-18 - Fix cron tasks bug diff --git a/cpanfile b/cpanfile index 22d3594..016e374 100644 --- a/cpanfile +++ b/cpanfile @@ -38,4 +38,9 @@ feature 'postgresql', 'PostgreSQL support' => sub { }; feature 'sqlite', 'SQLite support' => sub { requires 'Mojo::SQLite', '>= 3.000'; -} +}; +feature 'mysql', 'MySQL support' => sub { + requires 'DBD::mysql', '== 4.046'; + requires 'Mojo::mysql'; + requires 'Mojolicious::Plugin::PgURLHelper'; +}; diff --git a/cpanfile.snapshot b/cpanfile.snapshot index f10c591..c3ce858 100644 --- a/cpanfile.snapshot +++ b/cpanfile.snapshot @@ -148,6 +148,20 @@ DISTRIBUTIONS Test::More 0.47 Tie::Hash 0 perl 5.006 + DBD-mysql-4.046 + pathname: C/CA/CAPTTOFU/DBD-mysql-4.046.tar.gz + provides: + Bundle::DBD::mysql 4.046 + DBD::mysql 4.046 + DBD::mysql::GetInfo undef + DBD::mysql::db 4.046 + DBD::mysql::dr 4.046 + DBD::mysql::st 4.046 + requirements: + DBI 1.609 + Data::Dumper 0 + ExtUtils::MakeMaker 0 + perl 5.008001 DBI-1.641 pathname: T/TI/TIMB/DBI-1.641.tar.gz provides: @@ -323,6 +337,16 @@ DISTRIBUTIONS Data::Validate::Domain 0 Data::Validate::IP 0 ExtUtils::MakeMaker 0 + Devel-CheckLib-1.13 + pathname: M/MA/MATTN/Devel-CheckLib-1.13.tar.gz + provides: + Devel::CheckLib 1.13 + requirements: + Exporter 0 + ExtUtils::MakeMaker 0 + File::Spec 0 + File::Temp 0.16 + perl 5.00405 Devel-Cover-1.31 pathname: P/PJ/PJCJ/Devel-Cover-1.31.tar.gz provides: @@ -1018,6 +1042,23 @@ DISTRIBUTIONS URI::db 0.15 URI::file 4.21 perl 5.010001 + Mojo-mysql-1.07 + pathname: J/JH/JHTHORSEN/Mojo-mysql-1.07.tar.gz + provides: + Blog undef + Blog::Controller::Posts undef + Blog::Model::Posts undef + Mojo::mysql 1.07 + Mojo::mysql::Database undef + Mojo::mysql::Migrations undef + Mojo::mysql::PubSub undef + Mojo::mysql::Results undef + Mojo::mysql::Transaction undef + requirements: + DBD::mysql 4.042 + ExtUtils::MakeMaker 0 + Mojolicious 7.55 + SQL::Abstract 1.81 Mojolicious-8.04 pathname: S/SR/SRI/Mojolicious-8.04.tar.gz provides: diff --git a/lib/Lufi/DB/File.pm b/lib/Lufi/DB/File.pm index eababcd..e2b8b32 100644 --- a/lib/Lufi/DB/File.pm +++ b/lib/Lufi/DB/File.pm @@ -115,6 +115,9 @@ sub new { } elsif ($dbtype eq 'postgresql') { use Lufi::DB::File::Pg; $c = Lufi::DB::File::Pg->new(@_); + } elsif ($dbtype eq 'mysql') { + use Lufi::DB::File::Mysql; + $c = Lufi::DB::File::Mysql->new(@_); } } diff --git a/lib/Lufi/DB/File/Mysql.pm b/lib/Lufi/DB/File/Mysql.pm new file mode 100644 index 0000000..b4ad4c9 --- /dev/null +++ b/lib/Lufi/DB/File/Mysql.pm @@ -0,0 +1,13 @@ +# vim:set sw=4 ts=4 sts=4 ft=perl expandtab: +package Lufi::DB::File::Mysql; +use Mojo::Base 'Lufi::DB::File'; + +sub new { + my $c = shift; + + $c = $c->SUPER::new(@_); + + return $c; +} + +1; diff --git a/lib/Lufi/DB/Slice.pm b/lib/Lufi/DB/Slice.pm index 648eecb..932ac5e 100644 --- a/lib/Lufi/DB/Slice.pm +++ b/lib/Lufi/DB/Slice.pm @@ -67,6 +67,9 @@ sub new { } elsif ($dbtype eq 'postgresql') { use Lufi::DB::Slice::Pg; $c = Lufi::DB::Slice::Pg->new(@_); + } elsif ($dbtype eq 'mysql') { + use Lufi::DB::Slice::Mysql; + $c = Lufi::DB::Slice::Mysql->new(@_); } } diff --git a/lib/Lufi/DB/Slice/Mysql.pm b/lib/Lufi/DB/Slice/Mysql.pm new file mode 100644 index 0000000..cb55ed0 --- /dev/null +++ b/lib/Lufi/DB/Slice/Mysql.pm @@ -0,0 +1,13 @@ +# vim:set sw=4 ts=4 sts=4 ft=perl expandtab: +package Lufi::DB::Slice::Mysql; +use Mojo::Base 'Lufi::DB::Slice'; + +sub new { + my $c = shift; + + $c = $c->SUPER::new(@_); + + return $c; +} + +1; diff --git a/lib/Lufi/Plugin/Helpers.pm b/lib/Lufi/Plugin/Helpers.pm index 0599084..c72aa42 100644 --- a/lib/Lufi/Plugin/Helpers.pm +++ b/lib/Lufi/Plugin/Helpers.pm @@ -24,6 +24,17 @@ sub register { } else { $migrations->from_file('utilities/migrations/pg.sql')->migrate(2); } + } elsif ($app->config('dbtype') eq 'mysql') { + require Mojo::mysql; + $app->helper(dbi => \&_mysql); + + # Database migration + my $migrations = Mojo::mysql::Migrations->new(mysql => $app->dbi); + if ($app->mode eq 'development' && $ENV{LUFI_DEV}) { + $migrations->from_file('utilities/migrations/mysql.sql')->migrate(0)->migrate(1); + } else { + $migrations->from_file('utilities/migrations/mysql.sql')->migrate(1); + } } elsif ($app->config('dbtype') eq 'sqlite') { require Mojo::SQLite; $app->helper(dbi => \&_sqlite); @@ -61,10 +72,30 @@ sub register { sub _pg { my $c = shift; - state $pg = Mojo::Pg->new($c->app->pg_url($c->app->config('pgdb'))); + my $pgdb = $c->config('pgdb'); + my $port = (defined $pgdb->{port}) ? $pgdb->{port}: 5432; + my $addr = $c->pg_url({ + host => $pgdb->{host}, port => $port, database => $pgdb->{database}, user => $pgdb->{user}, pwd => $pgdb->{pwd} + }); + state $pg = Mojo::Pg->new($addr); + $pg->max_connections($pgdb->{max_connections}) if defined $pgdb->{max_connections}; return $pg; } +sub _mysql { + my $c = shift; + + my $mysqldb = $c->config('mysqldb'); + my $port = (defined $mysqldb->{port}) ? $mysqldb->{port}: 3306; + my $addr = $c->pg_url({ + host => $mysqldb->{host}, port => $port, database => $mysqldb->{database}, user => $mysqldb->{user}, pwd => $mysqldb->{pwd} + }); + $addr =~ s/postgresql/mysql/; + state $mysql = Mojo::mysql->new($addr); + $mysql->max_connections($mysqldb->{max_connections}) if defined $mysqldb->{max_connections}; + return $mysql; +} + sub _sqlite { my $c = shift; diff --git a/lufi.conf.template b/lufi.conf.template index 0136e1b..91f0eb3 100644 --- a/lufi.conf.template +++ b/lufi.conf.template @@ -114,7 +114,7 @@ #mail_sender => 'no-reply@lufi.io', # choose what database you want to use - # valid choices are sqlite and postgresql (all lowercase) + # valid choices are sqlite, postgresql and mysql (all lowercase) # optional, default is sqlite #dbtype => 'sqlite', @@ -131,8 +131,28 @@ #pgdb => { # database => 'lufi', # host => 'localhost', - # #user => 'DBUSER', - # #pwd => 'DBPASSWORD' + # # optional, default is 5432 + # #port => 5432, + # user => 'DBUSER', + # pwd => 'DBPASSWORD', + # # https://mojolicious.org/perldoc/Mojo/Pg#max_connections + # # optional, default is 1 + # #max_connections => 1, + #}, + + # MySQL ONLY - only used if dbtype is set to mysql + # these are the credentials to access the MySQL database + # mandatory if you choosed mysql as dbtype + #mysqldb => { + # database => 'lufi', + # host => 'localhost', + # # optional, default is 3306 + # #port => 3306, + # user => 'DBUSER', + # pwd => 'DBPASSWORD', + # # https://metacpan.org/pod/Mojo::mysql#max_connections + # # optional, default is 5 (set to 0 to disable persistent connections) + # #max_connections => 5, #}, # define a path to the upload directory, where the uploaded files will be stored diff --git a/t/mysql.conf b/t/mysql.conf new file mode 100644 index 0000000..4b6197c --- /dev/null +++ b/t/mysql.conf @@ -0,0 +1,251 @@ +# vim:set sw=4 ts=4 sts=4 ft=perl expandtab: +{ + #################### + # Hypnotoad settings + #################### + # see http://mojolicio.us/perldoc/Mojo/Server/Hypnotoad for a full list of settings + hypnotoad => { + # array of IP addresses and ports you want to listen to + listen => ['http://127.0.0.1:8081'], + # if you use Lufi behind a reverse proxy like Nginx, you want to set proxy to 1 + # if you use Lufi directly, let it commented + #proxy => 1, + + # Please read http://mojolicious.org/perldoc/Mojo/Server/Hypnotoad#workers + # to adjust this to your server + workers => 30, + clients => 1, + }, + + # put a way to contact you here and uncomment it + # you can put some HTML in it + # MANDATORY + contact => 'Contact page', + + # put an URL or an email address to receive file reports and uncomment it + # it's for make reporting illegal files easy for users + # MANDATORY + report => 'report@example.com', + + # array of random strings used to encrypt cookies + # optional, default is ['fdjsofjoihrei'], PLEASE, CHANGE IT + #secrets => ['fdjsofjoihrei'], + + # choose a theme. See the available themes in `themes` directory + # optional, default is 'default' + #theme => 'default', + + # length of the random URL + # optional, default is 8 + #length => 8, + + # how many URLs will be provisioned in a batch ? + # optional, default is 5 + #provis_step => 5, + + # max number of URLs to be provisioned + # optional, default is 100 + #provisioning => 100, + + # length of the modify/delete token + # optional, default is 32 + #token_length => 32, + + # max file size, in octets + # you can write it 100*1024*1024 + # optional, no default + #max_file_size => 104857600, + + # if you want to have piwik statistics, provide a piwik image tracker + # only the image tracker is allowed, no javascript + # optional, no default + #piwik_img => 'https://piwik.example.org/piwik.php?idsite=1&rec=1', + + # broadcast_message which will displayed on the index page + # optional, no default + #broadcast_message => 'Maintenance', + + # default time limit for files + # valid values are 0, 1, 7, 30 and 365 + # optional, default is 0 (no limit) + #default_delay => 0, + + # number of days after which the images will be deleted, even if they were uploaded with "no delay" (or value superior to max_delay) + # a warning message will be displayed on homepage + # optional, default is 0 (no limit) + #max_delay => 0, + + # size thresholds: if you want to define max delays for different sizes of file + # the keys are size in Bytes, you can't have 10*1000*10000 as key + # if a file is smaller than the smallest configured size, it will have a expiration delay of max_delay (see above) + # optional, default is using max_delay (see above) for all sizes + #delay_for_size => { + # 10000000 => 90, # between 10MB and 50MB => max is 90 days, less than 10MB => max is max_delay (see above) + # 50000000 => 60, # between 50MB ans 1GB => max is 60 days + # 1000000000 => 2, # more than 1GB => max is 2 days + #}, + + # URL sub-directory in which you want Lufi to be accessible + # example: you want to have Lufi under https://example.org/lufi/ + # => set prefix to '/lufi' or to '/lufi/', it doesn't matter + # optional, defaut is / + #prefix => '/', + + # array of authorized domains for API calls. + # if you want to authorize everyone to use the API: ['*'] + # optional, no domains allowed by default + #allowed_domains => ['http://1.example.com', 'http://2.example.com'], + + # if set, the shortened URLs will use this domain + # optional + #fixed_domain => 'example.org', + + # Mail configuration + # See https://metacpan.org/pod/Mojolicious::Plugin::Mail#EXAMPLES + # Optional, default to sendmail method with no arguments + #mail => { + # # Valid values are 'sendmail' and 'smtp' + # how => 'smtp', + # howargs => ['smtp.example.org'] + #}, + + # Email sender address + # Optional, default to no-reply@lufi.io + #mail_sender => 'no-reply@lufi.io', + + # choose what database you want to use + # valid choices are sqlite, postgresql and mysql (all lowercase) + # optional, default is sqlite + dbtype => 'mysql', + + # SQLite ONLY - only used if dbtype is set to sqlite + # define a path to the SQLite database + # you can define it relative to lufi directory or set an absolute path + # remember that it has to be in a directory writable by Lufi user + # optional, default is lufi.db + #db_path => 'lufi.db', + + # PostgreSQL ONLY - only used if dbtype is set to postgresql + # these are the credentials to access the PostgreSQL database + # mandatory if you choosed postgresql as dbtype + #pgdb => { + # database => 'lufi', + # host => 'localhost', + # # optional, default is 5432 + # #port => 5432, + # user => 'DBUSER', + # pwd => 'DBPASSWORD', + # # optional, default is 1 + # #max_connections => 1, + #}, + + # MySQL ONLY - only used if dbtype is set to mysql + # these are the credentials to access the MySQL database + # mandatory if you choosed mysql as dbtype + mysqldb => { + database => 'lufi_db', + host => 'mariadb', + # optional, default is 3306 + #port => 3306, + user => 'lufi', + pwd => 'lufi_pwd', + # # optional, default is 5 (set to 0 to disable persistent connections) + # #max_connections => 5, + }, + + # define a path to the upload directory, where the uploaded files will be stored + # you can define it relative to lufi directory or set an absolute path + # remember that it has to be in a directory writable by Lufi user + # DO NOT CHANGE THIS IF FILES HAVE BEEN ALREADY UPLOADED: THEY WILL NOT BE DOWNLOADABLE ANYMORE + # optional, default is 'files' + #upload_dir => 'files', + + # set `ldap` if you want that only authenticated users can upload files + # please note that everybody can still download files + # optional, no default + #ldap => { uri => 'ldap://rroemhild-test-openldap', user_tree => 'ou=people,dc=planetexpress,dc=com', bind_dn => 'cn=admin,dc=planetexpress,dc=com', bind_pwd => 'GoodNewsEveryone', user_attr => 'uid', user_filter => '' }, + + # set `htpasswd` if you want to use an htpasswd file instead of ldap + # see 'man htpasswd' to know how to create such file + #htpasswd => 't/lstu.passwd', + + # if you've set ldap above, the session will last `session_duration` seconds before + # the user needs to reauthenticate + # optional, default is 3600 + #session_duration => 3600, + + # allow to add a password on files, asked before allowing to download files + # optional, default is 0 + allow_pwd_on_files => 1, + + # force all files to be in "Burn after reading mode" + # optional, default is 0 + #force_burn_after_reading => 0, + + # if set, the files' URLs will always use this domain + # optional, no default + #fixed_domain => 'example.org', + + # abuse reasons + # set an integer in the abuse field of a file in the database and it will not be downloadable anymore + # the reason will be displayed to the downloader, according to the reasons you will configure here. + # optional, no default + abuse => { + 0 => 'Copyright infringment', + 1 => 'Illegal content', + }, + + # Content-Security-Policy header that will be sent by Lufi + # Set to '' to disable CSP header + # https://content-security-policy.com/ provides a good documentation about CSP. + # https://report-uri.com/home/generate provides a tool to generate a CSP header. + # optional, default is "base-uri 'self'; connect-src 'self'; default-src 'none'; font-src 'self'; form-action 'self'; frame-ancestors 'none'; img-src 'self' blob:; media-src blob:; script-src 'self' 'unsafe-inline' 'unsafe-eval'; style-src 'self' 'unsafe-inline'" + # the default value is good for `default` and `milligram` themes + #csp => "base-uri 'self'; connect-src 'self'; default-src 'none'; font-src 'self'; form-action 'self'; frame-ancestors 'none'; img-src 'self' blob:; media-src blob:; script-src 'self' 'unsafe-inline' 'unsafe-eval'; style-src 'self' 'unsafe-inline'" + + # X-Frame-Options header that will be sent by Lufi + # Valid values are: 'DENY', 'SAMEORIGIN', 'ALLOW-FROM https://example.com/' + # Set to '' to disable X-Frame-Options header + # See https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/X-Frame-Options + # Please note that this will add a "frame-ancestors" directive to the CSP header (see above) accordingly + # to the chosen setting (See https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Security-Policy/frame-ancestors) + # optional, default is 'DENY' + #x_frame_options => 'DENY', + + # X-Content-Type-Options that will be sent by Lufi + # See https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/X-Content-Type-Options + # Set to '' to disable X-Content-Type-Options header + # optional, default is 'nosniff' + #x_content_type_options => 'nosniff', + + # X-XSS-Protection that will be sent by Lufi + # See https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/X-XSS-Protection + # Set to '' to disable X-XSS-Protection header + # optional, default is '1; mode=block' + #x_xss_protection => '1; mode=block', + + ######################### + # Lufi cron jobs settings + ######################### + + # number of days senders' IP addresses are kept in database + # after that delay, they will be deleted from database (used with script/lufi cron cleanbdd) + # optional, default is 365 + #keep_ip_during => 365, + + # max size of the files directory, in octets + # used by script/lufi cron watch to trigger an action + # optional, no default + #max_total_size => 10*1024*1024*1024, + + # default action when files directory is over max_total_size (used with script/lufi cron watch) + # valid values are 'warn', 'stop-upload' and 'delete' + # please, see readme + # optional, default is 'warn' + #policy_when_full => 'warn', + + # images which are not viewed since delete_no_longer_viewed_files days will be deleted by the cron cleanfiles task + # if delete_no_longer_viewed_files is not set, the no longer viewed files will NOT be deleted + # optional, no default + #delete_no_longer_viewed_files => 90, +}; diff --git a/t/postgresql.conf b/t/postgresql.conf index 282333b..5306c8d 100644 --- a/t/postgresql.conf +++ b/t/postgresql.conf @@ -114,7 +114,7 @@ #mail_sender => 'no-reply@lufi.io', # choose what database you want to use - # valid choices are sqlite and postgresql (all lowercase) + # valid choices are sqlite, postgresql and mysql (all lowercase) # optional, default is sqlite dbtype => 'postgresql', @@ -131,10 +131,28 @@ pgdb => { database => 'lufi_db', host => 'postgres', + # optional, default is 5432 + #port => 5432, user => 'lufi', pwd => 'lufi_pwd' + # # optional, default is 1 + # #max_connections => 1, }, + # MySQL ONLY - only used if dbtype is set to mysql + # these are the credentials to access the MySQL database + # mandatory if you choosed mysql as dbtype + #mysqldb => { + # database => 'lufi', + # host => 'localhost', + # # optional, default is 3306 + # #port => 3306, + # user => 'DBUSER', + # pwd => 'DBPASSWORD', + # # optional, default is 5 (set to 0 to disable persistent connections) + # #max_connections => 5, + #}, + # define a path to the upload directory, where the uploaded files will be stored # you can define it relative to lufi directory or set an absolute path # remember that it has to be in a directory writable by Lufi user diff --git a/t/sqlite.conf b/t/sqlite.conf index 88d8787..03ee083 100644 --- a/t/sqlite.conf +++ b/t/sqlite.conf @@ -114,7 +114,7 @@ #mail_sender => 'no-reply@lufi.io', # choose what database you want to use - # valid choices are sqlite and postgresql (all lowercase) + # valid choices are sqlite, postgresql and mysql (all lowercase) # optional, default is sqlite #dbtype => 'sqlite', @@ -131,8 +131,28 @@ #pgdb => { # database => 'lufi', # host => 'localhost', - # #user => 'DBUSER', - # #pwd => 'DBPASSWORD' + # # optional, default is 5432 + # #port => 5432, + # user => 'DBUSER', + # pwd => 'DBPASSWORD', + # # https://mojolicious.org/perldoc/Mojo/Pg#max_connections + # # optional, default is 1 + # #max_connections => 1, + #}, + + # MySQL ONLY - only used if dbtype is set to mysql + # these are the credentials to access the MySQL database + # mandatory if you choosed mysql as dbtype + #mysqldb => { + # database => 'lufi', + # host => 'localhost', + # # optional, default is 3306 + # #port => 3306, + # user => 'DBUSER', + # pwd => 'DBPASSWORD', + # # https://metacpan.org/pod/Mojo::mysql#max_connections + # # optional, default is 5 (set to 0 to disable persistent connections) + # #max_connections => 5, #}, # define a path to the upload directory, where the uploaded files will be stored diff --git a/t/test.t b/t/test.t index b0d422e..a66ef1e 100644 --- a/t/test.t +++ b/t/test.t @@ -111,7 +111,7 @@ sub test_upload_file { ->message_like(qr@"created_at":\d+@) ->message_like(qr@"del_at_first_view":false@) ->message_like(qr@"delay":"0"@) - ->message_like(qr@"duration":0@) + ->message_like(qr@"duration":\d+@) ->message_like(qr@"i":0@) ->message_like(qr@"j":0@) ->message_like(qr@"name":"foobar\.txt"@) diff --git a/utilities/migrations/mysql.sql b/utilities/migrations/mysql.sql new file mode 100644 index 0000000..85b5eb4 --- /dev/null +++ b/utilities/migrations/mysql.sql @@ -0,0 +1,29 @@ +-- 1 up +CREATE TABLE IF NOT EXISTS files ( + short varchar(255) PRIMARY KEY, + deleted boolean default false, + mediatype varchar(255), + filename varchar(255), + filesize integer, + counter integer default 0, + delete_at_first_view boolean, + delete_at_day integer, + created_at integer, + created_by varchar(255), + last_access_at integer, + mod_token varchar(255), + nbslices integer, + complete boolean default false, + passwd varchar(255), + abuse integer +); + +CREATE TABLE IF NOT EXISTS slices ( + short varchar(255) NOT NULL REFERENCES files(short) ON DELETE CASCADE, + j integer NOT NULL, + path varchar(255) unique NOT NULL, + constraint slice_short_j UNIQUE (short, j) +); +-- 1 down +DROP TABLE slices; +DROP TABLE files;