B4A=true
Group=Default Group
ModulesStructureVersion=1
Type=Class
Version=9.85
@EndOfDesignText@
#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=\%PROJECT_NAME%.zip

'B4A ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=start&Args=..\..\B4A\%PROJECT_NAME%.b4a 
'B4i ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=start&Args=..\..\B4i\%PROJECT_NAME%.b4i 
'B4J ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=start&Args=..\..\B4J\%PROJECT_NAME%.b4j


' SQLiteLight1  	  2022.07.07	version 1.0
' B4XPages version  
' This is a small project showing the basics of a SQLite database
' The database has 3 columns:
' FirstName			TEXT
' LastName			TEXT
' City					TEXT
'
' For the row ID we use the internal 'rowid' which is automatically incremented by one when a new entry is added.
' When we delete an entry the ID is lost
'
' Following functions are supported:
' Add			adds a new entry
' Delete	deletes the current entry
' Update	updates the current entry
' First		shows the first entry
' Prev		shows the prvious entry
' Next		shows the next entry
' Last		shows the last entry
'
' The database name, the table name and the column names are hard coded, to make the code better readable
' The user interface is kept very simple
'
' Written by : klaus

Sub Class_Globals
	Private Root As B4XView
	Private xui As XUI
	
	Private lblToastMessage As B4XView	' for ToastMessages
	Public ToastMessageTimer As Timer
	
	Public SQL1 As SQL
	Public SQLDataBasePath As String
	Public lblRowID As B4XView
	Public edtFirstName, edtLastName, edtCity As B4XView

	Private CurrentIndex = 0 As Int		' index of the current entry
	
	Public RowIDList As List		' list containing the RowIDs of the database
	' we need it because the IDs can be different from the list indexes
	' if we delete an entry its ID is lost
End Sub

Public Sub Initialize
'	B4XPages.GetManager.LogEvents = True
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
	Root = Root1
	Root.LoadLayout("MainPage")
	
	B4XPages.SetTitle(B4XPages.MainPage, "SQLiteLight1")
	
	'initialize the Timer for the ToastMessages
	ToastMessageTimer.Initialize("ToastMessageTimer", 1000)
	
	'we define the default database folder
	#If B4J
	xui.SetDataFolder("SQLiteLight1") 'this is needed only for B4J
	#End If
	SQLDataBasePath = xui.DefaultFolder
	
'	File.Delete(SQLDataBasePath, "persons.db") ' only for testing, removes the database
		
	'check if the database already exists
	If File.Exists(SQLDataBasePath, "persons.db") = False Then
		'if not, initialize it
		#If B4J
		'the initialization is different in B4J
		SQL1.InitializeSQLite(SQLDataBasePath, "persons.db", True)
		#Else
		SQL1.Initialize(SQLDataBasePath, "persons.db", True)
		#End If
		'and create it
		CreateDataBase
	Else
		'if yes, initialize it
		#If B4J
		SQL1.InitializeSQLite(SQLDataBasePath, "persons.db", True)
		#Else
		SQL1.Initialize(SQLDataBasePath, "persons.db", True)
		#End If
	End If

	'initialize the ID list
	RowIDList.Initialize
	
	'read the database
	ReadDataBase
	
	'show the first entry
	CurrentIndex = 0
	ShowEntry(CurrentIndex)
End Sub

Private Sub B4XPage_Disappear
	'when the page is closed we close the database
	SQL1.Close
End Sub

Private Sub CreateDataBase
	Dim Query As String
	
	'create the database with 3 columns
	Query = "CREATE TABLE persons (FirstName TEXT, LastName TEXT, City TEXT)"
	SQL1.ExecNonQuery(Query)
	
	'Fills a few entries
	Query = "INSERT INTO persons VALUES (?, ?, ?)"
	SQL1.ExecNonQuery2(Query, Array As String("John", "KENNEDY", "New York"))
	SQL1.ExecNonQuery2(Query, Array As String("Peter", "FALK", "Los Angeles"))
	SQL1.ExecNonQuery2(Query, Array As String("Jack", "LONDON", "Seattle"))
	SQL1.ExecNonQuery2(Query, Array As String("Ronald", "REGAN", "Los Angeles"))
End Sub

Private Sub ReadDataBase
	Dim ResultSet1 As ResultSet
	
	RowIDList.Clear				'initialize the RowID list
	'We read only the internal 'rowid' column and put the rowids in a List
	ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM persons")
	Do While ResultSet1.NextRow
		RowIDList.Add(ResultSet1.GetInt2(0))		'add the rowid's to the ID list
	Loop
	CurrentIndex = 0			'set the current index to 0
	ResultSet1.Close			'close the cursor, we do not need it anymore
End Sub

