tableDB

20 August 2019 Link

On This Page



Description

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:
  1. This approach is more scalable since a relational database is much more scalable as opposed to storing Lua tables in files
  2. 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
  3. 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.

Examples

Note: Both examples require luasql sqlite3 module accessible by lua.

Example 1 - A simple recursive data structure

Suppose I have a program that is saving an Organization Hierarchy structure in a Lua table. Employee Data has the following information:
  1. Name
  2. Title
  3. Trial Period
  4. Phone Number
  5. Number of people Reporting
  6. People Reporting to this Employee
And suppose the company allows at most 4 people to report to any employee. So this is a tree hierarchy with every employee having at most 4 subordinate employee reporting to him. A template table for this would be:

  1. templateT = {
  2.             Name = "name",
  3.             Title = "title",
  4.             TrialPeriod = true,
  5.             NumOfReportees = 4
  6. }
  7. templateT[1] = templateT    -- 1st reporting employee table
  8. templateT[2] = templateT    --  2nd reporting employee table
  9. templateT[3] = templateT    --  3rd reporting employee table
  10. templateT[4] = templateT    --  4th reporting employee table


Note the table is defined to cover the maximum data. The employee tables has the same structure so they are defined recursively back to the main table.

A sample program to use the above template (which is written to the file testTables2.lua) is presented below with the sample output:
  1. tdb = require("tableDB")
  2. dofile("testTables2.lua")
  3.  
  4. print(templateT)
  5.  
  6. emp = tdb.createDB("testdb2",templateT,true)
  7.  
  8. emp.Name = "ABC"
  9. emp.Title = "CEO"
  10. emp.TrialPeriod = false
  11. emp.NumOfReportees = 2
  12.  
  13. emp[1] = {
  14.         Name = "DEF",
  15.         Title = "VP",
  16.         TrialPeriod = false,
  17.         NumOfReportees = 1
  18. }
  19. emp[1][1] = {
  20.         Name = "GHI",
  21.         Title = "Engineer",
  22.         TrialPeriod = false,
  23.         NumOfReportees = 0
  24. }
  25. emp[2] = {
  26.         Name = "JKL",
  27.         Title = "VP",
  28.         TrialPeriod = false,
  29.         NumOfReportees = 1
  30. }
  31. emp[2][1] = {
  32.         Name = "MNO",
  33.         Title = "Engineer",
  34.         TrialPeriod = true,
  35.         NumOfReportees = 0
  36. }
  37.  
  38. print(emp[2][1].Name)    -- Would print MNO
  39. -- Lets add something not defined in the original template
  40. emp[2].Address = "123 Fortune Street"
  41.  
  42. tdb.saveDB(emp) -- This saves all the data into the database
  43. tdb.closeDB(emp) -- close the file
  44.  
  45. -- Open the file again and do more operations
  46. emp = tdb.openDB("testdb2")
  47. print(emp[2])    -- print the table reference
  48. print(emp[2].Title) -- prints VP
  49. print(emp[1][1].Title) -- prints Engineer
  50. print(emp[2].Address)  -- prints 123 Fortune Street

And the output it gives is:
table: 007D1BE8
MNO
table: 00823E88
VP
Engineer
123 Fortune Street

A thing to note here is that even though the template table did not define the field address tableDB was still able to accommodate and store it. So why define template tables at all? Everything defined in the template table i.e. all the keys are mapped to different columns of a database. If some key that was not defined in the initial template database comes up then that is stored as Lua code in one combined column for the record. So retrieving that new data and putting it there is not as efficient as it would be for the structure defined in the template.

Example 2 - A recursive hierarchical structure with variable number of elements

This example covers the most diverse type of structure that we can have. In the previous example our database would only grow recursively but in many cases the database has sequential elements like a list of elements which grows. It is like an Array. Lua tables are used like Arrays by simply having numerical indices as keys. tableDB separates out numerical keys and Array types. This allows definition of database records which do not behave like arrays with numerical keys like we saw in Example 1 above.
The following template table define a database which has ARRAYs:
  1. templatet = {
  2.     __ARRAY = {
  3.         Tasks = {
  4.             TaskID = "T1",
  5.             Title = "This is root task 1",
  6.             Estimate = 3,
  7.             Private = true,
  8.             SubTasks = {
  9.                 tasks = 0,
  10.                 __ARRAY = {
  11.  
  12.                 }
  13.             }
  14.         }   -- Tasks end
  15.     }    -- __ARRAY ends
  16. }
  17.  
  18. templatet.__ARRAY.Tasks.SubTasks.__ARRAY.Tasks = templatet.__ARRAY.Tasks


This database is more complex but it is still a simple recursive Lua table. The only new thing here is the __ARRAY keyword. This keyword when used as a key in the template table tells tableDB that we are defining an ARRAY for this table. So the main database represented by templatet table only has 1 type of data and that is an ARRAY. It has an Array of records which is defined by the table Tasks. The table Tasks has some elements and it has a table called SubTasks which again has an Array of records. This Array of records is recursively again the Tasks table as defined by the code line in the end.
So saving this in file testTables1.lua and a sample table like:
  1. sample = {
  2.     [1] = {
  3.             TaskID = "T1",
  4.             Title = "This is root task 1",
  5.             Estimate = 3,
  6.             Private = nil,
  7.             SubTasks = {
  8.                 tasks = 1
  9.             },
  10.             Hello=2,
  11.             [4] = "This is it",
  12.             [3] = "New data"
  13.         }, -- [1] ends
  14.     [2] = {
  15.             TaskID = "T2",
  16.             Title = "This is root task 2",
  17.             Estimate = 4.4,
  18.             Private = nil,
  19.             SubTasks = {
  20.                 tasks = 2,
  21.                 [1] = {
  22.                         TaskID = "T2_1",
  23.                         Title = "This is Task 1 of T2",
  24.                         Estimate = 2.1,
  25.                         Private = nil,
  26.                         SubTasks = {
  27.                             tasks = 3
  28.                         }
  29.                     }, -- [1] ends
  30.                 [2] = {
  31.                         TaskID = "T2_2",
  32.                         Title = "This is Task 2 of T2",
  33.                         Estimate = 2.2,
  34.                         Private = nil,
  35.                         SubTasks = {
  36.                             tasks = 4,
  37.                             [1] = {
  38.                                 TaskID = "T2_3",
  39.                                 Title = "This is Task 3 of T3",
  40.                                 Estimate = 2.3,
  41.                                 Private = nil,
  42.                                 SubTasks = {
  43.                                     tasks = 5
  44.                                 }
  45.                             } -- [] ends
  46.                         }
  47.                     } -- [2] ends
  48.             },    -- Subtasks ends
  49.             [3] = true
  50.         }, -- [2] ends
  51.     [3] = {
  52.             TaskID = "T3",
  53.             Title = "This is root task 3",
  54.             Estimate = 1.2,
  55.             Private = false,
  56.             SubTasks = {
  57.                 tasks = 6,
  58.                 [1] = {
  59.                         TaskID = "T3_1",
  60.                         Title = "This is Task 1 of T3",
  61.                         Estimate = 3.1,
  62.                         Private = true,
  63.                         SubTasks = {
  64.                             tasks = 7
  65.                         }
  66.                     } -- [1] ends
  67.             },    -- Subtasks ends
  68.         }, -- [3] ends
  69. }


