From 424eea4f630d25158ec033a004666ecd0107f8b8 Mon Sep 17 00:00:00 2001 From: akpu1 Date: Wed, 22 Jun 2016 15:58:15 +1000 Subject: Added instructions and schema to use or migrate to SQLite (#17) * Added MySQL to SQLite migration instructions; SQLite setup instructions * Renamed schema.sql to mysql_schema.sql * Produced modified schema suitable for SQLite * Added SQLite setup and migration instructions --- README.md | 55 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ mysql_schema.sql | 27 +++++++++++++++++++++++++++ schema.sql | 27 --------------------------- sqlite_schema.sql | 16 ++++++++++++++++ 4 files changed, 98 insertions(+), 27 deletions(-) create mode 100644 mysql_schema.sql delete mode 100644 schema.sql create mode 100644 sqlite_schema.sql diff --git a/README.md b/README.md index 6e43462..ec2101b 100644 --- a/README.md +++ b/README.md @@ -69,6 +69,29 @@ option for nginx webserver is `client_max_body_size`. Example nginx configs can be found in confs/. +## Using SQLite as DB engine + +We need to create the SQLite database before it may be used by pomf. +Fortunately, this is incredibly simple. + +First create a directory for the database, e.g. `mkdir /var/db/pomf`. +Then, create a new SQLite database from the schema, e.g. `sqlite3 /var/db/pomf/pomf.sq3 -init /home/pomf/sqlite_schema.sql`. +Then, finally, ensure the permissions are correct, e.g. +```bash +chown nginx:nginx /var/db/pomf +chmod 0750 /var/db/pomf +chmod 0640 /var/db/pomf/pomf.sq3 +``` + +Finally, edit `php/includes/settings.inc.php` to indicate this is the database engine you would like to use. Make the changes outlined below +```php +define('POMF_DB_CONN', '[stuff]'); ---> define('POMF_DB_CONN', 'sqlite:/var/db/pomf/pomf.sq3');` +define('POMF_DB_USER', '[stuff]'); ---> define('POMF_DB_USER', null); +define('POMF_DB_PASS', '[stuff]'); ---> define('POMF_DB_PASS', null); +``` + +*NOTE: The directory where the SQLite database is stored, must be writable by the web server user* + ### Apache If you are running Apache and want to compress your output when serving files, @@ -79,6 +102,38 @@ add to your `.htaccess` file: Remember to enable `deflate_module` and `filter_module` modules in your Apache configuration file. +### Migrating from MySQL to SQLite + +MySQL is relatively complicated to administer, brings in many unneeded dependencies, and consumes +more resources than SQLite would, and result in worse performance for pomf. Additonally, poorly configured installations have the potential +to pose a security risk. + +Fortunately, it is incredibly simple to migrate your database. This may be done on a live server, and should require +zero downtime. + +_If doing this on a live server, you way wish to work in a subdirectory (or vhost, or equivelant), so that any complications or mistakes do not affect your main site. +If you choose not to do so, know that mistakes in the changes outlined below, will only temporarily impact **uploading**, causing **Server error** to be displayed. None of these steps are destructive, and are easily reverted._ + +Run the following commands as root, to dump your database, and make a SQLite database with the contents. +```bash +mkdir /var/db/pomf +wget -O /tmp/m2s https://github.com/dumblob/mysql2sqlite/raw/master/mysql2sqlite.sh +mysqldump -u OLD_DB_USER -p OLD_DB_PASS pomf | sh /tmp/m2s | sqlite3 /var/db/pomf/sq3 +rm /tmp/m2s +chown -R nginx:nginx /var/db/pomf #replace user as appropriate +chmod 0750 /var/db/pomf && chmod 0640 /var/db/pomf/sq3 +``` +Edit the file `php/includes/settings.inc.php`, in the subdirectory you just made, making the changes outlined below. +```php +define('POMF_DB_CONN', '[stuff]'); ---> define('POMF_DB_CONN', 'sqlite:/var/db/pomf/pomf.sq3');` +define('POMF_DB_USER', '[stuff]'); ---> define('POMF_DB_USER', null); +define('POMF_DB_PASS', '[stuff]'); ---> define('POMF_DB_PASS', null); +``` + +Then, run `make` to rebuild the website pages, and copy the new `settings.inc.php` file into place. + +All done! You may disable or uninstall MySQL if you wish. + ## Getting help The Pomf community gathers on IRC. You can also email the maintainer for help. diff --git a/mysql_schema.sql b/mysql_schema.sql new file mode 100644 index 0000000..154f095 --- /dev/null +++ b/mysql_schema.sql @@ -0,0 +1,27 @@ +-- MySQL dump 10.13 Distrib 5.6.21-70.1, for debian-linux-gnu (x86_64) +-- +-- Host: localhost Database: pomf + +-- +-- Table structure for table `files` +-- + +CREATE TABLE `files` ( + `id` int(10) unsigned NOT NULL auto_increment, + `hash` char(40) default NULL, + `originalname` varchar(255) default NULL, + `filename` varchar(30) default NULL, + `size` int(10) unsigned default NULL, + `date` date default NULL, + `expire` date default NULL, -- Deprecated since version 2.1.0 + `delid` char(40) default NULL, + `user` int(10) unsigned default '0', + `dir` int(2) default '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB; +-- `filename` length (30) may be a bit excessive, since the average length would be smaller +-- [6 for the name + the file extension length], but since there is no limit for the file +-- extension length (at the moment, at least) let's keep it high in order to avoid problems + + +-- Dump completed on 2014-12-07 19:30:13 diff --git a/schema.sql b/schema.sql deleted file mode 100644 index 154f095..0000000 --- a/schema.sql +++ /dev/null @@ -1,27 +0,0 @@ --- MySQL dump 10.13 Distrib 5.6.21-70.1, for debian-linux-gnu (x86_64) --- --- Host: localhost Database: pomf - --- --- Table structure for table `files` --- - -CREATE TABLE `files` ( - `id` int(10) unsigned NOT NULL auto_increment, - `hash` char(40) default NULL, - `originalname` varchar(255) default NULL, - `filename` varchar(30) default NULL, - `size` int(10) unsigned default NULL, - `date` date default NULL, - `expire` date default NULL, -- Deprecated since version 2.1.0 - `delid` char(40) default NULL, - `user` int(10) unsigned default '0', - `dir` int(2) default '0', - PRIMARY KEY (`id`) -) ENGINE=InnoDB; --- `filename` length (30) may be a bit excessive, since the average length would be smaller --- [6 for the name + the file extension length], but since there is no limit for the file --- extension length (at the moment, at least) let's keep it high in order to avoid problems - - --- Dump completed on 2014-12-07 19:30:13 diff --git a/sqlite_schema.sql b/sqlite_schema.sql new file mode 100644 index 0000000..414719c --- /dev/null +++ b/sqlite_schema.sql @@ -0,0 +1,16 @@ +PRAGMA synchronous = OFF; +PRAGMA journal_mode = MEMORY; +BEGIN TRANSACTION; +CREATE TABLE `files` ( + `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT +, `hash` char(40) default NULL +, `originalname` varchar(255) default NULL +, `filename` varchar(30) default NULL +, `size` integer default NULL +, `date` date default NULL +, `expire` date default NULL +, `delid` char(40) default NULL +, `user` integer default '0' +, `dir` integer default '0' +); +END TRANSACTION; -- cgit v1.2.3