Use the generic-db CGI to create and maintain database tables using a web interface. Access to each table is controlled by username or by CUNIX group. You create web forms that allow specific users to add data or display the data in your tables. You decide who has read access and who has insert, update, and delete access to each table. Users of the generic-db CGI authenticate to the secure web servers administered by CUIT using their UNI and password so they don't need a separate username for generic-db. Only people with a UNI (Columbia students, staff, and alumni) can access the generic-db CGI. Tables are created on the Oracle database server which is administered by CUIT and backed up daily.
This tutorial will get you started creating and modifying tables on the Oracle database server. The best way to learn about generic-db is to start using it. Have fun.
The table name must start with a letter. It can contain letters and numbers and underscores. For example: astronomy_club. When you create the table your UNI will prepended to the table name automatically, for example: abc2112_astronomy_club.
You need to decide on the record layout for your table. Here is an example:
record_id integer unique not null, firstname varchar2(50) not null, lastname varchar2(50) not null, email varchar2(100), cell_phone varchar2(50), office_phone varchar2(50), office_hours varchar2(50), member_since date, monthly_dues number, last_modified date
The only way to change the record layout is to drop the table and create it again with the new layout.
Every table will contain a record_id field which is automatically defined as the first table field and automatically maintained by the generic-db CGI. You don't need to include this field in your record layout since it is added automatically. The user can't modify the contents of this field.
If your table contains a field called last_modified that field will automatically be updated by generic-db with the current date each time a record is added or modified. The user can't modify the contents of this field.
Go to the generic-db CGI and click on Create a new table. Fill in the table name and the record layout. You can leave the rest of the fields empty for now. Click the Create a new table button to create the table. If you get an Oracle error message then you probably made a syntax error in the record layout. Read the message to find out what's wrong. Table names and field names are limited to a certain length. Field names can't be the same as reserved words (e.g. "size" and "date" are not valid field names). Hit the browser's Back button, fix your mistake, and click the Create a new table button again.
When the table has been created you will see a confirmation meesage. Click Back to main menu at the bottom of the page.
Click add record to table and then do View->Page Source from the browser menu. Save the web form and edit as desired then save it in a protected directory on the secure web server to make it part of your web site. See below for information about protected directories on the secure web server. Users can add records to your table using this form.
Note: the add record form includes the azrc command
<input type=hidden name=cmd value="azrc">
Here is a sample link with a URL that will display the contents of the abc2112_astronomy_club table.
<a href=https://www1.columbia.edu/sec-cgi-bin/cuit/generic-db/generic-db.pl?cmd=shtb&table=abc2112_astronomy_club>display astronomy club table</a>
You can copy and paste this link into your web page, and then replace abc2112_astronomy_club in the URL with your table name. Users with select permission (read access) can then click on the link to see the contents of the table.
Note: the show table URL includes the shtb command
The shtb command will normally sort by the record_id field. You can add the sort option to select a different sort order. If you add "&sort=3" to the URL then the results will be sorted by field 3 which is the third field after the record_id.
The shtb command will normally display all records in the table but you can refine the table search using a where clause. Here are some examples of valid Oracle syntax for a where clause:
dept_code='9002' dept_code is not null dept_name='English Lit' and valid='Y' dept_name like 'English%'
There are several ways to specify the where clause.
CUIT's authentication system requires each user to login with their UNI and password before accessing a protected resource. In order for generic-db to work properly the user must login before they enter data into the form when adding a new record. The only way to ensure that the user has logged in before typing their data is to put your "Add a New Record" web form on the secure web server in a protected directory. If you don't already have a directory on the secure server you can send mail to webmaster to request one. The path to your secure directory will start with /wwws/data instead of /www/data. For example your secure web directory might be /wwws/data/cu/astronomy. In that case you would create a protected subdirectory called forms by typing these commands:
$ mkdir -m 2775 /wwws/data/cu/astronomy/forms $ cp -p /wwws/data/cuit/generic-db/.htaccess /wwws/data/cu/astronomy/forms/.htaccess
Use your directory name instead of astronomy. Put all your web forms in the forms subdirectory.
The table owner can decide who has permission to select, insert, update, and delete table records. Access permission can be modified by the table owner at any time. Access can be granted to individuals or to groups. The table owner automatically gets full access to the table so the owner need not be included in the access lists.
From the main menu click modify table permission to assign table permission.
Users with Select Permission. Enter a list of UNIs in this field to give those users read access to your table. Enter the keyword any-valid-user in this field to allow select access to anyone with a valid UNI.
Groups with Select Permission. Enter a group name such as CUNIX_astronomy in this field to allow read access to everyone in that group. For more information about cunix groups see the online documentation regarding cunix groups. After you create a new group it takes some time (perhaps an hour or more) before the web server will recognize it.
Users with Insert Permission. Enter a list of UNIs in this field to give those users permission to insert new records into your table. Enter the keyword any-valid-user in that field to allow anyone with a valid UNI to insert new records.
Groups with Insert Permission. Enter a group name such as CUNIX_astronomy in this field to allow everyone in that group to insert new records.
Users with Update Permission. Enter a list of UNIs in this field to give those users permission to modify (edit) existing records in your table. Enter the keyword any-valid-user in that field to allow anyone with a valid UNI to modify existing records.
Groups with Update Permission. Enter a group name such as CUNIX_astronomy in this field to allow everyone in that group to modify (edit) existing records.
Users with Delete Permission. Enter a list of UNIs in this field to give those users permission to delete records from your table. Enter the keyword any-valid-user in that field to allow anyone with a valid UNI to delete records.
Groups with Delete Permission. Enter a group name such as CUNIX_astronomy in this field to allow everyone in that group to delete records.
Only the table owner can change the table permissions or drop the table.
If you would like your users to see a customized page after they add a new record you can create a confirmation page. It will be invoked automatically if it has the same name as the "Add a New Record" form but with confirm.html at the end. For example if you have a web form called /wwws/data/cu/astronomy/forms/addstudent.html and a confirmation page called /wwws/data/cu/astronomy/forms/addstudentconfirm.html then the confirmation page will be displayed after a new record is added using the addstudent.html web form. Otherwise the normal confirmation message will be displayed.
If you would like to get mail each time a new record is added to your table you can add the maildest and mailfields variables to your web form. You can put a list of email addresses in the maildest variable. You can also put a list of fields in the mailfields variable, and those fields will appear in the email message. For example:
<input type="hidden" name="maildest" value="rr2049@columbia.edu,mlc2144@columbia.edu"> <input type="hidden" name="mailfields" value="description">
Only mail to address@columbia.edu or address@barnard.edu will be accepted.
Last modified Sep 5 2006
Ben Beecher <beecher@columbia.edu>
ID Management Group
CUIT