[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
http://www.icdevgroup.org/pipermail/interchange-users/2002-November/029530.html
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.

Stanley





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
structure):

cd dbconf/mysql
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
So:
-----------
Database  products  products.txt __SQLDSN__
ifdef SQLUSER
Database  products  USER         __SQLUSER__
endif
ifdef SQLPASS
Database  products  PASS         __SQLPASS__
endif
Database  products  KEY          sku
Database  products  COLUMN_DEF   "sku=char(64) NOT NULL PRIMARY KEY"
..
-----------
Becomes for example:
------------
Database  newtable  newtable.txt __SQLDSN__
ifdef SQLUSER
Database  newtable  USER         __SQLUSER__
endif
ifdef SQLPASS
Database  newtable  PASS         __SQLPASS__
endif
Database  newtable  KEY          newfield
Database  newtable  COLUMN_DEF   "newfield=char(64) NOT NULL PRIMARY
KEY"
..
--------------

Then cd ../../products/
pico newtable.txt
(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:
vim newtable.txt
:set list
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!

Best,
Bryan

p.s.
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
products.txt.backup
  edit dbconfig/mysql/products.sql
  add your new fields _at the end_
  cd products/
  perl -i~ -wpe 's/$/\t\t/' products.txt
  Where you have one \t for each new field
  Then rm products.mysql
  restart interchange
  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.

p.p.s.
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.



Stanley Major
Calgary Ecommerce Services
http://www.calgary-ecommerce-services.com
Phone: 403-255-4701


More information about the interchange-users mailing list