[ic] TIPS: Adding a new mysql table to IC

[Zack Johnson]

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.

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.

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.

Good luck!

Zack Johnson
Internet Manager
Standard Printing & Web Center
St. Augustine, FL 32084
phone (904) 829-6488
fax (904) 824-2959
www.standardprinting.net