src/cards.py

   1 #!/usr/bin/env python
   2 # -*- coding: iso-8859-2 -*-
   3 #
   4 # Copyright (C) 2007 Adam Folmert <afolmert@gmail.com>
   5 #
   6 # This file is free software; you can redistribute it and/or modify
   7 # it under the terms of the GNU General Public License as published by
   8 # the Free Software Foundation; either version 2, or (at your option)
   9 # any later version.
  10 #
  11 # This file is distributed in the hope that it will be useful,
  12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14 # GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License
  17 # along with this program; if not, write to the Free Software
  18 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
  19 #
  20 #
  21 #
  22 """This is module for managing cards: storage, management, repetition algorithms,
  23 scheduling etc."""
  24
  25 import release
  26 import sqlite3
  27 from config import gui_config as config
  28 import sys
  29 from utils import nvl, log
  30
  31 __version__ = release.version
  32
  33
  34 # TODO write Qt Model  using my cards object (cached?)
  35 # mode lcaching result ?
  36 # create index - create for first record
  37   # for nth record in database
  38   # what about filters and sorting
  39   # refresh
  40   # get index get next basing on current sorting criteria
  41
  42   # I think i will have to read all and refresh on demand ?
  43   # display name and id and the rest will be read on demand
  44   #
  45   # how the model works
  46   # how many doe it reads?
  47   #
  48   # what if I had 1000 in database
  49   # i would read in chunks
  50   # read 1000 and if asked then fetch more
  51   #
  52   # first try to do one without model just ismple load to list
  53   # see how qt model works !!!
  54
  55
  56
  57
  58
  59 # Each card will have unique id generated from database
  60 #
  61
  62 class Card(object):
  63     """Basic in-memory card object. """
  64     def __init__(self, id=None, question='', answer='', question_hint='', answer_hint='', score=None):
  65         self.id            = int(id) if id is not None else None
  66         self.question      = str(question)
  67         self.answer        = str(answer)
  68         self.question_hint = str(question_hint)
  69         self.answer_hint   = str(answer_hint)
  70         self.score         = str(score)
  71
  72
  73     def __eq__(self, other):
  74         return self.id         == other.id \
  75         and self.question      == other.question \
  76         and self.answer        == other.answer \
  77         and self.question_hint == other.question_hint \
  78         and self.answer_hint   == other.answer_hint \
  79         and self.score         == other.score
  80
  81     def __str__(self):
  82         return str((self.id,
  83                   self.question,
  84                   self.answer,
  85                   self.question_hint,
  86                   self.answer_hint,
  87                   self.score))
  88
  89
  90     def clear(self):
  91         self.id            = None
  92         self.question      = ''
  93         self.answer        = ''
  94         self.question_hint = ''
  95         self.answer_hint   = ''
  96         self.score         = None
  97
  98     def isEmpty(self):
  99         return self.question == '' and self.answer == ''
 100
 101
 102
 103 # Cards will be stored in sqlite database
 104 # Once opened, each operation will make a commit on the database, so in case of
 105 # a crash, the data is always safe.
 106 #
 107 #
 108 class Cards(object):
 109     """Cards storage. This is an interface for sqlite database keeping cards."""
 110
 111     class CardsError(Exception) : pass
 112     class CannotOpenDatabaseError(CardsError) : pass
 113     class DataNotFoundError(CardsError) : pass
 114
 115
 116     def __init__(self):
 117         self.db_path = None
 118         self.db = None
 119
 120     def open(self, dbpath):
 121         """Opens or creates Card database. Use :memory: to open database in memory. """
 122         # close if currently open
 123         if self.db:
 124             self.db.close()
 125         # try to open
 126         try:
 127             self.db_path = dbpath
 128             self.db = sqlite3.connect(dbpath)
 129             self.initDb()
 130         except:
 131             log(sys.exc_info())
 132             self.db_path = None
 133             self.db = None
 134             raise Cards.CannotOpenDatabaseError, "Cannot open database: %s" % dbpath
 135
 136     def close(self):
 137         if self.db:
 138             self.db.close()
 139         self.db_path = None
 140         self.db = None
 141
 142
 143     def isOpen(self):
 144         return self.db is not None
 145
 146     def checkDbOpen(self):
 147         assert self.db is not None, "Database not open."
 148
 149     def initDb(self):
 150         self.checkDbOpen()
 151         cur = self.db.cursor()
 152         # check if tables exist
 153         try:
 154             result = cur.execute('SELECT VERSION FROM TVERSION')
 155             row = result.fetchone()
 156             assert row[0] == config.DB_VERSION, "Unknown database format."
 157         except:
 158             # if not supported version then we recreate the database
 159             cur.execute(r'''CREATE TABLE TCARDS (
 160                               ID             INTEGER PRIMARY KEY,
 161                               QUESTION       TEXT,
 162                               ANSWER         TEXT,
 163                               QUESTION_HINT  TEXT,
 164                               ANSWER_HINT    TEXT,
 165                               SCORE          NUMERIC
 166                               )
 167                           ''')
 168             cur.execute(r'''CREATE TABLE TVERSION (
 169                               VERSION        TEXT
 170                             )''' )
 171             cur.execute('INSERT INTO TVERSION ( VERSION ) VALUES ( ? ) ', (config.DB_VERSION,))
 172             self.db.commit()
 173             cur.close()
 174
 175
 176     def commit(self):
 177         self.checkDbOpen()
 178         self.db.commit()
 179
 180
 181     def addCard(self, card, commit=True):
 182         """Adds a card object to database and returns it's id object."""
 183         self.checkDbOpen()
 184         cur = self.db.cursor()
 185         cur.execute(r'''INSERT INTO TCARDS ( QUESTION, ANSWER, QUESTION_HINT, ANSWER_HINT, SCORE )
 186                           VALUES ( ? , ? , ? , ?, ? ) ''', \
 187                           (card.question,
 188                            card.answer,
 189                            card.question_hint,
 190                            card.answer_hint,
 191                            card.score))
 192         # TODO how to retrieve information about last without running
 193         # additional query ?
 194         # TODO remove the query if lastrowid is ok
 195         # move it to tests :
 196         # add a record, check max(ID) and compare if it's the id
 197         # returned actually by it's
 198         lastrowid = cur.lastrowid
 199         result = cur.execute('SELECT MAX(ID) FROM TCARDS').fetchone()[0]
 200         assert lastrowid == result, "Internal error: Lastrowid does not return MaxID!"
 201         cur.close()
 202         if commit:
 203             self.db.commit()
 204         return result
 205
 206
 207     def getCard(self, card_id):
 208         """Retrieves a card from database given it's id or None if it does not exist."""
 209         # TODO
 210         self.checkDbOpen()
 211         cur = self.db.cursor()
 212         rows = cur.execute(r'''SELECT ID, QUESTION, ANSWER, QUESTION_HINT, ANSWER_HINT, SCORE
 213                                  FROM TCARDS
 214                                 WHERE ID = ?
 215                             ''', (card_id,))
 216         row = rows.fetchone()
 217         if row:
 218             card = Card(*row)
 219             cur.close()
 220             return card
 221         else:
 222             raise Cards.DataNotFoundError, "Card not found = %d " % card_id
 223
 224     def getCardHeaders(self, sqlwhere='', minrow=None, maxrow=None):
 225         """Returns card ids using sqlwhere and minrow, maxrow range
 226         Params: minrow and maxrows are both counted from 0.
 227         Params: minrow is inclusive and maxrows is exclusive - this is similar to
 228         how range works:
 229         e.g.  getCardHeaders('', 1, 3) will return rows: 1 and 2
 230         """
 231         # using sqlite-only LIMIT clause for getting the result
 232         # from http://sqlite.org/lang_select.html:
 233         # "The LIMIT clause places an upper bound on the number of rows
 234         # returned in the result. A negative LIMIT indicates no upper bound.
 235         # The optional OFFSET following LIMIT specifies how many rows to skip
 236         # at the beginning of the result set. In a compound query, the LIMIT
 237         # clause may only appear on the final SELECT statement. The limit is
 238         # applied to the entire query not to the individual SELECT statement to
 239         # which it is attached."
 240         # in other words:
 241         # it firsts skips OFFSET records and then the rest is limited to max
 242         # LIMIT records
 243         self.checkDbOpen()
 244         cur = self.db.cursor()
 245         if sqlwhere.strip():
 246             sqlwhere = 'WHERE ' + sqlwhere
 247         sqllimit = ''
 248         if minrow is not None or maxrow is not None:
 249             if minrow is not None and maxrow is not None:
 250                 assert minrow >= 0 and maxrow >= 0 and maxrow >= minrow, \
 251                     "Invalid minrow %s maxrow %s params" % (str(minrow), str(maxrow))
 252             maxrow = nvl(maxrow, -1)
 253             minrow = nvl(minrow, 0)
 254             limit = max(maxrow - minrow, -1) # if maxrow is -1 then we want all anyway
 255             offset = max(minrow, 0)
 256             sqllimit = 'LIMIT %d OFFSET %d' % (limit, offset)
 257         query = r'''SELECT ID, QUESTION FROM TCARDS %s %s''' % (sqlwhere, sqllimit)
 258         rows = cur.execute(query)
 259         result = rows.fetchall()
 260         cur.close()
 261         return result
 262
 263
 264     def existsCard(self, card_id):
 265         """Returns True if given card_id exists in database."""
 266         self.checkDbOpen()
 267         cur = self.db.cursor()
 268         rows = cur.execute('SELECT ID FROM TCARDS WHERE ID = ?', (card_id,))
 269         row = rows.fetchone()
 270         exists = row is not None
 271         cur.close()
 272         return exists
 273
 274
 275     def deleteCard(self, card_id):
 276         """Deletes a card from database given it's id"""
 277         self.checkDbOpen()
 278         cur = self.db.cursor()
 279
 280         cur.execute(r'''DELETE FROM TCARDS WHERE ID = ? ''', (card_id,))
 281         assert cur.rowcount == 1, "Problem when updating card = %s" % card_id
 282         cur.close()
 283         self.db.commit()
 284
 285
 286     def deleteAllCards(self):
 287         """Deletes all cards from database"""
 288         self.checkDbOpen()
 289         cur = self.db.cursor()
 290
 291         cur.execute(r'''DELETE FROM TCARDS''')
 292         cur.close()
 293         self.db.commit()
 294
 295
 296
 297     def updateCard(self, card):
 298         """Updates a card in database using it's id and other fields. """
 299         self.checkDbOpen()
 300         cur = self.db.cursor()
 301         cur.execute(r'''UPDATE TCARDS
 302                           SET QUESTION    =  ?
 303                           , ANSWER        =  ?
 304                           , QUESTION_HINT =  ?
 305                           , ANSWER_HINT   =  ?
 306                           , SCORE         =  ?
 307                           WHERE ID        =  ?
 308                     ''', (card.question,
 309                           card.answer,
 310                           card.question_hint,
 311                           card.answer_hint,
 312                           card.score,
 313                           card.id))
 314         assert cur.rowcount == 1, "Problem when updating card %s" % card.id
 315         cur.close()
 316         self.db.commit()
 317
 318
 319     def getCardsCount(self):
 320         """Returns number of cards in the database."""
 321         self.checkDbOpen()
 322         cur = self.db.cursor()
 323
 324         rows = cur.execute('''SELECT COUNT(*) FROM TCARDS''')
 325         result = rows.fetchone()[0]
 326         cur.close()
 327         return result
 328
 329
 330     def logCards(self, sqlwhere='', max=None):
 331         """Helper function for logging cards with given sqlwhere condition."""
 332         self.checkDbOpen()
 333         cur = self.db.cursor()
 334         if sqlwhere.strip() != '':
 335             sqlwhere = 'WHERE %s' % sqlwhere
 336         rows = cur.execute(r'''SELECT ID, QUESTION, ANSWER, QUESTION_HINT, ANSWER_HINT, SCORE
 337                               FROM TCARDS %s''' % sqlwhere)
 338         try:
 339             i = 0
 340             row = -1
 341             while row is not None and (max is None or i < max):
 342                 row = rows.fetchone()
 343                 if row:
 344                     log('id:%-10s q:%-10s a:%-10s qh:%-10s ah:%-10s sc:%-10s' % row)
 345                 i += 1
 346         except:
 347             raise
 348         cur.close()