Notable Features & Technical Details
Welcome to OpenBase! This document highlights some notable feature you should be aware of.
Always-on encryption
Now OpenBase SQL delivers always-on encryption for secure database access, from anywhere, anytime. OpenBase uses the Diffie-Hellman (D-H) key exchange cryptographic protocol, which allows communications to be established securely over an insecure public network. D-H key exchange provides the basis for a variety of authentication protocols, including SSL and SSH. OpenBase uses this same trusted protocol to provide secure access to data without having to configure a VPN.
JSON / XML Server
The JSON/XML HTTP gateway allows your thin-client and web applications to communicate with OpenBase SQL databases through an HTTP gateway. The server acts as a proxy to restrict authenticated requests to the data you want users to be able to view and update. It supports the REST XML standard for updating as well as direct SQL requests. Three levels of security are provided.
Incremental Backups
OpenBase offers a sophisticated incremental backup system and GUI program for restoring databases. This gives OpenBase 'Time Machine'-like capabilities, allowing a user to restore their database as of any date and time, all the way up to the present. This makes it easy to recover from an accidental deletion. The backup has no impact on the databases operation and is maintained simultaneously with the database.
Incremental backups are configured in the System Preferences Panel. However, you can also configure the backup path by editing the following file:
/Library/OpenBase/Config/BackupPath
Diagnostic SQL Mode
Diagnostic SQL Mode can be turned on in the OpenBase Manager's Database Configuration panel under the Expert tab. This creates a directory inside your /Users/Shared/OpenBase/Databases directory called DiagnosticSQL for holding the diagnostic information about the communication between your applications and the database.
In the latest version the functionality has been expanded to not only log all SQL being sent to the server, but also maintain a log of slow running SQL. By default, all SQL taking more than 2 seconds to execute are sent to a timing.sql file. This allows you to see what operations need to be optimized. You can change the 2 second threshold by editing the threshold file.
SQL Plan
One popular request has been a PLAN command that would show what indexes are going to be used by a given query. Here is an example of how it is used:
PLAN SELECT * FROM authors WHERE au_fname like '*foo*'
We strongly recommend that you take a look at the performance documentation. There are many tricks you can use to optimize queries and schemas for better performance.
Record Schema
Now you can eliminate the risk of deploying schema changes to production environments. Simply turn on the new schema change recorder, accessible from the Database Configuration Expert panel and it will record the changes you make to the schema in your development database, so that later you can apply them to your production database.
Server-side Variables
OpenBase provides server-side global variables to make it easy to coordinate between stored procedures and database clients. The variables are thread-safe and can be safely incremented and decremented to provide specialized key generation or state management.
Here is an example:
DECLARE GLOBAL invoice_number AS INTEGER INITIALIZE 1000
If you are using a numeric variable type, you can use INCREMENT and DECREMENT to modify the value in a thread-safe manner.
By thread-safe we mean that if 10 processes all ask the server to increment the value at the same time, they will all take turns so two clients don’t generate the same number.
INCREMENT invoice_number
Increment increases the value of the variable by 1 and returns the current value.
DECREMENT invoice_number
Decrement decreases the value of the variable by 1 and returns the current value.
Currently we only support setting a simple value. Here is an example:
SET invoice_number TO 2000
When you set the value of a global variable in OpenScript the value is actually stored on the server.
Sometimes you may want to fetch the value of a variable. You can do this using the PRINT VARIABLE command. Here is an example:
PRINT VARIABLE invoice_number
This syntax is specific to the database server and cannot be used inside OpenScript. With OpenScript, use the global variables as you normally would and the values will flow through from the database server.
Performance & Memory Usage
OpenBase 11 offers huge performance gains for evaluating large sub-queries while using less memory. Evaluating this query can be as much as 200 times faster in OpenBase 11 from earlier versions:
SELECT a.name from tableA a where a.colA IN (select b.colA from veryLargeTable b where colB = ‘value’)
Interfaces
JDBC Java 1.5 Compatibility
OpenBase 11 offers a new JDBC driver which is simultaneously compatible with both Java versions 1.5 and 1.4.
REALBasic Connectivity Module
The latest connectivity module for REALBasic provides Data Change Notification, auto-failover capabilities and auto-reconnect features. It also provides 100% compatibility with all target platforms supported by REALbasic.
Omnis DAMs Universal Binary
OpenBase also comes with updated DAMs for Omnis Studio and Omnis 7. Among the updates is the addition of a Universal Binary version of the DAM providing compatibility with Apple’s MacOS X INTEL platform.
Universal Binary Cocoa Frameworks
All Mac OS X Frameworks have been recompiled for Universal Binary compatibility.
Configuration Options
Relocatable Databases and Work directories
Databases can be easily relocated to a different directory or an external drive (such as a RAID device). Since OpenBase SQL 10, the default database directory is:
/Users/Shared/OpenBase/Databases
This change was necessary for Mac OS X users because computer technicians usually do not backup /Library/OpenBase when servicing Mac computers. Moving the databases to a directory that is commonly backed up ensures that databases will not be accidentally lost when a hard drive is replaced.
To control the location of your databases, edit the following file:
/Library/OpenBase/Config/DatabasePath
If for some reason you need to change the location of your working database directory, you can do so by changing this file:
/Library/OpenBase/Config/WorkPath
Interfaces & Applications
OpenBase Forms
OpenBase Forms is a database interface that makes it easy to organize complex database information on functional data input screens, so users can find what they need fast.
OpenBase Forms organizes hierarchical database information on panels, which create a "storyboard" of related information and keeps things simple!. This unique approach eliminates the problems encountered when trying to fit too much information on a single window or forcing the user to try to manage many inter-related windows.
OpenBase Forms comes with complete source code, as well as examples of how to embed the OpenBase Forms Framework into Cocoa applications.
Using this source, you can add your own Forms menu to your applications and link your windows to Form panels directly. The source code is found in the OpenBase 11 DMG package, under Connectivity.
OpenBase Forms offers a full range of development tools:
- The OpenBase Form Wizard takes any table or group of related tables and generate a fully functional input form, which can be used to edit and search data.
- More advanced users can then edit those forms using OpenBase Form Designer to create full-featured business applications. (OpenBase Forms Designer is sold separately, and can be downloaded from the openbase.com website.)
Cocoa developers can integrate the source code to the OpenBase Forms client application into any of their Cocoa applications.to make it easy for users to extend the application and make it more specific to their needs. You can advertise this benefit simply by including the OpenBase Forms framework. There are no runtime fees for the OpenBase Forms client software.
Built-in Stored Procedures
SaveBlobToFile()
The SaveBlobToFile stored procedure allows you to export a blob (image or text) to the file system. It takes two parameters: a blob key, which is an 8-character identifier for a blob; and a file path, which must be valid and greater than 5 characters in length.
Here is an example:
call SaveBlobToFile ("abcf0100", "/PATH")
RemoteQuery() and RemoteJDBCQuery()
OpenBase SQL supports the ability to perform queries in other database servers using remoteQuery() and remoteJDBCQuery() procedure calls.
The remoteQuery() procedure takes login information for another OpenBase SQL Database, performs the query and then returns the results to the calling application. Here is the syntax:
call RemoteQuery(databaseName, ipaddress, login, password, sqlstring)
Here is an example of how it can be called:
call RemoteQuery("pubs", "127.0.0.1", "admin", "mypw", "select * from authors")
The remoteJDBCQuery() procedure takes remote query a step farther by enabling remote queries to be performed in any JDBC-compliant data source, including Oracle and Sybase databases, all from your OpenBase SQL database. Here is the syntax:
call RemoteJDBCQuery(jdbcDriver, jdbcURL, login, password, sqlstring)
Here is an example of how it can be called:
call RemoteJDBCQuery("com.sybase.jdbc2.jdbc.SybDriver", "jdbc:sybase:Tds:192.168.0.2:11222/pubs", "sa", "", "select * from authors")
Global Log
The Global Log stored procedure allows application designers to store error messages in a central log that is not specific to any one database. Storing error messages centrally makes it easier for application designers to better monitor what’s going on with their apps. Logging to a central file is accomplished using the GlobalLog() stored procedure. Here is an example:
call GlobalLog(logName,logMessage)
This is an example of how it might be used:
call GlobalLog("myGlobalLogFile","This is an error message")
New OpenBase SQL Features
New OpenBase 11 Views
OpenBase 11 offers a new ‘view’ mechanism that manages view result sets in virtual tables which can contain their own indexes and calculated columns. The new implementation will also accept any SQL select syntax in creating the view.
Server-side Variables
OpenBase 11 provides server-side global variables to make it easy to coordinate between stored procedures and database clients. The variables are thread-safe and can be safely incremented and decremented to provide specialized key generation or state management.
SQL Declaration:
DECLARE GLOBAL invoice_number AS INTEGER INITIALIZE 1000
Setting a Variable Value:
SET invoice_number TO 2000
Incrementing and Decrementing Variables (thread-safe & returns value):
INCREMENT invoice_number DECREMENT invoice_number
Returning a variable value:
PRINT VARIABLE invoice_number
Server-side Variables for OpenScript
Server-side variables have also been integrated into OpenScript 3.0 allowing for OpenScript and other clients to use it as an effective locking mechanism. Here is an example:
on MyProcess ( )
DECLARE GLOBAL MyProcess_lock AS INTEGER INITIALIZE 0
-- create the lock and exit if more than one is running
IF INCREMENT MyProcess_lock != 1 THEN
DECREMENT MyProcess_lock
RETURN "exited: " & MyProcess_lock & " processes running"
END IF
-- process source code here
-- decrement the value
DECREMENT MyProcess_lock
RETURN "success"
end MyProcess
Temporary Tables
You can create temporary tables that are private to the creator’s client connection and exist only as long as the client stays connected. Temporary tables act the same as regular tables. You can join them to other tables, assign indexes and alter them just like a regular table.
Temporary table names always start with a "#" in the first position. That is how OpenBase distinguishes a temporary table from a regular table. Everything else works exactly the same.
Because temporary tables are specific to client the connection, multiple users can create temporary tables with the same name, without any conflict.
Stored Procedures as Subqueries
Now it’s possible to call stored procedures as subqueries, a capability that’s especially useful to query other databases.
select TITLE t1 from MOVIE t1 where t1.MOVIE_ID IN (call FindMoviesForRole(‘Luke Skywalker’) )
Calling the RemoteJDBCQuery() stored procedure as a subquery, for instance, allows a result from Sybase or Oracle to be used to evaluate a query in OpenBase. RemoteJDBCQuery() allows any JDBC compliant database to be used.
The use of procedures as subqueries is limited to selects and inserts. It cannot be used for updates or deletes.
It is also important to note that only non-correlated subqueries will work with stored procedures. Each procedure is called only once, so parameters have to be static. You cannot pass in column names as parameters and use it as a function.
New SQL Functions
OpenBase has added a few SQL functions that give clients easier access to blobs and relevance scoring functions.
FETCHBLOB(blob_key)
Returns the first 4K of a text blob and allows it to be used inside an SQL operation. Longer blob text is truncated by the function. If the MOVIE table were to have a COMMENT column of type TEXT, the following SQL would work:
SELECT TITLE, FETCHBLOB(COMMENT) from MOVIE
FIGGSCORE(text, keywords)
The FIGG Score takes a text string and assigns a score to it based on how well it matches the keys passed into it. The higher the score, the closer the match.
SELECT TITLE, FIGGSCORE(TITLE,"Star") from MOVIE
FIGGSCOREBLOB(blob_key, keywords)
This version of the function takes a blob key as its first parameter and scores the entire Blob text , regardless of length.
SELECT TITLE, FIGGSCOREBLOB(COMMENT,"Star") from MOVIE
"HAVING" has been implemented
The HAVING clause provides a way to eliminate grouped sets of records based on the results of aggregate functions. It should always be used in conjunction with GROUP BY to specify which groups of records you want included in the final result set. . HAVING will only accept groups of records that satisfy the criteria.
Here is an example from the pubs database that includes all authors that live in states with other authors.
SELECT state, count(state) from authors group by state having count(state) >1
Sometimes you may need to require groups to satisfy multiple criteria. This next example lists all states, which have more than one author but less than 10.
SELECT state, count(state) from authors group by state having count(state) > 1 and count(state)<10
The HAVING clause can only be used with the <, <=, !=, =, >= and > operators. A static value must also be present on the right side of the HAVING constraint.
Inserting from a Select
INSERT … INTO … SELECT
In OpenBase 9.1 or later you can use sub-selects as part of an insert statement.
For example:
INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (SELECT FIRST, LAST, TAL_ID FROM ANOTHER_TABLE)
You can also use the output of a stored procedure in your insert statement:
INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (CALL findMyTalents(‘optional parameters’))
Server-Side SQL Cursors
New server-side SQL Cursors in OpenBase SQL 11 enable clients to perform a select or stored procedure call and cache a snapshot of the results on the server. This allows clients to incrementally request portions of the result set or start fetching again from the beginning.
SQL Cursors are named so that clients can navigate the results of several cursors simultaneously. Clients use the cursor name to specify which cursor you want to fetch.
Creating SQL Cursors
To create an SQL Cursor, start with an SQL Select or stored procedure call. Here is an SQL statement that uses the pubs database:
SELECT au_fname, au_lname FROM authors ORDER BY au_lname
Then, to create a cursor that uses this SQL result set, precede the SQL with the CREATE CURSOR command. Here is an example that creates a cursor named myAuthors:
CREATE CURSOR myAuthors AS SELECT au_fname, au_lname FROM authors ORDER BY au_lname
Creating a cursor will return nothing. Before you can get the results you need to FETCH.
Fetching SQL Cursor Results
Once a SQL cursor is created, you retrieve the data using the FETCH command. Here is an example of a FETCH command that specifies five result rows:
FETCH 5 FROM myAuthors
The label ‘myAuthors’ is the name of the cursor as specified in the CREATE CURSOR statement. A second call to FETCH will get the next five in the result set.
Rewinding Cursor Results
There may be cases where you want to rewind a result set to the beginning so it can be fetched again. OpenBase SQL Cursors allow you to do this using the REWIND command. Here is an example using the myAuthors cursor:
REWIND myAuthors
After a cursor result set has been reset using REWIND, the FETCH command will begin at the very beginning.
Closing Cursors
It is very important to close SQL cursors after you are finished with them. To close a cursor, issue the CLOSE CURSOR command. Here is an example that closes the myAuthors cursor:
CLOSE CURSOR myAuthors
OpenScript Stored Procedures
Arrays and Dictionaries
OpenScript now supports full array and dictionary manipulation and static declaration of values and key/value pairs. This section will introduce you to the basic operations. For a more complete tutorial please visit the section on OpenScript.
An array is a sequential set of values which can be referenced by position. An example of referencing a value in an array is:
SET var TO ITEM 1 OF myArray
A dictionary is a set of key/value pairs where each value is referenced by a key string. Here is an example where we are accessing the value for the string 'mykey':
SET var TO ITEM 'myKey' OF myDictionary
If you want to define an array or dictionary statically in your code, you would do it as follows:
SET array TO @('value','value','value')
SET dictionary TO @{'key1'='value','key2'='value','key3'='value'}
Arrays are defined with a @(). Dictionaries are defined by @{}.
Here are two examples of nesting arrays and dictionaries.
SET array TO @('value','value',@{'key1'='value','key2'='value','key3'='value'} )
SET dictionary TO @{'key1'='value','key2'='value','key3'=@('value','value','value')}
More on this topic can be found under OpenScript Stored Procedures.
Process Control
OpenScript now allows you to control a process by reading and writing to the processes standard input/output. This capability can be used for many things, including remote terminal operations, install scripts, or multi-step processes.
Here is an example of opening a terminal shell and waiting until the ".app" output is received before continuing.
on checkForApp ( )
OPEN PROCESS myProcess AT "/bin/sh" WITH TIMEOUT 10
WRITELN "/bin/ls /Applications" TO myProcess
SET ret TO EXPECT ".app" FROM myProcess
CLOSE PROCESS myProcess
return ret
END checkForApp
Note: Communication with the process is only bi-directional on Mac OS X.
