What do you think about Interbase?A database management system which is free of charge, available with source code, easy to install and queryable from all views, does not need to be named MySQL. Since Borland has opened the sources of Interbase a precious relational database management system for Linux does exist which can bear the "big ones". One should not underestimate the advantage that it is available for Windows and Linux. Fortunately the installation is quite easy on both systems.
Via www.interbase.com you get to the download page where you can start instantly without annoying registration. We decided to go for the Linux version. Intel-disciples will find ready RPM packages. The decision between Classic- and Super-Server remains. The Classic version starts a new process for every connection while the Super-Server uses threads; so it can provide a slight performance advantage when many connections are made because of shared cache. After the obligatory
rpm -ivh InterBase...i386.rpm
most of the installation & configuration is already done. (The installation on Windows is easy as well.) The server now resides in /opt/interbase/bin/ibserver and should be started with an init script. Such an init script can be created using the template of your distribution.
The server is running now and Interbase is waiting for its first contact with the database administrator, named SYSDBA with the password MASTERKEY.
An user is created with the command line tool gsec and the password of SYSDBA is changed:
gsec -user sysdba -password masterkey -modify sysdba -pw secret gsec -user sysdba -password secret -add superuser -pw mypass
Now we can start by running the SQL console:
isql -u superuser -p mypass
With CREATE DATABASE an initial database is created. Interbase saves its information in standard files. The data can be split into several files to cope with the 2 GB limitation. Unfortunately one cannot use this functionality for distributing databases to several harddisks because Interbase fills the files sequentially. It would be nice if you could tell Interbase which tables to put into which files.
CREATE DATABASE '/var/interbase/dummy.gdb';
Non-spectacular SQL functionality is achieved because of ANSI-SQL without re-acclimating and shall not be mentioned further in this article. Not only the web provides SQL documentation in large amounts but also Interbase includes a good reference - download it at any cost. Anyway, cleanly creating a database is slightly different from the familiar procedure.
create domain movietitle as varchar(200);
create domain zerototen as smallint default null check ((value is null) or ((value>=0) and (value<=10)));
create table movies ( movieid int not null primary key, moviename movietitle, rating zerototen );
|Excursus: Taking over old data|
If you are already experienced with databases and you are (hopefully) thinking of trying Interbase, you may wonder how to get your beloved old data into the new database.
In any case it is presumed that you have your data in raw format (data only). One possibility would be to parse these files manually and put it into the new database step by step with INSERT INTO ... VALUES .... This does not need to be the worst and slowest way (cue: parametrized queries, see part 2).
Another way - recommended in the Interbase documentation - would be creating external tables and selecting it into an internal table in one (huge) step. Detailed:
create table import (movieid char(8), moviename char(200), newline char(1)) external file 'mydata.raw'
Of course you must know the format of the raw file for that, but you also know the structure of the exported table. If newline was written (or is consisting of two signs) is also depended on the export. If the table was created this way, you can work with it the normal way (at least for our purpose).
A SELECT COUNT(*) should be used for checking if Interbase was able to cope with the data. Afterwards you fire up this SQL query and hope to find your data in the new table:
insert into movies(movieid, moviename) select movieid, moviename from import;
Step by step: Domains are new data types which we declare and then can use like integrated types (INT, CHAR, TIMESTAMP etc.). Every column with the type 'movietitle' so is nothing different than varchar(200); you could have left this out - its a matter of opinion. The second example is different. The domain 'zerototen' only allows values between 0 and 10 and the NULL value. In our example a movie can never be rated with 15 points.
When adding to tables there is the typical problem of generating new keys which must be unique in every case, nevermind if there are 2 or 2000 users are adding data at the moment. What is quite non-flexible and known as AUTO_INCREMENT in MySQL, is named generator in Interbase:
create generator g_movieid; insert into movies (movieid, moviename) values (GEN_ID(g_movieid, 1), 'The Matrix');
On INSERT the recent value of the generator is queried and then incremented by the function GEN_ID. You can automate this a little bit:
set term !! ; create trigger c_movieid for movies before insert position 0 as begin NEW.MOVIEID = GEN_ID(G_MOVIEID,1); end !! set term ; !!
First the sign that ends a command is changed to !! by SET TERM. So we can use the semicolon in the trigger without danger. Inside the trigger command single columns can be accessed with NEW - nevermind what the content is, we overwrite it with a new value. New movies can be inserted into the table the following way:
insert into movies (moviename) values ('Desperado');
When creating new tables it would be good to make Interbase check the reference integrity:
create table dvd ( movieid int not null REFERENCES MOVIES(MOVIEID), mynumber int, comment varchar(200) );
In the field MOVIEID in the table DVD only values may occur which are already defined in the table MOVIES - to speak clearly: we cannot have movie 13782 on DVD if it does not exist already. In the end we quickly create a view to be able to combine the contents of both tables comfortably:
create view moviesondvd as select movies.moviename, dvd.mynumber, movies.rating, dvd.comment from movies, dvd where dvd.movieid = movies.movieid;
Put in two data sets quickly...
insert into dvd (movieid, mynumber, comment) values (1, 23, 'unfortunately scratched'); insert into dvd (movieid, mynumber, comment) values (2, 24, '+ El Mariachi');
... and, hey presto, we own a really comfortable DVD management application! ;-)
select * from moviesondvd;
This is the end of part one. In the next part we are going to create a nice web frontend with the help of IBPerl to be able to manage our DVDs even more comfortably.
Enter Own Comment