OpenISQL 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 OpenISQL program and automatically connect it to the selected database.
OpenBase now offers two Interactive SQL options: the traditional terminal based OpenISQL program and a graphical equivalent called GUI SQL. Both do generally the same thing, but the terminal based version is much better.
Interactive SQL (Openisql)
This chapter introduces OpenISQL, the interactive command line interface to OpenBase. The OpenBase version of ISQL allows users to execute SQL from the command line and import data files. Due to a lack of shell support on Windows NT, OpenISQL is not yet available on that platform. However, there is now an OpenBase Manager tool that will allow you to do similar things through a GUI interface for Windows users.
You can start the OpenISQL program by launching it from the OpenBase Manager (See Interactive SQL Terminal in the Tools menu) or by entering / Library/OpenBase/bin/openisql in a terminal window. This will bring up the openbase prompt. The number following the word openbase in the prompt indicates the current SQL line.
The first thing you want to do is connect to a database. For the sake of discussion, let’s use the Movies database. You should make sure the Movies database has been started using the OpenBase manager. If you have not started the database, you will need to do so first. To connect to the customers database, type the following:
openbase 1> use Movies
The program will prompt you for a login and password. If you have not altered the user table, type admin when it asks for the login. For the password simply press the return key. Here is an example of what you should see on your screen:
openbase 1> use WOMovies login: admin password: using database `Movies' on host 'localhost' openbase 1>
Specifying just the database name will tell ISQL to perform a network wide search for the database named Movies. If you want to specify the hostname you can follow your database name with an @ and the host name. Here is an example:
openbase 1> use Movie@helsinki login:root password: using database `Movie' on host 'helsinki' openbase 1>
Executing SQL Queries
The first step is to type in the SQL query. You may type the entire query on one line, or, if you find it more convenient, you may type it on multiple lines. After entering your query, type go on a new line and press return to tell the server that you would like to execute the query. Here is an example:
openbase 1> select TITLE from MOVIE openbase 2> order by TITLE openbase 3> go
This will produce a list of movie titles in alphabetical order.
Clearing a Mistake
If you make a mistake, use the clear command to reset the command buffer. Here is an example of what you will see on your display.
openbase 1> clear Command cleared. openbase 1>
OpenISQL filters out comments when executing SQL allowing you to place comments in your script files. Comment lines start with //.
Importing SQL Data
Sometimes you may want to execute a list of SQL statements to populate your database. The load command will run an OpenISQL script containing SQL commands, each followed by 'go' The following example sends the contents of the file /tmp/mySQL to the database.
openbase 1> load /tmp/mySQL.file
This method of loading the database can be especially useful when executing upgrade scripts from the command line. It can also prove helpful for recreating and duplicating schemas from sql script files.
Bulk Loading Data
Some databases provide a bulk copy program to import and export data in a comma delimited format. For this reason we provide a bulk load function for importing comma delimited data into OpenBase.
Before loading a comma delimited file you need to specify the table and fields you wish to load the information into. This is done by adding a header to the file. Here is an example of what the top of your data file should look like.
TABLE customers firstname, lastname, company "Joe","Smith","Smith Inc." "Fred","Jones","Jones Inc."
The first line should specify the table name using the TABLE key word. The second line specifies the columns that the data is to be loaded into separated by commas and the following lines contain data. Each line of data should represent the data to be inserted into a single record and string values must be enclosed in quotes or the ? symbol.
Once you have added the appropriate header to your data file you can load it by using the bulk load command. Here is an example:
openbase1> bulk load /tmp/mydatafile.dta
ISQL will print any errors that it encounters to the ISQL window.
Bulk Saving Data
OpenISQL's bulk save function exports database information to an ASCII file. Tables that have been bulk saved can be bulk loaded into other databases.
To bulk save data to a file, type the following command into the OpenISQL window and press the return key.
openbase 1> bulk save /tmp/mydatafile.dta
OpenISQL will open the file and return the following message and prompt:
Enter select query and type go to bulk save data. bulk save 1>
At the prompt type in a select statement that returns the data you want to bulk save. Here is an example using the Company database:
Enter select query and type go to bulk save data. bulk save 1> select * from contacts bulk save 2> order by firstname bulk save 3> go
If no errors are printed, the data is successfully saved to an ASCII file.
Backup & Restore
The best way to backup your databases is to make copies of them. We recommend that you make regular backup copies of your databases located in /Library/OpenBase/Databases (OpenBase/Databases on WindowsNT computers). If you need to restore a database, stop the database, remove the database s work directory in: /Library/OpenBase/ work. Then replace the database files with the backup.
Backup and restore functions are also provided for bulk loading and saving ASCII snapshots of the database. The backup function includes schema information as well as bulk saved data for all tables. Files generated by the backup function and read by the restore function are compatible with the backup and restore functions found in the OpenBase Manager.
To backup the database to an ASCII file, type the following:
openbase 1> backup /tmp/myfile
To restore the database from an ASCII file, type the following:
openbase 1> restore /tmp/myfile
The backup and restore functions do not include user or permission information.
Backup database structure
You can use the backup command to backup the scripts used to create the database, but without including the data. To do this you would issue the following command:
openbase 1> backup create filepath
You could then run this script as discussed in 'Importing SQL Data'.
For a more complete backup we recommend copying the database.db bundle located in the /usr/openbase/Databases directory on your server computer.
The OpenBase Manager Data Viewer has an export function which allows you to export data in a character delimited format. The import command in openisql allows you to read character delimited files. Here is the command syntax:
The format of the file looks like this when ~ is the separator character:
[ table] column1 column2 column3 . data~data~data data~data~data
For large imports you should use the ImportFile() stored procedure as discussed in the stored procedures section.
The history function lists previously executed commands. Here is an example:
openbase 1> history backup /tmp/myfile restore /tmp/myfile
You can execute one of these commands by either pressing the up arrow key or by typing ! followed by the number or the first letter of the command. Here is an example of executing command #1:
To stop the OpenISQL program, type quit at the prompt and press the return key. This will quit the program.
Running Automated Scripts
OpenBase comes with two versions of openisql: openisql and openisql_ascii. The ASCII version is exactly the same except that all command-line editing has been disabled. This was necessary to get openisql working with crontab scripts and web cgi scripts.
When calling openisql or openisql_ascii from the command line you need to specify what database you want to connect to, login and password, and the command you want to perform. Here is an example of the parameters:
# /Library/OpenBase/bin/openisql -use " email@example.com " -login "admin" -password "mypw" - command "load /tmp/scriptfile"
In this example we are connecting to the pubs database on host 127.0.0.1 (in this case, the local computer). The login and password are specified within the quotes. If you do not have a password set you can use the admin user with as the password. Finally, we passed in the command. In this example we load a file which has a whole set of SQL commands to execute. When the command is done executing, openisql automatically quits.
The following provides details of how you would administrate your database from the command line using openisql_ascii. openisql_ascii works like openisql except that it is suitable for use with scripting and on non-MacOS X platforms. openisql_ascii has command line editing disabled.
Connecting to a Database
At this point you need to enter the name and location of the database:
openbase 1> use testdb@localhost
You will then be prompted for Login and Password. On a new database the login is 'admin' and the password is empty, so just hit return when prompted for the password.
You can create tables at the command line using standard SQL. So, to create a basic table you could do something like:
openbase 1> CREATE TABLE testtable (col1 char(10),col2 char(10)) openbase 2> go
It is important to type 'go' after your SQL statement, as this sends the command to the server.
You can the insert data into the table using:
openbase 1> INSERT INTO testtable (col1,col2) VALUES ('firstname','lastname') openbase 2> go
Adding and modifying users
To administrate users you can manually update the users system table _SYS_USERS. Some examples for using this are:
Creating a new user
To create a new user you add a new row to the _SYS_USERS table.
Note: Before inserting the password into the table you need to use the password(pw) function to encode the password.
openbase 1> INSERT INTO _SYS_USERS (fullname,login,password,usergroup) VALUES ('FirstName LastName','firstname', password('password'),'admin') openbase 2> go
So to view all users you can select all from the _SYS_USERS table.
Setting a password for a user
To set a password for a user you need to UPDATE the _SYS_USERS table.
openbase 1> UPDATE _SYS_USERS SET password = password('password') WHERE login = 'firstname' openbase 2> go
Creating a new group
To create a new group you need to insert a new row into the _SYS_GROUPS table
openbase 1> INSERT INTO _SYS_GROUPS (appAuthorizationEnabled,authorizedApps,blockedRadApps,group) VALUES ( '', '', '', 'staff' ) openbase 2> go
You can then add users to the newly created group.
openbase 1> UPDATE _SYS_USERS SET usergroup = 'staff' WHERE login = 'username' openbase 2> go
You can grant and revoke user permissions by manually updating the permissions system table _SYS_PERM
Setting and Revoking Permissions
openbase1> GRANT firstname ACCESS -d+i+s+u TO testtable openbase2> go openbase1> update _SYS_PERM set p_select = '1', p_insert='1', p_update='1', p_delete='0' where name = 'firstname' and table = 'table1' openbase2> go
Synchronizing schemas with the WebObjects tools can sometimes be a challenge. That’s why OpenBase has recently extended Openisql to include a command that will take an EOModel schema and make the OpenBase schema match it. Here is how to call the command:
openbase 1> synchronize eomodel /path/myEOModelFile.eomodeld