[ic] Create a table definition from your existing database tables
Calgary Ecommerce Services
webmaster at calgary-ecommerce-services.com
Thu Mar 3 14:19:54 EST 2005
Thank you, Brian, for these details. I also saw a previous posting here at
that resembled what I was trying to do.
I was able to use the
[query sql=...][sql-param ...][/query] syntax to display the table, which I'd
created with phpMyAdmin, even though a .mysql file was not created.
However, creating the .mysql file seems like the better approach.
One way to do what (I think) you want is to go into your catalog (note
you should back up your catalog before messing with the database
cp products.mysql newtable.mysql
(where newtable is the name of your new table)
Then open newtable.mysql and edit it (pico newtable.mysql or vim
newtable.mysql or whatever), changing all of the values to be
appropriate for your newtable
Database products products.txt __SQLDSN__
Database products USER __SQLUSER__
Database products PASS __SQLPASS__
Database products KEY sku
Database products COLUMN_DEF "sku=char(64) NOT NULL PRIMARY KEY"
Becomes for example:
Database newtable newtable.txt __SQLDSN__
Database newtable USER __SQLUSER__
Database newtable PASS __SQLPASS__
Database newtable KEY newfield
Database newtable COLUMN_DEF "newfield=char(64) NOT NULL PRIMARY
Then cd ../../products/
(enter each new field name seperated by tabs ... be very careful to
make sure there are tabs and not spaces!)
newfield newfield2 newfield3 newfield4
To double check that there are tabs and not spaces you can do:
If you see ^I between each field then there are tabs but if there isn't
^I then they aren't all tabs and you should fix this.
(exit vi use :q)
Sorry if some of the editor instructions are pedantic but wanted
someone who hasn't use vi/pico/emacs to be able to do this on linux ..
the reason of course is that you want a proper unix type file and not
one with windows type newlines...
Now restart interchange and see if the new table was added OK (beware
any errors). Then log into the admin and double check if it went OK.
If some fields are mysteriously missing I bet tabs are to blame (one
harrowing day proved this to me ;).
If anyone notices errors please correct me!
Note that you can also alter an existing table in a similar way:
For example, to edit the products.mysql file (first back it up by
doing cp products.mysql products.mysql.bk)
First go into the admin and export products.txt so you have a fresh
copy in products. Go into products and cp products.txt
add your new fields _at the end_
perl -i~ -wpe 's/$/\t\t/' products.txt
Where you have one \t for each new field
Then rm products.mysql
check for errors
if it didn't work and you need your old table back then replace the
.mysql and .txt files with their backups, delete the .sql file and
restart interchange again.
We really should add a functionality into interchange similar to a
simplified 'phpMyAdmin' so that people can add tables that interchange
is aware of and change field types, etc without all of the black
mojo... There are some perl mysql admin programs out there; perhaps
one could be integrated into ic.
Calgary Ecommerce Services
More information about the interchange-users