26-NOV-2009

After three extensions, and what I imagine was two weeks of running at risk, my executed contract extension came back today. It looks like I'm employed until next Thanksgiving.

Hedging the Cost of Energy

What an exciting year for energy! If you were an investing genius like me, you bought high and sold low. All is not lost, however, because my investments in energy were (mostly) just a hedge, so they did exactly what they were supposed to do.

For example: my electric bill is about $100 per month, so in January I purchased $1200.00 in Dominion directly through Dominion DRIPS. Today, my stock is worth $1,020.98 (though I have more shares because of dividend payments). Even though the value of my stock went down, my energy costs went down, which should offset the loss. I'll be investing another $1200.00 next month.

Maybe you don't pay an electric bill, but you do put gas in your car. I decided to look at my last year of spending on gas, lined up against the average price of a gallon of gas in the US:



I spent an average of $119.02 every two weeks, or $2,856.42 for the year. This number is slightly inflated if you consider that I pay for gas with my American Express Blue Cash card, which rewards me 1.0% on gas for the first $6,500.00 per year I charge on the card, and 5% on gas after I pass the $6,500.00 mark. As of October's statement (11 months), I had earned $247.54 on $6,249.73 spending (including Gas,Grocery, & Drugstore).

So what does all this mean? If I spend roughly $3,000.00 a year on gas, I should be hedging that amount in an equivalent energy hedge. That way, if the price of gas goes up, the value of my hedge goes up as well. If it goes down, even though the value of my hedge is reduced, so is the amount I spend on gas. This way, I'm don't get it coming and going when commodity prices fluctuate (which they are expected to through 2010 as we ease out of the current recession).

Now, where do you park your money? You could buy stock in Exxon-Mobile (a company forged in the fires of hell from pure evil), Shell, Chevon, BP, or any other company with a big stake in US retail gas sales. You could invest directly in oil through an exchange traded fund (ETF) like USO. You could invest in the broader energy market through an ETF like Vanguard's Energy ETF.

However you decide to hedge, it's something you should put on your list of New Year's resolutions.

Training

Next Mon/Tue and the following Mon/Tue/Wed, I'll be in iPhone SDK development training. By Christmas, I will have built my social networking War on Christmas app, putting that fat bitch Santa-Jesus on the unemployment rolls.

Cranky Friday

My cell phone has rung off the hook today.

I did something to hurt my lower back.

I had an incredibly annoying conference call this morning.

The only thing that will turn my day around is the french press I just made myself.

gforge hero

We use gforge for our software projects: tracking bugs and feature requests, storing documentation, and a bunch of other silly shit. Gforge is, in my opinion, somewhere between Jira (high end) and Bugzilla (low end) in the usability spectrum for this type of software. Likewise, beef bullion is somewhere between prime rib and feces in my choices for dinner tonight.

On November 6th, like a good project manager, I was cleaning up the enhancement tracker by removing choices from drop downs that aren't valid anymore (we renamed our 2.0.3 release 2.1, so 2.0.4 will now become 2.2), and accidentally deleted the entire enhancement request tracker. The error message doesn't tell you what you're doing (the cranky gforge admin didn't even blame me or say I fucked up), and I'm the fourth person to do this in as many years.

Our RFE tracker is 300+ items, collected over 4+ years, and includes notes, history, status, and all kinds of other information. This was a huge deal. Our system team, unwilling to restore a backup (which would punish everyone by obliterating their work for that day), restored a backup to our QA instance and told me to retype everything from there. I asked for access to the database and said I'd write the fix myself. I'm sure they were like 'whatever...project manager'.

My solution: archive tables and triggers on the 13 tables that hold tracker information (which I reverse engineered though my local backup/restore). Not only could I recover my shit this way, but the next fuckup won't be typing for days for a bad keystroke.

