db.py

Go to the documentation of this file.

#!/usr/bin/python
# -*- coding: utf-8 -*- 
 
import datetime
import logging 
import re
import json
import threading
import random
import sqlite3
import traceback
 
from pprint import pprint
 
from pafera.types import *
from pafera.utils import *
from pafera.validators import *
 
import pafera.modelbase
 
# Security access constants
DB_CAN_CREATE                   = 0x1
DB_CANNOT_CREATE                = 0x2
DB_CAN_CHANGE                   = 0x4
DB_CANNOT_CHANGE                = 0x8
DB_CAN_DELETE                   = 0x10
DB_CANNOT_DELETE                = 0x20
DB_CAN_VIEW                     = 0x40
DB_CANNOT_VIEW                  = 0x80
DB_CAN_LINK                     = 0x100
DB_CANNOT_LINK                  = 0x200
DB_CAN_CHANGE_PERMISSIONS       = 0x400
DB_CANNOT_CHANGE_PERMISSIONS    = 0x800
DB_CAN_VIEW_PROTECTED           = 0x1000
DB_CANNOT_VIEW_PROTECTED        = 0x2000
 
DB_CAN_ALL      = 0x555
DB_CANNOT_ALL   = 0xaaa
 
# dbflag constants
DB_DEBUG          = 0x01
DB_TRACK_CHANGES  = 0x02
DB_TRACK_VALUES   = 0x04
DB_TRACK_VIEW     = 0x08
DB_SECURE         = 0x10
DB_PRODUCTION     = 0x20
 
# *********************************************************************
class system_objtype(pafera.modelbase.ModelBase):
  """Bootstrap class for the database.
  
  This class keep tracks of every database model sent to 
  db.RegisterModel() and enables them to show up in the web database
  manager.
  """
  
  _dbfields     = {
    'typename': ('VARCHAR(255)', 'NOT NULL PRIMARY KEY',),
    'access':   ('INT', 'NOT NULL DEFAULT 0',),
    'aclid':    ('INT', 'NOT NULL DEFAULT 0',),
    'flags':    ('INT', 'NOT NULL DEFAULT 0',),
  }
  _dbdisplay  = ['typename']
  _dblinks    = []
  _dbflags    = 0
 
# *********************************************************************
class system_acl(pafera.modelbase.ModelBase):
  """Bootstrap class for the database.
  
  This class keeps track of ACL rules and exists to save space so that
  rules do not need to be stored in every object with security enabled.
  """
  
  _dbfields     = {
    'id':     ('INTEGER PRIMARY KEY AUTOINCREMENT', 'NOT NULL',),
    'rules':  ('DICT', "NOT NULL",),
  }
  _dbdisplay  = ['rules']
  _dblinks    = []
  _dbflags    = 0
 
# *********************************************************************
class system_config(pafera.modelbase.ModelBase):
  """Bootstrap class for the database.
  
  This class allows the database object itself to be used like a DB2
  key-value dict. Try 
  
  g.db['John'] = 'Cleese'
  
  print(g.db['John'])
  """
  
  _dbfields = {
    'dbkey':    ('VARCHAR(255)', 'NOT NULL PRIMARY KEY',),
    'dbvalue':  ('SINGLETEXT', 'NOT NULL',),
  }
  _dbdisplay  = ['dbkey', 'dbvalue']
  _dblinks    = []
  _dbflags    = 0
 
# *********************************************************************
class system_changelog(pafera.modelbase.ModelBase):
  """Bootstrap class for the database.
  
  This class keeps track of all changed values if you have enabled
  tracking in dbflags. Future plans involve using this class to enabled
  replication and rollbacks like the PHP version.
  """
  
  CREATED   = 1;
  MODIFIED  = 2;
  DELETED   = 3;
  RESTORED  = 4;
  ACCESSED  = 5;
  
  _dbfields   = {
    'id':         ('INT PRIMARY KEY', '',),
    'objtype':    ('INT', 'NOT NULL',),
    'eventtype':  (
      'ENUM',
      'NOT NULL',
      0,
      {
        'CREATED':    1,
        'MODIFIED':   2,
        'DELETED':    3,
        'RESTORED':   4,
        'ACCESSED':   5,
      }
    ),
    'userid':     ('INT', 'NOT NULL',),
    'eventtime':  ('BIGINT', 'NOT NULL',),
    'changed':    ('DICT', 'NOT NULL',),
    'ids':        ('NEWLINELIST', 'NOT NULL',),
  }
  _dbdisplay  = ['objtype', 'eventtype', 'eventtime', 'changed']
  _dblinks    = []
  _dbflags    = 0
 
# *********************************************************************
class system_tag(pafera.modelbase.ModelBase):
  """Bootstrap class for the database.
  
  Tagging system for every object in the system. Useful if you enjoy
  creating blogs or galleries.
  """
  
  _dbfields     = {
    'id':         ('INT PRIMARY KEY', '',),
    'languageid': ('INT', 'NOT NULL',),
    'tagname':    ('SINGLETEXT', 'NOT NULL',),
  }
  _dbindexes  = (
    ('unique', ('languageid', 'tagname',)),
  )
  _dbdisplay  = ['tagname']
  _dblinks    = []
  _dbflags    = 0
 
