Fun and games with foreign keys in Oracle

Thought I'd post two small scripts I wrote to take care of evil foreign keys when doing data restructuring.

First spool this script to a file to have the create statements of all FK's. The script does a self join to get the referenced column.

create_foreign_keys.sql

set lines 180
set pages 0
set heading off
set feedback off
select distinct
        'alter table ' ||c.table_name||
        ' add constraint '|| c.constraint_name ||
        ' foreign key ('||c.column_name||
        ') references ' || d.table_name ||
        '('|| d.column_name||');'
from
        dba_cons_columns c,
        dba_cons_columns d,
        dba_constraints a
where
        c.owner='SCHEMANAME'
        and c.constraint_name=a.constraint_name
        and a.constraint_type='R'
        and a.R_CONSTRAINT_NAME=d.CONSTRAINT_NAME
/

Then spool and execute this script to drop all constraints.

drop_foreign_keys.sql

set lines 180
set pages 0
set heading off
set feedback off
select distinct
        'alter table ' ||c.table_name||
        ' drop constraint '|| c.constraint_name ||';'
from
        dba_cons_columns c,
        dba_constraints a
where
        c.owner='SCHEMANAME'
        and c.constraint_name=a.constraint_name
        and a.constraint_type='R'
/

After you are done with your restructuring you just execute the first script to re-create all the foreign keys. Done!
Probably doesn't work on to advanced FK's but anyway. :)

phpNuke forums in your mobile

A few users over at Faktiskt.se got talking about how cool it would be to have the phpBB forum on their mobiles.
Well said and done, I sat down a wrote a small PHP script to do just that.
It probably doesn't comply with any WML standards but it works great on all phones we have tested it on (mostly semi-new Nokia and Sony/Ericssons (read XHTML)).

And lets say there is alot of room for improvements. Only functionality right now is basic browsing, I plan to add a search function and a text pre-processor (remove bbcode/large images etc) amongst other things when I find the time.
Posting will however probably not be implemented.

It shouldn't be to difficult to port it to a plain phpBB forum, just change on or two SQL queries.

Test drive (in Swedish) at http://www.faktiskt.se/wap/

Download the script from here.

Enterprise action

Lots of cool stuff happening in the IT world right now.
Summers over, get back to work!

Skype
Ebay buys Skype. I have my doubt that this was good for Skype but at least they made a shitload of cash ($2.6b + $1.5b to be specific). I myself is not to keep to use Skype with people on Ebay all the time. E-mail is good enough.
Reuters has got the scoop.

Siebel
I've been ranting about this one for a few years now :)
Oracle buys Siebel. Along with Peoplesoft Sibel got eaten by the beast.
Story at The Reg.

Sun
And I guess noone missed the NC05Q3 yesterday.
Some nice new Opteron boxes from Sun. Good boxes, but not amazing. Cool KVM remote access thingie, SAS 2.5" drives, low power consumption. I was hoping for an introduction of the new 32 thread SPARC procs (yes, after 3 years "suckyness" SPARCs are interesting again. Would make a perfect Java application server in my company. Wide load instead of the depth the Opterons give.
Anyway, the X-series is cool, good price. I'll try to get my hands on some :P

Cheap cables

I needed to order some Ethernet- and power-cables for our data centre and just did a quick Google for cables and found this company called Cable universe.
They seemed to have good prices, so I ordered a pack of power cables and Ethernet cables (violet cables for our MGMT/LOM segment *pretty*).
Cool, same day dispatch if I order before 3.15pm. Within 1 minute I had a "order is being processed" and after less than 4 minutes I received an e-mail saying that my order had been dispatched.

4 minutes for a complete order processing is quite impressive, I must say.

Highly recommended!

iPod nano

Apple does it again...

The new iPod nano must be the coolest gadget so far this year.
Sleak design as always. Looks amazing.
Priced quite good as well, 4Gb model is £179 GBP ($299 USD)

All together now:
-I want one!


Picture gallery

Dell PowerEdge SC1425 review

Since I never can find a review of servers before I buy them I thought it was my responsibility to do one myself.

Introduction
The SC1425 is thought to be a HPC-node or similar where more than one box is used. It gives good performance at a good price. But provides close to no redundancy, dual drives is about it. We will use our boxes as load balanced application servers where it doesn't really matter if one box fail as long as it is repaired in a reasonable time.
Starting price point in the UK is £339, the review spec. lists at £880.



Specs for my review box:

Dell PowerEdge SC1425

Singel Intel Xeon 3.2GHz

2Gb DDR2 ECC memory (4x512Mb modules)

2x 80Gb 7200rpm SATA drives

Dual port Intel Pro1000 Gigabit Ethernet

Intel SATA controller

Single PSU

1U rack-mount kit

No CD, no DVD, no floppy (Yes! the way I want it!)

See the Dell website for full specs.

Installation
Rack mounted in 4 minutes, nice snap-in rails.
I wrote a quick kickstart script for CentOS 4.
The PXE prompt appeared as it should I after entering my kickstart file and pressing enter twice it took about 30 minutes and voilaia, I had a clean login prompt and a pre-configured system. Quite lengthy install you might think but keep in mind that I did not have a hardware RAID controller in the box (£135 extra option) so I opted to build software-raid MD devices during the install so just syncing them took an extra 15 minutes.
Clean and simple, no hickups. All the hardware was supported, no problems with anything so far. [dmesg].
Logged in thru SSH and did a yum -y upgrade to get the latest kernel etc, nice 180Mb download. *sigh*
.

