Database Class

It is used for connecting to databases (currently only SQLite) and running SQL queries.

 

Create → Connect → Query

In order to create a database class, run the following command:

>>db=Database.new()
>>db
You are not connected to a database.

At this stage, an empty database structure is created; however, in order to run SQL queries, a connection must be opened:

>>db:open()

After the selection of a database file, if we query the state of the database object:

>>db
Connected to: C:\Users\gbingol\sciencesuit\_runtime\databases\ThermoFluids.db

 

Now that, a connection has been established, queries can be run. Let's query, which tables are available in the connected SQLite database:

>>db:tables()
MainTable    S_R12    SH_R12    S_R718    S_R22    S_R23    S_R32    S_R125    S_R134A    S_R143A    SH_R718    SH_R22    SH_R23    SH_R134A    S_R717    SH_R32    SH_R125    SH_R143A    SH_R717

 

Since the available table names are already known, we are ready to query the fields in a table:

>>db:fields("S_R12")
T    P    vf    vg    hf    hg    sf    sg

 

Now that we know the table names and fields in a table, we can run different types of SQL queries:

>>tbl,nrow=db:sql("Select hf from S_R12 where hf>140")
>>tbl
140.068    148.076    157.085    168.059    174.92

 

 

Member functions

 

close()

close()

Closes the existing connection to the database.

>>db:open()
>>db
Connected to: C:\Users\gbingol\sciencesuit\_runtime\databases\ThermoFluids.db

>>db:close()
>>db
You are not connected to a database.

 

 

columns

columns(str=)

An alias for the member function fields.

 

 

fields

fields(str=) → table

Returns a Lua table containing the names of the fields (column) existing in the table specified with the argument str.

 

 

open

open([path=])

Opens, therefore connects to, the database at the specified path. If the path is not specified, then an Open Dialog is shown to select the database file.

 

 

sql

sql(query=) → Table, integer, integer

Runs an SQL query. The returned table, a Lua table, contains the result of the query as type string and the two integers are number of rows and columns of the returned Lua table. If the query returns more than 1 columns then the table will be a 2D Lua table, whereas if the query returns a single column, the query will be a 1D Lua table.

 

A query that returns 1D Lua table:

>>tbl,nrow, ncol=db:sql("Select hf from S_R12 where hf>140")
>>tbl
140.068    148.076    157.085    168.059    174.92
>>nrow
5
>>ncol
1

 

A query that returns 2D Lua table:

>>tbl,nrow, ncol=db:sql("Select * from S_R12")
>>tbl
table table table table table table table table ...
>>type(tbl[1])
table
>>nrow
42
>>ncol
8

 

 

tables

tables() → table

Returns a Lua table containing the names of the existing tables in the database.