Sw4   >   Sql   >   Database Administration

Database Administration

The StudioWorks Database Administrator simplifies the process of preparing and maintaining SQL databases.

In an ideal world all SQL database management systems would use the same SQL syntax, but we live in an imperfect world. The StudioWorks DB Admin classes overcome the SQL database differences by allowing us to customize the SQL text and methods for accomplishing various tasks for each RDBMS.

The StudioWorks DBAdmin classes performs tasks such:

  1. Creating and dropping tables.
  2. Adding, renaming, and dropping columns.
  3. Adding and dropping primary and foreign keys.
  4. Adding and dropping indexes.
  5. Adding and dropping constraints.
  6. Adding and dropping collations.
  7. Adding and droppping database users and changing passwords.
  8. Setting next primary key counters.

The DBAdmin tools are able to accomplish the above using the meta-data which you add to the schema classes using the SQL Meta-Data Editor.

The DB Admin tools are accessed via the Database Admin tab of the Programmer Workbench.

All of the DB Admin functions can be accessed by your application through the oDBAdmin object. You simply create an object variable, point it to oDBAdmin, sent it an $initalize(pSessionRow) message, and you are in business. All of the public methods of oDBAdmin are at your disposal. You can create/sync tables, drop tables, drop columns, rename table, rename columns, etc., etc.

The various DB Admin windows are described in the next sections.

Data Mover

If you create a new database and want to move data from an existing StudioWorks application based database to the new database the Data Mover is your one click ticket.

To copy data from one database to another.

  1. Select the source database in the Data Mover window.
  2. Select the target database. If you need to set up a session with the target database, select Edit Sessions... in the session picker, and then create and test the session in the Sessions Manager window. The target sessions picker will be updates when you close the Sessions Manager window.
  3. Click the Show Records checkbox if you want to see the number or records in each table in the source database.
  4. Select the tables in the source database list which you want to copy to the target database. You would normally select all of the tables in the source list.

    StudioWorks use the foreign key score order of the source tables so that parent tables are created before child tables and parent records are copied before child records.
  5. If your target database is a brand spanking database with no tables in it you have 3 choices:
    1. Full Sync and Copy Data - click this button if the source database is fairly small.
    2. Quick Sync and Copy Data - click this button if the source database is big. The quick copy does not add indexes or constraints to the target database, so the copy operation is much faster. After the data is copied click the Full Sync button. This adds the indexes and constraints to the target database.
    3. Full Sync followed by Copy Data - This breaks the sync and copy operations into 2 separate steps.

  6. If you have already sync'd your target database in the Schema and Tables window you can simply click the Copy Data button to copy all of the data from the source database to the target database.

The sync operation always sets/resets the primary key counters. This ensures that the counter for each table will be greater than the maximum primary key value in each table after you copy in the new data.

The copy operation simply inserts the data. It does not check for existing records. The tables should alway be empty.

If the sync succeeds but then the copy fails part way through you can select and right-click on the tables in the target list and Deleted Records.

You can also right-click and select Drop Tables to completely delete the selected tables in the target database.

Schema and Tables

The Schema and Tables tab lets you look at all the schemas and columns in your application and the tables and columns in the database.

When you select a Schema Table in the left list, the Schema Columns are listed in the second list. If a matching table already exists in the database it is selected in the the Table Name list, and the database table's columns are list in the Column Name list.

If you click the Sync Database to Match Selected Schemas, StudioWorks will do just that. If a table is missing it is created, if a column is missing it is added, if the column datatype has changed it is altered, if there is an extra column you are given a warning, if an index is missing it is added, if a foreign key constraint is missing it is added, etc. Each time the DB Admin tool sync's a table it also sets the primary key counter.

The Insert Empty Records button will loop through the selected schemas which have Zero Allowed checked in the meta-data for the primary key column, and will check for a zero value primary key record and if not found insert one. Zero pkey records are used optional foreign key joins.

Indexes

