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).
|
