CODEBOXER.com

compiling mysql and getting the gem installed correctly

17 May 2010 | 3:45 pm by codeboxer

Download your preferred MYSQL version from the community download site. If you’re on Snow Leopard, you may want to install the 64-bit version.

Install mysql via dmg or compile it from source.

Type ‘which mysql_config’ on the terminal to locate said config. Take note of this location as we’ll use it on the next step.

Install the mysql gem with the command below. If the location for your mysql config is different with the one below, use that instead.

sudo env ARCHFLAGS="-Os -arch x86_64 -fno-common" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

worked for me!

thanks to http://www.sylv3rblade.com/2010/04/installing-the-mysql-gem-on-os-x-10-6...

dump in your stored proc via rake task

2 February 2009 | 3:35 pm by codeboxer

These days, a remote login to your prod database is a luxury, one not everyone has.

See below for how insert a stored proc (or 'stored routine', as MySQL likes to say) via rake task:

namespace :db do
task :load_sp1 do
sqlconn = ActiveRecord::Base.connection();

sql = %Q! CREATE PROCEDURE `sp_generate_forecast`(IN p_run_day INT(2), IN p_month INT(2), IN p_year INT(4), IN revplanid INT(10), IN month_days INT(2))
BEGIN
DECLARE r_units, r_day, r_partner int;
DECLARE done int DEFAULT 0;

-- fields - partial_total day_ave retail_chg margin_chg
-- retail_margin net_sales p_rev_share gross_margin
-- clear out any previous forecast
DELETE FROM revplan_infos
WHERE revplan_id = revplanid
AND (revplan_type = 'Forecast' OR revplan_type = 'Variance');

INSERT INTO revplan_infos (partner_id, amount, revplan_id, revplan_type, retail_margin, net_sales,
p_rev_share, gross_margin, retail_chg, margin_chg, created_at, updated_at, p_name)
SELECT t_plan.partner_id
,for_amount - plan_amount GROSS_RETAIL
,revplanid
,'Variance'
,(for_amount * p_infos.retail_margin/100) - (plan_amount * p_infos.retail_margin/100) RETAIL_MARGIN
,(for_amount - (for_amount * p_infos.retail_margin/100)) - (plan_amount - (plan_amount * p_infos.retail_margin/100)) NET_SALES
,((for_amount - (for_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) P_REV_SHARE
,((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) GROSS_MARGIN
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/plan_amount RETAIL_CHG
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/t_plan.gross_margin MARGIN_CHG
,SYSDATE()
,SYSDATE()
,p.name
FROM
(SELECT partner_id, amount plan_amount, gross_margin
FROM revplan_infos
WHERE revplan_type = 'Plan'
AND revplan_id = revplanid) t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast'
AND revplan_id = revplanid) t_for
ON t_for.partner_id = t_plan.partner_id
JOIN partner_infos p_infos
ON p_infos.partner_id = t_plan.partner_id
JOIN partners p
ON p.id = t_plan.partner_id;

END!

sqlconn.execute("DROP PROCEDURE IF EXISTS `sp_generate_forecast`")
sqlconn.execute sql
end
end

Original post blogged on codeboxer.com.

minor league pivot query

27 January 2009 | 10:33 pm by codeboxer

This would be a pivot query 101 kind of snippet. They teach this in SQL kindergarten - I only imagine, of course, because I never went.

The use case: it's a single table, with values grouped by partner_id and two enums for revplan_type. You need a variance report. What do you do?

SELECT t_plan.partner_id, plan_amount - for_amount variance
FROM
(SELECT partner_id, amount plan_amount
FROM revplan_infos
WHERE revplan_type = 'Plan') t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast') t_for
ON t_for.partner_id = t_plan.partner_id

Original post blogged on codeboxer.com.

MySQL 5.0 SP with Cursor Logic

23 January 2009 | 11:43 am by codeboxer

I have written a bunch of SPs over the years (stored procedure, as I learned it, but MySQL calls them Stored Routines - SRs?). I even have my best/largest/most complicated SP ever linked here on codeboxer.com at http://codeboxer.com/read.php?a=the-Monstertrak-migration.

That one was of course written in PL/SQL, and I have now officially moved out of the world of Oracle and into the world of MySQL 5.0 - which is sort of like moving from New York City to San Jose (weather notwithstanding).

Anyway, I found precious little RECENT coverage on how MySQL implemented it's 'stored routines', and I got to tell ya it took some digging - never mind trying to figure out how cursors work. MySQL, btw, has an ok documentation section but it is mediocre at best, somehow following in the fragmented example of the Rails community in what seems to be the curse of the open-source ADD.

So in the spirit of this site which has always been to keep pieces of code lying around that I may use later - here is my very first MySQL sp, which does nothing really useful except use a cursor to insert values into a table. Come back next week for the syntax needed to use parameters, lol.

Other things to notice - you have to declare a separate handler for pulling out of a cursor loop when you run out of records (does anyone else remember fondly how easy ADO recordsets were to use?) but other than that things look all right in MySQL land. Bye Oracle!

CREATE PROCEDURE testy ()
BEGIN
declare a, b int;

DECLARE done int DEFAULT 0;

declare masterCursor CURSOR FOR
SELECT SUM(sales.amount) amt, sales.item_month
FROM sales
WHERE RETAILER_ID = 336
GROUP BY sales.item_month;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN masterCursor;
REPEAT
FETCH masterCursor INTO a, b;
IF NOT done THEN
insert into revplan (amount, item_month)
values (a,b);
END IF;

UNTIL done END REPEAT;

CLOSE masterCursor;

END

ps, gotta give a shout out to http://www.razorsql.com/ which so far has been easy to use and has served as stand-in IDE for Toad, which is what I was used to.Original post blogged on codeboxer.com.

Perl linux and database backups

21 January 2009 | 4:52 pm by codeboxer

I got to tell ya, most of the greatest people don't get the recognition they deserve. Open Source is all about everyone putting in what they got. So mosey on over to http://worldcommunity.com/opensource/ and tell Peter Brown that his Perl-based mysql backup script works like a charm.

It is very easy to set up, so I won't go too far into it - just download and follow the instructions. I also opted in to for the ftp push, so after the script dumps out a mysql txt file for every table, it will tar it, zip it, and push it to the ftp server of your choice. Cool.

This is where it gets tricky - I pulled this little gem out of a comment on Peter Brown's site. This script leaves you with a directory of txt files - most of us are used to the single .sql file of the whole db, so getting at a full restore from a bunch of txt files does not sound fun - until linux jumps in for the rescue.

Get this, just untar it:

tar -xf bak.mysql.2009-01-20_14.55.30_.tar.gz

Move into that directory:

cd bak.mysql.2009-01-20_14.55.30

and hit it with the cat xargs combo, shooting it right into mysql:

find . -name "*.txt" -print | xargs -t --replace cat {} | mysql your_db_name -u your_db_user -p -h subdir.yoursite.com

and you're done! sweet, huh?

p.s. - the -h switch at the end (-h subdir.yoursite.com) is not always necessary, if it is localhost you can leave it out.Original post blogged on codeboxer.com.