files: """
  SQLStore to support Kypher queries over KGTK graphs.
  """
  
  import sys
  import os.path
  import sqlite3
  from   odictliteral import odict
  import time
  import csv
  import re
  from   functools import lru_cache
  import pprint
  
  import sh
  
  from   kgtk.value.kgtkvalue import KgtkValue
  from   kgtk.exceptions import KGTKException
  
  pp = pprint.PrettyPrinter(indent=4)
  
  
  ### TO DO:
  
  # o automatically run ANALYZE on tables and indexes when they get created
  #   - we decided to only do this for indexes for now
  # - support naming of graphs which would allow deleting of the source data
  #   as well as graphs fed in from stdin
  # - absolute file names are an issue when distributing the store
  # - support some minimal sanity checking such as empty files, etc.
  # - handle column name dealiasing and normalization
  # - explanation runs outside the sqlite connection and thus does not see
  #   user functions such as kgtk_stringify and friends which causes errors;
  #   see if we can fix this somehow
  # - support declaring and dropping of (temporary) graphs that are only used
  #   once or a few times
  # - allow in-memory graphs
  # - support other DB maintenance ops such as drop, list, info, etc.
  # - see how we could better support fine-grained querying via prepared statements
  #   and persistent connections that avoid the KGTK startup overhead
  # - check for version of sqlite3, since older versions do not support ascii mode
  # - protect graph data import from failure or aborts through transactions
  # - handle table/index creation locking when we might have parallel invocations,
  #   but it looks like sqlite already does that for us
  # - provide some symbolic graph size classification (small/medium/large/xlarge)
  #   and implement table optimizations based on those categories
  # - support bump_timestamp or similar to better keep track of what's been used
  # - improve table definitions to define core columns as required to be not null
  # - full LRU cache maintainance, but maybe abandon the whole LRU concept and
  #   call it a store and not a cache
  # + complete literal accessor functions
  # + handle VACUUM and/or AUTO_VACUUM when graph tables get deleted
  #   - actually no, that requires a lot of extra space, so require to do that manually
  
  
  ### Utilities
  
  # TO DO: I am sure some form of this already exists somewhere in Craig's code
  
  def open_to_read(file, mode='rt'):
      """Version of 'open' that is smart about different types of compressed files
      and file-like objects that are already open to read.  'mode' has to be a
      valid read mode such as 'r', 'rb' or 'rt'.
      """
      assert mode in ('r', 'rb', 'rt'), 'illegal read mode'
      enc = 't' in mode and 'utf8' or None
      if isinstance(file, str) and file.endswith('.gz'):
          import gzip
          return gzip.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.bz2'):
          import bz2
          return bz2.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.xz'):
          import lzma
          return lzma.open(file, mode, encoding=enc)
      elif hasattr(file, 'read'):
          return file
      else:
          return open(file, mode)
  
  def open_to_write(file, mode='wt'):
      """Version of 'open' that is smart about different types of compressed files
      and file-like objects that are already open to write.  'mode' has to be a
      valid write mode such as 'w', 'wb' or 'wt'.
      """
      assert mode in ('w', 'wb', 'wt'), 'illegal write mode'
      enc = 't' in mode and 'utf8' or None
      if isinstance(file, str) and file.endswith('.gz'):
          import gzip
          return gzip.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.bz2'):
          import bz2
          return bz2.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.xz'):
          import lzma
          return lzma.open(file, mode, encoding=enc)
      elif hasattr(file, 'write'):
          return file
      else:
          return open(file, mode)
  
  def get_cat_command(file, _piped=False):
      """Return a cat-like sh-command to copy the possibly compressed 'file' to stdout.
      """
      # This works around some cross-platform issues with similar functionality in zconcat.
      if file.endswith('.gz'):
          return sh.gunzip.bake('-c', file, _piped=_piped)
      elif file.endswith('.bz2'):
          return sh.bunzip2.bake('-c', file, _piped=_piped)
      elif file.endswith('.xz'):
          return sh.unxz.bake('-c', file, _piped=_piped)
      else:
          return sh.cat.bake(file, _piped=_piped)
  
  
  ### SQL Store
  
  class SqlStore(object):
      """SQL database capable of storing one or more KGTK graph files as individual tables
      and allowing them to be queried with SQL statements.
      """
      # This is just an abstract place-holder for now.  Once we complete SqliteStore
      # and generalize this to other SQL DB(s), we'll move API-level methods up here.
      pass
  
  
  def sql_quote_ident(ident):
      # - standard SQL quoting for identifiers such as table and column names is via double quotes
      # - double quotes within identifiers can be escaped via two double quotes
      # - sqlite also supports MySQL's backtick syntax and SQLServer's [] syntax
      return '"' + ident.replace('"', '""') + '"'
  
  class sdict(odict):
      """Ordered schema dict that supports property access of its elements.
      """
      def __getattr__(self, name):
          if name in self:
              return self[name]
          else:
              raise AttributeError("No such attribute: " + name)
  
      def __setattr__(self, name, value):
          self[name] = value
          
  
  class SqliteStore(SqlStore):
      """SQL store implemented via sqlite3 (which is supported as a Python builtin library.
      """
  
      MASTER_TABLE = sdict[
          '_name_': 'sqlite_master',
          'columns': sdict[
              # not sure about the real types of these, but that shouldn't matter:
              'type':     sdict['_name_': 'type',     'type': 'TEXT'],
              'name':     sdict['_name_': 'name',     'type': 'TEXT'],
              'tbl_name': sdict['_name_': 'tbl_name', 'type': 'TEXT'],
              'rootpage': sdict['_name_': 'rootpage', 'type': 'INTEGER'],
              'sql':      sdict['_name_': 'sql',      'type': 'TEXT'],
          ]
      ]
      
      FILE_TABLE = sdict[
          '_name_': 'fileinfo',
          'columns': sdict[
              'file':    sdict['_name_': 'file',    'type': 'TEXT', 'key': True, 'doc': 'real path of the file containing the data'],
              'size':    sdict['_name_': 'size',    'type': 'INTEGER'],
              'modtime': sdict['_name_': 'modtime', 'type': 'FLOAT'],
              'md5sum':  sdict['_name_': 'md5sum',  'type': 'TEXT'],
              'graph':   sdict['_name_': 'graph',   'type': 'TEXT', 'doc': 'the graph defined by the data of this file'],
          ]
      ]
  
      GRAPH_TABLE = sdict[
          '_name_': 'graphinfo',
          'columns': sdict[
              'name':    sdict['_name_': 'name',    'type': 'TEXT', 'key': True, 'doc': 'name of the table representing this graph'],
              'shasum':  sdict['_name_': 'shasum',  'type': 'TEXT', 'doc': 'table hash computed by sqlite shasum command'],
              'header':  sdict['_name_': 'header',  'type': 'TEXT'],
              'size':    sdict['_name_': 'size',    'type': 'INTEGER', 'doc': 'total size in bytes used by this graph including indexes'],
              'acctime': sdict['_name_': 'acctime', 'type': 'FLOAT', 'doc': 'last time this graph was accessed'],
          ]
      ]
  
      def __init__(self, dbfile, create=False, loglevel=0):
          self.loglevel = loglevel
          if not os.path.exists(dbfile) and not create:
              raise KGTKException('sqlite DB file does not exist: %s' % dbfile)
          self.dbfile = dbfile
          self.conn = None
          self.user_functions = set()
          self.init_meta_tables()
          self.configure()
  
      def log(self, level, message):
          if self.loglevel >= level:
              header = '[%s sqlstore]:' % time.strftime('%Y-%m-%d %H:%M:%S')
              sys.stderr.write('%s %s\n' % (header, message))
              sys.stderr.flush()
  
      def init_meta_tables(self):
          if not self.has_table(self.FILE_TABLE._name_):
              self.execute(self.get_table_definition(self.FILE_TABLE))
          if not self.has_table(self.GRAPH_TABLE._name_):
              self.execute(self.get_table_definition(self.GRAPH_TABLE))
  
      CACHE_SIZE = 2 ** 32 # 4GB
  
      def configure(self):
          """Configure various settings of the store.
          """
          self.pragma('main.cache_size = %d' % int(self.CACHE_SIZE / self.pragma('page_size')))
  
  
      ### DB control:
  
      def get_conn(self):
          if self.conn is None:
              self.conn = sqlite3.connect(self.dbfile)
          return self.conn
  
      def get_sqlite_cmd(self):
          # TO DO: this should look more intelligently to find it in the python install path
          # e.g., check 'sys.prefix/bin', 'sys.exec_prefix/bin' or do a 'which sqlite3';
          # if we use a conda environment we get it automatically.
          return 'sqlite3'
  
      def close(self):
          if self.conn is not None:
              self.conn.close()
              self.conn = None
              self.user_functions = set()
  
      def execute(self, *args, **kwargs):
          return self.get_conn().execute(*args, **kwargs)
  
      def executemany(self, *args, **kwargs):
          return self.get_conn().executemany(*args, **kwargs)
  
      def commit(self):
          self.get_conn().commit()
  
      def pragma(self, expression):
          """Evaluate a PRAGMA 'expression' and return the result (if any).
          """
          res = list(self.execute('PRAGMA ' + expression))
          if len(res) == 0:
              return None
          elif len(res) == 1:
              return res[0][0]
          else:
              return res
  
  
      ### DB functions:
  
      USER_FUNCTIONS = {}
      AGGREGATE_FUNCTIONS = ('AVG', 'COUNT', 'GROUP_CONCAT', 'MAX', 'MIN', 'SUM', 'TOTAL')
  
      @staticmethod
      def register_user_function(name, num_params, func, deterministic=False):
          name = name.upper()
          SqliteStore.USER_FUNCTIONS[name] = {'name': name, 'num_params': num_params, 'func': func, 'deterministic': deterministic}
  
      @staticmethod
      def is_user_function(name):
          name = name.upper()
          return SqliteStore.USER_FUNCTIONS.get(name) is not None
  
      def load_user_function(self, name, error=True):
          name = name.upper()
          if name in self.user_functions:
              return
          elif self.is_user_function(name):
              info = self.USER_FUNCTIONS.get(name)
              # Py 3.8 or later:
              #self.get_conn().create_function(info['name'], info['num_params'], info['func'], deterministic=info['deterministic'])
              self.get_conn().create_function(info['name'], info['num_params'], info['func'])
              self.user_functions.add(name)
          elif error:
              raise KGTKException('No user-function has been registered for: ' + str(name))
  
      def is_aggregate_function(self, name):
          """Return True if 'name' is an aggregate function supported by this database.
          """
          return name.upper() in self.AGGREGATE_FUNCTIONS
  
  
      ### DB properties:
      
      def get_db_size(self):
          """Return the size of all currently allocated data pages in bytes.  This maybe smaller than
          the size of the database file if there were deletions that put pages back on the free list.
          Free pages can be reclaimed by running 'VACUUM', but that might require a substantial amount
          of available disk space if the current DB file is large.
          """
          return (self.pragma('page_count') - self.pragma('freelist_count')) * self.pragma('page_size')
  
      def has_table(self, table_name):
          """Return True if a table with name 'table_name' exists in the store.
          """
          schema = self.MASTER_TABLE
          columns = schema.columns
          query = """SELECT COUNT(*) FROM %s WHERE %s=?""" % (schema._name_, columns.name._name_)
          (cnt,) = self.execute(query, (table_name,)).fetchone()
          return cnt > 0
  
      def get_table_header(self, table_name):
          """Return the column names of 'table_name' as a list.  For graph tables, this list will be
          isomorphic to the parsed header line of the corresponding KGTK file.
          """
          result = self.execute('SELECT * FROM %s LIMIT 0' % table_name)
          return [col[0] for col in result.description]
  
      def get_table_row_count(self, table_name):
          for (cnt,) in self.execute('SELECT COUNT(*) FROM %s' % table_name):
              return cnt
          return 0
      
  
      ### Schema manipulation:
      
      def kgtk_header_to_graph_table_schema(self, table_name, header):
          columns = sdict()
          for col in header:
              columns[col] = sdict['type': 'TEXT', '_name_': col]
          return sdict['_name_': table_name, 'columns': columns]
  
      def get_key_column(self, table_schema, error=True):
          """Return the name of the first column in 'schema' designated as a 'key',
          or raise an error if no key column has been designated (unless 'error' is False).
          """
          for col in table_schema.columns.values():
              if col.get('key') == True:
                  return col._name_
          if error:
              raise KGTKException('no key column defined')
          return None
  
      def get_table_definition(self, table_schema):
          colspec = ', '.join([sql_quote_ident(col._name_) + ' ' + col.type for col in table_schema.columns.values()])
          return 'CREATE TABLE %s (%s)' % (table_schema._name_, colspec)
  
      def get_index_name(self, table_schema, column):
          """Return a global name for the index for 'column' on 'table_schema'.
          """
          table_name = table_schema._name_
          column_name = table_schema.columns[column]._name_
          index_name = '%s_%s_idx' % (table_name, column_name)
          return index_name
  
      def get_index_definition(self, table_schema, column, unique=False):
          """Return a definition statement to create an index for 'column' on 'table_schema'.
          Create a 'unique' or primary key index if 'unique' is True.  We are currently
          only considering single-column indexes, however, we might generalize this down
          the road to two-column indices such as '(node1, label)', '(label, node2)', etc.
          """
          table_name = table_schema._name_
          column_name = table_schema.columns[column]._name_
          index_name = self.get_index_name(table_schema, column)
          unique = unique and 'UNIQUE' or ''
          return 'CREATE %s INDEX %s on %s (%s)' % (
              unique, sql_quote_ident(index_name), table_name, sql_quote_ident(column_name))
      
      def has_index(self, table_schema, column):
          """Return True if table 'table_schema' has an index defined for 'column'.
          """
          table_name = table_schema._name_
          column_name = table_schema.columns[column]._name_
          index_name = '%s_%s_idx' % (table_name, column_name)
          # we just key in on the name, not the table type, given how the names are constructed:
          return self.has_table(index_name)
  
      def get_column_list(self, *columns):
          return ', '.join([sql_quote_ident(col._name_) for col in columns])
  
      def get_full_column_list(self, table_schema):
          return ', '.join([sql_quote_ident(col._name_) for col in table_schema.columns.values()])
  
  
      ### Generic record access:
      
      def get_record_info(self, schema, key):
          """Return a dict info structure for the row identified by 'key' in table 'schema',
          or None if this key does not exist in the table.  All
  
  [... the rest is omitted ...]
files: kgtk/kypher/sqlstore.py
files: """
  SQLStore to support Kypher queries over KGTK graphs.
  """
  
  import sys
  import os.path
  import sqlite3
  from   odictliteral import odict
  import time
  import csv
  import re
  from   functools import lru_cache
  import pprint
  
  import sh
  
  from   kgtk.value.kgtkvalue import KgtkValue
  from   kgtk.exceptions import KGTKException
  
  pp = pprint.PrettyPrinter(indent=4)
  
  
  ### TO DO:
  
  # o automatically run ANALYZE on tables and indexes when they get created
  #   - we decided to only do this for indexes for now
  # - support naming of graphs which would allow deleting of the source data
  #   as well as graphs fed in from stdin
  # - absolute file names are an issue when distributing the store
  # - support some minimal sanity checking such as empty files, etc.
  # - handle column name dealiasing and normalization
  # - support declaring and dropping of (temporary) graphs that are only used
  #   once or a few times
  # - allow in-memory graphs
  # - support other DB maintenance ops such as drop, list, info, etc.
  # - see how we could better support fine-grained querying via prepared statements
  #   and persistent connections that avoid the KGTK startup overhead
  # - check for version of sqlite3, since older versions do not support ascii mode
  # - protect graph data import from failure or aborts through transactions
  # - handle table/index creation locking when we might have parallel invocations,
  #   but it looks like sqlite already does that for us
  # - provide some symbolic graph size classification (small/medium/large/xlarge)
  #   and implement table optimizations based on those categories
  # - support bump_timestamp or similar to better keep track of what's been used
  # - improve table definitions to define core columns as required to be not null
  # - full LRU cache maintainance, but maybe abandon the whole LRU concept and
  #   call it a store and not a cache
  # - complete literal accessor functions
  # + handle VACUUM and/or AUTO_VACUUM when graph tables get deleted
  #   - actually no, that requires a lot of extra space, so require to do that manually
  
  
  ### Utilities
  
  # TO DO: I am sure some form of this already exists somewhere in Craig's code
  
  def open_to_read(file, mode='rt'):
      """Version of 'open' that is smart about different types of compressed files
      and file-like objects that are already open to read.  'mode' has to be a
      valid read mode such as 'r', 'rb' or 'rt'.
      """
      assert mode in ('r', 'rb', 'rt'), 'illegal read mode'
      enc = 't' in mode and 'utf8' or None
      if isinstance(file, str) and file.endswith('.gz'):
          import gzip
          return gzip.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.bz2'):
          import bz2
          return bz2.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.xz'):
          import lzma
          return lzma.open(file, mode, encoding=enc)
      elif hasattr(file, 'read'):
          return file
      else:
          return open(file, mode)
  
  def open_to_write(file, mode='wt'):
      """Version of 'open' that is smart about different types of compressed files
      and file-like objects that are already open to write.  'mode' has to be a
      valid write mode such as 'w', 'wb' or 'wt'.
      """
      assert mode in ('w', 'wb', 'wt'), 'illegal write mode'
      enc = 't' in mode and 'utf8' or None
      if isinstance(file, str) and file.endswith('.gz'):
          import gzip
          return gzip.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.bz2'):
          import bz2
          return bz2.open(file, mode, encoding=enc)
      elif isinstance(file, str) and file.endswith('.xz'):
          import lzma
          return lzma.open(file, mode, encoding=enc)
      elif hasattr(file, 'write'):
          return file
      else:
          return open(file, mode)
  
  def get_cat_command(file, _piped=False):
      """Return a cat-like sh-command to copy the possibly compressed 'file' to stdout.
      """
      # This works around some cross-platform issues with similar functionality in zconcat.
      if file.endswith('.gz'):
          return sh.gunzip.bake('-c', file, _piped=_piped)
      elif file.endswith('.bz2'):
          return sh.bunzip2.bake('-c', file, _piped=_piped)
      elif file.endswith('.xz'):
          return sh.unxz.bake('-c', file, _piped=_piped)
      else:
          return sh.cat.bake(file, _piped=_piped)
  
  
  ### SQL Store
  
  class SqlStore(object):
      """SQL database capable of storing one or more KGTK graph files as individual tables
      and allowing them to be queried with SQL statements.
      """
      # This is just an abstract place-holder for now.  Once we complete SqliteStore
      # and generalize this to other SQL DB(s), we'll move API-level methods up here.
      pass
  
  
  def sql_quote_ident(ident):
      # - standard SQL quoting for identifiers such as table and column names is via double quotes
      # - double quotes within identifiers can be escaped via two double quotes
      # - sqlite also supports MySQL's backtick syntax and SQLServer's [] syntax
      return '"' + ident.replace('"', '""') + '"'
  
  class sdict(odict):
      """Ordered schema dict that supports property access of its elements.
      """
      def __getattr__(self, name):
          if name in self:
              return self[name]
          else:
              raise AttributeError("No such attribute: " + name)
  
      def __setattr__(self, name, value):
          self[name] = value
          
  
  class SqliteStore(SqlStore):
      """SQL store implemented via sqlite3 (which is supported as a Python builtin library.
      """
  
      MASTER_TABLE = sdict[
          '_name_': 'sqlite_master',
          'columns': sdict[
              # not sure about the real types of these, but that shouldn't matter:
              'type':     sdict['_name_': 'type',     'type': 'TEXT'],
              'name':     sdict['_name_': 'name',     'type': 'TEXT'],
              'tbl_name': sdict['_name_': 'tbl_name', 'type': 'TEXT'],
              'rootpage': sdict['_name_': 'rootpage', 'type': 'INTEGER'],
              'sql':      sdict['_name_': 'sql',      'type': 'TEXT'],
          ]
      ]
      
      FILE_TABLE = sdict[
          '_name_': 'fileinfo',
          'columns': sdict[
              'file':    sdict['_name_': 'file',    'type': 'TEXT', 'key': True, 'doc': 'real path of the file containing the data'],
              'size':    sdict['_name_': 'size',    'type': 'INTEGER'],
              'modtime': sdict['_name_': 'modtime', 'type': 'FLOAT'],
              'md5sum':  sdict['_name_': 'md5sum',  'type': 'TEXT'],
              'graph':   sdict['_name_': 'graph',   'type': 'TEXT', 'doc': 'the graph defined by the data of this file'],
          ]
      ]
  
      GRAPH_TABLE = sdict[
          '_name_': 'graphinfo',
          'columns': sdict[
              'name':    sdict['_name_': 'name',    'type': 'TEXT', 'key': True, 'doc': 'name of the table representing this graph'],
              'shasum':  sdict['_name_': 'shasum',  'type': 'TEXT', 'doc': 'table hash computed by sqlite shasum command'],
              'header':  sdict['_name_': 'header',  'type': 'TEXT'],
              'size':    sdict['_name_': 'size',    'type': 'INTEGER', 'doc': 'total size in bytes used by this graph including indexes'],
              'acctime': sdict['_name_': 'acctime', 'type': 'FLOAT', 'doc': 'last time this graph was accessed'],
          ]
      ]
  
      def __init__(self, dbfile, create=False, loglevel=0):
          self.loglevel = loglevel
          if not os.path.exists(dbfile) and not create:
              raise KGTKException('sqlite DB file does not exist: %s' % dbfile)
          self.dbfile = dbfile
          self.conn = None
          self.user_functions = set()
          self.init_meta_tables()
          self.configure()
  
      def log(self, level, message):
          if self.loglevel >= level:
              header = '[%s sqlstore]:' % time.strftime('%Y-%m-%d %H:%M:%S')
              sys.stderr.write('%s %s\n' % (header, message))
              sys.stderr.flush()
  
      def init_meta_tables(self):
          if not self.has_table(self.FILE_TABLE._name_):
              self.execute(self.get_table_definition(self.FILE_TABLE))
          if not self.has_table(self.GRAPH_TABLE._name_):
              self.execute(self.get_table_definition(self.GRAPH_TABLE))
  
      CACHE_SIZE = 2 ** 32 # 4GB
  
      def configure(self):
          """Configure various settings of the store.
          """
          self.pragma('main.cache_size = %d' % int(self.CACHE_SIZE / self.pragma('page_size')))
  
  
      ### DB control:
  
      def get_conn(self):
          if self.conn is None:
              self.conn = sqlite3.connect(self.dbfile)
          return self.conn
  
      def get_sqlite_cmd(self):
          # TO DO: this should look more intelligently to find it in the python install path
          # e.g., check 'sys.prefix/bin', 'sys.exec_prefix/bin' or do a 'which sqlite3';
          # if we use a conda environment we get it automatically.
          return 'sqlite3'
  
      def close(self):
          if self.conn is not None:
              self.conn.close()
              self.conn = None
              self.user_functions = set()
  
      def execute(self, *args, **kwargs):
          return self.get_conn().execute(*args, **kwargs)
  
      def executemany(self, *args, **kwargs):
          return self.get_conn().executemany(*args, **kwargs)
  
      def commit(self):
          self.get_conn().commit()
  
      def pragma(self, expression):
          """Evaluate a PRAGMA 'expression' and return the result (if any).
          """
          res = list(self.execute('PRAGMA ' + expression))
          if len(res) == 0:
              return None
          elif len(res) == 1:
              return res[0][0]
          else:
              return res
  
  
      ### DB functions:
  
      USER_FUNCTIONS = {}
      AGGREGATE_FUNCTIONS = ('AVG', 'COUNT', 'GROUP_CONCAT', 'MAX', 'MIN', 'SUM', 'TOTAL')
  
      @staticmethod
      def register_user_function(name, num_params, func, deterministic=False):
          name = name.upper()
          SqliteStore.USER_FUNCTIONS[name] = {'name': name, 'num_params': num_params, 'func': func, 'deterministic': deterministic}
  
      @staticmethod
      def is_user_function(name):
          name = name.upper()
          return SqliteStore.USER_FUNCTIONS.get(name) is not None
  
      def load_user_function(self, name, error=True):
          name = name.upper()
          if name in self.user_functions:
              return
          elif self.is_user_function(name):
              info = self.USER_FUNCTIONS.get(name)
              # Py 3.8 or later:
              #self.get_conn().create_function(info['name'], info['num_params'], info['func'], deterministic=info['deterministic'])
              self.get_conn().create_function(info['name'], info['num_params'], info['func'])
              self.user_functions.add(name)
          elif error:
              raise KGTKException('No user-function has been registered for: ' + str(name))
  
      def is_aggregate_function(self, name):
          """Return True if 'name' is an aggregate function supported by this database.
          """
          return name.upper() in self.AGGREGATE_FUNCTIONS
  
  
      ### DB properties:
      
      def get_db_size(self):
          """Return the size of all currently allocated data pages in bytes.  This maybe smaller than
          the size of the database file if there were deletions that put pages back on the free list.
          Free pages can be reclaimed by running 'VACUUM', but that might require a substantial amount
          of available disk space if the current DB file is large.
          """
          return (self.pragma('page_count') - self.pragma('freelist_count')) * self.pragma('page_size')
  
      def has_table(self, table_name):
          """Return True if a table with name 'table_name' exists in the store.
          """
          schema = self.MASTER_TABLE
          columns = schema.columns
          query = """SELECT COUNT(*) FROM %s WHERE %s=?""" % (schema._name_, columns.name._name_)
          (cnt,) = self.execute(query, (table_name,)).fetchone()
          return cnt > 0
  
      def get_table_header(self, table_name):
          """Return the column names of 'table_name' as a list.  For graph tables, this list will be
          isomorphic to the parsed header line of the corresponding KGTK file.
          """
          result = self.execute('SELECT * FROM %s LIMIT 0' % table_name)
          return [col[0] for col in result.description]
  
      def get_table_row_count(self, table_name):
          for (cnt,) in self.execute('SELECT COUNT(*) FROM %s' % table_name):
              return cnt
          return 0
      
  
      ### Schema manipulation:
      
      def kgtk_header_to_graph_table_schema(self, table_name, header):
          columns = sdict()
          for col in header:
              columns[col] = sdict['type': 'TEXT', '_name_': col]
          return sdict['_name_': table_name, 'columns': columns]
  
      def get_key_column(self, table_schema, error=True):
          """Return the name of the first column in 'schema' designated as a 'key',
          or raise an error if no key column has been designated (unless 'error' is False).
          """
          for col in table_schema.columns.values():
              if col.get('key') == True:
                  return col._name_
          if error:
              raise KGTKException('no key column defined')
          return None
  
      def get_table_definition(self, table_schema):
          colspec = ', '.join([sql_quote_ident(col._name_) + ' ' + col.type for col in table_schema.columns.values()])
          return 'CREATE TABLE %s (%s)' % (table_schema._name_, colspec)
  
      def get_index_name(self, table_schema, column):
          """Return a global name for the index for 'column' on 'table_schema'.
          """
          table_name = table_schema._name_
          column_name = table_schema.columns[column]._name_
          index_name = '%s_%s_idx' % (table_name, column_name)
          return index_name
  
      def get_index_definition(self, table_schema, column, unique=False):
          """Return a definition statement to create an index for 'column' on 'table_schema'.
          Create a 'unique' or primary key index if 'unique' is True.  We are currently
          only considering single-column indexes, however, we might generalize this down
          the road to two-column indices such as '(node1, label)', '(label, node2)', etc.
          """
          table_name = table_schema._name_
          column_name = table_schema.columns[column]._name_
          index_name = self.get_index_name(table_schema, column)
          unique = unique and 'UNIQUE' or ''
          return 'CREATE %s INDEX %s on %s (%s)' % (
              unique, sql_quote_ident(index_name), table_name, sql_quote_ident(column_name))
      
      def has_index(self, table_schema, column):
          """Return True if table 'table_schema' has an index defined for 'column'.
          """
          table_name = table_schema._name_
          column_name = table_schema.columns[column]._name_
          index_name = '%s_%s_idx' % (table_name, column_name)
          # we just key in on the name, not the table type, given how the names are constructed:
          return self.has_table(index_name)
  
      def get_column_list(self, *columns):
          return ', '.join([sql_quote_ident(col._name_) for col in columns])
  
      def get_full_column_list(self, table_schema):
          return ', '.join([sql_quote_ident(col._name_) for col in table_schema.columns.values()])
  
  
      ### Generic record access:
      
      def get_record_info(self, schema, key):
          """Return a dict info structure for the row identified by 'key' in table 'schema',
          or None if this key does not exist in the table.  All column keys will be set
          although some values may be None.
          """
          table = schema._name_
          cols = schema.columns
          keycol = self.get_key_column(schema)
         
  
  [... the rest is omitted ...]
files: kgtk/kypher/sqlstore.py