https://github.com/cheminfo/smart-sqlite3-filter

Science Score: 26.0%

This score indicates how likely this project is to be science-related based on various indicators:

○
CITATION.cff file
✓
codemeta.json file
Found codemeta.json file
✓
.zenodo.json file
Found .zenodo.json file
○
DOI references
○
Academic publication links
○
Academic email domains
○
Institutional organization owner
○
JOSS paper metadata
○
Scientific vocabulary similarity
Low similarity (11.2%) to scientific vocabulary

Last synced: 9 months ago · JSON representation

Repository

Basic Info

Host: GitHub
Owner: cheminfo
License: mit
Language: TypeScript
Default Branch: main
Homepage: https://cheminfo.github.io/smart-sqlite3-filter/
Size: 130 KB

Statistics

Stars: 1
Watchers: 5
Forks: 1
Open Issues: 2
Releases: 9

Created almost 2 years ago · Last pushed over 1 year ago

Metadata Files

Readme Changelog License

smart-sqlite3-filter

This package allows to query a sqlite3 database using better-sqlite3 in a way like 'firstName:luc'.

Development on Apple Silicon

If you npm configuration contains ignore-scripts=true you may have to execute the following commands:

bash cd node_modules/better-sqlite3 npm run build-release

Installation

$ npm i smart-sqlite3-filter

Usage

```js import { search } from 'smart-sqlite3-filter'; import sqLite from 'better-sqlite3';

const db = sqLite(':memory:'); const sql = ` CREATE TABLE IF NOT EXISTS names ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, year INTEGER NOT NULL age REAL NOT NULL );

INSERT INTO names (name, year, age) VALUES ('John', 1990, 30.1), ('Jane', 1985, 29.7), ('Alice', 2000, 25), ('Bob', 1990, 43); `; db.exec(sql);

// We don't search in a specific column, it will search everywhere // For string column it is case insensitive and should startWith, for number it should be the exact value search('John', db); // [{name: 'John', year: 1990}]

search('1990', db); // [{name: 'John', year: 1990}]

search('J', db); // [{name: 'John', year: 1990}, {name: 'Jane', year: 1985}]

// we search in a specific field and use the operator '>'. For numbers the following operators can be used: '>', '>=', '<', '<=', '<>', '!=', '='. Default to 'starts with' search('year:>1990', db); // [{name: 'Alice', year: 2000}] search('year:!=1990,2000', db); // [{name: 'Jane', year: 1985}] search('year:<>1990,2000', db); // [{name: 'Jane', year: 1985}]

search('age:30', db); // by default we take into account significative digits and it will search between 29.5 and 30.5 search('age:=30', db); // must be exactly 30, no hit search('age:=25', db); // must be exactly 25, 1 hit

// when searching for a string we can use the following operators: '^' (starts with), '$' (ends with), '~' (contains), '='. Default to contains. When searching for '=' it is case sensitive otherwise it is not. search('name:~o', db); // [{name: 'John', year: 1990}, {name: 'Bob', year: 1990}] search('name:$e', db); // [{name: 'Alice', year: 2000}] search('name:^J', db); // [{name: 'John', year: 1990}, {name: 'Jane', year: 1985}]

// A field may have various values separated by ',' search('year:1990,2000', db); // [{name: 'John', year: 1990}, {name: 'Bob', year: 1990}, {name: 'Alice', year: 2000}] search('year:1990,2000 name:$e,n', db); // [{name: 'John', year: 1990}, {name: 'Alice', year: 2000}] search('year:!=1990,2000', db); // [{name: 'Jane', year: 1985}]

// It is possible by searching for a range of values using '..' search('year:1980..1987', db); // [{name: 'Jane', year: 1985}] ```

Usage with BSON

```js import { search } from 'smart-sqlite3-filter'; import sqLite from 'better-sqlite3';

const db = sqLite(':memory:');

// create data and add some dummy data const sql = ` CREATE TABLE IF NOT EXISTS bsons ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, year INTEGER NOT NULL, bson BLOB NOT NULL );

INSERT INTO bsons (name, year, bson) VALUES
('John', 1990, jsonb('{"name": "John", "year": 1990}')),
('Jane', 1985, jsonb('{"name": "Jane", "year": 1985}')),
('Alice', 2000, jsonb('{"name": "Alice", "year": 2000}')),
('Bob', 1990, jsonb('{"name": "Bob", "year": 1990}'));

db.exec(sql);

search('name:$e,n', db); // 3 hits search('year:1990,2000 name:$e,n', db); // 2 hits search('bson.year:1990', db); // 2 hits search('bson.name:$e', db); // 2 hits search('bson.name:$e,n', db); // 3 hits search('bson.year:1990,2000 bson.name:$e,n', db); // 2 hits ```

License

MIT

Owner

Name: Cheminfo
Login: cheminfo
Kind: organization

Website: https://www.cheminfo.org/
Repositories: 242
Profile: https://github.com/cheminfo

GitHub Events

Total

Create event: 6
Issues event: 3
Release event: 4
Delete event: 2
Issue comment event: 14
Push event: 24
Pull request event: 10
Fork event: 1

Last Year

Create event: 6
Issues event: 3
Release event: 4
Delete event: 2
Issue comment event: 14
Push event: 24
Pull request event: 10
Fork event: 1

Issues and Pull Requests

Last synced: 10 months ago

All Time

Total issues: 3
Total pull requests: 16
Average time to close issues: N/A
Average time to close pull requests: about 16 hours
Total issue authors: 2
Total pull request authors: 3
Average comments per issue: 0.0
Average comments per pull request: 1.38
Merged pull requests: 14
Bot issues: 0
Bot pull requests: 0

Past Year

Issues: 3
Pull requests: 12
Average time to close issues: N/A
Average time to close pull requests: about 21 hours
Issue authors: 2
Pull request authors: 3
Average comments per issue: 0.0
Average comments per pull request: 1.5
Merged pull requests: 10
Bot issues: 0
Bot pull requests: 0

View more stats

Top Authors

Issue Authors

tpoisseau (2)
lpatiny (1)

Pull Request Authors

cheminfo-bot (15)
lpatiny (4)
tpoisseau (2)

Top Labels

Issue Labels

Pull Request Labels

autorelease: tagged (8) autorelease: pending (7)

Packages

Total packages: 1
Total downloads:
- npm 352 last-month

Total dependent packages: 0
Total dependent repositories: 0
Total versions: 10
Total maintainers: 2

npmjs.org: smart-sqlite3-filter

Query a sqlite3 database using better-sqlite3 in a way like 'firstName:luc'.

Homepage: https://github.com/cheminfo/smart-sqlite3-filter#readme
License: MIT
Latest release: 0.7.0
published over 1 year ago

Versions: 10
Dependent Packages: 0
Dependent Repositories: 0
Downloads: 352 Last month

Rankings

Dependent repos count: 26.3%

Average: 32.3%

Dependent packages count: 38.3%

Maintainers (2)

cheminfo-bot lpatiny