Unlike me -- who has used Oracle, SQL Server, Sybase, DB2, Informix, and MySQL -- gforge uses postgres. That meant I had to teach myself how to code for postgres (which, being based on Ingres, is 80% Oracle and 20% schizophrenic milk man). I could write 5 pages of the sillyness that is postgres (look: I'm Ada-like, but everything is a function, and triggers can't contain the trigger code, they must call a function that returns type trigger). Criminy.

I finally had a good backup and local copy of the db 11-NOV, had the archive table and trigger code written and debugged 12-NOV, and finished all the functions to restore and test by 13-NOV. I tested everything today, and all my codes without a single correction (and my test cases are tight as hell).

Now we just need to "delete" the data from QA, backup/restore the archive tables to production, and use my code to turn hamburger back into cow. I'm no expect, but I think I win.

-----------------------------------------------------------------
-- Table: 1.) deleted_artifact_history
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_history;
CREATE TABLE deleted_artifact_history(
like artifact_history
)
WITH OIDS;
ALTER TABLE deleted_artifact_history OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_history TO postgres;
ALTER TABLE deleted_artifact_history ADD PRIMARY KEY (id);

-- Function: fn_deleted_artifact_history
CREATE OR REPLACE FUNCTION fn_deleted_artifact_history()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_history SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_history on artifact_history
--DROP TRIGGER deleted_artifact_history ON artifact_history;
CREATE TRIGGER deleted_artifact_history
BEFORE DELETE
ON artifact_history
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_history();
-----------------------------------------------------------------
-- Table: 2.) deleted_artifact_file
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_file;
CREATE TABLE deleted_artifact_file(
like artifact_file
)
WITH OIDS;
ALTER TABLE deleted_artifact_file OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_file TO postgres;
ALTER TABLE deleted_artifact_file ADD PRIMARY KEY (id);

-- Function: fn_deleted_artifact_file
CREATE OR REPLACE FUNCTION fn_deleted_artifact_file()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_file SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_file on artifact_file
--DROP TRIGGER deleted_artifact_file ON artifact_file;
CREATE TRIGGER deleted_artifact_file
BEFORE DELETE
ON artifact_file
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_file();
-----------------------------------------------------------------
-- Table: 3.) deleted_artifact_group_list
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_group_list;
CREATE TABLE deleted_artifact_group_list(
like artifact_group_list
)
WITH OIDS;
ALTER TABLE deleted_artifact_group_list OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_group_list TO postgres;
ALTER TABLE deleted_artifact_group_list ADD PRIMARY KEY (group_artifact_id);

-- Function: fn_deleted_artifact_group_list
CREATE OR REPLACE FUNCTION fn_deleted_artifact_group_list()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_group_list SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_group_list on artifact_group_list
--DROP TRIGGER deleted_artifact_group_list ON artifact_group_list;
CREATE TRIGGER deleted_artifact_group_list
BEFORE DELETE
ON artifact_group_list
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_group_list();
-----------------------------------------------------------------
-- Table: 4.) deleted_artifact_extra_field_data
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_extra_field_data;
CREATE TABLE deleted_artifact_extra_field_data(
like artifact_extra_field_data
)
WITH OIDS;
ALTER TABLE deleted_artifact_extra_field_data OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_extra_field_data TO postgres;
ALTER TABLE deleted_artifact_extra_field_data ADD PRIMARY KEY (data_id);

-- Function: fn_deleted_artifact_extra_field_data
CREATE OR REPLACE FUNCTION fn_deleted_artifact_extra_field_data()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_extra_field_data SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_extra_field_data on artifact_extra_field_data
--DROP TRIGGER deleted_artifact_extra_field_data ON artifact_extra_field_data;
CREATE TRIGGER deleted_artifact_extra_field_data
BEFORE DELETE
ON artifact_extra_field_data
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_extra_field_data();
-----------------------------------------------------------------
-- Table: 5.) deleted_artifact_extra_field_list
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_extra_field_list;
CREATE TABLE deleted_artifact_extra_field_list(
like artifact_extra_field_list
)
WITH OIDS;
ALTER TABLE deleted_artifact_extra_field_list OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_extra_field_list TO postgres;
ALTER TABLE deleted_artifact_extra_field_list ADD PRIMARY KEY (extra_field_id);

