search-lite

Simple NodeJS backend service pre-indexing search for SQLite that updates when DB entries are added or updated.

By pre-indexing the search, search time is drastically reduced by simply evaluating matrices of integers, rather than comparing strings.

Data sources can be located in both the database and also a byte stream that is provided by the app when requested by search-lite. This is useful for indexing text files that are external to the database such as a source code repository or document collection.

By performing the string matching once in advance rather than every time a search is requested, the process is both faster and reduces the server load required to perform the search.

The result is a lightweight process capable of great results.

This library is for SQLite, thus the name search-lite

NOTE: New feature Ordering By Search Category requires a rebuild of the search-lite locators table.

Getting started

Installing

From within your project directory where package.json is found, simply run this.

npm install @roycethebiker/search-lite

This library performs search analysis in advance of a search request by indexing the searchable data as it is saved to the DB.

How It Works

SQLite Triggers are added to tables for data that the developer makes searchable. When new data is added or an update to that table is made, the triggers then log that the record in that table needs to be scanned.

The scanning of the data marked for searching is broken down into tags of three or more alphanumeric blocks and saved for indexing.

When a search is requested, the tags are located and then referenced back to the table and record of origin.

How To Use It

Constructor

Create your SQLite DB instance and pass it to search-lite with the second option of true to use the internal event timer. Use false if to use an external event timer that is controlled by the app.

this.Sql = new sqlite.Database('./my_app.db', (err) => {
    if (err) {
        console.error('Could not connect to database', err)
    } else {
        console.info('Connected to database');
        this.searchLite = new SearchLite(this.Sql, true);
    }
});

Constructor Arguments

| | | | ---- | ---- | | db: sqlite.Database | The SQLite DB instance | | useInternalEventTrigger: boolean | True to let search-lite manage its own events, false to call externally | | activeScanLimit?: number | Optional, to limit the number events to process during a scan | | delimiterSpannersV?: string | Optional, to set the Delimiter Spanner |

Registration

By registering an event trigger, the DB is modified to create event records when an update or insert happens. The registration is used by the event processor to perform the scanning of the data.

NOTE: This current version requires that the searching table use a PRIMARY INDEX column named id.

Simple registration of an indexing for changes made to the users table.

this.searchLite.registerEvent('users', 
                            [ 'username', 'firstname', 'lastname' ], 
                            'users');

Arguments

{
    tableName: string,      // Name of table to receive SQL triggering
    columnNames: string[],  // Array of column names to scan when triggered
    category: string        // Isolate search results by category 
}

The Search

Searching the index is done by asking with a list of tags as an array of strings. The return includes the table names and tuples for the records that match.

    this.searchLite.search([[ 'perl', 'bash', 'grep', 'pipe' ], (results: any) => {
        console.log('searched for %s has returned %s', w, JSON.stringify(results));
    }, 'marquis');

Managed Tables

The constructor will create search-lite tables if they don't exist. These tables are how the indexing is managed.

The Tables

All tables that search-lite creates are prefixed with searchLite and that is implied for the table below.

| Table name | Function | | ---- | ---- | | Triggers | Maps events back to the origin of the registration | | Events | SQL changes execute triggers that record the origin of the event in this table | | Tags | Normalizer for searchable text blocks | | Tables | Normalizer for table names | | Categories | Normalizer for categories | | Locators | Matrix of know scan results |

Delimiter Spanners

Normal search tags are only generated from unbroken strings of alphnumeric words. Delimiter spanners allow special characters to be validated as part of the unbroken words. It also allows the text following the spanner to begin it's own tag.

By setting the hypen as a delimiter spanner.

searchLite.delimiterSpanners('-');

The word CFM-109 becomes tags CFM, CFM-, CFM-1, CFM-10, and CFM-109, but also 109.

The word ABCD-1234-EFGH-5678 becomes 38 tags, all tags that start with ABC, all tags that start with 123, EFG, and one two tags that start with 567.

Ordering By Search Category

NOTE: This feature requires a rebuild of the search-lite locators table. Stop the backend, make a backup of the database, then drop the searchLiteLocators table. It will be rebuilt with the new primary keys at startup. Once the system is running, call SearchLite.scanAllData() to rebuild all the locators.

A single table can have multiple categories, and categories can have multiple columns. Categories and searches can only be applied to a single table.

In this example Replit example where searching for users with the word ''April'' and wanting results where the name is a match to come first, and include matches for birth date and address to be at the bottom of the result set.

Unlike the basic SearchLite:search that only returns the tuple ID and table name, SearchLite:extendedSearch returns the entire record set for all matching columns in the table that is defined by the first given category. An extra column sort: number is appended to the table set.

Set up mulitple catigories on a table using different columns.

constructor(private searchLite: SearchLite) {
    // ... other code
    this.searchLite.registerEvent('users', [ 'userName' ], 'usersByName');
    this.searchLite.registerEvent('users', [ 'address' ], 'userByAddress');
}

The use SearchLite:extendedSearch to search and the order of the category becomes the order of priority when returning results.

public search(searchText: string) {
    this.searchLite.extendedSearch([searchText], (result: any) => {
        // ... other code
    }, [ 'usersByName' , 'usersByAddress' ]);
}

When given the search text of Robin, results with Robin as part of the users name will be at the top of the results, followed by anyone living on a street named Robin.

Upgrading

After upgrading use scanAllData() to rebuild all tags and locators.

Support

Send emails to [email protected]

Buttons generated by Shields IO

Contributing

Anyone is welcome to fork this project on GitLab search-lite

Create a fork and submit a pull request and it will be reviewed.

Authors and acknowledgment

SQLite3 team!!! 🍺🍺🍺

License

Copyright (C) 2021 Silicon Tao Technology Systems

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; GPL-2.0-only.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to

Free Software Foundation
51 Franklin Street, Fifth Floor
Boston, MA 02110
USA

Project status

This is a new project that is being tested in PROD.

Feature requests are added in GitLab as issues.

Developing This Project

One-time only install and setup.

npm install --global np
npm i -g typescript -D
npm i -g typings -D
tsc --init

tsc is configured to compile in package.json in scripts.

To compile

tsc

Commit to GitLab.com

git commit
git push

Publishing is done by np

np

To publish a beta version for testing a branch in development.

npm run beta