On Part I of this series, we got our Oracle Express database up and running against Debian Testing. It involved quite a bit of fiddling but we seemed to get there in the end. In Part II we shall now finish the configuration of the Oracle database and set up the application dependencies. On Part III we will finally get to the Dogen model, and start to make use of ODB.
What's in a Schema?
The first thing we need to do to our database is add the "application users". This is a common approach to most server side apps, where we tend to have "service users" that login to the database and act upon user requests on their behalf. We can then use audit tables to stamp the user actions so we can monitor them. We can also have application level permissions that stop users from doing silly things. This is of course a step up from the applications in the nineties, where one would have one database account for each user - allowing all sorts of weird and wonderful things such as users connecting directly to databases via ODBC and Excel or Access. I guess nowadays developers don't even know someone thought this to be a good idea at one point.
When I say "database user", most developers exposed to RDBMS' immediately associate this to a user account. This is of course how most databases work, but obviously not so with Oracle. In Oracle, "users" and "schemas" are conflated, so much so it's hard to tell if there is any difference between them. For the purist RDBMS user, a schema is a schema - a collection of tables and other database objects, effectively a namespace - and a user is a user - a person (real or otherwise) that owns database objects. In Oracle these two more or less map to the same concept. So when you create a user, you have created a schema and you can start adding tables to it; and when you refer to database objects, you prefix them by the user name just as you would if they belonged to a schema. And, of course, you can have users that have no database objects for themselves, but which were granted permission to access database objects from other users.
So our first task is to create two schemas; these are required by the Dogen model which we will use as our "application". They are:
basicnorthwind
As I mentioned before, I had created some fairly basic tests for ODB
support in Dogen. Those entities were placed in the aptly named schema
basic. I then decided to extend the schema with something a bit more
meaty, which is where northwind comes in.
For the oldest readers, especially those with a Microsoft background, Northwind is bound to conjure memories. Many of us learned Microsoft Access at some point in the nineties, and in those days the samples were pure gold. I was lucky enough to learn about relational databases in my high-school days, using Clipper and dBASE IV, so the transition to Microsoft Access was more of an exercise in mapping than learning proper. And that's where Northwind came in. It was a "large" database, with forms and queries and tables and all sorts of weird and wonderful things; every time you needed something done to your database you'd check first to see how Northwind had done it.
Now that we are much older, of course, we can see the flaws of
Northwind and even call for its abolition. But you must remember that
in the nineties there was no Internet for most of us - even dial-up
was pretty rare where I was - and up-to-date IT books were almost as
scarce, so samples were like gold dust. So for all of these historic
reasons and as an homage to my olden days, I decided to implement the
Northwind schema in Dogen and ODB; it may not cover all corner cases,
but it is certainly a step up on my previous basic tests.
Enough about history and motivations. Returning to our SQLPlus from
Part I, where we were logged in as SYSTEM, we start first by
creating a table space and then the users which will make use of that
table space:
SQL> create tablespace tbs_01 datafile 'tbs_f01.dbf' size 200M online; Tablespace created. SQL> create user basic identified by "PASSWORD" default tablespace tbs_01 quota 100M on tbs_01; User created. SQL> create user northwind identified by "PASSWORD" default tablespace tbs_01 quota 100M on tbs_01; User created.
Remember to replace PASSWORD with your own passwords. This is of
course a very simple setup; in the real world you would have to take
great care setting the users and table spaces up, including thinking
about temporary table spaces and so forth. But for our simplistic
purposes this suffices. Now we need to grant these users a couple of
useful privileges - again, for a real setup, you'd need quite a bit
more:
SQL> GRANT create session TO basic; GRANT create session TO basic; Grant succeeded. SQL> GRANT create table TO basic; GRANT create table TO basic; Grant succeeded. SQL> GRANT create session TO northwind; GRANT create session TO northwind; Grant succeeded. SQL> GRANT create table TO northwind; GRANT create table TO northwind; Grant succeeded.
If all went well, we should now be able to exit the SYSTEM session,
start a new one with one of these users, and play with a test table:
$ sqlplus northwind@XE
SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 24 10:20:10 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create table test ( name varchar(10) );
Table created.
SQL> insert into test(name) values ('kianda');
insert into test(name) values ('kianda');
1 row created.
SQL> select * from test;
NAME
----------
kianda
SQL> grant select on test to basic;
Grant succeeded.
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
$ sqlplus basic@XE
SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 24 10:23:04 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> select * from northwind.test;
NAME
----------
kianda
This all looks quite promising. To recap, we logged in with user
northwind, created a table, inserted some random data and selected
it back; all looked ok. Then for good measure, we granted the rights
to see this test table to user basic; logged in as that user and
selected the test table, with the expected results.
At this point we consider our Oracle setup completed and we're ready to enter the application world.
Enter ODB
Setting up ODB is fairly easy, especially if you are on Debian: you
can simply obtain it from apt-get or synaptic. The only slight
snag is, I could not find the oracle dependencies
(i.e. libodb-oracle). Likely this is because they depend on OCI,
which is non-free, so Debian either does not bother to package it at
all or you need some kind of special (non-free) repo for it. As it
was, instead of losing myself on wild goose chases, I thought easier
to build from source. And since I had to build one from source,
might as well build all (or almost all) to demonstrate the whole
process from scratch as it is pretty straightforward, really.
Before we proceed, one warning: best if you either use your package manager or build from source. You should probably only mix-and-match if you really know what you are doing; if you do and things get tangled up, it may take you a long while to figure out the source of your woes.
So, the manual approach. I first started by revisiting my previous notes on building ODB; as it happens, I had covered installing ODB from source previously here for version 2.2. However, those instructions have largely bit-rotted at the Dogen end and things have changed slightly since that post, so a revisit is worthwhile.
As usual, we start by grabbing all of the packages from the main ODB website:
- odb 2.4.0-1 amd64.deb: the ODB compiler itself.
- libodb-2.4.0: the main ODB library, required by all backends.
- libodb-pgsql-2.4.0: the PostgreSQL backend. We don't need it today, of course, but since PostgreSQL is my DB of choice I always install it.
- libodb-oracle-2.4.0: the Oracle backend. We will need this one.
- libodb-boost-2.4.0: the ODB boost profile. This allows using boost types in your Dogen model and having ODB do the right thing in terms of ORM mapping. Our Northwind model does not use boost at present, but I intend to change it as soon as possible as this is a very important feature for customers.
Of course, if you are too lazy to click on buttons, just use wget:
$ mkdir odb $ cd odb $ wget http://www.codesynthesis.com/download/odb/2.4/odb_2.4.0-1_amd64.deb -O odb_2.4.0-1_amd64.deb $ wget http://www.codesynthesis.com/download/odb/2.4/libodb-2.4.0.tar.gz -O libodb-2.4.0.tar.gz $ wget http://www.codesynthesis.com/download/odb/2.4/libodb-pgsql-2.4.0.tar.gz -O libodb-pgsql-2.4.0.tar.gz $ wget http://www.codesynthesis.com/download/odb/2.4/libodb-oracle-2.4.0.tar.gz -O libodb-oracle-2.4.0.tar.gz $ wget http://www.codesynthesis.com/download/odb/2.4/libodb-boost-2.4.0.tar.gz -O libodb-boost-2.4.0.tar.gz
We start with the DEB, as simple as always:
# dpkg -i odb_2.4.0-1_amd64.deb Selecting previously unselected package odb. (Reading database ... 549841 files and directories currently installed.) Preparing to unpack odb_2.4.0-1_amd64.deb ... Unpacking odb (2.4.0-1) ... Setting up odb (2.4.0-1) ... Processing triggers for man-db (2.7.6.1-2) ...
I tend to store locally built software under my home directory, so that's where we'll place the libraries:
$ mkdir ~/local $ tar -xaf libodb-2.4.0.tar.gz $ cd libodb-2.4.0/ $ ./configure --prefix=/full/path/to/local <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-2.4.0' $ make install <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-2.4.0'
Remember to replace /full/path/to/local with your installation
directory. The process is similar for the other three packages, with
one crucial difference: you need to ensure the environment variables
are set to place all required dependencies in the include and link
path. This is achieved via the venerable environment variables
CPPFLAGS and LDFLAGS (and LD_LIBRARY_PATH as we shall see). You
may bump into --with-libodb. However, be careful; the documentation
states:
If these libraries are not installed and you would like to use their build directories instead, you can use the
--with-libodb, and--with-boostconfigure options to specify their locations, for example:
./configure --with-boost=/tmp/boost
So if you did make install, you need the environment variables
instead.
Without further ado, here are the shell commands. First boost; do note I am relying on the presence of Debian's system boost; if you have a local build of boost, which is not in the flags below, you will also need to add a path to it.
$ cd .. $ tar -xaf libodb-boost-2.4.0.tar.gz $ cd libodb-boost-2.4.0/ $ CPPFLAGS=-I/full/path/to/local/include LDFLAGS=-L/full/path/to/local/lib ./configure --prefix=/full/path/to/local <snip> config.status: executing libtool-rpath-patch commands $ make -j5 <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-boost-2.4.0' $ make install make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-boost-2.4.0'
For PostgreSQL again I am relying on the header files installed in Debian. The commands are:
$ cd .. $ tar -xaf libodb-pgsql-2.4.0.tar.gz $ cd libodb-pgsql-2.4.0/ $ CPPFLAGS=-I/full/path/to/local/include LDFLAGS=-L/full/path/to/local/lib ./configure --prefix=/full/path/to/local <snip> config.status: executing libtool-rpath-patch commands $ make -j5 <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-pgsql-2.4.0' $ make install <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-pgsql-2.4.0'
Finally, Oracle. For this we need to supply the locations of the
downloaded drivers or else ODB will not find the Oracle header and
libraries. If you recall from the previous post, they are located in
/usr/include/oracle/12.1/client64 and
/usr/lib/oracle/12.1/client64/lib, so we must augment the flags with
those two paths. In addition, I found configure was failing with
errors finding shared objects, so I added LD_LIBRARY_PATH for good
measure. The end result was as follows:
$ cd .. $ tar -xaf libodb-oracle-2.4.0.tar.gz $ cd libodb-oracle-2.4.0 $ LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib CPPFLAGS="-I/full/path/to/local/include -I/usr/include/oracle/12.1/client64" LDFLAGS="-L/full/path/to/local/lib -L/usr/lib/oracle/12.1/client64/lib" ./configure --prefix=/full/path/to/local <snip> config.status: executing libtool-rpath-patch commands $ make -j5 <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-oracle-2.4.0' $ make install <snip> make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-oracle-2.4.0'
And there you are; all libraries built and installed into our local directory, ready to be used.
Conclusion
In this part we've configured the Oracle Express database with the application users, and we sanity checked the configuration. Once that was out of the way, we built and installed all of the ODB libraries required by application code.
On Part III we will finally start making use of this setup and attempt to connect to the Oracle database. Stay tuned!
