Android Code Snippet Clean SQLite database, if it grows much

Long working apps have DB that grows and grows. It must be controlled, when records qty is very big, and most old data should be deleted:

Sub Clean_SQL(tables() As String) As Boolean
    'Starter.SQL is already open
    Dim TotalRecordsLimit As Int = 20000    'set yourself according to the project scale, speed of DB grow and needed time period for data storage. i guess, it should be > 10...100K
    Dim MinQty As Int = 1000
    Dim IndexFieldName As String = "id"    'each table must have the primary index with this name
    If tables = Null Then
        Dim cur As Cursor = Starter.SQL.ExecQuery("SELECT name FROM sqlite_master WHERE Type='table' ORDER BY name")
        If cur.RowCount = 0 Then
            cur.Close
            Return False
        End If
        Dim TotalRecords As Int
        For i = 0 To cur.RowCount - 1
            cur.Position = i
            Dim tablename As String = cur.GetString2(0)
            Dim count As Int = Starter.SQL.ExecQuerySingleResult("SELECT count(*) FROM '" & tablename & "'")
            TotalRecords = TotalRecords + count    'total records in the db
        Next
        If TotalRecords < TotalRecordsLimit Then
            cur.Close
            Return False    'small DB yet, no need to clean
        End If
        For i = 0 To cur.RowCount - 1
            cur.Position = i
            Dim tablename As String = cur.GetString2(0)
            Dim count As Int = Starter.SQL.ExecQuerySingleResult("SELECT count(*) FROM '" & tablename & "'")
            Dim TableLimit As Int = count - (TotalRecordsLimit / TotalRecords * count)    'qty to be deleted from this table
            If TableLimit < MinQty Then Continue    'do not clean table fully
            Starter.SQL.BeginTransaction
            Try
                Dim q As String = "DELETE FROM " & tablename & " WHERE " & IndexFieldName & " in (SELECT id FROM " & tablename & " ORDER BY " & IndexFieldName & " ASC LIMIT " & TableLimit & ")"
                'Log(q)
                Starter.SQL.ExecNonQuery(q)
                Starter.SQL.TransactionSuccessful
            Catch
                Log("Clean_SQL." & tablename & ".error = " & LastException.Message)
            End Try
            Starter.SQL.EndTransaction
        Next
        cur.Close
    Else
        Dim TotalRecordsLimit As Int = 2000    'set yourself according to the project scale, speed of DB grow and needed time period for data storage. i guess, it should be > 10...100K
        Dim TotalRecords As Int
        For i = 0 To tables.Length - 1
            Dim tablename As String = tables(i)
            Dim count As Int = Starter.SQL.ExecQuerySingleResult("SELECT count(*) FROM '" & tablename & "'")
            TotalRecords = TotalRecords + count    'total records in the db
        Next
        TotalRecords = TotalRecords + count    'total records in the db
        If TotalRecords < TotalRecordsLimit Then
            Return False    'small DB yet, no need to clean
        End If
        For i = 0 To tables.Length - 1
            Dim tablename As String = tables(i)
            Dim count As Int = Starter.SQL.ExecQuerySingleResult("SELECT count(*) FROM '" & tablename & "'")
            Dim TableLimit As Int = count - (TotalRecordsLimit / TotalRecords * count)    'qty to be deleted from this table
            If TableLimit < MinQty Then Continue    'do not clean table fully
            Starter.SQL.BeginTransaction
            Try
                Dim q As String = "DELETE FROM " & tablename & " WHERE " & IndexFieldName & " in (SELECT id FROM " & tablename & " ORDER BY " & IndexFieldName & " ASC LIMIT " & TableLimit & ")"
                'Log(q)
                Starter.SQL.ExecNonQuery(q)
                Starter.SQL.TransactionSuccessful
            Catch
                Log("Clean_SQL." & tablename & ".error = " & LastException.Message)
            End Try
            Starter.SQL.EndTransaction
        Next
    End If
    Return True
End Sub

 

[AnandGupta]

when records qty is very big, and most old data should be deleted

Sorry, you posted a solution or asking for the solution ?

Regards,

Anand

 

[peacemaker]

Code snippet subforum is for solutions, Anand

 

[Robert Valentino]

Along the same lines, I have this code in my database routines

My database are local and I have a few tables that I add and delete a lot of records.
If the table every becomes empty (no records) I reset the sequence set (Auto Increment)

#Region ResetTable
Private Sub ResetTable(TableName As String)
   
            Dim RecordCount As Long = cDatabase.SQLDatabase.ExecQuerySingleResult("SELECT Count(*) FROM " &TableName)

           If RecordCount = 0 Then
              cDatabase.SQLDatabase.BeginTransaction
                 
              Try
                mSQLStatement = "UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME=""" &TableName &""""
                   
                    cDatabase.SQLDatabase.ExecNonQuery(mSQLStatement)
                           
                    cDatabase.SQLDatabase.TransactionSuccessful
                    cDatabase.SQLDatabase.EndTransaction                                               
              Catch
                Log(LastException)
                  cDatabase.SQLDatabase.EndTransaction                                                                   
              End Try                                                 
           End If             
End Sub
#end Region

 

[peacemaker]

If the table every becomes empty (no records)

Thanks for this sub - i needed it recently.
But this topic is for that how to make tables smaller, but non-empty.

 

[Robert Valentino]

Oops sorry