SQLite Keynote

(Printer friendly version)

D. Richard Hipp, author of SQLite presented this years keynote. A fascinating jaunt from the civilized realms into barbarian territory and back. Here are some of the notes I took—

(Incidentally I noted that Richard pronounces SQLite S-Q-L-ite, not Sequel-Lite.)

Non-Comment Source Lines in SQLite 3.3.7 tree

Some of the major users of SQLite that he's allowed to talk about

Possibly the most widely distributed Tcl extension in the world.

Uses for SQLite

  1. Replacement for client-server RDBMS
  2. Stand-in for enterprise RDBMS during testing and demos
  3. Local cache of enterprise RDBMS data
  4. Persistence of objects, configuration
  5. Complex data structures
  6. ...

Complex Data Structures

Automated undo/redo using triggers.

Application File Format

What if ...

Key concept: SQLite wants to replace fopen()/[open] not Oracle/Oratcl.

Useful example code/utilities

Crossing into barbarian lands now...

Q: What is Full Text Search?
A: In brief: what Google does.
Q: Why is full text search important?
A: Internet search enginges have users spoiled. Modern applications need to support full text search in order to be competitive.
Q: Is it difficult to implement?
A: It is tricky for large data sets to get it right.

Basic approach

  1. tokenize into words
  2. case folding
  3. stemming (convert each word into its root form; porter stemmer very popular stemmer for English; recommended -> recommend; books -> book)
  4. remove stop words
  5. for each word left create posting list per word

OR queries do an intersection. AND queries do a union.

Phrase Queries: naive method: do an AND query, then examine every document in the result set in a second pass and eliminate those that lack the phrase. Very bad performance in pathalogical cases. Instead update posting list to store document:position (multiple occurances included multiple times).

Basic Operations

Keeping the Working Set Small

Full Text Search (version 1) built into SQLite 3.3.8 released Monday

