[ Previous ] [ Contents ] [ Index ] [ Next ]

Database Services in AOLserver

All of the functions to create and maintain database structures are accessible from the Database Services page, accessible from the AOLserver administration page.

To get to the administration page from AOLpress, select the Tools Administer Server menu item. To get the administration page from another browser, enter the URL http://hostname/NS/Admin. Select a server from the server selection dialog, and follow the Database Services link. The Database Services page, with the following hyperlinks to URLs for maintaining the database, is displayed:

Add New Table

Create a new table in the specified database pool (see page 191).

Customize Table

Tables can be customized with a table description, enabling or disabling links to update forms from search results pages, and specifying the number of sort levels on search forms (see page 194).

You can specify templates for the automatically-generated search, entry, update, and results forms (see page 196).

You can specify that the server automatically send mail when records are inserted, updated, or deleted (see page 200).

Columns can be customized with an index on the column, a column description, a default value, an input type, and a display type. You can also specify the order that columns will appear on the automatically-generated forms, or whether the column will appear on the form at all (see page 203).

Extend Table

Add columns to an existing table in the specified database pool (see page 207).

Drop Table

Delete a table from the specified database pool (see page 208).

Search a Table

Query a table in the specified database pool using the search form for the table (see page 209).

Enter Data into a Table

Add records to a table in the specified database pool using the entry form for the table (see page 210).

Set Permissions for a Database Table

Set table permissions for searching the table, entering a record into the table, accessing the table update form, updating a record, or deleting a record in the table (see page 210).

Get Table Schema

Download the SQL for the definition of a table (see page 212).

Get Data

Download the SQL INSERT statements containing the table data, or download the table data in CSV (Comma-Separated Values) format (see page 213).

Upload CSV data by visiting a CSV file (see page 215).

Execute Arbitrary SQL

Execute SQL statements by visiting an SQL file (see page 216).

Many of these links modify the structure of the associated database. By default, this is a privilege accessible only to the nsadmin user from the Tools Administer Server menu item. The nsadmin user controls who is allowed to use table maintenance operations. If you want to perform database maintenance operations, contact your AOLserver administrator.

Add a New Table

Most database forms used by AOLserver, whether they are automatically generated or customized, are based on a database table. To create a new table, select the Tools Administer Server menu item, follow the Database Services link, and click the Add New Table button.

Add a new table:
  1. From a Page window in AOLpress choose menu item Tools Administer Server. Choose your server from the dialog that appears. If it is not listed, type http:// followed by the name of the host where the server is running.

  2. On the Administration page, follow the Database Services link.

  3. Decide how many columns will be in the new table, and type that number into the field next to the Add New Table button. The default number of columns for a new table is 10.

  4. Click Add New Table. A database pool selection form is displayed.

  5. Select the database pool in which the table is to be created and click Create Table. A form allowing you to type in table information is displayed.

  6. In the Table Name field, enter the name you want to give the new table. The table name must not contain spaces or punctuation marks other than an underscore (_). The table name must also not be a reserved word in the database.

  7. For each column, enter the following information:

  8. When you have defined all the columns of the table, click the Create Table button to create the table. A page is displayed informing you that your table was created. It also contains a link to an entry form for the table, which you can follow to enter new records into the table.

    If your table name or column names contained illegal characters, they are changed or removed. (Spaces are changed to underscores, and other illegal characters are removed.) Table names or column names that were reserved words are changed also. You will receive a notice informing you that your table or column names were changed. You can keep the changed names or click Drop Altered Table to delete the table and try again.

Table Customizations

Database tables and columns can be customized through an HTML forms interface, available by following the Customize Table link on the Database Services Administration page. Note that the ExtendedTableInfo configuration parameter must be set to on to enable these table customizations. After selecting a database pool and a table to customize, a Customize Table page such as the one below is displayed:

The following sections describe the Customize Table features.

Table Description

