What do you think about Interbase?

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
  );
  

  create table import (movieid char(8), moviename char(200), newline char(1))
  external file 'mydata.raw'
  

  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.

Talkback Area