# *********************************************************************
class DB(object):
  """The main database object. Note that Pafera allows you to create as 
  many database objects as you want, so you can keep one part of your
  logic in a MySQL database, one part in an Sqlite database, and so 
  forth.
  
  The current access is saved in db.userid and db.groups. Root access
  can be enabled at any time by calling db.Sudo(), while debugging
  is enabled through db.Debug().
  """
  
  LANGUAGES = {
    1:  ('en', 'English', 'English'),
    2:  ('zh', 'Chinese', '中文'),
  }
 
  DBTYPES = {
    'sqlite':     1,
    'mysql':      2,
    'postgresql': 3,
  }
 
  CONNECTIONS = {}
  DEFAULTCONN = None
  DEFAULTFILE = "default.db"
 
  # ----------------------------------------------------------------------------------------
  def __init__(self, 
      connectionname  = None,
      dbname          = None, 
      dbhost          = 'localhost',
      dbtype          = 'sqlite',
      dbuser          = None,
      dbpassword      = None,
      dbflags         = DB_SECURE | DB_TRACK_CHANGES | DB_TRACK_VALUES,
      closeondelete   = 1
    ):
    """Basic database constructor.
    
    Note that security must be enabled on *both* the database and the model to take effect.
    
    Using closeondelete means that the object will automatically close the database
    connection when it goes out of scope.
    """
    
    self.debug    = 0
    self.language = 1
    self.objtypes = {}
    self.userid   = 0
    self.groups   = []
 
    self.numsudos       = 0
    self.numdebugs      = 0
    self.dbtype         = dbtype
    self.dbflags        = dbflags
    self.closeondelete  = closeondelete
    self.cursor         = 0
    self.closed         = 0
    self.acls           = {}
    
    if not connectionname:
      if not dbname:
        self.connectionname = 'default'
        self.dbname         = DB.DEFAULTFILE
      else:
        self.connectionname = dbname
        self.dbname         = dbname
 
    if dbtype == 'sqlite':
      import sqlite3
      self.dbconn = sqlite3.connect(dbname, 30)
      self.dbconn.row_factory = sqlite3.Row
      self.dbconn.execute("PRAGMA busy_timeout = 15000")
    elif dbtype == 'mysql':
      import mysql.connector as mysql
      
      self.dbconn  = mysql.connect(
        host      = dbhost,
        user      = dbuser,
        passwd    = dbpassword,
        database  = dbname,
      )
      
      self.dbconn.set_charset_collation('utf8mb4', 'utf8mb4_unicode_520_ci')
    elif dbtype == 'postgresql':
      import psycopg2 as postgresql
      
      self.dbconn   = postgresql.connect(
        host        = dbhost,
        user        = dbuser,
        password    = dbpassword,
        database    = dbname,
      )
    else: 
      raise Exception(f'Database type {dbtype} not supported')
 
    self.connectionname = connectionname
 
    DB.CONNECTIONS[connectionname]  = (dbname, dbtype, self)
 
    if not DB.DEFAULTCONN:
      DB.DEFAULTCONN  = self
    
    # Initialize system tables on first run
    try:
      for r in self.Execute("SELECT typename, access, aclid, flags FROM system_objtype"):
        self.objtypes[ r[0] ]  = (r[1], r[2], r[3]) 
        
      failed  = 0
    except Exception as e:
      self.RegisterModel(system_objtype)
      self.RegisterModel(system_acl)
      self.RegisterModel(system_config)
      self.RegisterModel(system_changelog)
      self.RegisterModel(system_tag)
      
      self.Commit()
      
      for r in self.Execute("SELECT typename, access, aclid, flags FROM system_objtype"):
        self.objtypes[ r[0] ]  = (r[1], r[2], r[3]) 
      
  # ----------------------------------------------------------------------------------------
  def __del__(self):
    """Standard destructor to automatically close the database connection
    """
    if self.closeondelete:
      self.Close()
  
  # ----------------------------------------------------------------------------------------
  def Close(self):
    """Close the database connection. You should normally never need to call this since the
    object will automatically close the connectino when it goes out of scope
    """
    if self.closed:
      return
    
    try:
      self.closed = 1
      self.dbconn.close()  
    except Exception as e:
      traceback.print_exc()
  
  # ----------------------------------------------------------------------------------------
  def Begin(self):
    """Begins a transaction. Useful if you don't use auto-commit mode.
    """
    if self.cursor:
      raise Exception("DB.Begin: Already in a transaction!")
    
    self.cursor = self.dbconn.cursor()
    
    if self.dbtype == 'mysql':
      self.dbconn.start_transaction()
    else:
      self.cursor.execute("BEGIN TRANSACTION")
    
  # ----------------------------------------------------------------------------------------
  def Commit(self):
    """Commits the current transaction. Be sure to call this at the end of your script or none
    of your changes will take effect.
    """
    self.dbconn.commit()
    
    if self.cursor:
      self.cursor.close()
      self.cursor = 0
    
  # ----------------------------------------------------------------------------------------
  def Rollback(self):
    """Rolls back the current transaction. This will also automatically be done if you end
    the script without calling db.Commit()
    """
    self.dbconn.rollback()
    
    if self.cursor:
      self.cursor.close()
      self.cursor = 0
    
  # ----------------------------------------------------------------------------------------
  def Execute(self, query, params = [], many = 0, returndict = 0):
    """Executes a query upon the database. Use db.Query() if you need a version which 
    automatically wraps the returned values into a dict. Use db.Debug() to see what is 
    being done behind the scenes.
    """
    
    if not (isinstance(params, list) or isinstance(params, tuple)):
      params  = (params,)
      
    query = query.strip()
    
    if self.dbtype ==  'mysql':
      query = query.replace('?', '%s')
    
    if self.numdebugs:
      print('DB: Executing query')
      pprint(query)
      print(f'with {len(params)} params')
      pprint(params)
    
    if self.cursor:
      c = self.cursor
    else:
      c = self.dbconn.cursor()
    
    if many:
      c.executemany(query, params)
    else:
      c.execute(query, params)
    
    if query.startswith('SELECT'):
      results = c.fetchall()
      
      if returndict:
        ls  = []
        
        if self.dbtype == 'mysql':
          columns = tuple([d[0] for d in c.description])
          
          for row in results:
            ls.append(dict(zip(columns, row)))
        else:
          for r in results:
            ls.append(dict(zip(r.keys(), r)))
          
        results = ls
      
    else:
      results = c.lastrowid
    
    if self.cursor:
      pass
    else:
      c.close()
    
    if self.numdebugs:
      if isinstance(results, list):
        Print(f'DB: Query results with length {len(results)}')
        pprint(results)
      else:
        Print(f'DB: Query results returned {results}')        
      
    return results
  
  # ----------------------------------------------------------------------------------------
  def ExecuteMany(self, query, params = []):
    """Convenience function to execute many statements at once. Separated into its own function 
    for more readable code.
    """
    return self.Execute(query, params, many = 1)    
  
  # ----------------------------------------------------------------------------------------
  def Query(self, query, params = []):    
    """Convenience function to execute many statements at once. Separated into its own function 
    for more readable code.
    """
    return self.Execute(query, params, many = 0, returndict = 1)    
    
  # ----------------------------------------------------------------------------------------
  def Sudo(self, state):
    """Enable or disable administrator mode, bypassing security for the current user.
    """
    if state:
      self.numsudos += 1
    elif self.numsudos > 0:
      self.numsudos -= 1
               
  # ----------------------------------------------------------------------------------------
  def Debug(self, state):
    """Enable or disable query debugging
    """
    if state:
      self.numdebugs  += 1
    elif self.numdebugs > 0:
      self.numdebugs  -= 1
    
  # ----------------------------------------------------------------------------------------
  def RequireGroup(self, groupname, errmsg):    
    """Throws an exception if the current user does not belong to one of the groups listed 
    in groupname. It's a less expensive check than going through object security.
    """
    if isinstance(groupname, list):
      found = 0
      
      for g in groupname:
        if g in self.groups:
          found = 1
          break
      
      if not found:
        raise Exception(errmsg)
    else:
      if groupname not in self.groups:
        raise Exception(errmsg)
  
  # ----------------------------------------------------------------------------------------
  def HasGroup(self, groupname):    
    """Returns true if the current user belongs to the given group
    """
    return groupname in self.groups
  
  # ----------------------------------------------------------------------------------------
  def GetAccess(self, obj):   
    """Utility function to calculate the current user's access to the given database object.
    It's only effective if security is enabled on both the database and the object's model.
    """
    if self.numsudos or 'admins' in self.groups or (hasattr(obj, 'dbowner') and obj.dbowner == self.userid):
      return DB_CAN_ALL
    
    typename, access, aclid  = self.objtypes[obj._dbtable]
    
    access  = self.ApplyACL(access, aclid)
    
    if hasattr(obj, 'dbaccess'):
      access  = self.ApplyAccess(access, obj.dbaccess)
      
    if hasattr(obj, 'dbgroupaccess') and getattr(obj, 'dbgroup') in self.groupids:
      access  = self.ApplyAccess(access, obj.groupaccess)
      
    if hasattr(obj, 'dbaclid'):
      access  = self.ApplyACL(access, obj.dbaclid)
      
    return access
    
  # ----------------------------------------------------------------------------------------
  def GetACL(self, aclid):    
    """Get the rules for the given ACL. You should not need to call this yourself since it's
    automatically handled by db.GetAccess()
    """
    acl = {}
    
    if aclid not in self.acls:
      acl = self.Execute('SELECT rules FROM system_acl WHERE id = ?', aclid)[0]      
      self.acls[aclid]  = json.loads(acl) if acl else {}
      
    return acl
    
  # ----------------------------------------------------------------------------------------
  def ApplyACL(self, access, aclid):    
    """Get the rules for the given ACL. You should not need to call this yourself since it's
    automatically handled by db.GetAccess()
    """
    if not aclid:
      return access
    
    acl = self.GetACL(aclid)
    
    if 'groups' in acl:
      for k, v in acl['groups'].items():
        if k in self.groups:        
          access  = self.ApplyAccess(access, v)
        
    if 'users' in acl:
      for k, v in acl['users'].items():
        if k == self.userid:
          access  = self.ApplyAccess(access, v)
    
    return access
    
  # ----------------------------------------------------------------------------------------
  def ApplyAccess(self, origaccess, newaccess):   
    """Utility function to calculate access control. You should not need to call this yourself 
    since it's automatically handled by db.GetAccess()
    """
    if newaccess & DB_CAN_CREATE:
      origaccess  = origaccess & (~DB_CANNOT_CREATE) | DB_CAN_CREATE
    elif newaccess & DB_CANNOT_CREATE:
      origaccess  = origaccess & (~DB_CAN_CREATE) | DB_CANNOT_CREATE
      
    if newaccess & DB_CAN_CHANGE:
      origaccess  = origaccess & (~DB_CANNOT_CHANGE) | DB_CAN_CHANGE
    elif newaccess & DB_CANNOT_CHANGE:
      origaccess  = origaccess & (~DB_CAN_CHANGE) | DB_CANNOT_CHANGE
      
    if newaccess & DB_CAN_VIEW:
      origaccess  = origaccess & (~DB_CANNOT_VIEW) | DB_CAN_VIEW
    elif newaccess & DB_CANNOT_VIEW:
      origaccess  = origaccess & (~DB_CAN_VIEW) | DB_CANNOT_VIEW
      
    if newaccess & DB_CAN_DELETE:
      origaccess  = origaccess & (~DB_CANNOT_DELETE) | DB_CAN_DELETE
    elif newaccess & DB_CANNOT_DELETE:
      origaccess  = origaccess & (~DB_CAN_DELETE) | DB_CANNOT_DELETE
      
    if newaccess & DB_CAN_LINK:
      origaccess  = origaccess & (~DB_CANNOT_LINK) | DB_CAN_LINK
    elif newaccess & DB_CANNOT_LINK:
      origaccess  = origaccess & (~DB_CAN_LINK) | DB_CANNOT_LINK
      
    if newaccess & DB_CAN_CHANGE_PERMISSIONS:
      origaccess  = origaccess & (~DB_CANNOT_CHANGE_PERMISSIONS) | DB_CAN_CHANGE_PERMISSIONS
    elif newaccess & DB_CANNOT_CHANGE_PERMISSIONS:
      origaccess  = origaccess & (~DB_CAN_CHANGE_PERMISSIONS) | DB_CANNOT_CHANGE_PERMISSIONS    
      
    if newaccess & DB_CAN_VIEW_PROTECTED:
      origaccess  = origaccess & (~DB_CANNOT_VIEW_PROTECTED) | DB_CAN_VIEW_PROTECTED
    elif newaccess & DB_CANNOT_VIEW_PROTECTED:
      origaccess  = origaccess & (~DB_CAN_VIEW_PROTECTED) | DB_CANNOT_VIEW_PROTECTED
      
    return origaccess
    
  # ------------------------------------------------------------------
  def RegisterModel(self, model):
    """All models must be registered before use, which means creating the
    table, indexes, and storing a record of the model for use in the 
    web database management interface.
    
    You normally do not need to call this function itself since any 
    database function like db.Find(), db.Load(), and db.Save() will
    automatically do it for you.
    """
    
    # If model is an instance, then work on the model itself
    if isinstance(model, pafera.modelbase.ModelBase):
      model = model.__class__
    
    # Model has already been registered
    if V(model, '_dbinit'):
      return
    
    # Automatically create the table name from the apppath and model names
    if not V(model, '_dbtable'):
      if V(model, '_apppath'):
        model._dbtable  = model._apppath.replace('/', '_') + '_' + model.__name__.lower()
      else:
        model._dbtable  = model.__name__.lower()
        
    # Find the primary key field for quick searches
    if 'id' in model._dbfields:
      model._dbid  = 'id'
    elif 'rid' in model._dbfields:
      model._dbid  = 'rid'
    elif 'rbid' in model._dbfields:
      model._dbid  = 'rbid'
    else:
      model._dbid = 0
      
      for k, v in model._dbfields.items():
        if v[1].find('PRIMARY KEY') != -1:
          model._dbid  = k
          break
    
    if not hasattr(model, '_dbflags'):
      model._dbflags = 0
 
    model._dbinit = 1
    
    if model._dbtable in self.objtypes.keys():
      return
    
    # Create table if necessary
    fields    = []
    
    for k, v in model._dbfields.items():
      fields.append(k + ' ' + self.MapFieldType(v[0]) + ' ' + v[1])
 
    if model._dbflags & DB_SECURE:
      fields.append('dbowner INT NOT NULL DEFAULT 0')
      fields.append('dbgroup INT NOT NULL DEFAULT 0')
      fields.append('dbaccess INT NOT NULL DEFAULT 0')
      fields.append('dbgroupaccess INT NOT NULL DEFAULT 0')
      fields.append('dbaclid INT NOT NULL DEFAULT 0')
      
    fields  = ','.join(fields)
 
    try:
      if self.dbtype == 'mysql':
        self.Execute(f"CREATE TABLE {model._dbtable}({fields}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;")
      else:
        self.Execute(f"CREATE TABLE {model._dbtable}({fields})")
    except Exception as e:
      # Skip exception if table already exists but was somehow not properly 
      # stored in objtypes
      if 'exists' not in str(e):
        raise Exception from e
    
    # Create indexes
    if V(model, '_dbindexes'):
      indexnum  = 1
      
      for r in model._dbindexes:
        indextype     = r[0]
        indexcolumns  = r[1]
        
        if not isinstance(indexcolumns, tuple):
          indexcolumns  = [indexcolumns]
      
        if self.dbtype == 'mysql':
            columns = []
            
            for c in indexcolumns:
              fielddef  = model._dbfields[c][0]
              
              if (fielddef.find('TEXT') != -1
                or fielddef.find('DICT') != -1
                or fielddef.find('LIST') != -1
                or fielddef.find('TRANSLATION') != -1
                or fielddef.find('FILE') != -1
              ):
                columns.append(c + '(255)')
              else:
                columns.append(c)
                
            columns = ', '.join(columns)
        else:
            columns = ', '.join(indexcolumns)
        
        self.Execute(f"CREATE {indextype} INDEX {model._dbtable}_index{indexnum} ON {model._dbtable}({columns})")
        indexnum  += 1
        
    # Register the model into the system for use in the web database manager
    if model._dbtable != 'system_objtype':
      self.Sudo(1)
      
      newtype = system_objtype()
      newtype.Set(
        typename  =  model._dbtable,
        access    =  DB_CANNOT_DELETE 
                        | DB_CANNOT_CHANGE
                        | DB_CANNOT_LINK
                        | DB_CANNOT_CHANGE_PERMISSIONS
                        | DB_CANNOT_VIEW_PROTECTED,
        aclid     = 0,
        flags     = 0,
      )
      
      self.Insert(newtype)
      self.Commit()
 
      self.Sudo(0)
      
      self.objtypes[model._dbtable] = newtype.ToJSON()
    
    return model
  
  # ------------------------------------------------------------------
  def UnregisterModel(self, model):
    """Removes the model from the system registry.
    
    Useful if you're uninstalling an app.
    """
    model = self.RegisterModel(model)
    
    self.Execute('DELETE FROM system_objtype WHERE typename = ?', model)
    self.Commit();
    
  # ------------------------------------------------------------------
  def MapFieldType(self, fieldtype):
    """This function is responsible for mapping python types to their
    database representations. If you are using a database outside of
    MySQL or SQLite, be sure to add the conversions here.
    """
    replacements  = []
    
    if self.dbtype == 'mysql':
      replacements  = (
        ('AUTOINCREMENT',       'AUTO_INCREMENT'),
        ('BIGINT PRIMARY KEY',  'BIGINT PRIMARY KEY'),
        ('INT PRIMARY KEY',     'INT PRIMARY KEY'),
        ('INT64',               'BIGINT'),
        ('INT32',               'INT'),
        ('INT16',               'SMALLINT'),
        ('INT8',                'TINYINT'),
        ('ENUM',                'INT'),
        ('BITFLAGS',            'INT'),
        ('DATETIME',            'BIGINT'),
        ('DICT',                'TEXT'),
        ('LIST',                'TEXT'),
        ('SINGLETEXT',          'TEXT'),
        ('MULTITEXT',           'TEXT'),
        ('PASSWORD',            'TEXT'),
        ('IMAGEFILE',           'TEXT'),
        ('SOUNDFILE',           'TEXT'),
        ('VIDEOFILE',           'TEXT'),
        ('SYSTEMFILE',          'TEXT'),
        ('NEWLINELIST',         'TEXT'),
        ('PROTECTED',           ''),
        ('PRIVATE',             ''),
        ('TRANSLATION',         'TEXT'),
      )
    elif self.dbtype == 'postgresql':
      replacements  = (
        ('AUTOINCREMENT',       'AUTO_INCREMENT'),
        ('BIGINT PRIMARY KEY',  'BIGINT PRIMARY KEY'),
        ('INT PRIMARY KEY',     'INT PRIMARY KEY'),
        ('INT64',               'BIGINT'),
        ('INT32',               'INT'),
        ('INT16',               'SMALLINT'),
        ('INT8',                'TINYINT'),
        ('ENUM',                'INT'),
        ('BITFLAGS',            'INT'),
        ('DATETIME',            'BIGINT'),
        ('DICT',                'TEXT'),
        ('LIST',                'TEXT'),
        ('SINGLETEXT',          'TEXT'),
        ('MULTITEXT',           'TEXT'),
        ('PASSWORD',            'TEXT'),
        ('IMAGEFILE',           'TEXT'),
        ('SOUNDFILE',           'TEXT'),
        ('VIDEOFILE',           'TEXT'),
        ('SYSTEMFILE',          'TEXT'),
        ('NEWLINELIST',         'TEXT'),
        ('PROTECTED',           ''),
        ('PRIVATE',             ''),
        ('TRANSLATION',         'TEXT'),
      )
    elif self.dbtype == 'sqlite':
      replacements  = (
        ('INT64',             'INT'),
        ('INT32',             'INT'),
        ('INT16',             'INT'),
        ('INT8',              'INT'),
        ('ENUM',              'INT'),
        ('BITFLAGS',          'INT'),
        ('DATETIME',          'INT'),
        ('DICT',                'TEXT'),
        ('LIST',                'TEXT'),
        ('SINGLETEXT',        'TEXT'),
        ('MULTITEXT',         'TEXT'),
        ('PASSWORD',          'TEXT'),
        ('IMAGEFILE',         'TEXT'),
        ('SOUNDFILE',         'TEXT'),
        ('VIDEOFILE',         'TEXT'),
        ('SYSTEMFILE',        'TEXT'),
        ('NEWLINELIST',            'TEXT'),
        ('PROTECTED',         ''),
        ('PRIVATE',           ''),
        ('TRANSLATION',       'TEXT'),
      )
      
    for r in replacements:
      fieldtype = fieldtype.replace(r[0], r[1])
      
    return fieldtype      
    
  # ---------------------------------------------------------------------------------------
  def HasTable(self, table):
    """Quick utility function to see if a table exists. 
    
    For model tables, the same functionality could be achieved by checking db._objtypes,
    but there is always the possibility that another script has created a table between
    the initialization of the database object and the current running code, so it's 
    always best to be sure by directly querying the database itself.
    """
    try:
      self.Execute(f"SELECT * FROM {table} LIMIT 1")
      return True
    except Exception as e:
      return False
    
  # ------------------------------------------------------------------
  def __getitem__(self, key, value = None):
    """Utility function to allow the database object itself to work like 
    a big DB2 store.
    """
    r = self.Execute("SELECT value FROM system_config WHERE key = ?", key)
    
    if r != None:     
      return r[0]
        
    return None
    
  # ------------------------------------------------------------------
  def __setitem__(self, key, value):
    """Utility function to allow the database object itself to work like 
    a big DB2 store.
    """
    self.Query("REPLACE INTO system_config(key, value) VALUES(?, ?)", [key, value])
    
  # ------------------------------------------------------------------
  def Insert(self, obj):
    """db.Insert(), db.Update(), and db.Replace() are all convenient
    wrappers to the underlying db.Save() function for easier
    readability of code.
    """
    return self.Save(obj, 'insert')
    
  # ------------------------------------------------------------------
  def Update(self, obj):
    """db.Insert(), db.Update(), and db.Replace() are all convenient
    wrappers to the underlying db.Save() function for easier
    readability of code.
    """
    return self.Save(obj, 'update')
    
  # ------------------------------------------------------------------
  def Replace(self, obj):
    """db.Insert(), db.Update(), and db.Replace() are all convenient
    wrappers to the underlying db.Save() function for easier
    readability of code.
    """
    return self.Save(obj, 'replace')
    
  # ------------------------------------------------------------------
  def Save(self, obj, method = ''):
    """This is the function where we convert python types
    and store them into the database.
    
    Any fields named "rid" or "rbid" will be automatically given a 
    randomized non-existing value. You can check its value on the 
    object after this function returns.
    
    Security fields will be automatically added if security is enabled
    for both the database and the object's model.
    """
    
    # No need to update objects which haven't changed
    if not obj._changed:
      return
    
    self.RegisterModel(obj.__class__)
    
    dbid          = obj._dbid
    dbidvalue     = getattr(obj, dbid, None)
    autoincrement = 0
    
    # Select method if not specified.
    # Note that REPLACE is not used since some databases will set all 
    # non-specified fields to defaults, wiping out existing data.
    if not method:
      if dbidvalue:
        method  = 'update'
      else:
        method  = 'insert'
    
    # Check if the current user has permission to save
    enablesecurity  = obj.__class__._dbflags & DB_SECURE
    
    if enablesecurity:
      access    = self.GetAccess(obj)
      cancreate = access & DB_CAN_CREATE
      canchange = access & DB_CAN_CHANGE
      
      if (
          ((method == 'insert') and not cancreate)
          or ((method == 'update') and not canchange)
          or ((method == 'replace') and not canchange and not cancreate)
        ):
        raise Exception("DB.Save: Permission denied")
    
    
    if obj._dbfields[dbid][0].find('AUTOINCREMENT') != -1:
      autoincrement = 1
    
    # Find a new RID or RBID if needed
    if (
        (method == 'insert' or method == 'replace')
        and not dbidvalue
        and (dbid == 'rid' or dbid == 'rbid')
      ):
      newid = 0
      
      for i in range(0, 100):
        if dbid == 'rid':
          newid = MakeRID()
        else:
          newid = MakeRBID()
        
        r = self.Execute(f"SELECT {dbid} FROM {obj._dbtable} WHERE {dbid} = ?", newid)
        
        if r:
          newid = 0
          continue
        else:
          setattr(obj, dbid, newid)
          break
        
      if not newid:
        raise Exception(f"DB.Insert(): Could not find new random ID for table {obj._dbtable}")
        
    keys    = []
    values  = []
    
    changedkeys = obj._changed.keys()
    
    # Convert python types to database types
    for k, v in obj._dbfields.items():
      if k not in changedkeys and k != dbid:
        continue
      
      if k == dbid:
        # Let the database handle autoincrement IDs
        if (
          (autoincrement and not dbidvalue)
          or (method == 'update')
        ):
          continue
      
      keys.append(k)
      
      value = getattr(obj, k, None)
      
      if v[0].find('PRIMARY KEY') != -1:
        values.append(value)
      elif v[0].find('INT') != -1:
        values.append(int(value if value else 0))
      elif v[0].find('FLOAT') != -1:
        values.append(float(value if value else 0))
      elif v[0] == 'NEWLINELIST':
        values.append('\n'.join(value) if value else '')
      elif v[0] == 'DICT' or v[0] == 'TRANSLATION':
        values.append(json.dumps(value if value else {}))
      elif v[0] == 'LIST':
        values.append(json.dumps(value if value else []))
      elif v[0] == 'DATETIME':
        values.append(value.timestamp() if value else 0)
      else:
        values.append(value)
    
    parameters = ', '.join('?' * len(keys))
    
    # Add security fields if enabled
    if enablesecurity:
      keys.append('dbowner')
      values.append(getattr(obj, 'dbowner', self.userid))
      
      keys.append('dbgroup')
      values.append(getattr(obj, 'dbgroup', 0))
      
      keys.append('dbaccess')
      values.append(getattr(obj, 'dbaccess', 0))
      
      keys.append('dbgroupaccess')
      values.append(getattr(obj, 'dbgroupaccess', 0))
      
      keys.append('dbaclid')
      values.append(getattr(obj, 'dbaclid', 0))        
    
    # Execute queries
    if method == 'insert' or method == 'replace':
      needcursor  = not self.cursor
      
      if needcursor:
        self.cursor = self.dbconn.cursor()
      
      if method == 'insert':
        self.Execute(f"INSERT INTO {obj._dbtable}({', '.join(keys)}) VALUES({parameters})", values)
      else:
        self.Execute(f"REPLACE INTO {obj._dbtable}({', '.join(keys)}) VALUES({parameters})", values)
      
      if dbid != 'rid' and dbid != 'rbid':
        if self.cursor.lastrowid:
          setattr(obj, dbid, self.cursor.lastrowid)
      
      if needcursor:
        self.cursor.close()
        self.cursor = 0
    elif method == 'update':
      if not dbidvalue:
        raise Exception(f"DB.Update(): Cannot update {obj._dbtable} since no ID was provided")
      
      ls  = []
      
      ls.append("UPDATE " + obj._dbtable + " SET ")
      
      for i in range(0, len(keys) - 1):
        if keys[i] == dbid:
          continue
        
        ls.append(keys[i] + ' = ?, ')
        
      ls.append(f" {keys[len(keys) - 1]} = ? WHERE {dbid} = ?")
      
      values.append(dbidvalue)
      
      self.Execute('\n'.join(ls), values)
    else:
      raise Exception(f"Unknown save method {method}")
    
    return self
  
  # ------------------------------------------------------------------
  def Load(self, model, objid, fields = '*'):
    """Loads an object given its ID. Like many other database designers,
    I consider giving every row its own ID value to be convenient 
    despite the added space requirements.
    """
    self.RegisterModel(model)
    
    enablesecurity  = model._dbflags & DB_SECURE
    
    if enablesecurity and fields != '*':
      fields  +=  ', dbowner, dbgroup, dbaccess, dbgroupaccess, dbaclid'
    
    r = self.Query(f"SELECT {fields} FROM {model._dbtable} WHERE {model._dbid} = ?", objid)
    
    if not r:
      raise Exception(f"No object with id {getattr(model, model._dbid, '[blank]')} found in {model._dbtable}")
    
    o = model()
    
    r = r[0]
    r['_updatechanged'] = 0
    o.Set(**r)
    
    if enablesecurity:
      access    = self.GetAccess(o)
      
      if not (access & DB_CAN_VIEW):
        raise Exception("DB.Load: Permission denied")
    
    return o
  
  # ------------------------------------------------------------------
  def LoadMany(self, model, ids, fields = '*'):
    """Does the same thing as db.Load(), only you can load a number of 
    objects at once. This is separated into its own function since
    it actually involves different syntax in returning an array versus
    returning a single object.
    """
    self.RegisterModel(model)
    
    enablesecurity  = model._dbflags & DB_SECURE
    
    if enablesecurity and fields != '*':
      fields  +=  ', dbowner, dbgroup, dbaccess, dbgroupaccess, dbaclid'
      
    questionmarks = ['?' for x in ids]
    
    return self.Find(
        model, 
        f'WHERE {model._dbid} IN (' + ', '.join(questionmarks) + ')',
        ids,
        fields  = fields,
        limit   = 99999999,
      )
  
  # ------------------------------------------------------------------
  def Find(self, 
      model, 
      cond    = '',
      params  = [],
      **kwargs
    ):
    """The Pafera equivalent to SQL SELECT. All it does is to pass the
    parameters to DBList()'s constructor. 
    
    cond is the filter such as WHERE or HAVING 
    
    params is a list of parameters to be used in the query. SQL
    injections are still a thing in 2016, so if you're still 
    manually constructing queries, be sure that you know what you're 
    doing.
    
    Other available parameters are fields, start, limit, and orderby.
    They do pretty much what you think they do in constructing the
    underlying SQL query.
    """
    self.RegisterModel(model)
    
    kwargs['cond']    = cond
    kwargs['params']  = params
    
    return pafera.modelbase.DBList(self, model, **kwargs)
  
  # ------------------------------------------------------------------
  def Delete(self, 
      model, 
      cond    = '', 
      params  = [],
    ):
    """Deletes the given object or objects of the same type matching cond and params.
    
    As with any deletion function, make sure that you're *really* sure before
    using this. If in doubt, enable DB_TRACK_VALUES on this model so that you can
    get the object back if you've made a mistake.
    """
    self.RegisterModel(model)
    
    enablesecurity  = model._dbflags & DB_SECURE
    
    if enablesecurity:
      if cond:
        for r in self.Find(model, cond, params):
          access  = self.GetAccess(r)
          
          if access & DB_CAN_DELETE:
            self.Execute(f"DELETE FROM {model._dbtable} WHERE {model._dbid} = ?", r[model._dbid])
      elif getattr(model, model._dbid, None):
        access  = self.GetAccess(model)
        
        if access & DB_CAN_DELETE:
          self.Execute(f"DELETE FROM {model._dbtable} WHERE {model._dbid} = ?", getattr(model, model._dbid))
      else:
        for r in self.Find(model):
          access  = self.GetAccess(r)
          
          if access & DB_CAN_DELETE:
            self.Execute(f"DELETE FROM {model._dbtable} WHERE {model._dbid} = ?", r[model._dbid])
    else:
      if cond:
        self.Execute(f"DELETE FROM {model._dbtable} " + cond, params)
      elif getattr(model, model._dbid, None):
        self.Execute(f"DELETE FROM {model._dbtable} WHERE {model._dbid} = ?", getattr(model, model._dbid))
      else:
        self.Execute(f"DELETE FROM {model._dbtable}")
    
  # ------------------------------------------------------------------
  def GetLinkTable(self, obj1, obj2):
    """Utility function to get the name of the database table between
    two objects. 
  
    Use this if you need to do any manual operations on link tables.
    """
    self.RegisterModel(obj1)
    self.RegisterModel(obj2)
    
    # Ensure that the table name is always the same between two objects, 
    # thus removing the need for bidirectional links
    if obj1._dbtable > obj2._dbtable:
      temp  = obj1
      obj1  = obj2
      obj2  = temp
      
    return obj1._dbtable + '__' + obj2._dbtable   
  
  # ------------------------------------------------------------------
  def CreateLinkTable(self, obj1, obj2):
    """Creates the link table if necessary. 
    """
    
    linktable = self.GetLinkTable(obj1, obj2)
    
    if not self.HasTable(linktable):
      aidtype   = self.MapFieldType(obj1._dbfields[obj1._dbid][0]).replace('PRIMARY KEY', '').replace('AUTOINCREMENT', '').replace('AUTO_INCREMENT', '')
      
      bidtype   = self.MapFieldType(obj2._dbfields[obj2._dbid][0]).replace('PRIMARY KEY', '').replace('AUTOINCREMENT', '').replace('AUTO_INCREMENT', '')
      
      self.Execute(f"""CREATE TABLE {linktable}(
        linkaid       {aidtype} NOT NULL,
        linkbid       {bidtype} NOT NULL,
        linktype      INT NOT NULL,
        linknum       INT NOT NULL,
        linkcomment   TEXT NOT NULL,
        linkflags     INT NOT NULL
      )""") 
      self.Execute(f"CREATE UNIQUE INDEX {linktable}_index1 ON {linktable}(linkaid, linkbid)")
      self.Commit()
      
    return linktable
      
  # ------------------------------------------------------------------
  def Link(self, 
      obj1, 
      obj2,
      linktype  = 0,
      linknum   = 0,
      comment   = '',
      flags     = 0,
    ):
    """Linking is one of the fundamental concepts in Pafera. It's essentially 
    a foreign key mechanism that can link two arbitary objects via their
    database IDs, thus you can do things like
    
    u = new user
    g = new group
    
    # Link the user and group
    db.Link(u, g)
    
    # prints g's information
    print(db.Linked(u, group))
    
    Linking is a bidirectional operation, meaning that both 
    db.Linked(u, group) and db.Linked(g, user) will work. For unidirectional
    links, it's suggested to store the linked IDs in a NEWLINELIST field
    in an object itself.
    
    Behind the scenes, linking works by create a separate table that connects 
    IDs between one table and other. Any attempt to find links are returned
    in a DBList() object which performs a SQL JOIN for one query results.
    
    Linking supports different types of links via the linktype parameter.
    
    The linknum parameter is used to create results that are returned in 
    order of linknum, guaranteeing that the same sequence will be returned
    every time. 
    
    flags can be used to store application-specific meanings outside of 
    linktype. 
    """
    
    if obj1._dbtable > obj2._dbtable:
      temp  = obj1
      obj1  = obj2
      obj2  = temp
      
    linktable = self.CreateLinkTable(obj1, obj2)
    
    enablesecurity  = (obj1.__class__._dbflags & DB_SECURE) or (obj2.__class__._dbflags & DB_SECURE)
    
    if enablesecurity:
      access1 = self.GetAccess(obj1)
      access2 = self.GetAccess(obj2)
      
      if (access1 & DB_CANNOT_LINK) or (access2 & DB_CANNOT_LINK):
        raise Exception("DB.Link: Permission denied")
    
    obj1id  = getattr(obj1, obj1._dbid)
    obj2id  = getattr(obj2, obj2._dbid)
    
    if not obj1id or not obj2id:
      raise Exception("DB.Link: Missing object IDs")
        
    self.Execute(f"""REPLACE INTO {linktable}(
        linkaid, 
        linkbid, 
        linktype, 
        linknum, 
        linkcomment, 
        linkflags
      ) VALUES(?, ?, ?, ?, ?, ?)
      """,
      (
        obj1id,
        obj2id,
        linktype,
        linknum,
        comment,
        flags,
      )
    )
      
  # ------------------------------------------------------------------
  def LinkArray(self, 
      obj1, 
      obj2list,
      linktype  = 0,
      comment   = '',
      flags     = 0,
    ):
    """A version of db.Link() that will erase all previous links of the
    same type and store the given links as an array. This is useful for
    representing tree diagrams from the normally flat SQL architecture
    outside of advanced usage in PostgreSQL and the sort.
    """
    
    obj2  = obj2list[0]
    
    self.RegisterModel(obj1)
    self.RegisterModel(obj2)
    
    enablesecurity  = (obj1.__class__._dbflags & DB_SECURE) or (obj2.__class__._dbflags & DB_SECURE)
    
    if enablesecurity:
      access1 = self.GetAccess(obj1)
      access2 = self.GetAccess(obj2)
      
      if (access1 & DB_CANNOT_LINK) or (access2 & DB_CANNOT_LINK):
        raise Exception("DB.Link: Permission denied")
      
    linktable = self.CreateLinkTable(obj1, obj2)
    
    params    = []
    
    obj1id    = getattr(obj1, obj1._dbid)
    obj2dbid  = obj2._dbid
    linknum = 0
    
    if not obj1id or not obj2dbid:
      raise Exception("DB.LinkArray: Missing object IDs")
        
    for obj2 in obj2list:
      params.append(
        (
          obj1id,
          getattr(obj2, obj2dbid),
          linktype,
          linknum,
          comment,
          flags,
        )
      )
      linknum += 1
    
    if obj1._dbtable > obj2._dbtable:
      self.Execute(f"""DELETE FROM {linktable}
        WHERE linkbid = ? and linktype = ?
        """,
        (obj1id, linktype)
      )
      self.ExecuteMany(f"""REPLACE INTO {linktable}(
          linkbid, 
          linkaid, 
          linktype, 
          linknum, 
          linkcomment, 
          linkflags
        ) VALUES(?, ?, ?, ?, ?, ?)
        """,
        params
      )
    else:
      self.Execute(f"""DELETE FROM {linktable}
        WHERE linkaid = ? and linktype = ?
        """,
        (obj1id, linktype)
      )
      self.ExecuteMany(f"""REPLACE INTO {linktable}(
          linkaid, 
          linkbid, 
          linktype, 
          linknum, 
          linkcomment, 
          linkflags
        ) VALUES(?, ?, ?, ?, ?, ?)
        """,
        params
      )
    
  # ------------------------------------------------------------------
  def Unlink(self, 
      obj1, 
      obj2,
      linktype  = 0,
    ):    
    """Unlink two objects, or if obj2 is a model, unlink *every* 
    object of the given linktype. Use with caution.
    """
    
    self.RegisterModel(obj1)
    self.RegisterModel(obj2)
    
    # If model is an instance, then work on the model itself
    if isinstance(obj2, pafera.modelbase.ModelBase):
      model2  = obj2.__class__
    else:
      model2  = obj2
      
    enablesecurity  = (obj1.__class__._dbflags & DB_SECURE) or (obj2._dbflags & DB_SECURE)
    
    if enablesecurity:
      access1 = self.GetAccess(obj1)
      access2 = self.GetAccess(obj2)
      
      if (access1 & DB_CANNOT_LINK) or (access2 & DB_CANNOT_LINK):
        raise Exception("DB.Link: Permission denied")
      
    linktable = self.CreateLinkTable(obj1, obj2)
    
    if isinstance(obj2, pafera.modelbase.ModelBase):
      self.Execute(f"""DELETE FROM  {linktable}
        WHERE linkaid = ? AND linkbid = ? AND linktype = ?
        """,
        (
          getattr(obj1, obj1._dbid),
          getattr(obj2, obj2._dbid),
          linktype,
        )
      )
    else:
      self.Execute(f"""DELETE FROM  {linktable}
        WHERE linkaid = ? AND linktype = ?
        """,
        (
          getattr(obj1, obj1._dbid),
          linktype,
        )
      )
      
  # ------------------------------------------------------------------
  def HasLink(self, 
      model1, 
      model1id,
      model2,
      model2id,
      linktype  = 0,
    ):
    """Convenience function to check if two objects are linked
    without actually loading the objects themselves. Commonly used
    to check for membership in a group.
    """
    
    linktable = self.CreateLinkTable(model1, model2)
    
    if model1._dbtable > model2._dbtable:
      r = self.Execute(f"""SELECT linkaid FROM {linktable}
        WHERE linkaid = ? AND linkbid = ? AND linktype = ?""",
        (model2id, model1id, linktype)
      )
    else:
      r = self.Execute(f"""SELECT linkaid FROM {linktable}
        WHERE linkaid = ? AND linkbid = ? AND linktype = ?""",
        (model1id, model2id, linktype)
      )
      
    return 1 if r else 0
  
  # ------------------------------------------------------------------
  def Linked(self, 
      obj1, 
      model2,
      linktype  = 0,
      start     = 0,
      limit     = 1000,
      fields    = '*',
      orderby   = '',
      cond      = '',
      params    = [],
    ):
    """Return the list of objects linked to this object with the given 
    linktype. Note that this function returns a DBList(), so if you have
    many objects linked, it will automatically separate them into chunks
    while iterating through them.
    """
    
    self.RegisterModel(obj1.__class__)
    self.RegisterModel(model2)
    
    if obj1.__class__._dbflags & DB_SECURE:
      access1 = self.GetAccess(obj1)
      
      if not access1 & DB_CAN_LINK:
        raise Exception('DB.Linked: Permission denied')
    
    linktable = self.CreateLinkTable(obj1, model2)
    
    questionmarks = []
    model2ids     = []
    
    obj1id    = getattr(obj1, obj1._dbid)
    obj2dbid  = model2._dbid
    
    totalcount  = 0
    
    if obj1._dbtable > model2._dbtable:
      joinclause  = f'JOIN {linktable} ON linkaid = ' + obj2dbid
      
      if not orderby:
        orderby     = 'linknum, linkaid'
      
      newparams  = [obj1id, linktype]
      
      if cond:
        cond  = 'WHERE linkbid = ? AND linktype = ? AND (' + cond + ')'
        newparams += params
      else:
        cond  = 'WHERE linkbid = ? AND linktype = ?'
    else:
      joinclause  = f'JOIN {linktable} ON linkbid = ' + obj2dbid
      
      if not orderby:
        orderby     = 'linknum, linkbid'
      
      newparams  = [obj1id, linktype]
      
      if cond:
        cond  = 'WHERE linkaid = ? AND linktype = ? AND (' + cond + ')'
        newparams += params
      else:
        cond  = 'WHERE linkaid = ? AND linktype = ?'
        
    cond  = joinclause + ' ' + cond
    
    return pafera.modelbase.DBList(
      self, 
      model2, 
      cond      = cond,
      params    = newparams, 
      start     = start,
      limit     = limit,
      orderby   = orderby,
    )
      
  # ------------------------------------------------------------------
  def LinkedToID(self, 
      model1, 
      model1id,
      model2,
      linktype  = 0,
      start     = 0,
      limit     = 1000,
      fields    = '*',
      orderby   = '',
      cond      = '',
      params    = [],
    ):
    """Convenience function for checking what objects are linked to 
    model1id without having to load the object itself.
    """
    
    self.RegisterModel(model1)
    
    obj1  = model1()
    
    setattr(obj1, obj1._dbid, model1id)
    
    return self.Linked(obj1, model2, linktype, start, limit, fields, orderby, cond, params)