<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Import sqlite3 library
Step1:  Connect to a database file or create an in-memory database
Step2:  SQLite basics
Step3:  Full-Text-Search
<ASSISTANT_TASK:>
Python Code:
import sqlite3
import sys
from glob import glob
Explanation: Import sqlite3 library
End of explanation
conn = sqlite3.connect('/users/mikespears/Desktop/mydb.db') #file-based db
#conn = sqlite3.connect(':memory:') # in-memory db
c = conn.cursor()
Explanation: Connect to a database file or create an in-memory database
End of explanation
c.execute('''DROP TABLE IF EXISTS uoftcoders''') 
c.execute('''CREATE TABLE uoftcoders (date, title, attendance)''')
conn.commit()
c.execute('''INSERT INTO uoftcoders VALUES (?, ?, ?)''', ["March 7, 2019", "Full Text Search with SQLITE", 12])
c.execute('''INSERT INTO uoftcoders VALUES (?, ?, ?)''', ["March 14, 2019", "Software defined radio", 15])
conn.commit()
c.execute('''SELECT * FROM uoftcoders WHERE date = ?''', ["March 7, 2019"])
print(c.fetchall())
c.execute('''SELECT * FROM uoftcoders WHERE attendance > ?''', [10])
print(c.fetchall())
#basic pattern matching with LIKE
c.execute('''SELECT * FROM uoftcoders WHERE date LIKE ?''', ["%march_%"])
print(c.fetchall())
Explanation: SQLite basics: Create a regular (non fts) table, insert and search for values
End of explanation
c.execute('''DROP TABLE IF EXISTS files''')
c.execute('''CREATE VIRTUAL TABLE files USING fts4(filename, text)''')
#c.execute('''CREATE VIRTUAL TABLE files USING fts4(filename, text, tokenize=porter)''')
#c.execute('''CREATE VIRTUAL TABLE files USING fts4(filename, text, tokenize=unicode61)''')
conn.commit()
# Insert all the files in a directory (that contains only plaintext files)
textFileDir = "/users/mikespears/downloads/science/"
allFiles = glob(textFileDir + "*.*")
for path in allFiles:
    with open(path) as file: 
        try:
            text = file.read()
            c.execute('''INSERT INTO files(filename, text) VALUES(?, ?)''', [path, text])
            #print(path)
        except:
            e = sys.exc_info()[0]
            print("Read or insert error: %s (in %s)" % (e, path))
            continue
    
    
conn.commit()
c.execute('''SELECT COUNT(*) from files''')
print(c.fetchall())
#basic token search text column
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ['relativity']):
    print(row)
#implicit 'AND'
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ['special relativity']):
    print(row)
# OR
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ['special OR relativity']):
    print(row)
#search for a phrase
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ['"special relativity"']):
    print(row)
#exclude a token
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ['relativity -special']):
    print(row)
#NEAR operator
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ["relativity NEAR special"]):
    print(row)
#specify a column in the search term
for row in c.execute('''SELECT filename FROM FILES WHERE text MATCH ?''', ['filename:faq']):
    print(row)
for row in c.execute('''SELECT snippet(files, ">>>>", "<<<<", "..."), filename FROM FILES WHERE text MATCH ?''', ['relativity']):
    print ("\n\n--------%s\n\n" % row[1])
    print(row[0])
#get offset
for row in c.execute('''SELECT offsets(FILES) FROM FILES WHERE text MATCH ?''', ['filename:faq']):
    print(row)
#get matchinfo
for row in c.execute('''SELECT matchinfo(FILES) FROM FILES WHERE files MATCH ?''', ['filename:faq']):
    print(row)
#direct access to full-text index
c.execute('''DROP TABLE IF EXISTS files_terms''')
c.execute('''CREATE VIRTUAL TABLE files_terms USING fts4aux(files)''')
conn.commit()
for row in c.execute('''SELECT * from files_terms ORDER BY occurrences DESC'''):
    print(row)
conn.close()
Explanation: Full-Text-Search: Create a table, insert data and search for matches
End of explanation
<END_TASK>