-- Function: fn_deleted_artifact_extra_field_list
CREATE OR REPLACE FUNCTION fn_deleted_artifact_extra_field_list()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_extra_field_list SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_extra_field_list on artifact_extra_field_list
--DROP TRIGGER deleted_artifact_extra_field_list ON artifact_extra_field_list;
CREATE TRIGGER deleted_artifact_extra_field_list
BEFORE DELETE
ON artifact_extra_field_list
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_extra_field_list();
-----------------------------------------------------------------
-- Table: 6.) deleted_artifact_perm
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_perm;
CREATE TABLE deleted_artifact_perm(
like artifact_perm
)
WITH OIDS;
ALTER TABLE deleted_artifact_perm OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_perm TO postgres;
ALTER TABLE deleted_artifact_perm ADD PRIMARY KEY (id);

-- Function: fn_deleted_artifact_perm
CREATE OR REPLACE FUNCTION fn_deleted_artifact_perm()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_perm SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_perm on artifact_perm
--DROP TRIGGER deleted_artifact_perm ON artifact_perm;
CREATE TRIGGER deleted_artifact_perm
BEFORE DELETE
ON artifact_perm
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_perm();
-----------------------------------------------------------------
-- Table: 7.) deleted_artifact_type_monitor
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_type_monitor;
CREATE TABLE deleted_artifact_type_monitor(
like artifact_type_monitor
)
WITH OIDS;
ALTER TABLE deleted_artifact_type_monitor OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_type_monitor TO postgres;
ALTER TABLE deleted_artifact_type_monitor ADD PRIMARY KEY (group_artifact_id,user_id);

-- Function: fn_deleted_artifact
CREATE OR REPLACE FUNCTION fn_deleted_artifact_type_monitor()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_type_monitor SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_type_monitor on artifact_type_monitor
--DROP TRIGGER deleted_artifact_type_monitor ON artifact_type_monitor;
CREATE TRIGGER deleted_artifact_type_monitor
BEFORE DELETE
ON artifact_type_monitor
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_type_monitor();
-----------------------------------------------------------------
-- Table: 8.) deleted_artifact_query
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_query;
CREATE TABLE deleted_artifact_query(
like artifact_query
)
WITH OIDS;
ALTER TABLE deleted_artifact_query OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_query TO postgres;
ALTER TABLE deleted_artifact_query ADD PRIMARY KEY (artifact_query_id);

-- Function: fn_deleted_artifact_query
CREATE OR REPLACE FUNCTION fn_deleted_artifact_query()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_query SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_query on artifact_query
--DROP TRIGGER deleted_artifact_query ON artifact_query;
CREATE TRIGGER deleted_artifact_query
BEFORE DELETE
ON artifact_query
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_query();
-----------------------------------------------------------------
-- Table: 9.) deleted_artifact_query_fields
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_query_fields;
CREATE TABLE deleted_artifact_query_fields(
like artifact_query_fields
)
WITH OIDS;
ALTER TABLE deleted_artifact_query_fields OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_query_fields TO postgres;
ALTER TABLE deleted_artifact_query_fields ADD PRIMARY KEY (artifact_query_id, query_field_type, query_field_id);

-- Function: fn_deleted_artifact_query_fields
CREATE OR REPLACE FUNCTION fn_deleted_artifact_query_fields()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_query_fields SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_query_fields on artifact_query_fields
--DROP TRIGGER deleted_artifact_query_fields ON artifact_query_fields;
CREATE TRIGGER deleted_artifact_query_fields
BEFORE DELETE
ON artifact_query_fields
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_query_fields();
-----------------------------------------------------------------
-- Table: 10.) deleted_artifact_monitor
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_monitor;
CREATE TABLE deleted_artifact_monitor(
like artifact_monitor
)
WITH OIDS;
ALTER TABLE deleted_artifact_monitor OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_monitor TO postgres;
ALTER TABLE deleted_artifact_monitor ADD PRIMARY KEY (artifact_id, user_id);

