# -*- coding: utf-8 -*-

"""wraps jSuite tables, like the jSuite OLE DB driver does
except this one does it by simply constructing the query requried given the columns and the table etc
see http://www.sjsoft.com/ for more information"""

# Copyright 2004 St James Software
# 
# This file is part of jToolkit.
#
# jToolkit is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# jToolkit is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with jToolkit; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

from jToolkit.data import database
from jToolkit.data import dbtable

def strfilter(str):
  """filters non-alphanumeric chars, replaces them with underscore"""
  fstr = ""
  for c in str:
    if c.isalnum(): fstr += c
    else: fstr += "_"
  return fstr

def strmatch(string1, string2):
  """non-case-sensitive compare of alphanumeric chars only"""
  strip1 = ""
  strip2 = ""
  for c in string1:
    if c.isalnum(): strip1 += c.lower()
  for c in string2:
    if c.isalnum(): strip2 += c.lower()
  return strip1 == strip2

def normalizename(tagname):
  """this makes sure the tagname is also a valid column name"""
  if not (tagname[:1].isalpha() or tagname[:1] == "_"):
    tagname = "t" + tagname
  return tagname

class jsuitedbwrapper(database.dbwrapper):
  """this is a wrapper around dbwrapper which modifies the queries, to handle historical data"""
  def __init__(self, instance, *args, **kwargs):
    """initialises the jsuitedbwrapper class"""
    database.dbwrapper.__init__(self, instance, *args, **kwargs)

  def jsuitequery(self, columns, table, wherecond=""):
    """returns a query given a list of columns, a table, and a where clause"""
    newcolumns = [strfilter(column) for column in columns]
    # if we are using one of our own OLE DB providers, we don't need to wrap the query
    if self.driver.__name__.endswith('PyADO') and self.instance.DBPROVIDER.startswith("jSuite."):
      return self.nowrapquery(newcolumns,table,wherecond)
    else:
      return self.wrapquery(newcolumns,table,wherecond)

  def nowrapquery(self, columns, table, wherecond=""):
    """simple constructs a query without any history data wrapping"""
    if len(wherecond.strip()) > 0:
      whereclause = " where "+wherecond
    else:
      whereclause = ""
    return "select "+",".join(columns)+" from "+table+whereclause

  def gettagname(self, column):
    """returns the actual tagname for a given column name"""
    # handle quality as though it were a value (same col)
    column = column.lower()
    if column.lower().endswith('_quality'):
      return column[:column.rfind('_quality')]
    else:
      return column

  def gettagsource(self, tagname):
    """returns the tag source identifier for the given tagname"""
    sql = "select name, tagsource from tag_storage_defs"
    cursor = self.query(sql)
    morerows = 1
    while morerows:
      row = cursor.fetchone()
      if row is None:
        morerows = 0
      else:
        name, tagsource = row
        if strmatch(name, tagname):
          cursor.close()
          return tagsource
    cursor.close()
    return None

  def tagexists(self, table, tagname):
    """checks whether the tag is valid for the given table"""
    ticker, col = self.gettickercol(table, tagname)
    if ticker is None:
      return False
    else:
      return True

  def gettickercol(self, table, tagname):
    """returns the ticker and col for the given tagname in the given table"""
    tagsource = self.gettagsource(tagname)
    if tagsource is None:
      return None, None
    # TODO: check case sensitivity requirements
    sql = "select ticker, col from tag_ticker_cols " + \
      "where hist_mgr_num = (select hist_mgr_num from hist_mgr where file_name = '%s') " % table + \
      "and tagsource = '%s' and validto is null" % tagsource
    cursor = self.cursorexecute(sql)
    row = cursor.fetchone()
    cursor.close()
    if row is None:
      return None, None
    else:
      ticker, col = row
      return ticker, col

  def wrapquery(self, columns, table, wherecond=""):
    """wraps a query by creating a subselect that extracts the required history data..."""
    select = ""
    fromclause = " from "
    whereclause = " where "
    subselect = ""
    tickers = []
    tickercolumns = []
    tickercolumnmap = {}
    tagnames = []
    useslogtime = 0
    for column in columns:
      if column == 'logtime':
        useslogtime = 1
        tagnames.append(column)
      else:
        tagname = self.gettagname(column)
        addticker = 1
        addcolumn = 1
        ticker,col = self.gettickercol(table,tagname)
        # normalize after we've found the ticker and column so it doesn't affect the search
        tagname = normalizename(tagname)
        tagnames.append(tagname)
        try:
          ticker,col = int(ticker), int(col)
        except:
          raise KeyError("error finding ticker and column for tagname %s in table %s" % (tagname, table))
        if ticker in tickers: 
          addticker = 0
          if (ticker,col) in tickercolumns:
            if tagname in tickercolumnmap[ticker, col]:
              addcolumn = 0
        if addticker:
          if len(tickers) > 0:
            fromclause += ", "
          fromclause += "%s %s%03d" % (table,table,ticker)
          if len(tickers) > 0:
            whereclause += " and "
          whereclause += "%s%03d.ticker = %d" % (table,ticker,ticker)
          if len(tickers) > 0:
            whereclause += " and %s%03d.logtime = %s%03d.logtime" % (table,ticker,table,tickers[0])
          tickers.append(ticker)
        if addcolumn:
          tickercolumns.append((ticker,col))
          if (ticker, col) in tickercolumnmap:
            tickercolumnmap[ticker, col].append(tagname)
            print "warning: same ticker and column accessed under multiple names: %d, %d, %r" % (ticker, col, tickercolumnmap[ticker, col])
          else:
            tickercolumnmap[ticker, col] = [tagname]
          if len(select) > 0:
            select += ", "
          # note: for access & sqlserver, need columnwrap [ ] around column name
          select += "%s%03d.value%03d as %s" % (table,ticker,col,tagname)
          # always put quality in the subquery...
          select += ", %s%03d.quality%03d as %s" % (table,ticker,col,tagname+"_quality")
                
    subselect = "(select "
    if useslogtime:
      if len(tickers) > 0:
        t0 = tickers[0]
      else:
        t0 = 0
        fromclause += "%s %s%03d" % (table,table,0)
	whereclause += "%s%03d.ticker = %d" % (table,0,0)
      subselect += "%s%03d.logtime as logtime" % (table,t0)
      if len(tickercolumns) > 0:
        subselect += ", "
    if len(tickercolumns) == 0 and not useslogtime:
      # not doing anything, no subselect will be created
      subselect = table
    else:
      subselect += select + fromclause + whereclause + ") " + table
    if len(wherecond.strip()) > 0:
      whereclause = " where "+wherecond
    else:
      whereclause = ""
    sqlquery = "select "+",".join(tagnames)+" from "+subselect+whereclause
    return sqlquery

