' Code module: DB_Cassa
' Responsabilità: apertura DB, migrazioni, CRUD tCassa
Sub Process_Globals
Public SQL1 As SQL
Private sDbName As String = "appdata.db"
End Sub
Public Sub Initialize
Dim dbPath As String = GetAppDataDir
If File.Exists(dbPath, sDbName) = False Then
SQL1.Initialize(dbPath, sDbName, True)
EnsureSchema
Else
SQL1.Initialize(dbPath, sDbName, True)
EnsureSchema ' idempotente
End If
End Sub
' Percorso portabile
Private Sub GetAppDataDir As String
#If B4A
Return File.DirInternal
#Else If B4i
Return File.DirDocuments
#Else If B4J
Return File.DirApp
#End If
End Sub
' Crea/Migra schema
Private Sub EnsureSchema
SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS tCassa (idCassa TEXT PRIMARY KEY, Nome TEXT NOT NULL)")
SQL1.ExecNonQuery("CREATE INDEX IF NOT EXISTS idx_tCassa_Nome ON tCassa (Nome)")
End Sub
' ------- Helpers -------
Private Sub JoinList(lst As List, sep As String) As String
Dim sb As StringBuilder
sb.Initialize
For i = 0 To lst.Size - 1
If i > 0 Then sb.Append(sep)
sb.Append(lst.Get(i))
Next
Return sb.ToString
End Sub
Private Sub IndexOfIgnoreCase(arr() As String, s As String) As Int
For i = 0 To arr.Length - 1
If arr(i).EqualsIgnoreCase(s) Then Return i
Next
Return -1
End Sub
' ------- Upsert "generico" sulla classe (B4A/B4J usa JavaObject; su B4i cadremo su ToMap) -------
Public Sub Upsert(obj As Object)
#If B4A Or B4J
Dim jo As JavaObject = obj
Dim fields() As String = jo.GetField("_db_orm_fields")
Dim cols As List, qmarks As List, args As List
cols.Initialize : qmarks.Initialize : args.Initialize
For Each f As String In fields
cols.Add(f)
qmarks.Add("?")
args.Add(jo.GetField("_" & f.ToLowerCase))
Next
Dim sqlText As String = $"INSERT OR REPLACE INTO tCassa (${JoinList(cols, ",")}) VALUES (${JoinList(qmarks, ",")})"$
SQL1.ExecNonQuery2(sqlText, args)
#Else If B4i
' in B4i niente JavaObject: usiamo ToMap
Dim m As Map = CallSub(obj, "ToMap")
SQL1.ExecNonQuery2("INSERT OR REPLACE INTO tCassa (idCassa, Nome) VALUES (?,?)", _
Array(m.Get("idCassa"), m.Get("Nome")))
#End If
End Sub
' ------- Update solo colonne non-PK, WHERE PK -------
Public Sub UpdateByPK(obj As Object)
#If B4A Or B4J
Dim jo As JavaObject = obj
Dim fields() As String = jo.GetField("_db_orm_fields")
Dim pks() As String = jo.GetField("_db_orm_pks")
Dim setParts As List, whereParts As List, args As List
setParts.Initialize : whereParts.Initialize : args.Initialize
For Each f As String In fields
If IndexOfIgnoreCase(pks, f) = -1 Then
setParts.Add(f & "=?")
args.Add(jo.GetField("_" & f.ToLowerCase))
End If
Next
For Each pk As String In pks
whereParts.Add(pk & "=?")
args.Add(jo.GetField("_" & pk.ToLowerCase))
Next
Dim sqlText As String = $"UPDATE tCassa SET ${JoinList(setParts, ", ")} WHERE ${JoinList(whereParts, " AND ")}"$
SQL1.ExecNonQuery2(sqlText, args)
#Else If B4i
Dim m As Map = CallSub(obj, "ToMap")
SQL1.ExecNonQuery2("UPDATE tCassa SET Nome=? WHERE idCassa=?", _
Array(m.Get("Nome"), m.Get("idCassa")))
#End If
End Sub
' ------- Get by id -------
Public Sub GetCassa(id As String) As tCassa
Dim rs As ResultSet = SQL1.ExecQuery2( "SELECT idCassa, Nome FROM tCassa WHERE idCassa=?", Array As String(id))
Dim c As tCassa
If rs.NextRow Then
c.Initialize
c.FromCursor(rs)
End If
rs.Close
Return c
End Sub
' ------- List all (con ricerca facoltativa per Nome) -------
Public Sub ListCasse(OptionalLikeNome As String) As List
Dim out As List : out.Initialize
Dim rs As ResultSet
If OptionalLikeNome = "" Then
rs = SQL1.ExecQuery("SELECT idCassa, Nome FROM tCassa ORDER BY Nome")
Else
rs = SQL1.ExecQuery2("SELECT idCassa, Nome FROM tCassa WHERE Nome LIKE ? ORDER BY Nome", Array As String("%" & OptionalLikeNome & "%"))
End If
Do While rs.NextRow
Dim c As tCassa
c.Initialize
c.FromCursor(rs)
out.Add(c)
Loop
rs.Close
Return out
End Sub
' ------- Delete -------
Public Sub DeleteCassa(id As String)
SQL1.ExecNonQuery2("DELETE FROM tCassa WHERE idCassa=?", Array(id))
End Sub
)
