Translating for developers

Hi, what follows are my personal thoughts after reading Dmitry Yemanov's comments to a great poll posted at Firebirdnews about the features people would like to see implemented.

Quote:

1. SMP support is a clear winner (23% of votes) in the poll. And this is definitely one of the high priority tasks in the project. As you know, it was the major goal of the Vulcan development and its implementation is being currently tested. The public test versions will be available soon for those who's willing to test it in real-world scenarios. The final SMP implementation is targeted for the v3.0 release, as stated in our roadmap.

Well, since Intel and AMD are massively going for multicore architectures on the desktop and SMP servers are more and more common in datacenters I wouldn't place this in a whishlist, it's simply a must to stay current, no choice.

Quote:

2. Better security (16%). It's not very clear what security issues are assumed here, but some fixes and improvements are already made and more ones are scheduled for the near future. In particular, you may expect embedded security, database encryption and trusted authentication quite soon.

Again, where are those developers hiding? It's good to see embedded security, database encryption and trusted auth, but isn't it time to add SSL encrypted communications between client and server? And, speaking of SSL, why not adding certificate based auth?
MySQL and PostgreSQL do support this from some time.

Quote:

3. Cross-database queries (14%). This is a quite important feature that requires serious design investigation. Its implementation is not trivial, so it's not in the nearest schedule and hence it may require sponsorship to get a higher priority.

Ahhh, finally, the database is not a single, isolated, ivory tower!!! Now, why not putting up a temptative implementation much like MySQL's Federated storage engine? Something like a simple interface to a client library that dispatches calls to remote databases, which connection parameters are embedded in a view definition, I agree that it's not a clean implementation, but it could be a starter and raise people's attention.

Quote:

4. Faster wire protocol (13%). Some improvements have already been made in this area (see previous blog records) and others are scheduled for the next versions.

Again, is it so hard to add what many other databases already have? Besides improvements to the protocol, which are extemely important for Firebird, please add zlib based compression, it shouldn't be that hard and it will put Firebird on pair with other OSS databases.

Quote:

5. Server monitoring (8%). This feature is already being debugged and tested, so you may expect the core monitoring abilities quite soon.

Great!!! Now, please state it clear, can we hope in a v$sessions like view?

Apart from some sarchasm, I consider Firebird an excellent database developed by a highly motivated and competent group, but I was very surprised reading that post, because it seemed quite surprised by those requests, which are IMHO pretty standard.
Ok, enough for the rants now, have a nice day ;-)

Note: Mariuz (see post comments) added this rant to Firebirdnews and we can read some of Dmitry's comments about it, my favourite is that, yes v$session like views ARE coming!!!

Relocating MySQL data

After an interesting question I'm posting here a simple description of how to place your MySQL data on another drive, if you got a server with multiple disks this can really benefit performance.
The picture is this, you have installed MySQL server in the usual C:\Program Files\MySQL\MySQL Server 5.0\ folder, but you have another drive D: and you want to place your data there, you'll create three folders there:

D:\MySQL DataDir\ which will contain MyISAM tables (.frm, .MYD, .MYI files) and also InnoDB tables definitions (.frm files)

D:\MySQL InnoDBDatafiles\ which will contain InnoDB data (ibdata1 etc. files)

D:\MySQL InnoDBLogfiles\ which will contain InnoDB logs (ib_logfile0, ib_logfile1 etc.)

this will be accomplished by:

1. creating those folders
2. stopping the server
3. editing my.ini file this way:

#Path to the database root
datadir="D:/MySQL DataDir/"

#*** INNODB Specific options ***
innodb_data_home_dir="D:/MySQL InnoDBDatafiles/"

innodb_log_group_home_dir="D:/MySQL InnoDBLogfiles/"

leave unchanged the following line

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Programmi/MySQL/MySQL Server 5.0/"

because you are not relocating the binaries.

4. restart the server.

Note that you .pid and your .err files will also be in D:\MySQL DataDir

Hope this helps!

Note: as you can see from the forum thread linked above, the most common problem is due to the user under which mysql is running not having proper permissions on the target folders.