The description for a table can be modified by editing the Description field of the Customize Table page and then clicking the Update button. The table description is used in place of the table name on any automatically-generated forms, except if you specify templates (see page 196), in which case you must provide your own title for the forms.

Links to Update Forms from Search Results

Check Yes or No in the field labeled "Enable update from search results" on the Customize Table page to determine whether a search results page for the table will contain links to update forms for each of the rows. Then click the Update button.

Note that update forms are only generated for tables that have primary indexes.

Delete Button on Update Forms

Check Yes or No in the field labeled "Enable delete from update form" on the Customize Table page to determine whether a Delete button will appear on update forms for the table. Then click the Update button.

Note that update forms are only generated for tables that have primary indexes.

Delete Button on Search Results

Check Yes or No in the field labeled "Enable delete from search results" on the Customize Table page to determine whether a Delete button will appear next to each table row on the search results page for the table. Then click the Update button.

Multiple Sort Levels for Search Results

Enter the number of Order By widgets you want to appear on search forms for the table in the "Number of order by widgets on search form" field on the Customize Table page. The default is 1 (one). If you specify a number more than 1, the first Order By will be used as the primary sort for the search results, and the second Order By will be used as the secondary sort, and so on. Then click the Update button.

Templates for Forms and Results

You can specify HTML templates for the automatically-generated search, entry, and update forms, and for the associated search, entry, update, and delete results forms. The server generates the complete form, including data, as before. You supply a "template" page with any HTML and graphics you want around the form and add a designator showing where in the page you want the generated form to appear. The server generates the forms dynamically and inserts them into your template page each time an automatic form is requested.

You can also specify the labels that will be used on the submit buttons for the search, entry, and update forms.

Note that the ExtendedTableInfo configuration parameter must be set to on to enable these customizations. Follow the Customize Table link from the Database Services Administration page, and choose the database pool and table you want to specify templates for. Then follow the Specify templates for forms and results link on the Customize Table page. The following page is displayed:

Fill in the fields on the page as described in the following table.

Field

Description

Entry Form Template

The URL of the HTML template for the automatically-generated entry form for the table.

The specified HTML page must contain the following tag to indicate where you want the generated entry form to appear on the page:

    <IMG SRC="/NS/Asset/entryform.gif">

Enter Button Label

The text you want to appear on the submit button for the entry form.

Entry Results Template

The URL of the HTML template for the entry results page. The specified HTML page must contain the following tag to indicate where you want the generated entry results to appear on the page:

    <IMG SRC="/NS/Asset/entryresults.gif">

Search Form Template

The URL of the HTML template for the automatically-generated search form for the table.

The specified HTML page must contain the following tag to indicate where you want the generated search form to appear on the page:

    <IMG SRC="/NS/Asset/searchform.gif">

Search Button Label

The text you want to appear on the submit button for the search form.

Search Results Template

The URL of the HTML template for the search results page. The specified HTML page must contain the following tag to indicate where you want the generated search results to appear on the page:

    <IMG SRC="/NS/Asset/searchresults.gif">

Update Form Template

The URL of the HTML template for the automatically-generated update form for the table.

The specified HTML page must contain the following tag to indicate where you want the generated update form to appear on the page:

<IMG SRC="/NS/Asset/updateform.gif">

Update Button Label

The text you want to appear on the submit button for the update form.

Update Results Template

The URL of the HTML template for the update results page. The specified HTML page must contain the following tag to indicate where you want the generated update results to appear on the page:

<IMG SRC="/NS/Asset/updateresults.gif">

Delete Button Label

The text you want to appear on the submit button for the update form.

Delete Results Template

The URL of the HTML template for the delete results page. The specified HTML page must contain the following tag to indicate where you want the generated delete results to appear on the page:

<IMG SRC="/NS/Asset/deleteresults.gif">

Note that once you enter any template URLs on this page, a View link will added to the page to allow you to view your templates. When you view your template, there will be a gif displayed to show you where the generated form or results page will appear. This is an example of how a template may look when you view it:

