Another Library Question... But a different Type of Library ;)

[BillytheImpaler]

My DVD collection is getting a bit out of hand. I have commercial DVDs, mini DVDs from my awful Sony DCR-DVD203, software DVDs and CDs, along with ages worth of backups of music, video, and general data. I'd like to implement a system of organization within the 800-or-so disks so that I can easily find the disk that contains the file I'm looking for.

Ideally it'd be a GUI app, perhaps linked to a MySQL or SQLite database for fast searches. I'd like it to make the data entry portion painless; stick the disk in, let it check the contents, write the proper metadata, and eject it so I can insert the next.

It's possible that something as simple as
find /cdrom/* >> big_nasty_list.txt could do most of the work. It'd be nice if it grabbed and recorded the disk's title as well. I could knock up a simple script that does that, but I imagine that with thousands or tens of thousands of lines in such a file grepping it would become a chore, hence the database. (I don't know squat about interfacing with databases in my own amateurish scripts).

Is there anything like this available? I'd prefer if it ran on Linux, but if you know of something excellent that runs on Windows, Mac, or even some other *NIX I'm all ears.

TIA!

 

[BillytheImpaler]

Anybody?

I do suppose I'm barking up a lonely tree. It might be time to dust off my weak scripting skills.

 

[Una]

So basically you would want disk title, files on that disk and some way to search through that. I assume your labeling all your DVD's with the disk title so you could locate it physically if you know the title?

 

[BillytheImpaler]

Yeah, I'll have to generate a semi-arbitrary catalog system to locate the disks; essentially just assigning all the current disks a number based on current shelf position, then as new disks are added assigning each a new number in sequence. If anybody has any better ideas for this I'm all ears.

 

[Una]

Are your filenames on the disks unique? Could make the search superfast if they are.

 

[BillytheImpaler]

The are for the most part. Some of the archive/backup disks will contain different versions or iterations of the same file (i.e. xorg.conf will probably be so named on all backups). These aren't a huge issue since most backups are tarballs named for the date of the backup and the machine name of the source of the files. Additionally it's much less likely that I'd be looking for a file like that by name rather than the date.

EDIT: Please pardon me if I'm not making any sense. I haven't had a wink of sleep in 45 hours.

EDIT2: If the metadata, such as disk title, is insufficient for making a good catalog entry that really is my problem. It's nothing to be too concerned about. For instance if I pop in disk 3 of Lost's second season the title might be useless as will be the names of the files found on the disk. It wouldn't be a problem for me to just manually enter the data for that disk if nothing useful could be gleaned.

 

[Una]

Nah its cool.. teaching myself a new language at the moment. Could throw something together pretty quick I guess. Database structure is actually the hardest part to get right

Something like this would be possible I guess. Then you just query the disk_files table with the filename your looking for, thus retreiving the disk_id and therefore the title_name.

Disk_Titles table:
disk_id | title_name
1 | title1
2 | title2

Disk_Files table:
id | filename | disk_id
1 | host1-date1.tgz | #1
2 | host1-date2.tgz | #1

 

[BillytheImpaler]

I'm going to have to finish out my day, nap, and come back to this. My feeble mind is overwhelmed by anything more complex than coffee right now.

Thanks for consideration!

 

[Una]

Needs ruby/sqlite3

Typical usage:

alex::nop { ~/Code/Ruby/DVDtool }-> ruby dvd.rb 
Command usage:
usage - view usage
insert_disk - add currrently inserted disk to database
locate <filename> - locate disk by filename specified
eject - eject currently inserted disk
quit - exit the program
:>insert_disk
Added disk AUG_26_2007 to database
Added House_MD_2x04_TB_or_Not_TB.avi to 1
Added House_MD_2x03_Humpty_Dumpty.avi to 1

:>locate House_MD_2x03_Humpty_Dumpty.avi
Located on disk AUG_26_2007

# Really crappy implementation of a dvd database prototype by Una
# needs ruby obviously :-) 
# Version 0.01 2008

# Change this for a different database name
DBNAME = "dvds.db"

# Change this to the location of the mounted dvd drive
DEVICE_PATH = "/media/cdrom0"


require 'sqlite3'
require 'find'

class DVDtool

    def initialize dbname
        @db = SQLite3::Database.new dbname
        create_tables
    end

    def create_tables
          # Two tables, disk_titles, disk_files
          sql = <<SQL

          create table if not exists disk_titles (
          disk_id INTEGER PRIMARY KEY,
          disk_title varchar(40)
          ); 

          create table if not exists disk_files (
          id INTEGER PRIMARY KEY,
          disk_filename varchar(50), 
          disk_id int
          ); 
SQL

          @db.execute_batch(sql)
    end

    def insert_disk_metadata volume_name
        @db.execute("insert into disk_titles values (NULL,?)",volume_name)
        puts "Added disk #{volume_name} to database"
    end

    def get_diskid
        @db.execute("select max(disk_id) from disk_titles")
    end

    def insert_disk_filestructure filelist
        # First we need to locate the correct disk id.
        disk_id = get_diskid
        filelist.each do |file|
            # Insert each file one by one into the database
            @db.execute("insert into disk_files values (NULL,?,?)",file,disk_id)
            puts "Added #{file} to #{disk_id}"
        end
    end

    def dump_db
        rows = @db.execute("select * from disk_titles")
        puts rows
    end


    # Locate dvd by filename, returns disk_name
    def locate filename
        result = @db.execute("select disk_title from disk_titles, disk_files where disk_filename=? 
                    and disk_titles.disk_id == disk_files.disk_id",filename)
        if result != []  
            puts "Located on disk #{result}"
        else
            puts "#{filename} not found!"
        end
    end

    def disk_exists_already? volume_name
       result = @db.execute("select disk_title from disk_titles where disk_title=?",volume_name)
       if result == []
           return false
       else
           puts "Err: Disk already exists in database!"
           return true
       end
    end
   
    ############################################### Disk Insertion Routines ############################################### 

    def insert_disk *args 
        # First read the metadata from the disk.
        volume_name = `volname`.strip!
        # Add that to the disk_title section.
        if disk_exists_already? volume_name
            return false
        end
        insert_disk_metadata volume_name 
        # Now read the file structure of that and add to db.
        insert_disk_filestructure(file_list(DEVICE_PATH))   
        false
    end

    def file_list(filename)
        files = []
        Find.find(filename) do |path|
            files << File.basename(path) unless FileTest.directory? path
        end
        files
    end

    def eject *args
        system("eject")
        false
    end

    ############################################### CLI ####################################################################
   

    def usage *args
        puts "Command usage:"
        puts "usage - view usage"
        puts "insert_disk - add currrently inserted disk to database"
        puts "locate <filename> - locate disk by filename specified"
        puts "eject - eject currently inserted disk"
        puts "quit - exit the program"
        false
    end

    def quit *args
        true
    end

    def main_loop
        print ":>"
        cmd = gets.strip!
        args = cmd.split
        cmd = args[0]
        args = args[1..args.size]

        begin
            send cmd, args.join(' ')
        rescue
            puts "Command not found!"
        end
    end
end


dvdtool = DVDtool.new DBNAME
dvdtool.usage

done = false

while not done
    done = dvdtool.main_loop
end

Main issues are you have to search by exact filename at the moment. Could make it so its a slightly more fuzzy search if you want. Will deny attempts to try add a CD with the same volume_id to the database if it already exists. Should really write a way to remove dvd's from db. You need to manually type insert_disk once you have inserted the disk. You could snoop HW requests so it automatically deals with the cd when inserted but I don't feel like writing a HAL wrapper atm :|

Anyhow you get the picture, there's more than enough showing you what you need to do if you want to make a proper job of it. There's prolly a ton of bugs but im not motivated to really test it