-- Table DB module
--[[
The MIT License (MIT)

Copyright (c) 2014 Milind Gupta milind.gupta@gmail.com

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
]]
--[[tableDB is a lua module that adds an abstraction layer over sqlite database. With some minor effort it may be extended to other databases as well. Using this module you need no knowledge of sqlite or SQL at all. If you want to store data in Lua tables, this module helps map your lua data tables automatically to an sqlite database and manages the accesses to the database automatically. So why do it this way? What are the advantages over just using Lua Tables and storing them in text files directly? Here are some:

    This approach is more scalable since a relational database is much more scalable as opposed to storing Lua tables in files
    When you read the Lua table it is fully loaded in the memory. For this approach it only loads the accessed data into the tables so it can handle large amounts of data without loading the entire thing in memory
    It gives you the best of both worlds since whatever you want to store in Lua tables gets mapped to your own database without dealing with the complexities of designing the database tables yourself or learning the SQL to manipulate them. All table manipulations are automatically mapped to the database when it is saved.

It handles any Lua table that has a number or a string as a key and has a table, number, string or boolean as a value. This set can accommodate majority of data storage tables one can imagine in Lua and more by manipulating those to this set in lua itself. To begin with you create your storage database by giving the module a template of your table which is almost like giving it a sample of the table you want to store in the database. The module creates the database using the template table and returns you the empty lua table which you use as a normal Lua table to store or retrieve your values, while the module automatically syncs the data between that and the database.
To best understand it let us look at some examples below.
]]
-- Milind Gupta 3/31/2014
-- To use and documentation look at http://www.amved.com/milindsweb/tableDB.html

local luasql = require "luasql.sqlite3"	-- Include sqlite3 module

local metaIndex, getTable, getLen

-- Override ipairs 
local ipairs_copy = ipairs
ipairs = function(tab)
	local mt = getmetatable(tab)
	if mt and mt.__index and mt.__index == metaIndex then
		-- tab is the invariant state
		return function(tab,prevIndex)
				return prevIndex+1,tab[prevIndex+1]
			end, tab, 0
	else
		return ipairs_copy(tab)
	end
end

