5th part (Oracle, DB2, MySQL, PostgreSQL) of my series „How xxx stores data“ with an idea of insider (system administrator) who wants to be rich (selling valid credit card numbers). Firebird is excellent RDBM – nevermind it is not so famous in Czech republic (*facepalm*).
I will continue with recently installed Firebird from .tar.gz archive. And of course i will show connecting into existing database + create table.
1) Preparing data
root@deb73:/opt# /opt/fb30-alpha/bin/isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect /opt/fb30-alpha/examples/empbuild/employee.fdb user SYSDBA password lala;
Database: /opt/fb30-alpha/examples/empbuild/employee.fdb, User: SYSDBA
SQL> show tables;
COUNTRY CUSTOMER
DEPARTMENT EMPLOYEE
EMPLOYEE_PROJECT JOB
PROJECT PROJ_DEPT_BUDGET
SALARY_HISTORY SALES
SQL> create table acc_test(noo smallint, accno varchar(25));
SQL>
As you can see, I was forced to changed name of first column (as „no“ is reserved word in Firebird) into „noo“:
SQL> create table acc_test (no smallint,accno varchar (25));
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown – line 1, column 24
-no
There are few ways how to insert data into this table. Typing as monkey or some batch processing. Let´s have a file /root/fbfill.txt containing:
CONNECT "/opt/fb30-alpha/examples/empbuild/employee.fdb" USER "SYSDBA" PASSWORD "lala";
insert into acc_test values (01,’4255-8613-9716-5076′);
insert into acc_test values (02,’4531-2930-4967-2232′);
insert into acc_test values (03,’4548-7929-1826-4899′);
insert into acc_test values (04,’4691-3746-9407-0405′);
insert into acc_test values (05,’4649-1103-3295-2551′);
insert into acc_test values (06,’4997-5257-8004-9774′);
insert into acc_test values (07,’4692-4078-2653-8573′);
insert into acc_test values (08,’4044-8832-9347-1412′);
insert into acc_test values (09,’4688-8299-4664-4385′);
insert into acc_test values (10,’4339-4136-0219-4499′);
insert into acc_test values (11,’4016-7845-2950-8098′);
insert into acc_test values (12,’4961-6591-9364-9979′);
insert into acc_test values (13,’4787-0768-3223-3820′);
insert into acc_test values (14,’4582-2114-8843-1397′);
insert into acc_test values (15,’4549-3001-8407-0658′);
insert into acc_test values (16,’4464-3676-8558-3399′);
insert into acc_test values (17,’4804-1261-5366-8044′);
insert into acc_test values (18,’4284-1059-8455-5702′);
insert into acc_test values (19,’4127-1856-7120-0292′);
insert into acc_test values (20,’4633-7926-0863-2443′);
commit;
EXIT;
And let´s run it!
root@deb73:/opt/fb30-alpha/bin# ./isql < /root/fbfill.txt
Use CONNECT or CREATE DATABASE to specify a database
root@deb73:~# /opt/fb30-alpha/bin/isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect /opt/fb30-alpha/examples/empbuild/employee.fdb user SYSDBA password lala;
Database: /opt/fb30-alpha/examples/empbuild/employee.fdb, User: SYSDBA
SQL>
SQL> show tables;
ACC_TEST COUNTRY
CUSTOMER DEPARTMENT
EMPLOYEE EMPLOYEE_PROJECT
JOB PROJECT
PROJ_DEPT_BUDGET SALARY_HISTORY
SALES
SQL> select * from acc_test;
NOO ACCNO
======= =========================
1 4255-8613-9716-5076
2 4531-2930-4967-2232
3 4548-7929-1826-4899
4 4691-3746-9407-0405
5 4649-1103-3295-2551
6 4997-5257-8004-9774
7 4692-4078-2653-8573
8 4044-8832-9347-1412
9 4688-8299-4664-4385
10 4339-4136-0219-4499
11 4016-7845-2950-8098
12 4961-6591-9364-9979
13 4787-0768-3223-3820
14 4582-2114-8843-1397
15 4549-3001-8407-0658
16 4464-3676-8558-3399
17 4804-1261-5366-8044
18 4284-1059-8455-5702
19 4127-1856-7120-0292
20 4633-7926-0863-2443
SQL>
Done.
2) Analyzing data physical layer
From all examples, Firebird is most streightforward. No tablespaces, no additional container nor other complications. We are specifying database file also in connection string =
root@deb73:/opt/fb30-alpha/examples/empbuild# ls -la
total 1312
drwxr-xr-x 2 root root 4096 Jan 27 16:14 .
dr-xr-xr-x 12 root root 4096 Jan 27 16:14 ..
-rw-rw—- 1 firebird firebird 1335296 Feb 10 11:48 employee.fdb
With high expectations I´m running command and waiting for 0 results:
root@deb73:/opt/fb30-alpha/examples/empbuild# time strings -a employee.fdb | grep „[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}“
4633-7926-0863-2443
4127-1856-7120-0292
4284-1059-8455-5702
4804-1261-5366-8044
4464-3676-8558-3399
4549-3001-8407-0658
4582-2114-8843-1397
4787-0768-3223-3820
4961-6591-9364-9979
4016-7845-2950-8098
4339-4136-0219-4499
4688-8299-4664-4385
4044-8832-9347-1412
4692-4078-2653-8573
4997-5257-8004-9774
4649-1103-3295-2551
4691-3746-9407-0405
4548-7929-1826-4899
4531-2930-4967-2232
4255-8613-9716-5076
real 0m0.123s
user 0m0.012s
sys 0m0.000s
Sorry, Firebird, in my testing of basic security, you failed. Score for now – only MySQL (MariaDB, TokuDB) is storing in binary (or just not-plain-readable-text). On opposite side is standing Oracle, DB2, PostgreSQL and Firebird. Pathetic.
-a-