aboutsummaryrefslogtreecommitdiffstats
path: root/README.md
blob: ffe962b550c91d75986cf91df30c5116f8d2bf75 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
**Summary**: Library for SQLite database schema migration

**Description**: Library written in PHP that assists with SQLite database 
schema migrations.

**License**: MIT

# Introduction

Library written in PHP that can assist with 
[SQLite](https://www.sqlite.org/index.html) database migrations.

# Why

Sometimes you need to perform database schema migrations "in the field" without
having the option to have an administrator manually perform the database 
migration after installing a software update.

Typically this is useful for software that is deployed on systems out of your
control through an OS package manager where you hook into the normal update 
process of the OS and want to have the ability to update the database schema.

This library is optimized for running during the "initialization" phase of your
`index.php`, so it can be executed on every request.

# Features

* Can be implemented for a currently deployed (web) application that did not 
  consider database scheme updates from the start;
* Ability run through multiple schema updates when e.g. software was not 
  regularly updated and multiple schema updates occurred between the installed 
  version and the latest version;
* No need to run through all schema updates on application install. The 
  application will always install the latest schema and start from there;
* Uses `PDO` database abstraction layer;
* Optimized for running during every (HTTP) request;
* Uses database transactions to run migrations, guaranteeing a migration either
  fully completed or is rolled back;
* Implement rudimentary "locking" to prevent the migration to run multiple 
  times when application is under load;
* No dependencies, only core PHP functionality;
* Less than 200 NCLOC;
* Supports PHP >= 5.4.

# Limitations

Migrating during the normal application flow may not be reasonable for tables
with millions of rows. Manually triggering the update during e.g. a maintenance 
window is also supported.

# Assumptions

We assume you have a SQLite database somewhere with some tables in it, or not 
yet when you start a new application. That's it. Ideally you interface with 
your database through one class. This way you can add the methods `init()` and 
`migrate()` to them. See [API](#api) below for examples.

# Use

You can use [Composer](https://getcomposer.org/) to integrate it in your 
project. The library is not currently published on 
[Packagist](https://packagist.org/), but you can add the repository directly
in your `composer.json`:

```javascript
    "repositories": [
        {
            "type": "vcs",
            "url": "https://git.tuxed.net/fkooman/php-sqlite-migrate"
        }
    ],

    "require": {
        "fkooman/sqlite-migrate": "^0"
    }
```

# Versions

The versions consist of a string of 10 digits. It makes sense to encode the 
current date in them with an additional 2 digit sequence number. The 
recommended format is `YYYYMMDDXX` where `XX` is the sequence that starts at 
`00` for the first version of that day. An example: `2018061502` for the 
third schema version on June 15th in 2018.

Internally the library uses `Migration::NO_VERSION` for when a database does 
not yet have a version. The value of `NO_VERSION` is `0000000000`. You can 
use `0000000000` also in migration files.

# Schema Files

Schema files are used to initialize a clean database. It contains the 
`CREATE TABLE` statements. They are named after their version and located in 
the schema directory. As an example, `/usr/share/app/schema/2018050501.schema` 
contains:

```sql
    CREATE TABLE foo (a INTEGER NOT NULL);
```

See [File Format](#file-format).

# Migration Files

Migration files contain the queries moving from one version to the next. 
Suppose in order to move from `2018050501` to `2018050502` a column is added
to the table `foo`. In this case, the file 
`/usr/share/app/schema/2018050501_2018050502.migration` could contain this:

```sql
    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;
```

Make sure to also create a `2018050502.schema` so new installations will 
immediately get the new database schema.

See [File Format](#file-format).

# File Format

Schema and migrations files contain SQL queries. Queries are separated by the 
semicolon character (`;`). The semicolon can ONLY be used to separate queries, 
the queries themselves can NOT contain semicolons.

# API

The API is very simple. The constructor requires a `PDO` object, the directory
where the schema and migration files can be found and the latest database
schema version.

```php
    $migration = new Migration(
        new PDO('sqlite::memory:'),
        '/usr/share/app/schema',
        '2018050501'
    );

    // initialize the database by looking for 2018050501.schema in the schema
    // directory. ONLY use this during application installation!
    $migration->init();

    // run the migration when needed moving from the currently deployed schema
    // version to the version specified in the constructor by looking for 
    // migration files in the schema directory
    $migration->run();
```

If your application has an "install" or "init" script you can use that to call
the `init()` method.

To perform database schema updates when needed, you can use the following call
in your `index.php` before using the database:

```php
    if ($migration->run()) {
        echo "Migrated!";
    } else { 
        echo "No migration was needed.";
    }
```

The `run()` method returns a `boolean`, indicating whether or not a migration 
was performed.

# Contact

You can contact me with any questions or issues regarding this project. Drop
me a line at [fkooman@tuxed.net](mailto:fkooman@tuxed.net).

If you want to (responsibly) disclose a security issue you can also use the
PGP key with key ID `9C5EDD645A571EB2` and fingerprint
`6237 BAF1 418A 907D AA98  EAA7 9C5E DD64 5A57 1EB2`.

# License

[MIT](LICENSE).