OpenBase Manager
The OpenBase Manager application provides graphicaltools for managing database servers across your local areanetwork. It also includes tools for viewing database tables,editing database schemas, managing database securityand starting database servers.
Overview of OpenBase Manager
OpenBase Manager allows you to view the different hosts and servers onyour local network with OpenBase installed. There are three categories provided for viewing databases and servers. By clicking on the category you can see the databases available within that specific category. These categories are Local, Network, and Remote. OpenBase Manager also allows you to create groups of databases.
The OpenBase Manager window serves as a database control panel allowing users to perform a variety of functions including starting and stopping databases remotely from across the network. Databases with a green dot beside their name have been started and are ready to be used.
Figure 1 OpenBase Manager Window
Icon Button/Function
The Action button drop down menu list allows you to perform the same functions as you could by clicking on the icons to the right of the database name. In addition, you can perform Backup functions, Synchronization functions, and create New Databases. When no database is selected, this button only shows the New Database option.
The Size button expands and collapses database information that is displayed in the window. When the database information is expanded it shows additional information such as database size and the text description. When collapsed, only the database name and action buttons are displayed.
Local shows all databases located on the local machine. If none are displayed, it may be that you do not have the server processes installed on the local machine.
Network uses Rendezvous to discover all network accessible database hosts on your local area network.
Remote allows you to specify other hosts outside your network that you want to monitor. The (+) allows you to add the domain names or the ip addresses you want to monitor to the list. See Configuring Remote Networks for more information.
Below the line are groups of databases. To add a group click the plus sign in the lower left hand corner. To edit a group click the little pencil icon beside each group name. To add databases to a group, you can drag and drop databases from Local, Network, Remote categories into the groups. To remove a database from a group, click the (X) icon at the far right of the action buttons.
Once you click on the category, the database names will be displayed. The icon to the left of the database names shows whether the databases are starting, running, stopping or stopped. The different icon meanings are explained as follows:
(Yellow)
The database is in the process of starting. During this time database files are checked and rebuilt if necessary.
(Green)
The database is running on the specified host and is ready to be accessed by client programs.
(Yellow- Red)
The database is in the process of cleaning up before stopping.
(Red)
The database has been stopped.
(Clear)
The database has not been started.
Server Management
OpenBase Manager provides options for managing server information. This includes Network access, License information, and Security. These settings are accessed by clicking File on the OpenBase Manager menu bar, selecting the Server option, and then Configure Local Host.
Figure 2:OpenBase Manager Server Configuration
Network Access
Network settings allow you to specify your local ipaddress and to which local network your database server belongs. Both are important to help people on your network find the databases that they are looking for.
For each local database network there is a process called infoserver which runs at a single central location. Please choose one computer on your network with OpenBase installed to be your database infoserver. Computers that share the same infoserver will appear on the same local network in the OpenBase Manager.
Computers which are used as production web servers should point to themselves. This minimizes dependencies between computers. If you have several workgroups which manage independant databases, you can create several “virtual” local database networks on your local network by assigning a different infoserver to each group. This will ensure that each group will only see their own databases by default.
For DHCP customers it is important to use “localhost” for the hostname and “127.0.0.1” as both the ipaddress and the infoserver address. This will enable the ipaddress of the computer to be dynamically assigned while allowing you to run local databases. Databases on computers that receive dynamic ipaddresses from DHCP can not be accessed from other computers. Network accessible database servers should always have static ipaddresses. Select this option to allow access to the network by this host.
License Install Wizard
Click this button to run the License Install Wizard and enter your licensing information. You will be prompted to enter your serial number and authorization code. It is recommended that you copy this information directly from the email you received from OpenBase when you purchased your license.
Security
When you select a host category, the OpenBase Manager checks the host security. It is recommended that you set a host password to make sure that only authorized users can copy, move, add and delete databases. If you are concerned about security you may also want to set the user passwords for each database.
To set a read password, click on the Read Password lock icon and enter the password. Enter the password a second time to verify.
To set a host password, click on the Host Password lock icon and enter the password. Enter the password a second time to verify. To change the password, click on Read Password or Host Password and enter the new password and verification. To remove the password, click on Read Password or Host Password and then click Continue without entering any password.
Safe SQL Mode
Safe SQL Mode makes the server run more defensively to protect users from misbehaving clients and wasted resources. This is especially valuable when some applications allow users to specify their own queries.
Sometimes leaving out a join constraint can multiply a result and turn thousands of records into an unintended result of tens of millions of result rows. When Safe SQL Mode is checked on, it checks the SQL to make sure all joins exist and have indexed constraints before they are evaluated.
Database Management Tools Icons
The buttons to the right of the database name provide easy access to a number of common functions. They are described as follows:
Start Database - Starts the selected database
Stop Database - Stops the selected database
User Manager - Allows the administrator to add new users and groups and to set the access levels for each
Message Log - Opens the message log for viewing
Starting Databases
To start a database:
1. Click on the database name that you want to start in the OpenBase Manager window.
2. Click the Start Database button or select Start Database from the Action menu or Action icon on the OpenBase Manager window. This may bring up a password window asking for the password of the host where you are starting the database. If this happens, type in the password and press the return key. The password can be set by selecting File > Server > Configure Local Host from the OpenBase Manager window.
A yellow dot icon will appear beside databases you start, indicating that the database is in the process of starting. The start-up procedure may take several seconds, during which time the server checks the data and loads some of the indexes into memory. When the database becomes available for user programs, a green dot will appear beside the database.
Stopping Databases
To stop a database:
1. Select the database you want to stop.
2. Click the Stop toolbar item or select the Stop menu item on the Database menu. This may bring up a password window asking for the password of the host where you are starting the database. If so, type in the password and press the return key.
The icon beside the database will turn to a yellow dot, indicating that it is shutting down. When the database actually stops, a red dot will be displayed.
Creating New Databases
To create a new database:
1. Click the Action icon on the OpenBase Manager window or the Action menu item and then select the Create New Database option. A panel will appear allowing you to specify a new database name and the host computer you would like your database to run on. The database name must not contain any spaces.
2. Select the host from the popup menu. Once the name has been entered and the host selected, the next window provides seven tabbed panels where you can configure the database settings. These tabs are General, Localization, Keys, Mirroring, Backup, Automatic, and Expert.
3. Click on the tabs and enter the database settings. See Configuring the Database for detailed information on each tab. When all the parameters have been set, close the window. You will be prompted to either Cancel, Close without Save, or Save Changes. Click Save Changes to save all the parameters entered. Before you can use your new database you need to start it. To start the database, select the database and click the start icon. When you do this a new server process will be started on the destination computer.
Duplicating Databases
To duplicate a database, select the database in the OpenBase Manager. Click the Action icon or click Action in the menu bar and select Duplicate Database. Only stopped databases can be duplicated. Use the Configuration General tab to edit the name of the new database.
Configuring the Database
The OpenBase Manager provides controls for renaming databases and/or moving them between host computers, as well as configuring other database preferences.
Selecting a database and clicking the configure button will bring up the configure database window. From this window you can configure your database. Click on the desired tab and enter the preferences for the database.
Figure 3: Configuration: General Settings
Change the name of the database by clicking in the Database Name field and entering the new name.
Changing the Run-on-Host popup will physically move the database from one host to another (this is not supported in Windows).
To make your database automatically start when the specified computer boots up, click the Start Database at Boot check-box.
Enable the Encrypt Communications by checking the box.
Enabling Change Notification instructs the server that you want applications to be notified when changes to the database take place. This allows clients to automatically update screen displays when database information changes. OpenBase Manager Data Viewer and RADbrowser both support this feature.
To ensure that the database cannot be deleted accidentally, click the check-box next to Disable Delete.
Select the desired display color, if any, and add a short description of the database. On the OpenBase Manager window the database name will appear in a bubble of the selected color.
Figure 4: Configuration: Localization Settings
On the Localization tab, set the encoding that the database will use internally for representing data. Latin encoding is recommended and will work with most single-character languages. For two byte character sets we recommend that you try either Japanese or Unicode Extended encoding. We recommend that you use the default unless your language requires another encoding.
Note: Changing this setting will not convert existing data. Select the desired international sort order and date/time preferences.
Date, Time, and Money
Specifying preferences for date, time and money will change the default format for databases on the specified host. If you would like to display several different formats at the same time in your program, you will need to change these formats programmatically. The datetime type only uses the default NeXT calendar date format, so it is not listed on this panel.
Localized Sorting
Since different languages have different characters and sorting rules, the international sort order section provides a way to specify upper and lower case letters in the natural sort order. The top text field should contain the set of lower case letters in order of their importance and the bottom text field should contain capital letters in the same order. Letters that are not specified in this list will assume their ASCII value and in a sorted list will appear after the letters in the text fields.
Figure 5: Configuration: Backup Settings
OpenBase comes with an online backup mechanism which allows you to schedule compressed copy of the database and/or complete data dumps of the database to timestamped ASCII files. You specify when you would like the backup to take place and where the backup files should be placed.
Click the box next to the desired database backup options to select it.
Enter the path for the backup. Set the schedule for the backup. Options are Every Day or a specific day of the week. Select the time for the backup to run.
The backup schedule is read each day around midnight and backups are scheduled for the following day.
When setting the backup schedule for your databases it is very important that you specify a valid pathname (with no spaces). Without a valid path the backup will not work.
Figure 6: Configuration: Expert Settings
Connection Timeouts
Enter the number of seconds for timeout on idle transactions and idle connections.
Specify Static TCP/IP Port
Click in the field and enter the specific IP address for the Static TCP/IP Port if you do not want the IP address to by dynamically assigned.
External SQL Log
Tells locally running servers to log SQL commands to a log file located in the directory specified in the Log File field. The generated log file, combined with a backup of the database, can be used to roll forward backup databases in case of hard disk failures.
Click the box to select it and enter the path of the log file.
Diagnostic Mode
Click the box to turn on logging for debugging purposes.
When all the parameters have been set, close the window. You will be prompted to either Cancel, Close without Save, or Save Changes. Click Save Changes to save all the parameters entered.
User Manager
The User Manager allows you to add users and groups to your databases. The database must be running to access the User Manager feature.
OpenBase’s security system is completely separate from the security system of the operating system you are running OpenBase on. This is an important feature because you may want to give people controlled access to your database without giving them access to your computer. You can also safely email databases knowing that people can not break into your database without the correct passwords. Passwords and databases are contained in one package.
The User Manager also provides some expert options to control what information users or groups of users are allowed to access and what they are allowed to change. You can also specify what applications certain groups of users are allowed to use so that they can not access the database through unauthorized tools (such as OpenBase Manager or openisql). This gives system administrators fine controls to disallow access to parts of the database.
Adding Database Users and Groups
Adding users is not always necessary since the login always defaults to the admin user. However, if you are running in an environment where security is an issue, you may want to define users and set user access permissions. The OpenBase Manager allows you to do this through it's user management panel shown in Figure 10.
To access User Manager, click the User Manager icon to the right of the database. This option may also be selected by clicking the Action icon or Action menu bar and selecting User Manager.
Figure 7: OpenBase User Manager
The OpenBase User Manager lists users and groups of users. By default a new database contains an admin group and user. Since all users listed inside the admin group have full access to the entire database, you may want to first create a new group.
To create a new group click the Add Group button located on the left of the display. It will create a new group with a default name. You should change the name to something that defines your new group. A valid group name contains no spaces or punctuation.
Figure 8: OpenBase User Manager-Groups
Once you have created a group you can create a new user by clicking the Add User button on the top left of the window. A default user name will be filled in on the left which you should edit. You can optionally fill in the e-mail information for that user. This is used by some RADstudio applications but is not necessary.
To edit the user information, select the user in the browser located on the left side of the window.
Figure 9: OpenBase User Manager - Users
The user information will display in the editable fields on the right. By changing the value in the group pop-up you can change the group of the user.
To set a user's password, you need to click the Set Password button. You will be prompted to type in the new password and then again to confirm.
If the information on the user window appears read-only, this may mean that your current database login does not have permission to update the users table. You may need to log in as the admin user to edit this table.
Expert Settings & Table Permissions
The User Manager also offers expert settings for setting user access to specific tables or applications. We recommended that you don't touch any of these settings unless you have a reason to be concerned about security.
You can restrict groups of users from using certain applications with specific databases by selecting the group in the list and checking the Application Authorization checkbox. Every application that has ever accessed the database will be listed with checkboxes beside each. Check only the applications you want the group to be able to use.
You can further restrict users that use RADstudio applications. The RAD Application tab gives you a list of RADstudio applications that you can enable or disable for each group.
Finally, the Expert button on the group and user panels allows you to define the specific things you want users to be able to do. For instance, you may want to give a user insert access to a database table but revoke read, update and delete access. This would allow a user to insert records without being able to see or update any of the existing data. The panel which sets this up is shown in Figure 10.
Figure 10: Expert Options - Assigning Table Access
Using the Expert Permissions panel you have the ability to give users and user groups permission to perform four standard types of database operations for each table. These include: select, insert, update, and delete. You can assign these on the user level or the group level. Users inherit the settings of their group.
Click on an item to select it, then click to deselect the Select, Insert, Update, or Delete operations as desired to prevent access to that operation(s).
To keep unauthorized users from editing user information or granting table permissions, you may want to revoke access to the tables _SYS_USERS and _SYS_PERM for each user. Revoking permissions for _SYS_TABLES will block users from creating, dropping, or renaming tables.
Users can perform operations on tables as long as they have the correct permissions. Otherwise an error is returned.
Editing the Database Schema
The Database Schema Editor allows administrators and database developers to view and change the database schema (the design of data tables and relationships). The Database Schema Editor offers a graphical database view showing tables as boxes and relationships as lines connecting boxes. It also offers a tabular view that lists the tables in alphabetical order and allows you to see the table columns as an easy to edit list.
When you alter the database schema your database data will remain intact. You can add, remove, rename, index, and change the type of columns. Any necessary data conversions are performed automatically.
You can also save your changes as a script which you can run later using Openisql. This is especially useful when you want to test your new schema on a test database before applying it to a production database (a database which users are actually using). You can save the changes to a script file and then save the same changes to your database.
Figure 11: Schema Editor - Table View
The Schema Editor’s table view lists the tables on the left and the table attributes on the right. A table below shows database relationships. Column attributes can be edited directly in the table view where they are listed.
Table Inspector
The Table Inspector allows you to change the name of a table. You can bring up the table inspector by double clicking on the table names on the left or by adding a new table.
Viewing Database Information
The OpenBase Manager allows you to view and edit database table information using the Database Browser. To do this select a started database (one with a green dot) and click the View Data icon. This will bring up the Data Viewer tab shown in Figure 12.
Figure 12: Data Browser Window
The table names listed on the left of the Data Viewer tab offer an easy mechanism for choosing the tables you want to view. Selecting a table will show the data on the right.
The Data Viewer allows you to edit as well as view table content. As long as the record you select hasn't been locked by another user, you can double click to select and change information. The data browser keeps two people from editing the same row of information so that your changes will not be overwritten by other users.
If you are interested in seeing OpenBase system tables in addition to your own tables, you can check the “show all tables” checkbox. When you do this the table list is refreshed to include all of the system tables. We strongly recommend that you do not try to edit or change any of the information in the system tables directly.
The columns on the data viewer may be reorganized allowing you to view information in a different order. You can change the position of a column by pointing to the column's title, clicking and holding down the mouse button, and dragging the title to its new location. When you let go of the mouse, the columns will reorganize.
The field entitled Maximum Fetch at the top right enables you to specify the maximum number of rows you wish to be returned. Clicking the refresh button will refresh the display using the new maximum.
Clicking on the column titles at the top of the Data Viewer will sort the data by that column attribute.
Clicking on the Search button will bring up a search panel giving you the ability to narrow the search results. This is displayed below:
Figure 13: Data Viewer Search Window
The Data Viewer Search allows you to contrain the search by up to three different qualifiers. Use stars (*) to indicate wildcards for LIKE constraints.
The Query Editor tab on the Data Browser window allows you to create custom queries. This is useful if you have a select that you need to perform frequently. All you need to do is enter your SQL in the space provided (or choose a template to edit) and name it something you will remember. Only select statements are allowed. When you go back to the Data Viewer tab, your query will show up in the box just below the table list. Selecting the title will run the query.
Figure 14: Query Editor Window
Configuring Remote Networks
Remote Networks are groups of databases which share different Infoserver. It could be a website or another group of servers which you want to remotely administrate. Remote Networks are listed under the Remote Networks title on the main OpenBase Manager window.
To add a Remote Network you need an ipaddress or fully qualified domain name and a user friendly title for the network. Click the plus (+) icon next to Remote on the OpenBase Manager window and enter either the hostname or IP address of the remote host. For best results the ipaddress should be the address of the remote network’s infoserver. However, the ipaddress could be any of the servers that share the infoserver as long as the infoserver computer is remotely accessible. Only one ipaddress per network should be included in order to see all of the accessible database hosts on that network. To configure Remote Networks
To configure the Remote Host, click the configure icon to the right of the database name and enter the login information for that host.
Interactive SQL
SQL Terminal allows you to execute SQL from a terminal window. Clicking the Interactive SQL Terminal icon or selecting SQL Terminal from the Action menu or Action icon will launch the SQL Terminal program and automatically connect it to the selected database.
Figure 15: SQL Window
Backup, Restore and Script Functions
The OpenBase Manager provides some tools for transferring database information to and from a bulk saved ASCII format. The Backup to ASCII and Restore from ASCII functions are accessed from the Action menu or icon. These functions transfer all information in the database including archived Objects and BLOBs. While it does save user account information, the ASCII format does not record table permissions. Another method to backup your databases is to copy the .db bundle.
Also included on the Action menu is a function for executing OpenISQL scripts. These can either be schema upgrade scripts generated by the Editing the Database Schema panel or it can be an ASCII file with a list of OpenISQL commands.
