Cy Think Survey

Programmer's Procedure to Add a Database Table  

A database table is first added to the database on a single client website. The table is then copied to the other client websites. See this link.

Programmer's Procedure to Copy a Database Table

Adding a table also involves changing the documentation and program modules. This usually involves major changes to the function of the overall system. Since this involves changes in the database and the perl program modules, this procedure is aimed at programmers.

It is important that all these steps be done more or less at the same time. This makes the entire process less error prone. The documentation must be updated to reflect the new features and how to use them. And, the code must be implemented.

The procedure to add a table is similar from table to table. This is the procedure.

1. Decide on the table name.
   
   
2. Access phpMyAdmin in the host control panel: Login >> MySQL Administration >> phpMyAdmin.
3. Verify the table name is not being used by checking the list of tables on the left hand side of the page.
4. Click the database name at the top of the list of tables.
5. Enter the table name and number of fields in the form at the bottom of the page. Click Go.
6. Enter the field names and field lengths in the form. The name of the first field is always "mykey". Enter a short description of the table in the Table comments field. Click Save.
7. Create appropriate indexes.
   • Click on the key icon next to mykey to set the primary key.
   • Create additional indexes, as needed, by clicking the "index" icon.
8. Make any corrections needed. Click the table name to compare the new table with any pre-existing tables in the database.
9. Use the Print View button to print the table structure.
10. File the print of the table structure in the documentation binder.
11. Export the tables structure (and data) to D:\CyThink\coreCMS\sql. This will save time later.
    • Click the Export tab.
    • Uncheck Data if exporting only the structure.
    • Check the Save as File checkbox.
    • Click Go.
12. If needed, add new codes to D:\CyThink\coreCMS\sql\code_add_codes.sql. Include codecode as well as actual values when appropriate. (These all have keys < 200.)
13. If you have new codes, copy them into a separate file that incorporates the table name, for use later. E.g. see the file D:\CyThink\coreCMS\sql\code_add_link_codes.sql.
14. If you created a .sql command file, use it to add new parameters to the code table.
15. Close the phpmyadmin window. Log out of the Control Panel.
    
    
16. Decide on the name for the data tier program module. This should normally be the table name preceded by "Jsh", all lower case. E.g. if the table is "abc" the module name will be Jshabc.pm.
17. Locate a similar module in D:\CyThink\coreCMS. Open it and save it in the client's site folder with the chosen module name.
18. Change the package name, at or near line 1.
19. Change the value for db_table, at or near line 30.
20. Change the sub prefixes to match the table name. E.g. change link_ to abc_.
21. Change the get_field sub names to match the table and fields. E.g. the sub that gets the xyz field from the abc table row is named abc_xyz.
22. Add additional get_field subs, as needed. Verify the values for index in these subs.
23. Edit the comments for the get_field subs.
24. Change the field names in the SQL in the abc_list_rows sub to reflect the field names in the new table.
25. Add the show_... parameter for the table to the Parm module, e.g. add "show_link". See this link.
    Programmer's Guide to Parameters
    
    
26. You are now ready to create the application tier module. See this link.
    Programmer's Guide to Application-Layer Programs
    
    

This procedure is abbreviated and high level. It is documented to prompt the programmer as to the needed steps and is not intended to be a detailed step-by-step procedures.