Private Sub ShowEntry(EntryIndex As Int)
	Dim ResultSet1 As ResultSet
	Dim RowID As Int
	
	If RowIDList.Size = 0 Then 		'check if the database is empty
		Return											'if yes leave the routine
	End If
	
	RowID = RowIDList.Get(EntryIndex)		'get the RowID for the given entry index
	'read the entry with the given ID
	ResultSet1 = SQL1.ExecQuery("SELECT * FROM persons WHERE rowid = " & RowID)
	lblRowID.Text = " " & RowID														'display the RowID
	ResultSet1.NextRow																		'set the cursor
	edtFirstName.Text = ResultSet1.GetString("FirstName")	'read the FirstName column
	edtLastName.Text = ResultSet1.GetString("LastName")		'read the LasstName column
	edtCity.Text = ResultSet1.GetString("City")						'read the City column
	ResultSet1.Close									'close the ResultSet, we do not need it anymore
End Sub

Private Sub AddEntry
	Dim Query As String
	Dim ResultSet1 As ResultSet
	Dim RowID As Int
	
	'we check if all fields are filled
	If edtFirstName.Text = "" Or edtLastName.Text = "" Or edtCity.Text = "" Then
		xui.MsgboxAsync("One or more data is missing", "Missing data")
		Return
	End If
	
	'we check if an entry with the same data already exists
	Query = "SELECT * FROM persons WHERE FirstName = ? AND LastName = ? AND City = ?"
	ResultSet1 = SQL1.ExecQuery2(Query, Array As String (edtFirstName.Text, edtLastName.Text, edtCity.Text))

	If ResultSet1.NextRow = True Then
		'if it exists show a message and do nothing else
		ShowToastMessage("This entry already exists", False)
	Else
		'if not, add the entry
		'we use ExecNonQuery2 because it's easier, we don't need to take care of the data types
		Query = "INSERT INTO persons VALUES (?, ?, ?)"
		SQL1.ExecNonQuery2(Query, Array As String(edtFirstName.Text, edtLastName.Text, edtCity.Text))
		
		ShowToastMessage("Entry added", False)	' confirmation for the user
		
		'to display the ID of the last entry we read the max value of the ID column
		RowID = SQL1.ExecQuerySingleResult("SELECT max(rowid) FROM persons")
		RowIDList.Add(RowID)								'add the last ID to the list
		CurrentIndex = RowIDList.Size - 1		'set the current index to the last one
		lblRowID.Text = " " & RowID					'display the last index
	End If
	ResultSet1.Close						'close the ResultSet, we do not need it anymore
End Sub

Private Sub DeleteEntry
	Private Query As String
	
	'ask the user for confirmation
	Dim sf As Object = xui.Msgbox2Async("Do you really want to delete " & edtFirstName.Text & " " & edtLastName.Text, "Delete entry", "Yes", "", "No", Null)
	Wait For (sf) Msgbox_Result (Result As Int)
	If Result = xui.DialogResponse_Positive Then
		Query = "DELETE FROM persons WHERE rowid = " & RowIDList.Get(CurrentIndex)
		SQL1.ExecNonQuery(Query)									'delete the entry
		RowIDList.RemoveAt(CurrentIndex)					'remove the ID from the list
		If CurrentIndex = RowIDList.Size Then			'if the current index is the last one
			CurrentIndex = CurrentIndex - 1					'decrement it by 1
		End If
		ShowEntry(CurrentIndex)										'show the next entry
		ShowToastMessage("Entry deleted", False)	'confirmation for the user
	End If
End Sub

Private Sub UpdateEntry
	Dim Query As String
	
	Query = "UPDATE persons Set FirstName = ?, LastName = ?, City = ? WHERE rowid = " & RowIDList.Get(CurrentIndex)
	SQL1.ExecNonQuery2(Query, Array As String(edtFirstName.Text, edtLastName.Text, edtCity.Text))
	ShowToastMessage("Entry updated", False)
End Sub

Private Sub btnAdd_Click
	AddEntry
End Sub

Private Sub btnDelete_Click
	DeleteEntry
End Sub

Private Sub btnUpdate_Click
	UpdateEntry
End Sub

Private Sub btnPrevious_Click
	If CurrentIndex > 0 Then
		CurrentIndex = CurrentIndex - 1
		ShowEntry(CurrentIndex)
	End If
End Sub

Private Sub btnNext_Click
	If CurrentIndex < RowIDList.Size - 1 Then
		CurrentIndex = CurrentIndex + 1
		ShowEntry(CurrentIndex)
	End If
End Sub

Private Sub btnFirst_Click
	CurrentIndex = 0
	ShowEntry(CurrentIndex)
End Sub

Private Sub btnLast_Click
	CurrentIndex = RowIDList.Size - 1
	ShowEntry(CurrentIndex)
End Sub

Private Sub ShowToastMessage(Message As String, LongDuration As Boolean)
	lblToastMessage.Text = Message
	
	If LongDuration Then
		ToastMessageTimer.Interval = 2000
	Else
		ToastMessageTimer.Interval = 1000
	End If
	ToastMessageTimer.Enabled = True
	lblToastMessage.Visible = True
End Sub

Private Sub ToastMessageTimer_Tick
	lblToastMessage.Visible = False
	ToastMessageTimer.Enabled = False
End Sub