Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 395 lines (286 sloc) 12.008 kb
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
1 # Installation #
2 In your terminal (vagrant), do:
65141f5 @brendonh Design notes as readme
authored
3
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
4 ```bash
5 cd [repo]/protected/config
6 cp db.json.sample db.json
7 cd [repo]/protected/schema
8 virtualenv env
9 . env/bin/activate
10 pip install -r requirements.txt
11 ```
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
12
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
13 Next time, when you want to run schemup:
65141f5 @brendonh Design notes as readme
authored
14
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
15 ```bash
16 . env/bin/activate
17 python update.py commit
18 ```
65141f5 @brendonh Design notes as readme
authored
19
20
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
21 # General #
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
22
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
23 Schemup versions a database on a per-table basis. This means that table X can be at version 1, while table Y can be at version 2.
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
24
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
25 All versioning data is stored in a special table called `schemup_tables`. This table keeps other (versioned) tables' schema history, including what their latest schemas should look like (somewhat similar to git history).
65141f5 @brendonh Design notes as readme
authored
26
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
27 Schemup provides 2 main features: validation (schemas synchronization checking), and migration (schemas updating).
65141f5 @brendonh Design notes as readme
authored
28
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
29
30
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
31 # Version declaration #
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
32
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
33 This is basically just a map that states what version each table should be at. There are a couple of convenient helpers to build this map.
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
34
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
35 ## Storm ORM
65141f5 @brendonh Design notes as readme
authored
36
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
37 This is achieved by using a decorator, and adding a special attribute `__version__` to model class declarations.
65141f5 @brendonh Design notes as readme
authored
38
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
39 ```python
40 from storm.locals import *
41 from schemup.orms import storm
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
42
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
43 # Pass this to validate/upgrade commands. It should be a global
44 # shared among model files, if there are several of them
45 stormSchema = storm.StormSchema()
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
46
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
47 @stormSchema.versioned
48 class User(Storm):
49 __storm_table__ = "user"
50 __version__ = "knn_1"
51 ```
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
52
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
53 ## JSON file
65141f5 @brendonh Design notes as readme
authored
54
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
55 Keep the map in a json file.
65141f5 @brendonh Design notes as readme
authored
56
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
57 **`versions.json`**
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
58
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
59 ```json
60 {
61 "users": "nta_6",
62 "message": "ntd_9"
63 }
64 ```
65141f5 @brendonh Design notes as readme
authored
65
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
66 **`update.py`**
65141f5 @brendonh Design notes as readme
authored
67
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
68 ```python
69 class DictSchema(object):
70 def __init__(self, path):
71 self.versions = json.load(open(path, "r"))
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
72
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
73 def getExpectedTableVersions(self):
74 return sorted(self.versions.iteritems())
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
75
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
76 # Pass this to validate/upgrade commands
77 dictSchema = DictSchema("versions.json")
78 ```
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
79
80
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
81 # Validation #
0c1cb3f @brendonh Sigh, fix Markdown formatting
authored
82
df41aa6 @brendonh Use Phu's docs as the README, move old notes to notes.md
authored
83 Schemup helps keeping track, for each table, of the synchronization between 3 things:
84
85 - The desired schema, declared in code, or data file (actually only version, no table structure).
86 - The journaled schema (cached schema, recorded schema) in `schemup_tables` (both version and table structure).
87 - The actual DB schema (table structure only, obviously).
88
89 Full validation happens in 2 steps:
90
91 ## Checking recorded schema vs. desired schema (version mismatches) ##
92
93 This is done by simply comparing the versions declared in code with the latest version recorded in `schemup_tables`. Note that there is not (yet) an actually schema comparison.
94
95 Out-of-sync tables detected by this validation indicate that the current schema in `schemup_tables` (and thus the actual schema, provided that they are in sync) need to be brought up-to-date with the desired schema (using Schemup migration feature).
96
97 ## Checking recorded schema vs. actual schema (schema mismatches) ##
98
99 This is done by getting the schema information from the DB (e.g. `information_schema.tables`), and compare them against the last recorded schema in `schemup_tables`.
100
101 Mismatches detected by this validation usually means the schema was changed outside of Schemup's control, which should be avoided.
102
103 ```python
104 from schemup import validator
105 from warp import runtime
106
107 conn = runtime.store.get_database().raw_connect()
108 dbSchema = postgres.PostgresSchema(conn)
109
110 errors = validator.findSchemaMismatches(dbSchema)
111 if errors:
112 print "Schema mismatches, was the schema changed outside Schemup?"
113 ```
114
115
116
117 # Migration #
118
119 Schemup migration feature attempts to bring the real schema (and `schemup_tables`) up-to-date with the current ORM schema, by applying a series of "upgraders".
120
121 Each upgrader is responsible for bringing a table from one version to another, using an upgrading function that will be run on the DB schema.
122
123 An upgrader also has dependencies, which are the required versions of some tables before it can be run. For example, a foreign key referencing a table can only be added after the table is created.
124
125 There are 2 types of upgraders: those created from decorated Python functions, and those loaded from YAML files. There is a command to load both types from files under a directory.
126
127 ```python
128 from schemup import commands
129
130 # Load upgraders from .py & .yaml files under "migration" directory
131 commands.load("migrations")
132 ```
133
134 After getting all the necessary upgraders, the `upgrade` command can be used to carry out the migration.
135
136 ```python
137 from schemup import commands
138 from warp import runtime
139 from models import stormSchema
140
141 conn = runtime.store.get_database().raw_connect()
142 dbSchema = postgres.PostgresSchema(conn)
143
144 commands.upgrade(dbSchema, stormSchema)
145 ```
146
147 ## Python upgrading functions ##
148
149 Note that the logic used by these functions must be immutable over time. Therefore application logic (functions, orm classes...) from other module must not be used directly, but copied for use only in the migrations; otherwise the migrations will be broken once application logic changes.
150
151 ```python
152 from schemup.upgraders import upgrader
153
154 @upgrader('user', 'bgh_2', 'bgh_3')
155 def user_add_email(dbSchema):
156 dbSchema.execute("ALTER TABLE user ADD email VARCHAR")
157 # Or running arbitrary code here
158
159 @upgrader('order', None, 'knn_1', dependencies=[('user', 'bgh_1')])
160 def order_create(dbSchema):
161 dbSchema.execute("""
162 CREATE TABLE order (
163 id integer NOT NULL PRIMARY KEY,
164 user_id integer NOT NULL,
165 CONSTRAINT order_user_id FOREIGN KEY (user_id) REFERENCES user(id)
166 )
167 """)
168 ```
169
170 ## Upgraders loaded from YAML files ##
171
172 One file can contain multiple blocks delineated by `---`. Each block corresponds to an upgrader. If a block's `from` key is omitted, it defaults to the previous block's `to` key.
173
174 ### One table per file ###
175
176 **`user.yaml`**
177
178 ```yaml
179 ---
180 # Another upgrader
181
182 ---
183 table: user
184 from: bgh_2
185 to: bgh_3
186 sql: |
187 ALTER TABLE user ADD email VARCHAR
188
189 ---
190 # Another upgrader
191 ```
192
193 **`order.yaml`**
194
195 ```yaml
196 ---
197 table: order
198 from: null
199 to: knn_1
200 depends:
201 - [ user, bgh_1 ]
202 sql: |
203 CREATE TABLE order (
204 id integer NOT NULL PRIMARY KEY,
205 user_id integer NOT NULL,
206 CONSTRAINT order_user_id FOREIGN KEY (user_id) REFERENCES user(id)
207 )
208 ```
209 ### One feature per file ###
210
211 **`feature.add-rule-table.yaml`**
212
213 ```yaml
214 ---
215 table: questionnaire_rule
216 from: null
217 to: nta_1
218 depends:
219 - [questionnaire, nta_2]
220 sql: |
221 CREATE TABLE questionnaire_rule (
222 id SERIAL NOT NULL PRIMARY KEY,
223 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
224 issue TEXT,
225 requires TEXT[2][],
226 recommendations INTEGER[],
227 questionnaire_id INTEGER NOT NULL REFERENCES questionnaire(id) ON DELETE RESTRICT
228 );
229
230 ---
231 table: questionnaire
232 from: nta_3
233 to: nta_4
234 depends:
235 - [questionnaire_rule, nta_2]
236 sql: |
237 ALTER TABLE questionnaire
238 DROP COLUMN rules;
239 ```
240
241 # Snapshoting #
242
243 ## Whole schema ##
244
245 Use this when you have an existing database whose schema changes need to be kept track of with Schemup.
246
247 - Add version declarations.
248 - Add correct schema migrations. This ensures that a new instance can be created from scratch. If there is not enough time, a workaround can be used: put the schema dump in one of the migration, leaving the rest of the migrations no-op (e.g. `SELECT 1;`). For example:
249
250 ```yaml
251 ---
252 table: users
253 from: null
254 to: nta_1
255 sql: |
256 # The whole schema here
257
258 ---
259 table: message
260 from: nul
261 to: nta_1
262 sql: |
263 SELECT 1;
264
265 # Other tables
266 ```
267
268 - Use the `snapshot` command.
269
270 ```python
271 from schemup.dbs import postgres
272 from schemup import commands
273 from warp.runtime import store
274 conn = store.get_database().raw_connect()
275 dbSchema = postgres.PostgresSchema(conn)
276 commands.snapshot(dbSchema, stormSchema)
277 ```
278
279 ## Single table (aka I mistakenly changed the schema in SQL shell) ##
280
281 Use this when you mistakenly chang a table's schema outside of schemup (e.g. trying out DDL in SQL shell without rolling back the transaction). This creates a
282 schema mismatch
283
284 ```python
285 from warp.common.schema import makeSchema
286 from warp.runtime import store
287 schema = makeSchema(store)
288 schema.setSchema("recommendation", "nta_5")
289 schema.commit()
290 ```
291
292
293 # Workflow #
294
295 - When adding to an existing DB, use snapshotting.
296 - When starting from scratch, provide upgraders with `from` equal to `None` (python) or `null` (yaml).
297 - Version naming convention: programmer initials and integer id. Example: `bgh_1`, `bgh_2`, `knn_3`, `nta_4`, `knn_5`.
298 - Migration organization: one-feature-per-file is preferred; initial schema can be in its own file.
299
300 ## Upgraders ##
301
302 - When there are schema changes, bump model classes' `__version__`.
303 - Put upgraders under `migrations` directory. Upgraders can be yaml files, or python files containing upgrader-decorated functions.
304 - Test the migration manually on a dev DB.
305 - Remember that Postgres DDL is transactional. Therefore it is a good idea to try out migration DDL in Postgres shell, wrapped in a transaction that will be rolled back.
306
307 ```sql
308 START TRANSACTION;
309 -- Try CREATE TABLE, ALTER TABLE... here
310 ROLLBACK;
311 ```
312
313 ## Migration ##
314
315 - Back up the DB before doing migration.
316 - Migration steps
317
318 ```python
319 from schemup.dbs import postgres
320 from schemup import commands
321 from warp.runtime import store
322
323 # Get current table versions, by ORM
324 from models import stormSchema
325
326 # Get schema
327 conn = store.get_database().raw_connect()
328 dbSchema = postgres.PostgresSchema(conn)
329
330 # Make sure the current DB is not "dirty"
331 validator.findSchemaMismatches(dbSchema)
332
333 # Load upgraders
334 commands.load("migrations")
335
336 # Do upgrade
337 commands.upgrade(schema, stormSchema)
338
339 # Check if the schemas are in sync
340 commands.validate(runtime.schema, stormSchema)
341 ```
342
343 ## Shared dev machine ##
344
345 Schemup works on a forward-only, no-branching (directed acyclic graph) basis. This creates a problem in using shared dev machines:
346
347 - Supposed the main branch is at `user:a1`, `message:b1`.
348 - Developer A add migration `user:a_1` to `user:a_2` on his topic branch and test it on dev.
349 - Developer B add migration `message:b_1` to `message:b_2` and wants to test it on dev. He checks out his branch and runs the migration. Because `user` is at `a_2`, but the code wants it to be at `a_1`, schemup tries migrating `user` from `a_2` to `a_1` and fails not knowing how.
350
351 The best solution is to ensure that the DB's schema is the same before and after you test the code with new schema. For example:
352
353 - Make a dump of the whole database before running schema migration.
354 - Switch back to the branch the code was on previously after testing the new code.
355 - Replace the current state of the database with the dump.
356
357 ## Snapshot-less application of schemup to existing DB ##
358
359 This method was by proposed Duy.
360 The idea is to use a dump as the DB's initial state, instead of a blank DB. The process looks like:
361
362 - Start with no migrations, blank version declarations.
363 - New instance are provisioned by the initial dump instead of just a blank DB.
364 - Continue as normal.
365 - New migrations should be written with the non-blank initial DB's state in mind. For example if the dump already contains a table `user`, its migrations should look like:
366
367 ```yaml
368 ---
369 table: user
370 from: null
371 to: lmd_1
372 sql: |
373 ALTER TABLE user ADD COLUMN age INTEGER DEFAULT NULL;
374 ```
375
376 and not
377
378 ```yaml
379 ---
380 table: user
381 from: null
382 to: lmd_1
383 sql: |
384 CREATE TABLE user (
385 # ...
386 )
387
388 ---
389 table: user
390 from: lmd_1
391 to: lmd_2
392 sql: |
393 ALTER TABLE user ADD COLUMN age INTEGER DEFAULT NULL;
394 ```
Something went wrong with that request. Please try again.