[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

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

Daniel Moore

4/14/2009 4:51:00 PM

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[:product_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.

On Fri, Apr 3, 2009 at 9:21 AM, Daniel Moore <yahivin@gmail.com> wrote:
> -=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.../sugge...
>
> 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 ;-)
> --
> -Daniel
> http://rubyquiz...
1 Answer

Martin DeMello

4/14/2009 6:43:00 PM

0

On Tue, Apr 14, 2009 at 10:21 PM, Daniel Moore <yahivin@gmail.com> wrote:
>
> 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.ruby...

martin