#include <stdio.h>
#include <sqlite3.h>

#include "update.h"

//***************************************************************************
// Class Table
//***************************************************************************

sqlite3* Table::handle = 0;
char*    Table::fileName = 0;
char*    Table::confPath = 0;
char*    Table::encoding = 0;
char*    Table::extensionFile = 0;
int      Table::attached = 0;
int      Table::initialized = no;
cMutex   Table::mutex;
int      Table::walMode = no;

//***************************************************************************
// cDbService
//***************************************************************************

const char* cDbService::formats[] =
{
   "INTEGER",
   "ASCII",
   0
};

const char* cDbService::toString(FieldFormat t)
{
   return formats[t];
}

//***************************************************************************
// Object
//***************************************************************************

Table::Table(const char* name, FieldDef* f, ViewDef* v)
{
   tableName = name;
   row = new cDbRow(f, v);
   holdInMemory = no;

   stmtSelect = 0;
   stmtInsert = 0;
   stmtUpdate = 0;
   stmtTruncate = 0;
}

Table::~Table()
{
   close();

   delete row;
}

//***************************************************************************
// Open / Close
//***************************************************************************

int Table::open()
{
   int res;

   if (!fileName)
      return fail;

   cMutexLock lock(&mutex);

   if (!handle)
   {
      res = sqlite3_open_v2(fileName, &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
   
      if (res)
      {
         errorSqlite("open()");
         sqlite3_close(handle);
         handle = 0;
         
         return fail;
      }
   }

   attached++;
   
   return init();
}

int Table::close()
{
   if (!handle)
      return success;

   cMutexLock lock(&mutex);

   if (stmtSelect)   { sqlite3_finalize(stmtSelect);   stmtSelect = 0;   }
   if (stmtInsert)   { sqlite3_finalize(stmtInsert);   stmtInsert = 0;   }
   if (stmtUpdate)   { sqlite3_finalize(stmtUpdate);   stmtUpdate = 0;   }
   if (stmtTruncate) { sqlite3_finalize(stmtTruncate); stmtTruncate = 0; }

   if (row->getView(0))
   {
      for (int i = 0; row->getView(i)->name; i++)
      {
         if (row->getView(i)->stmtSelect)
         {
            sqlite3_finalize(row->getView(i)->stmtSelect);
            row->getView(i)->stmtSelect = 0;
         }
      }
   }

   if (attached)
      attached--;

   if (!attached)
   {
      sqlite3_close(handle);
      handle = 0;
      initialized = no;
   }

   return success;
}

//***************************************************************************
// Init
//***************************************************************************

int Table::init()
{
   int res;
   char num[10];
   string str;
   sqlite3_stmt* checkStatement;
   char* tmp;

   cMutexLock lock(&mutex);

   // ------------------------------
   // performance optimizations ... use with care ;-)
   
   if (!initialized)
   {     
      if (sqlite3_exec(handle, "PRAGMA synchronous = OFF", 0, 0, 0) != SQLITE_OK)
         return errorSqlite("init()");

      if (sqlite3_exec(handle, "PRAGMA fullfsync = OFF", 0, 0, 0) != SQLITE_OK)
         return errorSqlite("init()");
      
      if (sqlite3_exec(handle, "PRAGMA temp_store = 2", 0, 0, 0) != SQLITE_OK)   // 2 = MEMORY
         return errorSqlite("init()");
      
      if (sqlite3_exec(handle, "PRAGMA page_size = 32768", 0, 0, 0) != SQLITE_OK)
         return errorSqlite("init()");
      
      if (sqlite3_exec(handle, "PRAGMA cache_size = 10000", 0, 0, 0) != SQLITE_OK)
         return errorSqlite("init()");

      if (sqlite3_exec(handle, "PRAGMA locking_Mode = NORMAL", 0, 0, 0) != SQLITE_OK)
         return errorSqlite("init()");

      if (encoding && *encoding)
      {
         asprintf(&tmp, "PRAGMA encoding = \"%s\"", encoding);
         
         if (sqlite3_exec(handle, tmp, 0, 0, 0) != SQLITE_OK)
            return errorSqlite("init()");
         
         free(tmp);
      }

      if (walMode)
      {
         // WAL Mode
         
         if (sqlite3_exec(handle, "PRAGMA journal_mode = WAL", 0, 0, 0) != SQLITE_OK)
            return errorSqlite("init()");
         
         // in WAL Mode we have to limit the journal size to avoid endles growing (50MB here)
         
         if (sqlite3_exec(handle, "PRAGMA journal_size_limit = 52428800", 0, 0, 0) != SQLITE_OK)
            return errorSqlite("init()");
      }
      else
      {
         if (sqlite3_exec(handle, "PRAGMA journal_mode = OFF", 0, 0, 0) != SQLITE_OK)
            return errorSqlite("init()");
      }

      // ----

      // set the timeout to handle lock conditions
      
      if (sqlite3_busy_timeout(handle, 10 * 1000) != SQLITE_OK)
         return errorSqlite("init(busy_timeout)");

      if (extensionFile)
      {
         // load extensions ...
         
         if (sqlite3_enable_load_extension(handle, yes) != SQLITE_OK)
            return errorSqlite("init(enable_load_extension)");
         
         if (sqlite3_load_extension(handle, extensionFile, 0, 0) != SQLITE_OK)
            return errorSqlite("init(load_extension)");
      }

      initialized = yes;
   }

   // ------------------------------
   // check/create table ...
   
   asprintf(&tmp, "select name from sqlite_master where type = 'table'"
            " and name = '%s'", tableName);
   
   res = sqlite3_prepare_v2(handle, tmp, -1, &checkStatement, 0);
   
   free(tmp);
   
   if (res != SQLITE_OK)
      return errorSqlite("init()");
   
   res = sqlite3_step(checkStatement);
   sqlite3_finalize(checkStatement);

   if (res == SQLITE_DONE)               // no row selectet -> table don't exists -> create table
   {
      if (createTable() != SQLITE_OK)
         return fail;
   }
   else if (res == SQLITE_ROW)           // table already exists
      ; // nothing to do
   else
      return errorSqlite("init()");


   // ------------------------------
   // prepare BASIC statements

   //
   // select by primary key ...

   str = "select ";

   for (int i = 0, n = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftCalculated)
         continue;

      if (n++) str += string(", ");
      str += getField(i)->name;
   }

   str += string(" from ") + tableName + string(" where ");

   for (int i = 0, n = 0; getField(i)->name; i++)
   {
      if (!(getField(i)->type & ftPrimary))
         continue;

      if (n++) str += string(" and ");
      sprintf(num, "%d", getField(i)->index+1);
      str += getField(i)->name + string(" = ?") + num;
   }

   str += ";";

   res = prepareStmt(str.c_str(), stmtSelect);

   //
   // insert ...

   str = string("insert into ") + tableName + string(" values(");

   for (int i = 0, n = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftCalculated)
         continue;

      if (n++) str += string(", ");
      sprintf(num, "%d", getField(i)->index+1);
      str += string("?") + num;
   }

   str += string(");");
   res += prepareStmt(str.c_str(), stmtInsert);

   //
   // update via primary key ...

   str = string("update ") + tableName + string(" set ");

   for (int i = 0, n = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftPrimary || getField(i)->type & ftCalculated)
         continue;

      if (strcmp(getField(i)->name, "inssp") == 0)  // don't update the insert stamp
         continue;

      if (n++) str += string(", ");
      sprintf(num, "%d", getField(i)->index+1);
      str += getField(i)->name + string(" = ?") + num;
   }

   str += " where ";

   for (int i = 0, n = 0; getField(i)->name; i++)
   {
      if (!(getField(i)->type & ftPrimary))
         continue;

      if (n++) str += string(" and ");
      sprintf(num, "%d", getField(i)->index+1);
      str += getField(i)->name + string(" = ?") + num;
   }

   str += ";";

   res = prepareStmt(str.c_str(), stmtUpdate);

   if (res != SQLITE_OK)
      return errorSqlite("init()");

   // 
   // truncate

   str = "delete from " + string(tableName);

   res = prepareStmt(str.c_str(), stmtTruncate);

   if (res != SQLITE_OK)
      return errorSqlite("init()");

   // -----------------------------------------
   // prepare selects for all defined views ..
   
   if (row->getView(0))
   {
      for (int i = 0; res == SQLITE_OK && row->getView(i)->name; i++)
      {
         str = "select ";

         for (int f = 0, n = 0; row->getView(i)->fields[f] != na; f++)
         {
            FieldDef* fld = getField(row->getView(i)->fields[f]);

            if (n++) str += string(", ");

            if (row->getView(i)->type == vtSelect && fld->viewStmt)
               str += fld->viewStmt;
            else
               str += fld->name;
         }

         str += " from ";

         str += row->getView(i)->type == vtDbView ? row->getView(i)->name : tableName;

         if (row->getView(i)->critCount)
         {
            str += " where ";
            
            for (int f = 0, n = 0; row->getView(i)->crit[f].fieldIndex != na; f++)
            {
               FieldDef* fld = getField(row->getView(i)->crit[f].fieldIndex);
               
               if (n++) str += string(" and ");
               sprintf(num, "%d", fld->index+1);
               str += fld->name + string(" ") 
                  + row->getView(i)->crit[f].op
                  + string(" ?") + num;
            }
         }

         str += ";";
         
         res = prepareStmt(str.c_str(), row->getView(i)->stmtSelect);
      }
   }

   if (res != SQLITE_OK)
      errorSqlite("init()");

   return res;
}