-- Function: fn_deleted_artifact_monitor
CREATE OR REPLACE FUNCTION fn_deleted_artifact_monitor()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_monitor SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_monitor on artifact_monitor
--DROP TRIGGER deleted_artifact_monitor ON artifact_monitor;
CREATE TRIGGER deleted_artifact_monitor
BEFORE DELETE
ON artifact_monitor
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_monitor();
-----------------------------------------------------------------
-- Table: 11.) deleted_artifact_message
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_message;
CREATE TABLE deleted_artifact_message(
like artifact_message
)
WITH OIDS;
ALTER TABLE deleted_artifact_message OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_message TO postgres;
ALTER TABLE deleted_artifact_message ADD PRIMARY KEY (id);

-- Function: fn_deleted_artifact_message
CREATE OR REPLACE FUNCTION fn_deleted_artifact_message()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_message SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_message on artifact_message
--DROP TRIGGER deleted_artifact_message ON artifact_message;
CREATE TRIGGER deleted_artifact_message
BEFORE DELETE
ON artifact_message
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_message();
-----------------------------------------------------------------
-- Table: 12.) deleted_artifact_counts_agg
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_counts_agg;
CREATE TABLE deleted_artifact_counts_agg(
like artifact_counts_agg
)
WITH OIDS;
ALTER TABLE deleted_artifact_counts_agg OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_counts_agg TO postgres;
ALTER TABLE deleted_artifact_counts_agg ADD PRIMARY KEY (group_artifact_id);

-- Function: fn_deleted_artifact_counts_agg
CREATE OR REPLACE FUNCTION fn_deleted_artifact_counts_agg()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_counts_agg SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact_counts_agg on artifact_counts_agg
--DROP TRIGGER deleted_artifact_counts_agg ON artifact_counts_agg;
CREATE TRIGGER deleted_artifact_counts_agg
BEFORE DELETE
ON artifact_counts_agg
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_counts_agg();
-----------------------------------------------------------------
-- Table: 13.) deleted_artifact
-----------------------------------------------------------------
--DROP TABLE deleted_artifact;
CREATE TABLE deleted_artifact(
like artifact
)
WITH OIDS;
ALTER TABLE deleted_artifact OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact TO postgres;
ALTER TABLE deleted_artifact ADD PRIMARY KEY (artifact_id);

-- Trigger: deleted_artifact_update_last_modified_date on deleted_artifact
--DROP TRIGGER deleted_artifact_update_last_modified_date ON deleted_artifact;
CREATE TRIGGER deleted_artifact_update_last_modified_date
BEFORE INSERT OR UPDATE
ON deleted_artifact
FOR EACH ROW
EXECUTE PROCEDURE update_last_modified_date();

-- Function: fn_deleted_artifact
CREATE OR REPLACE FUNCTION fn_deleted_artifact()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Trigger: deleted_artifact on artifact
--DROP TRIGGER deleted_artifact ON artifact;
CREATE TRIGGER deleted_artifact
BEFORE DELETE
ON artifact
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact();

and..

