I'm CCing the mailing list so that my reply can be on record. I hope you don't mind.
When I first wrote the store code it was actually running on MySQL. I ported it over to Postgresql because when I started deploying to a VPS (best solution for rails apps, IMO), I found out that MySQL running on InnoDB actually takes more RAM than Postgresql. If you run it on MyISAM the RAM issue goes away, but there is no transaction and foreign key support. MySQL still has some gotcha behavior too, especially when you start using tools such as Cocoamysql. My overall experience has been that I wouldn't trust MySQL with an application that deals with other people's money.
That said, the good news is that porting it back to MySQL is really easy. Rails abstracts away database systems for the most part. Off the top of my head, I think the only code that depends on Postgresql is in app/controller/admin_controller.rb, in the index and find_order methods. You'll find it if you keep scrolling down until you see big SQL queries. I left notes on how to port back to MySQL too.
> Just found your store code and downloaded it and would really like > to use it for my up and coming software release. But I have a > problem, my ISP doesn't have Postgresql. Is there a possibility to > use MySql? Haven't taken a look at the code as I haven't gotten > into Ruby on Rails yet.
I've got some basic SQL knowledge, decent Rails knowledge and good PHP knowledge... But your SQL queries in admin_controller.rb are WAY too advanced for me!
Can you give us the abstracted (SQL-independent) way to do this, with no SQL? I don't really care about the performance loss, since I'm the only one that every going to feel it (it's an Admin interface, after all!).
Or could you translate it into MySQL?
My web host doesn't support PostgreSQL...
Kenneth
On Jun 24, 6:25 pm, Andy Kim <andyki...@gmail.com> wrote:
> I'm CCing the mailing list so that my reply can be on record. I hope > you don't mind.
> When I first wrote the store code it was actually running on MySQL. I > ported it over to Postgresql because when I started deploying to a > VPS (best solution for rails apps, IMO), I found out that MySQL > running on InnoDB actually takes more RAM than Postgresql. If you run > it on MyISAM the RAM issue goes away, but there is no transaction and > foreign key support. MySQL still has some gotcha behavior too, > especially when you start using tools such as Cocoamysql. My overall > experience has been that I wouldn't trust MySQL with an application > that deals with other people's money.
> That said, the good news is that porting it back to MySQL is really > easy. Rails abstracts away database systems for the most part. Off > the top of my head, I think the only code that depends on Postgresql > is in app/controller/admin_controller.rb, in the index and find_order > methods. You'll find it if you keep scrolling down until you see big > SQL queries. I left notes on how to port back to MySQL too.
> Cheers,
> Andy Kim > Potion Factory LLC
> On Jun 24, 2007, at 5:56 AM, Jon Flowers wrote:
> > Andy,
> > Just found your store code and downloaded it and would really like > > to use it for my up and coming software release. But I have a > > problem, my ISP doesn't have Postgresql. Is there a possibility to > > use MySql? Haven't taken a look at the code as I haven't gotten > > into Ruby on Rails yet.
> I've got some basic SQL knowledge, decent Rails knowledge and good PHP > knowledge... But your SQL queries in admin_controller.rb are WAY too > advanced for me!
Don't get too scared of the big SQL statements. They are easily ported to MySQL using the notes provided in the source code.
> Can you give us the abstracted (SQL-independent) way to do this, with > no SQL? I don't really care about the performance loss, since I'm the > only one that every going to feel it (it's an Admin interface, after > all!).
To do it without the SQL statements, ActiveRecord will have to fetch every row from the orders and the line_items table for all orders in the last one year period, one row at a time. When you get a decent amount of orders in the system, you'll have to wait tens of seconds (if not minutes, depending on your system) for the admin page to load. Can you guess why I know this? :)
> Or could you translate it into MySQL?
Well, give it a go first. If you can't get it to work, get back to me.
> On Jun 24, 6:25 pm, Andy Kim <andyki...@gmail.com> wrote: >> Hi Jon,
>> I'm CCing the mailing list so that my reply can be on record. I hope >> you don't mind.
>> When I first wrote the store code it was actually running on MySQL. I >> ported it over to Postgresql because when I started deploying to a >> VPS (best solution for rails apps, IMO), I found out that MySQL >> running on InnoDB actually takes more RAM than Postgresql. If you run >> it on MyISAM the RAM issue goes away, but there is no transaction and >> foreign key support. MySQL still has some gotcha behavior too, >> especially when you start using tools such as Cocoamysql. My overall >> experience has been that I wouldn't trust MySQL with an application >> that deals with other people's money.
>> That said, the good news is that porting it back to MySQL is really >> easy. Rails abstracts away database systems for the most part. Off >> the top of my head, I think the only code that depends on Postgresql >> is in app/controller/admin_controller.rb, in the index and find_order >> methods. You'll find it if you keep scrolling down until you see big >> SQL queries. I left notes on how to port back to MySQL too.
>> Cheers,
>> Andy Kim >> Potion Factory LLC
>> On Jun 24, 2007, at 5:56 AM, Jon Flowers wrote:
>>> Andy,
>>> Just found your store code and downloaded it and would really like >>> to use it for my up and coming software release. But I have a >>> problem, my ISP doesn't have Postgresql. Is there a possibility to >>> use MySql? Haven't taken a look at the code as I haven't gotten >>> into Ruby on Rails yet.
It didn't take me long to convert the queries to MySQL, however, some of the queries just didn't work properly for me as I use percentage based coupons and the calculations were off. So I ended up beating up the admin controller and referencing a secondary database I use to get statistics (I use a combination of the orders database and my user database). You might just strip out the scary stuff and go for the simple like order and product count; those work well.
On Jul 4, 7:36 pm, Andy Kim <andyki...@gmail.com> wrote:
> > I've got some basic SQL knowledge, decent Rails knowledge and good PHP > > knowledge... But your SQL queries in admin_controller.rb are WAY too > > advanced for me!
> Don't get too scared of the big SQL statements. They are easily > ported to MySQL using the notes provided in the source code.
> > Can you give us the abstracted (SQL-independent) way to do this, with > > no SQL? I don't really care about the performance loss, since I'm the > > only one that every going to feel it (it's an Admin interface, after > > all!).
> To do it without the SQL statements, ActiveRecord will have to fetch > every row from the orders and the line_items table for all orders in > the last one year period, one row at a time. When you get a decent > amount of orders in the system, you'll have to wait tens of seconds > (if not minutes, depending on your system) for the admin page to > load. Can you guess why I know this? :)
> > Or could you translate it into MySQL?
> Well, give it a go first. If you can't get it to work, get back to me.
> > My web host doesn't support PostgreSQL...
> Sorry about that.
> Sincerely yours, > Andy Kim > Potion Factory LLC> Kenneth
> > On Jun 24, 6:25 pm, Andy Kim <andyki...@gmail.com> wrote: > >> Hi Jon,
> >> I'm CCing the mailing list so that my reply can be on record. I hope > >> you don't mind.
> >> When I first wrote the store code it was actually running on MySQL. I > >> ported it over to Postgresql because when I started deploying to a > >> VPS (best solution for rails apps, IMO), I found out that MySQL > >> running on InnoDB actually takes more RAM than Postgresql. If you run > >> it on MyISAM the RAM issue goes away, but there is no transaction and > >> foreign key support. MySQL still has some gotcha behavior too, > >> especially when you start using tools such as Cocoamysql. My overall > >> experience has been that I wouldn't trust MySQL with an application > >> that deals with other people's money.
> >> That said, the good news is that porting it back to MySQL is really > >> easy. Rails abstracts away database systems for the most part. Off > >> the top of my head, I think the only code that depends on Postgresql > >> is in app/controller/admin_controller.rb, in the index and find_order > >> methods. You'll find it if you keep scrolling down until you see big > >> SQL queries. I left notes on how to port back to MySQL too.
> >> Cheers,
> >> Andy Kim > >> Potion Factory LLC
> >> On Jun 24, 2007, at 5:56 AM, Jon Flowers wrote:
> >>> Andy,
> >>> Just found your store code and downloaded it and would really like > >>> to use it for my up and coming software release. But I have a > >>> problem, my ISP doesn't have Postgresql. Is there a possibility to > >>> use MySql? Haven't taken a look at the code as I haven't gotten > >>> into Ruby on Rails yet.
> > I've got some basic SQL knowledge, decent Rails knowledge and good PHP > > knowledge... But your SQL queries in admin_controller.rb are WAY too > > advanced for me!
> Don't get too scared of the big SQL statements. They are easily > ported to MySQL using the notes provided in the source code.
> > Can you give us the abstracted (SQL-independent) way to do this, with > > no SQL? I don't really care about the performance loss, since I'm the > > only one that every going to feel it (it's an Admin interface, after > > all!).
> To do it without the SQL statements, ActiveRecord will have to fetch > every row from the orders and the line_items table for all orders in > the last one year period, one row at a time. When you get a decent > amount of orders in the system, you'll have to wait tens of seconds > (if not minutes, depending on your system) for the admin page to > load. Can you guess why I know this? :)
> > Or could you translate it into MySQL?
> Well, give it a go first. If you can't get it to work, get back to me.
> > My web host doesn't support PostgreSQL...
> Sorry about that.
> Sincerely yours, > Andy Kim > Potion Factory LLC
> > Kenneth
> > On Jun 24, 6:25 pm, Andy Kim <andyki...@gmail.com> wrote: > >> Hi Jon,
> >> I'm CCing the mailing list so that my reply can be on record. I hope > >> you don't mind.
> >> When I first wrote the store code it was actually running on MySQL. I > >> ported it over to Postgresql because when I started deploying to a > >> VPS (best solution for rails apps, IMO), I found out that MySQL > >> running on InnoDB actually takes more RAM than Postgresql. If you run > >> it on MyISAM the RAM issue goes away, but there is no transaction and > >> foreign key support. MySQL still has some gotcha behavior too, > >> especially when you start using tools such as Cocoamysql. My overall > >> experience has been that I wouldn't trust MySQL with an application > >> that deals with other people's money.
> >> That said, the good news is that porting it back to MySQL is really > >> easy. Rails abstracts away database systems for the most part. Off > >> the top of my head, I think the only code that depends on Postgresql > >> is in app/controller/admin_controller.rb, in the index and find_order > >> methods. You'll find it if you keep scrolling down until you see big > >> SQL queries. I left notes on how to port back to MySQL too.
> >> Cheers,
> >> Andy Kim > >> Potion Factory LLC
> >> On Jun 24, 2007, at 5:56 AM, Jon Flowers wrote:
> >>> Andy,
> >>> Just found your store code and downloaded it and would really like > >>> to use it for my up and coming software release. But I have a > >>> problem, my ISP doesn't have Postgresql. Is there a possibility to > >>> use MySql? Haven't taken a look at the code as I haven't gotten > >>> into Ruby on Rails yet.
