Database migrations for PHP ala ActiveRecord Migrations with support for MySQL, Postgres, SQLite

Overview

Introduction

Ruckusing is a framework written in PHP5 for generating and managing a set of "database migrations". Database migrations are declarative files which represent the state of a DB (its tables, columns, indexes, etc) at a particular state of time. By using database migrations, multiple developers can work on the same application and be guaranteed that the application is in a consistent state across all remote developer machines.

The idea of the framework was borrowed from the migration system built into Ruby on Rails. Any one who is familiar with Migrations in RoR will be immediately at home.

Build Status

Getting Started & Documentation

See the Wiki for the complete documentation on the migration methods supported and how to get started.

Databases Supported

  • Postgres
  • MySQL
  • Sqlite

Features

  • Portability: the migration files, which describe the tables, columns, indexes, etc to be created are themselves written in pure PHP5 which is then translated to the appropriate SQL at run-time. This allows one to transparently support any RDBMS with a single set of migration files (assuming there is an adapter for it, see below).

  • "rake" like support for basic tasks. The framework has a concept of "tasks" (in fact the primary focus of the framework, migrations, is just a plain task) which are just basic PHP5 classes which implement an interface. Tasks can be freely written and as long as they adhere to a specific naming convention and implement a specific interface, the framework will automatically register them and allow them to be executed.

  • The ability to go UP or DOWN to a specific migration state.

  • Code generator for generating skeleton migration files.

  • Support for module based migration directories where migrations files could be generated/run from specified module directories.

  • Out-of-the-box support for basic tasks like initializing the DB schema info table (db:setup), asking for the current version (db:version) and dumping the current schema (db:schema).

Limitations

  • PHP 5.2+ is a hard requirement. The framework employes extensive use of object-oriented features of PHP5. There are no plans to make the framework backwards compatible.

Configuration

  • Copy /path/to/ruckusing-migrations/config/database.inc.php to /path/to/mycodebase/ruckusing.conf.php and update the development key with your DB credentials:

type is one of pgsql, mysql, sqlite depending on your database, as well migrations_dir, db_dir, log_dir, ruckusing_base paths.

  • If you want to use module migration directories, Edit /path/to/mycodebase/ruckusing.conf.php and update migrations_dir like array('default' => '/default/path', 'module_name' => '/module/migration/path') paths.

  • Copy /path/to/ruckusing-migrations/ruckus.php to /path/to/mycodebase/ruckus.php.

Custom Tasks

All tasks in lib/Task are enabled by default. If you would like to implement custom tasks then you can specify the directory of your tasks in your over-ridden ruckusing.conf.php in the tasks_dir key:

# ruckusing.conf.php

return array(
 /* ... snip ... */,
 'tasks_dir' => RUCKUSING_WORKING_BASE . '/custom_tasks'
);

Generating Skeleton Migration files

From the top-level of your code base, run:

$ php ruckus.php db:generate create_users_table

Created OK
Created migration: 20121112163653_CreateUsersTable.php

Module migration directory example:

$ php ruckus.php db:generate create_items_table module=module_name

Created OK
Created migration: 20121112163653_CreateItemsTable.php

The generated file is in the migrations directory. Open up that file and you'll see it looks like:

class CreateUsersTable extends Ruckusing_Migration_Base {

	public function up() {

	}//up()

	public function down() {

	}//down()
}

All of the methods below are to be implemented in the up() and down() methods.

Environments

You can switch environments via the ENV command line argument. The default environtment is development.

To specify an additional environment add it to ruckusing.conf.php under the db key.

Running with a different environment:

$ ENV=test php db:migrate

Running Migrations

Run all pending migrations:

$ php ruckus.php db:migrate

Rollback the most recent migration:

$ php ruckus.php db:migrate VERSION=-1

Rollback to a specific migration (specify the timestamp in the filename of the migration to rollback to):

$ php ruckus.php db:migrate VERSION=20121114001742

Overview of the migration methods available

The available methods are (brief list below, with detailed usageg further down):

Database-level operations

  • create_database
  • drop_database

Table-level operations

  • create_table
  • drop_table
  • rename_table

Column-level operations

  • add_column
  • remove_column
  • rename_column
  • change_column
  • add_timestamps

