Results 1 to 2 of 2

Thread: SQLLite and SQL Commands

  1. #1
    Join Date
    Apr 2013
    Posts
    15

    Default SQLLite and SQL Commands

    Hi All,

    I did a quick search on "SQL" only to find 0 items. My Collection has reached over 10,000 movies and several hundred TV Series. I installed SQLLite and have created several SQL Statements to search and catalog my collection. I thought i'd share these and hopefully others will have done the same. I have tried on several occasions to update the DB using SQLLite but this seems to corrupt the DB and so i've stuck to "read only" type of SQL statements. My 3 most used SQL are as follows:

    (1) This SQL selects some of the fields in the DB to list them -- i use the list in an excel spreadsheet before buying a DVD to convert it to disk:

    select IMDB_ID, Title, file, Duration/60 as Duration, printf("%.2f", CAST((cast(Size as real(10.2)) / 1073741824) AS real(5.2))) AS GB,
    Year, strftime('%m-%d-%Y', datetime(DateAdded, 'unixepoch')) as DateAdd from MGOFile
    where disc = '0'
    and not imdb_id = ""
    Order by Imdb_id;

    (2) This SQL gives me a breakdown of all my files by size.

    select t.range as [sz range], count(*) as [Count]
    from (
    select
    case
    when size between '0' and '2684354560' then "1- less than 2.5GB"
    when size between '2684354561' and '5368709120' then "2- 2.5GB to 5.0GB"
    when size between '5368709121' and '10737418240' then "3- 5.0GB to 10GB"
    when size between '10737418241' and '16106127360' then "4- 10GB to 15GB"
    else "5- more than 15GB"
    end as range
    from MGOfile where Disc = '0'
    and not (
    imdb_id = ""
    )) t
    group by range

    (3) Finally this SQL gives me breakdown by decade of the movie count

    select t.range as [year range], count(*) as [Decade Count]
    from (
    select
    case

    when year between '0' and '1919' then "A - Before 1920s "
    when year between '1920' and '1929' then "B - The 1920s"
    when year between '1930' and '1939' then "C - The 1930s"
    when year between '1940' and '1949' then "D - The 1940s "
    when year between '1950' and '1959' then "E- The 1950s "
    when year between '1960' and '1969' then "F- The 1960s "
    when year between '1970' and '1979' then "G- The 1970s "
    when year between '1980' and '1989' then "H- The 1980s "
    when year between '1990' and '1999' then "I- The 1990s "
    when year between '2000' and '2009' then "J- The 2000s "
    when year between '2010' and '2019' then " K- The 2010s "
    when year between '2020' and '2029' then " L- The 2020s "
    else " M- The 2030s "
    end as range
    from MGOfile where Disc = '0'
    and year > 0
    and not (
    imdb_id = ""
    )) t
    group by range
    order by range


    I hope this gets some traction and others have develop SQL statements to use the DB directly

  2. #2
    Join Date
    Jun 2012
    Posts
    1,007

    Default

    Great work. To do SQL updates I suggest stopping the Mezzmo service and shutting down the Mezzmo GUI. That has worked for me in the past. Obviously after you have done a backup

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •