Layer2 Data Provider for File System: Data Query
The data query retrieves the desired documents from the file system.
The File System Provider supports a SQL-like syntax to filter the files and folders found in the given directory as described below.
SELECT [Fields] FROM [Directory] WHERE [Filter]
The list of [Fields] can either contain a wildcard (*) or a comma-separated list of field names. Furthermore, it is possible to rename the fields by using the SQL alias-syntax. For example, SELECT FileExtension AS ext would select the contents of the file extension field and populate it as a field named ‘ext’ in the result.
The FROM part of the select statement currently supports exactly one directory path. This is optional if the connection string contains the ‘Directory’ parameter, otherwise it is mandatory. If both are given, the select statement has a higher priority and a warning will be logged.
The WHERE clause supports various elements known from SQL to build a complex conditional filter:
• Conditional operators: <, >, <=, >=, =, <>, LIKE, IN
• Logical operators: NOT, AND, OR
• Brackets can be used to change precedence
Various wildcards can be used with conditional operators. LIKE supports “%” (any characters) and all other conditional operators support “?” (exactly one character) and “*” (like %).
Example filter only includes files/folders in specific folders:
WHERE FilePath IN ('/myFolderA/*', '/myFolderC/myFolderD/*')
Example filter excludes files/folders in specific folders:
WHERE NOT FilePath IN ('/myFolderA/*', '/myFolderC/myFolderD/*')
Example for a simple filter to get all txt files:
WHERE FileExtension = ‘.txt’
Example for a complex filter to get all files created before the 1st of February 2016 with ‘customer’ in their file names:
WHERE Filename = ‘*customer*’ AND Created < ‘2/1/2016’
Example filter selects all files starting with the letter ‘A’:
WHERE Filename LIKE ‘A%’
Example filter selects all files starting with the letter ‘A’ and ending with ‘CDE’:
WHERE Filename = ‘A*CDE’
Example filter selects all files bigger than 1 GB:
WHERE Size > 1 GB
Example filter excludes all files with extension .aspx:
WHERE NOT FileExtension LIKE “.aspx”
Supported units for file-sizes are:
• KB = Kilobytes • MB = Megabytes • GB = Gigabytes
If the “IncludeFolders” parameter is “true”, which is the default, all folders that contain files matching the filter, will implicitly be included in the result, even if they do not match the filter. This is to make sure files will never be returned without the respecting folders. If only files should be returned, the “IncludeFolders” parameter needs to be set to “false”.