Home

Welcome to Dos SQL

I created this very quickly to solve a particular problem. Lots of photos and no great way to find duplicates.

So I started just scanning directories and putting them in a hash and then programatically extracting what I wanted but the requirements kept changing and so did the program. Then I found SQL Lite and memory databases. So I put the scanned files in the mem database and used command line to pass SQL to query.

The result was great.

A few issues unresolved.
An MD5 or query about content of the file would seem pretty unachievable. Particularly as the drives I was scanning were remote and 2TB each.

The Windows command line parser kept interpreting my SQL as commands for it - particularly & and || commands.

If you need these then you need to quote them "&" and "||" problem solved. Likewise my parsing for the filepaths was not that smart (an exercise for the reader ;-). If it contains spaces, quote them "C:\Temp Dir". And to be safe white space either side. I see it currently picks up and ) after if no white space.

eg.
Select * from C:\ where leafname in (select leafname from D:) will fail the path D:) not found
Select * from C:\ where leafname in (select leafname from D:\ ) will work fine

Fields - not so smart but I put all the data in duplicated

filename, leafname, rootname

These could be smarter but it made the users sql easier to write.

When I added the EXIF data I was not really expecting so many fields and it became a bit overwelming. But I left them all in anyway. I did try adding them as a name value table - but again doing that made the queries painful to write (rather than the usage documentation).

Usage: dosql.exe <sql select="" command="">
sql select command
SELECT * from <directoryname> ...
Other than the directory name the SQL is standard SQLite3
Note the Dos command parser can cause some issues with passing SQL clearly</directoryname></sql>

1. If the Directoryname has spaces it must be quoted
   Select * from "C:\Dir with spaces"
2. SQLite3 string concat is || but is seen as a dos command so must be quoted
   Select filename"||"ext from C:\Movies
3. Any comparitor may be seen as an I/O redirection
   Select filename from C:\TV where filebytes">"3000000
   What fields can you query
   filename - the filename including path
   leafname - the filename only
   rootname - the filename (less the ext)
   ext - the file extension
   filesize - a string representation of the size
   filebytes - a numeric value containing the file size in bytes

A series of strings from the files EXIF data (if available) list below;
filedatetime filetype mimetype sectionsfound computed_html computed_height computed_width computed_iscolor computed_byteordermotorola computed_aperturefnumber computed_thumbnail_filetype computed_thumbnail_mimetype make model orientation xresolution yresolution resolutionunit software datetime ycbcrpositioning exif_ifd_pointer gps_ifd_pointer thumbnail_compression thumbnail_xresolution thumbnail_yresolution thumbnail_resolutionunit thumbnail_jpeginterchangeformat thumbnail_jpeginterchangeformatle exposuretime fnumber exposureprogram isospeedratings exifversion datetimeoriginal datetimedigitized componentsconfiguration shutterspeedvalue aperturevalue brightnessvalue meteringmode flash focallength subjectlocation_0 subjectlocation_1 subjectlocation_2 subjectlocation_3 makernote subsectimeoriginal subsectimedigitized flashpixversion colorspace exifimagewidth exifimagelength sensingmethod scenetype exposuremode whitebalance focallengthin35mmfilm scenecapturetype undefinedtag_0xa432_0 undefinedtag_0xa432_1 undefinedtag_0xa432_2 undefinedtag_0xa432_3 undefinedtag_0xa433 undefinedtag_0xa434 gpslatituderef gpslatitude_0 gpslatitude_1 gpslatitude_2 gpslongituderef gpslongitude_0 gpslongitude_1 gpslongitude_2 gpsaltituderef gpsaltitude gpstimestamp_0 gpstimestamp_1 gpstimestamp_2 gpsimgdirectionref gpsimgdirection