[Date Prev][Date Next][Thread Prev][Thread Next][Interchange by date
][Interchange by thread
]
[ic] creating a new SQL table or adding field to products table
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
_______________________________________________
interchange-users mailing list
interchange-users@interchange.redhat.com
http://interchange.redhat.com/mailman/listinfo/interchange-users