ASP and PostgreSQL quick snippet

Hi, here is a small snippet to connect to PostgreSQL from an ASP page and run a select query.

dim adoConn 
dim adoRS 
dim counter
 
set adoConn = Server.CreateObject("ADODB.Connection") 
set adoRS = Server.CreateObject("ADODB.Recordset")
 
strConn = "Provider=PostgreSQL.1;Data Source=localhost;location=postgres;" & _
"User Id=login_role;password=login_role_password;"
 
adoConn.Open strConn
 
adoRS.ActiveConnection = adoConn
 
if adoConn.errors.count = 0 then

    adoRS.Open "select * from phpbb.phpbb_users" 
        while not adoRS.EOF 
            response.write adoRS.fields(0).value
            adoRS.MoveNext 
        wend
else
    response.write "ERROR: Couldn't connect to database"
end if

Probably the most interesting thing is the connectionstring where:
Localhost is the server
postgres is the database
login_role is ... the login role AKA username
and in the query phpbb is the schema.
To avoid access denied errors you'll have to grant proper rights to the phpbb schema to user login_role, like :

GRANT ALL privileges ON schema phpbb TO login_role;

Hardware upgrades are good!

Hi there, working in the IT sector and standing in front of my laptop every day for many hours brought me to forget about my home desktop, used by my parents and expecially about their old CRT 17'' monitor.
I kept thinking it was a bit old but generally good, now I switched to a 17'' LCD display and I have to say that is's waaaaay much better, more environment friendly, more desk space friendly, more ... well, I increased the res to 1280x1024 from 1024x768 and that is the biggest improvement, even if I have to say that I'm probably on the edge as not too many web sites seem to be designed with that kind of res in mind.

I love it!!

After the Olympics Turin has become a very nice place to stay, here is another great day













I'd love an LX 125cc 4t to speed through the city.

Being a mod means ...

receiving a little gift from the owners of the site ;-) which is very much appreciated.
In my case the first gift turned into this book about composites.
Don't be fooled by the title, I'm more into the sailing or skiing
side of composites, but it's a nice book.









An year after I got another nice one, "The Design of Everyday Things", well I just received it and haven't had the time to even take a peek at it's content
but I love the whole idea of how design can be a great part of even the simplest thing.

Now if I only could discover why I'm charged an extra 5€ (the postal service asked me that money) for each book bought at Amazon.com ...

Good news from the blogs!!!

Yes, for you replication lovers on MS platform, who had great time with MsSQL and MySQL's replication now Slony (replication engine for PostgreSQL) is here.
See the original blog post by Robert Treat (with installation guide). A prebuilt windows binary is available here.

ASP and MySQL

Seems there is some interest in running MySQL as a backend for (classic) ASP based sites, at least looking at recent posts on DevShed forums.
As questions are quite similar and I've not found a good ASP+MySQL tutorial on the web I'm posting a short example here
Here is my snippet:

 
dim adoConn 
dim adoRS 
    set adoConn = Server.CreateObject("ADODB.Connection") 
    set adoRS = Server.CreateObject("ADODB.Recordset")
    adoConn.CursorLocation = 3 
        'adUseClient
    adoConn.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=users;UID=root;PWD=; OPTION=16427"
    adoRS.ActiveConnection = adoConn
        if adoConn.errors.count = 0 then
            adoRS.Open "Select ...", adoConn 
.....
        adoRS.Close
                                else
        response.write ("ERROR: Couldn't connect to database")
        end if
 
adoConn.Close
Set adoRS = nothing
Set adoConn = nothing 

And here is an excellent date conversion function by Devshed user.
Note that Mike Hillyer's writings about vb and MySQL are an excellent resource!!

MySQL does it again ... MySQL Forge!!!

Great snippet on Dynamic SQL in stored procedures from MySQLForge!!!

Migrating triggers from SQLServer to MySQL

