Posted by Scott Laird
Thu, 05 Jan 2006 16:48:46 GMT
I just released a new version of my Rails Schema Generator;. There are two changes in this release:
- Migrations should finally work correctly using either old-style (
1_migrate_to_foo) or new-style (001_migrate_to_foo) numbering.
- SQL Server support has been added.
There’s also some support for Oracle hiding in the tree, but it’s disabled as I can’t see a way to get it to work without having all of the Oracle OCI libraries installed. So, if you’re using Oracle with Rails, and you care, try uncommenting the Oracle line in schema_generator.rb and then leave a comment telling me what happens.
Tags rubyonrails, schema, sqlserver | 5 comments | no trackbacks
Posted by Scott Laird
Sat, 31 Dec 2005 16:10:54 GMT
It’s taken forever, but I finally have a version of my schema generator that works with Rails 1.0. The actual fix was fairly minor (7 lines), but getting to a point where I could test it has been amazingly difficult–my new PowerBook had a bit of disk corruption and Ruby stopped working. Rebuilding the entire Ruby distribution from DarwinPorts wasn’t enough to fix the problem, somehow, but installing a new copy of REXML did the trick for reasons that are too obscure for understanding.
So, go and enjoy.
Update: I just bumped it to 1.0.1 to fix a MySQL dependancy bug that I’d forgotten about for 1.0.0. Have fun.
Tags ruby, rubyonrails, schema | 7 comments | no trackbacks
Posted by Scott Laird
Sat, 10 Dec 2005 18:14:12 GMT
I just released a new version of my Rails Schema Generator on Rubyforge. The schema generator takes a collection of Rails database migration scripts and assembles a complete set of SQL schema files using only the information from the migrations. This release supports MySQL, PostgreSQL, and SQLite; it can generate schemas for all three DB types even if the databases aren’t installed on the system.
We’ve found that the schema generator drastically lowers the work needed to keep Typo working correctly with multiple database types.
This release fixes a number of bugs and should finally work correctly with all common migration operations, including field renaming. This has only been tested with Rails 0.14.4, and at least one API has changed recently, so this may not work with earlier 0.14.x releases.
To use the schema generator, run gem install schema_generator, and then (from your Rails project directory) ./script/generate schema. This will create several schema files in the db/ directory, prompting you before overwriting existing files.
Tags database, ruby, rubyonrails, schema, typo | 4 comments | no trackbacks
Posted by Scott Laird
Mon, 24 Oct 2005 17:25:30 GMT
I just uploaded version 0.2.0 of my Rails Schema Generator to Rubyforge. This is a minor update, but it was needed to make the schema generator work with Rails 1.0rc2 (AKA 0.14.1).
The schema generator is sort of the flip side of the new schema code in Rails 1.0. It takes a set of Rails migrations, aggregates them all together, and spits out a SQL file that describes the DB that you’d get if you ran all of the migrations. Or, viewed in a more useful light, it gives you a SQL file that you can use to create a new DB from scratch. The current version actually produces three different schema files, one for PostgreSQL, one for MySQL, and one for SQLite, each with DB-appropriate syntax and types.
This is an outgrowth of Typo; we’re up to 25 migrations now, and we actively support 3 different DBs. It was getting really painful to maintain 3 distinct schema files in addition to the collection of migrations, so I wrote this schema generator. Now we’re back to DRY-land–we create new migrations and let the schema generator do all of the hard work.
Tags database, generator, ruby, rubyonrails, schema, schemagenerator | no comments
Posted by Scott Laird
Sat, 03 Sep 2005 08:09:00 GMT
I just uploaded the first version of my schema generator to rubyforge. This is a Rails generator that knows how to take a collection of migration scripts and use them to build up a valid SQL schema file.
You should be able to install it via gem install schema_generator, and run it on any Rails project by running ./script/generate schema from the root of the Rails project. The current release (0.1.0) supports MySQL, PostgreSQL, and SQLite. It will auto-generate a schema for each DB in db/schema.DBTYPE.sql every time it runs, prompting you before overwriting existing files.
For this to work, your Rails migrations must describe your complete database schema. Many projects, like Typo, are older then Rails’s migration support, so their migrations don’t start with a clean slate; instead they describe how to migrate from a specific old version of the DB schema to the current version. In this case, either create a 0_initial_schema migration or to modify the existing migration #1 to create all of the original tables. I just committed an example to Typo’s subversion tree, feel free to use it as an example.
Read more...
Tags database, generators, rails, ruby, rubyonrails, schema
Posted by Scott Laird
Fri, 02 Sep 2005 22:56:00 GMT
My Rails Schema Generator is nearly complete. Here’s a sample run:
$ ./script/generate schema
Found 6 migration classes
Starting migration for AddSidebars
Starting migration for AddCacheTable
Starting migration for AddPages
Starting migration for AddPageTitle
Starting migration for AddTags
Starting migration for AddTextfilters
Adding TextFilters table
Migrations complete.
Tables found: 6
Indexes found: 1
Records found: 8
exists db
overwrite db/schema.postgresql.sql? [Ynaq] y
force db/schema.postgresql.sql
overwrite db/schema.mysql.sql? [Ynaq] y
force db/schema.mysql.sql
overwrite db/schema.sqlite.sql? [Ynaq] y
force db/schema.sqlite.sql
The migration classes that I’m using are copied straight from Typo without modification. I’ve left out all of the migrations that add features to “legacy” tables–tables like articles–since there isn’t a table definition that I can use. That’s my next project–adding a 0_initial_schema migration for Typo. Once that’s complete, I have a bit of code cleanup and then I’ll release my schema generator code to the world. Hopefully that’ll be later today.
Tags database, generators, rails, ruby, rubyonrails, schema, typo | no comments
Posted by Scott Laird
Thu, 01 Sep 2005 08:14:00 GMT
One of the things that has really bugged me with recent Typo development is the pain of maintaining 3 different database schema files (PostgreSQL, MySQL, SQLite) along with a set of Rails DB migration scripts. Every time we add a new table, we have to edit 4 different files, even though all of the information that we need is available in the migration file. Unfortunately, without the static schemas, new users would be adrift, so we’re stuck having to hand-modify each of the static schema files. This violates the DRY principle, causes errors, and irritates developers.
So I figured I’d fix it by writing some code that can take a set of Rails DB migrations, fold, spindle, and mutilate Rails itself, and then spit out a database-specific schema file showing all of the tables, indexes, and seed data provided by the migration files. This includes handling cases where a table is added in migration #4, two new fields are added in migration #6, and one field is deleted in migration #9. There are some corner cases that just can’t be handled, mostly relating to seed data that needs to be migrated to be correct with more recent schemas, but I think I can come close enough to make Typo happy, and probably a lot of other open-source Rails projects.
This turned out to be easier then I expected. I’ve put about 4 hours into it so far, and I can take this migration:
class Test4 < ActiveRecord::Migration
def self.up
create_table :sidebars do |t|
t.column :controller, :string
t.column :active_position, :integer
t.column :active_config, :text
t.column :staged_position, :integer
t.column :staged_config, :text
end
Sidebar.create(:active_position=>0, :controller=>'category')
Sidebar.create(:active_position=>1, :controller=>'static')
Sidebar.create(:active_position=>2, :controller=>'xml')
end
def self.down
drop_table :sidebars
end
end
And then do this:
$ irb
irb(main):001:0> require 'migrate'
=> true
irb(main):002:0> require 'db/migrate/4_test4' # the code above
=> true
irb(main):003:0> Test4.up
=> ...
irb(main):004:0> puts DBMigrator::Database.dump('postgresql')
CREATE TABLE sidebars (id serial primary key, controller character varying(255), active_position integer, active_config text, staged_position integer, staged_config text) ;
BEGIN;
INSERT INTO sidebars ("staged_position", "active_config", "active_position", "controller", "staged_config") VALUES(NULL, NULL, 0, 'category', NULL);
COMMIT;
BEGIN;
INSERT INTO sidebars ("staged_position", "active_config", "active_position", "controller", "staged_config") VALUES(NULL, NULL, 1, 'static', NULL);
COMMIT;
BEGIN;
INSERT INTO sidebars ("staged_position", "active_config", "active_position", "controller", "staged_config") VALUES(NULL, NULL, 2, 'xml', NULL);
COMMIT;
Postgres works now, at least with the 4 or 5 examples that I’ve swiped from Typo’s migrations. SQLite and MySQL are nearly working; I think I just need to fake out a couple classes each and they’ll be up and running. Once that’s done, I’ll bundle this all up into a Rails generator so people can do this:
$ ./script/generate schema postgresql
create db/schema.postgresql.sql
$ ./script/generate schema mysql
create db/schema.mysql.sql
$ ./script/generate schema sqlite
create db/schema.sqlite.sql
Tags database, migrations, rails, ruby, rubyonrails, schema, typo | 8 comments
