B4J Question Easiest way to create database and run queries on it

kostefar

Active Member
Licensed User
Longtime User
Dear All,

I have a dataset which in itself is not very large nor complicated, 2 rows with each 1500 items, but I realized that the queries that I need to carry out on this would be a lot easier if using SQL or something similar, rather than trying to code it manually with multiple iterations that I cannot get my head around for now.
I would need to create the database each time the program is started. Does anybody have a recommendation of what the simplest solution would be here? JSQL perhaps?

Thanks in advance!
 

kostefar

Active Member
Licensed User
Longtime User
Do you mean you have your data in some text file already and it needs to be loaded each start ?
Hi peacemaker,

I download the data, not in a db format, from the web, but it could also have been in a text file. The data may get updated from time to time, being the reason for not keeping it in a text file.
 
Upvote 0

peacemaker

Expert
Licensed User
Longtime User
So better to make the code to init the SQLIte DB and make the import of the fresh data content via .csv file. Firstly save the data from web into .csv.
 
Upvote 0

kostefar

Active Member
Licensed User
Longtime User
Thanks, so it is not easier to simply add the data from a list for instance, rather than saving it first?
 
Upvote 0

rgarnett1955

Active Member
Licensed User
Longtime User
Hi

An sqLite database is the most flexible way of doing what you want.

Pretty much everything done with a database is done with the Structured Query Language (sql). You can create a database file or an in-memory data base.

You can add a table with fields specified, names, data types etc.

You can extract and sort data using SELECT queries and can do calculations on the fields.

Have a look at Erel's video on sqlite. This is a good starting point.

I use databases for pretty much all the data I store and analyze, it's a breeze.

An alternative in B4x is maps which are useful for storing simple data structures which can be assigned unique keys. These are very handy for storing application data for user application customization. But maps are nowhere near as flexible and powerful as sql databases.

To learn sqLite download sqLite Studio, it's free. Every operation you perform is stored as sql code in a Tab called DDL. i.e.




You can build a database, copy out the sql statements from the DDL, they are just strings into b4x and away you go. You can import test data into you DB using the sqLite Studio import functions. It's much easier to build queries and test them in the sqLite environment than in b4x, particulalry for complex queries using multiple tables and data aggregation. sqLite queries can have parameters which can also be tested.

A typical subroutine to create a DB and create a table within it are shown below:

B4X:
'============================================================================================
Public Sub     tuningVarsSQL_Init
    If tuningVarsSQL.IsInitialized = False Then
        Dim qryStr As String
        Dim rset As ResultSet
        Dim rnum As Int

        Try
            tuningVarsSQL.InitializeSQLite(File.DirData(networkPars.TUNING_DATABASE_PATH), "shorttClockTuningVariables.sqlite3", True)
        Catch
            #IF MIN_LOGGING_ON Or LOGGING_ON
            Log(LastException)
            #END IF
        End Try
        
        qryStr = "PRAGMA auto_vacuum = 1"
        
        Try
            tuningVarsSQL.ExecNonQuery(qryStr)
        Catch
            #IF MIN_LOGGING_ON Or LOGGING_ON
            Log("mcuClient Line 634" & LastException)
            #END IF
        End Try
        
        ' See if the data table exists
        
        qryStr = _
        $"SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type="table" AND name="tblTuningVars");"$
        
        rset = tuningVarsSQL.ExecQuery(qryStr)
        rnum = 0
        Do While rset.NextRow
            rnum = rset.GetInt2(0)
        Loop
        
        If rnum = 0 Then
        qryStr = _
        $"CREATE TABLE tblTuningVars (
            unixEpoch          BIGINT    NOT NULL PRIMARY KEY,
            dateTimeUTC        DATETIME  NOT NULL,
            tEncSP             DOUBLE    NOT NULL,
            tEncSP_Ramp           DOUBLE    NOT NULL,   
            EnclosureTempC     DOUBLE    NOT NULL,
            AmbientTempC       DOUBLE    NOT NULL,
            HeaterDemand       DOUBLE    NOT NULL,
            PID_Op             DOUBLE    NOT NULL,
            AutoManual           DOUBLE    NOT NULL   
        );"$

        Try
            tuningVarsSQL.ExecNonQuery(qryStr)
        Catch
            #IF MIN_LOGGING_ON Or LOGGING_ON
            Log("mcuClient Line 652" & LastException)
            #END IF
        End Try
        
        End If
        
        If tuningVarsSQL.IsInitialized Then
            setDbToWAL(tuningVarsSQL)
            checkPointDataBase(tuningVarsSQL)
        End If
    End If
End Sub


You can use b4x smart strings to create the query string in the "standard" sql formats that make them more readable.

The code above tests for a data base file and if it is not present it creates it , then creates a table called "tblTuningVars"

There is also a query to turn on auto vacuuming, which is used to clear out the space used by deleted records.

Regards
Rob
 
Upvote 0

Daestrum

Expert
Licensed User
Longtime User
Without seeing an example of how the data is laid out, which you describe as 2 rows with 1500 items, it's hard to decide whether a DB is really the best choice.

Other options could be a spreadsheet etc.
 
Upvote 0

MicroDrie

Well-Known Member
Licensed User
Longtime User
If you decide to use an SQlite database, you could use DB Browser for SQLite to design your database and test queries against the design. DB Browser for SQLite can also display the necessary SQL statements to add the table in a SQLite database with an SQL statement.
DB Browser for SQLite Table create statement:
CREATE TABLE "phonebook2" (
    "ID"    INTEGER,
    "name"    TEXT,
    "phonenumber"    TEXT,
    "validDate"    TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
)
 