-- Override pairs 
local pairs_copy = pairs
pairs = function(tab)
	local mt = getmetatable(tab)
	if mt and mt.__index and mt.__index == metaIndex then
		local function comp(a,b)
			return tostring(a)<tostring(b)
		end
		-- tab is the invariant state
		return function(tab,prevKey)
				local t = getTable(tab)
				local keys = {}
				for k,v in pairs_copy(t) do
					keys[#keys+1]=k
				end
				table.sort(keys,comp)
				if prevKey == nil then
					return keys[1],t[keys[1]]
				else
					for k = 1,#keys do
						if tostring(keys[k]) > tostring(prevKey) then
							return keys[k],t[keys[k]]
						end
					end
				end
			end, tab, nil
	else
		return pairs_copy(tab)
	end
end

local modname = ...
local os = os
local io = io
local require = require
local luasql = luasql
local pairs = pairs_copy
local ipairs = ipairs_copy
local type = type
local tostring = tostring
local getmetatable = getmetatable
local setmetatable = setmetatable
local error = error
local string = string
local rawset = rawset
local assert = assert
local loadstring = loadstring
local tonumber = tonumber
local collectgarbage = collectgarbage
local setfenv = setfenv

-- Override the pairs and ipairs functions
--##############################################################
----------------------------------------------------------
--module(modname)
-- NOT USING THE module KEYWORD SINCE IT DOES THIS ALSO _G[modname] = M
local M = {}
package.loaded[modname] = M
setfenv(1,M)
----------------------------------------------------------


local env, metaNewIndex, tableToString
env = luasql.sqlite3()

local preName = "__T_"
local enforceTYPES = false

local cache
cache = {
-- Cache to store all changes without modifying the database. The changes are written to the database when the save command is given
-- The structure of the cache table is:
-- Keys are record IDs in the format they are stored in the metatable i.e. ID:TableName
-- For each key is a table which contains key value pairs
-- The key is a number or a string corresponding to the column name of the table
-- The value can be number, string, boolean, table
	-- If we don't do the read/write cache method and write everything in the tables then the read and write cache gets mashed into the table as one and everthing that was also just read is also
	-- rewritten to the tables so simpleElementArrays end up deleting and re-writing the elements just just incrementing the IDs unnecessarily.
	readDB = function(ID,k,conn,readCache,writeCache,setNil)
		local id,tabName, colName, colType, curr, rec, dbmng
		id,tabName = ID:match("(.-):(.+)")
		curr = conn:execute("SELECT * FROM "..tabName.." WHERE "..preName.."ID="..id..";")
		if not curr then
			return nil,"Invalid ID:Table Name: "..ID 
		end
		rec = curr:fetch({},"a")
		if not rec then
			return nil,"Invalid ID:Table Name: "..ID 
		end
		
		-- Get the type of the data requested from DBMANAGE table and if the requested table has an array
		curr = conn:execute("SELECT Columns,Array FROM "..preName.."DBMANAGE WHERE TableName='"..tabName.."';")
		if not curr then
			return nil,"Cannot query DBMANAGE for table Name: "..tabName
		end
		dbmng = curr:fetch({},"a")	-- DBMANAGE record of the table
		if not dbmng then
			return nil,"Cannot query DBMANAGE for table Name: "..tabName
		end
		
		-- Now get the data from rec
		if type(k) == "number" and (not dbmng.Array or (k<1 and dbmng.Array)) then
			-- This is a simple numerical index
			colName = preName..tostring(k)
			colType = dbmng.Columns:match(colName..":(.-);",1)
			-- NOTE: if colName is not found in columns i.e. it may be in HOOKS then colType will be nil
		elseif type(k) == "string" then
			-- This is a simple string key
			colName = k
			colType = dbmng.Columns:match(colName..":(.-);",1)
			-- NOTE: if colName is not found in columns i.e. it may be in HOOKS then colType will be nil
		else
			-- This is an Array index i.e. a number key which points to an Array
			colName = preName.."ARRAY"
			colType = "Array"
		end
		-- Make the readCache ready to store the read value
		if not readCache[ID] then
			readCache[ID] = {}
		end
		local doHOOKS	-- To indicate whether the HOOKS need to be evaluated
		if colType == "number" then
			if rec[colName] then
				readCache[ID][k] = tonumber(rec[colName])
				return tonumber(rec[colName])
			else
				if not enforceTYPES then
					doHOOKS = true
				else
					return nil
				end
			end
		elseif colType == "boolean" then
			if rec[colName] then
				if rec[colName] ~= '0' then
					readCache[ID][k] = true
					return true
				else
					readCache[ID][k] = false
					return false
				end
			else
				if not enforceTYPES then
					doHOOKS = true
				else
					return nil
				end
			end
		elseif colType == "string" then
			if rec[colName] then
				readCache[ID][k] = rec[colName]
				return rec[colName]
			else
				if not enforceTYPES then
					doHOOKS = true
				else
					return nil
				end
			end
		elseif colType and colType:sub(1,5) == "table" then
			if rec[colName] then
				local t = {} -- table to pass on
				local metaT = {ID = rec[colName], __index = metaIndex, __newindex=metaNewIndex,__len=getLen,conn=conn,rc=readCache,wc=writeCache,sn=setNil}
				setmetatable(t,metaT)
				readCache[ID][k] = t
				return t
			else
				if not enforceTYPES then
					doHOOKS = true
				else
					return nil
				end
			end
		elseif colType == "Array" then
			curr = conn:execute("SELECT CHILDID FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..ID.."' AND SEQNO='"..tostring(k).."';")
			if not curr then
				return nil, "Cannot read from ARRAYELEMENTS table"
			end
			local arrElem = curr:fetch({},"a")
			if arrElem and not arrElem.CHILDID then
				-- Could not find any record
				if not enforceTYPES then
					doHOOKS = true
				else
					return nil
				end
			else
				-- Now to check if the array element is a simple element or a table
				local arrElemID, arrElemTab, simp
				arrElemID,arrElemTab = arrElem.CHILDID:match("(.-):(.+)")
				curr = conn:execute("SELECT SimpleElementArray,Columns FROM "..preName.."DBMANAGE WHERE TableName='"..arrElemTab.."';")
				if not curr then
					return nil,"Cannot read from DBMANAGE table"
				end
				simp = curr:fetch({},"a")
				if simp and simp.SimpleElementArray then
					-- NOTE: A simple Element array record does not have HOOKS
					curr = conn:execute("SELECT "..preName.."ELEM FROM "..arrElemTab.." WHERE "..preName.."ID="..arrElemID..";")
					if not curr then
						return nil,"Cannot read from table:"..arrElemTab
					end
					arrElem = curr:fetch({},"a")
					if not arrElem then
						return nil,"No entry in table:"..arrElemTab.." for ID="..arrElemID.." linked in the ARRAYELEMENTS table. Database Corrupt!" 
					end
					local colType
					colType = simp.Columns:match(preName.."ELEM:(.-);")
					if colType == "string" then
						readCache[ID][k] = arrElem[preName.."ELEM"]
						return arrElem[preName.."ELEM"]
					elseif colType == "boolean" then
						if arrElem[preName.."ELEM"] == "0" then
							readCache[ID][k] = false
							return false
						else
							readCache[ID][k] = true
							return true
						end
					elseif colType == "number" then
						readCache[ID][k] = tonumber(arrElem[preName.."ELEM"])
						return tonumber(arrElem[preName.."ELEM"])
					else
						return nil,"Wrong column type for element column in table:"..arrElemTab
					end		-- if colType == "string" then ends
				else
					local t = {}
					local metaT = {ID = arrElem.CHILDID, __index = metaIndex, __newindex=metaNewIndex,__len=getLen,conn=conn,rc=readCache,wc=writeCache,sn=setNil}
					setmetatable(t,metaT)
					readCache[ID][k] = t
					return t
				end
			end		-- if not arrElem.CHILDID then ends
		elseif not colType then
			if not enforceTYPES then
				doHOOKS = true
			else
				return nil
			end
		else
			-- Wrong data type
			return nil,"Wrong Data Type in DBMANAGE in table entry for:"..tabName
		end
		if doHOOKS then
			-- Look into HOOKS here
			if rec[preName.."HOOKS"] then
				local f,message = loadstring(rec[preName.."HOOKS"])
				if not f then
					return nil,"Database Corrupted: Cannot compile HOOKS"
				end
				local safeenv = {metaIndex = metaIndex, metaNewIndex = metaNewIndex,getLen=getLen,conn=conn,rc=readCache,wc=writeCache,sn=setNil}
				setmetatable(safeenv,{__index = _G})
				setfenv(f,safeenv)
				f()
				if safeenv.t0[k] then
					readCache[ID][k] = safeenv.t0[k]
					return safeenv.t0[k]
				else
					return nil
				end			
			end
		end
	end,		-- readDB ends
	-- Function to recursively process a HOOK table for any database linked tables
	flattenHookTable = function(t,tablesAdded,hooks,gbgCheck,conn,setNil)
		for k,v in pairs(t) do
			if type(v) == "table" then
				-- Check if this is a linked table
				local found 
				for i = 1,#tablesAdded do
					if v == tablesAdded[i][1] then
						found = true
						-- created the linked table and replace here
						local lt = {}
						local mt = {ID=tablesAdded[i][2],__index = metaIndex, __newindex = metaNewIndex, conn=conn}
						setmetatable(lt,mt)
						t[k] = lt
						break
					end
				end
				if not found then
					local mt = getmetatable(v)
					if mt and mt.__index and mt.__index == metaIndex then
						local ret,msg = cache.writeTable(mt.ID,v,tablesAdded,hooks,gbgCheck,conn,setNil)
						if not ret then
							return nil,msg
						end
					else
						local ret,msg = cache.flattenHookTable(v,tablesAdded,hooks,gbgCheck,conn,setNil)
						if not ret then
							return nil,msg
						end
					end
				end
			end
		end
		return t
	end,	-- flattenHookTable ends
	-- recursive function to write a table into the database
	writeTable = function(id,t,tablesAdded,hooks,gbgCheck,conn,setNil)
		local rid,tabName
		rid,tabName = id:match("(.-):(.+)")
		local col = conn:execute("SELECT Columns,Array FROM "..preName.."DBMANAGE WHERE TableName='"..tabName.."';")
		if not col then
			return nil,"Invalid Table Name in id"
		end
		local row = col:fetch({},"a")	--DBMANAGE row for this id table
		if not row then
			return nil,"No entry in DBMANAGE table for the table:"..tabName 
		end
		
		local recData = {} -- Record Data that can be written to the record
		local tabs = {}	-- to store the keys that have table values in the t table. Only keys which are not array indices
						-- This helps to track which tables have been handled and so recData stores its string IDs which is different than if recData had a normal string value
		-- First handle all table values so that the whole thing becomes a case of simple values
		for k,v in pairs(t) do
			if type(v) == "table" then
				if type(k) == "number" and k>0 and row.Array and row.Array ~= '0' then
					-- This is an Array element
					-- Get the original linked item in this place and place it in the gbgCheck if there is one
					local prevRec
					col = conn:execute("SELECT CHILDID FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..id.."' AND SEQNO='"..tostring(k).."';")
					if not col then
						return nil,"Cannot read from table ARRAYELEMENTS"
					end
					local arrElem = col:fetch({},"a")
					if arrElem and arrElem.CHILDID then
						prevRec = true
						local found
						for i = 1,#gbgCheck do
							if gbgCheck[i] == arrElem.CHILDID then
								found = true
								break
							end
						end
						if not found then
							gbgCheck[#gbgCheck+1] = arrElem.CHILDID
						end
					end
					-- v a linked table?
					local mt = getmetatable(v)
					if mt and mt.__index and mt.__index == metaIndex then
						-- This is a linked table so the entry just goes into the ARRAYELEMENTS table
						if prevRec then
							col = conn:execute("UPDATE "..preName.."ARRAYELEMENTS SET CHILDID='"..mt.ID.."' WHERE PARENTID='"..id.."' AND SEQNO="..tostring(k)..";")
						else
							col = conn:execute("INSERT INTO "..preName.."ARRAYELEMENTS(PARENTID, CHILDID, SEQNO) VALUES ('"..id.."','"..mt.ID.."',"..tostring(k)..");")
						end
						if not col then
							return nil,"Cannot write into the ARRAYELEMENTS table"
						end
						-- Recurse to write the data in this table as well
						local ret,msg = cache.writeTable(mt.ID,v,tablesAdded,hooks,gbgCheck,conn,setNil)
						if not ret then
							return nil,msg
						end
					else
						if tonumber(row.Array) == 1 then
							-- No specific table was specified
							-- Not a linked table so the table will be possibly hooked
							if not hooks[id] then
								hooks[id] = {}
							end
							local ret,msg = cache.flattenHookTable(v,tablesAdded,hooks,gbgCheck,conn,setNil)
							if not ret then
								return nil,msg
							end
							hooks[id][k] = ret
							if prevRec then
								-- Delete the element
								col = conn:execute("DELETE FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..id.."' AND SEQNO='"..tostring(k).."';")
								if not col then
									return nil,"Cannot delete row from table ARRAYELEMENTS"
								end
							end
						else
							-- This goes into a specific table
							-- Get one column name for the  destination table
							col = conn:execute("SELECT Columns,SimpleElementArray FROM "..preName.."DBMANAGE WHERE TableName='"..row.Array.."';")
							if not col then
								return nil,"Cannot read DBMANAGE table"
							end
							local arrTab = col:fetch({},"a")
							if not arrTab or not arrTab.Columns then
								return nil,"Wrong table name give:"..row.Array
							end
							-- Check if this is a SimpleElementArray definition since then it won't have HOOKS
							if arrTab.SimpleElementArray then
								-- This table goes into HOOKS
								if not hooks[id] then
									hooks[id] = {}
								end
								local ret,msg = cache.flattenHookTable(v,tablesAdded,hooks, gbgCheck,conn,setNil) 
								if not ret then
									return nil,msg
								end
								hooks[id][k] = ret
								if prevRec then
									-- Delete the element
									col = conn:execute("DELETE FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..id.."' AND SEQNO='"..tostring(k).."';")
									if not col then
										return nil,"Cannot delete row from table ARRAYELEMENTS"
									end
								end
							else
--								-- Pick the first column name
--								local colName = arrTab.Columns:match("(.-):")
								-- Add a record in the table row.Array
--								col = conn:execute("INSERT INTO "..row.Array.."("..colName..") VALUES (NULL);")
								col = conn:execute("INSERT INTO "..row.Array.." DEFAULT VALUES;")
								if not col then
									return nil,"Cannot write into table: "..arrTab
								end
								conn:commit()
								col = conn:execute("SELECT last_insert_rowid();")
								if not col then
									return nil,"Cannot get the last written record's row ID"
								end
								arrTab = col:fetch({},"a")
								if not arrTab then
									return nil,"Cannot get the last written record's row ID"
								end
								tablesAdded[#tablesAdded+1] = {v,arrTab["last_insert_rowid()"]..":"..row.Array}
								local ret,msg = cache.writeTable(arrTab["last_insert_rowid()"]..":"..row.Array,v,tablesAdded,hooks,gbgCheck,conn,setNil)
								if not ret then
									return nil,msg
								end
								if prevRec then
									col = conn:execute("UPDATE "..preName.."ARRAYELEMENTS SET CHILDID='"..arrTab["last_insert_rowid()"]..":"..row.Array.."' WHERE PARENTID='"..id.."' AND SEQNO="..tostring(k)..";")
								else
									col = conn:execute("INSERT INTO "..preName.."ARRAYELEMENTS(PARENTID, CHILDID, SEQNO) VALUES ('"..id.."','"..arrTab["last_insert_rowid()"]..":"..row.Array.."',"..tostring(k)..");")
								end
								if not col then
									return nil,"Cannot write into the ARRAYELEMENTS table"
								end
							end		-- if arrTab.SimpleElementArray then ends
						end		-- if tonumber(row.Array) == 1 then ends
					end		-- if mt and mt.__index and mt.__index == metaIndex then ends
				else	-- Not an array element i.e. a simple string or number key
					local key
					if type(k) == "string" then
						-- String Index
						key = k
					else
						-- Simple Numerical index
						key = preName..tostring(k)
					end
					-- Get the original element type
					local elemType = row.Columns:match(key..":(.-);")
					if not elemType or not elemType:sub(1,5) == "table" then
						-- This key does not exist or it is not table type so this has to go into hooks
						if not hooks[id] then
							hooks[id] = {}
						end
						-- v a linked table?
						local mt = getmetatable(v)
						if mt and mt.__index and mt.__index == metaIndex then
							hooks[id][key] = v
							-- Write the rest of the keys in the table
							local ret,msg = cache.writeTable(mt.ID,v,tablesAdded,hooks,gbgCheck,conn,setNil)
							if not ret then
								return nil,msg
							end
						else
							local ret,msg = cache.flattenHookTable(v,tablesAdded,hooks,gbgCheck,conn,setNil)
							if not ret then
								return nil,msg
							end
							hooks[id][key] = ret
						end
						if elemType then
							-- Nullify the element in the record here so that the access to it will point to the hooks
							col = conn:execute("UPDATE "..tabName.." SET "..key.."=NULL WHERE "..preName.."ID="..rid..";")
							if not col then
								return nil,"Cannot write the record table:"..tabName.." for ID:"..rid
							end
						end
					else	-- elemType:sub(1,5) == "table"
						-- This key has a type table
						-- check if some record was already linked then store it in gbgCheck
						col = conn:execute("SELECT * FROM "..tabName.." WHERE "..preName.."ID="..rid..";")
						if not col then
							return nil,"Cannot read the record table:"..tabName.." for ID:"..rid
						end
						local rec = col:fetch({},"a")
						if rec and rec[key] then
							local found
							for i = 1,#gbgCheck do
								if gbgCheck[i] == rec[key] then
									found = true
									break
								end
							end
							if not found then
								gbgCheck[#gbgCheck+1] = rec[key]
							end
							-- No need to nullify the element here since recData gets a value and if it is not the right type then the element is nullified in the record write loop
						end
						-- is v a linked table
						local mt = getmetatable(v)
						if mt and mt.__index and mt.__index == metaIndex then
							recData[key] = mt.ID
							tabs[#tabs+1] = key
							-- Write the rest of the keys in the table
							local ret,msg = cache.writeTable(mt.ID,v,tablesAdded,hooks,gbgCheck,conn,setNil)
							if not ret then
								return nil,msg
							end
						else
							-- Get the table name
							local linkTabName = elemType:match("table%-(.+)")
--							-- Get one column name for the  destination table
--							col = conn:execute("SELECT Columns FROM "..preName.."DBMANAGE WHERE TableName='"..linkTabName.."';")
--							if not col then
--								return nil,"Wrong name in DBMANAGE for "..tabName.." key "..key.." linked table"
--							end
--							local linkTab = col:fetch({},"a")
--							if not linkTab then
--								return nil,"No entry for table:"..linkTabName.." Columns field. Database Corrupt!"
--							end
--							local colName = linkTab.Columns:match("(.-):")
--							col = conn:execute("INSERT INTO "..linkTabName.."("..colName..") VALUES (NULL);")
							col = conn:execute("INSERT INTO "..linkTabName.." DEFAULT VALUES;")
							if not col then
								return nil,"Cannot write into table: "..linkTabName
							end
							conn:commit()
							col = conn:execute("SELECT last_insert_rowid();")
							if not col then
								return nil,"Cannot get the last written record's row ID"
							end
							linkTab = col:fetch({},"a")
							if not linkTab then
								return nil,"Cannot get the last written record's row ID"
							end
							recData[k] = linkTab["last_insert_rowid()"]..":"..linkTabName
							tabs[#tabs+1] = key
							tablesAdded[#tablesAdded+1]={v,linkTab["last_insert_rowid()"]..":"..linkTabName}
							local ret,msg = cache.writeTable(linkTab["last_insert_rowid()"]..":"..linkTabName,v,tablesAdded,hooks,gbgCheck,conn,setNil)
							if not ret then
								return nil,msg
							end
						end
					end		-- if elemType:sub(1:5) == "table" then ends
				end		-- if k>0 and row.Array then ends
			else
				-- Non table values go directly to recData
				recData[k] = v
			end		-- if type(v) == "table" then ends
		end		-- for k,v in pairs(t) do ends
		
		-- Now write the full record
		-- Write the defined type elements first
		for colName,colType in row.Columns:gmatch("(.-):(.-);") do
			local recKey
			if colName:sub(1,#preName) == preName and colName ~= preName.."ID" and colName ~= preName.."HOOKS" then
				recKey = tonumber(colName:sub(#preName+1,-1))
			else
				recKey = colName
			end
			if recData[recKey] ~= nil then
				local data
				if colType == "string" then
					if type(recData[recKey]) == "string" then
						data = "'"..recData[recKey].."'"
					else
						data = "NULL"
						if not enforceTYPES then
							if not hooks[id] then
								hooks[id] = {}
							end
							hooks[id][recKey] = recData[recKey]
						end
					end
				elseif colType == "boolean" then
					if type(recData[recKey]) == "boolean" then
						if recData[recKey] then
							data = "1"
						else
							data = "0"
						end
					else
						data = "NULL"
						if not enforceTYPES then
							if not hooks[id] then
								hooks[id] = {}
							end
							hooks[id][recKey] = recData[recKey]
						end
					end
				elseif colType:sub(1,5) == "table" then
					local found
					for i = 1,#tabs do
						if colName == tabs[i] then
							-- This table was handled during table handling and recData should just have the string as the ID
							found = true
							break
						end
					end
					if found then
						data = "'"..recData[recKey].."'"
					else
						data = "NULL"
						if not enforceTYPES then
							if not hooks[id] then
								hooks[id] = {}
							end
							hooks[id][recKey] = recData[recKey]
						end
					end
				elseif colType == "number" then
					if type(recData[recKey]) == "number" then
						data = tostring(recData[recKey])
					else
						data = "NULL"
						if not enforceTYPES then
							if not hooks[id] then
								hooks[id] = {}
							end
							hooks[id][recKey] = recData[recKey]
						end
					end
				else
					return nil,"Wrong column type for column:"..colName.." in table:"..tabName
				end
				col = conn:execute("UPDATE "..tabName.." SET "..colName.."="..data.." WHERE "..preName.."ID="..rid..";")
				recData[recKey] = nil
			elseif setNil[id] and setNil[id][recKey] then
				if colType:sub(1,5) == "table" then
					col = conn:execute("SELECT "..colName.." FROM "..tabName.." WHERE "..preName.."ID="..rid..";")
					if not col then
						return nil,"Cannot read the record table:"..tabName.." for ID:"..rid
					end
					local prevElem = col:fetch({},"a")
					if prevElem and prevElem[colName] then
						local found
						for i = 1,#gbgCheck do
							if gbgCheck[i] == prevElem[colName] then
								found = true
								break
							end
						end
						if not found then
							gbgCheck[#gbgCheck+1] = prevElem[colName]
						end
					end
				end
				
				col = conn:execute("UPDATE "..tabName.." SET "..colName.."=NULL WHERE "..preName.."ID="..rid..";")
				if not col then
					return nil,"Cannot write NULL in the the record table:"..tabName.." for ID:"..rid
				end
				setNil[id][recKey] = nil
			end		-- if recData[recKey] then ends
		end		-- for colName,colType in row.Columns:gmatch("(.-):(.-);") do ends
		
		-- Now write the Non-Table Array Elements if any i.e. all the Simple Elements of the array
		if row.Array then
			-- get the colType
			col = conn:execute("SELECT Columns,SimpleElementArray FROM "..preName.."DBMANAGE WHERE TableName='"..row.Array.."';")
			if not col then
				return nil,"Invalid Array table name given for table: "..tabName
			end
			local arrCols = col:fetch({},"a")
			if arrCols and arrCols.SimpleElementArray then
				local colType = arrCols.Columns:match(preName.."ELEM:(.-);")
				if not colType then
					return nil,"ELEM type data not written in Columns info in DBMANAGE for table: "..row.Array
				end
				for k,v in ipairs(recData) do
					local data, inHOOK
					if colType == "string" then
						if type(v) == "string" then
							data = "'"..v.."'"
						else
							data = "NULL"
							if not enforceTYPES then
								if not hooks[id] then
									hooks[id] = {}
								end
								hooks[id][k] = v
								inHOOK = true
							end
						end
					elseif colType == "boolean" then
						if type(v) == "boolean" then
							if v then
								data = "1"
							else
								data = "0"
							end
						else
							data = "NULL"
							if not enforceTYPES then
								if not hooks[id] then
									hooks[id] = {}
								end
								hooks[id][k] = v
								inHOOK = true
							end
						end
					elseif colType == "number" then
						if type(v) == "number" then
							data = tostring(v)
						else
							data = "NULL"
							if not enforceTYPES then
								if not hooks[id] then
									hooks[id] = {}
								end
								hooks[id][k] = v
								inHOOK = true
							end
						end
					else
						return nil,"Wrong column type for column:"..colName.." in table:"..tabName
					end
					col = conn:execute("SELECT CHILDID FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..id.."' AND SEQNO='"..tostring(k).."';")
					if not col then
						return nil,"Cannot read from ARRAYELEMENTS Table"
					end
					local prevElem = col:fetch({},"a")
					local linkRec
					if not inHOOK then
						-- Put the element in row.Array
						col = conn:execute("INSERT INTO "..row.Array.."("..preName.."ELEM) VALUES("..data..");")
						if not col then
							return nil,"Cannot write into table: "..row.Array
						end
						conn:commit()
						col = conn:execute("SELECT last_insert_rowid();")
						if not col then
							return nil,"Cannot get the last written record's row ID"
						end
						linkRec = col:fetch({},"a")
					end
					if prevElem and prevElem.CHILDID then
						-- Put it in gbgCheck if not there
						local found
						for i = 1,#gbgCheck do
							if gbgCheck[i] == prevElem.CHILDID then
								found = true
								break
							end
						end
						if not found then
							gbgCheck[#gbgCheck+1] = prevElem.CHILDID
						end
						if not inHOOK then
							col = conn:execute("UPDATE "..preName.."ARRAYELEMENTS SET CHILDID='"..linkRec["last_insert_rowid()"]..":"..row.Array.."' WHERE PARENTID='"..id.."' AND SEQNO="..tostring(k)..";")
						else
							-- Delete the element
							col = conn:execute("DELETE FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..id.."' AND SEQNO='"..tostring(k).."';")
							if not col then
								return nil,"Cannot delete row from table ARRAYELEMENTS"
							end
						end
					elseif not inHOOK then
						col = conn:execute("INSERT INTO "..preName.."ARRAYELEMENTS(PARENTID, CHILDID, SEQNO) VALUES ('"..id.."','"..linkRec["last_insert_rowid()"]..":"..row.Array.."',"..tostring(k)..");")
					end		-- if col then ends here
				end		-- for k,v in ipairs(recData) ends here
			else
				-- Since it does not have a simpleElement defined we place all values into HOOKS since they are not tables
				if not enforceTYPES then
					if not hooks[id] and #recData > 0 then
						hooks[id] = {}
					end
					for k,v in ipairs(recData) do
						hooks[id][k] = v
					end
				end
			end		--if arrCols.SimpleElementArray then ends here
			-- Nullify the placed elements here
			for i=1,#recData do
				recData[i] = nil
			end
		end		-- if row.Array then ends here
		
		-- Transfer the remaining to HOOKS
		for k,v in pairs(recData) do
			if not hooks[id] then
				hooks[id] = {}
			end
			hooks[id][k] = v
		end

		-- Remove all elements from the table here
		for k,v in pairs(t) do
			t[k] = nil
		end
		return true
	end,		-- function writeTable ends here
	-- Function to write the changes into the database
	save = function(t)
		local tablesAdded = {}		-- To record all tables added as records to the database to handle interlinks to those tables at other places
		local pHooks = {}			-- To track data that possibly has to go into HOOKS
		local gbgCheck = {}		-- To store record IDs of records that need to be garbage checked
		local curr,dbmng,rec
		local mt,conn,writeCache, readCache,setNil
		mt = getmetatable(t)
		if not mt or mt.__index ~= metaIndex then
			error("Table cannot be saved, it is not a tableDB table",2)
		end
		conn = mt.conn
		writeCache = mt.wc
		readCache = mt.rc
		setNil = mt.sn
		if tostring(conn):match("%(closed%)") then
			error("Database connection already closed.")
		end
		for k,v in pairs(writeCache) do
			assert(cache.writeTable(k,v,tablesAdded,pHooks,gbgCheck,conn,setNil))
		end
		-- empty the writeCache
		for k,v in pairs(writeCache) do
			writeCache[k] = nil
		end
		-- empty the readCache
		for k,v in pairs(readCache) do
			readCache[k] = nil
		end
		-- Now check if any tables that were added to the database exist in the hooks tables and link them up
		for k,v in pairs(pHooks) do
			-- Check if this is a linked table
			for k1,v1 in pairs(v) do	-- iterate over the keys for the record of ID k
				if type(v1) == "table" then
					local found
					local mt = getmetatable(v1)  
					for i = 1,#tablesAdded do
						-- tablesAdded has table in the raw at index 1 and the ID of the table as a record where it is placed in the database
						if v1 == tablesAdded[i][1] then
							found = true
							-- created the linked table and replace here
							local lt = {}
							local mt = {ID=tablesAdded[i][2],__index = metaIndex,__newindex=metaNewIndex,conn=conn}
							setmetatable(lt,mt)
							pHooks[k][k1] = lt
							break
						elseif mt and mt.ID and mt.ID == tablesAdded[i][2] then
							found = true
						end
					end
					if not found then
						if mt and mt.__index and mt.__index == metaIndex then
							-- Write the remaining keys
							assert(cache.writeTable(mt.ID,v1,tablesAdded,pHooks,gbgCheck,conn,setNil))
						else
							-- Flatten the table
							assert(cache.flattenHookTable(v1,tablesAdded,pHooks,gbgCheck,conn,setNil))
						end
					end
				end		-- if type(v1) == "table" then ends
			end		-- for k1,v1 in pairs(v) do ends
		end
		-- write all hooks to the database here
		for k,v in pairs(pHooks) do
			local recID,tabName
			recID,tabName = k:match("(.-):(.+)")
			-- Get the DBMANAGE Columns and Array entry
			curr = conn:execute("SELECT Columns,Array FROM "..preName.."DBMANAGE WHERE TableName='"..tabName.."';")
			if not curr then
				error("Cannot read from DBMANAGE table")
			end
			dbmng = curr:fetch({},"a")	--DBMANAGE row for this id table
			if not type(v) == "table" then
				error("Wrong format for hooks table")
			end	
			if dbmng and dbmng.Array and ((dbmng.Array == '1' and enforceTYPES) or  not enforceTYPES)then
				-- Check whether any element is an array element that can be written in the ARRAYELEMENTS table
				local placedIndices = {}
				for i=1,#v do
					if type(v[i]) == "table" then
						local mt=getmetatable(v[i])
						if mt and mt.__index == metaIndex then
							-- This is a linked record so we can put this in the ARRAYELEMENTS table
							curr = conn:execute("SELECT CHILDID FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..k.."' AND SEQNO='"..tostring(i).."';")
							if not curr then
								error("Cannot read from ARRAYELEMENTS table")
							end
							local prevElem = curr:fetch({},"a")
							if prevElem and prevElem.CHILDID then
								local found
								for i = 1,#gbgCheck do
									if gbgCheck[i] == prevElem.CHILDID then
										found = true
										break
									end
								end
								if not found then
									gbgCheck[#gbgCheck+1] = prevElem.CHILDID
								end								
								curr = conn:execute("UPDATE "..preName.."ARRAYELEMENTS SET CHILDID='"..mt.ID.."' WHERE PARENTID='"..k.."' AND SEQNO="..tostring(i)..";")
							else
								curr = conn:execute("INSERT INTO "..preName.."ARRAYELEMENTS(PARENTID, CHILDID, SEQNO) VALUES ('"..k.."','"..mt.ID.."',"..tostring(i)..");")
							end		-- if col then ends here
							placedIndices[#placedIndices + 1] = i
						end
					end
				end
				-- Nullify all placed indices
				for i = 1,#placedIndices do
					v[placedIndices[i]] = nil
				end
			end
			
			-- Check if a record exists
			curr = conn:execute("SELECT * FROM "..tabName.." WHERE "..preName.."ID="..recID..";")
			if not curr then
				error("Cannot read from table: "..tabName)
			end
			rec = curr:fetch({},"a")
			if rec and rec[preName.."ID"] then
				if rec[preName.."HOOKS"] then
					-- Get the HOOKS and add it to the current table
					local f,message = loadstring(rec[preName.."HOOKS"])
					if not f then
						return nil,"Database Corrupted: Cannot compile HOOKS"
					end
					local safeenv = {metaIndex = metaIndex, metaNewIndex = metaNewIndex,getLen=getLen,conn=conn,rc=readCache,wc=writeCache,sn=setNil}
					setmetatable(safeenv,{__index = _G})
					setfenv(f,safeenv)
					f()
					if safeenv.t0[colName] then
						for i,j in pairs(safeenv.t0) do
							if not v[i] then
								v[i] = j
							end
						end
					end			
				end		-- if rec[preName.."HOOKS"] then ends
				
				-- Execute the setNil loop adding any possible garbage to garbage check
				if setNil[k] then
					for i,j in pairs(setNil[k]) do
						if type(v[i]) == "table" then 
							local meta = getmetatable(v[i])
							if meta and meta.__index and meta.__index == metaIndex then
								-- This is a linked record
								-- Add this record to the gbgCheck table
								local found 
								for m = 1,#gbgCheck do
									if gbgCheck[m] == meta.ID then
										found = true
										break
									end
								end
								if not found then
									gbgCheck[#gbgCheck+1] = meta.ID
								end
							end
						end
						v[i] = nil
					end
				end
				-- Now convert v to string and write it in the hooks entry
				local str = tableToString(v)
				curr = conn:execute("UPDATE "..tabName.." SET "..preName.."HOOKS='"..str.."' WHERE "..preName.."ID="..recID..";")
			else
				-- Execute the setNil loop adding any possible garbage to garbage check
				for i,j in pairs(setNil[k]) do
					if type(v[i]) == "table" then 
						local meta = getmetatable(v[i])
						if meta and meta.__index and meta.__index == metaIndex then
							-- This is a linked record
							-- Add this record to the gbgCheck table
							local found 
							for m = 1,#gbgCheck do
								if gbgCheck[m] == meta.ID then
									found = true
									break
								end
							end
							if not found then
								gbgCheck[#gbgCheck+1] = meta.ID
							end
						end
					end
					v[i] = nil
				end
				-- Now convert v to string and write it in the hooks entry
				local str = tableToString(v)
				curr = conn:execute("INSERT INTO "..tabName.."("..preName.."HOOKS) VALUES ('"..str.."');")
			end		-- if rec and rec[preName.."ID"] then ends - check if a record exists where the HOOK needs to be added
		end	-- Writing hooks loop ends here
		
		-- empty the setNil table
		for k,v in pairs(setNil) do
			setNil[k] = nil
		end
		
		-- Now garbage check and clear
		-- Read all Table names and their columns
		curr = conn:execute("SELECT TableName,Columns,SimpleElementArray FROM "..preName.."DBMANAGE;")
		if not curr then
			error("Cannot read from DBMANAGE table")
		end
		dbmng = curr:fetch({},"a")	--DBMANAGE row for this id table

		local allTabs = {}
		while dbmng do
			allTabs[#allTabs + 1] = {TableName=dbmng.TableName, SimpleElementArray=dbmng.SimpleElementArray,tableCols = {}}
			-- Find which columns are tables for this table
			for colName,colType in dbmng.Columns:gmatch("(.-):(.-);") do
				if colType:sub(1,5) == "table" then
					allTabs[#allTabs].tableCols[#allTabs[#allTabs].tableCols+1] = colName
				end
			end
			dbmng = curr:fetch({},"a")	--DBMANAGE row for this id table
		end
		local finIndex = #gbgCheck
		local function collectChildren(ID)
			curr = conn:execute("SELECT * FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..ID.."';")
			if not curr then
				error("Cannot read from ARRAYELEMENTS table")
			end
			rec = curr:fetch({},"a")
			while rec do
				finIndex = finIndex + 1
				gbgCheck[finIndex] = rec.CHILDID
				rec = curr:fetch({},"a")
			end
			local rid,tabName
			rid,tabName = ID:match("(.-):(.+)")
			-- Now find all the other children of the record
			curr = conn:execute("SELECT * FROM "..tabName.." WHERE "..preName.."ID="..rid..";")
			if not curr then
				error("Cannot read from table:"..tabName)
			end
			rec = curr:fetch({},"a")
			for k = 1,#allTabs do
				if allTabs[k].TableName == tabName then
					-- Put all the children in gbgCheck
					for j = 1,#allTabs[k].tableCols do
						if rec[allTabs[k].tableCols[j]] then
							finIndex = finIndex + 1
							gbgCheck[finIndex] = rec[allTabs[k].tableCols[j]]
						end
					end
					break
				end
			end
			-- Now get all the children in the HOOKS
			if rec[preName.."HOOKS"] then
				for child in rec[preName.."HOOKS"]:gmatch("{ID=(.-),__index=metaIndex,__newindex=metaNewIndex,__len=getLen,conn=conn,rc=rc,wc=wc,sn=sn}") do
					finIndex = finIndex + 1
					gbgCheck[finIndex] = child
				end
			end		-- if rec[preName.."HOOKS"] then ends
		end
		-- Loop through gbgCheck and see if any ID exist in tables
		local gbgIndex = 1
		while gbgIndex <= finIndex do
			if gbgCheck[gbgIndex] then
				local found
				-- Check in ARRAYELEMENTS for CHILDIDs
				curr = conn:execute("SELECT * FROM "..preName.."ARRAYELEMENTS WHERE CHILDID='"..gbgCheck[gbgIndex].."';")
				if not curr then
					error("Cannot read from ARRAYELEMENTS table")
				end
				rec = curr:fetch({},"a")
				if rec and rec.CHILDID then
					-- Found a record that has this element as child element so this is not garbage
					found = true
				end
				if not found then
					-- Check each table type for each table in DBMANAGE
					for k = 1,#allTabs do
						-- Check if the table is linked in any tablCols
						if #allTabs[k].tableCols > 0 then
							-- Create query part to search ID in table
							local qu = "SELECT * FROM "..allTabs[k].TableName.." WHERE "
							for j = 1,#allTabs[k].tableCols do
								qu = qu..allTabs[k].tableCols[j].."='"..gbgCheck[gbgIndex].."' OR "
							end
							-- Remove the last OR
							qu = qu:sub(1,-5)..";"
							curr = conn:execute(qu)
							if not curr then
								error("Cannot read from table:"..allTabs[k].TableName)
							end
							rec = curr:fetch({},"a")
							if rec then
								for j,v in pairs(rec) do
									found = true
									break
								end
							end
							if found then
								break
							end	
						end
						if not allTabs[k].SimpleElementArray or allTabs[k].SimpleElementArray == "0" then
							-- Check in each table HOOKS element for the gbgCheck IDs
							curr = conn:execute("SELECT * FROM "..allTabs[k].TableName.." WHERE "..preName.."HOOKS LIKE '%{ID="..gbgCheck[gbgIndex]..",__index=metaIndex,__newindex=metaNewIndex,__len=getLen,conn=conn,rc=rc,wc=wc,sn=sn})%';")
							if not curr then
								error("Cannot read from the HOOKS column of table:"..allTabs[k].TableName)
							end
							rec = curr:fetch({},"a")
							if rec then
								for j,v in pairs(rec) do
									found = true
									break
								end
							end
						end
					end		-- for k = 1,#allTabs do ends
					if not found then
						-- This record needs to be deleted so add all children of this record to the gbgCheck table
						collectChildren(gbgCheck[gbgIndex])
						-- delete the record from the database
						local recID,TabID
						recID,TabID = gbgCheck[gbgIndex]:match("(.-):(.+)")
						curr = conn:execute("DELETE FROM "..TabID.." WHERE "..preName.."ID="..recID..";")
						if not curr then
							error("Cannot delete record:"..gbgCheck[gbgIndex])
						end
						-- Also delete any parent references from ARRAYELEMENTS if any
						curr = conn:execute("SELECT * FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..gbgCheck[gbgIndex].."';")
						if not curr then
							error("Cannot read from ARRAYELEMENTS table")
						end
						rec = curr:fetch({},"a")
						if rec then
							curr = conn:execute("DELETE FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..gbgCheck[gbgIndex].."';")
							if not curr then
								error("Cannot delete parent reference:"..gbgCheck[gbgIndex].." from ARRAYELEMENTS table.")
							end
						end
						gbgCheck[gbgIndex] = nil
						gbgIndex = 1
					else
						gbgIndex = gbgIndex + 1
					end
				else
					gbgIndex = gbgIndex + 1
				end		-- if not found then ends
			else		-- if gbgCheck[gbgIndex] then ends
				gbgIndex = gbgIndex + 1
			end		-- if gbgCheck[gbgIndex] then ends
		end		-- while gbgIndex < finIndex do ends
	end,
	-- Function to be placed in the record metatable
	indexFunc = function(t,k)
		local mt = getmetatable(t)
		if not mt.ID or not mt.__index or not mt.__index==metaIndex then
			return nil
		end
		if tostring(mt.conn):match("%(closed%)") then
			error("Database already closed.",2)
		end
		local readCache = mt.rc
		local writeCache = mt.wc
		local setNil = mt.sn
		if setNil[mt.ID] and setNil[mt.ID][k] then
			return nil
		else
			if writeCache[mt.ID] and writeCache[mt.ID][k] then
				return writeCache[mt.ID][k]
			elseif readCache[mt.ID] and readCache[mt.ID][k] then
				return readCache[mt.ID][k]
			else
				local data,msg
				data,msg = cache.readDB(mt.ID,k,mt.conn,readCache,writeCache,setNil)
				if not msg then	-- data can be nil if there is no data and also if there is error
					return data
				else
					error(msg,2)
				end
			end
		end		-- if setNil ends here
	end,
	newIndexFunc = function(t,k,v)
		local mt = getmetatable(t)
		if mt.ID and mt.__index and mt.__index == metaIndex then
			local writeCache = mt.wc
			local setNil = mt.sn
			if v==nil then
				if not setNil[mt.ID] then
					setNil[mt.ID] = {}
				end
				setNil[mt.ID][k] = true
				if not writeCache[mt.ID] then
					writeCache[mt.ID] = {}
				end
			else
				if setNil[mt.ID] and setNil[mt.ID][k] then
					setNil[mt.ID][k] = nil
				end
				if not writeCache[mt.ID] then
					writeCache[mt.ID] = {}
				end
			end
			writeCache[mt.ID][k] = v
		end
	end
}
metaIndex = cache.indexFunc
metaNewIndex = cache.newIndexFunc

getTable = function(t)
	local mt = getmetatable(t)
	local rid,tabName, retTab
	retTab = {}
	rid,tabName = mt.ID:match("(.-):(.+)")
	local curr = mt.conn:execute("SELECT Columns,Array FROM "..preName.."DBMANAGE WHERE TableName='"..tabName.."';")
	if not curr then
		return nil,"Invalid Table Name in id:"..mt.ID
	end
	local dbmng = curr:fetch({},"a")	--DBMANAGE row for this id table
	if not dbmng then
		return nil,"No entry in DBMANAGE table for the table:"..tabName 
	end
	-- Also get the record for this ID
	curr = mt.conn:execute("SELECT * FROM "..tabName.." WHERE "..preName.."ID="..rid..";")
	if not curr then
		return nil,"Invalid ID:Table Name:"..mt.ID
	end
	local rec = curr:fetch({},"a")
	if not rec then
		return nil,"Invalid ID:Table Name: "..mt.ID
	end

	-- Collect the basic record data first
	for colName,colType in dbmng.Columns:gmatch("(.-):(.-);") do
		local recKey
		if colName:sub(1,#preName) == preName and colName ~= preName.."ID" and colName ~= preName.."HOOKS" then
			recKey = tonumber(colName:sub(#preName+1,-1))
		else
			recKey = colName
		end
		if rec[colName] ~= nil then
			if colType == "string" then
				retTab[recKey] = rec[colName]
			elseif colType == "boolean" then
				if rec[colName] == "0" then
					retTab[recKey] = false
				else
					retTab[recKey] = true
				end
			elseif colType == "number" then
				retTab[recKey] = tonumber(rec[colName])
			elseif colType:sub(1,5) == "table" then
				local st = {}
				local metaT = {ID = rec[colName], __index = metaIndex, __newindex=metaNewIndex,__len=getLen,conn=mt.conn,rc=mt.rc,wc=mt.wc,sn=mt.sn}
				setmetatable(st,metaT)
				retTab[recKey] = st		
			else
				return nil,"Wrong column type for column:"..colName.." in table:"..tabName
			end		-- if colType == "string" then ends
		end		-- if rec[colName] ~= nil then ends
	end		-- for colName,colType ends here
	
	-- Collect the Hooks data 
	if rec[preName.."HOOKS"] then
		local f,message = loadstring(rec[preName.."HOOKS"])
		if not f then
			return nil,"Database Corrupted: Cannot compile HOOKS"
		end
		local safeenv = {metaIndex = metaIndex, metaNewIndex = metaNewIndex,getLen=getLen,conn=mt.conn,rc=mt.rc,wc=mt.wc,sn=mt.sn}
		setmetatable(safeenv,{__index = _G})
		setfenv(f,safeenv)
		f()
		for k,v in pairs(safeenv.t0) do
			retTab[k] = v
		end
	end
	
	-- Collect the ARRAYELEMENTS data
	curr = mt.conn:execute("SELECT * FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..mt.ID.."';")
	if not curr then
		return nil, "Cannot read from ARRAYELEMENTS table"
	end
	local arrElem = curr:fetch({},"a")
	while arrElem do
		if arrElem.CHILDID then
			-- Now to check if the array element is a simple element or a table
			local arrElemID, arrElemTab, simp
			arrElemID,arrElemTab = arrElem.CHILDID:match("(.-):(.+)")
			local curr1 = mt.conn:execute("SELECT SimpleElementArray,Columns FROM "..preName.."DBMANAGE WHERE TableName='"..arrElemTab.."';")
			if not curr1 then
				return nil,"Cannot read from DBMANAGE table"
			end
			simp = curr1:fetch({},"a")
			if simp and simp.SimpleElementArray then
				-- It is a simpleElement array so we have to pick the element directly
				curr1 = mt.conn:execute("SELECT "..preName.."ELEM FROM "..arrElemTab.." WHERE "..preName.."ID="..arrElemID..";")
				if not curr1 then
					return nil,"Cannot read from table:"..arrElemTab
				end
				local sarrElem = curr1:fetch({},"a")
				if not sarrElem then
					return nil,"No entry in table:"..arrElemTab.." for ID="..arrElemID.." linked in the ARRAYELEMENTS table. Database Corrupt!" 
				end
				local colType
				colType = simp.Columns:match(preName.."ELEM:(.-);")
				if colType == "string" then
					retTab[tonumber(arrElem.SEQNO)] = sarrElem[preName.."ELEM"]
				elseif colType == "boolean" then
					if sarrElem[preName.."ELEM"] == "0" then
						retTab[tonumber(arrElem.SEQNO)] = false
					else
						retTab[tonumber(arrElem.SEQNO)] = true
					end
				elseif colType == "number" then
					retTab[tonumber(arrElem.SEQNO)] = tonumber(sarrElem[preName.."ELEM"])
				else
					return nil,"Wrong column type for element column in table:"..arrElemTab
				end		-- if colType == "string" then ends
			else
				local st = {}
				local metaT = {ID = arrElem.CHILDID, __index = metaIndex, __newindex=metaNewIndex,__len=getLen,conn=mt.conn,rc=mt.rc,wc=mt.wc,sn=mt.sn}
				setmetatable(st,metaT)
				retTab[tonumber(arrElem.SEQNO)] = st
			end
		end		-- if not arrElem.CHILDID then ends
		arrElem = curr:fetch(arrElem,"a")
	end		-- while arrElem ends here
	
	-- Collect the writeCache data
	if mt.wc[mt.ID] then
		for k,v in pairs(mt.wc[mt.ID]) do
			retTab[k] = v
		end
	end
		
	-- Collect the setNil data
	if mt.sn[mt.ID] then
		for k,v in pairs(mt.sn[mt.ID]) do
			retTab[k] = nil
		end
	end
	
	return retTab
end

-- Metafunction to get the function length
getLen = function(t)
	local mt = getmetatable(t)
	local rid,tabName, numKeys
	numKeys = {}
	rid,tabName = mt.ID:match("(.-):(.+)")
	local curr = mt.conn:execute("SELECT Columns,Array FROM "..preName.."DBMANAGE WHERE TableName='"..tabName.."';")
	if not curr then
		return nil,"Invalid Table Name in id:"..mt.ID
	end
	local dbmng = curr:fetch({},"a")	--DBMANAGE row for this id table
	if not dbmng then
		return nil,"No entry in DBMANAGE table for the table:"..tabName 
	end
	-- Also get the record for this ID
	curr = mt.conn:execute("SELECT * FROM "..tabName.." WHERE "..preName.."ID="..rid..";")
	if not curr then
		return nil,"Invalid ID:Table Name:"..mt.ID
	end
	local rec = curr:fetch({},"a")
	if not rec then
		return nil,"Invalid ID:Table Name: "..mt.ID
	end
	for colName,colType in dbmng.Columns:gmatch("(.-):(.-);") do
		if colName:sub(1,#preName) == preName and colName ~= preName.."ID" and colName ~= preName.."HOOKS" then
			numKeys[tonumber(colName:sub(#preName+1,-1))] = true
		end
	end
	-- read the HOOKS
	if rec[preName.."HOOKS"] then
		local f,message = loadstring(rec[preName.."HOOKS"])
		if not f then
			return nil,"Database Corrupted: Cannot compile HOOKS"
		end
		local safeenv = {metaIndex = metaIndex, metaNewIndex = metaNewIndex,getLen=getLen,conn=mt.conn,rc=mt.rc,wc=mt.wc,sn=mt.sn}
		setmetatable(safeenv,{__index = _G})
		setfenv(f,safeenv)
		f()
		for k,v in pairs(safeenv.t0) do
			if type(k) == "number" then
				numKeys[k] = true
			end
		end
	end
	-- Now read the ARRAYELEMENTS
	curr = mt.conn:execute("SELECT * FROM "..preName.."ARRAYELEMENTS WHERE PARENTID='"..mt.ID.."';")
	if not curr then
		error("Cannot read from ARRAYELEMENTS table")
	end
	rec = curr:fetch({},"a")
	while rec do 
		numKeys[tonumber(rec.SEQNO)] = true
		rec = curr:fetch(rec,"a")
	end
	-- Now see the max seq indexes
	local i = 1
	local done
	while not done do
		if numKeys[i] then
			i = i + 1
		else
			done = true
		end
	end
	
	return i
end

-- Creates lua code for a table which when executed will create a table t0 which would be the same as the originally passed table
-- Handles the following types for keys and values:
-- Keys: Number, String, Table
-- Values: Number, String, Table, Boolean
-- It also handles recursive and interlinked tables to recreate them back
-- DEVIATION FROM STANDARD:
-- If the value is a table with a metatable __index==metaindex then the value's metatable is maintained
function tableToString(t)
	local rL = {cL = 1}	-- Table to track recursion into nested tables (cL = current recursion level)
	rL[rL.cL] = {}
	local tabIndex = {}	-- Table to store a list of tables indexed into a string and their variable name
	local latestTab = 0
	do
		rL[rL.cL]._f,rL[rL.cL]._s,rL[rL.cL]._var = pairs(t)
		rL[rL.cL].str = "t0={}"	-- t0 would be the main table
		rL[rL.cL].t = t		-- table at this recursion level
		rL[rL.cL].tabIndex = 0	-- table index concatenated with variable t to create unique table variable name
		tabIndex[t] = rL[rL.cL].tabIndex
		while true do
			local key
			local k,v = rL[rL.cL]._f(rL[rL.cL]._s,rL[rL.cL]._var)
			rL[rL.cL]._var = k
			if not k and rL.cL == 1 then
				break
			elseif not k then
				-- go up in recursion level
				--print("GOING UP:     "..rL[rL.cL].str.."}")
				rL[rL.cL-1].str = rL[rL.cL-1].str.."\n"..rL[rL.cL].str
				rL.cL = rL.cL - 1
				if rL[rL.cL].vNotDone then
					-- This was a key recursion so add the key string and then doV
					key = "t"..rL[rL.cL].tabIndex.."[t"..tostring(rL[rL.cL+1].tabIndex).."]"
					rL[rL.cL].str = rL[rL.cL].str.."\n"..key.."="
					v = rL[rL.cL].vNotDone
				end
				rL[rL.cL+1] = nil
			else
				-- Handle the key and value here
				if type(k) == "number" then
					key = "t"..rL[rL.cL].tabIndex.."["..tostring(k).."]"
					rL[rL.cL].str = rL[rL.cL].str.."\n"..key.."="
				elseif type(k) == "string" then
					key = "t"..rL[rL.cL].tabIndex.."."..tostring(k)
					rL[rL.cL].str = rL[rL.cL].str.."\n"..key.."="
				else
					-- Table key
					-- Check if the table already exists
					if tabIndex[k] then
						key = "t"..rL[rL.cL].tabIndex.."[t"..tabIndex[k].."]"
						rL[rL.cL].str = rL[rL.cL].str.."\n"..key.."="
					else
						-- Go deeper to stringify this table
						latestTab = latestTab + 1
						rL[rL.cL].str = rL[rL.cL].str.."\nt"..tostring(latestTab).."={}"	-- New table
						rL[rL.cL].vNotDone = v
						rL.cL = rL.cL + 1
						rL[rL.cL] = {}
						rL[rL.cL]._f,rL[rL.cL]._s,rL[rL.cL]._var = pairs(k)
						rL[rL.cL].tabIndex = latestTab
						rL[rL.cL].t = k
						rL[rL.cL].str = ""
						tabIndex[k] = rL[rL.cL].tabIndex
					end		-- if tabIndex[k] then ends
				end		-- if type(k)ends
			end		-- if not k and rL.cL == 1 then ends
			if key then
				rL[rL.cL].vNotDone = nil
				if type(v) == "table" then
					-- Check if this table is already indexed
					if tabIndex[v] then
						rL[rL.cL].str = rL[rL.cL].str.."t"..tabIndex[v]
					else
						local mt = getmetatable(v)
						if mt and mt.__index and mt.__index == metaindex then
							local ID = mt.ID
							rL[rL.cL].str = rL[rL.cL].str.."{}\nsetmetatable("..key..",{ID="..ID..",__index=metaIndex,__newindex=metaNewIndex,__len=getLen,conn=conn,rc=rc,wc=wc,sn=sn})\n"
						else
							-- Go deeper in recursion
							latestTab = latestTab + 1
							rL[rL.cL].str = rL[rL.cL].str.."{}" 
							rL[rL.cL].str = rL[rL.cL].str.."\nt"..tostring(latestTab).."="..key	-- New table
							rL.cL = rL.cL + 1
							rL[rL.cL] = {}
							rL[rL.cL]._f,rL[rL.cL]._s,rL[rL.cL]._var = pairs(v)
							rL[rL.cL].tabIndex = latestTab
							rL[rL.cL].t = v
							rL[rL.cL].str = ""
							tabIndex[v] = rL[rL.cL].tabIndex
							--print("GOING DOWN:",k)
						end
					end
				elseif type(v) == "number" then
					rL[rL.cL].str = rL[rL.cL].str..tostring(v)
					--print(k,"=",v)
				elseif type(v) == "boolean" then
					rL[rL.cL].str = rL[rL.cL].str..tostring(v)				
				else
					rL[rL.cL].str = rL[rL.cL].str..string.format("%q",tostring(v))
					--print(k,"=",v)
				end		-- if type(v) == "table" then ends
			end		-- if doV then ends
		end		-- while true ends here
	end		-- do ends
	return rL[rL.cL].str
end

-- Function to validate the data creation template
local function validateTemplate(template,tableChain)
	local hasArray, hasNumKeys
	if not tableChain then
		tableChain = {}		-- Contains the references to the tables in the hierarchy of the table passed to validateTemplate
							-- This allows to check recursive templates and skip traversing in the recursive loop infinitely
	end
	-- Values can only be Boolean, Number, String and Tables
	-- Keys can only be numbers or strings
	for k,v in pairs(template) do
		if type(k) ~= "number" and type(k) ~= "string" then
			return nil,"Key not a number or string in the template table"
		elseif type(v) ~= "boolean" and type(v) ~= "number" and type(v) ~= "string" and type(v) ~= "table" then
			return nil,"Value not of type boolean,number,string or table"
		end
		if type(k) == "string" and k == "__ARRAY" then
			hasArray = true
		end
		if type(k) == "number" and k > 0 then
			hasNumKeys = true
		end
		if type(v) == "table" and k ~= "__ARRAY" then
			local found
			for i=1,#tableChain do
				if tableChain[i] == v then
					found = true	-- Table already investigated so no need to go in again
					break
				end
			end
			if not found then
				tableChain[#tableChain + 1] = v
				local ret,msg = validateTemplate(v,tableChain)
				if not ret then
					return nil,msg
				end
			end
		end
	end		-- for k,v in pairs(template) do ends
	
	-- If a table has an ARRAY type then it cannot have natural number keys
	if hasArray and hasNumKeys then
		return nil, "Table cannot have natural number keys as well as an ARRAY type"
	end
	-- ARRAY definitions just have 1 element
	if hasArray then
		local numElems = 0
		for k,v in pairs(template.__ARRAY) do
			numElems = numElems + 1
		end
		if numElems > 1 then 
			return nil, "ARRAY definitions should only have 1 element defining the data in the array"
		end
	end
	return true
end

local function file_exists(file)
  local f = io.open(file, "rb")
  if f then f:close() end
  return f ~= nil
end

saveDB = function(table)
	cache.save(table)
end

-- Function to return the module version number
Version = function()
	return "1.14.3.31"
end

openDB = function(database)
	if not file_exists(database) then
		error("Cannot find database file!")
		return
	end
	local conn = env:connect(database)	
	-- Create the database pointer here and return it
	local curr = conn:execute("SELECT * FROM ldb1_DBROOT;")
	if not curr then
		error("Cannot read or find DBROOT table. Not a valid database file.")
	end
	local rec = curr:fetch({},"a")
	if rec and not rec[preName.."ID"] then
		error("Cannot find ID in database record. Database not valid")
	end
	
	local t = {} -- table to pass on
	local metaT = {ID = rec[preName.."ID"]..":ldb1_DBROOT", __index = metaIndex,__newindex=metaNewIndex,__len=getLen, conn=conn,rc={},wc={},sn={}}
	setmetatable(t,metaT)
	return t
end

closeDB = function(t)
	local mt = getmetatable(t)
	if not mt or not mt.__index or not mt.__index == metaIndex then
		return nil,"Not a TableDB table"
	end
	if tostring(mt.conn):match("%(closed%)") then
		return nil,"Database already closed"
	end
	collectgarbage("collect")
	mt.conn:commit()
	return mt.conn:close()
end

-- Populates t with the following keys:
-- 1. TableName
-- 2. NumOfCol
-- 3. Columns
-- 4. Array
-- 5. SimpleElementArray
-- 6. DefTable
local function populateDBmanage(t,name,ts)
	local currIndex = #t+1
	t[currIndex] = {
		["TableName"] = "ldb"..tostring(currIndex).."_"..name,	-- Prepend a unique identifier to the table name to take care of the possibility of similar names at different hierarchy levels
		["DefTable"] = ts 
	}
	if ts[preName.."SimpleElementArray"] then
		t[currIndex].SimpleElementArray = '1'	-- true value
	else
		t[currIndex].SimpleElementArray = 'NULL'
	end
	local numCols = 0
	local cols = ""
	for k,v in pairs(ts) do
		if k ~= preName.."SimpleElementArray" then
			numCols = numCols + 1
			if type(k) == "number" then
				-- Number index on the table
				cols = cols..preName..tostring(k)..":"
				if type(v) == "table" then
					local found
					for i = 1,#t do
						if v==t[i].DefTable then
							found = i
							break
						end
					end
					if not found then
						found = populateDBmanage(t,preName..tostring(k),v)
					end
					cols = cols.."table-"..t[found].TableName..";"
				else
					cols = cols..type(v)..";"
				end
			elseif k == "__ARRAY" then
				-- Array type defined in the table
				numCols = numCols - 1	-- Array does not have a column in the normal record just the elements in the table ARRAYELEMENTS
				-- Get the ARRAY element
				local elemKey,elemVal
				for i,j in pairs(v) do
					-- There should be at most 1 item in the __ARRAY definition
					elemKey = i
					elemVal = j
					break
				end
				if elemKey then		-- Optional to specify the array element. That way it can link to any already defined element
					if type(elemVal) == "table" then
						local found
						for i = 1,#t do
							if elemVal==t[i].DefTable then
								found = i
								break
							end
						end
						if not found then
							found = populateDBmanage(t,elemKey,elemVal)
						end
						t[currIndex].Array = "'"..t[found].TableName.."'"
					else
						local tdef = {[preName.."ELEM"] = elemVal, [preName.."SimpleElementArray"] = true}
						local found = populateDBmanage(t,elemKey,tdef)
						t[currIndex].Array = "'"..t[found].TableName.."'"
					end
				else
					t[currIndex].Array = "'1'"	-- true value
				end
			else
				-- This is a string index 
				cols = cols..k..":"
				if type(v) == "table" then
					local found
					for i = 1,#t do
						if v==t[i].DefTable then
							found = i
							break
						end
					end
					if not found then
						found = populateDBmanage(t,k,v)
					end
					cols = cols.."table-"..t[found].TableName..";"
				else
					cols = cols..type(v)..";"
				end
			end		-- if type(k) == "number" then ends
		end		-- if k ~= preName.."SimpleElementArray" then ends
	end		-- for k,v in pairs(ts) do ends
	if not t[currIndex].Array then
		t[currIndex].Array = 'NULL'
	end
	t[currIndex].NumOfCol = numCols
	t[currIndex].Columns = cols
	return currIndex
end		-- function populateDBmanage(t,name,ts,isArray) ends here

createDB = function(database,template,overwrite)
	local result,err
	result,err = validateTemplate(template)
	if not result then
		error(err)
	end
	if file_exists(database) and not overwrite then
		error("Database file already exists!")
	elseif file_exists(database) then
		os.remove(database)
	end
	-- Now create the database stucture as flat tables
	local dbManage = {
			[0] = {
					["TableName"] = "",
					["NumOfCol"] = 0,
					["Columns"] = "Name1:string;Name2:number;",
					["Array"] = "TableName";
					["SimpleElementArray"] = true,
					["DefTable"] = {}
				}
		}
	populateDBmanage(dbManage,"DBROOT",template)
	local conn = env:connect(database)	
	-- Create the DB Manage table here
	local curr = conn:execute([[CREATE TABLE ]]..preName..[[DBMANAGE (ID INTEGER PRIMARY KEY, TableName TEXT, NumOfCol INTEGER, Columns TEXT, Array TEXT, SimpleElementArray BOOLEAN, DefTable TEXT);]])
	if not curr then
		error("Cannot create database table.")
	end
	-- Create the Array Elements table here
	curr = conn:execute("CREATE TABLE "..preName.."ARRAYELEMENTS (ID INTEGER PRIMARY KEY, PARENTID TEXT, CHILDID TEXT, SEQNO INTEGER);")
	if not curr then
		error("Cannot create database table.")
	end
	-- Now create the database of all tables listed in the dbManage table
	for i = 1,#dbManage do
		-- Add entry in DBMANAGE
		curr = conn:execute([[INSERT INTO ]]..preName..[[DBMANAGE(TableName,NumOfCol,Columns,Array,SimpleElementArray,DefTable) VALUES (']]..dbManage[i].TableName.."',"..dbManage[i].NumOfCol..",'"..dbManage[i].Columns.."',"..dbManage[i].Array..","..dbManage[i].SimpleElementArray..",'"..tableToString(dbManage[i].DefTable)..[[');]])
		if not curr then
			error("Cannot write data into DBMANAGE table")
		end
		-- Create table for the entry here
		local cols
		if dbManage[i].SimpleElementArray == '1' then
			-- SimpleElementArray records do not have HOOKS since the HOOKS go into the parent record
			cols = preName.."ID INTEGER PRIMARY KEY"
		else
			cols = preName.."ID INTEGER PRIMARY KEY,"..preName.."HOOKS TEXT"
		end
		for c in dbManage[i].Columns:gmatch("(.-);") do
			local n,t
			n,t = c:match("(.-):(.+)")
			if t == "string" then
				t = "TEXT"
			elseif t == "number" then
				t = "REAL"
			elseif t == "boolean" then
				t = "BOOLEAN"
			else
				-- Table link
				t = "TEXT"
			end
			cols = cols..","..n.." "..t
		end
		curr = conn:execute("CREATE TABLE "..dbManage[i].TableName.." ("..cols..");")
        if not curr then
			error("Cannot create table:"..dbManage[1].TableName.." in database",2)
		end
	end
	-- Add entry in DBROOT to point to the database root
	curr = conn:execute("INSERT INTO "..dbManage[1].TableName.."("..preName.."HOOKS) VALUES (NULL);")
	if not curr then
		error("Cannot create database entries.")
	end
	conn:commit()
	-- Get the ID of this record
	curr = conn:execute("SELECT last_insert_rowid();")
	if not curr then
		error("Cannot get the last written record's row ID")
	end
	local row = curr:fetch({},"a")
	if not row then
		error("Cannot get the last written record's row ID")
	end
	-- Now to create a database object to return
	local t = {} -- table to pass on
	local readCache = {}
	local writeCache = {}	-- writeCache is needed to keep track of all the data written so that it is not floating in generated tables which are not tracked
	local setNil = {}	-- This stores the keys that have to be set to nil in the existing records the structure is that there are ID keys (ID:TableName) and each key has a table containing keys (with values true) who have to be set to NULL
	
	local metaT = {ID = row["last_insert_rowid()"]..":ldb1_DBROOT", __index = metaIndex,__newindex=metaNewIndex, __len=getLen,conn=conn, rc = readCache, wc=writeCache,sn=setNil}
	setmetatable(t,metaT)
	conn:commit()
	return t
end