Sibyl
Sibyl is an OCI-based interface between Rust applications and Oracle databases.
Example
use sibyl as oracle; // pun intended :)
Notes on Building
Sibyl needs an installed Oracle client in order to link either OCI.DLL
on Windows or libclntsh.so
on Linux. The cargo build needs to know where that library is. You can supply that information via environment variable OCI_LIB_DIR
. On Linux it would be the path to the lib
directory with libclntsh.so
. For example, you might build sibyl's example as:
OCI_LIB_DIR=/usr/lib/oracle/19.12/client64/lib
On Windows the process is similar if the target environment is gnu
. The OCI_LIB_DIR
would point to the directory with oci.dll
:
set OCI_LIB_DIR=%ORACLE_HOME%\bin
cargo build --examples
However, for msvc
environment the OCI_LIB_DIR
must point to the directory with oci.lib
. For example, you might build that example as:
set OCI_LIB_DIR=%ORACLE_HOME%\oci\lib\msvc
cargo build --examples
Usage
Environment
The OCI environment handle must be created before any other OCI function can be called. While there can be many environments - for example, one can create an environment per connection - usually one is enought. Sibyl initializes it to be the most compatible with Rust requirements - thread-safe using UTF8 character encoding. That single environment handle can be created in main
and then passed around:
Note however that some functions will need a direct reference to this handle, so instead of passing it around some applications might prefer to create it statically:
use Environment;
use lazy_static;
lazy_static!
Then later one would be able to create, for example, a current timestamp as:
use TimestampTZ;
let current_timestamp = from_systimestamp?;
Connections
Use Environment::connect
method to connect to a database:
Where dbname
can be any name that is acceptable to Oracle clients - from local TNS name to EZConnect identifier to a connect descriptor.
SQL Statement Execution
All SQL or PL/SQL statements must be prepared before they can be executed:
let stmt = conn.prepare?;
A prepared statement can be executed either with the query
or execute
or execute_into
methods:
query
is used forSELECT
statements. In fact, sibyl will complain if you try toquery
any other statement.execute
is used for all other, non-SELECT, DML and DDL that do not have OUT parameters.execute_into
is used with DML and DDL that have OUT parameters.
query
and execute
take a slice of IN arguments, which can be specified as positional arguments or as name-value tuples. For example, to execute the above SELECT we can call query
using a positional argument as:
let rows = stmt.query?;
or binding :id
by name as:
let rows = stmt.query?;
In most cases which binding style to use is a matter of convenience and/or personal preferences. However, in some cases named arguments would be preferable and less ambiguous. For example, statement changes during development might force the change in argument positions. Also SQL and PL/SQL statements have different interpretation of a parameter position. SQL statements create positions for every parameter but allow a single argument to be used for the primary parameter and all its duplicares. PL/SQL on the other hand creates positions for unique parameter names and this might make positioning arguments correctly a bit awkward when there is more than one "duplicate" name in a statement.
execute_into
allows execution of statements with OUT parameters. For example:
let stmt = conn.prepare?;
let mut department_id: u32 = 0;
let num_inserted = stmt.execute?;
execute
and execute_into
return the number of rows affected by the statement. query
returns what is colloquially called a "streaming iterator" which is typically iterated using while
. For example (continuing the SELECT example from above):
let mut employees = new;
let stmt = conn.prepare?;
let mut rows = stmt.query?;
while let Some = rows.next?
There are a few notable points of interest in the last example:
- Sibyl uses 0-based column indexing in a projection.
- Column value are returned as
Option
s. However, if a column is declared asNOT NULL
, likeEMPLOYEE_ID
andLAST_NAME
, the result will always beSome
and therefore can be safely unwrapped. LAST_NAME
andFIRST_NAME
are retrieved as&str
. This is fast as they are borrowed directly from the respective column buffers. However, those values will only be valid during the lifetime of the row. If the value needs to continue to exist beyond the lifetime of a row, it should be retrieved as aString
.
Note that instead of column indexes sibyl also accepts column names. The row processing loop of the previous example can be written as:
while let Some = rows.next?
Oracle Data Types
Sibyl provides API to access several Oracle native data types.
Number
use Number;
let pi = pi;
let two = from_int;
let two_pi = pi.mul?;
let h = from_string?;
let hbar = h.div?;
assert_eq!;
Date
use Date;
let mar28_1996 = from_string?;
let next_monday = mar28_1996.next_week_day?;
assert_eq!;
Timestamp
There are 3 types of timestamps:
Timestamp
which is equivalent to Oracle's TIMESTAMP,TimestampTZ
- for TIMESTAMP WITH TIME ZONE, andTimestampLTZ
- for TIMESTAMP WITH LOCAL TIME ZONE
use TimestampTZ;
let ts = from_string?;
assert_eq!;
Interval
There are 2 types of intervals:
IntervalYM
which is eqivalent to Oracle's INTERVAL YEAR TO MONTH,IntervalDS
- INTERVAL DAY TO SECOND
use ;
let launch = with_datetime?;
let landing = with_datetime?;
let duration : IntervalDS = landing.subtract?;
assert_eq!;
RowID
Oracle ROWID can be selected and retrieved explicitly into an instance of the RowID
. However, one interesting case is SELECT FOR UPDATE queries where Oracle returns ROWIDs implicitly. Those can be retrieved using Row::get_rowid
method.
let stmt = conn.prepare?;
let mut rows = stmt.query?;
let cur_row = rows.next?.unwrap;
let rowid = row.get_rowid?;
let manager_id: u32 = row.get?.unwrap;
assert_eq!;
let stmt = conn.prepare?;
let num_updated = stmt.execute?;
assert_eq!;
Cursors
Cursors can be returned explicitly:
let stmt = conn.prepare?;
let mut cursor = new?;
stmt.execute_into?;
let mut rows = cursor.rows?;
// ...
Or, beginning with Oracle 12.1, implicitly:
let stmt = conn.prepare?;
stmt.execute?;
if let Some = stmt.next_result?
Testing
Some of sibyl's tests connect to the database and expect certain objects to exist in it and certain privileges granted:
- At least the HR demo schema should be installed.
- While there is no need to install other demo schemas at least
MEDIA_DIR
should be created (see$ORACLE_HOME/demo/schema/mk_dir.sql
) and point to the directory with demo files that can be found inproduct_media
in the db-sample-schemas.zip. - Some of the LOB tests need text files with the the expected content. Those can be found in
etc/media
and copied intoMEDIA_DIR
. - A test user should be created. That user needs acccess to the HR schema and to the
MEDIA_DIR
directory. Seeetc/create_sandbox.sql
for an example of how it can be accomplished. - Tests that connect to the database use environment variables - DBNAME, DBUSER and DBPASS - to identify the database, user and password respectively. These variables should be set before executing
cargo test
.
Limitations
At this time sibyl provides only the most commonly needed means to interface with the Oracle database. Some of the missing features are:
- Non-blocking execution
- Array interface for multi-row operations
- User defined data types
- PL/SQL collections and tables
- Objects
- JSON data
- LDAP and proxy authentications
- Global transactions
- Session and connection pooling
- High Availability
- Continuous query and publish-subscribe notifications
- Advanced queuing
- Shards
- Direct path load
Some of these features will be added in the upcoming releases. Some will likely be kept on a backburner until the need arises or they are explicitly requested. And some might never be implemented.