Re: updating to SQL

[Kyle Hayes <kyle540@quicknet.net>]

Ik kan U een beetje hulpen...

I just moved my products database into MySQL, but I don't have 23k
products!!  

Steps:

0) get, install, test DBI.  Off of the MySQL site, there are links to
download the needed files.  If you do it in the order they recommend,
things seem to work.  I had some problems initially because I didn't
follow the direction exactly and didn't set up the permissions for MySQL
correctly.

1) I re-created my products database schema in mySQL:

CREATE TABLE products (
  code char(32) DEFAULT '' NOT NULL,
  description char(64),
  price double(10,2),
  category char(32),
  nontaxable char(5),
  weight float(10,2),
  cost double(10,2),
  reseller char(32),
  title char(32)
);

Note that this does not match the default products database exactly.  I
modified it to match some other tables we have.  We have few products, so
there isn't an index here, but you will _definitely_ want to create some
indexes on your table!!  I would suggest creating indexes on "code" at
least.  If you have a lot of product categories and often create category
pages, you might want to index on category too.  I will be indexing on
reseller because we have different prices negotiated with different
resellers and I generate special pages based on the reseller.

We calculate shipping insurance based on our cost for the product, not the
price we change the end user, thus we have both cost and price.

2) Add your data.  This is going to be a database specific thing.  I only
had a test database with a few rows in the products table, so I just
entered the data by hand.  In your case, a couple of quick perl scripts
with a few DBI calls should do the trick.  MySQL might even be able to
pull in a TAB delimited file, but I haven't tried it.

3) DO A FEW SQL QUERIES TO MAKE SURE THAT EVERYTHING IS HAPPY! I had some
annoying little bugs in my grants, so I couldn't access the products table
as the correct user.  Use mysql or one of the graphical front-ends to do
this test, not MV.  Test the exact user/password that you will be using
from MV, and test it from the same machine!  MySQL has complex access
control and it is easy (in my limited experience) to make an error
somewhere.

4) Add the database to the catalog.cfg and comment out the old one.  Here
is what I did:

old:

# Database            products     products.asc      TAB
# Database            products     INDEX             category

^
|
+-- commented out

new:


########### Database
##
## database for product info.
#
# 

Database	products	products.asc	SQL
Database	products	DSN	dbi:mysql:YourDB:YourHost
Database	products	USER	xxxxxxxxx
Database	products	PASS	xxxxxxxxx

Replace "YourDB" with the name of the database in which you have the
products table.  Replace "YourHost" with the name of the host running
MySQL.  Add in the appropriate USER and PASS values.

The very first time I fired off MV, I got an error from MV that it was
trying to create the table with slightly different datatypes and sizes.  I
suspect it was because I didn't erase the old product .gdbm files or
something.  I restarted MV and didn't get the error.  Beats me what it
was.

I built a page on which I simply tested a lot of the [data ...] tags
to make sure everything was happy.

I assume that the indexes should be created in MySQL, not through MV, but
I could be totally wrong.  I have precisely six rows in my products table
so far, so I can not tell if there is any slowdown due to a lack of
indexes :-)

One thing I did find that really suprized me is that I do not seem to be
able to have table names greater than 8 characters.  I had a table called
resellers and I got errors from MV (or DBI?) that it couldn't find the
database table reseller.reseller!  Since my database was named reseller
and the table was resellers, I guessed that the name of the table was
being truncated somewhere and I changed the name of the table from
resellers to reseller.  Suddenly, everything worked.  I have not checked
in the manual for more info.

I hope this helps...

I have moved nearly all the tables into MySQL now.  I find that it is a
lot easier to debug things with the text versions.  Once things are
working, I switch the database over to MySQL.  My first attempt was to
remove _all_ of the default order logging and place order info into a SQL
table.  We have a large number of back-end reporting systems we will be
using that require access via SQL.  That was a slow process as I was just
learning MV (and still clearly have a _long_ way to go).  Call me
old-fashioned, but I feel a lot more comfortable with a standardized
system holding my data.

Best,
Kyle