Re: Potion Store on MySQL?

[Andy Kim]

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.
>
> Thanks for any and all help.
>
> Jon Flowers

[seo...@gmail.com]

Andy,

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

[Andy Kim]

Hi Kenneth,

> 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,

[Scott Gruby]

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.

[Kenneth]

Andy,

Thanks for the tips. I finally got it working with MySQL. One thing I
learnt from this is that the MySQL.org docs absolutely suck...

Kenneth