jblite/table.py

   1 # -*- coding: utf-8 -*-
   2
   3 from pprint import pformat
   4
   5
   6 class Record(object):
   7
   8     """Represents a row in a table, plus all data it is a 'parent' of.
   9
  10     Each Record may be linked to multiple Records in child tables.
  11
  12     """
  13
  14     def __init__(self, data=None, children=None):
  15         self.data = data if data is not None else {}
  16         self.children = children if children is not None else {}
  17
  18     def as_dict(self):
  19         """Returns a dictionary representing the values in the record."""
  20         data_d = {}
  21         for key in self.data.keys():
  22             data_d[key] = self.data[key]
  23         children_d = {}
  24         for table, records in self.children.iteritems():
  25             children_d[table] = [record.as_dict() for record in records]
  26         result_d = {}
  27         if len(data_d) > 0:
  28             result_d[u"data"] = data_d
  29         if len(children_d) > 0:
  30             result_d[u"children"] = children_d
  31         return result_d
  32
  33     def find_children(self, *args):
  34         """Returns child records for the given string arguments.
  35
  36         Each argument is used as a key for selecting a group out of
  37         the next generation of child objects.  For example, the args
  38         ["sense", "gloss"] will return a list of all gloss records.
  39
  40         Returns a list of records, or an empty list if none are found.
  41
  42         """
  43         records = [self]
  44         for key in args:
  45             record_lists = [record.children.get(key, []) for record in records]
  46             # Merge the lists into one.
  47             records = reduce(lambda x, y: x + y, record_lists)
  48         return records
  49
  50     def __unicode__(self):
  51         return pformat(self.as_dict())
  52
  53     __repr__ = __unicode__
  54
  55
  56 class Table(object):
  57
  58     """Base class for tables.
  59
  60     Given SQL template queries, provides table create/insert logic and
  61     attaches indices if desired.
  62
  63     create_query and insert_query must be specified in child classes.
  64     index_queries is not required.
  65
  66     create_query and insert_query take a single template argument: the
  67     table name.  Do not put the table name in directly; replace it
  68     with %s.
  69
  70     index_queries are a little more complicated.  They should be
  71     specified like so:
  72
  73       CREATE INDEX %s_XXX ON %s (YYY)
  74
  75     where %s is a placeholder for the table name (note that it's used
  76     twice), and XXX/YYY are replaced as desired.
  77
  78     """
  79
  80     create_query = None
  81     insert_query = None
  82     index_queries = []
  83
  84     def __init__(self, cursor, name):
  85         self.cursor = cursor
  86         self.name = name
  87
  88     def create(self):
  89         """Creates table, plus indices if supplied in class definition."""
  90         query = self._get_create_query()
  91         #print(query)
  92         self.cursor.execute(query)
  93         index_queries = self._get_index_queries()
  94         for query in index_queries:
  95             #print(query)
  96             self.cursor.execute(query)
  97
  98     def insert(self, *args):
  99         """Runs an insert with the specified arguments.
 100
 101         Returns the row id of the insert.  (cursor.lastrowid)
 102
 103         """
 104         query = self._get_insert_query()
 105
 106         try:
 107             self.cursor.execute(query, args)
 108         except:
 109             print("Exception occurred on insert: query=%s, args=%s" %
 110                   (repr(query), repr(args)))
 111             raise
 112         return self.cursor.lastrowid
 113
 114     def insertmany(self, *args):
 115         """Runs a multi-row insert with the specified arguments.
 116
 117         Arguments are treated similarly to the DB-API 2.0 executemany
 118         statement: this function takes a list of argument lists.
 119
 120         There is no return value.
 121
 122         """
 123         query = self._get_insert_query()
 124         try:
 125             self.cursor.executemany(query, *args)
 126         except:
 127             print("Exception occurred on insertmany: query=%s, args=%s" %
 128                   (repr(query), repr(args)))
 129             raise
 130
 131     def _get_create_query(self):
 132         if self.name is None:
 133             raise ValueError(
 134                 "name must be specified in class definition")
 135         return self.create_query % self.name
 136
 137     def _get_insert_query(self):
 138         if self.name is None:
 139             raise ValueError(
 140                 "name must be specified in class definition")
 141         return self.insert_query % self.name
 142
 143     def _get_index_queries(self):
 144         if self.name is None:
 145             raise ValueError(
 146                 "name must be specified in class definition")
 147         if (not (isinstance(self.index_queries, list))
 148           or (len(self.index_queries) == 0)):
 149             return []
 150         else:
 151             # Each query needs to have the table name merged in two
 152             # places.
 153             queries = [q % (self.name, self.name)
 154                        for q in self.index_queries]
 155             return queries
 156
 157     def lookup_by_id(self, id):
 158         """Retrieves the row matching the id.
 159
 160         Returns the matching row, or None if no row was found.
 161
 162         """
 163         query = "SELECT * FROM %s WHERE id = ?" % self.name
 164         self.cursor.execute(query, (id,))
 165         row = self.cursor.fetchone()
 166         return row
 167
 168
 169 class ChildTable(Table):
 170
 171     """Table using a foreign key column for parent-child relationships."""
 172
 173     def lookup_by_fk(self, fk):
 174         """Retrieves all rows with the matching foreign key.
 175
 176         Returns a list of rows.  If no rows are found, an empty list
 177         is returned.
 178
 179         """
 180         query = "SELECT * FROM %s WHERE fk = ?" % self.name
 181         self.cursor.execute(query, (fk,))
 182         rows = self.cursor.fetchall()
 183         return rows
 184
 185
 186 class KeyValueTable(ChildTable):
 187     """General key/value table for one-many relations."""
 188     create_query = ("CREATE TABLE %s "
 189                     "(id INTEGER PRIMARY KEY, fk INTEGER, value TEXT)")
 190     insert_query = "INSERT INTO %s VALUES (NULL, ?, ?)"
 191     index_queries = [
 192         "CREATE INDEX %s_fk ON %s (fk)",
 193         ]