How to compare databases
#1
Hi there,
Let's say there are two people, A and B, who have xbmc eden running and have centralised databases with a large collection of media. Let's go on to say this media is in the MyVideos60.movies table.

Now let's say one person is about to visit the other and they want to:
1. Bring with them media which is in their collection but missing from the other's collection
2. Ensure the drive that they bring with them has sufficient capacity to hold any media which they may desire i.e work out what media they are likely to want to take home after the meeting.

To constrain the problem we'll put both people on silly network configurations so that when A and B are together they can only access the db at that location i.e. only one of the two dbs.

The above is our problem...

Our solution: Two python scripts:
1. dumpmovienameDB.py: writes a list of all the movies in the target db to sdout. This can then be written to a file called movieList with $./dumpmovienameDB > movieList
2. movieMatcher.py: compares movieList to the target db and sends the movies present in the file but not the db and present in the db but not the file to sdout.

to run these scripts you'll need to install python and the python-mysqldb module



dumpmovienameDB.py

replace con statement e.g. mysqldbHOST needs to be changed to your db ip address etc

Code:
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('mysqldbHOST', 'xbmc', 'xbmc', 'MyVideos60');


with con:

    cur = con.cursor()
    cur.execute("SELECT c00 FROM movie")

    numrowsDB = int(cur.rowcount)

    for i in range(numrowsDB):
        dbMovie = cur.fetchone()
        movienameDB = dbMovie[0].rstrip()
#               print "#" + movienameDB + "#"
        print movienameDB

movieMatcher.py

again you need to change the con statement to fit your set up

Code:
#!/usr/bin/python
# -*- coding: utf-8 -*-
# Make a file called movieList using $./dumpmovieDBnames.py > movieList
# Put movieList and this file in the same dir and execute this to see the differences
# between the db and the movieList. Remember to update db connection info in both
# this and the dumpmovieDBnames.py


import MySQLdb as mdb
import sys


con = mdb.connect('mysqldbHOST', 'xbmc', 'xbmc', 'MyVideos60');
movienameDBlist = []
movienameFilelist = []

with con:
    cur = con.cursor()
    cur.execute("SELECT c00 FROM movie")
    
    numrowsDB = int(cur.rowcount)
  
    for i in range(numrowsDB):
        dbMovie = cur.fetchone()
        movienameDB = dbMovie[0].rstrip()
        movienameDBlist.append(movienameDB)

with open('movieList') as f:
        content = f.readlines()

numrowsFile = len(content)

for j in range(numrowsFile):
        movienameFile = content[j].rstrip()
        movienameFilelist.append(movienameFile)

onlyDB = set(movienameDBlist) - set(movienameFilelist)
onlyFile = set(movienameFilelist) - set(movienameDBlist)

print "#################################"
print "### Missing from the database ###"
print "#################################"
print onlyFile
print '\n'
print "#############################"
print "### Missing from the file ###"
print "#############################"
print onlyDB

So both A and B run dumpmovieDBNames.py and then swap the resulting movieList files in advance of the meeting. The visiting party ensures that they bring with them copies of what ever files are missing from the counterpart db and that their drive is large enough for whatever they know they will need to bring back.