When the entry form is actually generated, the entry form will be substituted in the correct place in the template:

Automatic Mail

You can send mail automatically whenever a record is inserted, updated, or deleted from a table. Note that the ExtendedTableInfo configuration parameter must be set to on to enable these customizations.

Follow the Customize Table link from the Database Services Administration page, and choose the database pool and table you want to send automatic mail for. Then follow the Specify mail options link on the Customize Table page. The following page is displayed:

Fill in the fields on this page as described in the following table.

Field

Description

On insert send mail to

Email address to send mail to when a record is inserted into the table.

On insert send mail from

Email address to send mail from when a record is inserted into the table.

On insert send mail with subject

Subject line of mail message sent when a record is inserted into the table.

You can include the value of any table columns in the subject line by inserting:

#columnname#

where you want the value of the column to appear.

On insert send mail using template

The URL of the email template (with a .eml extension) for the body of the message sent when a record is inserted into the table.

The .eml file should contain the text of the message. If you want the values of any table columns to appear in the mail message, insert

#columnname#

in the template where you want the value of a column to appear.

On update send mail to

Email address to send mail to when a record is updated in the table.

On update send mail from

Email address to send mail from when a record is updated in the table.

On update send mail with subject

Subject line of mail message sent when a record is updated in the table.

You can include the value of any table columns in the subject line by inserting:

#columnname#

where you want the value of the column to appear.

On update send mail using template

The URL of the email template (with a .eml extension) for the body of the message sent when a record is updated in the table.

The .eml file should contain the text of the message. If you want the value of any table columns to appear in the mail message, insert

#columnname#

in the template where you want the value of the column to appear.

On delete send mail to

Email address to send mail to when a record is deleted from the table.

On delete send mail from

Email address to send mail from when a record is deleted from the table.

On delete send mail with subject

Subject line of mail message sent when a record is deleted from the table.

You can include the value of any table columns in the subject line by inserting:

#columnname#

where you want the value of the column to appear.

On delete send mail using template

The URL of the email template (with a .eml extension) for the body of the message sent when a record is deleted from the table.

The .eml file should contain the text of the message. If you want the value of any table columns to appear in the mail message, insert

#columnname#

in the template where you want the value of the column to appear.

Note that once you enter any template URLs on this page and click Update, a View link will be added to the page to allow you to view your templates. When you view your template, the contents of the .eml file will be displayed, along with the #columnname# designations to show where the values of columns will be filled in when the message is sent. This is an example of how a template may look when you view it:

This is how the mail message would look when it was sent:

Column Customizations

Database columns can be customized on the Customize Table page (available by following the Customize Table link on the Database Services Administration page). Note that the ExtendedTableInfo configuration parameter must be set to on to enable these column customizations. The bottom of the Customize Table page lists all the columns of the table and allows you to customize various column attributes:

Column Display and Ordering

The values in the left-most column of the display indicate how the fields will be ordered (or whether they will be displayed at all) in automatically-generated search, entry, and update forms. For each column, select a number to designate its ordering on generated forms, or select "Not Shown" to eliminate it from the forms. Then click Change Ordering. To reset the ordering to the previous values, click Reset Ordering.

Create/Drop Indexes

The right-most column on the display indicates whether there is an index on the field. If there is an index on the column, a link to Drop the index will be displayed. If there is no index on the column, a link to Create an index will be displayed. Follow the appropriate links to create or delete an index on a column. Creating and dropping indexes are only supported for Illustra databases.

Customize Column Page

Follow the link from the column name on the Customize Table page to go to another form that allows you to further customize the column:

The type of the field is displayed at the top of the form. After changing any of the column customizations on the page, click Update to save your changes. Click Reset Values to go back to the previous values.

Column Description

The description for a column can be modified by editing the Description field. The column description is used in place of the column name on any automatically-generated forms.

Default Value