Upvote 0

peacemaker

Expert
Licensed User
Longtime User
B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim SQL As SQL
    Dim dbName As String = "db.sqlite"
End Sub

Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    '-----db init----------
        'File.Delete(others.Folder, dbName)    'delete old database for clean test
    Try
        DBUtils.CopyDBFromAssets(dbName)
        SQL.Initialize(others.Folder, dbName, True)    'DB
        '-------------------------------------------------------------------
        If SQL.IsInitialized = False Then
            SQL.Initialize(others.Folder, dbName, True)    'DB open to check the version
        End If
        Dim v As Int = DBUtils.GetDBVersion(SQL)
        Dim NewVer As Int = 1    'update here
        If v < NewVer Then
            SQL.Close
            File.Delete(others.Folder, dbName)    'delete old database
            SQL.Initialize(others.Folder, dbName, True)
            v = DBUtils.GetDBVersion(SQL)    'set = 1
            DBUtils.SetDBVersion(SQL, NewVer)    'set new version = NewVer
        End If
        '-------------------------------------------------------------------
        If SQL.IsInitialized = False Then
            SQL.Initialize(others.Folder, dbName, True)
        End If
    Catch
        ToastMessageShow("DB trouble, re-install the app", True)
        DBUtils.CopyDBFromAssets(dbName)
    End Try
    '-----db init end----------
 
    Dim ft As Map, L As List
 
    If DBUtils.TableExists(SQL, "facts") = False Then
        ft.Initialize
        ft.Put("id", DBUtils.DB_INTEGER)
        ft.Put("name", DBUtils.DB_TEXT)
        ft.Put("desc", DBUtils.DB_TEXT)
        DBUtils.CreateTable(SQL, "facts", ft, "id")
     End If
     .....next tables    
End Sub

After this creation of the table - make some import from a text file
 
Upvote 0

kostefar

Active Member
Licensed User
Longtime User
Thanks everyone!

I found the jSQL library easy to use, creating a database without a file doing this:

B4X:
sql1.InitializeSQLite("", ":memory:", True)
    sql1.ExecNonQuery("CREATE TABLE table1 (col1 TEXT, col2 TEXT)")

Executing queries after loading the said data into it works like a charm.
 
Upvote 0

kostefar

Active Member
Licensed User
Longtime User

Thanks for the input, I´ll have a look at SQLiteStudio to see if it can save me time along the way creating queries.
 
Upvote 0

rgarnett1955

Active Member
Licensed User
Longtime User
Yes,

I use db Browser as well as sqLite. I think sqLite is better for database management and design, however DB Browser has a graphing feature which is extremely useful. Ypu can plot against a time field or do cartesian plots of multiple variables. The graphs are good for general analysis, but they can also be sued to quickly show "bad" data points which aren't obvious from the tables of numbers.

Another good sqLite management app is sqLite Expert. This has query by example sql builders and a lot of other features. It is not free however, but because I do a lot of sqLite I own a copy.
 
Upvote 0

rgarnett1955

Active Member
Licensed User
Longtime User
On the issue of whether to use a database or spread sheet I follow the following guidelines:

Database:
- Homogenous data tables of records with fixed fields and definable relationships between each family (table)
- Greater than 1000 records
- Standard calculations
- Fast search and update
- Standard symbolic language requirements where mathematical relationships are explicit, not "hidden" in cells.
- Where data and processes are seperate and distinguishable i.e. a query has operations not data
- High data integrity checking at the table and field level
- No requirement for data display and presentation within the data application itself

Spreadsheet:
- Inhomogenous data with arbitrary data types and "layouts"
- Less than 1000 records
- Non-standard calculations not available in databases
- No requirement for explicit definition of the mathematical relationships. Doesn't matter if the maths is buried in the data.
- Low data integrity checking which is OK for small amounts of data that can be eyeballed.
- High requirement that data and presentation must be kept together.

Most people when confronted with data will open up their spread sheet and create multi headed hydras. Spreadsheets were originally designed for accountants to display aggregated data to management with graphs and small summary tables. They were never designed to hold and process large amounts of data yet their apparent "ease of use" makes them the first choice for data management for a lot of people. They are not easy to use beyond trivial amounts of data and are a disaster to maintain and audit.

It is not just me saying it:

Spread Sheet Clangers.

One of the many power companies I worked for used excel spreadsheets for return on investment calculations when they were in the market to buy another power company. On one project a single spread sheet error over-estimated the revenue by $400 million (20% of revenue) which they found after the purchase had gone through. The reason was the discount factor had no been applied correctly to all revenues streams. Although the formulae were correct they weren't copied correctly to a range of cells.

Databases are easy, sql is easy, data aggregated by queries is easily exported to other applications. Data validation is robust. There are plenty of great database management tools.

Regards
Rob
 
Upvote 0

QSerg

Member
1500 x 2 = 3000 items. I do not know what kind of items you talking about. Lets say 100 bytes each. It means 300k. Nothing. Just keep them in memory as an array.
 
Upvote 0

MicroDrie

Well-Known Member
Licensed User
Longtime User
1500 x 2 = 3000 items. I do not know what kind of items you talking about. Lets say 100 bytes each. It means 300k. Nothing. Just keep them in memory as an array.
Yes, that is completely correct, but by using a SQLite database you do not have to worry about memory management and it becomes much easier to use all kinds of search options. It was in Visual Basic that for the searches on in-memory arrays and collections, the Language-Integrated Query (LINQ) was introduced.
 
Upvote 0

QSerg

Member
In return you have to have an additional software that in return consume not only CPU but memory as well. Writing searching or sorting procedure in an array is child's play.
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…