Friday, April 25, 2008

Integrated auth in Firebird 2.1

Just a quick sample of integrated windows auth in Firebird 2.1
Logon to my Firebird server as a user with administrative privileges on the machine:

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database: localhost:employee
SQL>

So I'm logged. let's check who am I for Firebird:

SQL> select current_user from rdb$database;

USER

===============================================================================

SYSDBA


SQL>

That's right, as an administrator on the machine, I'm SYSDBA on the database, now let's try with an unprivileged user I have on this machine:
I'll open another command line client as user postgres (guess what other database is installed on this machine?)

C:\Programmi\Firebird\Firebird_2_1\bin>runas /user:postgres cmd
Immettere la password per postgres:
Tentativo di avvio di cmd come utente "AB-2346789223445\postgres" ...

C:\Programmi\Firebird\Firebird_2_1\bin>

Now I have another command line window open

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database: localhost:employee
SQL> select current_user from rdb$database;

USER

===============================================================================

AB-2346789223445\POSTGRES


SQL>

Ok, I'm in as postgres, let's try a select

SQL> select * from country;

COUNTRY CURRENCY
=============== ==========
USA Dollar
England Pound
Canada CdnDlr
Switzerland SFranc
Japan Yen
Italy Lira
France FFranc
Germany D-Mark
Australia ADollar
Hong Kong HKDollar
Netherlands Guilder
Belgium BFranc
Austria Schilling
Fiji FDollar

SQL>

This works as expected, table country is available for SYSDBA and for PUBLIC, my current user is in PUBLIC by default.
Let's check a different table, on which PUBLIC has no grants

SQL> select * from tbl_stock_warehouse;
Statement failed, SQLCODE = -551
no permission for read/select access to TABLE TBL_STOCK_WAREHOUSE
SQL>

My request has been rejected as expected, great!
If this is not enough for you a lot of improvements in the user management area are currently in the works for Firebird 2.5 (Core 696 and CORE 1660) and in derived databases like RedSoft's one which will hopefully be merged in the official Firebird server.

Saturday, April 19, 2008

Loading data from files

Having already blogged about loading data from flat files to MySQL, it's time to post a similar case for PostgreSQL, as the manual seems to lack a real life example ...

First of all the table to be loaded
CREATE TABLE target
(
code character(3) NOT NULL,
"name" character varying(50) NOT NULL,
amount numeric,
CONSTRAINT pk_1 PRIMARY KEY (code)
)
WITH (OIDS=FALSE);
as you can see one of the column names is a reserved word! Bad practice, but it's here to add some spice.

Now the data
code;name;amount
12A;Pippo;12.5
13B;Topolino;45
23D;Pluto;NULL
as you can see it's delimited by ";", has an header row and contains NULLs, which we want to preserve in our target table.
This file is named 2beloaded.csv and it's placed in c:\

After logging in to PostgreSQL and creating the table with the statement provided above it's time to load it
copy
--target table with columns listed
target(code, "name", amount)
from
--source file
'C:/2beloaded.csv'
with
--list of options
csv
--switches on csv mode
header
--ignores first line as an header line
delimiter ';'
--sets the delimiter
null as 'NULL'
--preserves nulls by telling the database what represents a NULL

Quite nice, isn't it?
Hope this helps.

Sunday, April 06, 2008

Two basic indexing tips ...

Here are two basic tips for proper indexing ...

  1. Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?
  2. Put indexes where the database can really use them, if a table is to be fully scanned anyway, it's indexes are unlikely to be used, unless you can compare those index entries with other indexes on tables that won't be fully scanned. Ordering is another game ;). See here for an example.
Easy, isn't it? But these mistakes are still very common ...