FrontBase Documentation

Backtrack:
  Welcome!
    4. Administering a FrontBase Server
      4.3. Adminstration Tools
        4.3.1. FBManager
Updated: 20-Nov-2000
prev — next
Table of Contents

4.3.1.3. Editing Databases

Transactions and why you should worry about them

FrontBase is 100% transaction-oriented, which is your protection against corrupted data and unwanted conflicts stemming from multiple users updating the same tables at the same time. The deafult "transaction mode", which FBDatabaseEditor uses, is called SERIALIZABLE READ WRITE PESSIMISTIC. This means that the isolation level is SERIALIZABLE, the access mode is READ WRITE, and the locking discipline (a FrontBase extension to SQL 92) is PESSIMISTIC.
More generally, this transaction mode means that access to tables etc. is effectively serialized, i.e. if you want to access a particular table which is currently participating in another transaction, you will wait until that other transaction is completed.
While the creation of a transaction is automatic, as defined by the SQL 92 standard, you will have to tell FrontBase when you want the transaction completed (COMMIT or ROLLBACK).
Whenever one or more windows show the edited state (icon in the upper left corner of a window), a transaction has been created. The COMMIT and ROLLBACK submenu items of the Transaction main menu item will also be enabled as soon as a transaction has been created.
As soon as you have completed the necessary modifications to your database, you should thus COMMIT or ROLLBACK the active transaction. Once a transaction has been successfully committed, the modifications are visible to other users of the database.

Connecting to a database

Connect to a database by doubleclicking on the corresponding icon or row in the monitor window (icon or list view). The following panel appears:

Click on the Connect button once the appropriate information has been entered. You can have open connections to several databases at the same time.

Database editor

If a connection to the database could be established, a corresponding management window is ordered front. In the following we assume, that a user thomas with corresponding schema has already been created. After clicking on the Editor tab you should see something like the following.

The title of the management window indicates, that user thomas is connected to database Pictures on host jupiter. The first column of the browser is a list of the defined schemas in the database, including schema THOMAS that has been created automatically while creating user thomas. Please note that INFORMATION_SCHEMA cannot be edited via this application.

Creating a schema

Objects like tables, views, collations,... are always created in and owned by a so called schema. While creating user thomas a schema thomas has automatically been created. However, you can also create additional schemas on your own. Choose Management->Create->Schema from the menu. The following window appears:

The user name is important because this is the user which will be the owner of the new schema, i.e. only this user will be able to modify the schema. Enter a name for the new schema and click on the OK button. If the OK button doesn't become enabled after you have typed a schema name, it is because a schema with the specified name already exists.

Creating a table

Select any schema owned by the connected user (e.g. thomas) by clicking on it in the editor browser and choose Management->Create->Table from the menu. The following panel appears:

Enter the name of the table you are going to create and click on the OK button. A table must have at least one column that is to be entered into the now upcoming panel.

At a minimum, you need to enter a column name, but also both the datatype and the default value can be changed/set. Single column constraints can be created by selecting the appropriate constraint. Please note that PRIMARY KEY implies NOT NULL.
Enter firstname or whatever in the Column name field and choose varchar(<len>) from the Datatype combobox. Then replace <len> with an integer, e.g. 100. That is the maximum length of the varchar column being created. Click on Add. The following table editor window appears.

Note that the icon in the upper left corner of the window indicates an uncommited transaction. More precisly no create statement has been sent to the database yet at all. You can add additional columns by choosing Management->Create->Column from the menu (this can be done at any time after creating the table as well) or let FBManager send the create table statement to the database by either choosing Transaction->Commit from the menu or simply pressing Command-s. Have a look in the log window. It should show the table create statement followed by a commit.

Please note that indexes and multi-column constraints cannot be created until the table has been physically created in the database, i.e. you need to do a COMMIT once the columns have been added.

Creating a view

Select the schema, by clicking on a schema in the browser, and choose Management->Create->View from the menu. Enter the name of the view (see Creating a table) and press Ok. The following window appears:

Enter the view definition and click on the Create button. The view will be created in the given schema and a view editor appears:

The names of the columns of the view can be changed by double-clicking on a column name and entering the new name. You may want to press Command-s now to commit the transaction.

