import sqlite

class Indexer(object):
    test_statement = 'select * from fulltext limit 1'
    create_statements = (
        "create table fulltext (id text not null, data text not null)",
        "create unique index idxid on fulltext (id)",
        "create index idxdata on fulltext (data)",
    )
    insert_statement = "insert into fulltext values ('%(id)s', '%(data)s')"
    update_statement = "update fulltext set data='%(data)s' where id='%(id)s'"
    search_statement = ("select id from fulltext where data like "
                        "'%%%(query)s%%'")

    def __init__(self, dbpath):
        self.dbpath = dbpath
        self.connection = self._connect()

    def index(self, id, data):
        cursor = self.connection.cursor()
        idata = {'id': self._escape(id), 'data': self._escape(data)}
        try:
            cursor.execute(self.insert_statement % idata)
        except sqlite.DatabaseError, e:
            cursor.execute(self.update_statement % idata)
        else:
            self.connection.commit()
            return 'data has been inserted'
        self.connection.commit()
        return 'data has been updated'

    def search(self, query):
        cursor = self.connection.cursor()
        cursor.execute(self.search_statement % {'query': self._escape(query)})
        def i():
            while 1:
                res = cursor.fetchone()
                if res is None:
                    break
                yield res[0]
        return i()

    def _connect(self):
        conn = sqlite.connect(self.dbpath)
        cursor = conn.cursor()
        try:
            cursor.execute(self.test_statement)
        except sqlite.DatabaseError:
            for s in self.create_statements:
                cursor.execute(s)
            conn.commit()
        return conn

    def _escape(self, data):
        return data.encode('UTF-8').replace('\r', ' ').replace('\n', ' '
                ).replace("'", "''")