Index-level operations

  • add_index
  • remove_index

Query execution

  • execute
  • select_one
  • select_all

Database-level Operations

There are two database-level operations, create_database and drop_database. Migrations that manipulate databases on this high of a level are used rarely.

Creating a new Database

This command is slightly useless since normally you would be running your migrations against an existing database (created and setup with whatever your traditional RDMBS creation methods are). However, if you wanted to create another database from a migration, this method is available:

Method Call: create_database

Parameters name : Name of the new database

Example:

    $this->create_database("my_project");

Removing a database

To completely remove a database and all of its tables (and data!).

Method Call: drop_database

Parameters name : Name of the existing database

Example:

    $this->drop_database("my_project");

This method is probably the most complex of all methods, but also one of the most widely used.

Method Call: create_table

Parameters

name : Name of the new table

options : (Optional) An associative array of options for creating the new table.

Supported option key/value pairs are:

id : Boolean - whether or not the framework should automatically generate a primary key. For MySQL the column will be called id and be of type integer with auto-incrementing.

options : A string representing finalization parameters that will be passed verbatim to the tail of the create table command. Often this is used to specify the storage engine for MySQL, e.g. 'options' => 'Engine=InnoDB'

Assumptions Ultimately this method delegates to the appropriate RDMBS adapter and the MySQL adapter makes some important assumptions about the structure of the table.

Table-level operations

The database migration framework offers a rich facility for creating, removing and renaming tables.

Creating tables

A call to $this->create_table(...) actually returns a TableDefinition object. This method of the framework is one of the very few which actually returns a result that you must interact with (as and end user).

The steps for creating a new table are:

  • Create the table with a name and any optional options and store the return value for later use:
    $users = $this->create_table("users");
  • Add columns to the table definition:
    $users->column("first_name", "string");
    $users->column("last_name", "string");
  • Call finish() to actually create the table with the definition and its columns:
    $users->finish();

By default, the table type will be what your database defaults too. To specify a different table type (e.g. InnoDB), pass a key of options into the $options array, e.g.

Example A: Create a new InnoDB table called users.

    $this->create_table('users', array('options' => 'Engine=InnoDB'));
  • This command also assumes that you want an id column. This column does not need to be specified, it will be auto-generated, unless explicitly told not to via the id key in $options array.

Example B: Create a new table called users but do not automatically make a primary key.

    $this->create_table('users', array('id' => false));

The primary key column will be created with attributes of int(11) unsigned auto_increment.

Example C: To specify your own primary key called 'guid':

    $t = $this->create_table('users', array('id' => false, 'options' => 'Engine=InnoDB'));
    $t->column('guid', 'string', array('primary_key' => true, 'limit' => 64));
    $t->finish();

Removing tables

Tables can be removed by using the drop_table method call. As might be expected, removing a table also removes all of its columns and any indexes.

Method Call: drop_table

Arguments:: table_name: The name of the table to remove.

Example:

   $this->drop_table("users");

Renaming tables

Tables can be renamed using the rename_table method.

Method Call: rename_table

Arguments:: table_name: The existing name of the table. new_name: The new name of the table.

Example:

   // rename from "users" to "people"
   $this->rename_table("users", "people");

Column-level operations

Adding a new column to a table

For the complete documentation on adding new columns, please see Adding Columns

Removing Columns

Removing a database column is very simple, but keep in mind that any index associated with that column will also be removed.

Method call: remove_column

Arguments table_name: The name of the table from which the column will be removed.

column_name: The column to be removed.

Example A:: Remove the age column from the users table.

    $this->remove_column("users", "age");

Renaming a column

Database columns can be renamed (assuming the underlying RDMBS/adapter supports it).

Method call: rename_column

Arguments: table_name: The name of table from which the column is to be renamed.

column_name: The existing name of the column.

new_column_name: The new name of the column.

Example A: From the users table, rename first_name to fname

    $this->rename_column("users", "first_name", "fname");

Modifying an existing column

The type, defaults or NULL support for existing columns can be modified. If you want to just rename a column then use the rename_column method. This method takes a generalized type for the column's type and also an array of options which affects the column definition. For the available types and options, see the documentation on adding new columns, AddingColumns.

Method Call: change_column