After a few posts on Devshed forums I decided to summarize some basic directions about porting SQLServer triggers to MySQL.
There are some notable differencies between trigger syntax in SQLServer and MySQL (and other databases in general).
First of all usually you reference old (preexisting) and new (inserted or updated) column values by NEW.column_name and OLD.column_name, but in SQLServer those values are held in two tables named INSERTED and DELETED, on which you'll have to perform selects.
Second relevant difference is that SQLServer doesn't have BEFORE triggers, you'll have to use a rather convoluted syntax for this.
Third difference is that one SQLServer trigger can fire upon multiple actions (one single trigger for insert/update/delete).
Fourth difference, as new/old values are held in a table there is no FOR EACH ROW implicit loop but you'll have to loop through the table values with a cursor (or process records in a block through a DML statement).
Here are two examples:

First is a SQLServer trigger that converts a string into uppercase before inserting.

CREATE TRIGGER upper_all
ON test_table
instead of INSERT
AS
INSERT INTO test_table (a_field) SELECT UPPER(a_field) FROM INSERTED; 

Note that the trigger will fire upon insert cancelling the original insert and doing an alternate insert which converts into uppercase the value of a_field column.
The same in MySQL would be:

DELIMITER $$
 
DROP TRIGGER test.upper_all$$
 
CREATE TRIGGER upper_all before INSERT ON  test_table
 
FOR EACH ROW
BEGIN
 
SET NEW.a_field = UPPER(NEW.a_field);
 
END$$
 
DELIMITER ; 

IMHO this syntax is simpler and clearer.
Another example:

CREATE TRIGGER [mDBpush_Authors] ON [Authors] FOR INSERT, UPDATE,
DELETE AS
 
IF NOT EXISTS (SELECT * FROM INSERTED) -- "inserted" table is empty so must be delete trigger
INSERT INTO mDBpkstub (TransTime,PK,LayerName,Type,STATUS)
SELECT GetDate(),au_id,"Authors",3,0 FROM deleted
ELSE IF NOT EXISTS (SELECT * FROM DELETED) -- "deleted" table is empty so must be insert trigger
INSERT INTO mDBpkstub (TransTime,PK,LayerName,Type,STATUS)
SELECT GetDate(),au_id,"Authors",2,0 FROM inserted
ELSE -- both tables have entries so must be update trigger
INSERT INTO mDBpkstub (TransTime,PK,LayerName,Type,STATUS)
SELECT GetDate(),au_id,"Authors",1,0 FROM inserted 

This trigger fires for inserts/updates and deletes thus has to check which of the three events is causing it to fire and act accordingly.
In MySQL you'll be required to create three triggers, each firing before action, an example (BEFORE UPDATE) is:

DELIMITER $$
DROP TRIGGER test.mDBpush_Authors$$
CREATE TRIGGER mDBpush_Authors BEFORE UPDATE ON  Authors
FOR EACH ROW
BEGIN
SET NEW.TransTime = now();
-- PK field (au_id) comes from the update statement, no need to change it
SET NEW.LayerName = "Authors";
SET NEW.Type = 1;
SET NEW.STATUS = 0;
END$$
DELIMITER ;  

Hope this helps!
More on this here and here.

An INFORMATION_SCHEMA for Firebird?

Excellent blog post by Lorenzo Alberton! Is INFORMATION_SCHEMA coming to Firebird? I hope so!

EDIT:
The author left out one important bit of info that you can retrieve from Firebird, a list of Stored Procedures, here is the sql query:

SELECT* FROM rdb$procedures; 

MySQL full LOAD DATA INFILE example

Here is a more detailed example of LOAD DATA INFILE syntax:

First of all create a table to be loaded:

mysql> create table 2beloaded (field_1 char(1), field_2 int, field_3 char(1), fi
eld_4 varchar(50));
Query OK, 0 rows affected (0.15 sec)


Then the text file:

c, 1, a
b, 2, d

