[QUIZ][SUMMARY] Restoring Data From SQL (#199)

[Daniel Moore]

No submissions this week (there may have been a glitch causing this
not to have even been posted to the mailing list, so that might
explain it...)

Here's the solution that I ended up using:

file_names =3D Dir['PROD_*']

compositions_found =3D false

compositions =3D Hash.new {{}}

file_names.each do |file_name|
File.read(file_name).each_line do |line|
compositions_found =3D false if line =3D~ /\/\*/
if compositions_found
line =3D~
/\(([^,]+),([^,]+),([^,]+),([^,]+),([^,]+),([^,]+),([^,]+),([^,]+)/

if $2 && $2 !=3D 'NULL'
composition =3D {:id =3D> $1.to_i}
composition[roduct_id] =3D $2.to_i if $2 && $2 !=3D 'NULL'
composition[:component_id] =3D $3.to_i if $3 && $3 !=3D 'NULL'
composition[:quantity] =3D $6[1...-1].to_f if $6 && $6 !=3D 'NU=
LL'
composition[:line_num] =3D $7.to_i if $7 && $7 !=3D 'NULL'
composition[:fixed] =3D $8.to_i if $8 && $8 !=3D 'NULL'
#puts composition.inspect
compositions[$1] =3D compositions[$1].merge(composition)
end
#puts line
else
compositions_found =3D true if line =3D~ /INSERT INTO `compositio=
ns`/
end

next unless compositions_found
end
end

compositions.sort.each do |id, composition|
puts composition.inspect
end

It reads the comma separated fields using a wonky regex (please let me
know of a better way!). The output is inspected hashes that I eval in
another program to load the data back into the DB. It worked
surprisingly well.

I apologize for the late summary and any mishaps on the mailing of this qui=
z.

-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-= =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-

The three rules of Ruby Quiz:

1. =A0Please do not post any solutions or spoiler discussion for this
quiz until 48 hours have elapsed from the time this message was
sent.

2. =A0Support Ruby Quiz by submitting ideas and responses
as often as you can!
Visit: <http://rubyquiz.strd6.com/suggestions>

3. =A0Enjoy!

Suggestion: =A0A [QUIZ] in the subject of emails about the problem
helps everyone on Ruby Talk follow the discussion. =A0Please reply to
the original quiz message, if you can.

-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-= =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-

## Restoring Data From SQL (#199)

Ahoy Rubyists,

This weeks quiz comes from a real world situation. While our team was
working on one of our internal applications a bug was introduced. This
bug causes certain `Composition` database entries to be deleted when
updating an associated `Product`. It only happened with one way of
updating the products and went undetected for while. Now we notice
that we are missing lots of `Composition` data and we need it back
fast!

Fixing the bug was trivial once it was discovered, but restoring the
data? That may be much harder... Fortunately we have lots of backup
SQL files laying around so we can piece together the missing data.
This week's quiz is to parse a sequence of SQL files and output a list
of all records that ever existed in them.

Each file looks like this:

--
-- Definition of table `compositions`
--

DROP TABLE IF EXISTS `compositions`;
CREATE TABLE `compositions` (
=A0`id` int(11) NOT NULL auto_increment,
=A0`product_id` int(11) default NULL,
=A0`component_id` int(11) default NULL,
=A0`created_at` datetime default NULL,
=A0`updated_at` datetime default NULL,
=A0`quantity` decimal(15,3) default '1.000',
=A0`line_num` int(11) default NULL,
=A0`fixed` tinyint(1) default NULL,
=A0PRIMARY KEY =A0(`id`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1;

--
-- Dumping data for table `compositions`
--

/*!40000 ALTER TABLE `compositions` DISABLE KEYS */;
INSERT INTO `compositions`
(`id`,`product_id`,`component_id`,`created_at`,`updated_at`,`quantity`,`l= ine_num`,`fixed`)
VALUES
=A0(1,1,2,NULL,NULL,'1.000',NULL,NULL),
=A0(2,1,3,NULL,NULL,'1.000',NULL,NULL),
=A0(3,NULL,190,'2008-07-24 10:27:34','2008-07-24 10:27:34','5.000',NULL,N= ULL),
=A0/* ... more ... */
(135,259,358,'2008-11-19 16:50:36','2008-11-19 16:52:20','1.000',80,1);

Non-null column values in more recent files should take precedence
over those same value is previous files. So for example if row 135 had
a quantity of 2.000 in the second to last file and a quantity of 1.000
in the last file then the final output should have a quantity of 1.000
for row 135, overwriting the previous 2.000 value.

See attached for sql input files.

Have Fun... and save our business!*

[*]: 48-hour no-spoiler period still applies ;-)

 

[Martin DeMello]

It reads the comma separated fields using a wonky regex (please let me
know of a better way!). The output is inspected hashes that I eval in

There's a nice, friendly CSV library! http://fastercsv.rubyforge.org/

martin