filesearchdb

filesearchdb is a solution to index files based on custom criteria. It is not coupled to a central database but instead uses SQLite databases to store the criteria to files.

The problem filesearchdb solves

There are file indexing solutions out there, but mostly, they are coupled to specific programs. For example, audio players have their own database for music files in directories. But that database usually isn't usable from another audio player that is accessing the same storage so the index has to be rebuilt for the second audio player.

There is also the problem that documents are indexed on one computer using a file index that is tied to a desktop environment. But others cannot access that file index.

In cases where files are not indexed and no desktop environment is used to do that indexing, it may be hard to manually select the right files.

In all these scenarios, filesearchdb may become handy.

How it works

filesearchdb relies on a SQLite database named "filesearch.db" that is placed inside a directory in the file system. That database contains a table with all files that are indexed and several tables defining schemas for indexing. Each schema defines a set of attributes that can be added to a file. filesearchdb can be used to query files that are known to the database and it will return the file paths.

How to use it

Creating a database

To build a database, invoke filesearchdb like this:

filesearchdb.php create

This will create a new database in the current directory. The next step is to add a schema.

Adding a schema

To add a schema, you need to define a name for it and define at least one attribute for it. An attribute can have one of the following three data types:

• string: text up to 512 characters.
• int: integer values.
• boolean: true/false flags.

Attribute names must not contain commas, since commas separate attributes from each other. When defining an attribute, use the following syntax: attribute-name=attribute-type.

Example: Create the schema test1 with the attributes hello of type string and world of type int:

filesearchdb.php schema add test1 hello=string,world=int

Adding files

To add a file, filesearchdb needs to know the attributes for the file and the path to the file. The latter can be a relative or an absolute file, depending on the use case. Theoretically, URLs could also be possible as file paths.

Example: Add a file to the schema "test1" with the attributes hello set to "hallo" and world set to 42:

filesearchdb.php add hello=hallo world=42 /path/to/file

There is no need to specify the schema. filesearchdb will loop over all schemas of the database and set the columns that match the attribute names.

Updating files

Updating the attributes of a file is the same as adding a file. If we want to change the value of the "world" attribute from the example above, we would invoke filesearchdb like this:

filesearchdb.php add world=23 /path/to/file

Querying files

After adding files to the database, we can retrieve file paths by specifying attributes. The attributes are concatenated by "AND" in SQL when querying the database. The result of querying the database is a list of file paths that can be passed to another program using xargs.

Example 1: Output all file paths for all files that have the attribute "world" set to the value 42:

filesearchdb.php query world=42

Example 2: Play all music files with mpv that have the attribute "coolness" set to 10:

filesearchdb.php query coolness=10 | xargs mpv