The DB Admin - Indexes tab simply lists the database tables and the indexes for the table you select.

You can right-click Delete selected indexes.

A sync operation will readd meta-data based indexes you delete.

Primary Keys

The DB Admin - Primary Keys tab lists the database tables, the maximum primary key value for each table, and the current primary key counter value.

You can right-click Set Counter Value to set a counter to a specific value.

Clicking the Reset Counter Values button at the bottom of the window will reset the counters to one above the max value or each selected table.

A sync operation resets a tables primary key counter.

Constraints

The DB Admin - Constraints tab simply lists the database tables and the constraints for the table you select.

You can right-click Delete selected constraints.

A sync operation will readd meta-data based constraints you delete.

The Omnis data file does not support constraints. (other than not null)

All RDMBSs that I know of support foreign key constraints.

Some RDBMSs support CHECK constraints. (FrontBase and PostgreSQL)

Users

The DB Admin - Users tab lists the App users (in the usr table) the Database Users, and the database tables.

Code was written and tested for FrontBase for adding Database users which match the App users, and granting them privileges for selected tables.

The original intent was that a Database user would be added for each App user and the Database user's PRIVILEGES for each table would match their schema classes settings in their security profile. (canview, caninsert, canedit, candelete).

Keeping Database users sync'd with App users is a lot of work. Every time you add an App user or change an App user's security settings you have to sync the Database users.

Currently all of the StudioWorks developers use the tablesownerlogon technique, so I have not spent further time developing the code for synchronizing Database users with App users. The Users tab is currently disabled in the DB Admin window.

Interactive SQL

You can use the Interactive SQL window of the DB Admin tool for entering and executing SQL.

I use the Interactive SQL window for testing and editing long SQL queries that for some reason aren't working.

There are some handy features in the StudioWorks Interactive SQL window that you should be aware of:

  1. Unique History - This is a list of the last 50 queries you executed in this window. Each time you successfully execute a SQL statement is it added to the end of the list. Any duplicate queries are removed from the list.

    If you single click on a query in the history list it is copied to the SQL text field.

    If you double click on a query in the history list it is copied to the SQL text field and it is immediately executed.
  2. SQL Text - You enter and edit SQL text in this multi-line entry field.

    For long SQL statements I like to use multiple lines, for example:

    SELECT TownCityName, StateProvName, CountryName
    FROM Towncity, Stateprov, Country
    WHERE Towncity.Stateprov_fkey = StateProv_pkey
    AND Towncity.Country_fkey = Country_pkey



    To execute the SQL text you have a number of options:
    • Click the Execute SQL button.
    • Press the Enter key once on your numeric keypad.
    • Press the Return key twice on your keyboard.

When you close the window the SQL history is saved to the local_prefs.df1 file.

When you open the window it is restored.

Data Viewer

The DB Admin - Data Viewer tab lists the database tables and fetches the first batch of records the table you select.

You can right-click Delete a record in a table.

SQL Reserved Words

The DB Admin - SQL Reserved Words tab lists the reserved words which StudioWorks uses to audit your table and column names.

Each time a sync operation is done, StudioWorks first audits the table and column names of all the selected tables. If a reserved word is used in a table or column name, an error is logged and the sync operation is halted.

This saves you the grief of wondering why a perfectly good looking SQL statement fails because it use a SQL reserved word for a table or column name. (I know from experience, I and others have lost hours trying to solve these types of SQL errors.)

Query Builder

The Query Builder is a handy window that helps you build and modify query classes.

Base on meta-data Query Builder makes its best guess on the extra query text to join tables by way of their foreign key relationships.

Some of the advantages the Query Builder window are:

  1. Ability to drag and reorder multiple columns.
  2. Ability to select and right-click Delete multiple columns.
  3. Automatic generation of $extraquerytext based on foreign key relationships.
  4. Ability to have carriage returns in the extra query text making complex joins easier to read.

StudioWorks Documentation - Copyright 2005 Vencor Software