B4A=true
Group=Default Group
ModulesStructureVersion=1
Type=Class
Version=11.8
@EndOfDesignText@
Sub Class_Globals
	
	Public kvs As KeyValueStore
	Public sqlite As SQL
	Public mysql As JdbcSQL
	
	Public enablesqllog As Boolean = True

End Sub

Public Sub Initialize
	
	MysqlDisableStrictMode
	
	SqliteInit(False)
	
	KvsInit(False)
	
End Sub

Sub MysqlDisableStrictMode
	
	Private jo As JavaObject

	jo.InitializeStatic("android.os.Build.VERSION")

	If jo.GetField("SDK_INT") > 9 Then

		Private policy As JavaObject

		policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)

		policy = policy.RunMethodJO("permitAll", Null).RunMethodJO("build", Null)

		Private sm As JavaObject

		sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))

	End If
	
End Sub

Sub SqliteInit(ResetDB As Boolean)
	
	Private rp As RuntimePermissions

	Private datadir As String = rp.GetSafeDirDefaultExternal("")

	sqlite.Close
	
	If ResetDB = True Then
		
		File.Delete(datadir, "sqlite.db") 'DELETES SQLITE DATABASE

	End If

	sqlite.Initialize(datadir, "sqlite.db", True)

End Sub

Sub KvsInit(ResetKVS As Boolean)
	
	Private rp As RuntimePermissions
	
	If ResetKVS = True Then
		
		File.Delete(rp.GetSafeDirDefaultExternal(""),"keyvaluestore.dat")

	End If
	
	kvs.Initialize(rp.GetSafeDirDefaultExternal(""), "keyvaluestore.dat")
	
End Sub


Sub SqliteTryExecNonQuery(sql As String) As Boolean 'Ignore
	
	Private result As Boolean

	If enablesqllog Then Log(sql)

	Try

		sqlite.ExecNonQuery(sql)
		
		result = True
		
	Catch
		
		result = False
		
	End Try
	
	If result = False Then
		
		LogException

	End If
	
	Return result

End Sub


Sub SqliteVacuum 'Ignore
	
	Private sql As String = "VACUUM"
	
	SqliteTryExecNonQuery(sql)
	
End Sub

Sub SqliteZap(table As String) 'Ignore DELETES ALL ROWS FROM A SQLITE TABLE
	
	Private sql As String = $"DELETE FROM "${table}""$
			
	SqliteTryExecNonQuery(sql)
	
End Sub

Sub SqliteDropTable(table As String) 'Ignore
	
	Private sql As String = $"DROP TABLE IF EXISTS "${table}""$
	
	SqliteTryExecNonQuery(sql)

End Sub

Sub SqliteToMysql(sqlitetable As String, mysqltable As String, DropBefore As Boolean) As Boolean 'ignore
	
	'CREATES A MYSQL TABLE FROM AN SQLITE TABLE. THE SQLITE TABLE MUST HAVE BEEN CREATED USING MYSQL_TO_SQLITE (BECAUSE OF TYPES)

	Private rs As ResultSet

	Private Sql As StringBuilder

	Private result As Boolean = False
	
	If mysql.IsInitialized = False Then
	
		Return result
	
	End If
	
	Sql.Initialize
	
	Sql.Append($"PRAGMA table_info("${sqlitetable}")"$)
	
	If enablesqllog Then Log(Sql.ToString)

	rs = sqlite.ExecQuery(Sql.ToString)
	
	If rs.RowCount = 0 Then
		
		rs.Close
		
		Return result
		
	End If
	
	If DropBefore = True Then
		
		MysqlDropTable(mysqltable)
				
	End If
	
	Sql.Initialize
	
	Sql.Append($"CREATE TABLE IF NOT EXISTS `${mysqltable}` ("$ & CRLF)

	Private firstrow As Boolean = True
	
	Private primarykey As String = ""

	Do While rs.NextRow
		
		If firstrow = False Then Sql.Append(", " & CRLF) Else firstrow = False
		
		Sql.Append($"`${rs.GetString("name")}` "$)
		
		Sql.Append($"${rs.GetString("type")}"$)
		
		If rs.GetInt("notnull") = 1 Then
			
			Sql.Append(" NOT NULL")
			
		End If
	
		If rs.GetString("dflt_value") <> Null Then
			
			Sql.Append(" DEFAULT " & rs.GetString("dflt_value"))
			
		End If
		
		If rs.GetInt("pk") = 1 Then primarykey = $"PRIMARY KEY (`${rs.GetString("name")}`)"$

	Loop
	
	rs.Close

	If primarykey.Length > 0 Then
		
		Sql.Append("," & CRLF & primarykey)
		
	End If
	
	Sql.Append(")")

	Sql.Append(" ENGINE=InnoDB DEFAULT CHARSET=utf8")

	'CREATE MYSQL TABLE
	
	result = MysqlTryExecNonQuery(Sql.ToString)
	
	Return result
	
