On This Page
- 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.
To best understand it let us look at some examples below.
- Trial Period
- Phone Number
- Number of people Reporting
- People Reporting to this Employee
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:
And the output it gives is:
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.
The following template table define a database which has ARRAYs:
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:
running the following test code on it gives the following output:
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:
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.
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.
| | | |