-----------------------------------------------------------------
-- Test Case: I. Archive Deleted Records
-- Precondition 1) Deleted tables have 0 records
-- 2) Artifact tables have N records
-- Postcondition 1) Deleted tables have N records
-- 2) Artifact tables reduced by N records
-- Execution 1) In isolation (from SQL prompt)
-- 2) Through gforge GUI
-----------------------------------------------------------------
-----------------------------------------------------------------
-- Test Case: II. Restore Deleted Records
-- Precondition: 1) Deleted tables have X records
-- 2) Artifact tables have Y records
-- Postcondition 1) Deleted tables have 0 records
-- 2) Artifact tables = X + Y records
-- Execution 1) In isolation (from SQL prompt)
-- 2) Through gforge GUI
-----------------------------------------------------------------
CREATE TABLE testRecordCount (
run_type varchar,
artifact integer NOT NULL,
d_artifact integer NOT NULL,
a_history integer NOT NULL,
d_a_history integer NOT NULL,
a_file integer NOT NULL,
d_a_file integer NOT NULL,
a_extra_field_data integer NOT NULL,
d_a_extra_field_data integer NOT NULL,
a_group_list integer NOT NULL,
d_a_group_list integer NOT NULL,
a_perm integer NOT NULL,
d_a_perm integer NOT NULL,
a_type_monitor integer NOT NULL,
d_a_type_monitor integer NOT NULL,
a_query integer NOT NULL,
d_a_query integer NOT NULL,
a_query_fields integer NOT NULL,
d_a_query_fields integer NOT NULL,
a_monitor integer NOT NULL,
d_a_monitor integer NOT NULL,
a_message integer NOT NULL,
d_a_message integer NOT NULL,
a_counts_agg integer NOT NULL,
d_a_counts_agg integer NOT NULL,
run_date date NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION populateTestRecordCount (notes varchar, tracker_id int)
RETURNS VOID AS $$
DECLARE
v_artifact int;
v_d_artifact int;
v_a_history int;
v_d_a_history int;
v_a_file int;
v_d_a_file int;
v_a_extra_field_data int;
v_d_a_extra_field_data int;
v_a_group_list int;
v_d_a_group_list int;
v_a_perm int;
v_d_a_perm int;
v_a_type_monitor int;
v_d_a_type_monitor int;
v_a_query int;
v_d_a_query int;
v_a_query_fields int;
v_d_a_query_fields int;
v_a_monitor int;
v_d_a_monitor int;
v_a_message int;
v_d_a_message int;
v_a_counts_agg int;
v_d_a_counts_agg int;
BEGIN
-----------------------------------------------------------------
-- Artifact Tables
-----------------------------------------------------------------
select into v_artifact count(*) from artifact; --where group_artifact_id=tracker_id;
select into v_a_history count(*) from artifact_history;
--where artifact_id in (select artifact_id from artifact where group_artifact_id=tracker_id);
select into v_a_file count(*) from artifact_file;
--where artifact_id in (select artifact_id from artifact where group_artifact_id=tracker_id);
select into v_a_extra_field_data count(*) from artifact_extra_field_data;
--where artifact_id in (select artifact_id from artifact where group_artifact_id=tracker_id);
select into v_a_group_list count(*) from artifact_group_list; --where group_artifact_id=tracker_id;
select into v_a_perm count(*) from artifact_perm; --where group_artifact_id=tracker_id;
select into v_a_type_monitor count(*) from artifact_type_monitor; --where group_artifact_id=tracker_id;
select into v_a_query count(*) from artifact_query; --where group_artifact_id=tracker_id;
select into v_a_query_fields count(*) from artifact_query_fields;
--where artifact_query_id in (select artifact_query_id from artifact_query where group_artifact_id=tracker_id);
select into v_a_monitor count (*) from artifact_monitor;
select into v_a_message count (*) from artifact_message;
select into v_a_counts_agg count (*) from artifact_counts_agg;
-----------------------------------------------------------------
-- Deleted Tables
-----------------------------------------------------------------
select into v_d_artifact count(*) from deleted_artifact where group_artifact_id=tracker_id;
select into v_d_a_history count(*) from deleted_artifact_history where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
select into v_d_a_file count(*) from deleted_artifact_file where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
select into v_d_a_extra_field_data count(*) from deleted_artifact_extra_field_data where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
select into v_d_a_group_list count(*) from deleted_artifact_group_list where group_artifact_id=tracker_id;
select into v_d_a_perm count(*) from deleted_artifact_perm where group_artifact_id=tracker_id;
select into v_d_a_type_monitor count(*) from deleted_artifact_type_monitor where group_artifact_id=tracker_id;
select into v_d_a_query count(*) from deleted_artifact_query where group_artifact_id=tracker_id;
select into v_d_a_query_fields count(*) from deleted_artifact_query_fields where artifact_query_id in (
select artifact_query_id from deleted_artifact_query where group_artifact_id=tracker_id
);
select into v_d_a_monitor count (*) from deleted_artifact_monitor;
select into v_d_a_message count (*) from deleted_artifact_message;
select into v_d_a_counts_agg count (*) from deleted_artifact_counts_agg;
-----------------------------------------------------------------
-- Populate Test Table with Counts
-----------------------------------------------------------------
INSERT INTO testRecordCount (
run_type, artifact, d_artifact, a_history, d_a_history,
a_file, d_a_file, a_extra_field_data, d_a_extra_field_data,
a_group_list, d_a_group_list, a_perm, d_a_perm,
a_type_monitor, d_a_type_monitor, a_query, d_a_query,
a_query_fields, d_a_query_fields, a_monitor, d_a_monitor,
a_message, d_a_message, a_counts_agg, d_a_counts_agg
) VALUES (
notes, v_artifact, v_d_artifact, v_a_history, v_d_a_history,
v_a_file, v_d_a_file, v_a_extra_field_data, v_d_a_extra_field_data,
v_a_group_list, v_d_a_group_list, v_a_perm, v_d_a_perm,
v_a_type_monitor, v_d_a_type_monitor, v_a_query, v_d_a_query,
v_a_query_fields, v_d_a_query_fields, v_a_monitor, v_d_a_monitor,
v_a_message, v_d_a_message, v_a_counts_agg, v_d_a_counts_agg
);
END;
$$ LANGUAGE 'plpgsql';

-----------------------------------------------------------------
-- Test Deleting Records
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION testRecordDelete (tracker_id int)
RETURNS VOID AS $$
BEGIN
-----------------------------------------------------------------
-- artifact_history, file, and extra_field_data have fk's to artifact
-----------------------------------------------------------------
delete from artifact_history where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_file where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_extra_field_data where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_monitor where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_message where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- group_list, perm, type_monitor, counts_agg are straight forward
-----------------------------------------------------------------
delete from artifact_perm where group_artifact_id=tracker_id;
delete from artifact_group_list where group_artifact_id=tracker_id;
delete from artifact_type_monitor where group_artifact_id=tracker_id;
delete from artifact_counts_agg where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- query_history needs query to turn hamburger back into cow
-----------------------------------------------------------------
delete from artifact_query_fields where artifact_query_id in (
select artifact_query_id from artifact_query where group_artifact_id=tracker_id
);
delete from artifact_query where group_artifact_id=tracker_id;
END;
$$ LANGUAGE 'plpgsql';

-----------------------------------------------------------------
-- Restore Records
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION RecordRestore (tracker_id int)
RETURNS VOID AS $$
BEGIN
-----------------------------------------------------------------
-- artifact_group_list (disable trigger before restore)
-----------------------------------------------------------------
alter table artifact_group_list disable trigger artifactgrouplist_insert_trig;
insert into artifact_group_list select * from deleted_artifact_group_list where group_artifact_id=tracker_id;
alter table artifact_group_list enable trigger artifactgrouplist_insert_trig;
delete from deleted_artifact_group_list where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- artifact_query & artifact_query_fields
-----------------------------------------------------------------
insert into artifact_query select * from deleted_artifact_query where group_artifact_id=tracker_id;
insert into artifact_query_fields select * from deleted_artifact_query_fields where artifact_query_id in (
select artifact_query_id from deleted_artifact_query where group_artifact_id=tracker_id
);
delete from deleted_artifact_query_fields where artifact_query_id in (
select artifact_query_id from deleted_artifact_query where group_artifact_id=tracker_id
);
delete from deleted_artifact_query where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- artifact_perm & artifact_type_monitor
-----------------------------------------------------------------
insert into artifact_counts_agg select * from deleted_artifact_counts_agg where group_artifact_id=tracker_id;
insert into artifact_perm select * from deleted_artifact_perm where group_artifact_id=tracker_id;
insert into artifact_type_monitor select * from deleted_artifact_type_monitor where group_artifact_id=tracker_id;
delete from deleted_artifact_counts_agg where group_artifact_id=tracker_id;
delete from deleted_artifact_perm where group_artifact_id=tracker_id;
delete from deleted_artifact_type_monitor where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- fish desired records out of deleted_artifact, restore, clean up
-----------------------------------------------------------------
insert into artifact select * from deleted_artifact where group_artifact_id=tracker_id;
insert into artifact_history select * from deleted_artifact_history where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_file select * from deleted_artifact_file where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_extra_field_data select * from deleted_artifact_extra_field_data where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_monitor select * from deleted_artifact_monitor where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_message select * from deleted_artifact_message where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_message where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_monitor where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_extra_field_data where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_file where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_history where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact where group_artifact_id=tracker_id;
END;
$$ LANGUAGE 'plpgsql';

iPhone App: Kitten Escape

It is a game that moves the block, and goes out of the kitten that has been confined. Kitten Escape is said "Daughter in the Box" of classical slide puzzle game.

Kitten reminds me of my old friend, CATS...

You have no chance to survive. Make your time. Haha!

CNN Now Projecting a Ratings Win for Themselves

Had you known before the polls closed, would you have voted for a Hologram instead of a human?



Fox News probably had to execute a terrorist live to get people to turn away from this marvel. It's like the advent of the talkies, only not-so-much.

Gizmodo has a write up if you're curious how it works.

Google's 'Relief' Gadget

Live here.



:p

'Democracy in Action' does not mean 'hack our listservs'

A succession of three emails from the Provost of George Mason. First:

Date: Tue, 04 Nov 2008 00:41:05 -0500
From: Office of the Provost
Subject: Additional Info on Election Day
To: ANNOUNCE04-L@mail04.gmu.edu

To the Mason Community:

I hear some troubling rumors, so here are a couple of facts: 1. The election is Nov. 4, for all political parties. The notion that one party votes Nov. 5 is UNTRUE. 2. It is also UNTRUE that any student jeopardizes financial aid by voting.

Peter N. Stearns
Provost

Then:

Date: Tue, 04 Nov 2008 01:16:42 -0500
From: Office of the Provost
Subject: Election Day Update
To: ANNOUNCE04-L@mail04.gmu.edu

To the Mason Community:

Please note that election day has been moved to November 5th. We apologize for any inconvenience this may cause you.

Peter N. Stearns
Provost

Finally:

Date: Tue, 04 Nov 2008 08:08:21 -0500
From: Office of the Provost
Subject: Urgent Voting Information
To: PROVOSTOFFICE-L@mail04.gmu.edu

Dear Colleagues,

It has come to my attention early this morning that a message was hacked into the system fraudulently stating that election day has been moved. I am sure everybody realizes this is a hoax, it is also a serious offense and we are looking into it. Please be reminded that election day is today, November 4th.

Peter N. Stearns
Provost

I spent exactly 30 seconds looking at the headers of the second message and found:

Received: from m154.prod.democracyinaction.org ([8.15.20.154])
by ironport2.gmu.edu with ESMTP; Tue, 04 Nov 2008 01:16:42 -0500
Received: from [10.15.20.114] ([10.15.20.114:39637] helo=web4.mcl.wiredforchange.com)

Apparently someone at Democracy In Action in DC wants to keep our browner students away from the polls today? Don't they know Mason is a commuter school?

Google News, Finance need to stop ignoring each other

I found yet another bug in Google News. To give some content, the stock market is down a shit-ton right now. I head over to GNews and see:



That the DJ closed UP 172 points? But it's the middle of the trading day, and doom is everywhere. Let's ask Google Finance:



Oh nevermind, it's DOWN 373 points!

Honest mistake for 3 year old betaware.