def new(self, path): if self.is_loaded(): self.unload() self.load_failed = False self.start_date = StartDate() config()["path"] = path log().new_database() self.save(contract_path(path, config().basedir))
def new(self, path): """ Create new database """ self.path = path if self.is_loaded(): self.unload() self.start_date = StartDate() config()["path"] = path log().new_database() # create tables according to schema self.conn.executescript(SCHEMA) # save start_date self.conn.execute("insert into meta(key, value) values(?,?)", ("start_date", datetime.strftime(self.start_date.start, '%Y-%m-%d %H:%M:%S'))) self.save()
class Pickle(Database): """A simple storage backend, mainly for testing purposes and to help flesh out the design. It has several problems: * It does not support filtering operations, so it cannot activate and deactivate categories. * Due to an obscure bug in SIP, we need to replace the card type info in fact by a card type id, otherwise we get:: File "/usr/lib/python2.5/copy_reg.py", line 70, in _reduce_ex state = base(self) TypeError: the sip.wrapper type cannot be instantiated or sub-classed * It is wasteful in memory during queries. It would be possible to work around all these limitations, but doing so would taint the design of the rest of the library. """ def __init__(self): self.start_date = None self.categories = [] self.facts = [] self.fact_views = [] self.cards = [] self.fact_views = [] self.load_failed = False def new(self, path): if self.is_loaded(): self.unload() self.load_failed = False self.start_date = StartDate() config()["path"] = path log().new_database() self.save(contract_path(path, config().basedir)) def load(self, path): path = expand_path(path, config().basedir) if self.is_loaded(): unload_database() if not os.path.exists(path): self.load_failed = True raise LoadError try: infile = file(path, 'rb') db = cPickle.load(infile) self.start_date = db[0] self.categories = db[1] self.facts = db[2] self.fact_views = db[3] self.cards = db[4] infile.close() self.load_failed = False except: self.load_failed = True raise InvalidFormatError(stack_trace=True) # Work around a sip bug: don't store card types, but their ids. for f in self.facts: f.card_type = card_type_by_id(f.card_type) # TODO: This was to remove database inconsistencies. Still needed? #for c in self.categories: # self.remove_category_if_unused(c) config()["path"] = contract_path(path, config().basedir) log().loaded_database() for f in component_manager.get_all("function_hook", "after_load"): f.run() def save(self, path): path = expand_path(path, config().basedir) # Work around a sip bug: don't store card types, but their ids. for f in self.facts: f.card_type = f.card_type.id # Don't erase a database which failed to load. if self.load_failed == True: return try: # Write to a backup file first, as shutting down Windows can # interrupt the dump command and corrupt the database. outfile = file(path + "~", 'wb') db = [self.start_date, self.categories, self.facts, self.fact_views, self.cards] cPickle.dump(db, outfile) outfile.close() shutil.move(path + "~", path) # Should be atomic. except: print traceback_string() raise SaveError() config()["path"] = contract_path(path, config().basedir) # Work around sip bug again. for f in self.facts: f.card_type = card_type_by_id(f.card_type) def unload(self): self.save(config()["path"]) log().saved_database() self.start_date = None self.categories = [] self.facts = [] self.fact_views = [] self.cards = [] scheduler().clear_queue() return True def backup(self): # TODO: implement return if number_of_items() == 0 or get_config("backups_to_keep") == 0: return backupdir = os.path.join(basedir, "backups") # Export to XML. Create only a single file per day. db_name = os.path.basename(config["path"])[:-4] filename = db_name + "-" +\ datetime.date.today().strftime("%Y%m%d") + ".xml" filename = os.path.join(backupdir, filename) export_XML(filename, get_category_names(), reset_learning_data=False) # Compress the file. f = bz2.BZ2File(filename + ".bz2", 'wb', compresslevel=5) for l in file(filename): f.write(l) f.close() os.remove(filename) # Only keep the last logs. if get_config("backups_to_keep") < 0: return files = [f for f in os.listdir(backupdir) if f.startswith(db_name + "-")] files.sort() if len(files) > get_config("backups_to_keep"): os.remove(os.path.join(backupdir, files[0])) def is_loaded(self): return len(self.facts) != 0 def set_start_date(self, start_date_obj): self.start_date = start_date_obj def days_since_start(self): return self.start_date.days_since_start() def add_category(self, category): raise NotImplementedError def modify_category(self, modified_category): raise NotImplementedError def delete_category(self, category): raise NotImplementedError # TODO: benchmark this and see if we need a dictionary category_by_name. def get_or_create_category_with_name(self, name): for category in self.categories: if category.name == name: return category category = Category(name) self.categories.append(category) return category # TODO: we used to check on name here. OK to check on instance? def remove_category_if_unused(self, cat): for f in self.facts: if cat in f.cat: break else: self.categories.remove(cat) del cat def add_fact(self, fact): self.load_failed = False self.facts.append(fact) def update_fact(self, fact): return # Should happen automatically. def add_fact_view(self, fact_view, card): # TODO: add link with card self.load_failed = False self.fact_views.append(fact_view) def update_fact_view(self, fact_view): return # Should happen automatically. def add_card(self, card): self.load_failed = False self.cards.append(card) log().new_card(card) def update_card(self, card): return # Should happen automatically. def delete_fact_and_related_data(self, fact): old_cat = fact.cat for c in self.cards: if c.fact == fact: self.cards.remove(c) try: self.fact_views.remove(c.fact_view) except: pass # Its fact view is a card type fact view one. log().deleted_card(c) self.facts.remove(fact) scheduler().rebuild_queue() for cat in old_cat: self.remove_category_if_unused(cat) def cards_from_fact(self, fact): return [c for c in self.cards if c.fact == fact] def has_fact_with_data(self, fact_data): for f in self.facts: if f.data == fact_data: return True return False def duplicates_for_fact(self, fact): duplicates = [] for f in self.facts: if f.card_type == fact.card_type and f != fact: for field in fact.card_type.unique_fields: if f[field] == fact[field]: duplicates.append(f) break return duplicates def category_names(self): return (c.name for c in self.categories) def card_count(self): return len(self.cards) def non_memorised_count(self): return sum(1 for c in self.cards if (c.grade < 2)) def scheduled_count(self, days=0): """ Number of cards scheduled within 'days' days.""" days_since_start = self.days_since_start() return sum(1 for c in self.cards if (c.grade >= 2) and \ (days_since_start >= c.next_rep - days)) def active_count(self): """Number of cards in an active category. (Remember we don't support unactive categories in this database.) """ return len(self.cards) def average_easiness(self): if len(self.cards) == 0: return 2.5 else: return sum(c.easiness for c in self.cards) / len(self.cards) def set_filter(self, filter): print "SQL filtering not implemented in pickle database." # Note that in the SQL version, the following queries should use the # filter from above. def list_to_generator(self, list): if list == None: raise StopIteration for x in list: yield x def cards_due_for_ret_rep(self, sort_key=""): days_since_start = self.start_date.days_since_start() cards = [c for c in self.cards if c.grade >= 2 and \ days_since_start >= c.next_rep] if sort_key: cards.sort(key=lambda c : getattr(c, sort_key)) return self.list_to_generator(cards) def cards_due_for_final_review(self, grade, sort_key=""): cards = [c for c in self.cards if c.grade == grade and c.lapses > 0] if sort_key: cards.sort(key=lambda c : getattr(c, sort_key)) return self.list_to_generator(cards) def cards_new_memorising(self, grade, sort_key=""): cards = [c for c in self.cards if c.grade == grade and c.lapses == 0 \ and c.unseen == False] if sort_key: cards.sort(key=lambda c : getattr(c, sort_key)) return self.list_to_generator(cards) def cards_unseen(self, sort_key=""): cards = [c for c in self.cards if c.unseen == True] if sort_key: cards.sort(key=lambda c : getattr(c, sort_key)) return self.list_to_generator(cards) def cards_learn_ahead(self, sort_key=""): days_since_start = self.start_date.days_since_start() cards = [c for c in self.cards if c.grade >= 2 and \ days_since_start < c.next_rep] if sort_key: cards.sort(key=lambda c : getattr(c, sort_key)) return self.list_to_generator(cards)
class Sqlite(Database): """Sqlite database backend class.""" def __init__(self): self._connection = None self.path = None self.start_date = None self.load_failed = False self.start_date = None self.filter = "" @property def conn(self): """Connect to the database. Lazy.""" if not self._connection: self._connection = sqlite.connect(self.path, detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES) self._connection.row_factory = sqlite.Row return self._connection def new(self, path): """ Create new database """ self.path = path if self.is_loaded(): self.unload() self.start_date = StartDate() config()["path"] = path log().new_database() # create tables according to schema self.conn.executescript(SCHEMA) # save start_date self.conn.execute("insert into meta(key, value) values(?,?)", ("start_date", datetime.strftime(self.start_date.start, '%Y-%m-%d %H:%M:%S'))) self.save() def save(self, path=None): """Commit changes.""" # Saving to another file not implemented if path and path != self.path: raise NotImplementedError self.conn.commit() def backup(self): """Backup database. Not Implemented.""" # FIXME: wait for pickle implementation, then implement pass def load(self, fname): """Load database from file.""" # Unload opened database if exists self.unload() self.path = expand_path(fname, config().basedir) try: res = self.conn.execute("select value from meta where key=?", ("start_date", )).fetchone() self.load_failed = False self.set_start_date(StartDate(datetime.strptime(res["value"], "%Y-%m-%d %H:%M:%S"))) self.load_failed = False except sqlite.OperationalError: self.load_failed = True def unload(self): """Commit changes and close connection to the database.""" if self._connection: self._connection.commit() self._connection.close() self._connection = None self.load_failed = False def is_loaded(self): """Is connection set?""" return bool(self._connection) # Get objects from the database def get_view(self, view_id): """Get view object by id.""" res = self.conn.execute("select name from views where id=?", (view_id, )).fetchone() fact_view = FactView(view_id, res["name"]) # Find fact view in registered card_types and copy # *fields attributes from it for card_type in card_types(): for view in card_type.fact_views: if view.name == res["name"]: fact_view.q_fields = view.q_fields fact_view.a_fields = view.a_fields fact_view.required_fields = view.required_fields return fact_view raise RuntimeError("Wrong view(id=%d) found in the database" % view_id) def get_fact(self, guid=None, fact_id=None): """Get fact object by guid.""" # Get fact by id or guid if guid: fact = self.conn.execute("select * from facts where guid=?", (guid, )).fetchone() elif fact_id: fact = self.conn.execute("select * from facts where id=?", (fact_id, )).fetchone() else: raise RuntimeError("get_fact: No guid nor fact_id provided") # Get fact data by fact id cursor = self.conn.execute("""select * from factdata where fact_id=?""", (fact["id"], )) data = dict([(item["key"], item["value"]) for item in cursor]) categories = [Category(cat["name"]) for cat in self.conn.execute("""select cat.name from categories as cat, fact_categories as f_cat where f_cat.category_id=cat.id and f_cat.fact_id=?""", (fact["id"], ))] card_type = card_type_by_id(str(fact["facttype_id"])) return Fact(data, card_type, categories, uid=fact['guid'], added=fact['ctime']) @staticmethod def get_card(fact, view, sql_res): """Get card object from fact, view and query result.""" card = Card(fact, view) for attr in ("id", "grade", "lapses", "easiness", "acq_reps", "acq_reps_since_lapse", "last_rep", "next_rep", "unseen"): setattr(card, attr, sql_res[attr]) return card # Start date. def set_start_date(self, start_date_obj): """Setter for start_date attribute.""" self.start_date = start_date_obj def days_since_start(self): """Return amount of days since start_date.""" return self.start_date.days_since_start() # Adding, modifying and deleting categories, facts and cards. def add_category(self, category): """Add new category.""" self.conn.execute("insert into categories(name) values(?)", (category.name, )) def delete_category(self, category): """Delete category.""" self.conn.execute("delete from categories were name=?", (category.name, )) def get_or_create_category_with_name(self, name): """Try to get category from the database create if not found. """ category = self.conn.execute("""select * from categories where name=?""", (name, )).fetchone() if category: return Category(category["name"]) self.conn.execute("""insert into categories(name) values(?)""", (name, )) self.conn.commit() return Category(name) def add_fact(self, fact): """Add new fact.""" # Add record into fact types if needed if self.conn.execute("""select count() from facttypes where name=?""", (fact.card_type.name, )).fetchone()[0] == 0: self.conn.execute("""insert into facttypes(id, name) values(?,?)""", (fact.card_type.id, fact.card_type.name, )) # Add fact to facts and factdata tables fact_id = self.conn.execute("""insert into facts(guid, facttype_id, ctime) values(?,?,?)""", (fact.uid, fact.card_type.id, fact.added)).lastrowid self.conn.executemany("""insert into factdata(fact_id,key,value) values(?,?,?)""", ((fact_id, key, value) for key, value in fact.data.items())) # Link fact to its categories for cat in fact.cat: cat_id = self.conn.execute("""select id from categories where name=?""", (cat.name, )).fetchone()[0] self.conn.execute("""insert into fact_categories(category_id, fact_id) values(?,?)""", (cat_id, fact_id)) self.conn.commit() def update_fact(self, fact): """Update fact.""" # update factdata self.conn.executemany("""update factdata set value=? where fact_id=? and key=?""", ((value, fact.uid, key) for key, value in fact.data.items())) # update timestamp self.conn.execute("update facts set mtime=? where guid=?", (datetime.now(), fact.uid)) def add_fact_view(self, fact_view, card): """Add new view.""" fact = self.get_fact(card.fact.uid) self.conn.execute("""insert into views(id, facttype_id, name) values(?,?,?)""", (fact_view.id, fact.card_type.id, fact_view.name)) def update_fact_view(self, fact_view): """Update view.""" self.conn.execute("update views set name=? where id=?", (fact_view.name, fact_view.id)) def add_card(self, card): """Add new card and its fact_view.""" self.conn.execute("""insert into reviewstats(id, fact_id, view_id, grade, lapses, easiness, acq_reps, acq_reps_since_lapse, last_rep, next_rep, unseen) values(?,?,?,?,?,?,?,?,?,?,?)""", (card.id, card.fact.uid, card.fact_view.id, card.grade, card.lapses, card.easiness, card.acq_reps, card.acq_reps_since_lapse, card.last_rep, card.next_rep, card.unseen)) # Add view if doesn't exist if not self.conn.execute("select count() from views where id=?", (card.fact_view.id, )).fetchone()[0]: self.add_fact_view(card.fact_view, card) log().new_card(card) def update_card(self, card): """Update card.""" self.conn.execute("""update reviewstats set grade=?, easiness=?, lapses = ?, acq_reps=?, acq_reps_since_lapse=?, last_rep=?, next_rep=?, unseen=? where id=?""", (card.grade, card.easiness, card.lapses, card.acq_reps, card.acq_reps_since_lapse, card.last_rep, card.next_rep, card.unseen, card.id)) def cards_from_fact(self, fact): """Generate cards related to fact.""" cursor = self.conn.execute("""select * from reviewstats where fact_id=?""", (fact.uid, )) return (self.get_card(fact, self.get_view(res['view_id']), res) for res in cursor) def delete_fact_and_related_data(self, fact): """Delete fact and all relations to it.""" # Get fact id by uid fact_id = self.conn.execute("select id from facts where guid=?", (fact.uid, )).fetchone()["id"] # Delete related records from other tables (linked by id) for table in ("factdata", "fact_categories"): self.conn.execute("delete from %s where fact_id=?" % table, (fact_id, )) # Delete from reviewstats (linked by uid) self.conn.execute("delete from reviewstats where fact_id=?", (fact.uid, )) # Delete record from facts table self.conn.execute("delete from facts were guid=?", (fact.uid, )) # Queries. def category_names(self): """Generate categories' names.""" return (res[0] for res in self.conn.execute("select name from categories")) def has_fact_with_data(self, fact_data): """Check if there is already the fact with fact_data.""" fact_ids = set() for key, value in fact_data.items(): item_fact_ids = set() # if key and value from fact_data isn't in the database # we don't have such a fact if not self.conn.execute("""select count() from factdata where key=? and value=?""", (key, value)).fetchone()[0]: return False for res in self.conn.execute("""select fact_id from factdata where key=? and value=?""", (key, value)): item_fact_ids.add(res["fact_id"]) # save fact_ids found on the first run if not fact_ids: fact_ids = item_fact_ids else: # assign common ids to fact_ids fact_ids = fact_ids.intersection(item_fact_ids) # if there is no common fact_ids we don't have # fact with fact_data if not fact_ids: return False return True def duplicates_for_fact(self, fact): """Return list of facts with the same key.""" # find duplicate fact data duplicates = [] for value in fact.data.values(): for res in self.conn.execute("""select * from factdata where value=?""", (value, )): fact_db = self.get_fact(fact_id=res['fact_id']) # Filter out facts from other categories for cat in fact_db.cat: if cat in fact.cat: duplicates.append(fact_db) break return duplicates def fact_count(self): """Get total of facts.""" return self.conn.execute("select count() from facts").fetchone()[0] def card_count(self): """Get total number of cards.""" return self.conn.execute("select count() from reviewstats").\ fetchone()[0] def non_memorised_count(self): """Get number of non memorised cards.""" return self.conn.execute("""select count() from reviewstats where grade < 2""").fetchone()[0] def scheduled_count(self, days=0): """Get number of cards scheduled within 'days' days.""" count = self.conn.execute("""select count() from reviewstats where grade >=2 and ? >= next_rep - ?""", (self.days_since_start(), days)).fetchone()[0] return count def active_count(self): """Get number of cards in an active category.""" return self.conn.execute("""select count() from fact_categories, categories where category_id = categories.id and categories.enabled=1""").fetchone()[0] def average_easiness(self): """Get average easiness of cards.""" average = self.conn.execute("""select sum(easiness)/count() from reviewstats""").fetchone()[0] if average: return average else: return 2.5 # Filter is a SQL filter, used e.g. to filter out inactive categories. def set_filter(self, attr): """Set filter for category attribute. See below methods for details """ self.filter = attr def cards_due_for_ret_rep(self, sort_key="id"): """Generate cards due for repetition.""" if sort_key == "interval": sort_key = "next_rep - last_rep" return(self.get_card(self.get_fact(res["fact_id"]), self.get_view(res["view_id"]), res) for res in self.conn.execute("""select * from reviewstats where grade >=2 and ? >= next_rep order by %s""" % sort_key, (self.start_date.days_since_start(), ))) def cards_due_for_final_review(self, grade, sort_key="random()"): """Generate cards for final review.""" return (self.get_card(self.get_fact(res["fact_id"]), self.get_view(res["view_id"]), res) for res in self.conn.execute("""select * from reviewstats where grade = ? and lapses > 0 order by %s""" % sort_key, (grade, ))) def cards_new_memorising(self, grade, sort_key="random()"): """Generate cards for new memorising (lapses=0 and unseen=0).""" return (self.get_card(self.get_fact(res["fact_id"]), self.get_view(res["view_id"]), res) for res in self.conn.execute("""select * from reviewstats where grade = ? and lapses = 0 and unseen = 0 order by %s""" % sort_key, (grade, ))) def cards_unseen(self, sort_key="id", randomise=False): """Generate unseen cards.""" if randomise: sort_key = "random()" return (self.get_card(self.get_fact(res["fact_id"]), self.get_view(res["view_id"]), res) for res in self.conn.execute("""select * from reviewstats where unseen = 1 order by %s""" % sort_key)) def cards_learn_ahead(self, sort_key="id"): """Generate cards for learning ahead.""" return(self.get_card(self.get_fact(res["fact_id"]), self.get_view(res["view_id"]), res) for res in self.conn.execute("""select * from reviewstats where grade >=2 and ? < next_rep order by %s""" % sort_key, (self.start_date.days_since_start(), )))