This documentation provides a detailed overview of the query syntax, supported operations, and properties, as well as some examples on how you might use sortQL in your projects.
To get started, please refer to our quick start guide. This guide will walk you through the installation process and provide you with a basic understanding of how to use sortQL.
sortQL's syntax is inspired by SQL and is designed to be intuitive and easy to use. The basic structure of a sortQL query is as follows:
-- This is a comment: The basic structure of a sortQL query
OPERATION '<target>' FROM '<path>' (WHERE '<property>' <operator> '<value>' (AND/OR '<property>' <operator> '<value>')) (TO '<path>')
As you might notice, the query is made up of several components. Below is a brief explanation of each component:
- OPERATION: The type of operation to be performed (e.g., SELECT, MOVE, COPY, DELETE, ARCHIVE, UNARCHIVE).
- TARGET: The type of target (= 'files' or 'folders').
- FROM: The source path where the operation will be performed.
- WHERE: The condition used to filter files or folders based on specific properties (optional).
- PROPERTY: The property to be used for filtering (e.g., name, extension, size, created, modified, accessed).
- OPERATOR: The comparison operator used to compare the property with a specific value (e.g., =, !=, >, <, >=, <=, LIKE).
- AND/OR: The logical operator to combine multiple conditions (optional).
- TO: The destination path used for some operations (e.g., MOVE, COPY, ARCHIVE, UNARCHIVE).
Operations are divided into two categories: file operations and folder operations. Thus, each operation is designed to perform a specific task on files or folders.
sortQL supports the following operations:
- SELECT: Selects files.
SELECT 'files' FROM ''
- MOVE: Moves files to a different location.
MOVE 'files' FROM '' TO 'text-files'
- COPY: Copies files to a different location.
COPY 'files' FROM '' TO 'backup'
- DELETE: Deletes files.
DELETE 'files' FROM ''
- ARCHIVE: Archives files to a zip file.
ARCHIVE 'files' FROM '' TO 'archive.zip'
- UNARCHIVE: Unarchives files to a different location.
UNARCHIVE 'files' FROM '' TO 'unarchived'
- CONVERT: Converts files to a different format. (Supported files: image, audio, video)
CONVERT 'files' FROM '' TO 'jpg'
- SELECT: Selects folders.
SELECT 'folders' FROM ''
- MOVE: Moves folders to a different location.
MOVE 'folders' FROM '' TO 'projects'
- COPY: Copies folders to a different location.
COPY 'folders' FROM '' TO 'backup'
- DELETE: Deletes folders.
DELETE 'folders' FROM ''
- ARCHIVE: Archives folders to a zip file.
ARCHIVE 'folders' FROM '' TO 'archive.zip'
sortQL supports the following properties for files and folders:
Note
Allowed values: Any string. Regular expressions are supported.
Example:
-- Select files with a specific name
SELECT 'files' FROM '' WHERE 'name' = 'example'
-- Select files with a name that matches a pattern
SELECT 'files' FROM '' WHERE 'name' LIKE 'example.*'
Note
Allowed values: Any string. Regular expressions are supported.
Example:
-- Select files with a specific extension
SELECT 'files' FROM '' WHERE 'extension' = 'txt'
-- Select files with extensions 'txt' and 'pdf'
SELECT 'files' FROM '' WHERE 'extension' LIKE '(txt|pdf)'
Note
Allowed values: Any integer. Comparison operators are supported.
Example:
-- Select files with file size greater than 100000 bytes (= 100KB)
SELECT 'files' FROM '' WHERE 'size' > 100000
-- Select files with file size less than or equal to 1000000 bytes (= 1MB)
SELECT 'files' FROM '' WHERE 'size' <= 1000000
Note
Allowed values: Any string. Regular expressions are supported.
Example:
-- Select files with specific content
SELECT 'files' FROM '' WHERE 'content' = 'example'
-- Select files with content that matches a pattern (e.g., 'car', 'cars', 'racecar', 'carpet', etc.)
SELECT 'files' FROM '' WHERE 'content' LIKE '[^,]*car[^,]*'
Note
Allowed values: Any date string. Comparison operators are supported.
Example:
-- Select files created after a specific date
SELECT 'files' FROM '' WHERE 'created' > '01.01.2021'
-- Select files created before a specific date
SELECT 'files' FROM '' WHERE 'created' < '01.01.2021'
Note
Allowed values: Any date string. Comparison operators are supported.
Example:
-- Select files modified after a specific date
SELECT 'files' FROM '' WHERE 'modified' > '01.01.2021'
-- Select files modified before a specific date
SELECT 'files' FROM '' WHERE 'modified' < '01.01.2021'
Note
Allowed values: Any date string. Comparison operators are supported.
Example:
-- Select files accessed after a specific date
SELECT 'files' FROM '' WHERE 'accessed' > '01.01.2021'
-- Select files accessed before a specific date
SELECT 'files' FROM '' WHERE 'accessed' < '01.01.2021'
Note
Allowed values: Any string. Regular expressions are supported.
Example:
-- Select folders with a specific name
SELECT 'folders' FROM '' WHERE 'name' = 'example'
-- Select folders with a name that matches a pattern
SELECT 'folders' FROM '' WHERE 'name' LIKE 'project-*'
Note
Allowed values: Any date string. Comparison operators are supported.
Example:
-- Select folders created after a specific date
SELECT 'folders' FROM '' WHERE 'created' > '01.01.2021'
-- Select folders created before a specific date
SELECT 'folders' FROM '' WHERE 'created' < '01.01.2021'
Note
Allowed values: Any date string. Comparison operators are supported.
Example:
-- Select folders modified after a specific date
SELECT 'folders' FROM '' WHERE 'modified' > '01.01.2021'
-- Select folders modified before a specific date
SELECT 'folders' FROM '' WHERE 'modified' < '01.01.2021'
Note
Allowed values: Any date string. Comparison operators are supported.
Example:
-- Select folders accessed after a specific date
SELECT 'folders' FROM '' WHERE 'accessed' > '01.01.2021'
-- Select folders accessed before a specific date
SELECT 'folders' FROM '' WHERE 'accessed' < '01.01.2021'
sortQL supports advanced features such as comparative operators and regular expressions to provide more flexibility and control over file and folder operations.
sortQL supports the following comparative operators:
- LIKE: Similar to SQL's LIKE operator, it allows you to match properties with regex patterns.
- =: Equal to.
- !=: Not equal to.
- >: Greater than.
- <: Less than.
- >=: Greater than or equal to.
- <=: Less than or equal to.
You can use these operators to compare properties with specific values. Below are some examples of using comparative operators:
-- Select files with file size greater than 100000 bytes (= 100KB)
SELECT 'files' FROM '' WHERE 'size' > 100000
-- Move files created between specific dates to a different location
MOVE 'files' FROM '' WHERE 'created' >= '01.01.2021' AND 'created' <= '31.12.2021' TO 'projects_2021'
-- Delete files with extensions other than 'txt' and 'pdf'
DELETE 'files' FROM '' WHERE 'extension' LIKE '?!(txt|pdf)'
Additionally, you can combine multiple conditions using the logical operators AND and OR.
A valid example of using logical operators:
-- Select big files created after a specific date
SELECT 'files' FROM '' WHERE 'size' > 100000 AND 'created' > '01.01.2021'
sortQL supports regular expressions for matching file and folder properties. In case you're not familiar with regular expressions, they are a powerful tool for pattern matching and can be used to match complex patterns in strings.
To learn more about regular expressions, you can refer to the MDN Web Docs.
Here's an example of using regular expressions to match files with a specific extension:
-- Select files with extensions 'txt' and 'pdf'
SELECT 'files' FROM '' WHERE 'extension' LIKE '(txt|pdf)'
-- Select files with names that match a specific pattern
SELECT 'files' FROM '' WHERE 'name' LIKE 'example.*'
Before actually performing an operation, you can use the SELECT operation to probe the files or folders that match specific conditions. This can be useful to verify that the conditions are correct before performing the actual operation. Especially when using complex conditions, probing can help you avoid unintended consequences.
Below we list a few examples of how sortQL can be used:
Suppose you have a directory containing various types of files, including documents, presentations, and spreadsheets. You want to move these files to separate subfolders based on their file extensions:
-- Move documents to a different subfolder
MOVE 'files' FROM '' WHERE 'extension' LIKE '(docx|doc|pdf)' TO 'documents'
MOVE 'files' FROM '' WHERE 'extension' LIKE '(pptx|ppt)' TO 'presentations'
MOVE 'files' FROM '' WHERE 'extension' LIKE '(xlsx|xls)' TO 'spreadsheets'
You have a directory with a large number of files and want to delete files that are older than a specific date:
-- Delete files older than a specific date
DELETE 'files' FROM '' WHERE 'created' < '01.01.2021'
You have a directory with a large number of files and want to archive files that are larger than 100MB:
-- Archive files based on size
ARCHIVE 'files' FROM '' WHERE 'size' > 100000000 TO 'archive.zip'
You have a directory with several subfolders and want to copy all folders that contain the word "project" in their name to a different location:
-- Copy folders based on name
COPY 'folders' FROM '' WHERE 'name' LIKE '*project*' TO 'old-projects'
You have a shared directory between Windows and Mac, and you want to convert all MOV files to MP4 for better compatibility:
-- Convert MOV files to MP4
CONVERT 'files' FROM 'shared' WHERE 'extension' = 'mov' TO 'mp4'