End Sub

Sub SqliteToMysqlData(sqlitetable As String, mysqltable As String, wherefilter As String, orderby As String, deleterows As Boolean) As Boolean 'Ignore
	
	Private result As Boolean = False
	
	Private sqls As StringBuilder
	
	Private sqlm As StringBuilder
	
	If mysql.IsInitialized = False Then
	
		Return result
	
	End If
	
	If deleterows = True Then
		
		MysqlZap(mysqltable)
		
	End If

	sqls.Initialize

	sqlm.Initialize

	Private rs As ResultSet

	sqls.append($"SELECT * FROM "${sqlitetable}""$)
	
	If wherefilter.Length > 0 Then
	
		sqls.Append(" WHERE " & wherefilter)
		
	End If

	If orderby.Length > 0 Then
	
		sqls.append(" ORDER BY " & orderby)
		
	End If

	rs = sqlite.ExecQuery(sqls.ToString)
	
	sqlm.Append("REPLACE INTO `" & mysqltable & "`(")
	
	For icol = 0 To rs.ColumnCount - 1
		
		If icol > 0 Then sqlm.Append(",")
			
		sqlm.Append(rs.GetColumnName(icol))
			
	Next
		
	sqlm.Append(")")
	
	sqlm.Append(" VALUES(")
	
	For icol = 0 To rs.ColumnCount - 1
		
		If icol > 0 Then sqlm.Append(",")
			
		sqlm.Append("?")
			
	Next
		
	sqlm.Append(")")
	
	Private params(rs.ColumnCount) As String
	
	mysql.BeginTransaction
	
	Try
	
		For i = 0 To rs.RowCount - 1
	
			rs.Position = i

			For x = 0 To rs.ColumnCount - 1
			
				params(x) = rs.GetString2(x)
			
			Next
		
			mysql.ExecNonQuery2(sqlm.ToString, params)
		
		Next
	
		mysql.TransactionSuccessful
		
		result = True

	Catch

		LogException
		
		mysql.RollBack 'no changes will be made
		
		result = False

	End Try
	
	rs.Close
	
	If enablesqllog Then Log(sqls.ToString)

	If enablesqllog Then Log(sqlm.ToString)
	
	Return result
	
End Sub

Sub LogException() 'ignore
	
	If LastException.IsInitialized Then
				
		Log(LastException.Message)
				
	End If
	
End Sub

Sub MysqlTryExecNonQuery(sql As String) As Boolean 'Ignore
	
	Private result As Boolean

	If enablesqllog Then Log(sql)

	Try

		mysql.ExecNonQuery(sql)
		
		result = True
		
	Catch
		
		result = False
		
	End Try
	
	If result = False Then LogException
		
	Return result
	
End Sub

Sub MysqlOpen As ResumableSub 'Ignore
	
	If mysql.IsInitialized Then
		
		Return True
	
	End If

	Private mysqldriver As String = "com.mysql.jdbc.Driver"
	Private mysqlurl As String
	Private mysql_server As String = kvs.Get("mysql_server")
	Private mysql_port As String = kvs.GetDefault("mysql_port","3306")
	Private mysql_user As String = kvs.Get("mysql_user")
	Private mysql_pass As String = kvs.Get("mysql_pass")
	Private mysql_database As String = kvs.Get("mysql_database")
	
	mysqlurl = "jdbc:mysql://" & mysql_server & ":" & mysql_port & "/" & mysql_database & "?autoReconnect=true&useSSL=false"
	
	If enablesqllog Then Log(mysqlurl)
	
	mysql.InitializeAsync("mysql", mysqldriver, mysqlurl, mysql_user, mysql_pass)
	
	wait for mysql_ready(result As Boolean)

	If result = False Then LogException
		
	Return result
	
End Sub

Sub MysqlClose 'Ignore

	If enablesqllog Then Log("mysql.close")
	
	mysql.Close

End Sub

Sub MysqlZap(table As String)
	
	Private sql As String = $"TRUNCATE TABLE `${table}`"$
	
	MysqlTryExecNonQuery(sql)
	
End Sub

Sub MysqlDropTable(table As String)
	
	Private sql As String = $"DROP TABLE IF EXISTS `${table}`"$
	
	MysqlTryExecNonQuery(sql)