Arguments: table_name: The name of the table from which the column will be altered.

column_name: The name of the column to change.

type: The desired generalized type of the column.

options: (Optional) An associative array of options for the column definition.

Example A: From the users table, change the length of the first_name column to 128.

    $this->change_column("users", "first_name", "string", array('limit' => 128) );

Add timestamps columns

We often need colunmns to timestamp the created at and updated at operations. This convenient method is here to easily generate them for you.

Method Call:add_timestamps

Arguments: table_name: The name of the table to which the columns will be added

created_name: The desired of the created at column, be default created_at

updated_name: The desired of the updated at column, be default updated_at

Exemple A: Add timestamps columns to users table.

    $this->add_timestamps("users");

Exemple B: Add timestamps columns to users table with created and updated column names.

    $this->add_timestamps("users", "created", "updated");

Index-level operations

Indexes can be created and removed using the framework methods.

Adding a new index

Method Call: add_index

Arguments: table: The name of the table to add the index to.

column: The column to create the index on. If this is a string, then it is presumed to be the name of the column, and the index will be a single-column index. If it is an array, then it is presumed to be a list of columns name and the index will then be a multi-column index, on the columns specified.

options: (Optional) An associative array of options to control the index generation. Keys / Value pairs:

unique: values: true or false. Whether or not create a unique index for this column. Defaults to false.

name : values: user defined. The name of the index. If not specified, a default name will be generated based on the table and column name.

Known Issues / Workarounds: MySQL is currently limited to 64 characters for identifier names. When add_index is used without specifying the name of the index, Ruckusing will generate a suitable name based on the table name and the column(s) being index. For example, if there is a users table and an index is being generated on the username column then the generated index name would be: idx_users_username . If one is attempting to add a multi-column index then its very possible that the generated name would be longer than MySQL's limit of 64 characters. In such situations Ruckusing will raise an error suggesting you use a custom index name via the name option parameter. See Example C.

Example A: Create an index on the email column in the users table.

    $this->add_index("users", "email");

Example B: Create a unqiue index on the ssn column in the users table.

    $this->add_index("users", "ssn", array('unique' => true)));

Example C: Create an index on the blog_id column in the posts table, but specify a specific name for the index.

    $this->add_index("posts", "blog_id", array('name' => 'index_on_blog_id'));

Example D: Create a multi-column index on the email and ssn columns in the users table.

    $this->add_index("users", array('email', 'ssn') );

Removing an index

Easy enough. If the index was created using the sibling to this method (add_index) then one would need to just specify the same arguments to that method (but calling remove_index).

Method Call: remove_index

Arguments: table_name: The name of the table to remove the index from.

column_name: The name of the column from which to remove the index from.

options: (Optional) An associative array of options to control the index removal process. Key / Value pairs: name : values: user defined. The name of the index to remove. If not specified, a default name will be generated based on the table and column name. If during the index creation process (using the add_index method) and a name is specified then you will need to do the same here and specify the same name. Otherwise, the default name that is generated will likely not match with the actual name of the index.

Example A: Remove the (single-column) index from the users table on the email column.

    $this->remove_index("users", "email");

Example B: Remove the (multi-column) index from the users table on the email and ssn columns.

    $this->remove_index("users", array("email", "ssn") );

Example C: Remove the (single-column) named index from the users table on the email column.

    $this->remove_index("users", "email", array('name' => "index_on_email_column") );

Query Execution

Arbitrary query execution is available via a set of methods.

Execute method

The execute() method is intended for queries which do not return any data, e.g. INSERT, UPDATE or DELETE.

Example A: Update all rows give some criteria

    $this->execute("UPDATE foo SET name = 'bar' WHERE .... ");

Queries that return results

For queries that return results, e.g. SELECT queries, then use either select_one or select_all depending on what you are returning.

Both of these methods return an associative array with each element of the array being itself another associative array of the column names and their values.

select_one() is intended for queries where you are expecting a single result set, and select_all() is intended for all other cases (where you might not necessarily know how many rows you will be getting).

NOTE: Since these methods take raw SQL queries as input, they might not necessarily be portable across all RDBMS.

