Moving from MySQL to PostgreSQL with Ruby on Rails.

On Thu, 2005-11-17 at 08:48 -0800, Peter Michaux wrote:

Hi,

I'm just new to the PostgreSQL world. I've been using MySQL but I want
to develop a Ruby on Rails application that can be installed on either
MySQL or PostgreSQL. I don't know how much the DDL dialects vary
between them. At the moment I am interested in the options on a table
like UTF-8. In MySQL I write

CREATE TABLE product (
id INT NOT NULL AUTOINCREMENT,
name VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) DEFAULT CHARSET=UTF-8;

Will this definition work in the PostgreSQL world? Is there a web page
for people with MySQL exerience moving to PostgreSQL?

CREATE TABLE product (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL DEFAULT '',
);

Part of the issue is the way Ruby on Rails migration class enables me
to add options to Rails' own abstraced DDL just like I have done in
the above example. Other ways of adding options might be tricky.

With ActiveRecord::Migration:

# db/migrate/1_initial.rb
class Initial < ActiveRecord::Migration

def self.up
create_table :products do |t|
t.column :name, :string, :default => ''
end
end

# drop all tables 'rake migrate VERSION=0'
def self.down
drop_table :products
end

end

# Run from main Rails directory
rake migrate

Using either plain SQL like above or AR::Migrate will generate the same table structure.

Cheers,

-Robby

--
/******************************************************
* Robby Russell, Founder.Developer.Geek
* PLANET ARGON, Rails Development, Consulting & Hosting
* Portland, Oregon | p: 503.351.4730 | f: 815.642.4068
* www.planetargon.com | www.robbyonrails.com
* Programming Rails | www.programmingrails.com
*******************************************************/

On Thu, Nov 17, 2005 at 08:48:45AM -0800, Peter Michaux wrote:

Hi,

I'm just new to the PostgreSQL world. I've been using MySQL but I want to
develop a Ruby on Rails application that can be installed on either MySQL or
PostgreSQL. I don't know how much the DDL dialects vary between them. At the
moment I am interested in the options on a table like UTF-8. In MySQL I
write

CREATE TABLE product (
id INT NOT NULL AUTOINCREMENT,
name VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) DEFAULT CHARSET=UTF-8;

CREATE TABLE product (
id SERIAL PRIMARY KEY,
"name" TEXT NOT NULL
/* "name" isn't your greatest idea because it's a keyword.
* http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
* A more descriptive word or pair of words would be good here.
* There is no DEFAULT clause because the database should throw an
* error if somebody tries to INSERT a NULL here, not march onward.
*/
);

/* You might also want this: */
CREATE UNIQUE INDEX idx_uniq_product_name
ON product(LOWER(TRIM("name")));

Will this definition work in the PostgreSQL world? Is there a web
page for people with MySQL exerience moving to PostgreSQL?

Here are a couple. The first is a general "how to convert from other
things," while the second is MySQL specific. :)

http://techdocs.postgresql.org/#convertfrom
http://www.in-nomine.org/~asmodai/mysql-to-pgsql.html

Part of the issue is the way Ruby on Rails migration class enables
me to add options to Rails' own abstraced DDL just like I have done
in the above example.

I can't say I think it's a good idea to have "abstracted" or
"portable" DDL. It's always expensive and difficult to maintain
because you're either writing the DDL, etc. several times, or you're
pushing functionality up into middleware where it may not belong.

Pick one database back-end and stick with it. It's ever so much
easier to deal with.

OK, that's my $.02 :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On Thu, Nov 17, 2005 at 09:23:51 -0800,
Robby Russell <robby.lists@planetargon.com> wrote:

CREATE TABLE product (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL DEFAULT '',
);

And depending on why you chose VARCHAR(255), you may really want to use TEXT
instead.

On Thu, 2005-11-17 at 15:10 -0600, Bruno Wolff III wrote:

On Thu, Nov 17, 2005 at 09:23:51 -0800,
Robby Russell <robby.lists@planetargon.com> wrote:

CREATE TABLE product (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL DEFAULT '',
);

And depending on why you chose VARCHAR(255), you may really want to use TEXT
instead.

Mainly because, Rails will generate forms based on the data types
differently. A text field -> VARCHAR... textarea -> TEXT.

Sort of a meta-approach for Rails-based scaffolding generator. Not
required, but it'll speed up the process and limit the number of chars
that you can stick into a text field opposed to a text area.

Robby

--
/******************************************************
* Robby Russell, Founder.Developer.Geek
* PLANET ARGON, Rails Development, Consulting & Hosting
* Portland, Oregon | p: 503.351.4730 | f: 815.642.4068
* www.planetargon.com | www.robbyonrails.com
* Programming Rails | www.programmingrails.com
*******************************************************/

On Nov 17, 2005, at 4:44 PM, Robby Russell wrote:

Sort of a meta-approach for Rails-based scaffolding generator. Not
required, but it'll speed up the process and limit the number of chars
that you can stick into a text field opposed to a text area.

Yet again you see RoR compensating for lack of data integrity
checking in the DB of choice... :-)