[ic] creating a new SQL table or adding field to products table

[Bob Ramstad]

Zack Johnson wrote a good TIPS file which someday should become part
of a newbie document on how to create an entirely new SQL table.  The
techniques work fairly well for adding fields to the product table
too.  I've made some comments on it below with leading *** symbols.

1. Design your table.  Avoid using a tool.  Rather, write your table
definition by hand.  If you do use a tool (like phpmyadmin), dump the
structure to a text file.  You will need this later.

    For example:
        CREATE TABLE foo
        (
            id INT(3) AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(64) NOT NULL,
            atomic_number TINYINT UNSIGNED NOT NULL
        ) ;

    Watch out for those commas.  Of course, I've never wasted hours
    debugging table definitions that were simply missing commas, or
    had misplaced semi-colons instead.

    Goto http://www.mysql.com for more information.

2. Run your statement above using the mysql client, or phpmyadmin, to create
the table if you haven't already.

** Not entirely necessary, but a good thing to do to make sure that
   the table definition doesn't have any stupid syntax errors.  In
   general, I find that I like having <catroot>/dbconf/mysql/foo.txt
   correct and letting Interchange create the table.  Easier for
   documentation if nothing else.

3. Create and open a file in <catroot>/dbconf/mysql/foo.txt

4. Type in the following information where appropriate:

    Database  foo  foo.txt __SQLDSN__
    ifdef SQLUSER
        Database  foo  USER         __SQLUSER__
    endif
    ifdef SQLPASS
        Database  foo  PASS         __SQLPASS__
    endif

5.  Next, add a line following the pattern below.  Use the field definitions
from step 1.  Just add an equal sign (=) after the field name.  'foo' is the
name of your table. The first and third columns are the same from table to
table.  No commas this time.

    Database  foo  COLUMN_DEF   "id=INT(3) UNSIGNED AUTO_INCREMENT PRIMARY
KEY"
    Database  foo  COLUMN_DEF   "name=VARCHAR(64) NOT NULL"
    Database  foo  COLUMN_DEF   "atomic_number=TINYINT UNSIGNED NOT NULL"

    Watch out for those quotes.  Don't waste hours on missing quotes.  Not
that I've done that sort of thing. . .

6.  Restart IC.  Your table should now appear under Administration > Tables.

** From what I can tell, IC assumes that <catroot>/products/foo.txt
   exists, and looks there for initial data to import.  

** BIG IMPORTANT NOTE ** If <catroot>/products/foo.txt exists, and
   <catroot>/products/foo.sql does not, Interchange will drop and
   create the table before doing the import.  However, it will OMIT
   any and all fields that are not included in foo.txt -- at the very
   least, it needs to have each of the fields included in the first
   line separated by tabs.  Note that this means that any INDEX
   statements on columns that are not created will fail!

** Doug Alcorn brought up this detail, and it's true, even in 4.8.3.
   Interchange seems to cross reference between the column definition
   file and the data file, and will only include "used" columns in the
   table it creates.

7.  To add labels, goto Administration > Tables > mv_metadata (at the bottom
in small type).

8.  Select 'New Entry'

9.  In the first field, labelled 'Table::Column', enter the table name by
itself.

        e.g. 'foo'

10. Enter the label you would like for the table in 'Variable Name'

11. Click 'OK'

12. Apply Changes.

You can now use mv_metadata to do all kinds of neat stuff.  You might want
to create mv_metadata entries for all of your fields in 'foo'.  This would
let you configure filters to remove malicious code, etc., among other
things.

** mv_metadata is pretty nifty.  If you are modifying the product
   table and adding columns, as per other posts here, after defining
   entries for each new column, go to Admin->Items then click on Item
   Editor (just above the search box).  "Fields to edit" controls
   which fields are displayed in the admin editor for the items.

**********************************************************************

Here is an example from my own (recent) experience.

I am using foundation as the basis for a new catalog.

I have tab delimited product data from another source which I want to
use to replace the fake tools data in foundation.

My tab delimited data includes fields which foundation doesn't
include, so I need to add them.  

As per instructions elsewhere, I decided to leave the columns in
foundation alone and add new columns even though many of the
foundation columns are deprecated.

I prefer to have Interchange create the table, so I skipped to step 3
above and edited <catroot>/dbconf/mysql/products.mysql and added the
new columns to the end:

Database  products  COLUMN_DEF   "orderable=CHAR(3) DEFAULT 'NO' NOT NULL"
Database  products  COLUMN_DEF   "type=CHAR(4) DEFAULT 'PAGE' NOT NULL"
Database  products  COLUMN_DEF   "options=VARCHAR(255)"
Database  products  COLUMN_DEF   "contents=VARCHAR(255)"
Database  products  COLUMN_DEF   "source=VARCHAR(32)"

Remember if using emacs to remove any ~ (autosave) files as there
should be only one file in this directory that starts with
"products." -- otherwise there might be issues with Interchange
finding the wrong file.

My tab delimited product file only included a few of the columns
required by foundation.  I opened up my editor and took the first line
out of the current <catroot>/products/products.txt and placed it at
the top of my tab delimited product file.  This first line was then
edited so that the columns that were included in my data file all
appeared first.  Here are the first couple of lines from my
products.txt file:

sku	source	description	orderable	price	options	type	contents	comment	nontaxable	title	template_page	thumb	image	wholesale	prod_group	category	tax_category	weight	size	color	gift_cert	related	featured	download	dl_type	dl_location	inactive	url	sale_price	image_large
AT-INSPCT	at-103	Inspected By #47 T-Shirt	YES	14.49	"Size" "XL" "L" 	ITEM		Marge is in charge! Marge is #47 at Acme Rubber Company and she takes her job seriously. Back shows <a href="/images/at-103-a.gif">six hilarious scenes</a> of Marge putting a sample through the approval process, front has logo over the right breast. 100% cotton. <B>Sizes:</b> X-Large, Large.<BR> <B>Unit size:</b> One T-shirt.<BR clear="all"><P> <I>The ranch hands been tryin' to git Marge to come on over to inspect fer us, but those rascals at Acme gave her stock options, dang blast it!</i>	0

The last field that I'm actually using is "nontaxable".  Interchange
doesn't require that each line be padded with tabs at the end, which
is nice.  One thing which did bite me in the butt is that price MUST
be defined, otherwise Interchange will not import the line.

Remove <catroot>/products/products.sql and restart IC.  IC drops the
existing products table, creates the new products table and imports
the data, then creates <catroot>/products/products.sql once the import
is successful so that future restarts do not do the same thing.

Step 7-11 should be done for each of the new columns, then the columns
can be added to the item editor.  It's also pretty easy to delete the
columns that aren't being used from the item editor at this point too.

Complicated, yes, but it is easier to do than it sounds.

-- Bob