Twatter

@twitter =p

I Made the Amazon EC2 Cloud Rain

Sometimes, elastic ain't so elastic.

I got (2) $50 AMEX Gift Cards for my Birthday, which are like feces that can only be exchanged for exactly ${FECES} worth of merchandise (not one dollar more!).

Sure enough, I decide to buy a couple games for my PS3, because it's lonely and nothing I want costs ~$50.00.

I have a credit on my Amazon account for $119.45 (two Christmas returns and three gift certs). After carefully reading Amazon's allowable methods of payment:

Which cards can you use on Amazon.com? We accept the following cards as payment for your orders:

* Visa (including the Amazon.com Visa Card)
* MasterCard/EuroCard
* Discover Network
* American Express
* Diner's Club (U.S. billing addresses only)
* JCB
* Visa or MasterCard check cards or ATM cards
* Visa, MasterCard, or American Express gift cards
* Amazon Credit Account

You can use debit, check, and gift cards issued by the companies above in addition to their standard credit cards. For example, if you're paying with a Visa Check Card please select Visa from the drop-down menu on the order form when indicating the card type. Similarly, if you're paying with a EuroCard or MasterMoney Card, select MasterCard.

If you are using a Visa, MasterCard, or American Express gift card, please note the following restrictions:

* Pre-paid gift cards can't be combined with credit cards on a single order.
* Pre-paid gift cards can't be used to sign up for Amazon Prime membership.
* Amex Gift Cards can't be used to purchase Amazon.com gift cards.

It says I can use an AMEX Gift Card (PayPal says no, but they lie), it says I cannot tender a second credit card with a pre-paid gift card, and I cannot use an AMEX Gift card to buy an Amazon.com gift card.

Knowing the rules, I try to buy a $59.99 game with a $50.00 gift card, assuming the $9.99 can come off the $119.45 Amazon owes me (!). Sure enough, it tries to put the whole amount on the card and closes out the transaction. Five minutes later, I get the "Your Order Has Blown Up" email. I try to explain to Customer Service three times by email (with my good friends Pranvi, Rhett, and Ooshmani) that I read and followed the rules and the payment system exploded. Frustrated, I used the "Call Me now" option, and Caucasian Brian from Seattle called me back.

Over the next 28 minutes, I discovered:

• There is a bug in the system that Amazon's Customer Service Folk used, that does not show them the proper gift card balance (my $119.45 showed as $0)


• My CS rep was very knowledgeable, but I taught him that you can't use Amex GCs to buy Amazon.com GCs (when he recommended that could fix this situation


• Amazon employees cannot, in fact, fix this on their side. They have an extra option on their screen (gift cards), but that doesn't work either. I know this b/c I bought a $15.00 gc while we were on the phone, he tried to apply it, and it wouldn't work. I was able to add it to my account, bringing me up to $134.45


• Though the pimply douche who makes $7.00/hr at Best Buy can tender two credit cards, the Internet(s) have no such capability. As I told Caucasian Brian: I've only been an Amazon customer since 1999, why should they enhance their checkout process? It's only been 10 years -- they'll catch up to cash registers eventually.


• In the end, the only way to fix this was to reduce the price until it went through.

I think bug-finding and brow-beating a human-Amazon-employee were far more rewarding that the $10 off my video game. I did fill out a survey and gave him excellent marks, but only because he laughed at my jokes.

Old

thirty candles.

Trannies Done-Throwed Down

Mmmm hrmm...and how.

Now that gas is cheap (again), we wish it was even cheaper

Commentary: Americans sure do love them a discount (and how).



I was having my car inspected at a Shell station on Saturday, and I noticed the Shell Saver Card. It links your checking account to a card (more like a gift card than a credit card) to allow direct debit at the pump. If you sign up now, you save $0.05/gallon until June 1st, and $0.02/gallon thereafter.

I was reminded of this again today when I saw a story on Consumerist about something similar in the lone star state. I read the story, dismissed it, and returned to work for about 5 minutes before I lost control of the voices. They said:

Right now, you use your American Express Blue Cash to get 1% back on gas (and groceries) for the first $6500/year, and 5% back for the rest of the year after you cross $6500. Is this a better deal?!?! Are you leaving precious pennies on the table?

I mean, fuck, how do I get any work done? Even my god-damned voices have OCD.