End Sub

Sub MysqlToSqlite(mysqltable As String, sqlitetable As String, DropBefore As Boolean) As Boolean 'Ignore
	
	Private result As Boolean
	
	Private sqls As StringBuilder
	
	Private sqlm As String
	
	Private rs As JdbcResultSet

	'GET MYSQL TABLE STRUCTURE
	
	sqlm = $"SHOW COLUMNS FROM `${mysqltable}`"$
		
	Try
			
		rs = mysql.ExecQuery(sqlm)
			
		result = True

	Catch

		result = False

	End Try
		
	If result = False Then
		
		LogException

		Return result
		
	End If

	If DropBefore = True Then
		
		SqliteDropTable(sqlitetable)
		
	End If

	'BUILD SQLITE CREATE TABLE COMMAND
	
	Private primarykey As String = ""
	
	sqls.Initialize
		
	sqls.Append($"CREATE TABLE IF NOT EXISTS "${sqlitetable}" ("$ & CRLF)

	Private firstrow As Boolean = True
	
	Do While rs.NextRow
				
		If firstrow = False Then sqls.Append(", " & CRLF) Else firstrow = False
		
		sqls.Append($""${rs.Getstring("field")}""$)
		
		sqls.Append(" " & rs.Getstring("type").Replace(" unsigned", ""))
				
		If rs.Getstring("key") = "PRI" Then
					
			primarykey = $"PRIMARY KEY ("${rs.Getstring("field")}")"$
					
		End If
		
		If rs.Getstring("null") = "NO" Then
			
			sqls.Append(" NOT NULL")
		
		End If
		
		If rs.Getstring("default") <> Null Then
			
			sqls.Append(" DEFAULT '" & rs.Getstring("default") & "'")
		
		End If
		
		If rs.Getstring("type").Contains(" unsigned") Then
			
			sqls.Append(" CHECK(" & rs.Getstring("field") & " >= 0)")
			
		End If
					
	Loop
	
	If primarykey.Length > 0 Then
		
		sqls.Append("," & CRLF & primarykey & ")")
		
	Else
		
		sqls.Append(")")
		
	End If
	
	rs.Close 'CLOSE MYSQL RECORDSET
			
	result = SqliteTryExecNonQuery(sqls.ToString)
	
	Return result
		
End Sub

Sub MysqlToSqliteData(mysqltable As String, sqlitetable As String, wherefilter As String, orderby As String, deleterows As Boolean) As Boolean 'Ignore
	
	'SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'
	
	Private sqlm As StringBuilder
	
	Private sqls As StringBuilder
	
	Private result As Boolean = False
	
	Private rs As JdbcResultSet
	
	If deleterows = True Then
		
		SqliteZap(sqlitetable)
		
	End If
	
	sqlm.Initialize
	
	sqls.Initialize
	
	sqlm.Append($"SELECT * FROM `${mysqltable}`"$)

	If wherefilter.Length > 0 Then
	
		sqlm.Append(" WHERE " & wherefilter)
		
	End If

	If orderby.Length > 0 Then
	
		sqlm.Append(" ORDER BY " & orderby)
		
	End If

	rs = mysql.ExecQuery(sqlm.ToString)
			
	Private params(rs.ColumnCount) As String
		
	sqls.Append($"INSERT OR REPLACE INTO "${sqlitetable}"("$)
	
	For icol = 0 To rs.ColumnCount - 1
		
		If icol > 0 Then sqlm.Append(",")
			
		sqls.Append(rs.GetColumnName(icol))
			
	Next

	sqls.Append(")")

	sqls.Append($" VALUES("$)

	For icol = 0 To rs.ColumnCount - 1
		
		If icol > 1 Then sqls.Append(",")
			
		sqls.Append("?")
			
	Next
		
	sqls.Append(")")

	sqlite.BeginTransaction
	
	Try
		
		Do While rs.NextRow
		
			For icol = 0 To rs.ColumnCount - 1
			
				params(icol) = rs.GetString2(icol)
				
			Next
		
			sqlite.ExecNonQuery2(sqls.ToString, params)
			
		Loop
		
		sqlite.TransactionSuccessful
		
		result = True
	
	Catch

		result = False
			
		LogException
		
	End Try

	sqlite.EndTransaction
	
	rs.Close 'CLOSE MYSQL RECORDSET
	
	If enablesqllog Then Log(sqls.ToString)

	If enablesqllog Then Log(sqlm.ToString)

	Return result
	
End Sub