ruckus / ruckusing-migrations Goto Github PK

I need to be able to run a set of migrations against databases with different names.

Eg I have 2 configurations dev and prod which have database names db_dev and db_prod

during development I generate a few migrations which end up in the folder ./migrations/db_dev

If I then run my alternative environment it will not run these because the database name is now db_prod. This occurs because of the function:

    public function migrations_directory()
    {
        return $this->_config['migrations_dir'] . DIRECTORY_SEPARATOR . $this->_config['db'][$this->_env]['database'];
    }

It would be a disappointing limitation if all of your databases had to have the same name in order for this to work.

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

The rails migration system keeps the latest version of the schema in a schema.rb file
(see http://guides.rubyonrails.org/migrations.html#schema-dumping-and-you )

This allows:

• An up to date schema reference to be kept in source control
• The rake task "db:setup" which allows installing a new instance without running all the migrations in turn
• The rake task "db:reset" for integration testing

Hi,

I would like to know how to test the library currently.

Thanks

I think very good feature will be possibility to generate all migration queries and save for example in file, for example:

php rucus.php db:get_queries > file.sql

Why? I my case I develop some application at linux, but in hosting server I dont have shell and I must run all queries from phpMyAdmin, so when I have file with queries everything will be easier.

Now you need to use
php main.php db:migrate VERSION=20110716134657
I'd suggest having also the option to use
php main.php db:migrate DOWN=N
that would migrate N revisions DOWN. The problem with current solution is that you need to get the propper timestamp from the schema_migrations table to insert into the CLI, which is really annoying when not connected to the DB.

It can be implemented fairly easily - you can do sth like:
SELECT version FROM schema_migrations ORDER BY version DESC LIMIT N,1
that will select N-th oldest version. That would simplify thing a lot. Unfortunately it won't work this way for UP migrations as they are not in the table (but could be done using ordered filesystem iteration). But usually you don't need to migrate "some" revisions up, but mainly to the last revision. But migrating down the last revision has a strong use case in correcting mistakes ;)

As with my other issues I'll try to fix it myself later (when I finish the project I'm using RU for = september)... if you would not fix that first :)

I'd like to get anyone's general ideas about the feasibility of having merging capabliities built in. For example:

Dev Branch:
Adds migration 1 and 2; commits.

Topic Branch:
Checkout dev branch, up to date with 1 and 2.
Adds migration file 3, does not yet merge back to dev.

Dev Branch:
Adds migration file 4. Commits.

Topic Branch:
Finished work, merges to dev branch.

Develoer in dev branch now has 1, 2, 3, and 4, but since 4 has already been run prior to getting migration file 3, file 3 never runs.

Ideally, it might be nice to be able to see a diff of all migrations that have not run yet, since they will be sprinkled in various places of your migrations folder, and run them either all once or specifically (ex: db::migrate apply=12121220000)

is there any coding standard that you prefer to use? I'm currently integrating ruckusing in our workflow but find there are some features missing that i would like to implement. Then i took a look at the code and saw a mixture of tab- and space indentation, etc ...

I would like to settle on one thing before i start filing pull requests with my updates. Tabs or spaces for indentation. I'm for spaces following the Zend Framework coding standard.

As far as I can see, without using execute, there is no support for foreign keys.

ErrorHandler (Ruckusing_Exception.php:79) cannot call exit(1) for warnings.

E.g. when pg_query fails not only it sets error code but also it generates a warning.
Currently errorHandler calls exit(1) on warnings and db errors are not handled properly (e.g. migration that failed is not shown).

(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): 

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 ?

With the rapid development going on in ruckusing-migrations at the moment it would be useful to start tagging releases as per http://semver.org/. This way the folks using Composer can more reliably lock themselves to a release instead of running on the dev-master branch, which often has backwards-incompatible changes.

Here are a couple more posts that go into a bit more detail about why proper version tagging is important for Composer packages:

• http://sf.khepin.com/2013/01/version-tagging-strategies-for-composer/
• https://igor.io/2013/01/07/composer-versioning.html

