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.

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.

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 ...