//***************************************************************************
// Create Table
//***************************************************************************

int Table::createTable()
{
   int res;
   string statement;
   sqlite3_stmt* create;

   cMutexLock lock(&mutex);

   tell(0, "TVM2VDR: Initialy creating table for %s in '%s'", 
        tableName, fileName);

   // build 'create' statement ...

   statement = string("create table ") + tableName + string("(");

   for (int i = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftCalculated)
         continue;

      if (i) statement += string(", ");
      statement += string(getField(i)->name) + " " + string(toString(getField(i)->format));
   }

   statement += string(", PRIMARY KEY(");

   for (int i = 0, n = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftPrimary)
      {
         if (n++) statement += string(", ");
         statement += string(getField(i)->name) + " DESC";
      }
   }

   statement += string("))");

   // prepare ...

   res = sqlite3_prepare_v2(handle, statement.c_str(), -1, &create, 0);
   
   if (res != SQLITE_OK)
   {
      tell(0, "TVM2VDR: Error preparing '%s'", statement.c_str());
      return errorSqlite("createTable()");
   }
   
   res = sqlite3_step(create);
   sqlite3_finalize(create);
 
   // prepare views ...
   
   if (row->getView(0))
   {
      for (int i = 0; row->getView(i)->name; i++)
      {
         ViewDef* view = row->getView(i);

         if (view->type == vtSelect)
         {
            if (!view->critCount)
               continue;
            
            // index anlegen
            
            statement = "create index idx" + string(view->name) + " on " 
               + string(tableName) + "(";
            
            int n = 0;
            
            for (int f = 0; view->crit[f].fieldIndex != na; f++)
            {
               if (n++) statement += string(", ");
               
               FieldDef* fld = getField(view->crit[f].fieldIndex);
               
               if (!(fld->type & ftCalculated))
                  statement += fld->name;
            }
            
            statement += ");";
            
            res = sqlite3_prepare_v2(handle, statement.c_str(), -1, &create, 0);
            
            if (res != SQLITE_OK)
            {
               tell(0, "TVM2VDR: Error preparing '%s'", statement.c_str());
               return errorSqlite("createTable()");
            }
            
            res = sqlite3_step(create);
            sqlite3_finalize(create);
            
         }
         else
         {
            if (createView(row->getView(i)) != success)
               return fail;

//          statement = string("create view ") + row->getView(i)->name + string(" as select ");

//          int n = 0;

//          for (int f = 0; row->getView(i)->crit[f].fieldIndex != na; f++)
//          {
//             if (n++) statement += string(", ");

//             FieldDef* fld = getField(row->getView(i)->crit[f].fieldIndex);

//             if (fld->type & ftCalculated)
//                statement += string(fld->viewStmt) + " as " + string(fld->name);
//             else
//                statement += string(tableName) + "." + string(fld->name);
//          }

//          for (int f = 0; row->getView(i)->fields[f] != na; f++)
//          {
//             if (n++) statement += string(", ");

//             FieldDef* fld = getField(row->getView(i)->fields[f]);

//             if (fld->type & ftCalculated)
//                statement += string(fld->viewStmt) + " as " + string(fld->name);
//             else
//                statement += string(tableName) + "." + string(fld->name);
//          }

//          if (row->getView(i)->from)
//             statement += string(" ") + row->getView(i)->from;
//          else
//             statement += " from " + string(tableName);

//          // ...
         
//          res = sqlite3_prepare_v2(handle, statement.c_str(), -1, &create, 0);

//          // printf("create view '%s'\n", statement.c_str());
         
//          if (res != SQLITE_OK)
//          {
//             tell(0, "TVM2VDR: Error preparing '%s'", statement.c_str());
//             return errorSqlite("createTable()");
//          }
         
//          res = sqlite3_step(create);
//          sqlite3_finalize(create);
         }
      }
   }

   return SQLITE_OK;
}