running the following test code on it gives the following output:
  1. tdb = require("tableDB")
  2. dofile("testTables2.lua")
  3.  
  4.  
  5. print(templatet)
  6.  
  7. x = tdb.createDB("testdb2",templatet,true)
  8.  
  9. for i = 1,#sample do
  10.     x[i] = sample[i]
  11. end
  12. tdb.saveDB(x)
  13. tdb.closeDB(x)
  14.  
  15. y = tdb.openDB("testdb2")
  16. print(tableToString(y))    -- Prints the whole table as a string
  17. print(y[1])    -- prints the table reference
  18. print(y[1].TaskID)  -- prints T1
  19. print(y[1][3])        -- prints New Data
  20. print(y[1].Private) -- prints nil
  21. print(y[1][4])        -- prints This is it
  22. print(y[1].Hello)    -- prints 2
  23. print(y[3].Private)    -- prints true
  24. print(y[2].SubTasks[1].SubTasks.tasks)    -- prints 3
  25.  
  26. y[4] = {
  27.         TaskID = "T4",
  28.         Title = "This is root task 4",
  29.         Private = true
  30. }
  31. y[3].SubTasks = nil
  32. y[2].SubTasks.tasks = 10
  33.  
  34.  
  35. print(tableToString(y))    -- prints the whole table (after modifications) as a string
  36.  
  37. tdb.saveDB(y)
  38. tdb.closeDB(y)


For the definition of tableToString see the Appendix Section. All the files for each example can also be downloaded in the Appendix Section.

The output is:

