[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