//***************************************************************************
// Create View
//***************************************************************************

int Table::createView(ViewDef* view)
{
   char* tmp;
   FILE* f;
   char* buffer;
   int size = 1000;
   int nread = 0;
   int res;
   sqlite3_stmt* statement;

   asprintf(&tmp, "%s/%s.sql", confPath, view->name);

   tell(0, "TVM2VDR: Initialy creating view '%s' for %s using definition in '%s'", 
        view->name, tableName, tmp);
   
   if (!(f = fopen(tmp, "r")))
   {
      free(tmp);
      tell(0, "Fatal: Can't access '%s'; %m", tmp);
      return fail;
   }
   
   buffer = (char*)malloc(size+1);
   
   while (res = fread(buffer+nread, 1, 1000, f))
   {
      nread += res;
      size += 1000;
      buffer = (char*)realloc(buffer, size+1);
   }

   fclose(f);
   buffer[nread] = 0;

   // execute statement

   tell(0, "TVM2VDR: Executing '%s'", buffer);

   res = sqlite3_prepare_v2(handle, buffer, -1, &statement, 0);
   
   free(buffer);
   
   if (res != SQLITE_OK)
      return errorSqlite("createView()");
   
   res = sqlite3_step(statement);
   sqlite3_finalize(statement);

   return success;
}

//***************************************************************************
// Prepare
//***************************************************************************

int Table::prepareStmt(const char* aStatement, sqlite3_stmt*& stmt)
{
   if (!aStatement)
      return fail;

   // free previous statement

   if (stmt)
      sqlite3_finalize(stmt);

   // prepare statement

   int res = sqlite3_prepare_v2(handle, aStatement, -1, &stmt, 0);

   if (res != SQLITE_OK)
   {
      tell(0, "Preparing '%s' failed", aStatement);
      return errorSqlite("prepare()");
   }

   tell(0, "TVM2VDR: Statement '%s' prepared", aStatement);

   return success;
}

//***************************************************************************
// Bind
//***************************************************************************

int Table::bindField(sqlite3_stmt* stmt, FieldDef* field, cDbRow* r)
{
   int res;

   if (field->format == ffAscii)
   {
      const char* v = r->getStrValue(field->index);

      if (!v ||!*v)
         res = sqlite3_bind_null(stmt, field->index+1);
      else
         res = sqlite3_bind_text(stmt, field->index+1, v, 
                                 -1, SQLITE_TRANSIENT);
   }
   else
      res = sqlite3_bind_int(stmt, field->index+1, 
                             r->getIntValue(field->index));
   
   return res;
}

//***************************************************************************
// Store Values
//***************************************************************************

void Table::storeValues(sqlite3_stmt* stmt, ViewDef* view)
{
   // in case of a view we have to assign the value to the right field some tricky
   
   if (view)
   {
      for (int i = 0; view->fields[i] != na; i++)
      {
         if (getField(view->fields[i])->format == ffAscii)
            row->setValue(view->fields[i], (const char*)sqlite3_column_text(stmt, i));
         else
            row->setValue(view->fields[i], sqlite3_column_int(stmt, i));
      }
   }
   else
   {
      int n = 0;

      for (int i = 0; i < fieldCount(); i++)
      {
         // calc fields only supported in views by this Table implementation

         if (getField(i)->type & ftCalculated)
            continue;

         if (getField(i)->format == ffAscii)
            row->setValue(i, (const char*)sqlite3_column_text(stmt, n));
         else
            row->setValue(i, sqlite3_column_int(stmt, n));
         n++;
      }
   }
}

