How to Query

USAGE [TABLE_ALIAS] [[FILTERS]] [[OPTIONS]] INPUT DATA TYPES RANGE An integer range is of the form [MIN]-[MAX] MIN must be a non-negative integer MAX must be either a non-negative integer, or "?" (without the quotation marks) NAME A string surrounded by double quotes, with all double quotes within itself escaped EXAMPLES "Foo" "Bar" "Ree Gee" "Jeremy \"Stark\" Smith" "[.]mp4$" NAME_LIST A list of NAMEs If there is just one NAME in the list, it is interpreted as a regexp. EXAMPLES "English (Language)" "French (Language)" A list of two strings that is interpreted as an array of literal strings "[^ ]+ \(Language\)$" A list of one string that is interpreted as a regexp TABLE_ALIAS file f dir d dir d! (matching only against the basename) device D Only available for one sub-filter - "D exists". tag t (matching against child tags too) tag t! external_db x Only available as a sub-filter, for files. ATTRIBUTE Different tables may have different valid attributes. Each attribute has a "kind" of relation too - either one-to-many, or many-to-many VALID ATTRIBUTES [ATTRIBUTE_KIND] attributes [ATTRIBUTE_NAME] [VALID_TABLES] many-to-one attributes name all except D and e title f description f dir f mime f size all except x, D and e duration f md5 f sha f time_origin f time_recorded f width f height f fps f views f likes f dislikes f many-to-many options: dct (perceptual hash) f audio (perceptual hash) f tag f parent (direct ancestor) t parenty (any ancestory) t child t childy t FILTERS Each may be preceded by "not" (without the quotation marks) to invert the filter Each must be separated by a logical operator - thus far the only implemented ones are "and" and "or" (use without the quotation marks) [TABLE_ALIAS] [NAME_LIST] Entries have relations to entries of one of these names in this table D exists Only available for file and directory tables. Filters for files/dirs that reside on a device that the server can access with an fopen call (i.e. files that are both local and readable) value [RANGE] [NAME_LIST] Entries have a variable named one of the listed names, which has a value in the specified range attr [ATTRIBUTE] [OPERATOR] [STRING_OR_INTEGER] A filter that operates directly on the columns of the primary table. If it is a string, STRING_OR_INTEGER must be surrounded by double quotes, with any contained double quotes escaped. Else it must be a positive integer OPERATOR must be one of Integers only: ">", "<" Strings only: "r" (regexp) Any: "=" SHORTHAND A NAME on its own, without even specifying "attr", is interpreted as shorthand for attr name r [NAME] EXAMPLES f attr name r "[Pp]attern" Filter for files whose names match the regexp f attr id < 99 f attr size < 1000000 Search for files under 1MB in size f attr dir < 1000 Note that dir is an integer - it is not translated to a string with this filter f "^A" Shorthand for f attr name r "^A" Lists all files beginning with "A" t parent = "Country" Filter for tags where one immediate ancestor is the tag "Country" t parenty = "Continent" Filter for tags where any ancestor is the tag "Continent" backups [RANGE] Filter for files with a number of backups in this range same [ATTRIBUTE] [RANGE] This filter is NOT to be preceded by a logical operator Only for the file table Filters results for files sharing an attribute, that attribute having a number (in the given range) of files associated with it. The order in which this filter is applied matters For many-to-one attributes (such as name, dir, mime, size, duration, md5, sha) All other filters declared before this filter apply WITH this filter All other filters declared after this filter apply AFTER this filter For many-to-many attributes (such as dct) It is a bit different WARNING Using the name attribute might take a VERY long time to execute without other filters EXAMPLES many-to-one f "foo" same dir 2-? Finds files matching the regexp "foo", so long as they share a directory with another file matching that regexp. f same dir 2-? "foo" Finds files matching the regexp "foo", so long as there is another file in its directory many-to-many f "foo" same dct 2-? Finds files whose DCT hashes overlap with those of files with names matching the regexp "foo" f same dct 2-? "foo" Finds files matching the regexp "foo", so long as there is another file with overlapping DCT hashes f same dct 101-? List files who have a DCT hash that at least 100 other files also have f same dct 1-1 List files who have a unique DCT hash OPTIONS order [MODE] [ATTRIBUTE] MODE must be one of a (ascending) or d (descending) Order results by the ATTRIBUTE For many-to-many attributes, files with multiple matching attributes will appear multiple times ATTRIBUTE must be one of many-to-one options: name one-to-one options: id size (for tags/dirs, it is the number of files associated with it) file table additional options: Any of the attributes listed under the "same" filter order-by-value [MODE] [[NAMES]] MODE must be one of a (ascending) or d (descending) NAMES must be a list of NAMEs (like a NAME_LIST, except a list of length 1 will not be interpreted as a regexp) Order results by the named variables, in that order. The order is left-to-right, the value being a chain of IFNULLs limit [INTEGER] offset [INTEGER] KNOWN BUGS At least one filter must be included EXAMPLES f t "Music" t "English (Language)" "French (Language)" "German (Language)" and value 90-100 "Musicness" "Score" and attr name r "[.]webm$" limit 100 List 100 WEBM files tagged Music and also tagged one of: English, French or German language, having either a musicness or score of between 90 and 100 f attr name r "\\\\.mp4" and d "^/" limit 100 List 100 MP4 files on the server f d "^https://www[.]youtube[.]com/" order-by-value d "Musicness" "Score" List files from https://youtube.com/ in descending order of 'value', where the 'value' is either the file's "Musicness" or - if that is unavailable - the file's "Score" f d "^http" and not t "Music" List remote (over HTTP/HTTPS) files which are not tagged "Music" f not t "!!ROOT TAG||" and not "[.](json|xml)$" List files which are not tagged with anything, ignoring JSON and XML files. f mime "^image/" List files with image mimetypes f ( t "Music" and t "Video" ) or ( t "Music" and mime "^video/" ) List music videos f attr name r "[.](png|jpe?g)" same dct 60-? limit 1000 The command I used to locate all my neural style transfer renders f "[.](mp4)" same dct 10-? limit 1000 Equivalent to f attr name r "[.](mp4)" same dct 10-? limit 1000 The command I used to locate videos by a Youtuber (using the same video intro in his videos) f attr name r "[.](mp4|MP4|mkv|MKV|avi|AVI|webm|WEBM)$" same dct 2-? and attr name r "[.](png|jpe?g|PNG|JPE?G|gif|GIF|bmp|BMP)$" Find files which are probably screenshots of movies f same size 2-? limit 100 order d size Find files that are probably duplicates, based only on their file sizes. f d "^https://www.youtube.com/watch\?v=" same dct 2-? same size 2-? limit 100 Find files which have the same DCT hash and same size as a file from youtube. d "." order d size List the directories with the most files (non-recursive) t "." order d size List the tags associated with the most files (not counting descendant tags) f attr tag r "Foobar" Equivalent to f t "Foobar" f attr tag = "Foobar" Equivalent to f t "^Foobar$" t attr parent = "Country" order d size List the Country tags in order of decreasing numbers of files they are associated with f t! "Uploader: " and not t "Youtube Channel" and d "^http" and d "youtube" limit 100 List Youtube files directly tagged with "Uploader: ", where those tags are not recognised as Youtube Channels. Useful for fixing tag mistakes. f attr title r "Podcast" and not eras 1-? Look for videos with "Podcast" in the title, which have not got any associated eras e t "Advert" Look for eras tagged "Advert"