Reducing session history size

[BarryG]

Hi, my session history retention was set to 365 days and the "History.db" file is 757 MB in size - yikes! So I changed the retention to 90 days but the history file size didn't change? I expected it would cull the old stuff? How can I remove anything older than 90 days in it?

[jassing]

Old post, but I'm working on a more robust cleaner (removing invalid/corrupt records, mismatched records, etc)

In case anyone else wants to, here's a simple way to remove older records:

Code: Select all

UseSQLiteDatabase()
Declare removeOlder( cHistoryDB.s, days=-1 )

removeOlder( "c:\temp\dump\d\backup\history.db", 10 )

End

#defaultAge = 90
Procedure removeOlder( cHistoryDB.s, days=-1 )
  Protected ra, db
  If days=-1
    days=#defaultAge
    If FileSize( GetPathPart(cHistoryDB)+"purebasic.prefs" ) > 0 And OpenPreferences( GetPathPart(cHistoryDB)+"purebasic.prefs" )
      If PreferenceGroup("EditHistory")
        days = ReadPreferenceInteger( "MaxDays", #defaultAge )
      EndIf
      ClosePreferences()
    EndIf
  EndIf
  
  Debug "Removing records older than "+Str(days)+" days"
  Debug "start " + FormatNumber( FileSize( cHistoryDB ), 0 ) + " bytes"
  
  db = OpenDatabase( #PB_Any, cHistoryDB, "","" )
  If IsDatabase( db )
    DatabaseUpdate( db, "BEGIN"  )
    
    DatabaseUpdate( db, "DELETE FROM event WHERE time < " + Str(AddDate(Date(), #PB_Date_Day, days * -1)) ) : ra + AffectedDatabaseRows( db )
    DatabaseUpdate( db, "DELETE FROM session WHERE session_id NOT IN (SELECT session_id FROM event)"      ) : ra + AffectedDatabaseRows( db )
    
    Debug "Removed "+FormatNumber( ra, 0 )+" records" 
    
    DatabaseUpdate( db, "COMMIT" )
    DatabaseUpdate( db, "VACUUM" )
    
    CloseDatabase(  db )
    Debug "  end " + FormatNumber( FileSize( cHistoryDB ), 0 ) + " bytes"
  EndIf
EndProcedure

[boddhi]

Old post, but I'm working on a more robust cleaner (removing invalid/corrupt records, mismatched records, etc)
In case anyone else wants to, here's a simple way to remove older records:

I haven't taken the time to test your code yet but, I'll do, I promise it
Just a suggestion, I'd have seen a test like this to avoid the systematic vacuum process which can be more or less long and perhaps useless if no data has been deleted (This is what I do with all my code).

Code: Select all

    ...
    If ra
      DatabaseUpdate( db, "VACUUM" )
    EndIf

[BarryG]

Hi Jassing,

Just tried it with an age of 180 days and got this Debug Output:

Code: Select all

Removing records older than 365 days
start 794,058,752 bytes
Removed 0 records
  end 152,035,328 bytes

The History.db file was 757 MB and is now 144 MB, but I'm really worried by the Debug Output showing 365 days and 0 records removed. I've put back my original History.db file for now (I backed it up before running your code).

[jassing]

Over time, database files can grow. Leaving empty space.

Do this, before the 'vacuum'

Code: Select all

If DatabaseQuery(db,"pragma freelist_count")
  If NextDatabaseRow(db)
    Debug "Free count "+FormatNumber(GetDatabaseLong(db,0),0)
  EndIf
  FinishDatabaseQuery(db)
EndIf

I'm betting it's a rather large #.
The reason the database remains large after records deleted (probably by purebasic's "session history" routine) is that it 'saves the space' to be re-used.

So you can have 0 records removed, but still see a massive reduction in size just due to the nature of an sqlite database ...

[BarryG]

The database hasn't remained large (the file is now only 144 MB compared to 757 MB), but why does the debug output say "Removing records older than 365 days" when I told it to only remove older than 180 days? Has it kept history that I didn't want? I know nothing about SQLite.

[jassing]

The database hasn't remained large (the file is now only 144 MB compared to 757 MB), but why does the debug output say "Removing records older than 365 days" when I told it to only remove older than 180 days? Has it kept history that I didn't want? I know nothing about SQLite.

How did you specify 180?
if it's not specified, it reads it from the preference, otherwise, uses #defaultAge

Code: Select all

 
  If days = -1
    days=#defaultAge
    If FileSize( GetPathPart(cHistoryDB)+"purebasic.prefs" ) > 0 And OpenPreferences( GetPathPart(cHistoryDB)+"purebasic.prefs" )
      If PreferenceGroup("EditHistory")
        days = ReadPreferenceInteger( "MaxDays", #defaultAge )
      EndIf
      ClosePreferences()
    EndIf
  EndIf

[BarryG]

How did you specify 180?

Like this:

Code: Select all

removeOlder( "C:\Users\{User}\AppData\Roaming\PureBasic\History.db", 180)

[jassing]

Curious. I guess do a search & replace on "days" to "180" .

Here is a simpled example. Note, this example has saving any changes commented out

Code: Select all

EnableExplicit
UseSQLiteDatabase()

#file$ = "c:\temp\dump\d\backup\history.db"
#daysOld=180

Debug "Removing records older than "+Str( #daysOld )+" days"
Debug "start " + FormatNumber( FileSize( #file$ ), 0 ) + " bytes"

Define db, ra

OpenDatabase( 0, #file$, "","" )
If IsDatabase( 0 )
  DatabaseUpdate( 0, "BEGIN"   )
  
  DatabaseUpdate( 0, "DELETE FROM event WHERE time < " + Str(AddDate(Date(), #PB_Date_Day, #daysOld * -1)) ) 
  Debug "File Data removed: " + AffectedDatabaseRows( db )
  DatabaseUpdate( 0, "DELETE FROM session WHERE session_id NOT IN (SELECT session_id FROM event)" ) 
  Debug "Session data removed: " + AffectedDatabaseRows( db )
  
  DatabaseUpdate( 0, "ROLLBACK")
  ;DatabaseUpdate( 0, "COMMIT" )
  ;DatabaseUpdate( 0, "VACUUM" )
  
  CloseDatabase(  0 )
EndIf