[ic] Create a table definition from your existing database tables.

Bryan Gmyrek bryangmyrek at yahoo.com
Thu Mar 3 13:16:08 EST 2005


Hi,

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.


On Mar 3, 2005, at 8:29 AM, Calgary Ecommerce Services wrote:

> I've tried to use the script you posted at
> http://www.icdevgroup.org/pipermail/interchange-users/2004-June/ 
> 039592.html
>
> and I'm seeing the following:
>
> [root at localhost Alsew]# ./createMYSQLtabledefn
> ------------------------------------------------------
>  Extractor  -- table structure extract utility
>
>                for Interchange table definition
>
> ------------------------------------------------------
>
> Enter Database (mysqql:database): test_Alsew
> Enter the database user: root
> Enter password (root):
> Enter the table to extract: zippers30invis
>
> install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC  
> contains:
> /usr/lib/perl5/site_perl/5.8.5/i686-linux/DBI
> /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0
> /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi
> /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl
> /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi
> /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl
> /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .)  
> at (eval
> 1) line 3, <STDIN> line 4.
> Perhaps the DBD::mysql perl module hasn't been fully installed,
> or perhaps the capitalisation of 'mysql' isn't right.
> Available drivers: DBM, ExampleP, File, Metadata, Multiplex, Proxy,  
> Sponge.
>  at ./createMYSQLtabledefn line 47
>
> I believe I have the bundle and module installed:
>
> [root at localhost Alsew]# find / -name mysql.pm
> /root/.cpan/build/DBD-mysql-2.9004/lib/DBD/mysql.pm
> /root/.cpan/build/DBD-mysql-2.9004/lib/Bundle/DBD/mysql.pm
> /root/.cpan/build/DBD-mysql-2.9004/blib/lib/DBD/mysql.pm
> /root/.cpan/build/DBD-mysql-2.9004/blib/lib/Bundle/DBD/mysql.pm
> /root/.cpan/Bundle/DBD/mysql.pm
> /usr/local/lib/perl5/site_perl/5.8.5/i686-linux/DBD/mysql.pm
> /usr/local/lib/perl5/site_perl/5.8.5/i686-linux/Bundle/DBD/mysql.pm
>
> I am able to access a mysql database elsewhere with Interchange. I  
> created a new
> mysql table, separate from products, and wanted to be able to display  
> that table
> in its entirety - I haven't figured it out, but thought that this  
> would be a
> step toward getting there.
> Any suggestions here?
>
>
>
> Stanley Major
> Calgary Ecommerce Services
> http://www.calgary-ecommerce-services.com
> Phone: 403-255-4701
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>



More information about the interchange-users mailing list