class jSuiteTable(dbtable.DBTable):
  def __init__(self, db, tablename, columnlist, rowidcols, orderbycols):
    dbtable.DBTable.__init__(self, db, tablename, columnlist, rowidcols, orderbycols)
    self.tablename = "(%s) %s" % (db.jsuitequery(self.columnlist, tablename), tablename)
  def getsqlcolumns(self):
    tagnames = [normalizename(column) for column in self.columnlist]
    return ", ".join(tagnames)
  def addrow(self, argdict):
    raise NotImplementedError("can't insert into jHistorian row")
  def modifyrow(self, argdict):
    raise NotImplementedError("can't modify jHistorian row")
  def deleterow(self, argdict):
    raise NotImplementedError("can't delete jHistorian row")
  def createtable(self):
    raise NotImplementedError("can't create jHistorian table")
  def droptable(self):
    raise NotImplementedError("can't drop jHistorian table")
  def denormalizenames(self, rows):
    for row in rows:
      for column in self.columnlist:
        tagname = normalizename(column)
        if tagname != column:
          if tagname in row:
            row[column] = row.pop(tagname)
    return rows
  def gettablerows(self, filter = None):
    return self.denormalizenames(super(jSuiteTable, self).gettablerows(filter))
  def getsometablerows(self, minRow = None, maxRow = None, filter = None):
    return self.denormalizenames(super(jSuiteTable, self).getsometablerows(minRow, maxRow, filter))
  def getsomerecord(self, filter = None):
    rowid, record = super(jSuiteTable, self).getsomerecord(filter)
    return rowid, self.denormalizenames([record])[0]