gpg primer

miblogic@gmail.com procedure:

gpg --gen-key
gpg --list-keys

// export public key, --armor makes the key file ascii readable
gpg --armor --export miblogic@gmail.com > miblogic.gmail.public.key
// just the same
gpg -a --export miblogic@gmail.com > miblogic.gmail.public.key

// export private key
gpg --armor --export-secret-key miblogic@gmail.com > miblogic.gmail.private.key


// import the public key of miblogic@yahoo.com
gpg --import miblogic.yahoo.public.key




// gpg a piped output:
pg_dump -i -h 127.0.0.1 -p 5432 -U postgres -F c -v Cashflow | gpg --recipient miblogic@yahoo.com --encrypt --output Cashflow.backup.gpg


// gpg a file:
gpg --recipient miblogic@yahoo.com ---output file.txt.encrypted --encrypt file.txt


// miblogic@yahoo.com decrypting a message from miblogic@gmail.com
gpg --recipient miblogic@yahoo.com --output the_file.txt --decrypt file.txt.encrypted

my first time to win computer chess (GNU Chess)

killing time from our Linux server this afternoon after i ate lunch :-D



:-) my first time to win again against computer, also my first time to play GNU Chess (using normal settings) next time i'll try the difficult level

simple rant: a CRUD day

i don't get all these trend about using stored procs for CRUD. i'm not a web app programmer, i mostly develop desktop apps. how to code stored procs for header and detail tables (textboxes above and datagridview below) ?

i would use stored procs for reports. but for desktop app CRUD routines? 怎么办 (how to do?)

using stored procs for simple database chores such as CRUD have an architecture astronaut ring to me. that's my impression of stored proc'd CRUDs

if there's a way to pass the delta of the dataset (header and detail tables) directly to just one stored proc (create procedure invoice_save @invoice_rec_id varchar, @invoice_header table, @invoice_detail table), i might use SP for CRUD

