aboutsummaryrefslogtreecommitdiffstats
path: root/posts
diff options
context:
space:
mode:
authorFran├žois Kooman <fkooman@tuxed.net>2018-06-19 08:41:48 +0200
committerFran├žois Kooman <fkooman@tuxed.net>2018-06-19 08:41:48 +0200
commit11b6d9c01a65cfdfac8629bcd8a65ab6c7b11893 (patch)
treec427c5f4c38d949c651687cc471b46fbe9e15c35 /posts
parentb765b2ed62071807af1f9b5d90862a52074e77e4 (diff)
downloadwww.tuxed.net-11b6d9c01a65cfdfac8629bcd8a65ab6c7b11893.zip
www.tuxed.net-11b6d9c01a65cfdfac8629bcd8a65ab6c7b11893.tar.gz
www.tuxed.net-11b6d9c01a65cfdfac8629bcd8a65ab6c7b11893.tar.xz
add php database migrations post, update some older posts, about
Diffstat (limited to 'posts')
-rw-r--r--posts/git_sign.md5
-rw-r--r--posts/php_database_migrations.md71
2 files changed, 76 insertions, 0 deletions
diff --git a/posts/git_sign.md b/posts/git_sign.md
index 0a164d7..907eec5 100644
--- a/posts/git_sign.md
+++ b/posts/git_sign.md
@@ -1,6 +1,7 @@
---
title: Signing Git Commits
published: 2018-05-11
+modified: 2018-06-18
---
What a mess! This is my `$HOME/.gitconfig` that seems to make everything work.
@@ -16,4 +17,8 @@ As my keyring only works with `gpg2` and not with `gpg` you also have to set
gpgSign = true
[gpg]
program = /usr/bin/gpg2
+[tar "tar.xz"]
+ command = xz -c
```
+
+**UPDATE** (2018-06-18): add the `tar "tar.xz"` section.
diff --git a/posts/php_database_migrations.md b/posts/php_database_migrations.md
new file mode 100644
index 0000000..b64db01
--- /dev/null
+++ b/posts/php_database_migrations.md
@@ -0,0 +1,71 @@
+---
+title: PHP Database Migrations
+published: 2018-06-19
+---
+
+While working on [Let's Connect!](https://letsconnect-vpn.org/) /
+[eduVPN](https://eduvpn.org/) I reached the point of needing to perform a
+database migration, i.e. modify the database schema. I wanted to add a column
+to a table.
+
+Software exists to do this, e.g. [Phinx](https://phinx.org/) that seems to be
+a popular choice. It supports many databases and makes managing migrations
+easy!
+
+One feature was not explicitly mentioned there which I really wanted to have:
+the ability to migrate the database without involving the system administrator.
+I don't want to require them to run a migration script before things start
+working again. Ideally the migration is immediately done when needed at
+"run time". The call to trigger the migration can be added to your `index.php`
+before you start using the database. It should be as simple as that.
+
+Of course, this requires a design that optimizes specifically for this flow:
+
+* use the least amount of code and SQL queries possible as to not slow down
+ the "normal" flow, i.e. when no migration is needed;
+* make sure migrations either complete fully, or not at all (use transactions);
+* make sure only one request can trigger the database migrations and make the
+ other requests either wait or return "Internal Server Error" during the
+ migration.
+
+As currently only [SQLite](https://sqlite.org/) is supported, this makes it
+both easier, because one only has to deal with SQLite, and harder to support
+migrations as SQLite does [not](https://www.sqlite.org/lang_altertable.html)
+support many different `ALTER TABLE` commands that are common with other
+relational databases.
+
+Most libraries work around this by creating some higher level language to
+support all databases with one code base or migration "language". This makes it
+easier to change databases when needed. Supporting more databases also requires
+more QA, so for now I decided to only support SQLite and keep doing this as
+long as possible...
+
+With SQLite most migrations will require creating a new table, copy the old
+data in and remove the old table. As an example:
+
+ ALTER TABLE foo RENAME TO _foo
+ CREATE TABLE foo (a INTEGER NOT NULL, b INTEGER DEFAULT 0)
+ INSERT INTO foo (a) SELECT a FROM _foo
+ DROP TABLE _foo
+
+This example renames the existing table `foo` to `_foo`, creates a new table
+`foo` with the additional column `b` and copies the data from `_foo` back to
+`foo` and eventually deletes the `_foo` table when the copying is done. Just
+to be complete: adding a column is one of the `ALTER TABLE` commands that *is*
+actually supported, but just to show an example that works with all kinds of
+migrations.
+
+One would think this is very slow, but some simple tests doing this with a
+table that contains around 100.000 rows is barely noticeable.
+
+To make integrating this in applications easier, I created
+[php-sqlite-migrate](https://git.tuxed.net/fkooman/php-sqlite-migrate/about/).
+This library is easy to use and contains a lot of documentation to get you
+started.
+
+The API is not yet stable, I'm thinking of adding support for a "schema"
+directory that contains the database schema and migrations files, that will
+make it cleaner to add to your code as you don't need to embed the SQL
+queries in the source code.
+
+Let [me](../about.html) know what you think!