files.new_contents: """
  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
  
  [... the rest is omitted ...]
files.new_path: kgtk/kypher/sqlstore.py
files.old_contents: """
  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
  
  [... the rest is omitted ...]
files.old_path: kgtk/kypher/sqlstore.py