Example A (select_one): Get the sum of of a column

    $result = $this->select_one("SELECT SUM(total_price) AS total_price FROM orders");
    if($result) {
     echo "Your revenue is: " . $result['total_price'];
    }

**Example B (select_all): **: Get all rows and iterate over each one, performing some operation:

    $result = $this->select_all("SELECT email, first_name, last_name FROM users WHERE created_at >= SUBDATE( NOW(), INTERVAL 7 DAY)");

    if($result) {
      echo "New customers: (" . count($result) . ")\n";
      foreach($result as $row) {
        printf("(%s) %s %s\n", $row['email'], $row['first_name'], $row['last_name']);
      }
    }

Testing

The unit tests require phpunit to be installed: http://www.phpunit.de/manual/current/en/installation.html

Running the complete test suite

$ vi config/database.inc.php
$ mysql -uroot -p < tests/test.sql
$ psql -Upostgres -f tests/test.sql
$ phpunit

Will run all test classes in tests/unit.

Running a single test file

$ vi config/database.inc.php
$ mysql -uroot -p < tests/test.sql
$ phpunit tests/unit/MySQLAdapterTest.php

Some of the tests require a mysql_test or pg_test database configuration to be defined. If this is required and its not satisfied than the test will complain appropriately.

Comments
  • Us of different migrations dirs

    Us of different migrations dirs

    Is there a possibility to add multiple directory's to read the migrations? I want this to set the migrations in the module (a git submodule) but run the migrations from the base project.

    opened by tvbeek 19
  • Allow custom configs for using per-project

    Allow custom configs for using per-project

    looks for ruckusing.conf in current directory - if found uses it, else uses the main file in ruckusing base directory. Also allows custom directory for migrations.

    Feel free to comment :)

    opened by tomasfejfar 12
  • Multiple Databases

    Multiple Databases

    How would you recommend working with multiple databases?

    I know the create_database() function will allow you to create a new database but not return an object to add tables to or such. I don't think any of the functions to alter or insert tables allow an argument to be passed that sets the database to perform the action.

    Is this a future plan or does this go against the primary goal? Is there a recommended way to configure ruckusing-migrations to easily allow for multiple dbs?

    opened by Jmayhak 11
  • Shared Development Model

    Shared Development Model

    Is their a recommended approach/best practice for shared development using this framework? I am not familiar with Ruby and the original DB migrations framework.

    If there are multiple developers in a project. Which parts of the ruckusing framework are shared in CVS or subversion? Let's assume one developer adds new functionality to an application and creates a migration using "php generate.php ...". Would the developer then commit these migrations to the CVS/SVN repository to be picked up by other developers? This is the direction I'm headed in but wanted get some feedback.

    Thanks, Stan

    opened by stansa 9
  • Migration via an offset does not work

    Migration via an offset does not work

    (more of a note to self).

    I'm attempting to migrate DOWN via an offset (really I just want to rollback the last executed migration) via:

    $ php ruckus.php db:migrate version=-1
    

    But it does not succeed and only prints out:

    Cannot migrate DOWN via offset "-1": not enough migrations exist to execute.
    You asked for (1) but only available are (0): 
    
    opened by ruckus 8
  • Create execute-multiple-queries

    Create execute-multiple-queries

    This fork adds multiple queries support when running $this->execute($query); in migration.

    With this, you can export queries from PHPMyAdmin or your favourite SQL Client and copy-paste it to ruckusing.

    Have fun ruckusing!

    opened by jaycode 7
  • Outdated adapter for mysql

    Outdated adapter for mysql

    Hi guys. I found that you use standard mysql adapter which called "mysql". It is a little bit awkward for the 2013 year =) I will replace it for "mysqli" (will do it in 2 hours) or "PDO" (10 hours). What do you prefer?

    That change will not affect requirements.

    opened by timtonk 7
  • extend tests via Travis CI + code-coverage via PhpUnit

    extend tests via Travis CI + code-coverage via PhpUnit

    • execute tests also with PHP 5.6, PHP 7.0, HHVM
    • code-coverage:upload for https://codecov.io, https://coveralls.io and https://scrutinizer-ci.com

    This change is Reviewable

    opened by voku 6
  • Rename main.php to ruckus.php

    Rename main.php to ruckus.php

    I think It's better to give main.php some identification. When deploying it with some other scripts, main.php doesn't tell immediately what this script is for. Renaming it to :

    1 - ruckus with the shebang #!/usr/bin/env php at the top of the script 2 - ruckus.php

    I think ruckus is the best but ruckus.php is also ok, at least it's better than main.php

    What do you think ?

    opened by salimane 6
  • Composer support

    Composer support

    Hello ;

    I want use that framework to manage database migrations on my project. I would recommend to add composer support for easy integration. It's possible ?

    opened by nuxwin 6
  • Multi execute

    Multi execute

    I have tried to refactor execute method to perform multiple queries correctly (https://github.com/ruckus/ruckusing-migrations/issues/154). I've removed split_query method and implemented multi_query method in each adapter instead.

    opened by silverslice 5
  • too many connections

    too many connections

    I have used this library for my product with is linked to multiple databases. Earlier it was working fine because we have less number of databases but recently it showed an error "too many connections" error. I have fixed this issue by writing a code to close the connection. it's working fine. This is just to inform you guys. Thanks for creating this library. It helps a lot.

    opened by harp-eng 0
  • Use PSR LoggerInterface instead of Ruckusing_Util_Logger.

    Use PSR LoggerInterface instead of Ruckusing_Util_Logger.

    The Ruckusing_Util_Logger is not PSR compatible ( see https://github.com/php-fig/fig-standards/blob/master/accepted/PSR-3-logger-interface.md ). It's log method should accept a log level as the first argument and the message as the second argument.

    Because the Ruckusing_Util_Logger is not PSR compatible it's impossible to reuse existing loggers that will in all likelihood be compatible with that standard.

    All methods that accept a Ruckusing_Util_Logger should also be typehinted with LoggerInterface instead so that projects may more easily reuse existing loggers.

    opened by herregroen 0
  • Roadmap: Integrate Percona Toolkit?

    Roadmap: Integrate Percona Toolkit?

    I've been using RM for a couple years now, and I've got tables in production with millions of rows. Adding a column these days is downright painful just because it is slow and I think it locks the table.

    Percona Toolkit (https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html) can do online table alterations with a command line interface, but I like the way RM keeps track of migrations.

    I wonder if there might be a way to hook the two up? I want to use RM for all migrations, but where there are super large tables, RM could use Percona?

    opened by ghost 1
  • Error:I dont know what column type of 'longtext' maps to for MySQL

    Error:I dont know what column type of 'longtext' maps to for MySQL

    Hi,

    When trying to change a column to longtext on MySQL, I get this error:

    Error:I dont know what column type of 'longtext' maps to for MySQL

    I thin longtext should map to longtext directly.

    opened by underdpt 0
  • CREATE INDEX CONCURRENTLY cannot run inside a transaction block

    CREATE INDEX CONCURRENTLY cannot run inside a transaction block

    Hi,

    I am using execute method and getting following error:

    Base.php(381) : pg_query(): Query failed: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

    My exact query is as following: $this->get_adapter()->execute_ddl("DROP INDEX IF EXISTS ix_events_type_cd;"); $this->get_adapter()->execute_ddl("CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_events_type_cd ON public.xtable USING btree (type_cd);");

    I am using postgres, Any would be appreciated.

    thanks, Jaffar

    opened by jaffarhussain1011 0
Owner
Cody Caughlan
Cody Caughlan
Framework agnostic database migrations for PHP.

Phoenix Framework agnostic database migrations for PHP. Features Validation all settings in migration before executing first query Multiple migration

Michal Lulco 148 Nov 19, 2022
Simple migrations system for php

Phpmig What is it? Phpmig is a (database) migration tool for php, that should be adaptable for use with most PHP 5.3+ projects. It's kind of like doct

Dave Marshall 561 Jan 1, 2023
php 5.3 Migration Manager

#What are Database Migrations? Migrations are a convenient way for you to alter your database in a structured and organized manner. You could edit fra

Lewis Dyer 38 Sep 28, 2022
Pico disk, Not need any database, support html5, support mp3, mp4, support streaming media, support AriaNg

Nano netdisk, Now changed to pico disk. Pico disk,does not need any database, support html5, support mp3, mp4, support streaming media, support AriaNg.

null 53 Dec 26, 2022
A simple blog app where a user can signup , login, like a post , delete a post , edit a post. The app is built using laravel , tailwind css and postgres

About Laravel Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experie

Nahom_zd 1 Mar 6, 2022
Laravel Migrations Generator: Automatically generate your migrations from an existing database schema.

Laravel Migrations Generator Generate Laravel Migrations from an existing database, including indexes and foreign keys! Upgrading to Laravel 5.4 Pleas

Bernhard Breytenbach 3.3k Dec 30, 2022
Laravel Migrations Generator: Automatically generate your migrations from an existing database schema.

Laravel Migrations Generator Generate Laravel Migrations from an existing database, including indexes and foreign keys! This package is cloned from ht

Kit Loong 1.4k Jan 1, 2023
A simple and extensible fixture loader for PHP 7.3+, supporting SQLite and MySQL

Flowder Flowder is a (really) simple fixture loader for PHP 7.3+, supporting SQLite and MySQL. Using Flowder in PHP 7.2 or below? Try version 1 instea

Joe Haines 6 Jan 17, 2021
Bolt is a simple CMS written in PHP. It is based on Silex and Symfony components, uses Twig and either SQLite, MySQL or PostgreSQL.

⚠️ Note - Not the latest version This is the repository for Bolt 3. Please know that Bolt 5 has been released. If you are starting a new project, plea

Bolt 4.1k Dec 27, 2022
Independent query builders for MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.

Aura.SqlQuery Provides query builders for MySQL, Postgres, SQLite, and Microsoft SQL Server. These builders are independent of any particular database

Aura for PHP 424 Dec 12, 2022
Minimalistic bookmark manager for your own server written in PHP. Bookmarks are stored in a sqlite database.

b - Bookmark manager b is a minimalistic bookmark manager for your own server. Written in PHP. Bookmarks are stored in a sqlite database. Features: fi

Sebastian Volland 48 Jan 6, 2023
temperature-pi: a simple Raspberry Pi based temperature logger using a DS18B20 1-Wire digital temperature sensor, & a local sqlite database

temperature-pi temperature-pi is a simple Raspberry Pi based temperature logger using a DS18B20 1-Wire digital temperature sensor, & a local sqlite da

Ronan Guilloux 23 Dec 27, 2020
Tarantool connector for yii2 framework. Allow to use activerecord, schemas, widgets and more.

Tarantool connector for yii2 framework Tarantool connector for yii2 framework. Allow to use framework abstractions such as ActiveRecord, Schema, Table

Andrey 11 Nov 21, 2021
Handles ActiveRecord's attribute translations

TranslateableBehavior for Yii2 This behavior has been inspired by the great work of Mikehaertl's Translatable Behavior for Yii 1.*. It eases the trans

2amigOS! Consulting Group 67 May 21, 2022
Experimental ActiveRecord layer on top of Doctrine2 using the Twig templating engine

This is an experiment for building ActiveRecord functionality on top of Doctrine2 using the Twig templating engine. Whether it is called Propel2 or not is irrelevant.

Francois Zaninotto 85 Dec 5, 2022
FlatsApp using Laravel 8 with SQLite

The FlatsApp This Application Stacks using Laravel 8 with MySQL. Installation To get up and running of this Application please make sure you already h

ivandjoh 4 Nov 17, 2022
URL - link shortener based on sqlite

link-url-shortener url - link shortener based on sqlite.

Okin On 1 Nov 12, 2021
An extension that enables the SQLite DB

SQLite on MediaWiki This extension is based on a WIP in the WMF’s gerrit. How to use it Put the following in your composer.local.json: { "require": {

MediaWiki Stakeholders 0 Jul 15, 2019
Feather - a highly performant SQLite Cache Driver for Kirby 3

?? Kirby3 SQLite Cache-Driver Feather - a highly performant SQLite Cache Driver for Kirby 3 Commerical Usage Support open source! This plugin is free

Bruno Meilick 1 Dec 15, 2021
A PHP MySQL database client class to simplify database access

This lightweight database class is written with PHP and uses the MySQLi extension, it uses prepared statements to properly secure your queries, no need to worry about SQL injection attacks.

Khader Handal 50 Jul 30, 2022