(named 2beloaded.txt and placed in c:\, so it's a file created on Windows platform!!)

Now the query to load it, we want to load the file setting proper line termination and all, but also to load field_4 of the table with the file name, here it is:

mysql> load data infile 'c:\\2beloaded.txt' into table 2beloaded fields terminat
ed by ',' optionally enclosed by '"' lines terminated by '\r\n' (field_1, field_2, field_3)
set field_4 = '2beloaded.txt';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Note that the file name is properly escaped, that the line is terminated by the usual windows sequence, that we are telling the server to load the first 3 fields of the table with data coming from the file and to load field_4 with a value provided by us (the file name).
Now check the result:

mysql> select * from 2beloaded;
+---------+---------+---------+---------------+
| field_1 | field_2 | field_3 | field_4 |
+---------+---------+---------+---------------+
| c | 1 | a | 2beloaded.txt |
| b | 2 | d | 2beloaded.txt |
+---------+---------+---------+---------------+
2 rows in set (0.01 sec)

Another example, with a file that has values separated by a space:

mysql> load data infile 'd:/file.txt' into table target
-> fields terminated by ' ' lines terminated by '\r\n'
-> (status, value)
-> set date_time = now();
Query OK, 3 rows affected (0.05 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from target;
+---------------------+--------+-------+
| date_time | status | value |
+---------------------+--------+-------+
| 2006-08-18 16:56:58 | a | 1 |
| 2006-08-18 16:56:58 | b | 2 |
| 2006-08-18 16:56:58 | c | 3 |
+---------------------+--------+-------+
3 rows in set (0.00 sec)

mysql>

MySQL triggers and Master/Detail tables

Have you ever wondered how to update the status of an order to "Closed" when the whole ordered quantity has been received?
If you are on a database that supports triggers like me (I'm on MySQL 5.0.16 right now) you can have something like:

Table structure:

Orders table

DROP TABLE IF EXISTS `test`.`orders`;
CREATE TABLE  `test`.`orders` (
  `order_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_date` datetime DEFAULT NULL,
  `order_status` CHAR(1) NOT NULL DEFAULT '',
  PRIMARY KEY  (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Order lines table

DROP TABLE IF EXISTS `test`.`order_lines`;
CREATE TABLE  `test`.`order_lines` (
  `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `order_line_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item` varchar(45) NOT NULL DEFAULT '',
  `order_quantity` float NOT NULL DEFAULT '0',
  PRIMARY KEY  (`order_line_id`,`order_id`),
  KEY `FK_order_lines_1` (`order_id`),
  CONSTRAINT `FK_order_lines_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Order deliveries table

DROP TABLE IF EXISTS `test`.`order_schedule_lines`;
CREATE TABLE  `test`.`order_schedule_lines` (
  `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `order_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `schedule_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `delivery_quantity` float NOT NULL DEFAULT '0',
  PRIMARY KEY  (`order_id`,`order_line_id`,`schedule_line_id`),
  CONSTRAINT `FK_order_schedule_lines_1` FOREIGN KEY (`order_id`, `order_line_id`) REFERENCES `order_lines` (`order_id`, `order_line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

You see, a lot of nice things, InnoDB tables, Foreign Keys ...
But now the real meat, a trigger that, after every insert in the "order_schedule_lines" table, where deliveries for each order line are kept, will compute the difference between ordered quantity and delivered quantity and will update the order status to closed when that difference is 0.

Trigger code:

DELIMITER $$
 
DROP TRIGGER test.close_order $$
 
CREATE TRIGGER close_order
AFTER INSERT
ON order_schedule_lines
 
 
 
  FOR EACH ROW
 
 
 
  BEGIN
 
  DECLARE a float;
 
SELECT
(ol.order_quantity - COALESCE(SUM(osl.delivery_quantity), 0))  INTO a
FROM orders o
INNER JOIN order_lines ol ON o.order_id = ol.order_id
LEFT OUTER JOIN order_schedule_lines osl ON ol.order_id = osl.order_id AND ol.order_line_id = osl.order_line_id
WHERE osl.order_id = NEW.order_id
GROUP BY
o.order_id;
 
IF a = 0 THEN
 
UPDATE orders SET order_status = 'C' WHERE order_id = NEW.order_id;
 
END IF;
 
  END $$
 
DELIMITER ; 

Of course it would be good to have another check (a before insert trigger? ;-)) that prevents further deliveries on closed orders ...

Partly inspired by this blog entry

Php PDO and Firebird

A quick snippet ...

try {
   $dbh = new PDO("firebird:dbname=localhost:C:\\Programmi\\Firebird\\Firebird_2_0\\examples\\empbuild\\EMPLOYEE.FDB", "SYSDBA", "masterkey");
   foreach ($dbh->query('SELECT COUNTRY from COUNTRY') as $row) {
     print_r($row);
   }   
   $dbh = null;  
} catch (PDOException $e) {
   print "Error!: " . $e->getMessage() . "
";
   die();
}
 
?> 

Php Firebird (ehm Interbase) functions ...

A post on Devshed forums showed that the php manual did not have any detailed info about ibase_service_attach and some other Interbase/Firebird functions, of course the question has been answered in the forums and the user promised to add a note on the official docs, anyway here is a small example of use of that specific function:
...
// get server version and implementation strings
if (($service = ibase_service_attach('localhost', 'sysdba', 'masterkey')) != FALSE) {
$server_info = ibase_server_info($service, IBASE_SVC_SERVER_VERSION)
. ' / '
. ibase_server_info($service, IBASE_SVC_IMPLEMENTATION);
ibase_service_detach($service);
}
else {
$ib_error = ibase_errmsg();
}
echo $server_info;

...

Which echoes something like

WI-T2.0.0.12484 Firebird 2.0 Release Candidate 1 / Firebird/x86/Windows NT

A more useful example would be using the whole thing to create a new user, you'll have to attach to server as a user with appropriate privileges (like SYSDBA) and then issue an:

ibase_add_user($service, 'pippo', 'pp')

where $service is the resorce got from the ibase_service_attach, 'pippo' is the username and 'pp' is the password


Sample code taken and adapted from the ibWebAdmin source code.

Replicating from MsSQL Server to PostgreSQL

Very interesting post from Magnus Hagander, take a look at it.

OpenLDAP Win32

Good news for OpenLDAP fans!!
Lucas Bergman with help from Matthias Mohr has put up a win32 build of OpenLDAP 2.2.29.
Thank you very much and keep up the good work.

Small Firebird trigger example

After reading a post in the Firebird forum at Devshed I immediately put up a small example about writing a trigger (and also upgraded my favourite tool, Flamerobin, to the latest version) , here it goes:

1. Create an empty table












2. Create the trigger on it using Flamerobin's guided menu




As you can see the menu will guide you through defining the trigger name, to activate it (in other words you can deactivate triggers without deleting them), setting it to fire before or after a DML statement (insert/update/delete) and also define it's position, which means that many triggers with the same action (i.e. "before insert") can exist on the same table and the position will tell the firing order.
Now the trigger code:

SET TERM ^ ;

CREATE TRIGGER modified_when FOR TEST_TABLE
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
/* enter trigger code here */
NEW.updated_at = CURRENT_TIMESTAMP;

END^

SET TERM ; ^

Note that after every DML and DDL statement you need to commit to make changes effective.
Now the test phase, I'll insert a dummy row:

insert into test_table (name_field) values ('pippo');

And check for the insert result completed by the trigger action:


See the result? ;-)

More infos about triggers in Firebird can be found in this article.

BTW: isn't Flamerobin's code autocompletion great?

GIMP 2.3.7 on WinXP

After many difficulties with preceding development releases I finally succeeded in compiling The GIMP on WinXP with MinGW.
Right now I only used a simple --configure --disable-print --disable-python --prefix=/d/gimp237 but I'll try with more complex config options and also adding littlecms to the pack, see some screenshots below

MySQL Workbench 1.0.5 beta ...

... nice, but not quite there yet.
I mean, it has a clean look at I processed the ported Firebird Employee database of my previous posts to check it's reverse engineering abilities, everything went smooth for tables and views (and foreign keys too!), but unfortunately no triggers and stored procs where reverse engineered.
Here is the visual cronicle:

Connection to database:










After the connection MySQL Workbench does a first roundtrip to retrieve structures:











Once structures are retrieved it offers you the option to choose which database to revers:












Right after the database selection you are asked to specify which objects belonging to it are going to be reverse engineered:











Reverse engineering is done:











Checking the result:










Exporting to sql:











Bye ;-)