Make sqlite3 database searchable on Mac OS X

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Make sqlite3 database searchable on Mac OS X

Peng Yu
Hi,

It seems that sqlite3 databases are not searchable by Spotlight on Mac
OS X. Is there a way to make them searchable? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Make sqlite3 database searchable on Mac OS X

Simon Slavin-3
On 11 Feb 2020, at 4:10am, Peng Yu <[hidden email]> wrote:

> It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. Is there a way to make them searchable? Thanks.

A long time ago I wrote a SQLite indexer for … I think it was Sherlock back then.  It went through all text fields (ignoring numbers and BLOB) and indexed them.  It worked fine. But every time even a single field in a big SQLite database changed, my Mac slowed to a crawl, because the entire file got reindexed.  That was how it worked.

Hmm.  I see Core Spotlight allows entity registering.  You'd add hooks to SQLite so that every time a row was added, updated or deleted another routine was called, which told Spotlight about the new (or vanished) entity.  Problem is, it would work only for apps which used your own library with the extra programming in.  Without that, you're back to reindexing the entire database file any time anything in it changes.

Whichever way you did it, it would slow down SQLite a lot.  Sorry.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Make sqlite3 database searchable on Mac OS X

Jens Alfke-2
In reply to this post by Peng Yu


> On Feb 10, 2020, at 8:10 PM, Peng Yu <[hidden email]> wrote:
>
> It seems that sqlite3 databases are not searchable by Spotlight on Mac
> OS X. Is there a way to make them searchable? Thanks.

How would Spotlight know what tables or columns to index? It doesn't understand what database schema mean, and it can't tell a column with user-visible text apart from one with internal info.

You can write a Spotlight indexer plugin for your application that recognizes your file type and scans your SQLite database to generate indexing data.

The downside is that Spotlight is fundamentally file-based: it considers a file to be a single entity. So when the user searches for something that's in your database, it will just show the database file as a hit. It won't show separate results for every row that matches.

(Some apps work around this by creating stub files in a hidden directory, one file per entity. All these files need to contain is a record identifier so your indexer can index the file by looking at the corresponding row in the database. Spotlight will return the stub file as a match; when your app is told to open it, it looks at its record ID and displays the corresponding database row in its UI. Apple Mail is an example; there's a directory somewhere in ~/Library/Mail that contains an empty file for every email message.)

—Jens

PS: Disclaimer: My knowledge of Spotlight is years out of date; it may be that it's advanced since then.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users