Creating a stored procedure

To create a stored procedure select a schema in the editor browser and choose Management->Create->procedure from the menu. The Create schema object window appears.

Enter a name for the procedure (e.g. dosomething) and press Return. The following window appears:

Note that a procedure template is created automatically. All you have to do is fill in the body, if necessary specify some parameters (see Editing a stored procedure) and press Command-s to commit the transaction (create the procedure).

Creating a stored function

Make sure you have selected a schema in the editor bwoser, then choose Management->Create->Function from the menu. Enter a function name in the Create Object panel and press Return. The following window appears:

Note that a function template with default return type INT is created automatically. All you have to do is fill in the body, if necessary specify some parameters (see Editing a stored functions) and press Command-s to commit the transaction (create the function).

Editing a table

Click on a schema in the first column of the browser and on Tables in the second column. All tables of the selected schema appear in the third column. Select the table you want to edit and choose Management->Definition Editor from the menu or simply doubleclick on the table name. The second approach assumes that you have specified "Definition Editor" as the default editor in application preferences. The table editor appears.

The table editor lets you:
Rename the table - Management->Rename...
Change column names - Double-click and edit
Changing the datatype of columns - select from the combobox
Set a collation for character columns - select from the combobox
Set normalize for character columns - click the checkbox
Add/Drop columns - Management->Column->Add/Drop
Add/Drop deafults - Double-click and edit (blank to drop)
Add/Drop constraints - Management->...
Add/Drop indexes - Management->...
Add/Drop checks - Management->...

If you select a column in the table editor window, a column inspector is ordered front that for character columns allows you to set a collation and enable/disable normalization. Moreover it contains a brief descripton of the selected datatype.

Editing a view

Double-click on the view name in the editor browser of the management window if you have specified "Definition Editor" in application preferences of choose Management->Definition Editor from the menu. The following window appears:

The view editor lets you:
Rename the view - Management->Rename...
Change column names - Double-click and edit

Viewing and editing content data (rows)

Select a table or view in the editor browser and choose Management->Content Editor from the menu. The following window appears:

If you click on the Fetch button all rows of the table (or view) will be fetched. If you want to limit the number of rows to be fetched you need to enter an appropriate WHERE clause as in the following example. After entering the clause either click on fetch or simply press Return.

The content editor lets you:
Delete all rows ("Delete all rows" button)
Delete selected rows (hilite and hit "delete" button)
Insert rows ("Insert row" button)
Update column values (double-click and edit)
Insert BLOB/CLOB values ("Insert BLOB..." button)
Drop BLOB/CLOB values (double-click and make blank)

Inserting a BLOB/CLOB value

Bring up the content editor for the given table, select a row, click on the "Insert BLOB..." button and the following window will appear:

Select a column (only BLOB or CLOB columns will be shown), select a file by clicking on the "Select file with CLOB/BLOB" button, and click insert.

Editing a stored procedure

Select the schema that contains the procedure you are going to edit in the editor browser. If this schema contains any procedures a row Procedures appears in the second column. Click on this row. All procedures of the selected schema are listed in the third browser column. Double-click on the procdure you want to edit. The corresponding editor window appears.

Click into the textview and modify the procedure definition to fit your needs. If you select a FrontBase database in the combobox, the corresponding string is inserted into the textview for your convenience. After entering some logic and commiting the transaction (save changes), the window might look as follows.

You can modify the set of parameters and the procedure definition at any time. To call the newly created procedure click on the SQL92 tab of the management window and enter:

call dosomething('Steve');

in the textview. Then press Esc to execute the statement. Commit the transaction and verify that a new row has been inserted into table Person.

Editing a stored function

Select the schema that contains the function you are going to edit in the editor browser. If this schema contains any functions a row Functions appears in the second column. Click on this row. All functions of the selected schema are listed in the third browser column. Double-click on the function you want to edit. The corresponding editor window appears:

The only difference to editing a stored procedure is that you have to specify a return type and a return value in the function definition.

If you have feedback or questions on this document, please send e-mail to doc-feedback@frontbase.com. Please reference the section number and topic. Thanks!!