void Table::copyValues(cDbRow* r)
{
   for (int i = 0; i < fieldCount(); i++)
   {
      if (getField(i)->format == ffAscii)
         row->setValue(i, r->getStrValue(i));
      else
         row->setValue(i, r->getIntValue(i));
   }
}

//***************************************************************************
// Error Sqlite
//***************************************************************************

int Table::errorSqlite(const char* prefix)
{
   if (!handle)
      return fail;

   tell(0, "TVM2VDR: sqlite-error table %s, '%s' '%s' (%d)", 
           tableName,
           prefix,
           sqlite3_errmsg(handle), 
           sqlite3_errcode(handle));

   return fail;
}

//***************************************************************************
// Delete Where
//***************************************************************************

int Table::deleteWhere(const char* where)
{
   sqlite3_stmt* delStatement;
   char* tmp;
   int res;

   asprintf(&tmp, "delete from %s where %s;", tableName, where);
   res = sqlite3_prepare_v2(handle, tmp, -1, &delStatement, 0);
   free(tmp);

   if (res != SQLITE_OK)
      return errorSqlite("deleteWhere()");
   
   res = sqlite3_step(delStatement);
   sqlite3_finalize(delStatement);

   if (!res == SQLITE_DONE)
      return errorSqlite("deleteWhere()");

   return success;
}

//***************************************************************************
// Truncate
//***************************************************************************

int Table::truncate()
{
   if (sqlite3_step(stmtTruncate) != SQLITE_DONE)
      return errorSqlite("truncate()");

   sqlite3_reset(stmtTruncate);

   return success;
}

//***************************************************************************
// Insert
//***************************************************************************

int Table::insert(cDbRow* r)
{
   int res = 0;
   int affected;

   if (!stmtInsert)
   {
      tell(0, "TVM2VDR: Fatal missing insert statement\n");
      return fail;
   }

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   if (sqlite3_reset(stmtInsert) != SQLITE_OK
       || sqlite3_clear_bindings(stmtInsert) != SQLITE_OK)
   {
      return errorSqlite("insert(reset)");
   }

   for (int i = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftCalculated)
         continue;

      if (strcmp(getField(i)->name, "updsp") == 0 || strcmp(getField(i)->name, "inssp") == 0)
         r->setValue(getField(i)->index, time(0));
         
      if (bindField(stmtInsert, getField(i), r) != SQLITE_OK)
         return errorSqlite("insert(bind)");
   }

   res = sqlite3_step(stmtInsert);

   if (res != SQLITE_DONE)
      return errorSqlite("insert(step)");

   affected = sqlite3_changes(handle);
   sqlite3_reset(stmtInsert);
  
   return affected == 1 ? success : fail;
}

//***************************************************************************
// Update
//***************************************************************************

int Table::update(cDbRow* r)
{
   int res = SQLITE_OK;
   int affected;

   if (!stmtUpdate)
   {
      tell(0, "TVM2VDR: Fatal missing update statement\n");
      return fail;
   }

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   if (sqlite3_reset(stmtUpdate) != SQLITE_OK ||
       sqlite3_clear_bindings(stmtUpdate) != SQLITE_OK)
      return errorSqlite("update(reset)");
   
   for (int i = 0; getField(i)->name; i++)
   {
      if (getField(i)->type & ftCalculated)
         continue;

      if (strcmp(getField(i)->name, "updsp") == 0)
         r->setValue(getField(i)->index, time(0));

      if (bindField(stmtUpdate, getField(i), r) != SQLITE_OK)
         return errorSqlite("update(bind)");
   }

   res = sqlite3_step(stmtUpdate);

   if (res != SQLITE_DONE)
      return errorSqlite("update(step)");

   affected = sqlite3_changes(handle);
   sqlite3_reset(stmtUpdate);
  
   return affected == 1 ? success : fail;
}

//***************************************************************************
// Find
//***************************************************************************

int Table::find(cDbRow* r, int copy)
{
   int res = SQLITE_OK;

   if (!stmtSelect)
      return no;

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   if (r != row && copy)    // if unsing external clear my own row
      row->clear();

   if (sqlite3_reset(stmtSelect) != SQLITE_OK
       || sqlite3_clear_bindings(stmtSelect) != SQLITE_OK)
   {
      errorSqlite("find(reset)");
      return no;
   }

   // bind prepared statement
   
   for (int i = 0; getField(i)->name; i++)
   {
      if (!(getField(i)->type & ftPrimary))
         continue;

      if (bindField(stmtSelect, getField(i), r) != SQLITE_OK)
      {
         errorSqlite("find(bind)");
         return no;
      }
   }
  
   res = sqlite3_step(stmtSelect);

   if (res != SQLITE_DONE && res != SQLITE_ROW)
   {
      errorSqlite("find(step)");
      return no;
   }

   if (copy)
   {
      if (res == SQLITE_ROW)
         storeValues(stmtSelect);
      else if (r != row)           // if using external row copy values to own row
         copyValues(r);
   }

   sqlite3_reset(stmtSelect);

   return res == SQLITE_ROW;
}

//***************************************************************************
// 
//***************************************************************************