i learned that ms sql 2005 allow passing XML(passing list of rows is now allowed) to stored procs, so passing of header and detail to SP is now allowed, but then again, another problem persists, for datagrid's changes, does mssql 2005 accepts datatable's delta (datagrid's changes) through XML?

is enforcing business logic on C# middletier not elegant? Remoting and Webservices can also act as stored proc for rdbms(es) which don't have stored proc. it's also centrally managed, no need to re-distribute the business logic changes to other apps (desktop or otherwise). and also, these middle-tier stuffs are more amenable to horizontal scaling

in this age of B2B, do you think enterprise communicate to each other directly via stored procs? web services, ajax, remoting, etc also have these "centrally-managed" aspect in it

with all these great facilitators (dcom, rds, java rmi, .net web services, .net remoting) of centrally-managed code, would you still use stored procs?

why app programmers of the world, why do you code your CRUD on stored proc? do middletiers doesn't cut it?

P.S.
i will not convert my datagridview to to textboxes, users find datagrid editing very intuitive. would you like to earn the ire of my programs' users? :-)

sql code readability

though logically the same, this is easier to read:

select coalesce(
(select CURRENT_TIMESTAMP > unlocked_expiration
from invoice
where invoice_rec_id = 'value here'), true) as has_expired


than this:

select coalesce(
(select unlocked_expiration < CURRENT_TIMESTAMP
from invoice
where invoice_rec_id = 'value here'), true) as has_expired

NULL should be renamed to UNKNOWN

problem:

the following has no output when it should has:

select * from src where n not in (select n from dst);

reproduce the problem:

create table src (n int);

create table dst(n int);

insert into src(n) values(1),(2),(3),(4),(5);
insert into dst(n) values(1),(2),(3);
select * from src where n not in (select n from dst); -- lists 4, 5

insert into dst(n) values(null);
select * from src where n not in (select n from dst); -- no output

should list 4 and 5?

it isn't, it didn't list anything

hmm.. seems strange

explanation:

SELECT 'SEE' WHERE 7 IN (SELECT 2 UNION SELECT 4)
is a shorthand for:
SELECT 'SEE' WHERE 7 = 2 OR 7 = 4



SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4

now let's inject NULL data :

SELECT 'SEE' WHERE 7 IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 = 2 OR 7 = 4 or 7 = NULL



SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4 AND 7 <> NULL

you cannot compare NULL, null means unknown. by the virtue of it being unknown, the expression 7 <> NULL is neither true nor false. the expression 7 = NULL, is also neither true nor false. 7 <> NULL: NULL, 7 = NULL: NULL.

100 <> 500 results true
100 = 500 results false
100 <> NULL results NULL
100 = NULL results NULL
100 IS NULL results false
100 IS NOT NULL results true
NULL = NULL results NULL
NULL <> NULL results NULL

NULL AND NULL results NULL
NULL AND TRUE results NULL
TRUE AND NULL results NULL
TRUE AND TRUE results TRUE

NULL OR NULL results NULL
NULL OR TRUE results TRUE
TRUE OR NULL results TRUE
TRUE OR TRUE results TRUE


NULL represents unknown, cannot be determined, not yet determined

rough analogy, think of WHERE A <> B as left hand <> right hand

WHERE 100 <> NULL

in plain english: is the left hand holding 100 dollar bill different with the bill in the closed right hand?

so what's the answer for 100 <> NULL?


let's have an example, your friend approach you, his/her left hand is open, you can see 100 bucks, right hand is close, you can't see anything. then he/she asks you if right hand holds different bill than the left hand, what would you(or any human) would answer? a human would answer, "it's different", "not different", or could answer "i don't know"


while on a computer, when you ask it, the computer's answer is always "i don't know." there is no gratification for a computer to guess correctly. perhaps, it's human nature to try to predict unknown forces, like trying to gauge if himself is capable of ESP, of capable of correctly predicting things 9 out of 10, or inclined in believing in divination(god will help me correctly guess so i can win this Deal or No Deal). humans are constantly feeding on gratifications, be it a guessing game, or trying their "pure luck"(believing in divination sometimes plays a large role here). so much for digressing :-)



so there we go, computers can't guess, it always answer "i don't know" when faced with unknown.


so the answer for the expression WHERE 100 <> NULL is unknown. neither true nor false. 100 = NULL is also unknown, neither true nor false. perhaps, RDBMS vendors should rename NULL to UNKNOWN.


let's try replacing NULL with UNKNOWN

100 <> 500 results true
100 = 500 results false
100 <> UNKNOWN results UNKNOWN
100 = UNKNOWN results UNKNOWN
100 IS UNKNOWN results false
100 IS NOT UNKNOWN results true
UNKNOWN = UNKNOWN results UNKNOWN
UNKNOWN <> UNKNOWN results UNKNOWN

UNKNOWN AND UNKNOWN results UNKNOWN
UNKNOWN AND TRUE results UNKNOWN
TRUE AND UNKNOWN results UNKNOWN
TRUE AND TRUE results TRUE


UNKNOWN OR UNKNOWN results UNKNOWN
UNKNOWN OR TRUE results TRUE
TRUE OR UNKNOWN results TRUE
TRUE OR TRUE results TRUE


there it is, more intuitive, perhaps when reading NULL, mentally read them as UNKNOWN.


these would not return a row:
SELECT * FROM employee WHERE FALSE
SELECT * FROM employee WHERE NULL

this could:
SELECT * FROM employe WHERE TRUE




let's go back on NOT IN construct:


SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4 AND 7 <> NULL

resolved to:
SELECT 'SEE' WHERE TRUE AND TRUE AND NULL

then resolved to:
SELECT 'SEE' WHERE NULL


final result: no output

perhaps the rationale for using the word NULL is because it sounds so computer-sciencey. as for not using UNKNOWN as an identifier for er.. unknown, heck, it's hard to read double negatives, NOT UNKNOWN, oh.. humour me :-)

but... all these arguments can be thrown out of the window when mainstream programming languages like c# go the extra mile to make null comparisons not much to fuzz about:

int? a = 7;

int? b = null;

if (a != null) Console.WriteLine("indeed");

if (a != b) Console.WriteLine("still indeed");

programmer's life is sometimes difficult, impedance mismatches on our array of tools are in your face. our tools sometimes deviates from what we expected and asserted them to be. nonetheless we're constantly humbly learning to deal and get over with it.

which led me to the conclusion that i shouldn't have written these IS NULL, IS NOT NULL stuffs in the first place :-)

// Buen

Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
- Fred Brooks, Jr.

P.S.

i'd go on a limb here to generalize VB.NET's inconsistency(for the nth time), it's the MySQL of imperative programming languages. null handling in vb.net is basically broken. but perhaps one of VB.NET's few redeeming factor is its block-structured constructs, e.g. If-EndIf, Select-EndSelect, For-Next, Do-Loop, etc, they are very programmer-friendly, i wish C# use those block delineators instead of curly brackets

string s = null;
if (s == "") MessageBox.Show("Not Good"); else MessageBox.Show("Good");
// outputs Good

Dim s As String
s = Nothing
If s = "" Then
MessageBox.Show("Broken")
Else
MessageBox.Show("Expect Nothing")
End If
' outputs Broken

How to detect if field's value changes in UPDATE trigger

use this approach if your field is NOT NULLable:

if NEW.field <> OLD.field then
raise notice 'something changes';
end if;





use this approach if your field is NULLable: # is the XOR of Postgres



false xor false = false
false xor true = true
true xor false = true
true xor true = false


if

( (NEW.field is null)::int # (OLD.field is null)::int ) = 1


OR

NEW.field <> OLD.field

then

raise notice 'something changes';

end if;

Calculate Age or Birthday

CREATE or replace FUNCTION CalculateAge(m_birthday date, m_datetoday date)
RETURNS INTEGER
AS
$$
BEGIN

RETURN

(
select
(
CASE
WHEN dateadd('y', datediff ('y', m_birthday, m_datetoday), m_birthday) > m_datetoday
THEN datediff ('y', m_birthday, m_datetoday) - 1
ELSE datediff ('y', m_birthday, m_datetoday)
END)

);

END;
$$ language 'plpgsql';

-- see my previous post on dateadd and datediff

gapless sequence without using extra table, however, locks the whole table

CREATE OR REPLACE FUNCTION get_tryrecord_lastid()
RETURNS int AS
$$
begin
lock table tryrecord;
return coalesce( (select tryrecord_id from tryrecord order by tryrecord_id desc limit 1) + 1, 1 );
end;
$$
LANGUAGE 'plpgsql' ;





CREATE TABLE tryrecord
(
tryrecord_id integer NOT NULL DEFAULT get_tryrecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_tryrecord PRIMARY KEY (tryrecord_id)
);



begin;
insert into tryrecord(name) values('see');
insert into tryrecord(name) values('great');
end;

gapless sequence without locking the whole table

create table therecord_lastid
(
last_id int default 0,
constraint pk_therecord_lastid primary key (last_id)
);
insert into therecord_lastid values(0);



CREATE OR REPLACE FUNCTION get_therecord_lastid()
RETURNS int AS
$$
declare
x int;
begin
update therecord_lastid set last_id = last_id + 1;
select last_id into x from therecord_lastid;
return x;
end;
$$
LANGUAGE 'plpgsql' ;





CREATE TABLE testrecord
(
testrecord_id integer NOT NULL DEFAULT get_therecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_testrecord PRIMARY KEY (testrecord_id)
);



begin;
insert into testrecord(name) values('see');
insert into testrecord(name) values('great');
end;

select * from testrecord

if you have many foreign keys, how to check that only one of them must not be null

create table shipment
(
int seq serial bigint not null

client_code,
supplier_code,
testing_company_code,
branch_code,

date_to_ship date,

constraint fk_shipment__client foreign key(client_code)
references client(client_code),


constraint fk_shipment__supplier foreign key(supplier_code)
references supplier(supplier_code),


constraint fk_shipment__testing_company foreign key(testing_company_code)
references testing_company(testing_company_code),

constraint fk_shipment__branch foreign key(branch_code)
references branch(branch_code),



constraint ck_shipment__company check
(
(client_code is not null)::int
+
(supplier_code is not null)::int
+
(testing_company_code is not null)::int
+
(branch_code is not null)::int
) = 1

);



Note: if you only need to test at least one of them is not null, just change = 1 to >= 1.

or better yet, do this:

constraint ck_shipment__company

check(coalesce(client_code, supplier_code, testing_company_code, branch_code) is not null)

Postgres is faster on Windows XP (on AMD only)

(an e-mail i wrote to my peers last july 23, guess it's better to post this on blog. these are all out-of-the-box installation)



this is my latest benchmarkings from fresh installation i performed last saturday, i use the same drive on all installation. Western Digital 160 GB.

Core 2 Duo E6700 2.67 GHz vs AMD64 3400+ 2.0 GHz

select * from technicalsheet_color order by techsheetid, recid limit 100:

c2d 2.67 GHz + ubuntu64 + postgresql = 6 seconds
amd64 2.0 GHz + ubuntu64 + postgresql = 9 seconds

c2d 2.67 GHz + winxp32 + postgresql = 12 seconds
amd64 2.0 GHz + winxp32 + postgresql = 8 seconds


as you can gloss from the results, the speed differences are not symmetrical, it is hard to draw one-sweeping-generalization™ conclusion ;-)

PostgreSQL on laptops doesn't bodes well for Windows XP users, because most of laptops are Core 2 Duo than AMD64/Turion. PostgreSQL runs two times faster on Core 2 Duo if you only use Linux.


upsides:
* PostgreSQL on Ubuntu64 is faster on Core 2 Duo
* PostgreSQL on WinXP32 is faster on AMD64


downsides:
* PostgreSQL on WinXP32 slow down miserably on Core 2 Duo
* on AMD64, PostgreSQL is only marginally faster on WinXP32 than its Ubuntu64 counterpart


it is interesting to note that Dave Cutler (Windows NT,2000,XP,Vista,2003 chief engineer) have work closely/co-developed AMD64 with AMD, this could explain why Windows runs marginally better on AMD64 than Core 2 Duo.
and also, maybe AMD64 or Athlon X2 runs better than Core 2 Duo, i haven't yet pitted AMD's 2.6 GHz against Core 2 Duo 2.67 GHz :-)


on Core 2 Duo, WinXP to Ubuntu gains two-fold speed increase, from 12 seconds to 6 seconds. at first, i thought AMD64 WinXP to Ubuntu would gain the same twofold speed increase (8 to 4 seconds), but as i benchmarked it, it wasn't.
AMD64 seems was designed to run Windows well, Microsoft's Dave Cutler and AMD collaboration at its best :-)






just draw your own conclusions :-)

i'll just choose the best combo of processor + os + database




p.s.


these are the results without the ordering:

select * from technicalsheet_color limit 100:

c2d 2.67 GHz + ubuntu64 + postgresql = 11 seconds
amd64 2.0 GHz + ubuntu64 + postgresql = 16 seconds


c2d 2.67 GHz + winxp32 + postgresql = 19 seconds
amd64 2.0 GHz + winxp32 + postgresql = 13 seconds