In addition to this, a CHANGELOG.md document would be handy - especially if it documented how to upgrade between different tagged versions.

It would be really nice if I could specify a .sql file as a starting point for deployment. for example If I want to move an existing site over to ruckusing I would like to be able to do a database dump and then do migrations off of that. is there a way to do this?

Please see http://stackoverflow.com/questions/115983/how-do-i-add-an-empty-directory-to-a-git-repository for adding empty directories to git repositories.

Ruckusing should add support for table prefixes which can be configured in database.inc.php and will be applied automatically to every table name. This makes it easier to run migrations on hosts that force table prefixes or in cases where variable prefixes are necessary.

Greetings,

I couldn't find anything in wiki on creating an adaptor for the Postgres DB. Any pointers on how I could go about doing this?

Thanks,
Stan

Currently the execute function splits the query string given by semi colons, see: https://github.com/ruckus/ruckusing-migrations/blob/master/lib/Ruckusing/Adapter/MySQL/Base.php#L325

But that doesn't take into consideration semicolons inside of quotes that should not be treated this way.
E.g.

insert into a(id,name) values(1,'test'); insert into b(id,name) values(1,'another test');

That should work this way, however:

insert into a(id,name) values(1,'test;test')

should not be split!
This should apply to semicolons inside single and double quotes.

On the page with migration methods there is nothing to be found on manipulating columns. The section titled "Adding Columns" contains table-manipulation documentation?

https://github.com/ruckus/ruckusing-migrations/wiki/Migration-Methods

ruckus.php does not currently print any output until all migrations have finished.

If one or more migrations take a very long time, then this can be disconcerting. It is not clear whether ruckus.php has started or not.

It would be better if a "starting migration x" message were printed before each migration started.

(Tested on version d8b1e737 (current master) on Windows with PHP 5.4.13 + Cygwin shell)

I'm not sure what was the motivation of using multiple files - the migration file generation can as well be turned into a task, couldn't it? Why it's in it's own file (mirroring the bootstrap code).

I thought that the common practice is that the ID is the first column.

Is the github service hook for packagist setup?

https://packagist.org/about

Packagist isn't detecting version 0.1.1 yet.

I would like the up and down methods to be wrapped in SQL transactions, if the backend supports it (and I believe they all do).

I was surprised to see that a table was left in my database after an execute statement errored out.

Exception handlers set no exit code, which causes problems when migrations are called from bash scripts.

Fix:

