The information in this app will be stored in a SQLite database.
The database is located in the app's internal folder.
You can find a lot of information on how to use a SQLite database in the booklets. Link: B4X documentation
And in this list there is a SQLite database booklet. Link: B4X SQLite Database
A database has a name and consists of tables with fields that have a certain datatype.
Some tables are connected to each other by means of a relation: the primary key field of one table is linked to a foreign key field in another table. That foreign key field has the same value as the primary key field from the other table.
There is a special computer language called SQL.
With this language you send a formatted question (query) to the database and the database will send a response back in the form of a result set. This result set is a list of records (rows) with in each record a list of fields (columns) that contain one value (having a datatype).
The SQL language has 3 major sections: data definition language (DDL), data manipulation language (DML and data control language (DCL).
SQL statements belonging to the data definition language are: CREATE DATABASE, CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP INDEX, DROP TABLE, DROP DATABASE.
The data manipulation section contains the following statements: INSERT INTO, UPDATE SET, DELETE FROM, SELECT FROM.
The data control language has the following statements: GRANT, REVOKE.
For our app we will be using the CREATE TABLE statement and all the data manipulation statements.
We also use the available library SQL.

So let's start by making a class. Add a new standard class to the project and name it database. Make sure to add it to the parent folder.
Click on the database tab in the IDE.
Before we can use the database we must first initialize it. The code is different in B4A then in B4J. By using the #-directive you can specify the correct statement.
First add the SQL library to the project : check SQL in the Libraries Manager pane of the IDE.
Declare 2 variables: xui and sqllib.

	Private xui As XUI
	Public sqllib As SQL

The initialize method takes 4 arguments: the folder, the database name, the make boolean and the reset boolean.
The reset boolean can be used to remove the database from the project and start with a new one. If you are done testing then set it to false!
Replace the initialize code that is in the database class with:

Public Sub Initialize(folder As String,dbname As String, make As Boolean, reset As Boolean)
	#If B4J
		xui.SetDataFolder(folder)
	#End If
	If reset = True Then
		File.Delete(xui.DefaultFolder,dbname)		' remove db for testing
	End If
	If File.Exists(xui.DefaultFolder,dbname) = False Then
		#If B4J
			sqllib.InitializeSQLite(xui.DefaultFolder,dbname,make)
		#Else
		sqllib.Initialize(xui.DefaultFolder,dbname,make)
		#End If
	Else
		#If B4J
			sqllib.InitializeSQLite(xui.DefaultFolder,dbname,make)
		#Else
		sqllib.Initialize(xui.DefaultFolder,dbname,make)
		#End If
	End If
	Return
End Sub

Now you can declare a variable db in the Class_Globals subroutine of the B4XMainPage tab as a database type (= the name of the database class).

	Private db As database

And in the B4XPage_Created subroutine you can initialize the db object:

	db.Initialize("mybudget","budget_db",True,False)

We will be using a lot of SQL statements and for those occasions we provide a method.
For a simple SQL statement we make this subroutine (in the database tab):

Public Sub select_query(sql As String) As ResultSet
	Private rs As ResultSet = sqllib.ExecQuery(sql)
	Return rs
End Sub

For a prepared select query we make this method:

Public Sub select_query_prepared(sql As String, values() As String) As ResultSet
	Private rs As ResultSet = sqllib.ExecQuery2(sql,values)
	Return rs
End Sub

A prepared statement is used to prevent SQL-injection attacks. The query contains placeholders (?) for the values. These values are inserted in the query after the query was prepared by the database management system. So the contents of the values have no effect on the syntax of the query.
For action queries we use the following subroutine:

Public Sub action_query(sql As String, values As List)
	sqllib.BeginTransaction
	Try
		sqllib.ExecNonQuery2(sql,values)
		sqllib.TransactionSuccessful
	Catch
		Log(LastException.Message)
	End Try
	sqllib.EndTransaction
	Return
End Sub

The transaction statements keep the database in a consistent state. If an action query fails then no changes are made to the database.
The following methods you find below are convenient to have.
The get_last_rowid method selects the last rowid that was added to the table.
The get_num_rows method counts the number of records in a table.
The table_exists method checks if the table exists in the database. If the table doesn't exists you have to create it.
The get_fieldnames method collects the fieldnames from a table. Using this method allows us to make general subroutines that can be used for any table!

Before we can add, change or remove a record from a table we first have to create the tables.
Write a subroutine to create the tables for our project (in B4XMainPage tab above the fill_clv1 subroutine):

Private Sub create_tables
	If db.table_exists("expenses") = False Then
		Private sql As String = "CREATE TABLE expenses (date TEXT, category_id INTEGER, description TEXT, amount TEXT)"
		db.action_query(sql,Null)
	End If
	If db.table_exists("earnings") = False Then
		Private sql As String = "CREATE TABLE earnings (date TEXT, category_id INTEGER, description TEXT, amount TEXT)"
		db.action_query(sql,Null)
	End If
	If db.table_exists("category") = False Then
		Private sql As String = "CREATE TABLE category (name TEXT, reference TEXT)"
		db.action_query(sql,Null)
	End If
	If db.table_exists("settings") = False Then
		Private sql As String = "CREATE TABLE settings (startdate TEXT, enddate TEXT, startbalance TEXT, currency TEXT)"
		db.action_query(sql,Null)
	End If
End Sub

The data types are TEXT except for the category_id fields. The category_id fields contain a foreign key value of type integer that is related to the rowid (integer) of the category table.
You don't have to specify a primary key field. The SQL library provides a rowid field as a primary key field that will auto-increment.
Now call the create_tables subroutine in the B4XPage_Created method below the db.initialize statement.

	create_tables

In the subroutines below we provide a method to add, change or remove a record from a table.
The methods take an argument for the tablename, an argument for the value list and if needed an argument for the rowid.
The add_record method uses the fields list but those lines can be left out. The SQL library requires you to only provide the values.
Place these methods below the create_tables method.

Let's see if we can use the database methods.
Change in the designer the text of sbtn1 to Add (if you didn't do that yet).
In the B4XMainPage tab write a test in the sbtn1_Click subroutine.

Sub sbtn1_Click
	add_record("expenses", Array As String("2022-05-20","1","description","100.00"))
End Sub

To see the result we can add a line below the add_record line that adds the information to the clv1 list.

	clv1.AddTextItem("2022-05-20 1 description 100.00","1")

When you run the app you can tap (or click) on the Add button of the expenses tab.
You should see that a line is added in the list: The add_record method shows the last added rowid in the Logs pane.