Use of the database through a browser

All interface with the databases may be performed through the user's favorite browser.
The user may connect to the database either through the database manager or (more often) by requesting connection on the host and port where the database resides. The HTTP Port of the database is known to the database manager at creation time and may be edited at any time. See also per-database parameters and the Database Manager.

After requesting connection to a database, the user will be presented with a display similar to the one of caption interf-1 (depending on the browser). This is the main screen from which all activities about the database may be started. This welcome screen is a default page used for databases that do not have a customized initial page. Users may not edit this screen, but they may create their own index.html (or index.htm) on the database documents (docs) directory.

Among other selections, there are two, specifically SQL Terminal and Administrator's Console, which lead to user and administrator interface respectively.

SQL Terminal - User Interface

When clicking on the SQL Terminal selection, an authentication dialog box appears on screen, requesting user name and password. The only initial user is the administrator, therefore initial user name is admin and initial password is pegasus.

A form, similar to caption interf-2, will appear on screen.

Description of the SQL Terminal screen

The SQL Terminal page is not a physical HTML file. It is server code which responds as a URL. See also Special URL addresses in the HTTP server.

Home:  Leads to the Welcome page, which is the home page for the database interface.

Roadmap: Leads to a pictorial database map (roadmap), where one may see the users, tables and views of the database. This is a handy tool, since both user and system tables are visible.

Console: Leads to the administrator's interface console. This link is visible only when the initial login is done with the administrator id and password. It is not accessible to other users.

Disconnect: Disconnects the user from the database. Disconnecting from the database does not stop the base. This can only be done through the administrator's console or through the database manager screen. See also Disconnecting and Reconnecting to the database.

The main portion of the SQL Terminal screen is a scrollable window, where the commands to the database may be typed. Any command may be executed at this point (with the exception of address assignment to BLOBs, which must be done by the sqlapp4 application program terminal). Except for Bulk updates,  there is no need to add a semicolon (;) at the end of the statement. The user will simply click on the Execute button.

The buttons at the bottom of the screen window allow basic operation and control of the amount of output. They also provide a means for execution of SQL script files.

Specifically, the buttons provide the following facilities:

Execute button: Executes the command that is typed on the screen window.

Clear button: It clears the screen window.

Rowset size box: Setting this size allows the user to control the amount of returned output. By default the number of returned rows is 20, since this is about as much as may be displayed at once on a standard screen without the need of moving too much up and down to locate a row. This number may be modified, according to the user's needs. If the number of rows selected is more than 20 (or more than the current rowset), output control buttons appear at the bottom of the screen. See Output control buttons.

Fetch all box: If this box is checked, then all output is displayed at once on the screen and the rowset size is ignored.

Note! The control buttons and the rowset size are only used to control the appearance of the output. The operation of fetching the data is independent of the number of rows displayed.
 
Captions interf-3 and interf-4 show the screen window before and after executing an SQL command.

Output control buttons

Whenever some rows are displayed as output, the output control buttons appear as shown in caption interf-4.
The command typed on the SQL Terminal is select * from alpha, where alpha is a table containing furniture names and prices.

Previous: Displays the previous N rows.

Next: Displays the next N rows. If the output is the last set of the selected rows, then Next button does not appear.

First: Displays the first N rows.

Last: Displays the last N rows.

Commit button: All modifications are commited and thus made permanent to the database. Warning! It is necessary to click on Commit before closing the connection. Unlike the sqlapp4 program, where committing on exit is the default, the SQL terminal closes the connection without doing so (and without warning).

Rollback: All uncommited updates are rolled back and undone.

Browse box: It is possible to execute an SQL script that is written in a file. The user may type the file name in the box, or may use the browse button to select the file from the directory tree.

Upload button: Begins the execution of the SQL script file. The results of the execution appear on the screen in the same manner as if the commands had been issued from the screen window. The only difference in this case is that the commands of the script file are not included in the terminal window History, since they were not typed in it.

After submitting the first SQL command, the connection to the database is established. One more line of selection lists appear on the window. These are the History list, the Isolation level list and the Update mode list.

Command History list: The history of previously issued commands appear in this list. The user may choose one of the previously typed commands and execute it again, or edit it.

Isolation level list: The isolation level of the transaction. One of the four isolation levels, i.e Read Uncommited, Read Commited, Repeatable Read and Serializable are available for selection. For more details on Isolation levels, see the The Lock Manager.

Update mode list: The transaction mode may be set either to Read/Write or to Read Only.
 

Bulk Updates
To speedup execution, when a number of updates are required (i.e. insert, delete, update) a bulk update mechanism is provided. Each command is typed between the begin  - end delimiters and all commands are sent together for execution at the end via the Execute button. Users should make sure that their text total is no longer than 64K. Bulk update commands must be delimited by semicolon, begin keyword included.

Example:
begin;
insert into products values (1, 'Table', 100);
...
insert into products values (100, 'Armchair', 230);
end

These commands are executed together in a bulk insert. Of course, no select commands may be executed in bulk.

