-
Notifications
You must be signed in to change notification settings - Fork 15
/
README
583 lines (434 loc) · 21.8 KB
/
README
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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
This document is a copy of http://code.google.com/p/scala-migrations/;
it may be easier to read it in a browser.
Scala Migrations is a library to manage upgrades and rollbacks to
database schemas. Migrations allow a source control system to manage
together the database schema and the code using the schema. It is
designed to allow multiple developers working on a project with a
database backend to design schema modifications independently, apply
the migrations to their local database for debugging and when
complete, check them into a source control system to manage as one
manages normal source code. Other developers then check out the new
migrations and apply them to their local database. Finally, the
migrations are used to migrate the production databases to the latest
schema version.
The Scala Migrations library is written in Scala and makes use of the
clean Scala language to write easy to understand migrations, which are
also written in Scala. Scala Migrations provides a database
abstraction layer that allows migrations to target any supported
database vendor.
=== History ===
Scala Migrations is developed at <a href="http://www.imageworks.com/">Sony Pictures Imageworks</a>
to manage database versioning for internal applications. The design
is based off <a href="http://api.rubyonrails.org/classes/ActiveRecord/Migration.html">Ruby
on Rails Migrations</a> and in fact shares the exact same
`schema_migrations` table to manage the list of installed migrations.
=== Sample Migration ===
Here is a migration used by !VnP3, an internal Imageworks project.
{{{
package com.imageworks.vnp.dao.migrations
import com.imageworks.migration.{Limit,
Migration,
Name,
NotNull,
OnDelete,
Restrict,
Unique}
/**
* Create the 'facility_set_membership' table, which is a many-to-many
* join table between the 'facility' and 'facility_set' tables. It
* represents the sets that a facility is a member of and the
* facilities that are in a set. Rows do not have a their own primary
* key.
*/
class Migrate_20081216235329_FacilitySetMembership
extends Migration
{
val tableName = "facility_set_membership"
def up() {
createTable(tableName) { t =>
t.varbinary("pk_facility", NotNull, Limit(16))
t.varbinary("pk_facility_set", NotNull, Limit(16))
t.bigint("created_micros", NotNull)
t.bigint("modified_micros", NotNull)
}
// There should only be one pair of (pk_facility_set, pk_facility)
// tuples in the entire table, i.e., for one facility set, the
// facility should only appear once.
addIndex(tableName,
Array("pk_facility_set", "pk_facility"),
Unique,
Name("idx_fac_set_mmbrshp_uniq_pks"))
addForeignKey(on(tableName -> "pk_facility"),
references("facility" -> "pk_facility"),
OnDelete(Restrict),
Name("fk_fac_set_mmbrshp_pk_fac"))
addForeignKey(on(tableName -> "pk_facility_set"),
references("facility_set" -> "pk_facility_set"),
OnDelete(Restrict),
Name("fk_fac_set_mmbrshp_pk_fac_set"))
}
def down() {
dropTable(tableName)
}
}
}}}
To migrate a database to the latest version requires code similar to:
{{{
import com.imageworks.migration.{DatabaseAdapter,
InstallAllMigrations,
Vendor}
object Test
{
def main(args: Array[String]) {
val driver_class_name = "org.postgresql.Driver"
val vendor = Vendor.forDriver(driver_class_name)
val migration_adapter = DatabaseAdapter.forVendor(vendor, None)
val data_source: javax.sql.DataSource = ...
val migrator = new Migrator(data_source, migration_adapter)
// Now apply all migrations that are in the
// com.imageworks.vnp.dao.migrations package.
migrator.migrate(InstallAllMigrations, "com.imageworks.vnp.dao.migrations", false)
}
}}}
To rollback a database to its pristine state:
{{{
migrator.migrate(RemoveAllMigrations, "com.imageworks.vnp.dao.migrations", false)
}}}
To rollback two migrations:
{{{
migrator.migrate(RollbackMigration(2), "com.imageworks.vnp.dao.migrations", false)
}}}
And to migrate to a specific migration, rollbacking back migrations
that are newer than the requested migration version and installing
migrations older than the requested version.
{{{
migrator.migrate(MigrateToVersion(20090731), "com.imageworks.vnp.dao.migrations", false)
}}}
=== Supported Databases ===
Scala Migrations currently supports
* Derby
* MySQL
* Oracle
* PostgreSQL
Patches for other databases are welcome; however, you will need to
submit a [http://opensource.imageworks.com/cla/ Contributor License Agreement].
=== Start using Scala Migrations ===
Maven Central hosts compiled jars for Scala 2.8.0 and greater,
compiled on JDK 1.6/JDBC 4. All Scala Migrations artifacts have a
`groupId` of `com.imageworks.scala-migrations`. A separate
compilation and publish is done for each Scala version, with a
distinct artifactId of the form `scala-migrations_X.Y.Z`, where
`X.Y.Z` is the Scala version used to compile Scala Migrations.
Direct links to jars compiled against 2.8.0 or greater can be found at
[http://search.maven.org/#search%7Cga%7C1%7Cg%3Acom.imageworks.scala-migrations Maven Central].
Jars for Scala 2.7.7 for JDBC 3 and JDBC 4 are on the
[http://code.google.com/p/scala-migrations/downloads/list Downloads]
page.
==== sbt ====
Add the following to your `build.sbt`:
{{{
libraryDependencies ++= Seq("com.imageworks.scala-migrations" %% "scala-migrations" % "1.1.1")
}}}
==== Ivy ====
Add the following to the dependencies section of the `ivy.xml` file,
replacing `X.Y.Z` with your Scala version.
{{{
<dependency org="com.imageworks.scala-migrations" name="-migrations_X.Y.Z" rev="1.1.1" />
}}}
==== Maven ====
Add the following snippet to the `<dependencies />` section of the
project's `pom.xml` file, replacing `X.Y.Z` with your Scala version.
{{{
<dependency>
<groupId>com.imageworks.scala-migrations</groupId>
<artifactId>scala-migrations_X.Y.Z</artifactId>
<version>1.1.1</version>
</dependency>
}}}
=== Dependencies and Setup ===
Scala Migrations depends upon:
* The Simple Logging Facade for Java (SLF4J).
http://www.slf4j.org/
The Simple Logging Facade for Java or (SLF4J) serves as a simple
facade or abstraction for various logging frameworks, e.g. log4j
and java.util.logging, allowing the end user to plug in the
desired logging framework at deployment time.
Scala Migrations has a library dependency upon SLF4J's slf4j-api
jar, which only provides an interface to a logging API. The
application must chose a concrete logging implementation by
ensuring that one of the following jars is available in the
classpath. If no implementation jar is provided, the the
no-operation logging implementation is used.
- slf4j-log4j12
Binding for log4j version 1.2, a widely used logging
framework. You also need to place log4j.jar on your
classpath.
- slf4j-jcl
Binding for Jakarta Commons Logging. This binding will
delegate all SLF4J logging to JCL.
- slf4j-jdk14
Binding for java.util.logging, also referred to as JDK 1.4
logging.
- slf4j-nop
Binding for NOP, silently discarding all logging.
- slf4j-simple
Binding for Simple implementation, which outputs all events to
System.err. Only messages of level INFO and higher are
printed. This binding may be useful in the context of small
applications.
See http://www.slf4j.org/manual.html for more information.
* The log4jdbc logging JDBC wrapper that logs all JDBC operations.
http://code.google.com/p/log4jdbc/
Since running a migration on a production database is dangerous
operation that can leave irreversible damage if anything goes
wrong, the JDBC connection given to all migrations is a log4jdbc
`net.sf.log4jdbc.ConnectionSpy` that wraps the real connection.
This logs all method calls so that any migration errors can be
fully debugged. log4jdbc uses SLF4J; see the log4jdbc website on
how to set up the loggers and logging level for log4jdbc messages.
As of 1.0.3, Scala Migrations will use log4jdbc to wrap the real
database connection if log4jdbc is found at runtime in the
classpath, otherwise it will use the raw database connection and
not do any SQL specific logging. No special work needs to be done
by the migration author to use log4jdbc, besides making it
available in the classpath. Before 1.0.3, Scala Migrations
required that log4jdbc be in the classpath.
=== Migration Naming ===
In Scala Migrations, the migrations needs to be compiled and their
`*`.class files need to be made available at runtime; the source files
will not be available at runtime.
Scala Migrations then needs to know an ordering on the migrations, so
the timestamp needs to be in the class name. Scala does not support
naming a symbol such as `20080717013526_YourMigrationName` because the
name begins with a digit (unless one were to quote the name which
would look odd), so the Scala Migrations looks for classes named
{{{
Migrate_(\\d+)_([_a-zA-Z0-9]*)
}}}
The time stamp can be generated using the following command on Unix systems:
{{{
$ date -u +%Y%m%d%H%M%S
}}}
This is different than Ruby on Rails migrations which are in filenames
of the form
{{{
20080717013526_your_migration_name.rb
}}}
and have a corresponding class name such as `YourMigrationName`. Ruby
on Rails can find all the migration `*`.rb files for a project and
load them at runtime and from the filename load the correct class
name. The ordering to apply the migrations is contained in the
filename, not the class name.
=== Unsupported Database Features ===
It is not a goal of Scala Migrations to check and report on the
compatibility of a Scala Migrations specific feature with a database.
For example, Oracle does not support the `"ON UPDATE SET NULL"` clause
on a foreign key constraint. If a `OnUpdate(SetNull)` is specified
for a foreign key constraint, then Scala Migrations will generate that
clause and ask the database to execute it.
If Scala Migrations did attempt to check on the compatibility of each
feature, then it would need to grow much larger to know which features
worked on which database, and even worse, potentially know which
features appear in which database versions. This is not something
that the authors of Scala Migrations want to maintain.
=== Data Types ===
The following data types are supported listed with their mappings. If
a database name is not specified, then the default mapping is used.
More information on the mappings is below.
* Bigint
* Default: `BIGINT`
* Oracle: `NUMBER(19, 0)`
* Blob
* Default: `BLOB`
* MySQL: `LONGBLOB`
* PostgreSQL: `BYTEA`
* Boolean
* Default: `BOOLEAN`
* Derby: Unsupported; even though Derby 1.7 supports a `BOOLEAN`
type, Scala Migrations currently always throws an
`UnsupportedColumnTypeException`
* Oracle: Unsupported; an `UnsupportedColumnTypeException` is
thrown if Boolean is used
* Char
* Default: `CHAR`
* Decimal
* Default: `DECIMAL`
* Oracle: `NUMBER`
* Integer
* Default: `INTEGER`
* Oracle: `NUMBER(10, 0)`
* Smallint
* Default: `SMALLINT`
* Oracle: `NUMBER(5, 0)`
* Timestamp
* Default: `TIMESTAMP`
* MySQL: `TIMESTAMP` but does not support fractional precision
* Varbinary
* Default: `VARBINARY`
* Derby: `VARCHAR FOR BIT DATA`
* Oracle: `RAW`
* PostgreSQL: `BYTEA`
* Varchar
* Default: `VARCHAR`
* Oracle: `VARCHAR2`
=== Boolean Mapping ===
Scala Migrations does not define a mapping for the Boolean data type
in databases that do not have a native Boolean data type. The reason
is that there are many ways of representing a Boolean value database
and Scala Migrations is not an ORM layer, so this decision is left to
the application developer.
Different representations that have been used in schemas include:
* A `CHAR(1)` column containing a 'Y' or 'N' value. The column may
have a `CHECK` constraint to ensure that the values are only 'Y'
or 'N'.
* An `INTEGER` column with 0 representing to false and all other
values representing true.
=== BLOB and VARBINARY Mappings ===
Each database treats BLOB and VARBINARY differently.
|| *Database* || *Scala Migrations Type* || *SQL Type* || *Maximum Length (bytes)* || *Specify Length?* || *Specify Default?* || *References* || *Notes* ||
|| Derby || Blob || `BLOB` || 2,147,483,647 || Optional, defaults to 2 GB || No || [http://db.apache.org/derby/docs/10.9/ref/rrefblob.html 1] || ||
|| || Varbinary || `VARCHAR FOR BIT DATA` || 32,672 || Required || Yes || [http://db.apache.org/derby/docs/10.9/ref/rrefsqlj32714.html 2] || ||
|| MySQL || Blob || `LONGBLOB` || 4,294,967,295 || No || No || [http://dev.mysql.com/doc/refman/5.5/en/blob.html 3] || ||
|| || Varbinary || `VARBINARY` || 21,844 >= && <= 65,535 || Required || Yes || [http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html 4] || Stored in row ||
|| Oracle || Blob || `BLOB` || 4,294,967,296 in Oracle 8, larger in newer versions || No || ?? || [http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#i54330 5] [http://ss64.com/ora/syntax-datatypes.html 6]|| ||
|| || Varbinary || `RAW` || 2,000 || Required || ?? || || ||
|| PostgreSQL || Blob || `BYTEA` || 1,073,741,823 || No || Yes || [http://www.postgresql.org/docs/9.1/static/storage-toast.html 7]|| ||
|| || Varbinary || `BYTEA` || 1,073,741,823 || No || Yes || || ||
=== Oracle and SMALLINT, INTEGER and BIGINT ===
Oracle does not have `SMALLINT`, `INTEGER` or `BIGINT` SQL types
comparable to other databases, such such as Derby, MySQL and
PostgreSQL. These other databases used a fixed sized signed integer
with a limited range of values that can be stored in the column.
||Type ||Storage ||Min value ||Max value ||
||SMALLINT||2-byte signed integer||-32768 ||32767 ||
||INTEGER ||4-byte signed integer||-2147483648 ||2147483647 ||
||BIGINT ||8-byte signed integer||-9223372036854775808||9223372036854775807||
Oracle does support an `INTEGER` column type but it uses a `NUMBER(38)`
to <a href="http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref218">store it</a>.
On Oracle, a Scala Migration using any of the `SMALLINT`, `INTEGER`
and `BIGINT` types is mapped to a `NUMBER` with a precision smaller
than 38.
||Migration Type||Oracle Type ||
||SMALLINT ||NUMBER(5, 0) ||
||INTEGER ||NUMBER(10, 0)||
||BIGINT ||NUMBER(19, 0)||
This helps ensure the compatibility of any code running against an
Oracle database so that it does not assume it can use 38-digit integer
values in case the data needs to be exported to another database or if
the code needs to work with other databases. Columns wishing to use a
`NUMBER(38)` should use a DecimalType column.
=== NUMERIC and DECIMAL ====
There is a minor difference in the definition of the `NUMERIC` and
`DECIMAL` types according to the <a
href="http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt">SQL
1992 standard</a>:
{{{
17) NUMERIC specifies the data type exact numeric, with the decimal
precision and scale specified by the <precision> and <scale>.
18) DECIMAL specifies the data type exact numeric, with the decimal
scale specified by the <scale> and the implementation-defined
decimal precision equal to or greater than the value of the
specified <precision>.
}}}
However, in practice, all databases we looked at implement them
identically.
*) Derby
"NUMERIC is a synonym for DECIMAL and behaves the same way. See
DECIMAL data type."
http://db.apache.org/derby/docs/10.4/ref/rrefsqlj12362.html
http://db.apache.org/derby/docs/10.4/ref/rrefsqlj15260.html
*) Mysql
"NUMERIC implemented as DECIMAL."
http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html
*) Oracle
Only has the `NUMBER` type.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref218
*) PostgreSQL
"The types decimal and numeric are equivalent. Both types are
part of the SQL standard."
The documentation uses `NUMERIC` more and lists `DECIMAL` as an alias.
http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html
http://www.postgresql.org/docs/8.3/interactive/datatype.html#DATATYPE-TABLE
=== Auto-incrementing Column Default Values ===
Several databases natively support a default value for integer column
data types that use as the next default value the next value from an
automatically increasing sequence of integer values. The use of the
AutoIncrement column option enables this feature for a column.
Here are the database mappings:
* Derby
Only supported on `SMALLINT`, `INT` and `BIGINT` data types using
Derby's `GENERATED BY DEFAULT AS IDENTITY`. The alternate setting
`GENERATED ALWAYS AS IDENTITY` is not used as it is not consistent
with MySQL and PostgreSQL which permits the application to
explicitly specify the column's value.
http://db.apache.org/derby/docs/10.9/ref/rrefsqlj37836.html
* MySQL
Only supported on `SMALLINT`, `INT` and `BIGINT` data types using
MySQL's `AUTO_INCREMENT` keyword.
http://dev.mysql.com/doc/refman/5.5/en/create-table.html
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
* PostgreSQL
Only supported on `SMALLINT`, `INT` and `BIGINT` data types by
replacing the data type name with `SMALLSERIAL`, `SERIAL` and
`BIGSERIAL`, respectively. Support for `SMALLSERIAL` is only
available in PostgreSQL 9.2 and greater.
http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-SERIAL
* Oracle
No support is provided in this commit as it appears that
equivalent functionality can only be provided by using triggers.
=== Character Set Encoding ===
Scala Migrations supports specifying the character set for `Char` and
`Varchar` columns with the `CharacterSet()` column option, which takes
the name of the character set as an argument. Currently, the only
supported character set name is Unicode.
Here is how different databases handle character set encoding.
* Derby
"Character data types are represented as Unicode 2.0 sequences in
Derby."
So specifying `CharacterSet(Unicode)` does not change its
behavior. Using any character set name besides Unicode as the
argument to `CharacterSet()` raises a warning and is ignored.
http://db.apache.org/derby/docs/10.4/devguide/cdevcollation.html
* MySQL
MySQL supports 30+ character sets and and all of them can be
simultaneously used; in fact, a table can have multiple character
type columns, each with a different character set. See
http://dev.mysql.com/doc/refman/5.5/en/charset-database.html for
reference.
If no `CharacterSet` is used, then MySQL will use the database's
or the server's default character set and the default character
set's default collation. If `CharacterSet(Unicode)` is used, then
Scala Migrations uses the `utf8` character set with the
`utf8_unicode_ci` collation, which is not MySQL's default
`utf8_general_ci` collation for `utf8`, as `utf8_unicode_ci` is
[http://stackoverflow.com/questions/766809/ not incorrect].
Users wishing to have more control on specifying character sets
and collations can discuss this on the developers mailing list.
* PostgreSQL
The character set encoding is chosen when a database is created
with the "createdb" command line utility or the
`CREATE DATABASE ENCODING [=] encoding`
SQL statement. So specifying any `CharacterSet` has no effect.
* Oracle
Oracle only supports two character sets. The first uses the
database character set which was chosen when the database was
created. This encoding is used for `CHAR`, `VARCHAR2` and `CLOB`
columns. The second character set is called the national
character set and is Unicode, which is used for `NCHAR`,
`NVARCHAR2` and `NCLOB` columns. There are two encodings
available for the national character set, `AL16UTF16` and `UTF8`.
By default, Oracle uses `AL16UTF16`.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/ch6unicode.htm
Specifying no `CharacterSet` column option defaults the `Char`
type to `CHAR` and the `Varchar` type to `VARCHAR2`. If
`CharacterSet(Unicode)` is given, then `Char` uses `NCHAR` and
`Varchar` uses `NVARCHAR2`. Using any character set name besides
`Unicode` as the argument to `CharacterSet()` raises a warning and
is ignored, resulting in `CHAR` and `VARCHAR2` column types.
=== Caveats ===
* Index and foreign key names do not use the same naming convention
as the Ruby on Rails migrations, so a port of Ruby on Rails
migrations to Scala Migrations should specify the index name using
the `Name()` case class as an option to `add_index()` or
`remove_index()`.