The default value for a field can be modified by editing the Default Value field. For date, time, or timestamp fields, the appropriate widgets will be displayed to allow you to set the default. If you choose "Current", AOLserver defaults the time to the local time when it generates the form.

Input Type

The input type of a column determines what kind of values can be entered into that column and what kind of widget will be used to enter the values on automatically-generated search forms. The following table lists the possible choices for the Input Type field and describes what must be entered in the associated Input Type Data field.

Input Type

Input Type Data

Select

SelectOrNULL

The automatic forms will display a selection box containing valid values for the field.

The Input Type Data field must contain the list of valid values separated by spaces. Individual values containing spaces must be enclosed in single quotes. Any character, including a single quote ('), can be escaped with a backslash (\).

If you want NULL to be one of the valid values, use SELECTORNULL. Otherwise, use SELECT.

SelectSQL

SelectSQLorNULL

The automatic forms will display a selection box containing valid values for the field.

The Input Type Data field must contain an SQL SELECT statement. The SELECT statement must return only one column of rows, the values of which will be used as the valid values for the field.

If you want NULL to be one of the valid values, use SELECTSQLORNULL. Otherwise, use SELECTSQL.

Radio

RadioOrNULL

The automatic forms will display a radio field containing valid values for the field.

The Input Type Data field must contain the list of valid values separated by spaces. Individual values containing spaces must be enclosed in single quotes. Any character, including a single quote ('), can be escaped with a backslash (\).

If you want NULL to be one of the valid values, use RADIOORNULL. Otherwise, use RADIO.

Textarea

Input

The automatic forms will display a TEXTAREA or INPUT widget for the field.

The Input Type Data field can contain HTML options for the widget, such as ROWS=6 or COLS=40 for a TEXTAREA or SIZE=2 for an INPUT widget.

AutoIncr

The field in the automatic entry form will just display the text "Automatic Increment". When a record is inserted, the value for the field will be calculated to be 1 (one) greater than the maximum value in that column already. If there are no values yet in the column, the value for the field will be 1 (one). AUTOINCR can only be used with integer columns.

The Input Type Data field is ignored.

Money

The field in the automatic forms will be displayed with two places following the decimal. MONEY can only be used with columns of type real or integer.

The Input Type Data field is ignored.

Display Type

The Display Type for a column determines how the value in the column will be displayed on search results forms. If a column has a display type, the value is shown on the update form using the display type next to the input widget. The following table lists the possible choices for the Display Type field and describes what must be entered in the associated Display Type Data field:

Display Type

Display Type Data

mailto

The column value is assumed to be an email address, and the value displayed will be the address hyperlinked to the appropriate mailto URL. For example:

    <A HREF=mailto:bob@aol.com>bob@aol.com</A>

The Display Type Data field is ignored.

URL

The column value is assumed to be a URL and is displayed as a hyperlink. For example:

    <A HREF=http://www.aol.com/>
    http://www.aol.com</A>

The Display Type Data field is ignored.

ImageURL

The column value is assumed to be a URL pointing to an image and is displayed as an inline image. For example:

    <IMG SRC=
    http://www.aolserver.com/images/town.gif>

The Display Type Data field is ignored.

Record

The column value will be used to identify a row in a table. The Display Type Data must consist of a table name and a column name separated by a space. A row will be selected such that:

SELECT * FROM tablename WHERE columnname = the value in the column

The field for the column in the automatically-generated forms will contain a link to the selected row.

Money

The column value will be displayed with two places following the decimal. MONEY can only be used with columns of type real or integer.

The Display Type Data field is ignored.

Extend a Table

If you create a table initially with too few columns, or you need to add more columns to a table later, you can extend the table. To extend an existing table, select the Tools Administer Server menu item, follow the Database Services link, and then follow the Extend Table link.

Extend a table:
  1. From a Page window in AOLpress choose menu item Tools Administer Server. Choose your server from the dialog that appears. If it is not listed, type http:// followed by the name of the host where the server is running.

  2. On the Administration page, follow the Database Services link.

  3. Follow the Extend Table link. A database pool selection form is displayed.

  4. Select the database pool in which the table resides and click Extend Table.

  5. Select the table from the Table to extend selection box.

  6. Type the number of columns you want to add in the Number of new columns field.

  7. Click the Extend button. A form appears listing all the columns currently in the table at the top. The bottom of the form allows you to define the new columns.

  8. Define each column by giving it a Name and a Type, following the same guidelines used when creating a new table. You can also add an index for the column by checking the Add index box.

  9. Click Extend. A confirmation form is displayed informing you that your table has been extended and providing a link to the entry form so that you can enter new records to the table. As with creating a table, any illegal column names are automatically changed, and you are given the opportunity to either accept the changes or try again.

Drop a Table

Dropping a table deletes the table structure within the database and all of the data in the table. To drop a table you just select it from a list of tables and confirm the operation. To delete an existing table, select the Tools Administer Server menu item, follow the Database Services link, and then follow the Drop Table link.

Delete a table:
  1. From a Page window in AOLpress choose menu item Tools Administer Server. Choose your server from the dialog that appears. If it is not listed, type http:// followed by the name of the host where the server is running.

  2. On the Administration page, follow the Database Services link.

  3. Follow the Drop Table link. A database pool selection form is displayed.

  4. Select the database pool in which the table resides and click Drop Table.

  5. Select the table from the table selection box and click Drop Table. A form is displayed asking you to confirm the delete operation.

    You're asked for confirmation since deleting is a destructive operation for which there is no "undo." If you accidentally remove a table that you want to restore, see your AOLserver administrator. If the administrator has implemented proper backup procedures, you can probably get your table and all of its contents restored.

  6. Click Yes, Really Drop if you're sure you want to delete the table and all of its data.

Search a Table

You can search for records within a table and then update the records you retrieve. (Refer to "Automatic Forms Generation" section for detailed information on search, entry, and update forms for database tables.) The results page for your search contains links to the update form for each record retrieved. To search an existing table, select the Tools Administer Server menu item, follow the Database Services link, and then follow the Search a Table link.

Search a table:
  1. From a Page window in AOLpress choose menu item Tools Administer Server. Choose your server from the dialog that appears. If it is not listed, type http:// followed by the name of the host where the server is running.

  2. On the Administration page, follow the Database Services link.

  3. Follow the Search a Table link. A database pool selection form is displayed.

  4. Select the database pool in which the table resides and click Search.

  5. Select the table from the table selection box and click Search.

  6. The search form for the table is displayed. Enter your search criteria as described in "Search Forms" section and click Search. A results page is displayed. The top of the page shows your search criteria, and the bottom of the page is a table displaying the results of the search. Each row in the table contains a link to the update form for the record.

Enter Data into a Table

You can insert new records into a table using the entry form for the table. (Refer to "Automatic Forms Generation" section for detailed information on search, entry, and update forms for database tables.) The results page contains a link to the update form for the new record. To enter data into a table, select the Tools Administer Server menu item, follow the Database Services link, and then follow the Enter Data into a Table link.

Enter Data into a table:
  1. From a Page window in AOLpress choose menu item Tools Administer Server. Choose your server from the dialog that appears. If it is not listed, type http:// followed by the name of the host where the server is running.

  2. On the Administration page, follow the Database Services link.

  3. Follow the Enter Data into a Table link. A database pool selection form is displayed.

  4. Select the database pool in which the table resides and click Enter Data.

  5. Select the table from the table selection box and click Enter Data.

  6. The entry form for the table is displayed. Enter data as described in "Entry and Update Forms" section and click Enter Record. A results page is displayed containing links to the update form (to update the new record), the entry form (to enter another record), and the search form (to search the table).

Set Table Permissions

You can set table permissions for the following table operations:

Searching the table
Entering a record into the table
Accessing the table update form
Updating a record in the table
Deleting a record in the table

You can access the table permissions page from any of the following places:

The table permissions page contains a number of links to let you set permissions for any of the table operations:

If you follow any of the links, the permissions page for the Method URL controlling that table operation is displayed. You can then set permissions for that table operation as you would set permissions for any other URL. The permissions page for the Execute Search operation for a table is shown below:

Download SQL for Table Definition

AOLserver can generate the SQL statements (DDL) that define a table. You can use the SQL statements to re-create the table in another system. You may want to move the SQL statements to another machine and execute them as described in the "Execute SQL" section.

Download SQL Table Definition
  1. Follow the Get Table Schema link on the Database Services Administration page.

  2. Choose the database pool where the table resides, and then choose the table you want. If the pool is defined to use ExtendedTableInfo (see the ExtendedTableInfo configuration parameter), you can specify whether table customizations should be included in the SQL statements after the CREATE TABLE statement.

  3. Choose "Yes" or "No", depending on whether you want the table customizations included in the generated SQL, and click Get Table Schema. A file selection dialog is displayed, allowing you to specify the location and filename in which to store the SQL statements. The default filename is tablename.sql.

Note that AOLserver cannot detect whether fields are unique or nullable in all situations. In those situations, the unique and/or nullable designations will not be output in the SQL statements generated using the above steps.

Download Table Data

You can download table data in either CSV (Comma Separated Values) or SQL format.

Download Table Data:
  1. Follow the Get Data link on the Database Services Administration page.

  2. Select the database pool from which you want to download data and click Get Data.

  3. Choose the table from which to download data and the output format. Then click Get Data:

    CSV (Comma-Separated Values) format. The table data will be output as a list of comma-separated values. The CSV format is supported as in import format by several applications, including Microsoft Excel.

    The column values are surrounded by double quotes ("). If any of the column values contain a double quote, it will be represented as two double quotes ("") in the CSV file. If you then upload the CSV file, any sets of two double quotes within a value will be read as one double quote.

    If you choose CSV format, you will have the option of whether the first row of the file will contain column names or not. You may want to upload the resulting data file into another AOLserver as described in the "Upload Table Data" section.

    SQL INSERT statements. The table data will be output as SQL INSERT statements. If you want to load this data into another database, you may need to create a table for the data in the other database or change the table name and column names in the SQL file to match a table in the other database. This SQL file can then be executed as described in the "Execute SQL" section.

  4. Choose a file to contain the table data in the file selection dialog and click OK.

Upload Table Data

You can upload table data simply by visiting a CSV file on the server.

Upload Table Data:
  1. Visit a CSV file on the server, for example:

        http://www.myserver.com/testdata.csv
    

  2. The contents of the CSV file are displayed. If there are more than 50 lines in the file, only the first 50 lines are displayed, and you are given the option to download the entire file to view it.:

  3. Choose the database pool in which to upload the data and click Upload.

  4. Choose the table in which to insert the data and click Upload.

  5. The next page displayed gives you several options on how to upload the data.

  6. Select the appropriate option according to whether the First line of file is data or First line of file is column names. If the first line of the file is column names, it will not be uploaded into the table.

    Select the appropriate option according to whether you want to Add data in file to data in table or Delete data in table before inserting from file. If you choose to delete all the existing data in the table before inserting the values in the file, you will be asked to confirm the operation before the data is deleted.

    You can optionally choose to download the entire file.

    The table at the bottom of the page allows you to select the table column that the values below it will be inserted into. Note that even if the first line of the file is column names, you must still select the correct column name from the select box at the top of each column of data.

  7. When you have specified all the upload options you want, click Upload to upload the data. Empty strings are inserted into the table as NULLs. For Illustra database pools, the upload is performed within a transaction. If any errors occur during the transaction, the transaction will be aborted.

Execute SQL

You can execute SQL statements within the HTML forms interface in two ways:

Top of Page

[ Previous ] [ Contents ] [ Index ] [ Next ]
Copyright © 1996 America Online, Inc.