int Table::find(int viewIndex, cDbRow* r)
{
   int res = SQLITE_OK;

   ViewDef* view = row->getView(viewIndex);

   if (!view || !view->stmtSelect)
      return no;

   if (view->stmtSelect)
      sqlite3_reset(view->stmtSelect);

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   if (r != row)    // if unsing external clear my own row
      row->clear();

   if (sqlite3_reset(view->stmtSelect) != SQLITE_OK
       || sqlite3_clear_bindings(view->stmtSelect) != SQLITE_OK)
   {
      errorSqlite("find(reset)");
      return no;
   }

   // bind prepared statement
   
   for (int i = 0; view->crit[i].fieldIndex != na; i++)
   {
      if (bindField(view->stmtSelect, getField(view->crit[i].fieldIndex), r) != SQLITE_OK)
      {
         errorSqlite("find(bind)");
         return no;
      }
   }
  
   res = sqlite3_step(view->stmtSelect);

   if (res != SQLITE_DONE && res != SQLITE_ROW)
   {
      errorSqlite("find(step)");
      return no;
   }

   if (res == SQLITE_ROW)
      storeValues(view->stmtSelect, view);
   else if (r != row)           // if using external row copy values to own row
      copyValues(r);

   if (res != SQLITE_ROW)
      sqlite3_reset(view->stmtSelect);

   return res == SQLITE_ROW;
}

//***************************************************************************
// Fetch
//***************************************************************************

int Table::fetch(int viewIndex, cDbRow* r)
{
   int res;
   ViewDef* view = row->getView(viewIndex);

   if (!view)
      return no;

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   res = sqlite3_step(view->stmtSelect);

   if (res != SQLITE_DONE && res != SQLITE_ROW)
   {
      errorSqlite("fetch(step)");
      return no;
   }

   if (res == SQLITE_ROW)
      storeValues(view->stmtSelect, view);
   else if (r != row)           // if using external row copy values to own row
      copyValues(r);

   if (res != SQLITE_ROW)
      sqlite3_reset(view->stmtSelect);

   return res == SQLITE_ROW;
}

//***************************************************************************
// Reset Fetch
//***************************************************************************

void Table::resetFetch(int viewIndex)
{
   ViewDef* view = row->getView(viewIndex);
   
   if (view)
      sqlite3_reset(view->stmtSelect);
}

//***************************************************************************
// Find Where
//***************************************************************************

int Table::prepareWhere(const char* where, sqlite3_stmt*& stmt)
{
   string statement;
   int res = SQLITE_OK;

   if (!where || !*where)
      return fail;

   statement = "select * from " + string(tableName) + " where " + where + ";";

   if (stmt) sqlite3_finalize(stmt);
   res = prepareStmt(statement.c_str(), stmt);

   if (res != SQLITE_OK)
      return errorSqlite("prepareWhere(prepare)");

   return success;
}

int Table::bindWhereStr(sqlite3_stmt* stmt, int index, const char* value)
{
   int res;

   if (!stmt)
      return fail;

   if (!value ||!*value)
      res = sqlite3_bind_null(stmt, index);
   else
      res = sqlite3_bind_text(stmt, index, value, -1, SQLITE_TRANSIENT);

   if (res != SQLITE_OK)
      return errorSqlite("bindWhereStr()");

   return success;
}

int Table::bindWhereInt(sqlite3_stmt* stmt, int index, int value)
{
   int res;

   if (!stmt)
      return fail;

    res = sqlite3_bind_int(stmt, index, value);

   if (res != SQLITE_OK)
      return errorSqlite("bindWhereInt()");

   return success;
}

int Table::findWhere(sqlite3_stmt*& stmt, cDbRow* r)
{
   int res = SQLITE_OK;

   if (!stmt)
      return no;

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   if (r != row)                // if unsing external clear my own row
      row->clear();

   res = sqlite3_step(stmt);

   if (res != SQLITE_DONE && res != SQLITE_ROW)
   {
      errorSqlite("findWhere(step)");
      return no;
   }

   if (res == SQLITE_ROW)
      storeValues(stmt);
   else if (r != row)           // if using external row copy values to own row
      copyValues(r);

   if (res != SQLITE_ROW)
   {
      sqlite3_finalize(stmt);
      stmt = 0;
   }

   return res == SQLITE_ROW;
}

//***************************************************************************
// Fetch
//***************************************************************************

int Table::fetch(sqlite3_stmt*& stmt, cDbRow* r)
{
   int res;

   if (!stmt)
      return no;

   if (!r)
      r = row;

   cMutexLock lock(&mutex);

   res = sqlite3_step(stmt);

   if (res != SQLITE_DONE && res != SQLITE_ROW)
   {
      errorSqlite("fetch(step)");
      return no;
   }

   if (res == SQLITE_ROW)
      storeValues(stmt);
   else if (r != row)           // if using external row copy values to own row
      copyValues(r);

   if (res != SQLITE_ROW)
   {
      sqlite3_finalize(stmt);
      stmt = 0;
   }

   return res == SQLITE_ROW;
}

//***************************************************************************
// Reset Fetch
//***************************************************************************

void Table::resetFetch(sqlite3_stmt* stmt)
{
   if (stmt)
   {
      sqlite3_reset(stmt);
      sqlite3_clear_bindings(stmt);
   }
}

void Table::finalizeFetch(sqlite3_stmt*& stmt)
{
   if (stmt)
   {
      sqlite3_reset(stmt);
      sqlite3_finalize(stmt);
      stmt = 0;
   }
}

//***************************************************************************
// Store
//***************************************************************************

int Table::store(cDbRow* r)
{
   // insert or just update ..

   if (find(r, no))
      return update(r);
   else
      return insert(r);
}


//***************************************************************************
// Event Fields
//***************************************************************************
//***************************************************************************
// Fields
//***************************************************************************

const char* decriptionViewStatementOld =
   "replace(\n"
   "case when genre is Null then '' else '|' || 'Genre: ' || genre end ||\n"
   "case when category is Null then '' else '|' || 'Kategorie: ' || category end ||\n"
   "case when country is Null then '' else '|' || 'Land: ' || country end ||\n"
   "case when year is Null then '' else '  ' || year end ||\n"
   "case when duration is Null then '' else '   (' || (duration/60) || 'min)' end ||\n"
   "case when shortdescription is Null then '' else '||' || shortdescription end ||\n"
   "case when shortreview is Null then '' else '||' || 'Kurzkritik: ' || shortreview end ||\n"
   "case when tipp is Null and rating is Null then '' else '||' end ||\n"
   "case when tipp is Null then '' else '|' || tipp end ||\n"
   "case when rating is Null then '' else '|' || rating end ||\n"
   "case when longdescription is Null then '' else '||' || longdescription end ||\n"
   "case when info is Null then '' else '||' || 'Info: ' || info end ||\n"
   "case when origtitle is Null and fsk is Null then '' else '|' end ||\n"
   "case when origtitle is Null then '' else '|' || 'Originaltitel: ' || origtitle end ||\n"
   "case when fsk is Null then '' else '|' || 'Altersempfehlung: ab ' || fsk end ||\n"
   "case when actor is Null then '' else '||' || 'Schauspieler: ' || actor end ||\n"
   "case when topic is Null then '' else '||' || 'Thema: ' || topic end ||\n"
   "case when director is Null and screenplay is Null and music is Null and audio is Null and flags is Null then '' else '|' end ||\n"
   "case when director is Null then '' else '|' || 'Regie: ' || director end ||\n"
   "case when screenplay is Null then '' else '|' || 'Drehbuch: ' || screenplay end ||\n"
   "case when music is Null then '' else '|' || 'Musik: ' || music end ||\n"
   "case when audio is Null then '' else '|' || 'Audio: ' || audio end ||\n"
   "case when flags is Null then '' else '|' || 'Flags: ' || flags end\n"
   ",'|','\n"
   "')\n";

const char* decriptionViewStatement =
   "replace(\n"
   "ltrim(\n"
   "case when genre is Null then ''            else '|' || 'Genre: ' || genre end ||\n"
   "case when category is Null then ''         else '|' || 'Kategorie: ' || category end ||\n"
   "case when country is Null then ''          else '|' || 'Land: ' || country end ||\n"
   "case when year is Null then ''             else '|' || 'Jahr: ' || year end\n"
   ",'|') ||\n"
   "case when shortdescription is Null then '' else '||' || shortdescription end ||\n"
   "case when shortreview is Null then ''      else '||' || 'Kurzkritik: ' || shortreview end ||\n"
   "case when tipp is Null and rating is Null then '' else '||' end ||\n"
   "case when tipp is Null then ''             else '|' || tipp end ||\n"
   "case when rating is Null then ''           else '|' || rating end ||\n"
   "case when topic is Null then ''            else '||' || 'Thema: ' || topic end ||\n"
   "case when longdescription is Null then ''  else '||' || longdescription end ||\n"
   "case when info is Null then ''             else '||' || 'Info: ' || info end ||\n"
   "case when moderator is Null then ''        else '||' || 'Moderator: ' || moderator end ||\n"
   "case when team is Null then ''             else '|' || 'Team: ' || team end ||\n"
   "case when guest is Null then ''            else '|' || 'Gäste: ' || guest end ||\n"
   "case when origtitle is Null and fsk is Null then '' else '|' end ||\n"
   "case when origtitle is Null then ''        else '|' || 'Originaltitel: ' || origtitle end ||\n"
   "case when fsk is Null then ''              else '|' || 'Altersempfehlung: ab ' || fsk end ||\n"
   "case when actor is Null then ''            else '||' || 'Darsteller: ' || actor end ||\n"
   "case when director is Null and screenplay is Null and music is Null and audio is Null and flags is Null then '' else '|' end ||\n"
   "case when director is Null then ''         else '|' || 'Regie: ' || director end ||\n"
   "case when screenplay is Null then ''       else '|' || 'Drehbuch: ' || screenplay end ||\n"
   "case when music is Null then ''            else '|' || 'Musik: ' || music end ||\n"
   "case when audio is Null then ''            else '|' || 'Audio: ' || audio end ||\n"
   "case when flags is Null then ''            else '|' || 'Flags: ' || flags end ||\n"
   "case when epi.episodename is Null then ''  else '||' || 'Serie: ' || epi.episodename end ||\n"
   "case when epi.shortname is Null then ''    else '|' || 'Kurzname: ' || epi.shortname end ||\n"
   "case when epi.partname is Null then ''     else '|' || 'Episode: ' || epi.partname end ||\n"
   "case when epi.season is Null then ''       else '|' || 'Staffel: ' || epi.season end ||\n"
   "case when epi.part is Null then ''         else '|' || 'Staffelfolge: ' || epi.part end ||\n"
   "case when epi.number is Null then ''       else '|' || 'Folge: ' || epi.number end\n"
   ",'|', '\n')";

const char* evtViewFrom =
   "from events left outer join episodes epi on "
   " (events.episode = epi.compname"
   " and events.episodepart = epi.comppartname"
   " and events.episodelang = epi.lang"
   ")";

cDbService::FieldDef cEventFields::fields[] =
{
   // name               format     index               type          viewStmt

   // primary key

   { "eventid",          ffInt,     fiEventId,          ftPrimary,    0 },
   { "channelid",        ffAscii,   fiChannelId,        ftPrimary,    0 },

   // meta                                                         

   { "source",           ffAscii,   fiSource,           ftMeta,       0 },
   { "fileref",          ffAscii,   fiFileRef,          ftMeta,       0 },
   { "inssp",            ffInt,     fiInsSp,            ftMeta,       0 },
   { "updsp",            ffInt,     fiUpdSp,            ftMeta,       0 },
   { "updflg",           ffAscii,   fiUpdFlg,           ftMeta,       0 },
                                                                   
   // vdr event data                                               
                                                                   
   { "tableid",          ffInt,     fiTableId,          ftData,       0 },
   { "version",          ffInt,     fiVersion,          ftData,       0 },
   { "title",            ffAscii,   fiTitle,            ftData,       0 },
   { "comptitle",        ffAscii,   fiCompTitle,        ftData,       0 },
   { "shorttext",        ffAscii,   fiShortText,        ftData,       0 },
   { "compshorttext",    ffAscii,   fiCompShortText,    ftData,       0 },
   { "longdescription",  ffAscii,   fiLongDescription,  ftData,       0 },
   { "starttime",        ffInt,     fiStartTime,        ftData,       0 },
   { "duration",         ffInt,     fiDuration,         ftData,       0 },
   { "parentalrating",   ffInt,     fiParentalRating,   ftData,       0 },
   { "vps",              ffInt,     fiVps,              ftData,       0 },

   { "description",      ffAscii,   fiDescription,      ftCalculated, decriptionViewStatement },
                                                                   
   // additional tvm data                                          
                                                                   
   { "shortdescription", ffAscii,   fiShortDescription, ftData,       0 },
   { "actor",            ffAscii,   fiActor,            ftData,       0 },
   { "audio",            ffAscii,   fiAudio,            ftData,       0 },
   { "category",         ffAscii,   fiCategory,         ftData,       0 },
   { "country",          ffAscii,   fiCountry,          ftData,       0 },
   { "director",         ffAscii,   fiDirector,         ftData,       0 },
   { "flags",            ffAscii,   fiFlags,            ftData,       0 },
   { "genre",            ffAscii,   fiGenre,            ftData,       0 },
   { "info",             ffAscii,   fiInfo,             ftData,       0 },
   { "music",            ffAscii,   fiMusic,            ftData,       0 },
   { "origtitle",        ffAscii,   fiOrigtitle,        ftData,       0 },
   { "screenplay",       ffAscii,   fiScreenplay,       ftData,       0 },
   { "shortreview",      ffAscii,   fiShortreview,      ftData,       0 },
   { "tipp",             ffAscii,   fiTipp,             ftData,       0 },
   { "topic",            ffAscii,   fiTopic,            ftData,       0 },
   { "year",             ffAscii,   fiYear,             ftData,       0 },
   { "rating",           ffAscii,   fiRating,           ftData,       0 },
   { "fsk",              ffAscii,   fiFsk,              ftData,       0 },
   { "movieid",          ffAscii,   fiMovieid,          ftData,       0 },
   { "moderator",        ffAscii,   fiModerator,        ftData,       0 },
   { "team",             ffAscii,   fiTeam,             ftData,       0 },
   { "guest",            ffAscii,   fiGuest,            ftData,       0 },

   // episodes

   { "episode",          ffAscii,   fiEpisode,          ftData,       0 },
   { "episodepart",      ffAscii,   fiEpisodePart,      ftData,       0 },
   { "episodelang",      ffAscii,   fiEpisodeLang,      ftData,       0 },

   { 0 }
};

cDbService::FieldDef* cEventFields::toField(const char* name)
{
   for (int i = 0; i < fiCount; i++)
      if (strcmp(fields[i].name, name) == 0)
         return &fields[i];
   
   tell(0, "TVM2VDR: Request for unexpected field '%s', ignoring", name);

   return 0;
}

cDbService::ViewDef cEventFields::views[] =
{
   // name         type      from             keyfields            cnt    fields           cnt     sqlite3_stmt*

   { "eventsview", vtDbView, evtViewFrom, { { fiEventId,   "=" },
                                            { fiChannelId, "=" },
                                            { na               } },  2, { fiEventId,
                                                                          fiChannelId,
                                                                          fiSource,
                                                                          fiFileRef,
                                                                          fiTableId,
                                                                          fiVersion,
                                                                          fiTitle,
                                                                          fiShortText,
                                                                          fiStartTime,
                                                                          fiDuration,
                                                                          fiParentalRating,
                                                                          fiVps,
                                                                          fiDescription,
                                                                          na },              13,    0 },


   { "bychannel",  vtSelect, evtViewFrom, { { fiChannelId, "=" },
                                            { fiSource,    "=" },
                                            { na               } },  2, { fiEventId,
                                                                          fiChannelId,
                                                                          na },               2,    0 },

   { "bycomptitle", vtSelect, 0,           { { fiCompTitle, "=" },
                                             { na            }  },   1, { fiEventId,
                                                                          fiChannelId,
                                                                          fiCompShortText,
                                                                          na },               3,    0 },

   { "byupdflag",   vtSelect, 0,           { { fiUpdFlg, "=" },
                                             { fiSource, "=" },
                                             { na            }  },   2, { fiEventId,
                                                                          fiChannelId,
                                                                          na },               2,    0 },

   { "all",         vtSelect, 0,           { { na            }  },   0, { fiEventId,
                                                                          fiChannelId,
                                                                          fiSource,
                                                                          fiCompTitle,
                                                                          fiTitle,
                                                                          na },               5,    0 },

   { "allbysrc",    vtSelect, 0,           { { fiSource, "=" },
                                             { na            }  },   1, { fiEventId,
                                                                          fiChannelId,
                                                                          fiSource,
                                                                          fiCompTitle,
                                                                          fiTitle,
                                                                          na },               5,    0 },

   { 0 }
};

//***************************************************************************
// TVM File Fields
//***************************************************************************

cDbService::FieldDef cTvmFields::fields[] =
{
   // name               format     index          type

   { "name",             ffAscii,   fiName,        ftPrimary,      0 },

   { "inssp",            ffInt,     fiInsSp,       ftMeta,         0 },
   { "updsp",            ffInt,     fiUpdSp,       ftMeta,         0 },

   { "fileref",          ffAscii,   fiFileRef,     ftData,         0 }, // name + '-' + tag
   { "tag",              ffAscii,   fiTag,         ftData,         0 },

   { 0 }
};

//***************************************************************************
// Image Fields
//***************************************************************************

cDbService::FieldDef cImageFields::fields[] =
{
   // name               format     index          type

   { "eventid",          ffInt,     fiEventId,     ftPrimary, 0 },
   { "lfn",              ffInt,     fiLfn,         ftPrimary, 0 },

   { "inssp",            ffInt,     fiInsSp,       ftMeta,    0 },
   { "updsp",            ffInt,     fiUpdSp,       ftMeta,    0 },

   { "name",             ffAscii,   fiName,        ftData,    0 },

   { 0 }
};

cDbService::ViewDef cImageFields::views[] =
{
   // name       type      from  keyfields            cnt    fields      cnt     sqlite3_stmt*

   { "lesslfn",  vtSelect, 0,    { { fiLfn, "<"  },
                                 { na            } },   1,  { fiEventId,
                                                             fiLfn,
                                                             fiInsSp,
                                                             fiUpdSp,
                                                             fiName,
                                                             na },         5,    0 },

   { "byname",   vtSelect, 0,    { { fiName, "=" },
                                   { fiLfn, "<"  },
                                   { na          } },   2, { fiEventId,
                                                             fiName,
                                                             fiLfn,
                                                             fiInsSp,
                                                             fiUpdSp,
                                                             na },         5,    0 },

   { 0 }
};

//***************************************************************************
// Series Episode Fields
//***************************************************************************

cDbService::FieldDef cEpisodeFields::fields[] =
{
   // name               format     index           type

   // primary key 

   { "compname",         ffAscii,   fiCompName,     ftPrimary,    0 },   // episode name compressed
   { "comppartname",     ffAscii,   fiCompPartName, ftPrimary,    0 },   // part name compressed
   { "lang",             ffAscii,   fiLang,         ftPrimary,    0 }, 

   { "distinctepisode",  ffAscii,   fiDistCompName, ftCalculated, "distinct compname" },

   { "inssp",            ffInt,     fiInsSp,        ftMeta,       0 },
   { "updsp",            ffInt,     fiUpdSp,        ftMeta,       0 },
   { "maxupdsp",         ffInt,     fiMaxUpdSp,     ftCalculated, "max(updsp)" },
   { "link",             ffInt,     fiLink,         ftData,       0 },

   // episode data 

   { "shortname",        ffAscii,   fiShortName,    ftData,       0 },
   { "episodename",      ffAscii,   fiEpisodeName,  ftData,       0 },   // episode name

   // part data

   { "partname",         ffAscii,   fiPartName,     ftData,       0 },   // part name
   { "season",           ffInt,     fiSeason,       ftData,       0 },
   { "part",             ffInt,     fiPart,         ftData,       0 },
   { "number",           ffInt,     fiNumber,       ftData,       0 },

   { 0 }
};

cDbService::ViewDef cEpisodeFields::views[] =
{
   // name       type        from   keyfields                   cnt    fields       cnt   sqlite3_stmt*

   { "maxupdsp",    vtSelect, 0,    { { na } },                   0, { fiMaxUpdSp,
                                                                       na },          1,  0 },

   { "alldistinct", vtSelect, 0,    { { na } },                   0, { fiDistCompName,
                                                                       na },          1,  0 },

   { "bycompnames", vtSelect, 0,    { { fiCompName,     "=" }, 
                                      { fiCompPartName, "=" },
                                      { na                  } },  2, { fiEpisodeName,
                                                                       fiPartName,
                                                                       fiLang,
                                                                       na },            3,  0 },

   { "bycompname",  vtSelect, 0,    { { fiCompName,     "=" }, 
                                      { na                  } },  1, { fiCompPartName,
                                                                       fiLang,
                                                                       na },            2,  0 },

   { 0 }
};

//***************************************************************************
// Tvm Map Fields
//***************************************************************************

cDbService::FieldDef cTvmMapFields::fields[] =
{
   // name               format     index           type

   { "tvmid",            ffInt,     fiTvmId,        ftPrimary,    0 },
   { "channelname",      ffAscii,   fiChannelName,  ftPrimary,    0 },

   { "source",           ffAscii,   fiSource,       ftData,       0 },

   { "inssp",            ffInt,     fiInsSp,        ftMeta,       0 },
   { "updsp",            ffInt,     fiUpdSp,        ftMeta,       0 },

   { 0 }
};