dartabase_migration 0.4.1 
dartabase_migration: ^0.4.1 copied to clipboard
Serverside Database migration for easy database structure manipulation inspired by Ruby on Rails migrations for mysql and postgresql
Dartabase Migration 0.4.1 #
Serverside Database migration
for simple version controlled database structure manipulation
now possible to combine the power of Dartabase Model and Dartabase Migration
*see http://pub.dartlang.org/packages/dartabase_model
inspired by Ruby on Rails migrations
Version
0.4.1 -added column options 'default' and 'null'
see "COLUMN OPTIONS"
0.4.0 -small breaking change -> added posibility for column options
0.3.0 -breaking change -> added autogenerated created_at and updated_at
see "COLUMN CREATED/UPDATED" info below
0.2.0 -breaking change -> added autogenerated id
see "COLUMN ID" info below
0.1.0 -updated for Dart 1.0, restructured code for use with Dartabase Models
0.0.5 -adapted breaking changes due to dart:encoder
Tested on
Dart Editor version 1.0.1_r30657 (DEV)
Dart SDK version 1.0.1.3_r30657
compatibility
depending on the migration version you are using
you have to use a differend model version in your app
migration model
-------------------------
0.4.x requires 0.4.x
0.3.0 requires 0.3.0 deprecated!!
Uses
MYSQL via http://pub.dartlang.org/packages/sqljocky version 0.9.0
PGSQL via http://pub.dartlang.org/packages/postgresql version 0.2.12
HOW TO SETUP #
USE THIS INSTALL GUIDE AND IGNORE THE INSTALL PAGE!!!
This is a stand alone app!
1.Download dartabase_migration somewhere on your drive
now run 'Pub Get' on dartabase_migration/pubspec.yaml
2.Execute dartabase_migration/bin/dbInit.dart
to initiate the dartabase_migration tool for your project.
3.follow the instructions
*enter a project name
*enter path to project root folder
4.dartabase_migration will create files and folders below to do its magic
*dartabase_migration/bin/projectsMapping.json
-maps project names to absolute project path
*$yourProjectName/db/
*$yourProjectName/db/config.json
-dartabase config file to connect IP/PORT/DBType
*$yourProjectName/db/schema.json
-current dartabase_migration structure as JSON used by Dartabase tools
*$yourProjectName/db/schemaVersion.json
-safes name of latest migrated version
*$yourProjectName/db/migrations
-folder for your database migration files
5.Edit the *$yourProjectName/db/config.json file
so dartabase_migration can connect to your existing database.
eg.
--------config.json---------
{
"adapter": "MySQL",
"database": "dbName",
"username": "dbUsername",
"password": "dbPassword",
"host": "localhost",
"port": "3306"
}
----------------------------
for postgresql use
"adapter": "PGSQL" (all capital)
HOW TO UPDATE DARTABASE MIGRATION #
GENEREL UPDATE
it is important to keep 'dartabase_migration/bin/projectsMapping.json'
but since this file will be autogenerated only on initiation of dartabase migration,
there should not be a problem.
1.download the new version of dartabase migration
2.replace all files from your current running version
(but not projectsMapping.json)
LOST 'projectsMapping.json'
DONT PAN!C
create a file named projectsMapping.json inside 'dartabase_migration/bin/'
inside the file enter a json key value pair of a projectname of your choice
keys : project_names (your choice/short is good ;)
values : project absolute project path
eg.
{
"mysql":"C:\\myServer",
"pgsql":"C:\\pgServer"
}
MOVED/RENAMED project
Rename the values when moving/renaming one of your projects
now you should be able to find your projects again when running dbUp or dbDown
UPDATE TO VERSION 0.4.0
inside your migration files createTable and createColumn hashes
instead of
"createTable": {
"Gamechar": {
"name": "VARCHAR"
}
},
"createColumn": {
"Account": {
"charname": "VARCHAR"
}
},
write
"createTable": {
"Gamechar": {
"name": {"type":"VARCHAR"}
}
},
"createColumn": {
"Account": {
"charname": {"type":"VARCHAR"}
}
},
UPDATE TO VERSION 0.2.0
If you had already created a migration file with a comuln 'id' in version < 0.2.0
remove the entry for 'id' from all your migration file UP and DOWN hashes
HOW TO CREATE MIGRATIONS #
Either
1a.execute dartabase_migration/bin/createMigration.dart and follow the instructions
*enter project name
*enter migration name eg. "create_table_user"
it will create a dummy migration inside
"$yourProjectName/db/migrations/YYYYMMTTHHMMSS_create_table_user"
or
1b.Create a migration json file "timestamp_action.json"
inside "$yourProjectName/db/migrations"
eg. "$yourProjectName/db/migrations/20130709134700_create_table_user.json"
2.inside your migration file you have a fixed structure!
a JSON object with a key "UP" and a json object value
to use migrations you can specify 4 keys/actions inside the "UP" value
createTable
-----------
"createTable" key takes a json object as value
keys : non_existent_table_names
values : json object
keys : non_existent_column_names
values : json object
keys: column options
values: column option values
eg.
"createTable": {
"new_table_name_one": {
"new_column_name": {
'type':"DATATYPE"
}
}
}
createColumn
------------
"createColumn" key takes a json object as value
keys : existing_table_names
values : json object
keys : non_existent_column_names
values : json object
keys: column options
values: column option values
eg.
"createColumn": {
"existing_table_name_one": {
"new_column_name": {
'type':"DATATYPE"
}
}
}
removeColumn
------------
"removeColumn" key takes a json object as value
keys : existing_table_names
values : array[existing_column_names]
eg.
"removeColumn": {
"existing_table_name_one": ["existing_column_name_one"]
}
removeTable
-----------
"removeTable" key takes array of existing_table_names
eg.
"removeTable": ["existing_table_name_one"]
A simple migration could look like
----------20130709134700_create_table_user.json--------------
{
"UP": {
"createTable": {
"user": {
"name": "VARCHAR"
}
}
},
"DOWN": {
"removeTable": ["user"]
}
}
We create a table
named "user" with column "name" and datatype "variable length of characters"
COLUMN ID #
The 'id' column will be generated by 'Dartabase Migration' for every table
as primary key. Dont add 'id' in any of the migration files.
This is to let 'Dartabase Model' decide when to create or update an Object
on save() - see http://pub.dartlang.org/packages/dartabase_model
COLUMN CREATED/UPDATED #
For each table a created_at and updated_at column will be generated automatically.
created_at
will only be set to current datetime on creation of table row entry
updated_at
will be set to current datetime on creation of table row entry
PGSQL
will be updated when the row has been saved
MySQL
will be updated when the row has been saved and a value of the row changed
COLUMN OPTIONS #
following options are awailable for columns
option values
"type" -> "DARTABASETYPE" <---always needed
"default" -> "yourDefaultValue" <---optional
"null" -> "true" or "false" <---optional
if options are not set then it should use dbAdapter standart settings
example
"createColumn": {
"player": {
"player_name": {
'type':"VARCHAR",
'null':"false",
'default':"unnamed Player"
}
}
}
UP AND DOWN #
Additionally to the "UP" key you can specify all actions inside the "DOWN" key
actions inside "UP" are executed during migration
actions inside "DOWN" are executed when reverting migrations
since we created a table named "user",
we might want to remove it once we want to revert the migration
!!!ATTENTION be sure your don't need the data inside a table/column
before you remove it!!!
ORDER OF EXECUTION #
Once you have more than one action in the migration file
eg.
adding a column
adding a table
removing a column
remember that the order of execution inside a migration will be
createTable
->
createColumn
->
removeColumn
->
removeTable
but I cant think of a feasible example where that might bring up problems.
HOW TO RUN MIGRATIONS #
1.Execute dartabase_migration/bin/dbUp.dart
2.Follow instructions in console
*enter project name
*enter goal migration version
dartabase_migration should have executed the actions specified inside the "UP" key
for all files INCLUDING the goal migration version.
Additionally it will update
*$yourProjectName/db/schema.json
with the current database structure as JSON
*$yourProjectName/db/schemaVersion.json
with the name of latest migrated migration file
HOW TO REVERT MIGRATIONS #
1.Execute dartabase_migration/bin/dbDown.dart to execute the dartabase_migration
actions you specified inside the "DOWN" key.
2.Follow instructions in console
*enter project name
*enter goal migration version
dartabase_migration should have executed the actions specified inside the "DOWN" key
for all files EXCLUDING the goal migration version.
Additionally it will update
*$yourProjectName/db/schema.json
with the current database structure as JSON
*$yourProjectName/db/schemaVersion.json
with the name of latest migrated migration file
DARTABASE DATA TYPES #
dartabase_migration types are Specified in capitals.
on the left hand you see the dartabase_migration data type name on the right the data type your database will use
MYSQL #
{
"BINT": "BIGINT",
"BINT UNSIGNED": "BIGINT UNSIGNED",
"BINARY": "BINARY",
"BIT": "BIT",
"BLOB": "BLOB",
"BOOLEAN": "BOOLEAN",
"BYTEARRAY": "BLOB",
"CHAR": "CHAR(255)",
"DATE": "DATE",
"DATETIME": "DATETIME",
"DOUBLE": "DOUBLE",
"FLOAT": "FLOAT(2)",
"FLOAT UNSIGNED": "FLOAT(2) UNSIGNED",
"INT": "INT",
"INT": "INT",
"INT UNSIGNED": "INT UNSIGNED",
"LBLOB": "LONGBLOB",
"LTEXT": "LONGTEXT",
"MBLOB": "MEDIUMBLOB",
"MINT": "MEDIUMINT",
"MINT UNSIGNED": "MEDIUMINT UNSIGNED",
"MTEXT": "MEDIUMTEXT",
"SINT": "SMALLINT",
"SINT UNSIGNED": "SMALLINT UNSIGNED",
"TEXT": "TEXT",
"TIME": "TIME",
"TIMESTAMP": "TIMESTAMP",
"TBLOB": "TINYBLOB",
"TINT": "TINYINT",
"TINT UNSIGNED": "TINYINT UNSIGNED",
"TTEXT": "TINYTEXT",
"VARBINARY": "VARBINARY(255)",
"VARCHAR": "VARCHAR(255)"
}
PGSQL #
{
"BINT": "bigint",
"BINT UNSIGNED": "numeric(20)",
"BINARY": "bytea",
"BIT": "bytea",
"BLOB": "bytea",
"BOOLEAN": "boolean",
"BYTEARRAY": "bytea",
"CHAR": "char(255)",
"DATE": "date",
"DATETIME": "timestamp",
"DOUBLE": "double precision",
"FLOAT": "real",
"FLOAT UNSIGNED": "real",
"INT": "integer",
"INT UNSIGNED": "bigint",
"LBLOB": "bytea",
"LTEXT": "text",
"MBLOB": "bytea",
"MINT": "integer",
"MINT UNSIGNED": "integer",
"MTEXT": "text",
"SINT": "smallint",
"SINT UNSIGNED": "integer",
"TEXT": "text",
"TIME": "time",
"TIMESTAMP": "timestamp",
"TBLOB": "bytea",
"TINT": "smallint",
"TINT UNSIGNED": "smallint",
"TTEXT": "text",
"VARBINARY": "bytea",
"VARCHAR": "varchar(255)"
}
Now you can add migration files for simple database manipulation
TODO #
*fix async outputtext
*workarround for database problems with reserved words
on creation or when switching DBAdapter from PG to MY.
eg. table name 'user' will break in MySQL
fix -> add '_' as prefix to all column and table names
*test on other systems
*adding rename action
*adding option to specify variable length
currently VARCHAR fix at 255
*test functionality of all data types
*improvements, adapt more functionality from db connectors
*and much more
Please let me know about bugs you find and or improvements/features you would like to see in future.
ENJOY & BE NICE ;)
Metadata
0
likes
0
pub points
0%
popularity
Publisher
unverified uploader
Metadata
Serverside Database migration for easy database structure manipulation inspired by Ruby on Rails migrations for mysql and postgresql
Homepage
Repository (GitHub)
View/report issues
License
unknown (license)
Dependencies
dartabase_core, postgresql, sqljocky