As of SQLite 3.3.8 (released Monday!) full text search support in SQLite. Ricahrd's authorized to announce help from engineers at Google. Later question elicited that roughly half of the FTS code was written by him & Dan, the other half by four engineers [didn't have time to write down their names] from Google. He isn't able/can't comment on their motivations/plans/internal usage. (Obviouslyy won't be replacing their search engine with SQLite.)

Some example usage

CREATE VIRTUAL TABLE email USING fts1(content);

CREATE VIRTUAL TABLE email USING fts1("from" TEXT, "to" TEXT, subject TEXT, body TEXT, tokenize porter);

SELECT rowid, subject FROM email WHERE email MATCH 'wyrick sqlite';

SELECT rowid, subject FROM email WHERE email MATCH $::querytext;

MATCH: when left operand is the name of the table match against any column can specify a particular column $::querytext can specify columns i.e., "from:wyrick"

Built in snippet generator: SELECR rowid, snippet(email) FROM email WHERE email MATCH ...

FTS1 comes standard with Tcl bindings for SQLite (>= 3.3.8).

Potential uses:

Pervasive Full-Text Search (users want it; made easy using Tcl and SQLite).

—Michael A. Cleverly

Comments

Cameron Laird: [ mail | www | link ]

Many thanks, Michael. It's great that you wrote this up, and so promptly.

Wed, 11 Oct 2006, 10:49

Matt Good: [ mail | www | link ]

How does Subversion use SQLite? The Subversion backends are Berkely DB, or their own FSFS format. Trac uses both Subversion and SQLite, but I don't think Subversion has any SQLite support.

Fri, 13 Oct 2006, 21:48

Manuzhai: [ mail | www | link ]

I think the Subversion work is on the merge-tracking branch, where (I think!) an SQLite database is used to keep track of what revisions where merged from what branch to where.

Also, it says in this article that OR does intersection, AND does union. Isn't that the other way around?

Sat, 14 Oct 2006, 07:36

riya: [ mail | www | link ]

hello

http://blog.cleverly.com/permalinks/247.html

Thu, 24 Jan 2019, 04:07

mcafee.com/activate: [ mail | www | link ]

mcafee antivirus is widely used aantivirus helps to detect and neutralize computer virus, the mail worms,

[url=http://usa-mcafee.com/activate]mcafee.com/activate[/url] -the trojan programs,and also helps your system free

of virus and other malware is quite a daily challenge. for more details visit:

Tue, 05 Feb 2019, 03:17

mcafee.com/activate: [ mail | www | link ]

It is all time best protective antivirus and anti-spyware .It is real time protection technology and it is safer

[url=http://www.mcafeecom-activate.services]mcafee.com/activate[/url] - , faster and more complete than ever

before.if want to install it then visit our site.

Tue, 05 Feb 2019, 03:19

norton.com/setup: [ mail | www | link ]

norton internet security is widely used antivirus provides the easiest to use and most intutive protection for your

computer and your mobiles .install [url=http://nortoncom-setup.support]norton.com/setup[/url] - it and forget

about viruses,spyware,root-kits, hackers.for more details visit:

Tue, 05 Feb 2019, 03:20

mcafee activate: [ mail | www | link ]

McAfee Activate! you can start incitation with enter thing code/key at[url=http://mcafee.comactivate.support]mcafee activate[/url] and visit for more nuances.

Sat, 16 Feb 2019, 00:53

www.office.com/setup: [ mail | www | link ]

Endeavor [url=http://officecom--setup.support]www.office.com/setup[/url] which is amazingly easy to present, download and recoup. Usage of it is moreover fundamental

and the customer can pick up capability with the use of it adequately. Online Support&help elective is in like manner open in all application which gives a minute

guideline.

Sat, 16 Feb 2019, 00:54

google flights: [ mail | www | link ]

It is a gathering of client programming, server programming and organizations through this you can make professsional looking records , plots , figurings, reports, and

presentation in fast and accuracy.if you have to present it by then visit our site.[url=http://googleflights.services]google flights[/url]

Sat, 16 Feb 2019, 00:54

roku activation code: [ mail | www | link ]

roku.com/interface this is least troublesome way to deal with stream incitement to your TV. On your terms. With a considerable number of available channels to

investigate for more details.visit: [url=http://roku-activation-code.net]roku activation code[/url] today.

Sat, 16 Feb 2019, 00:55

office.com/setup: [ mail | www | link ]

office setup is the best programming which is broadly utilized in globe .It is a dazzling association that causes you release your best contemplations, complete

things, and stay related on the go.for more subtleties visit: [url=http://office.com-set-up.support]office.com/setup[/url] today.

Sat, 16 Feb 2019, 00:55

www.mcafee.com/activate: [ mail | www | link ]

Present mcafee thing with no issue. We give best foundation advantage. visit:[url=http://mcafeecom-activate.me]www.mcafee.com/activate[/url] today.

Sat, 16 Feb 2019, 00:56

nortoncomsetupp: [ mail | www | link ]

Facing issues with your Norton software, we are here to assist you. If you need assistance we have team of technical executives to assist you. Contact us on our toll free number +1800-378-0714 or you can check our website http://nortoncomsetupp.com

Sat, 27 Apr 2019, 00:58

officecomsetuppp: [ mail | www | link ]

Looking for easy ways to download Office 365, download it at http://officecomsetuppp.com . Need any other assistance, contact us on our toll free number +1800-378-0714 ,we have have our technical executives to assist you.

Sat, 27 Apr 2019, 00:59

mcafeecomactivateee: [ mail | www | link ]

McAfee is one of the reliable tool to protect your PC from virus. To download visit http://mcafeecomactivateee.com. Need any other assistance, you contact us on our toll free number 1800-378-0714

Sat, 27 Apr 2019, 01:00

aol-support: [ mail | www | link ]

Facing issues with your AOL mail, we are here to assist you. We at AOL support have technical executives to assist you.Contact us on our toll free number +1800-378-0714 and get assisted by our technical executives.

Wed, 01 May 2019, 01:06

Office.com/Setup: [ mail | www | link ]

Sign in to enter office setup product key. Know how to benefit, download, install, set in movement, uninstall and reinstall MS office setup.visit http://officecom-officeoffice.com and Get Started by now Office setup.

call us at +1-888-421-9666[tool free]

Thu, 29 Aug 2019, 14:42

williamjames: [ mail | www | link ]

Despite on account of programming security highlights can manage all the security related issues in any case progression is making standard so as programming engineers and automated liable gatherings. Sage Support Number Sage Support Phone Number Sage Tech Support Number Sage Tech Support Phone Number Sage Technical Support Number Sage Technical Support Phone Number

Mon, 16 Sep 2019, 22:41

jackharry: [ mail | www | link ]

For true fans of this thread I will address is a free online!

Thu, 05 Dec 2019, 05:43

ufabet1688: [ mail | www | link ]
Attractive component to content. I simply stumbled upon your website and in accession capital to claim that I acquire in fact enjoyed account your weblog posts. Any way I will be subscribing in your augment and even I achievement you access constantly rapidly. ทางเข้ายูฟ่า168

Sun, 22 Oct 2023, 09:40

Leave a comment

Name:  
Email: (optional)
URL: (optional)

Your comment: