Seperate botocs_hash column from matches table

[GoogleCodeExporter]

...

Original issue reported on code.google.com by Nimda...@gmail.com on 23 Sep 2009 at 11:39

[GoogleCodeExporter]

How specific do I have to be when filling in these tables?  Will this work?

    return array(
        'leegmgr_matches' => array(
            'mid' => 'MEDIUMINT',
            'mid' => 'VARCHAR(32)',
            'replay' => 'MEDIUMBLOB',
        ),
    );

Original comment by funnyfin...@hotmail.com on 23 Sep 2009 at 9:31

[GoogleCodeExporter]

Yeah, but you can't have two columns named 'mid'!
I assume you mean the return value of getModuleTables().

Original comment by Nimda...@gmail.com on 28 Sep 2009 at 9:46

[GoogleCodeExporter]

Sorry meant the second to be hash.

Ok I'll try to work on this.

Original comment by funnyfin...@hotmail.com on 29 Sep 2009 at 8:22

[GoogleCodeExporter]

Let me know once you know the (new) table name and (new?) field name of the
botocs_hash field, then I'll add the proper SQL upgrade statement in the 
INSTALL file.

Original comment by Nimda...@gmail.com on 3 Oct 2009 at 9:32

[GoogleCodeExporter]

No wait, there is no need for this, is there? I can just remove the field, you 
can
recalculate all the hashes anyway? This would really be the best solution, I can
already tell that things get ugly the other way.

Original comment by Nimda...@gmail.com on 3 Oct 2009 at 9:33

[GoogleCodeExporter]

No I will add it to the table creation routine as written above.  That is the 
whole
point of you writing the table routine, right?

Original comment by funnyfin...@hotmail.com on 3 Oct 2009 at 12:37

[GoogleCodeExporter]

Yes, that is fine - was what I meant, I think :-).

Original comment by Nimda...@gmail.com on 4 Oct 2009 at 1:41

[GoogleCodeExporter]

Actually I will need a statment in INSTALL to move all of the current entries 
from
matches to leegmgr_matches.

Original comment by funnyfin...@hotmail.com on 4 Oct 2009 at 1:44

[GoogleCodeExporter]

I've committed the changes for this issue.  Nicholas, would you create me an SQL
statement that will move all of the hashes from matches to leegmgr_matches?

table: matches
    column: match_id, hash_botocs

table: leegmgr_matches
    column: mid, hash

Once I have that statement I will use it and move the latest revision over into 
www.

Original comment by funnyfin...@hotmail.com on 5 Oct 2009 at 12:03

[GoogleCodeExporter]

Original comment by funnyfin...@hotmail.com on 5 Oct 2009 at 1:47

• Changed state: Started

[GoogleCodeExporter]

INSERT INTO leegmgr_matches (mid, hash) SELECT match_id, hash_botocs FROM 
matches;

Original comment by Nimda...@gmail.com on 5 Oct 2009 at 9:07

[GoogleCodeExporter]

I will do that tonight and then just delete the column myself.  Don't forget to 
remove the botocs_hash column as well.  I also think the reset match will need 
to be 
modified and maybe the delete match as well...

Original comment by funnyfin...@hotmail.com on 5 Oct 2009 at 5:50

[GoogleCodeExporter]

Phew I almost did this...

I think it could be disastrous.  I already have leegmgr_matches using mid and 
the
replay field.  If I run this, I think it may wipe out those rows.  Am I 
correct? 
Would I need an UPDATE for existing rows and an INSERT for new ones?

Original comment by funnyfin...@hotmail.com on 6 Oct 2009 at 1:06

[GoogleCodeExporter]

I need more info... all all matches from the matches table represented in
leegmgr_matches?

No, INSERTS inserts new rows. UPDATE overwrites.

Original comment by Nimda...@gmail.com on 6 Oct 2009 at 8:14

[GoogleCodeExporter]

leegmgr_matches had 2 columns: mid and replay.  The replay column contains the 
entire zip file that was uploaded.  Now I added a new column: hash.

So these are in use and cannot be lost.

So botocs_hash has to be moved over from matches to leegmgr_matches.  An INSERT 
won't take care of them all as leegmgr_matches is newer than matches and 
doesn't 
have an mid for every match played.  Update won't work alone for the same 
reason.

Original comment by funnyfin...@hotmail.com on 6 Oct 2009 at 4:35

[GoogleCodeExporter]

