Accessing SQL Explorer

1. Open a library in Memento Database
2. Click on the “Data” menu item
3. Select “SQL Explorer” from the dropdown menu

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

Using SQL Explorer to Create Custom Views

One of the most powerful features of SQL Explorer is the ability to create custom views of your data. Here’s how you can do it:

1. Write your SQL query in the SQL Explorer
2. Run the query to ensure it’s correct
3. If the query is valid, a “Custom View” button will appear on the toolbar
4. Click the “Custom View” button and Enter a name for your custom view.
5. The new view will now be available as a tab in the main library window

Important Notes on Presets

•  When creating a custom view, your SQL query must include an ‘id’ column. This is crucial for the program to match each row with the corresponding entry in the database. Without the ‘id’ column, the custom view cannot be created.
•  You can create views that display entries from the current library.

Tips for Effective Use

• Start with simple queries and gradually increase complexity as you become more comfortable with SQL syntax.
• 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.