Management
During the startup I had seen a cool IPMI prompt flash by so I just had to reboot and check it out. The IPMI/BMC interface will give you some very basic remote managemepossibilitiestes outside the OS level. Like power cycle the machine or check sensor temperatures. Keep in mind you need a Dell patched ipmitool for use with Dell servers (bastards). Configured an IP for the IPMI card (which actually uses eth0 as it's mgmt interface) and set it to our mgmt vlan (yes, had to tag the port in the switch as well). Easy enough. All worked straight out. I could not find an option to switch on or off the gimp light.
The IPMI facility does not compare to the LOM cards in Sun boxes or the addon RAC cards from Dell, no remote console etc. But it does the trick to power cycle a frozen server. Totally acceptable for the price.


Performance
The SC1425 is very fast for the price, chipset is alright and I guess the only performance draw back is expected. The onboard SATA controller and software-raid.
CPU crunching and memory access is quite good.
During a the pgbench runs I got about 20% iowait which is far from good. But fixing this adds another £250 to the box price (PERC + SCSI drives).

PGBENCH results.

[root@ron1 pgbench]# ./pgbench -t 50 -c 50
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 50
number of transactions per client: 50
number of transactions actually processed: 2500/2500
tps = 149.314233 (including connections establishing)
tps = 150.790507 (excluding connections establishing)
[root@ron1 pgbench]# ./pgbench -t 50 -c 100
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 100
number of transactions per client: 50
number of transactions actually processed: 5000/5000
tps = 139.651475 (including connections establishing)
tps = 140.947096 (excluding connections establishing)

Compiling PostGRES 8.0.3

time make -j3 
real    1m58.274s
user    3m22.002s
sys     0m13.220s

Conclusion
My full impression of the Dell PowerEdge SC1425 is quite good. Price wise it is really good, performance is good and redundancy is as expected. I would never recommend someone to use this machine as a database server or office file server. It should purely to be used in farms of two or more boxes. Web servers, Citrix servers, application nodes or pure HPC.
They could have added a few more remote management features.

More pictures










* Note, all picture are taken with a Ericsson K750i mobile phone, quite good phone I must say. Thanks John.

PS
The SC1425 is known to work flawlessly under Solaris 10 so if I manage to get some time I will try to install it and check it out.

Ask slashdot about free RDBMS

Slashdot is running an "ask-slashdot" thread about comparing MySQL and PostgreSQL. Quite funny to see peoples misunderstandings of Postgres.

I just can't figure out why most people prefer MySQL over Postgres. Probably "user-friendliness", you can throw ugly SQL at it and it will still make a decent attempt to parse it and execute.
Secondly speed of course, however I find the speed point a bit hyped. When indexing Postgres properly with partial and functional indexes it is damn fast. And given that most of the MySQL speed optimizations are done with the sacrifice of transaction robustness and data concistantcy.
Now with bitmap scans in PG 8.1 it should fly.
On the other hand, all these things require a good DBA, not a "well I did rpm -i and it all is sooo fast" guy. :)

I'd say that people should read the slashdot debate with a pinch of salt, 90% of the people probably have no clue what they are talking about.

And don't forget that Mysql is now evil.

Read more over at Slashdot.Org

Postgres triggers

Postgres trigger
Database triggers are server side function that are executed on a row or statement when a DML command is received to the server. It's a good way to "fool" applications and users to believe things are done as they should or simply to cascade an command to more tables, i.e. logging.
Today (yes, on a damn Saturday) I was asked to try to create a scenario to stop our application from changing a value in the database so our client unit would repeat an operation for regression testing purpose (without modifying application code).
Simple, just add a trigger to change it back.

Here is a small trigger example for PG 8 (almost identical in Oracle except for some syntax changes):

-- Create sample table
CREATE TABLE dogs (
 DOG_ID  SERIAL PRIMARY KEY,
 NAME    VARCHAR(20),
 STATUS  VARCHAR(1)
);

-- Create function
CREATE FUNCTION dogstat() RETURNS trigger AS '
BEGIN
IF NEW.name=''FIDO'' THEN
  IF NEW.status=''S'' THEN
    UPDATE dogs SET STATUS=''A'' where name=''FIDO'';
  END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER dogtrigg AFTER UPDATE ON dogs
  FOR EACH ROW EXECUTE PROCEDURE dogstat();

-- Insert test
INSERT INTO dogs(name,status) VALUES('FIDO','A');
INSERT INTO dogs(name,status) VALUES('PLUTO','A');
-- Ok lets test
test1=> select * from dogs;
 dog_id | name  | status
--------+-------+--------
      1 | FIDO  | A
      2 | PLUTO | A
(2 rows)

test1=> update dogs set status='S' where name='FIDO';
UPDATE 1
-- Ok what happened?
test1=> select status from dogs where name='FIDO';
 status
--------
 A
(1 row)
-- Nothing really! Noone can (S)ell FIDO! :-)
-- What about PLUTO?
test1=> update dogs set status='S' where name='PLUTO';
UPDATE 1
test1=> select status from dogs where name='PLUTO';
 status
--------
 S
(1 row)
-- SOLD!

Ok, not to difficult.
However, I do not advice to use triggers all over the place in production databases. This sort of logic should be at the application level in my opinion, but in this case for a regression test scenario it works very well.

Want to read more?
Postgres 8.0 trigger docs