Hi, Anyone got any cool tricks for converting a Rails site (in this case, a typo installation) from a mysql database to a postgresql database? I''ve got it almost working -- I''m doing a SQL dump from mysql and loading it into postgres. However, mysql does booleans as a tinyint with 0 = false and 1 = true. When I try to import that into a postgres database that expects booleans to be actual booleans, it fails. Any ideas? Thanks, Joe
Hi Joe, IMHO you could utilize migrations for this. With a database.yaml which sets adapter to mysql do something like rake db_schema_dump (if I remember that correctly - it creates a schema.rb from your database) than change your environment to the postgres-adapter and do something like rake db_schema_import (don''t know if I remember that correctly either). Cheers, Jan On 8/5/06, Joe Van Dyk <joevandyk@gmail.com> wrote:> > Hi, > > Anyone got any cool tricks for converting a Rails site (in this case, > a typo installation) from a mysql database to a postgresql database? > > I''ve got it almost working -- I''m doing a SQL dump from mysql and > loading it into postgres. However, mysql does booleans as a tinyint > with 0 = false and 1 = true. When I try to import that into a > postgres database that expects booleans to be actual booleans, it > fails. > > Any ideas? > > Thanks, > Joe > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060805/6f26774a/attachment.html
Joe Van Dyk wrote:> Hi, > > Anyone got any cool tricks for converting a Rails site (in this case, > a typo installation) from a mysql database to a postgresql database? > > I''ve got it almost working -- I''m doing a SQL dump from mysql and > loading it into postgres. However, mysql does booleans as a tinyint > with 0 = false and 1 = true. When I try to import that into a > postgres database that expects booleans to be actual booleans, it > fails. > > Any ideas? > > Thanks, > JoeI''ve had to deal with that going from Postgres to Oracle, which doesn''t have the boolean. Why not just have your Postgres data model use smallint and keep the same convention of zero and one? If you''re using AR, it''s smart enough to do the right thing in any case (if your column is named xxx, then the AR method xxx? will autoconvert numeric zero/nonzero to logical false/true). -- Posted via http://www.ruby-forum.com/.
On 8/5/06, Francis Cianfrocca <garbagecat10@gmail.com> wrote:> Joe Van Dyk wrote: > > Hi, > > > > Anyone got any cool tricks for converting a Rails site (in this case, > > a typo installation) from a mysql database to a postgresql database? > > > > I''ve got it almost working -- I''m doing a SQL dump from mysql and > > loading it into postgres. However, mysql does booleans as a tinyint > > with 0 = false and 1 = true. When I try to import that into a > > postgres database that expects booleans to be actual booleans, it > > fails. > > > > Any ideas? > > > > Thanks, > > Joe > > I''ve had to deal with that going from Postgres to Oracle, which doesn''t > have the boolean. Why not just have your Postgres data model use > smallint and keep the same convention of zero and one? If you''re using > AR, it''s smart enough to do the right thing in any case (if your column > is named xxx, then the AR method xxx? will autoconvert numeric > zero/nonzero to logical false/true). > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >Postgres has a native boolean column, so it makes sense to use it, doesn''t it? It''s a bit more upfront work when dumping the data, but I think it''s worth it so that the database is "done right." Joe, there are a couple things you can do. The first is to do a regex replacement in your dump file. You could also just write a custom dump script (using AR models) and output the SQL that you need, using true/false instead of 1/0. You might also be able to use migrations. Create the schema, but make that column an integer column. Then load all your data in. Then do a new migration that looks something like: rename_column :my_table, :bool_field, :old_bool add_column :my_table, :bool_field, :boolean MyTable.update_all "bool_field=true", "old_bool=1" MyTable.update_all "bool_field=false", "old_bool=0" Assuming that all your data loads fine, I think that would be the easiest way. Pat
Pat Maddox wrote:> Postgres has a native boolean column, so it makes sense to use it, > doesn''t it? It''s a bit more upfront work when dumping the data, but I > think it''s worth it so that the database is "done right." >No argument with your suggestions to Joe, but how do you determine that the database is "done right"? I''m not a purist about sticking to standard SQL, because every engine has its own quirks and its own lore, and being sensitive to these can hugely improve performance. But I doubt you''ll get a benefit from Postgres'' boolean, which is merely a notational convenience. So why not stick to the standard in this case? -- Posted via http://www.ruby-forum.com/.
Francis Cianfrocca wrote:> Pat Maddox wrote: > > >> Postgres has a native boolean column, so it makes sense to use it, >> doesn''t it? It''s a bit more upfront work when dumping the data, but I >> think it''s worth it so that the database is "done right." >> >> > > No argument with your suggestions to Joe, but how do you determine that > the database is "done right"? I''m not a purist about sticking to > standard SQL, because every engine has its own quirks and its own lore, > and being sensitive to these can hugely improve performance. But I doubt > you''ll get a benefit from Postgres'' boolean, which is merely a > notational convenience. So why not stick to the standard in this case? > >Hello Francis, Not to ''split hairs'', but if your worried about sticking to ''the standard'', then the standard actually does have a ''boolean'' defined in it. SQL99 defines it, and your going to laugh at this but, Oracle is then ''behind the times'' here in not defining a boolean type as an allowed database column type, but it -does- have a boolean datatype that you can use in pl/sql. Perhaps the easiest way to go from postgreSQL->Oracle is to create the datatype using user defined datatypes, but, thats beyond my Oracle know how and starting to get away from the posters original question ;) Regards Stef
On 8/5/06, Francis Cianfrocca <garbagecat10@gmail.com> wrote:> So why not stick to the standard in this case?That''s precisely why you should use a boolean. The standard defines it, Postgres implements it. Pat
On Aug 5, 2006, at 1:21 AM, Joe Van Dyk wrote:> Anyone got any cool tricks for converting a Rails site (in this case, > a typo installation) from a mysql database to a postgresql database? > > I''ve got it almost working -- I''m doing a SQL dump from mysql and > loading it into postgres. However, mysql does booleans as a tinyint > with 0 = false and 1 = true. When I try to import that into a > postgres database that expects booleans to be actual booleans, it > fails. > > Any ideas?How about - dump production db to fixtures - switch database.yml to postgresql - rake migrate - load fixtures to production with the help of the ar_fixtures plugin http://topfunky.net/svn/plugins/ar_fixtures http://nubyonrails.com/articles/2005/12/27/dump-or-slurp-yaml- reference-data jeremy
Pat Maddox wrote:> On 8/5/06, Francis Cianfrocca <garbagecat10@gmail.com> wrote: >> So why not stick to the standard in this case? > > That''s precisely why you should use a boolean. The standard defines > it, Postgres implements it. > > PatI may be wrong about this but BOOLEAN is optional in ANSI SQL, and only Postgres implements it in a compliant way. This is a really tiny point in the context of what the OP was asking, but I am curious whether you think one should code to standards because they are standard, or because they are widely-adopted? Oracle is far more interested in adding up-stack features to their product line, and they probably won''t be adding BOOLEAN any time soon. It often happens that you have to migrate databases, and I''ve found that migrating away from Postgres is generally painful, because it''s full of great features that aren''t widely available (Postgres to Oracle is especially painful, and especially common). The lack of BOOLEAN is easy to work around, but other often-used Postgres features are not. I love Postgres and I use it whenever possible. But if you''re going to use Postgres features, you need to know what''s not available elsewhere, isolate the dependencies in your code, and document them carefully (and grep-ably). And of course you can use AR to wrap most of this up, but only if performance doesn''t matter in your application. -- Posted via http://www.ruby-forum.com/.
On 8/5/06, Pat Maddox <pergesu@gmail.com> wrote:> On 8/5/06, Francis Cianfrocca <garbagecat10@gmail.com> wrote: > > Joe Van Dyk wrote: > > > Hi, > > > > > > Anyone got any cool tricks for converting a Rails site (in this case, > > > a typo installation) from a mysql database to a postgresql database? > > > > > > I''ve got it almost working -- I''m doing a SQL dump from mysql and > > > loading it into postgres. However, mysql does booleans as a tinyint > > > with 0 = false and 1 = true. When I try to import that into a > > > postgres database that expects booleans to be actual booleans, it > > > fails. > > > > > > Any ideas? > > > > > > Thanks, > > > Joe > > > > I''ve had to deal with that going from Postgres to Oracle, which doesn''t > > have the boolean. Why not just have your Postgres data model use > > smallint and keep the same convention of zero and one? If you''re using > > AR, it''s smart enough to do the right thing in any case (if your column > > is named xxx, then the AR method xxx? will autoconvert numeric > > zero/nonzero to logical false/true). > > > > -- > > Posted via http://www.ruby-forum.com/. > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > Postgres has a native boolean column, so it makes sense to use it, > doesn''t it? It''s a bit more upfront work when dumping the data, but I > think it''s worth it so that the database is "done right." > > Joe, there are a couple things you can do. The first is to do a regex > replacement in your dump file. You could also just write a custom > dump script (using AR models) and output the SQL that you need, using > true/false instead of 1/0. > > You might also be able to use migrations. Create the schema, but make > that column an integer column. Then load all your data in. Then do a > new migration that looks something like: > rename_column :my_table, :bool_field, :old_bool > add_column :my_table, :bool_field, :boolean > MyTable.update_all "bool_field=true", "old_bool=1" > MyTable.update_all "bool_field=false", "old_bool=0" > > Assuming that all your data loads fine, I think that would be the easiest way.Thanks -- I ended up doing exactly that last night. Everything works fine! Joe
On Aug 5, 2006, at 12:13 PM, Jeremy Kemper wrote:> On Aug 5, 2006, at 1:21 AM, Joe Van Dyk wrote: >> Anyone got any cool tricks for converting a Rails site (in this case, >> a typo installation) from a mysql database to a postgresql database? >> >> I''ve got it almost working -- I''m doing a SQL dump from mysql and >> loading it into postgres. However, mysql does booleans as a tinyint >> with 0 = false and 1 = true. When I try to import that into a >> postgres database that expects booleans to be actual booleans, it >> fails. >> >> Any ideas? > > How about > - dump production db to fixtures > - switch database.yml to postgresql > - rake migrate > - load fixtures to production > > with the help of the ar_fixtures plugin > http://topfunky.net/svn/plugins/ar_fixtures > http://nubyonrails.com/articles/2005/12/27/dump-or-slurp-yaml- > reference-data > > jeremy >This is pretty much what I did for a few of our development projects where the client came to us with a Rails/MySQL application. I turned this into rake tasks that also used RailsFS (I needed to have an excuse to use this..) http://www.robbyonrails.com/articles/2005/10/29/migrating-from-mysql- to-postgresql-in-60-seconds-with-rails :-) Robby -- Robby Russell Founder & Executive Director PLANET ARGON, LLC Ruby on Rails Development, Consulting & Hosting www.planetargon.com www.robbyonrails.com +1 503 445 2457 +1 877 55 ARGON [toll free] +1 815 642 4068 [fax]