Will you be making any changes to the columns of leegmgr_matches ? Else I'll 
make
them permanent for 0.8 and add the appropriate code to INSTALL file.

I suggest creating a temporary table, something like (haven't tested the code, 
insert
correct column definitions too):

CREATE TABLE 'temp' (
mid     COL_DEF_HERE
replay  COL_DEF_HERE
hash    COL_DEF_HERE
);

INSERT INTO temp (mid, hash) SELECT match_id, hash_botocs FROM matches;

UPDATE temp, leegmgr_matches SET replay.temp = replay.leegmgr_matches WHERE 
mid.temp
= mid.leegmgr_matches;

... then drop your own leegmgr_matches and rename "temp" to leegmgr_matches...

Original comment by Nimda...@gmail.com on 9 Oct 2009 at 9:48

[GoogleCodeExporter]

Ok I am trying this now.

Original comment by funnyfin...@hotmail.com on 9 Oct 2009 at 10:51

[GoogleCodeExporter]

CREATE TABLE temp (
These first two worked:
mid     MEDIUMINT,
replay  MEDIUMBLOB,
hash   VARCHAR(32)
);
INSERT INTO temp (mid, hash) SELECT match_id, hash_botocs FROM matches;

This does not work:
UPDATE temp, leegmgr_matches SET replay.temp = replay.leegmgr_matches WHERE 
mid.temp
= mid.leegmgr_matches;

#1054 - Unknown column 'replay.temp' in 'field list' 

Original comment by funnyfin...@hotmail.com on 9 Oct 2009 at 11:01

[GoogleCodeExporter]

I think the error is pretty explicit? Does the column exist?

Original comment by Nimda...@gmail.com on 10 Oct 2009 at 7:10

[GoogleCodeExporter]

Try:

UPDATE temp, leegmgr_matches SET temp.replay = leegmgr_matches.replay WHERE 
temp.mid
= leegmgr_matches.mid;

Original comment by Nimda...@gmail.com on 10 Oct 2009 at 7:35

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

CREATE TABLE temp (
mid     MEDIUMINT,
replay  MEDIUMBLOB,
hash   VARCHAR(32)
);
INSERT INTO temp (mid, hash) SELECT match_id, hash_botocs FROM matches;
UPDATE temp, leegmgr_matches SET temp.replay = leegmgr_matches.replay WHERE 
temp.mid
= leegmgr_matches.mid;
ALTER TABLE leegmgr_matches RENAME TO leegmgr_matches1;
ALTER TABLE temp RENAME TO leegmgr_matches;
#AFTER TESTING
DROP TABLE leegmgr_matches1;
alter table matches drop column hash_botocs;

Now you should be adding all of this to the upgrade or install as some sites 
I've
seen have a .8 beta version running.

Also when deleting or resetting a match, I need that mid row deleted from
leegmgr_matches.  How can this be handled?

*editted this comment

Original comment by funnyfin...@hotmail.com on 10 Oct 2009 at 3:10

[GoogleCodeExporter]

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Original comment by Nimda...@gmail.com on 10 Oct 2009 at 3:18

[GoogleCodeExporter]

What specifically are you saying?

Original comment by funnyfin...@hotmail.com on 10 Oct 2009 at 11:59

[GoogleCodeExporter]

So besides the deleting or resetting needing to delete the appropriate row in 
leegmgr_matches, this is done.

Original comment by funnyfin...@hotmail.com on 11 Oct 2009 at 2:41

[GoogleCodeExporter]

Sorry, made no sense.

You are looking for:
    DELETE FROM leegmgr_matches WHERE mid = XXX
..where XXX is the match ID.

Original comment by Nimda...@gmail.com on 11 Oct 2009 at 10:32

[GoogleCodeExporter]

Syntax isn't the problem.  Did you want ME to add this line to the delete and 
reset
routines?

Original comment by funnyfin...@hotmail.com on 11 Oct 2009 at 1:07

[GoogleCodeExporter]

Sorry, I've had a couple of days with much to little sleep. I keep misreading 
stuff. 
Anyway, I'll do it. I'm writing your SQL upgrade code too.

Original comment by Nimda...@gmail.com on 11 Oct 2009 at 6:27

[GoogleCodeExporter]

Added the SQL code and implemented the triggers:
r335 & r336

Original comment by Nimda...@gmail.com on 11 Oct 2009 at 8:20

• Changed state: Fixed