{"id":364,"date":"2014-02-10T10:59:59","date_gmt":"2014-02-10T10:59:59","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=364"},"modified":"2014-02-10T11:11:47","modified_gmt":"2014-02-10T11:11:47","slug":"how-firebird-3-0alpha2-stores-data","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=364","title":{"rendered":"How Firebird 3.0 alpha 2 stores data"},"content":{"rendered":"

5th part (Oracle<\/a>, DB2<\/a>, MySQL<\/a>, PostgreSQL<\/a>) of my series \u201eHow xxx stores data\u201c 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*).<\/p>\n

<\/p>\n

I will continue with recently installed Firebird from .tar.gz archive. And of course i will show connecting into existing database + create table.<\/p>\n

1) Preparing data<\/strong><\/p>\n

root@deb73:\/opt# \/opt\/fb30-alpha\/bin\/isql<\/strong><\/span><\/span>
\nUse CONNECT or CREATE DATABASE to specify a database<\/span>
\nSQL> connect \/opt\/fb30-alpha\/examples\/empbuild\/employee.fdb user SYSDBA password lala;<\/strong><\/span><\/span>
\nDatabase:\u00a0 \/opt\/fb30-alpha\/examples\/empbuild\/employee.fdb, User: SYSDBA<\/span><\/p>\n

SQL> show tables;<\/strong><\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNTRY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DEPARTMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EMPLOYEE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EMPLOYEE_PROJECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PROJECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PROJ_DEPT_BUDGET\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SALARY_HISTORY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SALES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span><\/p>\n

SQL> create table acc_test(noo smallint, accno varchar(25));<\/strong><\/span><\/span>
\nSQL> <\/span><\/p>\n

As you can see, I was forced to changed name of first column (as „no“ is reserved word in Firebird) into „noo“:<\/p>\n

SQL> create table acc_test (no smallint,accno varchar (25));<\/strong><\/span>
\nStatement failed, SQLSTATE = 42000<\/span>
\nDynamic SQL Error<\/span>
\n-SQL error code = -104<\/span>
\n-Token unknown – line 1, column 24<\/span>
\n-no<\/span><\/p>\n

There are few ways how to insert data into this table. Typing as monkey or some batch processing. Let\u00b4s have a file \/root\/fbfill.txt<\/strong> containing:<\/p>\n

CONNECT \"\/opt\/fb30-alpha\/examples\/empbuild\/employee.fdb\" USER \"SYSDBA\" PASSWORD \"lala\";<\/code><\/span>
\ninsert into acc_test values (01,’4255-8613-9716-5076′);<\/span>
\ninsert into acc_test values (02,’4531-2930-4967-2232′);<\/span>
\ninsert into acc_test values (03,’4548-7929-1826-4899′);<\/span>
\ninsert into acc_test values (04,’4691-3746-9407-0405′);<\/span>
\ninsert into acc_test values (05,’4649-1103-3295-2551′);<\/span>
\ninsert into acc_test values (06,’4997-5257-8004-9774′);<\/span>
\ninsert into acc_test values (07,’4692-4078-2653-8573′);<\/span>
\ninsert into acc_test values (08,’4044-8832-9347-1412′);<\/span>
\ninsert into acc_test values (09,’4688-8299-4664-4385′);<\/span>
\ninsert into acc_test values (10,’4339-4136-0219-4499′);<\/span>
\ninsert into acc_test values (11,’4016-7845-2950-8098′);<\/span>
\ninsert into acc_test values (12,’4961-6591-9364-9979′);<\/span>
\ninsert into acc_test values (13,’4787-0768-3223-3820′);<\/span>
\ninsert into acc_test values (14,’4582-2114-8843-1397′);<\/span>
\ninsert into acc_test values (15,’4549-3001-8407-0658′);<\/span>
\ninsert into acc_test values (16,’4464-3676-8558-3399′);<\/span>
\ninsert into acc_test values (17,’4804-1261-5366-8044′);<\/span>
\ninsert into acc_test values (18,’4284-1059-8455-5702′);<\/span>
\ninsert into acc_test values (19,’4127-1856-7120-0292′);<\/span>
\ninsert into acc_test values (20,’4633-7926-0863-2443′);<\/span>
\ncommit;<\/span>
\nEXIT;<\/span><\/p>\n

And let\u00b4s run it!<\/p>\n

