Package csb :: Package io :: Module tsv

[frames] | no frames]

# Source Code for Module csb.io.tsv

       1  """
       2  Read, query and update textual tables via flexible SQL interface.
       3
       4  L{Table}s can be created and populated with data from scratch, built from TSV
       5  files, 2D lists or other tables. Once the data is loaded in memory, each
       6  storage operation on the table object is delegated via bridge to an SQL
       7  storage backend (by default this is SQLite). However the table uses the backend
       8  only as a temp storage to ensure maximum portability of the data. Tables can be
       9  stored persistently as text (TSV) files and then loaded back in memory when
      10  needed.
      11
      12  These Tables can be queried and updated in a vast number of ways; each query
      13  returns a new L{Table}:
      14
      15      1. Using slice expressions. The general form of a slice expression is
      16      C{[rows, columns]}, where C{rows} can be:
      17
      18
      19          - a row index, 0-based, e.g. C{5}
      20          - a tuple of row indices, e.g. C{(1, 3, 6)}
      21          - a standard Python slice, e.g. C{1:3} or C{:5} or C{:}
      22          - omitted (means: all rows)
      23
      24      and C{columns} can be:
      25
      26          - a column index, 0-based, e.g. C{5}
      27          - a tuple of columns indices, 0-based
      28          - a column name, e.g. C{'TmScore'}
      29          - a tuple of column names, e.g. C{('ID', 'TmScore')}
      30          - a standard Python slice using column indices
      31          - a slice using column names, e.g. C{'ID':'TM'} or C{:'TM'} or C{:}
      32          - omitted (means: all columns)
      33
      34      2. Using query expressions, for example:
      35
      36
      37      >>> table.where('ID').between(1, 5).select('TmScore', 'RMSD')
      38      Table ('TmScore', 'RMSD')
      39
      40      >>> table.where('ID').between(1, 5).update('RMSD', 0.2)
      41      Table (the same table)
      42
      43      3. With SQL queries:
      44
      45
      46      >>> t.query(r'''SELECT  ColumnB * ColumnA AS ComputedValue
      47                      FROM    {0.name}
      48                      WHERE   ColumnC IN ({1}, {1})'''.format(t, Predicate.PH),
      49                  [12, 55])
      50      iterable
      51
      52  The data contained in a Table can be extracted in several ways:
      53
      54      - if you need a single (scalar) value -- with the C{table[row, column]}
      55      indexing expression or with the dedicated C{table.scalar(row, column)} method.
      56      - by treating the table as an iterator; each cycle will then yield a L{DataRow}
      57      object
      58      - with text (TSV) serialization: simply call C{table.dump(file)}.
      59
      60  See L{Table} for full API details.
      61  """
      62
      63  try:
      64      import __builtin__ as builtins
      65  except ImportError:
      66      import builtins
      67
      68  import os
      69
      70  import csb.io
      71  import csb.core
      72
      73  from abc import ABCMeta, abstractmethod, abstractproperty
      74
      75
      76 -class RepositoryImp(object):
      77      """
      78      Abstract SQL backend interface. Defines a number of platform-specific
      79      operations, that each concrete backend implementor must provide.
      80      """
      81
      82      __metaclass__ = ABCMeta
      83
      84 -    def __init__(self, tablename):
      85          self._table = tablename
      86
      87      @abstractproperty
      88 -    def pk(self):
      89          pass
      90
      91      @property
      92 -    def table(self):
      93          """
      94          Table name
      95          @rtype: str
      96          """
      97          return self._table
      98
      99 -    def query(self, sql, params=None):
     100          """
     101          Execute a native SQL query against the backend, as-is.
     102
     103          @param sql: SQL query
     104          @type sql: str
     105          @param params: query bound parameters, if any
     106          @type params: tuple
     107
     108          @return: data reader (2D iterable)
     109          """
     110          raise NotImplementedError()
     111
     112      @abstractmethod
     113 -    def count(self):
     114          """
     115          Count the number of rows in the table.
     116
     117          @rtype: int
     118          """
     119          pass
     120
     121      @abstractmethod
     122 -    def execute(self, expression):
     123          """
     124          Perform a select operation given L{expression}.
     125
     126          @type expression: L{Expression}
     127          @return: data reader (2D iterable)
     128          """
     129          pass
     130
     131      @abstractmethod
     132 -    def update(self, expression):
     133          """
     134          Perform an update operation given L{expression}.
     135
     136          @type expression: L{Expression}
     137          @return: void
     138          """
     139          pass
     140
     141      @abstractmethod
     142 -    def insert(self, row):
     143          """
     144          Insert a new tuple in the table.
     145
     146          @type row: tuple
     147          @return: void
     148          """
     149          pass
     150
     151      @abstractmethod
     152 -    def create(self, metadata):
     153          """
     154          Create a table given L{metadata}.
     155
     156          @type metadata: tuple of L{ColumnInfo}
     157          @return: void
     158          """
     159          pass
     160
     161      @abstractmethod
     162 -    def close(self):
     163          """
     164          Perform cleanup (e.g. close connections).
     165          """
     166          pass
     167
     168 -class InvalidColumnError(KeyError):
     169      pass
     170
     171 -class UnsupportedTypeError(ValueError):
     172      pass
     173
     174 -class SQLiteRepository(RepositoryImp):
     175      """
     176      SQLite-based concrete repository implementor.
     177      This is the default L{Table} backend.
     178      """
     179
     180      PK = 'ROWID'
     181      TYPES = { int: 'BIGINT', float: 'REAL', str: 'VARCHAR' }
     182
     183 -    class ChunkedReader(object):
     184
     185          SIZE = 10000
     186
     187 -        def __init__(self, cursor):
     188              self._cursor = cursor
     189
     190 -        def __iter__(self):
     191              try:
     192                  while True:
     193                      rows = self._cursor.fetchmany(self.SIZE)
     194                      if not rows:
     195                          break
     196                      else:
     197                          for row in rows:
     198                              yield row
     199              finally:
     200                  self._cursor.close()
     201
     202 -    def __init__(self, tablename):
     203          import sqlite3
     204
     205          self._conn = sqlite3.connect(':memory:')
     206          self._pk = SQLiteRepository.PK
     207
     208          super(SQLiteRepository, self).__init__(tablename)
     209
     210      @property
     211 -    def pk(self):
     212          return self._pk
     213
     214 -    def _cursor(self, sql, params=None):
     215
     216          sql = sql.replace(Predicate.PH, '?')
     217          if not params:
     218              params = []
     219
     220          return self._conn.execute(sql, params)
     221
     222 -    def query(self, sql, params=None):
     223
     224          return self._cursor(sql, params).fetchall()
     225
     226 -    def count(self):
     227
     228          query = 'SELECT  COUNT(*)\nFROM    {0}\n'.format(self.table)
     229          return self._cursor(query).fetchone()[0]
     230
     231 -    def execute(self, exp):
     232
     233          query = 'SELECT  {0}\nFROM    {1}\n'.format(', '.join(exp.select), self.table)
     234
     235          if exp.where:
     236              predicate = str(exp.predicate).replace(Predicate.PH, '?')
     237              query += 'WHERE   {0} {1}\n'.format(exp.where, predicate)
     238
     239          query += 'ORDER BY  {0} ASC\n'.format(self.pk)
     240
     241          cursor = self._cursor(query, exp.params)
     242          return SQLiteRepository.ChunkedReader(cursor)
     243
     244 -    def update(self, exp):
     245
     246          params = [exp.data]
     247          query = 'UPDATE  {0}\n  SET   {1} = ?\n'.format(self.table, exp.select[0])
     248
     249          if exp.where:
     250              predicate = str(exp.predicate).replace(Predicate.PH, '?')
     251              query += 'WHERE   {0} {1}\n'.format(exp.where, predicate)
     252              if exp.params:
     253                  params.extend(list(exp.params))
     254
     255          return self.query(query, params)
     256
     257 -    def insert(self, row):
     258
     259          row = list(row)
     260          params = ','.join(['?' for dummy in row])
     261          query = 'INSERT INTO {0} VALUES({1})'.format(self.table, params)
     262          self.query(query, row)
     263
     264 -    def create(self, metadata):
     265
     266          cols = []
     267
     268          for ci in metadata:
     269              type = self._gettype(ci.type)
     270              cols.append('{0}  {1}'.format(ci.name, type))
     271
     272          statement = 'CREATE TABLE {0} (\n    {1}\n);'.format(self.table, ',\n    '.join(cols))
     273
     274          self._conn.execute(statement)
     275
     276 -    def _gettype(self, type):
     277          try:
     278              return SQLiteRepository.TYPES[type]
     279          except KeyError:
     280              raise UnsupportedTypeError(type)
     281
     282 -    def close(self):
     283          try:
     284              return self._conn.close()
     285          except:
     286              pass
     287
     288 -class ColumnInfo(object):
     289      """
     290      Holder object for column metadata.
     291
     292      @param name: column name
     293      @type name: str
     294      @param type: column data type (Python)
     295      @type type: type
     296      """
     297
     298 -    def __init__(self, name, type):
     299          self._name = name
     300          self._type = type
     301
     302      @property
     303 -    def name(self):
     304          """
     305          Colulmn name
     306          @rtype: str
     307          """
     308          return self._name
     309
     310      @property
     311 -    def type(self):
     312          """
     313          Python data type
     314          @rtype: class
     315          """
     316          return self._type
     317
     318 -    def __str__(self):
     319          return '{0.name}:{0.type.__name__}'.format(self)
     320
     321 -    def copy(self):
     322          """
     323          @return: a deep copy of C{self}
     324          """
     325          return ColumnInfo(self.name, self.type)
     326
     327 -class DataRow(object):
     328      """
     329      Represents a table data row. This is basically what a table iterator
     330      yields for each row in a table. Provides both index (position) and
     331      column name-based access to the data.
     332      """
     333
     334 -    def __init__(self, columns, number, row):
     335
     336          self._number = number
     337          self._row = tuple(row)
     338          self._columns = {}
     339          for i, c in enumerate(columns):
     340              self._columns[c] = i
     341          assert len(self._columns) == len(self._row)
     342
     343 -    def __iter__(self):
     344          return iter(self._row)
     345
     346 -    def __getitem__(self, i):
     347
     348          if isinstance(i, csb.core.string):
     349              return self._row[self._columns[i]]
     350          else:
     351              return self._row[i]
     352
     353 -    def __len__(self):
     354          return len(self._row)
     355
     356 -    def __repr__(self):
     357          return '{0}: {1}'.format(self.__class__.__name__, repr(self._row))
     358
     359 -    def __str__(self):
     360          return self.dump()
     361
     362 -    def dump(self, delimiter='\t'):
     363          """
     364          Dump the row as a string.
     365
     366          @param delimiter: column separator (defaults to tab)
     367          @type delimiter: str
     368          """
     369          return delimiter.join(map(Table._isnull, self._row))
     370
     371      @property
     372 -    def columns(self):
     373          """
     374          Available column names
     375          @rtype: tuple
     376          """
     377          c = dict((self._columns[cn], cn) for cn in self._columns)
     378          return tuple(c[i] for i in sorted(c))
     379
     380      @property
     381 -    def number(self):
     382          """
     383          Row number
     384          @rtype: int
     385          """
     386          return self._number
     387
     388 -class Table(object):
     389      """
     390      Build and query a TSV Table. See the documentation of L{csb.io.tsv} for
     391      details and examples.
     392
     393      @param definition: column definition string: C{ColA:typeA colB:typeB ...},
     394                         where C{ColN} is a column name and C{typeN} is one of the
     395                         base Python data types: str, int, long, float.
     396                         Alternatively, the table definition may be specified
     397                         directly as a list of metadata objects.
     398      @type definition: str, tuple of L{ColumnInfo}
     399      @param name: name of the table on the SQL backend. Useful when you need to
     400                   execute native SQL queries against the table.
     401      @type name: str
     402      @param backend: table backend storage engine. This must be a proper
     403                      L{RepositoryImp} bridge implementor.
     404      @type backend: type (reference to a L{RepositoryImp} subclass)
     405
     406      @raise UnsupportedTypeError: when an unsupported type is used in the table
     407                                   C{definition}
     408      @raise ValueError: if the C{definition} is not valid
     409      """
     410
     411      """
     412      Table header string, used when saving and restoring TSV files.
     413      """
     414      HEADER = '# @TSV '
     415      NULL = ''
     416
     417 -    def __init__(self, definition, name='TSV', backend=SQLiteRepository):
     418
     419          if not issubclass(backend, RepositoryImp):
     420              raise TypeError('The Table Backend must be a Repository Implementor')
     421
     422          self._name = name
     423          self._backend = backend
     424          self._imp = backend(name)
     425          self._metadata = []
     426
     427          try:
     428              if isinstance(definition[0], ColumnInfo):
     429                  self._metadata = [ c.copy() for c in definition ]
     430              else:
     431                  if isinstance(definition, csb.core.string):
     432                      definition = [ (d.split(':')[0], getattr(builtins, d.split(':')[1])) for d in definition.split() ]
     433                  self._metadata = [ ColumnInfo(c[0], c[1]) for c in definition ]
     434              if len(self._metadata) < 1:
     435                  raise ValueError()
     436          except UnsupportedTypeError:
     437              raise
     438          except (TypeError, IndexError, ValueError, NameError, AttributeError):
     439              raise ValueError('Invalid table definition')
     440
     441          self._imp.create(self._metadata)
     442
     443      @staticmethod
     444 -    def from_tsv(tsv, definition=None, delimiter='\t', skip=0, name='TSV',
     445                   backend=SQLiteRepository):
     446          """
     447          Table factory: build a L{Table} from a TSV file.
     448
     449          @param tsv: TSV path and filename. This can be either a conventional
     450                      TSV/CSV file, or a file created with C{table.dump(tsv)}
     451          @type tsv: str
     452          @param definition: table column definition (see L{Table}). If defined,
     453                             this parameter will determine the structure of the
     454                             table. Otherwise, the table definition will be
     455                             extracted from the TSV header. If the file contains
     456                             no TSV header, this parameter is mandatory.
     457          @type definition: str, tuple of L{ColumnInfo}
     458          @param delimiter: column separator used in the file
     459          @type delimiter: str
     460          @param skip: skip the first N number of rows (the header can still be
     461                       extracted from those however)
     462          @type skip: int
     463
     464          @rtype: L{Table}
     465
     466          @raise ValueError: if neither a table C{definition} is provided,
     467                             nor the C{tsv} file has a header line
     468          """
     469
     470          if not definition:
     471              with open(tsv) as tsvfile:
     472                  for line in tsvfile:
     473                      if line.startswith(Table.HEADER):
     474                          definition = line[ len(Table.HEADER) : ]
     475
     476          if not definition:
     477              raise ValueError('No header definition found')
     478
     479          table = Table(definition, name=name, backend=backend)
     480
     481          with open(tsv) as tsvfile:
     482              for i, line in enumerate(tsvfile, start=1):
     483                  if (skip and i <= skip) or line.startswith(Table.HEADER):
     484                      continue
     485                  data = line.rstrip(os.linesep).split(delimiter)
     486                  table.insert(data)
     487
     488          return table
     489
     490      @staticmethod
     491 -    def from_iterable(iterable, definition, name='TSV', backend=SQLiteRepository):
     492          """
     493          Table factory: build a L{Table} from a 2D iterable/data reader.
     494
     495          @param iterable: data container
     496          @type iterable: iterable (2D)
     497          @param definition: table column definition (see L{Table}).
     498          @type definition: str, tuple of L{ColumnInfo}
     499
     500          @rtype: L{Table}
     501          """
     502          table = Table(definition, name=name, backend=backend)
     503
     504          for row in iterable:
     505              table.insert(list(row))
     506
     507          return table
     508
     509      @staticmethod
     510 -    def from_table(table, data=False, name='TSV', backend=SQLiteRepository):
     511          """
     512          Table factory: build a L{Table} with the definition of another L{Table}.
     513
     514          @param table: template table
     515          @type table: L{Table}
     516          @param data: if True, also copy the data from the source C{table}
     517          @type data: bool
     518
     519          @rtype: L{Table}
     520          """
     521          if data:
     522              return Table.from_iterable(table, table._metadata, name=name, backend=backend)
     523          else:
     524              return Table(table._metadata, name=name, backend=backend)
     525
     526      @staticmethod
     527 -    def _isnull(value):
     528          if value is None or str(value) == "":
     529              return Table.NULL
     530          else:
     531              return str(value)
     532
     533 -    def _convert(self, row):
     534
     535          if len(row) != len(self._metadata):
     536              raise ValueError("{0} columns expected, got {1}".format(
     537                                              len(self._metadata), len(row)))
     538
     539          data = []
     540
     541          for value, ci in zip(row, self._metadata):
     542              if value == Table.NULL:
     543                  data.append(None)
     544              elif isinstance(value, csb.core.string):
     545                  data.append(ci.type(value))
     546              else:
     547                  data.append(value)
     548
     549          return data
     550
     551 -    def __del__(self):
     552          self._imp.close()
     553
     554 -    def __len__(self):
     555          return self._imp.count()
     556
     557 -    def __iter__(self):
     558          rn = 0
     559          exp = Expression(self.columns)
     560
     561          for row in self._imp.execute(exp):
     562              rn += 1
     563              yield DataRow(self.columns, rn, row)
     564
     565 -    def __array__(self):
     566          import numpy
     567          return numpy.array([ tuple(row) for row in self ])
     568
     569 -    def __getstate__(self):
     570
     571          temp = csb.io.MemoryStream()
     572          self.dump(temp)
     573          return temp.getvalue()
     574
     575 -    def __setstate__(self, state):
     576
     577          with csb.io.TempFile() as temp:
     578              temp.write(state)
     579              temp.flush()
     580              clone = Table.from_tsv(temp.name)
     581
     582          self.__init__(definition=clone._metadata, name=clone.name, backend=clone._backend)
     583
     584          for row in clone:
     585              self.insert(row)
     586
     587 -    def __setitem__(self, i, value):
     588
     589          exp = self._interpret(i)
     590
     591          if len(exp.select) != 1:
     592              raise NotImplementedError('single-column expression expected')
     593          if csb.core.iterable(value):
     594              raise NotImplementedError("single-value assignment expected")
     595
     596          exp.data = value
     597          self._update(exp)
     598
     599 -    def __getitem__(self, i):
     600
     601          exp = self._interpret(i)
     602
     603          if exp.scalar:
     604              return self.scalar(i[0], exp.select[0])
     605          else:
     606              return self._execute(exp)
     607
     608 -    def _interpret(self, i):
     609          """
     610          Parse a table slice and convert it into an L{Expression}.
     611          @rtype: L{Expression}
     612          """
     613
     614          if not csb.core.iterable(i):
     615              i = [i, slice(None, None)]
     616          else:
     617              i = list(i)
     618
     619          if len(i) not in (1, 2):
     620              raise ValueError('Tables are only 2 dimensional')
     621          if len(i) == 1:
     622              i.append(slice(None, None))
     623
     624          exp = Expression(self.columns)
     625          columns = self._getcols(i[1])
     626          if len(columns) < 1:
     627              raise ValueError('Column slices must return at least one column')
     628          exp.select = columns
     629          exp.where = self.pk
     630
     631          if isinstance(i[0], int):
     632              self._checkrow(i[0])
     633              if len(columns) == 1 and isinstance(i[1], (int, csb.core.string)):
     634                  exp.scalar = True
     635              exp.predicate = Equals(i[0] + 1)
     636
     637          elif csb.core.iterable(i[0]):
     638              params = list(i[0])
     639              self._checkrow(params)
     640              params = list(map(lambda x: x + 1, params))
     641              exp.predicate = In(params)
     642
     643          elif isinstance(i[0], slice):
     644
     645              sl = i[0]
     646              if sl.step is not None:
     647                  raise NotImplementedError('Row slice steps are not supported')
     648
     649              if sl == slice(None, None):
     650                  exp.where = None
     651              elif sl.start is None:
     652                  self._checkrow(sl.stop)
     653                  exp.predicate = Lower(sl.stop + 1)
     654              elif sl.stop is None:
     655                  self._checkrow(sl.start)
     656                  exp.predicate = GreaterOrEquals(sl.start + 1)
     657              else:
     658                  self._checkrow([sl.start, sl.stop])
     659                  exp.predicate = Between(sl.start + 1, sl.stop)
     660
     661          else:
     662              raise TypeError("Can't handle row slice expression: {0}".format(i[0]))
     663
     664          return exp
     665
     666 -    def _checkrow(self, i):
     667
     668          if isinstance(i, int):
     669              if i < 0:
     670                  raise NotImplementedError('Negative row indices are not supported')
     671          elif csb.core.iterable(i):
     672              for j in i:
     673                  self._checkrow(j)
     674          else:
     675              raise TypeError(i)
     676
     677 -    def _getcols(self, spec, ifnull=None):
     678
     679          columns = list(self.columns)
     680
     681          if spec is None and ifnull is not None:
     682              return [ifnull]
     683
     684          elif isinstance(spec, int):
     685              try:
     686                  return [columns[spec]]
     687              except:
     688                  raise IndexError('Column {0} out of range'.format(spec))
     689
     690          elif isinstance(spec, csb.core.string):
     691              if spec in columns:
     692                  return [spec]
     693              else:
     694                  raise InvalidColumnError(spec)
     695
     696          elif isinstance(spec, slice):
     697              start = self._getcols(spec.start, columns[0])
     698              start = columns.index(start[0])
     699
     700              end = self._getcols(spec.stop, columns[-1])
     701              end = columns.index(end[0])
     702              if spec.stop is None:
     703                  end += 1
     704
     705              return [columns[i] for i in range(start, end, spec.step or 1)]
     706
     707          elif csb.core.iterable(spec):
     708              return [self._getcols(i)[0] for i in spec]
     709
     710          else:
     711              raise TypeError("Can't handle column slice expression: {0}".format(spec))
     712
     713      @property
     714 -    def name(self):
     715          """
     716          Table name
     717          @rtype: str
     718          """
     719          return self._name
     720
     721      @property
     722 -    def columns(self):
     723          """
     724          All column names
     725          @rtype: tuple
     726          """
     727          return tuple(i.name for i in self._metadata)
     728
     729      @property
     730 -    def pk(self):
     731          return self._imp.pk
     732
     733 -    def dump(self, file):
     734          """
     735          Dump the table in a file.
     736
     737          @param file: destination stream or filename
     738          @type file: file (stream) or str (filename)
     739          """
     740
     741          with csb.io.EntryWriter(file, close=False) as out:
     742
     743              definition = map(str, self._metadata)
     744              out.write(Table.HEADER)
     745              out.writeall(definition, delimiter=' ')
     746              out.write(csb.io.NEWLINE)
     747
     748              for row in self:
     749                  out.writeline(row.dump(delimiter='\t'))
     750
     751 -    def query(self, sql, params=None):
     752          """
     753          Execute a native SQL query against the storage engine.
     754
     755          @param sql: SQL query text. May contain parameter binding placeholders
     756                      (see L{Predicate.PH}). The SQL dialect of the query depends
     757                      on the SQL C{backend} being used by the table.
     758
     759          @return: native data reader
     760          @rtype: iterable (2D)
     761          """
     762          return self._imp.query(sql, params)
     763
     764 -    def insert(self, row):
     765          """
     766          Insert a new row in the table.
     767
     768          @param row: a tuple of the appropriate length
     769          @type row: tuple
     770          """
     771          data = self._convert(row)
     772          self._imp.insert(data)
     773
     774 -    def _project(self, columns):
     775
     776          metadata = dict((c.name, c) for c in self._metadata)
     777          try:
     778              return [metadata[cn].copy() for cn in columns]
     779          except KeyError as ke:
     780              raise InvalidColumnError(ke.message)
     781          except:
     782              raise
     783
     784 -    def _execute(self, exp):
     785
     786          newdef = self._project(exp.select)
     787          reader = self._imp.execute(exp)
     788          return Table.from_iterable(reader, newdef, name=self.name, backend=self._backend)
     789
     790 -    def _update(self, exp):
     791
     792          if exp.select[0] not in self.columns:
     793              raise InvalidColumnError(exp.select[0])
     794
     795          self._imp.update(exp)
     796          return self
     797
     798 -    def where(self, column):
     799          """
     800          @param column: column name
     801          @type column: str
     802          @raise InvalidColumnError: when an invalid column is requested
     803          """
     804          exp = Expression(self.columns)
     805          return Where(self, exp, column)
     806
     807 -    def select(self, *columns):
     808          """
     809          @return: a new L{Table}
     810
     811          @param columns: column names; defaults to all columns
     812          @type columns: str, tuple of str
     813          @raise InvalidColumnError: when an invalid column is requested
     814          """
     815          columns = Expression.array(columns)
     816
     817          exp = Expression(self.columns)
     818          exp.select = columns
     819
     820          return self._execute(exp)
     821
     822 -    def update(self, column, value):
     823          """
     824          Update C{column} for all rows in the table.
     825
     826          @param column: column to update (name)
     827          @type column: str
     828          @param value: new column value
     829          @raise InvalidColumnError: when an invalid column is referenced
     830          """
     831          exp = Expression(self.columns)
     832          exp.select = [column]
     833          exp.data = value
     834
     835          return self._update(exp)
     836
     837 -    def scalar(self, row=None, column=None):
     838          """
     839          @return: a scalar value at the specified row and column.
     840
     841          @param row: row index; if not specified - take the first row
     842          @type row: int
     843          @param column: column name; if not specified - take the first
     844          @type column: str
     845
     846          @raise IndexError: when an invalid row is requested
     847          @raise InvalidColumnError: when an invalid column is requested
     848          """
     849
     850          if row is None:
     851              row = 0
     852          row += 1
     853          if column is None:
     854              column = self.columns[0]
     855          elif column not in self.columns:
     856              raise InvalidColumnError(column)
     857
     858          exp = Expression(self.columns)
     859          exp.select = [column]
     860          exp.where = self.pk
     861          exp.predicate = Equals([row])
     862
     863          reader = list(self._imp.execute(exp))
     864          if len(reader) > 0:
     865              return reader[0][0]
     866          else:
     867              raise IndexError()
     868
     869 -    def column(self, column):
     870          """
     871          @return: a list all values in the specified column
     872
     873          @param column: column to fetch
     874          @type column: str
     875          """
     876          if column not in self.columns:
     877              raise InvalidColumnError(column)
     878
     879          return [ row[column] for row in self ]
     880
     881 -class Expression(object):
     882      """
     883      Metadata container: represents a table select or update expression.
     884      """
     885
     886 -    def __init__(self, columns):
     887
     888          self._table = None
     889          self._columns = []
     890
     891          self._columns = list(columns)
     892          self._select = []
     893          self._where = None
     894          self._predicate = None
     895          self._data = None
     896          self._scalar = False
     897
     898          self.select = '*'
     899
     900      @staticmethod
     901 -    def array(args):
     902          if len(args) == 1 and csb.core.iterable(args[0]):
     903              args = args[0]
     904          return list(args)
     905
     906      @property
     907 -    def all(self):
     908          return tuple(self._columns)
     909
     910      @property
     911 -    def params(self):
     912          if self.where and self.predicate:
     913              return self.predicate.params
     914          else:
     915              return None
     916
     917      @property
     918 -    def select(self):
     919          return self._select
     920      @select.setter
     921 -    def select(self, value):
     922          self._select = []
     923          if not value:
     924              self._select = list(self.all)
     925          else:
     926              if not csb.core.iterable(value):
     927                  value = [value]
     928              for i in value:
     929                  if i == '*':
     930                      self._select.extend(self.all)
     931                  else:
     932                      if i not in self._columns:
     933                          raise InvalidColumnError(i)
     934                      self._select.append(i)
     935
     936      @property
     937 -    def where(self):
     938          return self._where
     939      @where.setter
     940 -    def where(self, value):
     941          if not value:
     942              self._where = None
     943              self._predicate = None
     944          else:
     945              self._where = value
     946
     947      @property
     948 -    def predicate(self):
     949          return self._predicate
     950      @predicate.setter
     951 -    def predicate(self, value):
     952          if not value:
     953              self._where = None
     954              self._predicate = None
     955          else:
     956              self._predicate = value
     957
     958      @property
     959 -    def data(self):
     960          return self._data
     961      @data.setter
     962 -    def data(self, value):
     963          self._data = value
     964
     965      @property
     966 -    def scalar(self):
     967          return self._scalar
     968      @scalar.setter
     969 -    def scalar(self, value):
     970          self._scalar = value
     971
     972 -class Step(object):
     973
     974 -    def __init__(self, table, expression):
     975
     976          self._table = table
     977          self._expression = expression
     978
     979      @property
     980 -    def table(self):
     981          return self._table
     982
     983      @property
     984 -    def expression(self):
     985          return self._expression
     986
     987 -class Where(Step):
     988
     989 -    def __init__(self, table, expression, column):
     990
     991          if column not in table.columns and column != table.pk:
     992              raise InvalidColumnError(column)
     993
     994          expression.where = column
     995          super(Where, self).__init__(table, expression)
     996
     997 -    def in_(self, *values):
     998          return Operator(self.table, self.expression, In(values))
     999
    1000 -    def notin(self, *values):
    1001          return Operator(self.table, self.expression, NotIn(values))
    1002
    1003 -    def between(self, start, end):
    1004          return Operator(self.table, self.expression, Between(start, end))
    1005
    1006 -    def equals(self, value):
    1007          return Operator(self.table, self.expression, Equals(value))
    1008
    1009 -    def notequals(self, value):
    1010          return Operator(self.table, self.expression, NotEquals(value))
    1011
    1012 -    def greater(self, value):
    1013          return Operator(self.table, self.expression, Greater(value))
    1014
    1015 -    def lower(self, value):
    1016          return Operator(self.table, self.expression, Lower(value))
    1017
    1018 -class Operator(Step):
    1019
    1020 -    def __init__(self, table, expression, predicate):
    1021
    1022          expression.predicate = predicate
    1023          super(Operator, self).__init__(table, expression)
    1024
    1025 -    def select(self, *columns):
    1026          """
    1027          @return: a new L{Table}
    1028
    1029          @param columns: column names; defaults to all columns
    1030          @type columns: str, tuple of str
    1031          @raise InvalidColumnError: when an invalid column is requested
    1032          """
    1033          exp = self.expression
    1034          exp.select = columns
    1035
    1036          return self.table._execute(exp)
    1037
    1038 -    def update(self, column, value):
    1039          """
    1040          Update C{column} for all rows in the table.
    1041
    1042          @param column: column to update (name)
    1043          @type column: str
    1044          @param value: new column value
    1045          @raise InvalidColumnError: when an invalid column is referenced
    1046          """
    1047          exp = self.expression
    1048          exp.select = [column]
    1049          exp.data = value
    1050
    1051          return self.table._update(exp)
    1052
    1053 -class Predicate(object):
    1054
    1055      __metaclass__ = ABCMeta
    1056
    1057      PH = '?'
    1058
    1059 -    def __init__(self, params):
    1060
    1061          self._params = []
    1062
    1063          if not csb.core.iterable(params):
    1064              params = [params]
    1065
    1066          for p in params:
    1067              if csb.core.iterable(p):
    1068                  self._params.extend(p)
    1069              else:
    1070                  self._params.append(p)
    1071
    1072          self._validate()
    1073
    1074      @property
    1075 -    def params(self):
    1076          return tuple(self._params)
    1077
    1078 -    def _validate(self):
    1079
    1080          if len(self._params) < 1:
    1081              raise ValueError('{0} predicate with no params'.format(self.__class__.__name__))
    1082
    1083      @abstractproperty
    1084 -    def sql(self):
    1085          pass
    1086
    1087 -    def __str__(self):
    1088          return self.sql
    1089
    1090 -class In(Predicate):
    1091
    1092      @property
    1093 -    def sql(self):
    1094          p = [Predicate.PH for dummy in self.params]
    1095          return 'IN ({0})'.format(', '.join(p))
    1096
    1097 -class NotIn(Predicate):
    1098
    1099      @property
    1100 -    def sql(self):
    1101          p = [Predicate.PH for dummy in self.params]
    1102          return 'NOT IN ({0})'.format(', '.join(p))
    1103
    1104 -class Equals(Predicate):
    1105
    1106      @property
    1107 -    def sql(self):
    1108          return '= {0}'.format(Predicate.PH)
    1109
    1110 -class NotEquals(Predicate):
    1111
    1112      @property
    1113 -    def sql(self):
    1114          return '!= {0}'.format(Predicate.PH)
    1115
    1116 -class Greater(Predicate):
    1117
    1118      @property
    1119 -    def sql(self):
    1120          return '> {0}'.format(Predicate.PH)
    1121
    1122 -class GreaterOrEquals(Predicate):
    1123
    1124      @property
    1125 -    def sql(self):
    1126          return '>= {0}'.format(Predicate.PH)
    1127
    1128 -class Lower(Predicate):
    1129
    1130      @property
    1131 -    def sql(self):
    1132          return '< {0}'.format(Predicate.PH)
    1133
    1134 -class LowerOrEquals(Predicate):
    1135
    1136      @property
    1137 -    def sql(self):
    1138          return '<= {0}'.format(Predicate.PH)
    1139
    1140 -class Between(Predicate):
    1141
    1142 -    def __init__(self, start, end):
    1143          super(Between, self).__init__([start, end])
    1144      @property
    1145 -    def sql(self):
    1146          return 'BETWEEN {0} AND {0}'.format(Predicate.PH)
    1147