Warning! DO NOT attempt to update system tables from SQL. System tables are visible, but they are not to be updated from SQL. Users should not alter the system table data. In case of updating a user (name, privileges, password etc.) administrators should use the update user command, which is specifically designed for this purpose. See Appendix SQL: update user.

Disconnecting and Reconnecting to the database

When the Disconnect link is pressed, then the user is disconnected from the database, but the SQL terminal window remains open. The Isolation mode and Transaction mode lists disappear and only the History list remains on the screen. The user may reconnect to the database (without the need to type again user name and password), by selecting a command from the History list and submitting it for execution.

Warning! Commit is not automatic on Disconnect. The user must click on the Commit button to make the updates permanent before attempting to disconnect. Otherwise, a rollback is performed automatically and all the connection updates are lost.

Roadmap Description

The Roadmap is a graphical tree-like display of the database catalog contents. In addition to the graphical interface which makes information more comprehensible, it is also a special facility that is used to display information about all database objects, including system tables, users and views. This tool, among others, makes Ovrimos an extremely user and developer friendly environment.

A page like the one of caption interf-5 is displayed when the Roadmap is selected.

The first screen of the Roadmap contains links to: Home, SQL Terminal, Login as another user, Users, Tables and Views. Some of this information may be different depending on the user privileges. In the following description, whenever a difference exists, it will be noted in the appropriate paragraph.

Home: Leads to the first connection screen, which is the home page for the database interface.

SQL Terminal: Leads to the SQL Terminal to allow execution of SQL statements.

Login as another user: Allows the user to login as another database user. This link appears only when a user does not have administrator privileges, since the roadmap is then limited. If login as another user is clicked on, a message about failed authorization is issued.
Should the user press Retry, the authorization dialog box appears to allow logging in as another user.

All three roadmap issues extend to tree structures, where the + symbol indicates that expansion is possible, while the - symbol indicates that this branch may be collapsed.

Users link: Transfers the user to the place of the page where the Users branch is analyzed. This link is visible only to users with administrator privileges, since casual inspection of users is not allowed to all database users.

Tables link: Tranfers the user to the place of the page where the Tables branch is analyzed.

Views link: Transfers the user to the place of the page where the Views link is analyzed.

Users: The Users image is a utility that displays information about the database users.

Caption interf-6 is an example of the expanded users' information.

For every user, the following data are supplied:

Id: The user's ordinal number.

Proto ID: The prototype ID, according to which privileges are granted. In the above example, the ADMIN has prototype ID 0 and user PANDORA, who has administrator privileges has the same prototype id. There is also a user PUBLIC with NULL password. This user cannot connect, but may be used as a prototype for easier granting of privileges to other users.

Name: The user's name, required for login.

Full Name: User's full name, required to provide more information to the administrator.

Remarks: Any comments about the user, inserted at creation time or added later, are displayed here.

More on users and user administration on Privilege Inheritance.

Tables

The Tables roadmap is a utility offering graphic information about the tables, columns and attributes of each table.
The tree structure extends to any depth the user desires, until the fine details of each table column. Of course, only information about the structure of the tables is displayed and not table data, since this may be sensitive information and requires the appropriate privileges.

Caption interf-7 shows a fully expanded tree on table sys.tables. The rest of the tables appear also, but their details are not displayed.

In the following paragraph, the caption is explained in detail on purpose. After describing one system table, users of the roadmap will be able to decipher easily the meaning of information displayed about any table.

Tables  (The table name)
 
 

Id	Table id is a small integer, e.g. 1
Owner	The owner is a small integer. In case of a system table, it corresponds to the administrator id
Type	System table, simple (user) table or temporary table
Check	Possible constraints

Columns of the table
All columns (names, types and constraints) of the table are displayed:
 
 

table_id	smallint	not null
owner	smallint
table_name	varchar(60)	not null
table_type	tinyint	not null
check_constr	varchar(128)

Indices  (Keys)
Here, the Name corresponds to the key name defined when the key was described and attached to the table as index. The Unique field indicates whether the column will be unique or not, while the Definition is the column name(s) of the key.
 
 

sys_tables_k1	unique	table_id
sys_tables_k2	unique	table_name

In the above example, table sys.tables has two indices, table_id and table_name. Both keys are unique.

Foreign_keys
The foreign keys are table columns of the table referencing columns in other tables.
 
 

owner

sys.users(user_id)

In the example, column owner references the column user_id on table sys.users.

Imported_foreign_keys
Imported foreign keys are descriptions of table columns that point to the particular table. The reference is the column of the table to which each column points.
 
 

sys.columns(table_id)	table_id
sys.table_privileges(table_id)	table_id
sys.keys(table_id)	table_id
sys.foreign_keys(table_id)	table_id

In the example above it happens that all the system tables that point via a foreign key to sys.tables do that pointing to the column table_id.

Views

Simpler than the Tables tree, the Views tree shows the existing views.
 

Previous Chapter: The Database Manager

Next Chapter: Administrator Interface