Index: ruckusing-migrations/lib/Ruckusing/Exception.php
===================================================================
--- ruckusing-migrations/lib/Ruckusing/Exception.php    (revision 6344)
+++ ruckusing-migrations/lib/Ruckusing/Exception.php    (working copy)
@@ -79,6 +79,7 @@
     public static function errorHandler($code, $message, $file, $line)
     {
         file_put_contents('php://stderr', "\n" . basename($file) . "({$line}) : {$message}\n\n");
+        die(1);
     }

     /**
@@ -89,6 +90,7 @@
     public static function exceptionHandler($exception)
     {
         file_put_contents('php://stderr', "\n" . basename($exception->getFile()) . "({$exception->getLine()}) : {$exception->getMessage()}\n\n");
+        die(1);
     }

 }

E.g. PDO or other systems could be quickly added this way.

with two files to be migrated like (generated with generate.php):

20101001002834_UpdateTableTransport.php
20101209002840_UpdateTableTransport.php

The following command breaks a previously set 'NOT NULL' restriction on a MySQL database:

$this->rename_column("<table>", "<old_column_name>", "<new_column_name>");

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 ?

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.

I can't seem to figure out how to create a field of type 'longblob' without running a query after creating the table

The .gitignore file in this project includes db/migrate/*.php as ignore directive. Since this package is not available as a packaged download anymore, this directive is likely to stay in production when downloading this repo. When using git as an SCM, this means any created migrations in the default configuration are silently not checked in, something that may easily go overlooked (well, it did here... D-:).

The limit option is ignored in the latest master version (6ab9673):

$t = $this->create_table('test');
$t->column('nolimit', 'string');
$t->column('limit', 'string', array('limit' => 50));
$t->finish();

Result:

CREATE TABLE `test` (
  `nolimit` varchar(255) DEFAULT NULL,
  `limit` varchar(255) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

BIGINT currently fails as a datatype

I am trying to create a database where indexes are associated and deletes/updates cascade, The only way I can figure out how to do this is to use a query:
$this->execute('
ALTER TABLE events
ADD FOREIGN KEY ( restaurant_id )
REFERENCES restaurants (id)
ON DELETE CASCADE
ON UPDATE CASCADE
');

It would be nice if there was a cleaner way of doing this.

I am getting the following error when I run:

php main.php db:setup

(class.Ruckusing_DB_Setup.php:26) Undefined property: Ruckusing_DB_Setup::$adapter

So I'm easily able to go up with the migrations on Capistrano deploy.
I would like to implement going down on Capistrano rollback, how would you go about that?

Something like this, or is there any easier way?

On deploy, read from the db and save the belonging db version number in a file in the deployed directory.
Then on rollback, read from the file and take the db back to the right version.

schema_info could have 'applied_at' column of type timestamp.

If you maintain multiple databases it would be nice to know when exactly migrations were applied.

I think having a working autocompletion for the methods provided by ruckusing-migrations. This partially works until the a create_table method call on the $insertYouVarNameHere variable.

Would let users use Ruckusing to setup temporary databases in SQLite ( really helpful when doing DB testing in PHPUnit, for example )

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?

this function not working like it should under windows server.
little fix done by me:

$file_name = str_replace('\\', '/', $file_name);
//we could be given either a string or an absolute path
//deal with it appropriately
$parts = explode('/', $file_name);

It would be nice to have a possibility to define a custom task dir in database.inc.php (just like migrations dir).
Currently, if I use composer I cannot define custom tasks.

I need to do something like this:

alter table tablename add unique index(columnname, id2);

but I can't seem to figure out how to do it without using an execute..

maybe something like this?

$this->add_index("customers", "columnname, id2", array("unique" => true));

I don't understand why this is appending the database name to the migrations module path?

https://github.com/ruckus/ruckusing-migrations/blob/master/lib/Ruckusing/FrameworkRunner.php#L206

The migrations we use shouldn't be different per database. I want to write migrations for a module I'm using, and for those migrations to work across different databases.

Currently:

my_module/migrations/project_databasename/*.php
my_module/migrations/anotherproject_databasename/*.php

The migrations for these are identical, but currently duplicated.

Instead of:

my_module/migrations/*.php

Is this necessary? If not, can I propose we remove it? I'm happy to submit a PR.

Though its referenced here:

Ruckus treats all dirs under "Adapter" as db adapters.
It doesn't work for svn 1.6, which adds .svn folders.

Error log:
FrameworkRunner.php(454) : require_once(ruckusing-migrations/lib/Ruckusing/Adapter/.svn/Base.php): failed to open stream: No such file or directory

Currently I'm in a situation where I've forked our master (git) and have been working on a new feature for a month. So far I have 44 changes to the database (ruckusing migrations), but in the same period of time master have "moved" ahead with about 20 of its own changes to the database.

So far I've rebased my fork on top of master and until now gotten all database changes in my development environment because of that. But when my feature is ready to go into production and I merge back into master there is no guarantee my migrations files will be applied in production.

Migration files - new feature (NF)
1 NF
2 NF
5 NF

Migration files - master
3 master
4 master - currently deployed in production

Migration files - master merged with NF
1 NF
2 NF
3 master
4 master - currently deployed in production
5 NF

In the above example when we update our production system migrations files 1 and 2 won't be applied because 3 and 4 is already applied. Do you have a recommendation for this scenario, if not I would like to request support for this (working in parallel on different branches which is merged back together).

Right now the only solution I can come up with is to rename all my 44 migration files to something that is newer than the current deployed version in production.

The wiki currently lacks a table describing which types maps to which data type for Sqlite.

See Adding Columns in the wiki.

Is Ruckusing able to generate a full text search index like below?

ALTER TABLE test ADD FULLTEXT(title, article);