Thankfully, I kept the spreadsheet I made last month to calculate my energy expenditures so I could hedge my estimated energy costs for 2009. For the closest comparison, I found where I hit $6500 on my AMEX (sometime in March), so I figured out 1.0% rewards on Dec-Mar, then 5% back on Apr-Nov. Then I divided the US average against what I spent to estimate gallons of gas bought, and multiplied it by (average price minus $0.05 for Dec-Jun and $0.02 for Jun-Dec). My math is a little b0rked, but here's what I got:

AMEX 1% - $4.78
AMEX 5% - $118.90
Total - $123.68

Shell $0.05 - $19.77
Shell $0.02 - $9.13
Total - $28.90

Caveats: 1) per-gallon discount is immediate, AMEX rewards is annual. 2) Rewards is likely overstated; as gas was expensive, my rewards were equally inflated 3) I would have to always go to a Shell to get these rewards, with my AMEX I can butter any gas station's bread as I see fit 4) Since I backed into the consumption numbers using the national average for that week, who the hell knows.

That's right, I did the math so you don't have to.

Had this program existed last year (when gas was hellua expensive), and I chose the Shell foolishness instead of using my AMEX Blue Cash, I would have lost out on almost $100 in rewards. I'm going to stick to my Blue Cash.

Voices satisfied, back to work.

Anderson Cooper hosts CNN's "Puppy Debate"

Aww...I want an Anderson Cooper...

New Office

I've had an office since 2000, and I haven't shared an office since 2001. I thought it would be an adjustment, but it wasn't at all. As it turns out, what's on my desk mattered more to me that where I sit. Since I'm a contractor here, I was free to buy my own keyboard, monitor, and bring my laptop to work (for the record: latop to work +5, child to work -5).

I've been in a cube since I started working here last November. At that time, all the feds all got offices and the contractors got cubes. Six months later, half the offices were empty and all the cubes were full, so they decided contractors can share an office.

Someone who worked in the office will be remote for at least the next six months, so his office opened up. My boss suggested I should move in, so I did.



I had more room to spread out in my cube, but here I can pretend I moved from suburbia to a high-rise Tokyo. My two external hard drives (USB 2.0 and Firewire 400), laptop and speakers, french press and electric tea kettle, and panda-slave are tucked in the cabinet over the desk. I've run the cable for the monitor up, and the USB connection for the wireless keyboard down through the conduit in the corner (gray triangle).

Fine use of space if you ask me.

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.

You piss me off real good! I no talk to you.

Kristin: um, again, chick question... but are you mad at me about something? Or is the shortness cause you're wicked busy and I keep interrupting
theAlphaJohn: haha
theAlphaJohn: im on a conference call
theAlphaJohn: just working on stuff in the background
theAlphaJohn: not mad at you at all
Kristin: ok... whew
Kristin: well... I hope you come out tomorrow night
Kristin: haven't seen you in 100 years
Kristin: would be nice to shake it on the dancefloor with you
theAlphaJohn: yeah it's been a while
theAlphaJohn: are you still white?
Kristin: lmao
Kristin: as far as I know
Kristin: I'm still a chick as you can tell
theAlphaJohn: im all asian now
Kristin: really...
theAlphaJohn: i hear yellow fever is the new black
Kristin: LMAO
Kristin: alrighty!
Kristin: does that require surgery?
theAlphaJohn: not much
theAlphaJohn: it's all outpatient
Kristin: oh good...
Kristin: as long as they don't have to remove you nipples and reattach them
theAlphaJohn: then hair dye, spray on tan, and lots of anime / graphic novels (read: comic books for people with masters degrees)
Kristin: omg
theAlphaJohn: would you like me to go back to being terse?
Kristin: NO!
Kristin: that was not a complaint

McCain Hilarious at Al Smith 2008 Dinner

Seriously. I was laughing my ass off in the car last night. C-SPAN has the entire dinner, McCain's speech starts at 7:10 ::

There are signs of hope, even in this room full of proud, Manhattan Democrats. I can't shake that feeling that some people in here are pulling for me. I'm delighted to see you here tonight, Hillary. Where's Bill, by the way? Can't he take one night off from his tireless quest to make the man who defeated his wife the next President? The man is a relentless advocate for the Obama campaign, and he has a subtle approach to making the case. When a reporter asked him if Senator Obama was qualified to be President, Bill Clinton pointed out, "Sure. He's over 35 years of age and a US citizen". He was pandering to the strict constructionist crowd!

The entire transcript is here.

For serious, where has this speech writer been hiding?

The Real VP Debate

From Jared:



In Kung Fu Election, Michelle can battle Cindy.