dartabase_migration 0.6.1

Database Migration stands for version controlled database structure manipulation for MySQL and PGSQL without having to write SQL. inspired by Ruby on Rails models

Dartabase Migration 0.6.1

Serverside Database migration for simple version controlled database structure manipulation using MySQL/PGSQL without having to write SQL

combine the power of Dartabase Model and Dartabase Migration

!!now supporting scaffolding!! see bottom of the page

inspired by Ruby on Rails migrations

Tested on

Dart Editor version 1.4.0.dev_04_00 (DEV)
Dart SDK version 1.4.0-dev.4.0

Compatibility

depending on the migration version you are using 
you have to use a differend model version in your app
  
  migration           model
  -------------------------
  0.6.x <-requires->  0.6.x
  0.5.x <-requires->  0.5.x

Uses

MYSQL via sqljocky version 0.9.0

PGSQL via postgresql version 0.2.12

TUTORIAL 1 HOW TO SETUP AND RUN MIGRATION AND MODEL

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
  yourProject/db/
  yourProject/db/config.json          
    -dartabase config file to connect IP/PORT/DBType
  yourProject/db/schema.json          
    -current dartabase_migration structure as JSON used by Dartabase tools
  yourProject/db/schemaVersion.json   
    -safes name of latest migrated version
  yourProject/db/migrations           
    -folder for your database migration files

5.Edit the -yourProject/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

1.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.5.0 (breakting change for some)

This applies ONLY 
if a "table_name" you specified in your migration files actions
starts with a capital letter

eg. like this

"createTable": {
  "Account": {
    "name": "INT"
  }
}

to fix this

1.Change all "table_names" inside your migrations to lower case

eg. like this

"createTable": {
  "account": {
    "name": "INT"
  }
}          
    
and 
2.Open schema.json and replace all capital table names to lower case aswell

eg from

{"Gamechar":{"id":{"type":"INT"}}}

to

{"gamechar":{"id":{"type":"INT"}}}

now Migration and Model should work like a charm again ;)

---

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

"$yourProject/db/migrations/YYYYMMTTHHMMSS_create_table_user"

or

1b.Create a migration json file "timestamp_action.json"

inside "$yourProject/db/migrations"

eg. "$yourProject/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 the keys/actions below inside the "UP" value

createTable

"createTable" key takes a json object as value

    keys    : non_existent_table_names (lower_case)
    values  : json object
                keys    : non_existent_column_names (lower_case)
                values  : DARTABASETYPE
                      or
                      json object
                      keys: column options 
                      values: column option values 

type only
"createTable": {
  "new_table_name_one": {
    "new_column_name": "DATATYPE"
    }
  }
  
    or with options          
    "createTable": {           
        "new_table_name_one": {        
            "new_column_name": {          
              'type':"DATATYPE",      
                'default':"1234"    
            }                
        }                
    }

createColumn

"createColumn" key takes a json object as value

    keys    : existing_table_names (lower_case)
    values  : json object
                keys    : non_existent_column_names (lower_case)
                values  : DARTABASETYPE
                      or
                      json object
                      keys: column options 
                      values: column option values 

type only
"createColumn": {
  "existing_table_name_one": {
    "new_column_name": "DATATYPE"
    }
  }
    
    or with options
    "createColumn": {          
        "existing_table_name_one": {    
            "new_column_name": {         
              'type':"DATATYPE"      
            }              
        }
    }

removeColumn

"removeColumn" key takes a json object as value

    keys    : existing_table_names (lower_case)
    values  : array[existing_column_names] (lower_case)

    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"] (lower_case)

createRelation

"createRelation" key takes an array of arrays with two existing table names as value

    value  : array[array[existing_table_name_one,existing_table_name_two]] 

    eg.
    "createRelation": [
      ["existing_table_name_one","existing_table_name_two"]
  ]

removeRelation

"removeRelation" key takes an array of arrays with two existing table names as value

    value  : array[array[existing_table_name_one,existing_table_name_two]] 

    eg.
    "removeRelation": [
      ["existing_table_name_one","existing_table_name_two"]
  ]

A simple migration could look like

----------20130709134700_create_table_user.json--------------
{
    "UP": {
        "createTable": {
            "account": {
                "name": "VARCHAR"
            },
            "picture": {
                "file_name": "VARCHAR"
            }
        },
        "createRelation": [
          ["picture","account"]
        ]
    },
    "DOWN": {
      "removeRelation": [
          ["picture","account"]
        ],
        "removeTable": ["account","picture"]
    }
}

