dartabase_migration 1.1.3

logo Dartabase Migration GUI GUIDE

Serverside Database migration for simple version controlled database structure manipulation using MySQL/SQLite/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

Tested on

Dart SDK version 1.16.0

Uses Polymer version "^1.0.0-rc.16" MYSQL via sqljocky "^0.14.1"

PGSQL via postgresql version "^0.3.3"


###WIKI

1.How to setup

2.How to update existing dartabase migration version

3.How to create migrations

4.How to run migrations

5.How to revert migrations

6.How to generate backend code from database

7.How to run generated backend code

About column id

About create updated at column

About up and down

About order of execution

About dartabase data types


####Dartabase Documentation

1.Dartabase Migration - How to install and use - current

2.Dartabase Model - How to how to install and use

####Dartabase Tutorials

1.How to create a Dartabase supported app from scratch

2.How to create a Dartabase supported app for an existing database


1. HOW TO SETUP <a name="how-to-setup"></a>

USE THIS INSTALL GUIDE AND IGNORE THE INSTALL PAGE!!! This is a stand alone app!

1.Download dartabase_migration somewhere to your drive

2.run 'Pub Get' on dartabase_migration/pubspec.yaml

3.Execute dartabase_migration/bin/simpleServer.dart

4.Execute dartabase_migration/tool/index.html which will open in chromeium

now you should see page with a button

one

5.click on add project, now you should see

one

6.fill in the form and click enhance or create

one

7.after reload you should see the created object

one

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

9.IMPORTANT if your config data is not correct it might be that dartabase will stop running

in this case make sure to insert the correct data into the config.json file created in your project -yourProject/db/config.json file so dartabase_migration can connect to your existing database. and rerun simpleServer.dart and index.html

eg.
--------config.json---------
{
    "adapter": "MySQL",
    "database": "dbName",
    "username": "dbUsername",
    "password": "dbPassword",
    "host": "localhost",
    "port": "3306",
    "ssl": "false"
}
----------------------------


2. HOW TO UPDATE EXISTING DARTABASE MIGRATION VERSION <a name="how-to-update-dartabase-migration"></a>

GENEREL UPDATE

it is important to keep a backup of 'dartabase_migration/bin/projectsMapping.json'

1.download the new version of dartabase migration 

2a.replace all files from your current running version 
  (but not projectsMapping.json)
2b. extract all files into a new folder and paste a copy of bin/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


3. HOW TO CREATE MIGRATIONS <a name="how-to-create-migrations"></a>

  1. select the project you want to create a migration for

one

  1. open the "create migration" view, enter a migration name and click on the action you want to execute

one

createTable

one

enter a table name and additional columns if needed like seen below

one

createColumn

one

select an existing table name and add additional columns like seen below

one removeColumn

one

select an existing table and the existing column you want to remove, like below

one

removeTable

one

select an existing table you want to remove, like below

one

createRelation

one

select two existing tables to create a relation between them like below

one

removeRelation

one

select two existing tables to create a relation between them like below

one

click on "create migration"

it will create a migration inside

"$yourProject/db/migrations/YYYYMMTTHHMMSS_migration_name.json"

if everything works it will show a text mesage inside a toast that asks you to reload the page

one


4. HOW TO RUN MIGRATIONS <a name="how-to-run-migrations"></a>

1 . open the run migration view

one

2 . select the preferred migration that is newer than the one marked as current. in this case version 1

one

3 . now click on "migrate to newer version".

one

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

5. HOW TO REVERT MIGRATIONS <a name="how-to-revert-migrations"></a>

1 . open the run migration view

one

2 . select the preferred migration that is older than the one marked as current. in this case version 1

one

3 . now click on "migrate to older version".

one

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

6. HOW TO generate backend code from database <a name="how-to-generate-backend-code-from-db"></a>

  1. open the "create Scaffold" view in your project.one

  2. click "generate all at once"the newest feature of Dartabase is to generate fa the whole backend/admin webapp

red: by us created migrations

orange: database relation models in dart (scaffold)

blue: server files, serverfunctions, rouiting (scaffold)

pink: database config and current schema structure

green: frontend for the backend (scaffold)

one


