SQL Explorer is a powerful feature in Memento Database that allows advanced users to retrieve records using SQL queries. This tool provides flexibility and precision in data retrieval, enabling you to perform complex searches across multiple libraries.

Accessing SQL Explorer

1. Open a library in Memento Database
2. Tap the search icon
3. Select the SQL icon to open SQL Explorer

Understanding SQL Explorer Functionality

Query Support

Currently, SQL Explorer supports SELECT queries of any complexity, including queries involving multiple tables (libraries).

SQLite Syntax

Queries are executed through SQLite, so use SQLite syntax for your queries. For more information on SQLite syntax, please refer to the SQLite documentation.

Data Type Mapping

Memento Database fields map to SQLite data types as follows:

• INTEGER: Integer, Date/Time (Unix time, seconds since 1970), Boolean (0 or 1), Rating
• REAL: Real, Currency
• NUMERIC: JavaScript field
• TEXT: All other fields

Notes:

• For Calculation fields, the column data type corresponds to the result format settings in the field settings.
• Lookup fields are currently not supported in SQL Explorer queries.

Table and Column Names

Table names correspond to library names, and column names correspond to field names. Use quotes for names containing spaces or non-Latin characters. For example:
SELECT * FROM "My Library"
SELECT "my field", "my field 2" FROM "My Library" WHERE "my field" < 100

Record Identification

Each table contains an ‘id’ column that defines the entry identifier. If your query results include the ‘id’ column, SQL Explorer will display a table with the option to open the corresponding entry.

To view the ‘id’ column in your results, you need to explicitly select it and give it an alias. For example:

SELECT id AS team_id, name FROM Team

In this query, the ‘id’ column will be displayed in the results under the alias ‘team_id’, along with the ‘name’ column.

Formatting

When the ‘id’ column is present in the results, Memento field formatting rules will be applied to the columns. Otherwise, they will be displayed in their raw format.

For example, Date fields with an ‘id’ column display in the format set in field settings (e.g., “Jan 1, 2023”), while those without an ‘id’ column show as a Unix timestamp (e.g., 1672531200). For image fields, an ‘id’ column displays the actual image, and without it, only the file name is shown.

Deleted Records

Tables contain all records, including those in the trash. The ‘removed’ column determines whether a record is deleted or not. To get all non-deleted records from a library, use:
SELECT * FROM Library WHERE removed = 0

Linked Records

You can use records from different libraries in one query and use JOIN operations. There are two types of links:

One-to-one or one-to-many: The “Link to Entry” field type column in the library table contains the linked record’s identifier.

SELECT Books.title, Authors.name
FROM Books
JOIN Authors ON Books.author = Authors.id
WHERE Books.removed = 0 AND Authors.removed = 0

Many-to-many: These links use an additional table named “relations_[Current Library Name]_[Link to Entry Field Name]”. This table has two columns: source_id (TEXT) and target_id (TEXT).

SELECT Students.name, Courses.course_name
FROM Students
JOIN relations_Students_Courses ON Students.id = relations_Students_Courses.source_id
JOIN Courses ON relations_Students_Courses.target_id = Courses.id
WHERE Students.removed = 0 AND Courses.removed = 0

Saving and Using SQL Presets

You can save your SQL queries as presets for quick access and reuse. Here’s how:

Saving a Preset

1. Execute your SQL query in the SQL Explorer
2. Once the results are displayed, tap the save button on the toolbar above the results table
3. Give your preset a name and save it

Using a Preset

You can select your saved preset from the library menu to view the records or data returned by the query.

Editing a Preset

To modify a preset’s SQL query:

1. Locate the preset in the library menu
2. Tap the three-dot menu next to the preset
3. Select “Edit SQL”
4. Make your changes to the query and save

Important Notes on Presets

• If the query includes an ‘id’ column, you’ll be able to open individual entries from the results.
• When your query results contain data from multiple tables, only one ‘id’ column is allowed.

Tips for Effective Use

• Start with simple queries and gradually increase complexity as you become more comfortable with SQL syntax.
• Memento Database features an AI Assistant for generating SQL queries. Simply open SQL Explorer, tap the robot icon, and describe your query in natural language. The AI Assistant will create the SQL query for you.
• Save frequently used queries as presets to save time and ensure consistency in your data analysis.
• Use the ‘id’ column in your SELECT statement to enable record opening and proper field formatting.
• Remember to filter out deleted records using the ‘removed’ column when necessary.
• When working with linked records, understand the relationship type to use the correct join method.

Common Issues and Solutions

Slow Query Execution

Due to Memento’s data structure, queries are executed through an additional layer. This means that before the first query execution, an initialization process occurs. If you have a large amount of data, this may take some time. The more fields in your query, the longer the initialization takes.

To improve performance, try to limit the number of fields in your query to only those necessary for your task.