table: 008DB818
{[1]={[3]="New data",[4]="This is it",Estimate=3,Hello=2,SubTasks={tasks=1},Task
ID="T1",Title="This is root task 1"},[2]={[3]="true",Estimate=4.4,SubTasks={[1]=
{Estimate=2.1,SubTasks={tasks=3},TaskID="T2_1",Title="This is Task 1 of T2"},[2]
={Estimate=2.2,SubTasks={[1]={Estimate=2.3,SubTasks={tasks=5},TaskID="T2_3",Titl
e="This is Task 3 of T3"},tasks=4},TaskID="T2_2",Title="This is Task 2 of T2"},t
asks=2},TaskID="T2",Title="This is root task 2"},[3]={Estimate=1.2,Private="true
",SubTasks={[1]={Estimate=3.1,Private="true",SubTasks={tasks=7},TaskID="T3_1",Ti
tle="This is Task 1 of T3"},tasks=6},TaskID="T3",Title="This is root task 3"}}
table: 0082BB20
T1
New data
nil
This is it
2
true
3
{[1]={[3]="New data",[4]="This is it",Estimate=3,Hello=2,SubTasks={tasks=1},Task
ID="T1",Title="This is root task 1"},[2]={[3]="true",Estimate=4.4,SubTasks={[1]=
{Estimate=2.1,SubTasks={tasks=3},TaskID="T2_1",Title="This is Task 1 of T2"},[2]
={Estimate=2.2,SubTasks={[1]={Estimate=2.3,SubTasks={tasks=5},TaskID="T2_3",Titl
e="This is Task 3 of T3"},tasks=4},TaskID="T2_2",Title="This is Task 2 of T2"},t
asks=10},TaskID="T2",Title="This is root task 2"},[3]={Estimate=1.2,Private="tru
e",TaskID="T3",Title="This is root task 3"},[4]={TaskID="T4",Title="This is root
 task 4",Private="true"}}

As seen above the pairs and ipairs function also work on these database tables. For Lua 5.2 the # operator will also work on these tables to give the length of the table.

Word about Arrays

The ARRAY datatype has a single value with a string key which defines the data type that would be the elements of the array. This data type may itself be a table, string, number or boolean. If there is nothing in the ARRAY definition then it is a general array and can store any other record in the database at each position.

Backend database structure

In the backend the database contains the following tables to manage the database:
1. DBMANAGE - stores info about all the user tables in the database, about their column types etc.
2. ARRAYELEMENTS - table to store all links to ARRAY elements. Having an additional table to manage ARRAY element links makes it possible to have records with multiple parents and children.
3. DBROOT is the root table containing the template table definition. Sub tables in the template table generate other tables in the database.

For each key in the template table a column is created in the database table. Apart from that the database table has 2 additional columns:
1. ID - this is the record ID for each record in the table. Used by the database to identify records uniquely.
2. HOOKS - This is the column that holds any lua code associated with storing any keys that were not defined in the template but were stored in the table by the user

Links to other tables/records are in the form of 'ID:tableName' where ID is the corresponding record ID and the tableName is the name of the table where the record exists.

Appendix

  1. Download the module here
  2. Download example files example1, example2
  3. tableToString function:
  1. function tableToString(t)
  2.     local rL = {cL = 1}    -- Table to track recursion into nested tables (cL = current recursion level)
  3.     rL[rL.cL] = {}
  4.     do
  5.         rL[rL.cL]._f,rL[rL.cL]._s,rL[rL.cL]._var = pairs(t)
  6.         rL[rL.cL].str = "{"
  7.         rL[rL.cL].t = t
  8.         while true do
  9.             local k,v = rL[rL.cL]._f(rL[rL.cL]._s,rL[rL.cL]._var)
  10.             rL[rL.cL]._var = k
  11.             if not k and rL.cL == 1 then
  12.                 break
  13.             elseif not k then
  14.                 -- go up in recursion level
  15.                 if string.sub(rL[rL.cL].str,-1,-1) == "," then
  16.                     rL[rL.cL].str = string.sub(rL[rL.cL].str,1,-2)
  17.                 end
  18.                 --print("GOING UP:     "..rL[rL.cL].str.."}")
  19.                 rL[rL.cL-1].str = rL[rL.cL-1].str..rL[rL.cL].str.."}"
  20.                 rL.cL = rL.cL - 1
  21.                 rL[rL.cL+1] = nil
  22.                 rL[rL.cL].str = rL[rL.cL].str..","
  23.             else
  24.                 -- Handle the key and value here
  25.                 if type(k) == "number" then
  26.                     rL[rL.cL].str = rL[rL.cL].str.."["..tostring(k).."]="
  27.                 else
  28.                     rL[rL.cL].str = rL[rL.cL].str..tostring(k).."="
  29.                 end
  30.                 if type(v) == "table" then
  31.                     -- Check if this is not a recursive table
  32.                     local goDown = true
  33.                     for i = 1, rL.cL do
  34.                         if v==rL[i].t then
  35.                             -- This is recursive do not go down
  36.                             goDown = false
  37.                             break
  38.                         end
  39.                     end
  40.                     if goDown then
  41.                         -- Go deeper in recursion
  42.                         rL.cL = rL.cL + 1
  43.                         rL[rL.cL] = {}
  44.                         rL[rL.cL]._f,rL[rL.cL]._s,rL[rL.cL]._var = pairs(v)
  45.                         rL[rL.cL].str = "{"
  46.                         rL[rL.cL].t = v
  47.                         --print("GOING DOWN:",k)
  48.                     else
  49.                         rL[rL.cL].str = rL[rL.cL].str.."\""..tostring(v).."\""
  50.                         rL[rL.cL].str = rL[rL.cL].str..","
  51.                         --print(k,"=",v)
  52.                     end
  53.                 elseif type(v) == "number" then
  54.                     rL[rL.cL].str = rL[rL.cL].str..tostring(v)
  55.                     rL[rL.cL].str = rL[rL.cL].str..","
  56.                     --print(k,"=",v)
  57.                 else
  58.                     rL[rL.cL].str = rL[rL.cL].str..string.format("%q",tostring(v))
  59.                     rL[rL.cL].str = rL[rL.cL].str..","
  60.                     --print(k,"=",v)
  61.                 end        -- if type(v) == "table" then ends
  62.             end        -- if not rL[rL.cL]._var and rL.cL == 1 then ends
  63.         end        -- while true ends here
  64.     end        -- do ends
  65.     if string.sub(rL[rL.cL].str,-1,-1) == "," then
  66.         rL[rL.cL].str = string.sub(rL[rL.cL].str,1,-2)
  67.     end
  68.     rL[rL.cL].str = rL[rL.cL].str.."}"
  69.     return rL[rL.cL].str
  70. end


References

| | | |
| | | |