I started my app before migrations were a best practice and have been using SQL scripts. Now I''m looking at potentially having to move from using MySQL to postgreSQL to use a particular hosting provider. I understand migrations are the way to go to make this ''easy'' but it also looks like the use of migrations introduces extra work in other areas. I''d really appreciate feedback from anybody with experience on a some questions I''m mulling around. The app is simple: form-completion -> generate XML file -> delete data from db The form entry fields are all defined as varchar(n). The largest is about 80 characters. MySQL truncates any entry. With migrations, my understanding is that the fields get declared as string. So it looks like, at a minimum, moving to migrations is going to make me put validates_length_of on every text_field in every model to protect against SQL injection. (I already escape all text output in the views) Is my understanding about migrations using string, and the correllated assumption re" length_of validation correct? Just how ''easy'' do migrations make it to move from MySQL to postgreSQL? I''m assuming the DDL for postgreSQL is the major benefit. But, never having used postgreSQL, I''m wondering if there are application level changes that are going to be required above and beyond what migrations can do for me. It''s obvious there''s going to be at least ''some'' work involved in moving to migrations. OTOH, the only thing currently driving the question is the potential use of a particular hosting service. Is there, for this simple type of app, any real benefit to moving to postgreSQL that I ought to be factoring into the equation? Thanks in advance for any experience / thoughts you''d be willing to share. Best regards, Bill -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060716/5ad43fb2/attachment.html
On 7/16/06, Bill Walton <bill.walton@charter.net> wrote:> > > I started my app before migrations were a best practice and have been using > SQL scripts. Now I''m looking at potentially having to move from using MySQL > to postgreSQL to use a particular hosting provider. I understand migrations > are the way to go to make this ''easy'' but it also looks like the use of > migrations introduces extra work in other areas. I''d really appreciate > feedback from anybody with experience on a some questions I''m mulling > around. > > The app is simple: form-completion -> generate XML file -> delete data from > db > > The form entry fields are all defined as varchar(n). The largest is about > 80 characters. MySQL truncates any entry. With migrations, my > understanding is that the fields get declared as string. So it looks like, > at a minimum, moving to migrations is going to make me put > validates_length_of on every text_field in every model to protect against > SQL injection. (I already escape all text output in the views) Is my > understanding about migrations using string, and the correllated assumption > re" length_of validation correct? > > Just how ''easy'' do migrations make it to move from MySQL to postgreSQL? I''m > assuming the DDL for postgreSQL is the major benefit. But, never having > used postgreSQL, I''m wondering if there are application level changes that > are going to be required above and beyond what migrations can do for me. > > It''s obvious there''s going to be at least ''some'' work involved in moving to > migrations. OTOH, the only thing currently driving the question is the > potential use of a particular hosting service. Is there, for this simple > type of app, any real benefit to moving to postgreSQL that I ought to be > factoring into the equation? > > Thanks in advance for any experience / thoughts you''d be willing to share. > > Best regards, > Bill > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >Hi Bill, I think you should be able to easily manage the schema creation itself. In RAILS_ROOT you can just do rake db:schema:dump and it''ll dump your schema to db/schema.rb. You can then just create a migration numbered 000 or 001, and paste the inner part of the schema into the migrations up method. As far as your issue with varchar goes, varchar doesn''t mean a whole lot in postgresql. Afaik, it''s not implemented any differently from a standard char field. If you have limits though it will truncate your strings. Finally, imo I think that it''s much better to put that kind of validation in the model anyway. Do you really want to just automatically truncate any text that goes in? You might right now, but at some point you might want to have some kind of error. Even if you just want to truncate it, it''s pretty simple in Rails def before_save; self.my_field = my_field[0..79]; end You might be interested in reading DHH''s "Choose a Single Layer of Cleverness" post [1] if you haven''t already. Alex Bunardzic wrote a follow-up [2] that provides some good reasoning behind that opinion. I suggest you read through each post, along with the comments, and see which approach suits you best for your particular project. Pat [1] http://www.loudthinking.com/arc/000516.html [2] http://lesscode.org/2005/09/29/should-database-manage-the-meaning/
On 7/16/06, Bill Walton <bill.walton@charter.net> wrote:> The form entry fields are all defined as varchar(n). The largest is about > 80 characters. MySQL truncates any entry. With migrations, my > understanding is that the fields get declared as string. So it looks like, > at a minimum, moving to migrations is going to make me put > validates_length_of on every text_field in every model to protect against > SQL injection. (I already escape all text output in the views) Is my > understanding about migrations using string, and the correllated assumption > re" length_of validation correct?This is database-dependent. If you look in postgresql_adapter.rb, you''ll find this: def native_database_types { :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" } } end So, you can still choose between :string or :text depending on if you want a field to automatically have a size limit or not. -- James
On 7/16/06, Pat Maddox <pergesu@gmail.com> wrote:> On 7/16/06, Bill Walton <bill.walton@charter.net> wrote: > > > > > > I started my app before migrations were a best practice and have been using > > SQL scripts. Now I''m looking at potentially having to move from using MySQL > > to postgreSQL to use a particular hosting provider. I understand migrations > > are the way to go to make this ''easy'' but it also looks like the use of > > migrations introduces extra work in other areas. I''d really appreciate > > feedback from anybody with experience on a some questions I''m mulling > > around. > > > > The app is simple: form-completion -> generate XML file -> delete data from > > db > > > > The form entry fields are all defined as varchar(n). The largest is about > > 80 characters. MySQL truncates any entry. With migrations, my > > understanding is that the fields get declared as string. So it looks like, > > at a minimum, moving to migrations is going to make me put > > validates_length_of on every text_field in every model to protect against > > SQL injection. (I already escape all text output in the views) Is my > > understanding about migrations using string, and the correllated assumption > > re" length_of validation correct? > > > > Just how ''easy'' do migrations make it to move from MySQL to postgreSQL? I''m > > assuming the DDL for postgreSQL is the major benefit. But, never having > > used postgreSQL, I''m wondering if there are application level changes that > > are going to be required above and beyond what migrations can do for me. > > > > It''s obvious there''s going to be at least ''some'' work involved in moving to > > migrations. OTOH, the only thing currently driving the question is the > > potential use of a particular hosting service. Is there, for this simple > > type of app, any real benefit to moving to postgreSQL that I ought to be > > factoring into the equation? > > > > Thanks in advance for any experience / thoughts you''d be willing to share. > > > > Best regards, > > Bill > > > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > Hi Bill, > > I think you should be able to easily manage the schema creation > itself. In RAILS_ROOT you can just do rake db:schema:dump and it''ll > dump your schema to db/schema.rb. You can then just create a > migration numbered 000 or 001, and paste the inner part of the schema > into the migrations up method. > > As far as your issue with varchar goes, varchar doesn''t mean a whole > lot in postgresql. Afaik, it''s not implemented any differently from a > standard char field. If you have limits though it will truncate your > strings. > > Finally, imo I think that it''s much better to put that kind of > validation in the model anyway. Do you really want to just > automatically truncate any text that goes in? You might right now, > but at some point you might want to have some kind of error. Even if > you just want to truncate it, it''s pretty simple in Rails > def before_save; self.my_field = my_field[0..79]; endYou''re badly mistaken. I''m only aware of one ''database'' with low enough regard for the user''s data that it will silently alter them to fit the column definitions. Bill is switching away from it..> You might be interested in reading DHH''s "Choose a Single Layer of > Cleverness" post [1] if you haven''t already. Alex Bunardzic wrote a > follow-up [2] that provides some good reasoning behind that opinion. > I suggest you read through each post, along with the comments, and see > which approach suits you best for your particular project.For most applications bad data is worse than no data. If you can make the db enforce the integrity of your data, why wouldn''t you? There''s so little effort involved, and the payoff is huge. Just make sure you don''t go beyond enforcing integrity, business logic in the db is evil. IMHO switching from mysql to postgres is a no-brainer. I don''t know the specifics of your application, but not tying yourself or your customer to that pile of dung is reason good enough. I''m sure there''s a little work involved, postgres is a fair bit stricter than mysql and will throw errors if you feed it invalid sql/data instead of just making a guess, but it _will_ pay off. I don''t have too much experience with migrations, but they seem to do a good job at maintaining column definitions. If you use parametrized queries (i.e. ["update stuff set a = ? where id = ?", param1, param2]), rails and the db adapters will take care of escaping everything for you. In either case, field length isn''t a concern in that area. Good luck, Isak> > Pat > > [1] http://www.loudthinking.com/arc/000516.html > [2] http://lesscode.org/2005/09/29/should-database-manage-the-meaning/ > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 7/16/06, Isak Hansen <isak.hansen@gmail.com> wrote:> > You''re badly mistaken. > > I''m only aware of one ''database'' with low enough regard for the user''s > data that it will silently alter them to fit the column definitions. > > Bill is switching away from it.. > > For most applications bad data is worse than no data. If you can make > the db enforce the integrity of your data, why wouldn''t you? > > There''s so little effort involved, and the payoff is huge. Just make > sure you don''t go beyond enforcing integrity, business logic in the db > is evil.eek. I use postgresql and do just what you suggested - take advantage of referential integrity in the db, and business logic goes in the model. I was just telling him that he should probably put that auto-truncation in his model. I definitely consider it worthwhile to switch over to postgres :) Pat