root@deb73:\/opt\/fb30-alpha\/bin# .\/isql < \/root\/fbfill.txt<\/strong><\/span><\/span>
\nUse CONNECT or CREATE DATABASE to specify a database<\/span><\/p>\n

root@deb73:~# \/opt\/fb30-alpha\/bin\/isql<\/strong><\/span><\/span>
\nUse CONNECT or CREATE DATABASE to specify a database<\/span>
\nSQL> connect \/opt\/fb30-alpha\/examples\/empbuild\/employee.fdb user SYSDBA password lala;<\/strong><\/span><\/span>
\nDatabase:\u00a0 \/opt\/fb30-alpha\/examples\/empbuild\/employee.fdb, User: SYSDBA<\/span>
\nSQL> <\/span>
\nSQL> show tables;<\/strong><\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ACC_TEST<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNTRY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DEPARTMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EMPLOYEE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EMPLOYEE_PROJECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PROJECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PROJ_DEPT_BUDGET\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SALARY_HISTORY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SALES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\nSQL> select * from acc_test;<\/strong><\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 NOO ACCNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n======= ========================= <\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 1 4255-8613-9716-5076\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 2 4531-2930-4967-2232\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 3 4548-7929-1826-4899\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 4 4691-3746-9407-0405\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 5 4649-1103-3295-2551\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 6 4997-5257-8004-9774\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 7 4692-4078-2653-8573\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 8 4044-8832-9347-1412\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0 9 4688-8299-4664-4385\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 10 4339-4136-0219-4499\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 11 4016-7845-2950-8098\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 12 4961-6591-9364-9979\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 13 4787-0768-3223-3820\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 14 4582-2114-8843-1397\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 15 4549-3001-8407-0658\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 16 4464-3676-8558-3399\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 17 4804-1261-5366-8044\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 18 4284-1059-8455-5702\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 19 4127-1856-7120-0292\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 20 4633-7926-0863-2443\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span><\/p>\n

SQL> <\/span><\/p>\n

Done.<\/p>\n

2) Analyzing data physical layer<\/strong><\/p>\n

From all examples, Firebird is most streightforward. No tablespaces, no additional container nor other complications. We are specifying database file also in connection string =<\/p>\n

root@deb73:\/opt\/fb30-alpha\/examples\/empbuild# ls -la<\/strong><\/span><\/span>
\ntotal 1312<\/span>
\ndrwxr-xr-x\u00a0 2 root\u00a0\u00a0\u00a0\u00a0 root\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4096 Jan 27 16:14 .<\/span>
\ndr-xr-xr-x 12 root\u00a0\u00a0\u00a0\u00a0 root\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4096 Jan 27 16:14 ..<\/span>
\n-rw-rw—-\u00a0 1 firebird firebird 1335296 Feb 10 11:48 employee.fdb<\/span><\/p>\n

With high expectations I\u00b4m running command and waiting for 0 results:<\/p>\n

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\\}“<\/strong><\/span><\/span>
\n4633-7926-0863-2443<\/span>
\n4127-1856-7120-0292<\/span>
\n4284-1059-8455-5702<\/span>
\n4804-1261-5366-8044<\/span>
\n4464-3676-8558-3399<\/span>
\n4549-3001-8407-0658<\/span>
\n4582-2114-8843-1397<\/span>
\n4787-0768-3223-3820<\/span>
\n4961-6591-9364-9979<\/span>
\n4016-7845-2950-8098<\/span>
\n4339-4136-0219-4499<\/span>
\n4688-8299-4664-4385<\/span>
\n4044-8832-9347-1412<\/span>
\n4692-4078-2653-8573<\/span>
\n4997-5257-8004-9774<\/span>
\n4649-1103-3295-2551<\/span>
\n4691-3746-9407-0405<\/span>
\n4548-7929-1826-4899<\/span>
\n4531-2930-4967-2232<\/span>
\n4255-8613-9716-5076<\/span><\/p>\n

real\u00a0\u00a0 \u00a00m0.123s<\/span>
\nuser\u00a0\u00a0 \u00a00m0.012s<\/span>
\nsys\u00a0\u00a0 \u00a00m0.000s<\/span><\/p>\n

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.<\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

5th part (Oracle, DB2, MySQL, PostgreSQL) of my series \u201eHow xxx stores data\u201c 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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,16],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/364"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=364"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/364\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=364"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}