[mythtv-users] Possibly OT: Merging Tables, NO duplicates. [SOLVED?]
R. G. Newbury
newbury at mandamus.org
Tue Nov 11 23:15:30 UTC 2008
Shanon Swafford wrote:
> Maybe write a little PHP/Perl script that pulls from SQLlite and inserts
> into mysql with the keys updated. Or export to a file and read/insert with
> PHP/Perl. Run script, change connector on machine to start updating mysql.
> Move to next machine.
The whole idea here is to NOT mess around with translations.
Sqlite3 is limited and does not actually work as it says it does, but it
has the necessary.
> It is probably a sacrilege, but if it where me, I would suck those tables
> from all 3 machines into Excel
BLASPHEMER! UNCLEAN! LEPER!......Do you have a bell you can wear, so we
can hear you coming?
Joke. Joke.
> using it's ODBC import function (or export
> and paste into Excel if necessary), then massage the keys and use Excel's
> "concatenate" function to spit out SQL insert statements. If you have
> multiple tables and keys between them, the "vlookup" function is a great one
> too. Not sure if OpenOffice.org has that functionality but I am setting up
> a PC in my spare time to try and replace all I do in Excel so I'll find out
> someday. Right now I couldn't make a living without Excel.
This is overkill. And you just may be addicted to Excel...how about
starting by changing to OO Calc, and wean yourself off the bad stuff?
But seriously, sqlite (or sqlite3) has all that is needed, and it can
even be scripted.
sqlite has a .dump command which is the equivalent of mysqldump.
The killer is finding the correct command structure to do this *easily
and simply*.
> I also might think about adding a new columns for the machine name and
> existing index/key. Like "machinea,1", "machinea,2", "machineb,1",
> "machineb,2", .... if you care to keep that history.
Actually, I already have that built in.
However, interestingly, neither INSERT *OR* REPLACE worked as expected.
I tried 'INSERT OR IGNORE INTO tableA select * from tableB;' and it
created duplicates.
I tried with a 'select distinct' clause, but same result.
I tried 'REPLACE INTO tablsA select * from tableB;' and it created
duplicates, as did 'select distinct ...'.
Neither of these results was what I expected from reading 'Mysql in a
Nutshell' (where admittedly, MySql might work differently).
End result: I need to use a temporary table after all.
I will use:
.dump on each machine to create the dumpfiles
rsync the files to the 'A' machine
.read the dumpfiles to create tableB and tableC
'CREATE tableD .....(same structure as tableA, tableB, tableC)'
'INSERT INTO tableA select * from tableB;'
'INSERT INTO tableA select * from tableC;'
# this will create a large file with duplicates, then,
'INSERT OR IGNORE INTO tableD select distinct * from tableA;
This will create a single table containing all unique entries.
I will dump this file for backup, and rsync those to the other machines.
I haven't yet had a chance to try the same steps in MySql.
Geoff
--
Please let me know if anything I say offends you.
I may wish to offend you again in the future.
Tux says: "Be regular. Eat cron flakes."
More information about the mythtv-users
mailing list