createTable creates a table named
"account" with column "name" and datatype "variable length of characters" "picture" with column "name" and datatype "variable length of characters"

createRelation creates a table named "account_2_picture" with columns "account_id" and "picture_id"

---

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 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
   ->
   createRelation
    ->
    removeRelation
     ->
     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

-yourProject/db/schema.json
with the current database structure as JSON

-yourProject/db/schemaVersion.json
with the name of latest migrated migration file

HOW TO REVERT MIGRATIONS

1.Execute dartabase_migration/bin/dbDown.dart

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

-yourProject/db/schema.json
with the current database structure as JSON

-yourProject/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)"

}

---

SCAFFOLDING

HOW TO RUN SCAFFOLDING

Scaffolding is the programmers best friend!

run scaffolding and enter a table name, column names and their DARTABASE type, and scaffolding will generate most of the standart code needed to get startet developing your app

to generate the code we have to

1. run 'dartabase_migration/bin/scaffolding.dart'

2. Select the project we initiallized with dartabase_migration

let say we want to extend our (simple todo list)[https://github.com/HannesRammer/DartabaseTutorials/blob/master/tutorials/TUT1.md] example with a user

3. type 'todo' and hit enter

4. now type 'user_account[name:VARCHAR,active:BOOLEAN]-m-c-s' and hit enter

scaffold will generate something like

Migration (generated adding the -m )

-yourProject/db/migrations/20140512025616_create_user_account.json 

A migration file, that will create a table user with the specified 2 columns (and the dartabase autogenerated columns)

Server model (generated adding the -s )

-yourProject/bin/userAccount.dart

The dart representation of its database table 
with simple functions for 

loading a list of all entries
  
  -loadUserAccounts(HttpResponse res) 

viewing, editing and deleting a single entry

  -loadUserAccount(HttpResponse res,objectId)
  -editUserAccounts(HttpRequest req,HttpResponse res)
  -deleteUserAccounts(HttpRequest req,HttpResponse res) 

Client view (generated adding the -c )

-yourProject/web/userAccount/create.dart 
-yourProject/web/userAccount/create.html 
-yourProject/web/userAccount/edit.dart 
-yourProject/web/userAccount/edit.html 
-yourProject/web/userAccount/view.dart 
-yourProject/web/userAccount/view.html 
-yourProject/web/userAccount/index.dart 
-yourProject/web/userAccount/index.html 

simple html views to display and manipulate the data from the database 

**Poly view** (generated adding the -c )

-yourProject/web/poly/userAccount.dart 
-yourProject/web/poly/userAccount.html

the client views all make use of its custom polymer elements 

paths (generated adding the -c )

-yourProject/lib/paths.dart 

since the client views, have links that connect the pages with each other,
it is usefull to handle the paths in a seperate file and use vars instead of strings, 
so in case we want to change the path, we only need to change it in one place. 

HOW TO USE SCAFFOLSING

1. Migrate the new migration into the database follow the steps from HOW TO RUN MIGRATIONS

2. If not already inside your dart server add the line

import '../lib/paths.dart'; (you have to add "library paths;" at the top of the file if it didnt exisct before)

3. Add the new object model to the dart server

part 'userAccount.dart';

4. Add the following code inside the dart simpleServer

inside handleGet add

[else] if(path == "/$userAccountsLoadUrl"){
  UserAccount.loadUserAccounts(res);
}else if(path.contains("/$userAccountLoadUrl")){
  String id= path.split("/$userAccountLoadUrl/")[1];
  UserAccount.loadUserAccount(res, id);
}

inside handlePost

[else] if(path == "/$userAccountSaveUrl"){
  UserAccount.saveUserAccount(req, res);
} else if(path == "/$userAccountDeleteUrl"){
  UserAccount.deleteUserAccount(req, res);
}

only add the first else if there is already an if statement .. obviously ;)

5. now you only need to add a link like below inside your client views

   Element linkToUserAccounts = new Element.tag('div'); linkToUserAccounts.onClick.listen((e) => window.location.assign(userAccountsUrl)); content.append(linkToUserAccounts);

6. run the dart server, run the client! Enjoy!

---

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