Another example of this would be to run the movieMatcher.py against the imdb top 250 by making a movieList file containing the list shown below. This will tell you what movies you need to get if you want to have everything in the top250 (nb issue with Wall.E)
Code:
The Shawshank Redemption
The Godfather
The Godfather: Part II
Pulp Fiction
The Good, the Bad and the Ugly
12 Angry Men
Schindler's List
The Dark Knight
The Lord of the Rings: The Return of the King
One Flew Over the Cuckoo's Nest
Star Wars: Episode V - The Empire Strikes Back
Fight Club
Seven Samurai
Inception
The Lord of the Rings: The Fellowship of the Ring
Goodfellas
Star Wars: Episode IV - A New Hope
City of God
Casablanca
The Matrix
Once Upon a Time in the West
Rear Window
Raiders of the Lost Ark
The Silence of the Lambs
The Usual Suspects
The Lord of the Rings: The Two Towers
Se7en
Forrest Gump
Psycho
It's a Wonderful Life
Avengers Assemble
Leon
Sunset Boulevard
Memento
Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb
Apocalypse Now
American History X
North by Northwest
Terminator 2: Judgment Day
Citizen Kane
Saving Private Ryan
Alien
City Lights
American Beauty
Spirited Away
Toy Story 3
Taxi Driver
The Shining
Vertigo
M
Paths of Glory
The Pianist
Modern Times
The Departed
Amelie
Double Indemnity
WALL·E
The Lives of Others
Aliens
A Clockwork Orange
Life Is Beautiful
Lawrence of Arabia
To Kill a Mockingbird
Back to the Future
Das Boot
Requiem for a Dream
Eternal Sunshine of the Spotless Mind
Reservoir Dogs
The Third Man
A Separation
The Prestige
The Green Mile
Cinema Paradiso
Chinatown
The Treasure of the Sierra Madre
L.A. Confidential
The Great Dictator
Gladiator
Once Upon a Time in America
Rashomon
Monty Python and the Holy Grail
Full Metal Jacket
Bicycle Thieves
Singin' in the Rain
Amadeus
Some Like It Hot
All About Eve
Raging Bull
Metropolis
Braveheart
Oldboy
2001: A Space Odyssey
The Bridge on the River Kwai
The Apartment
Star Wars: Episode VI - Return of the Jedi
Pan's Labyrinth
Unforgiven
Princess Mononoke
The Sting
Downfall
Indiana Jones and the Last Crusade
The Lion King
The Intouchables
Inglourious Basterds
Mr. Smith Goes to Washington
Die Hard
Grave of the Fireflies
The Seventh Seal
On the Waterfront
Up
The Elephant Man
The Great Escape
The Maltese Falcon
Yojimbo
Gran Torino
Rebecca
Batman Begins
Witness for the Prosecution
For a Few Dollars More
Snatch.
The General
Heat
Blade Runner
Fargo
Wild Strawberries
Ran
Sin City
Toy Story
Touch of Evil
The Big Lebowski
Jaws
The Deer Hunter
Hotel Rwanda
No Country for Old Men
Ikiru
Cool Hand Luke
The Artist
It Happened One Night
The Wizard of Oz
Scarface
The King's Speech
The Sixth Sense
Black Swan
Strangers on a Train
The Kid
The Wages of Fear
Kill Bill: Vol. 1
Annie Hall
The Gold Rush
High Noon
Platoon
Warrior
Trainspotting
Butch Cassidy and the Sundance Kid
Lock, Stock and Two Smoking Barrels
Into the Wild
Sunrise
The Grapes of Wrath
The Secret in Their Eyes
Donnie Darko
Notorious
The Thing
Gone with the Wind
Casino
Million Dollar Baby
There Will Be Blood
Life of Brian
Les Diaboliques
My Neighbour Totoro
Amores Perros
Finding Nemo
Slumdog Millionaire
Ben-Hur
How to Train Your Dragon
Groundhog Day
The Terminator
The Big Sleep
V for Vendetta
Good Will Hunting
The Best Years of Our Lives
Stand by Me
The Graduate
Dog Day Afternoon
Judgement at Nuremberg
Twelve Monkeys
Network
The Bourne Ultimatum
The Manchurian Candidate
The 400 Blows
The Night of the Hunter
Mary and Max
Gandhi
Harakiri
District 9
Persona
Dial M for Murder
The Battle of Algiers
The Princess Bride
The Killing

La strada
The Hustler
Who's Afraid of Virginia Woolf?
Sherlock Jr.
The Passion of Joan of Arc
Ratatouille
Howl's Moving Castle
The Wrestler
Fanny and Alexander
The Exorcist
The Wild Bunch
The Diving Bell and the Butterfly
Harry Potter and the Deathly Hallows: Part 2
Kind Hearts and Coronets
Stalag 17
Rocky
Barry Lyndon
A Streetcar Named Desire
Nights of Cabiria
Star Trek
All Quiet on the Western Front
A Beautiful Mind
The Truman Show
Ip Man
Infernal Affairs
Rope
Roman Holiday
Tokyo Story
The Man Who Shot Liberty Valance
Rosemary's Baby
High and Low
Festen
Stalker
Mystic River
Come and See
Let the Right One In
Pirates of the Caribbean: The Curse of the Black Pearl
Nausicaä of the Valley of the Wind
Beauty and the Beast
Monsters, Inc.
Manhattan
Nosferatu
Anatomy of a Murder
Magnolia
Big Fish
Throne of Blood
Le Samouraï
La Grande Illusion
3 Idiots
Shutter Island

I hope this helps someone...
Reply

Logout Mark Read Team Forum Stats Members Help
How to compare databases0