7. HOW TO run generate backend code <a name="how-to-run-generate-backend-code"></a>

  1. make sure you add the entry point "web/db/index.html" for the backend/admin appone

  2. start "dart_demo\db\server\simple_server.dart" followd by "dart_demo\web\db\index.html"one

3a. the resulting app allows to display edit and delete all database entries one

since we have not stored anything in our database yet the boxes are empty

another example with data could look like this 3b. one


ABOUT COLUMN ID <a name="about-column-id"></a>

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


ABOUT CREATED/UPDATED AT COLUMN <a name="about-create-updated-at-column"></a>

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 
     

ABOUT UP AND DOWN <a name="about-up-and-down"></a>

Additionally to the "UP" key, migration automatically generates the opposite migration for reverting 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!!!

ABOUT ORDER OF EXECUTION <a name="about-order-of-execution"></a>

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

its always best to keep migration files as simple as possible and therefore create more migration files


ABOUT DARTABASE DATA TYPES <a name="about-dartabase-data-types"></a>

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

}


###TODO

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

VERSION

1.1.3 -fixed CRUD scaffold generator issue
        --create update delete client and server now working 
        --extract correct library name on linux systems 
      -fix missing close button in project view
      -fixed color mixeup
      -update core to 0.5.2
1.1.0 -added CRUD backend scaffolding - generate dart app from existing database with one click - see doc 
        --generate dbSchema from existing db
        --generate dart models representing existing db tables and columns
        --generate CRUD dart views for existing db tables and columns
        --generate simple dart server + CRUD functionality for all Tables  
      -update paper toast show call
      -added automatic backup of generated files
      -update core to 0.5.1
1.0.0 -created polymer 1.0 GUI on top of the console functions
        --simplified handling of dartabase enhanced projects
        --fail-safe way to create and manage migrations over multiple projects at once 
      -update core to 0.4.0
0.6.3 -added ssl support for Mysql and PGSQL via config file
      -improved speed via single connection instead of multi pools
      -updated postgresql to 0.2.13 
      -update scaffold using params 0.0.2 package
0.6.2 -updated sqlJocky to 0.11.0
      -fixed problem due to generation of table column created_at using now().
       should work in mysql version >= 5.0 //tested on producktion 5.0 and 5.5
       //pls file me a bug, if you still have problems creating tables via migration.
      -scaffolg now generates bin and a lib folder if not exists
      -fixed some resource issues on load  
0.6.1 -added link to tutorial in readme.md  
0.6.0 -required Model 0.6.x
      -added scaffolding for autogenerated dart/html code
       see "SCAFFOLDING"  
0.5.4 -added logo in md format
      -moved versions into changelog.md
0.5.3 -fixed "stream.listen" problem when calling dbUp.dart and dbDown.dart
       problem seems to have appeared with Dart 1.4 changes// note to me CHECK WHY!! listen var on init seems to be string path to dart.exe
0.5.2 -updated core dependency to 0.2.4
0.5.1 -fixed README.md
0.5.0 -added migration functions createRelation and removeRelation
       see createRelation and removeRelation
       see "Update to version 0.5.0" for possible breaking change 
0.4.3 -fixed crash when projectsMapping.json does not exist on dbInit.dart
0.4.2 -adapted column option to support old migration files
       migrations now support '"column" : "INT"' and '"column" : {"type":"INT"}'
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

1. Depend on it

Add this to your package's pubspec.yaml file:

dependencies:
  dartabase_migration: 

2. Install it

You can install packages from the command line:

$ pub get

Alternatively, your editor might support pub. Check the docs for your editor to learn more.

About

Database Migration is a version-controlled database structure manipulation for MySQL(sqljocky) and PGSQL(postgresql) without having to write SQL. now supports scaffolding(database2code) ORM CRUD inspired by Ruby on Rails!!

Author

Email Hannes.Rammer@gmail.com Hannes Rammer

Homepage

github.com/HannesRammer/Dartabase/tree/master/dartabase_migration

Documentation

www.dartdocs.org/documentation/dartabase_migration/1.1.3/

Source code (hyperlinked)

www.crossdart.info/p/dartabase_migration/1.1.3/

Uploader

Hannes.Rammer@googlemail.com
Hannes.Rammer@gmail.com

Share