[Done]-SQLite + many UPDATE : Purebasic process stand still

[flaith]

Hi everyone,

I wanted to try a code to get all of my files in my current directory and in the sub-directories
And I want to save them in a sqlite database

My issue is in my directory there is more than 150 files and, each time, I need to kill purebasic.exe process
If there is around 20 files, it's ok (but slow to insert each time)

I see my hard drive at full charge during 30 sec and then stop, and no debug message at all
I checked the database, everything's inside, so he made the inserts as expected

But if I use the memory database, it works perfectly

PS: Deactivated anti-virus and firewall already, still the same issue

Code: Select all

#DATABASE = 0
#DIRECTORY = 1

UseSQLiteDatabase()

Structure CS_LISTFILE
    path.s
    file.s
EndStructure

Global.CS_LISTFILE NewList listFile()

Directory$ = GetCurrentDirectory()
Debug "Current Directory: " + Directory$
path$ = Directory$

If ExamineDirectory(#DIRECTORY, Directory$, "*.*")  
    While NextDirectoryEntry(#DIRECTORY)
        If DirectoryEntryType(#DIRECTORY) = #PB_DirectoryEntry_File
            file$ = DirectoryEntryName(#DIRECTORY)
        Else
            currDir$ = DirectoryEntryName(#DIRECTORY)
            If currDir$ <> "." And currDir$ <> ".."
                path$ = Directory$ + DirectoryEntryName(#DIRECTORY)
            EndIf
        EndIf
        
        If file$ <> ""
            AddElement(listFile())
                listFile()\path = path$
                listFile()\file = file$
        EndIf
        
    Wend
    FinishDirectory(#DIRECTORY)
EndIf

Filename$ = "IdxFiles.db"

If CreateFile(#DATABASE, Filename$)
    Debug Filename$ + " created"
    CloseFile(#DATABASE)
EndIf

CREATE_TABLE$ = "CREATE TABLE file (id INTEGER PRIMARY KEY AUTOINCREMENT, path TEXT, filename TEXT)"
; If OpenDatabase(#DATABASE, ":memory:", "", "")
If OpenDatabase(#DATABASE, Filename$, "", "")
    If DatabaseUpdate(#DATABASE, CREATE_TABLE$)
        Debug "Table created"
    EndIf
    
    ResetList(listFile())
    ForEach listFile()
;         DatabaseUpdate(#DATABASE, "INSERT INTO file(id, path, filename) VALUES (NULL,NULL,'')")
;         insert$ = "UPDATE file SET path='" + listFile()\path + "', filename='" + listFile()\file + "'"
        insert$ = "INSERT INTO file(path,filename) VALUES ('" + listFile()\path + "','" + listFile()\file + "')"

        Debug insert$

        If Not DatabaseUpdate(#DATABASE, insert$)
            Debug DatabaseError()
        EndIf
    Next
    
    CloseDatabase(#DATABASE)
    Debug "done"
EndIf

Anything's wrong in the code?
Thanks for your help

[flaith]

Another try, without DEBUGGER
I changed to a console program, launch it, worked, BUT purebasic.exe froze again more than a minute
Using sqlite with purebasic opened is an issue?

[skywalk]

Did you try with the IDE session history disabled?

[flaith]

Did you try with the IDE session history disabled?

Thanks for your answer
I just tried no change, even worse
Before launching the console program:

During:

[the.weavster]

Try wrapping it inside a transaction started with 'begin immediate'

I suspect it's to do with implicit transactions and the associated file locking.

[flaith]

Try wrapping it inside a transaction started with 'begin immediate'

I suspect it's to do with implicit transactions and the associated file locking.

Great
Thanks for your reply, I've added "BEGIN" before and "COMMIT" after the updates, and it worked really well without frozen behavior


Updated code:

Code: Select all

#DATABASE = 0
#DIRECTORY = 1

UseSQLiteDatabase()

Structure CS_LISTFILE
    path.s
    file.s
EndStructure

Global.CS_LISTFILE NewList listFile()

Directory$ = GetCurrentDirectory()
Debug "Current Directory: " + Directory$
path$ = Directory$

If ExamineDirectory(#DIRECTORY, Directory$, "*.*")  
    While NextDirectoryEntry(#DIRECTORY)
        If DirectoryEntryType(#DIRECTORY) = #PB_DirectoryEntry_File
            file$ = DirectoryEntryName(#DIRECTORY)
        Else
            currDir$ = DirectoryEntryName(#DIRECTORY)
            If currDir$ <> "." And currDir$ <> ".."
                path$ = Directory$ + DirectoryEntryName(#DIRECTORY)
            EndIf
        EndIf
        
        If file$ <> ""
            AddElement(listFile())
                listFile()\path = path$
                listFile()\file = file$
        EndIf
        
    Wend
    FinishDirectory(#DIRECTORY)
EndIf

Filename$ = "IdxFiles.db"

If CreateFile(#DATABASE, Filename$)
    Debug Filename$ + " created"
    CloseFile(#DATABASE)
EndIf

CREATE_TABLE$ = "CREATE TABLE file (id INTEGER PRIMARY KEY AUTOINCREMENT, path TEXT, filename TEXT)"

If OpenDatabase(#DATABASE, Filename$, "", "")
    If DatabaseUpdate(#DATABASE, CREATE_TABLE$)
        Debug "Table created"
    EndIf

    DatabaseUpdate(#DATABASE, "BEGIN")

    ResetList(listFile())
    ForEach listFile()
        insert$ = "INSERT INTO file(path,filename) VALUES ('" + listFile()\path + "','" + listFile()\file + "')"

        Debug insert$

        If Not DatabaseUpdate(#DATABASE, insert$)
            Debug DatabaseError()
        EndIf
    Next
    
    DatabaseUpdate(#DATABASE, "COMMIT")
    
    CloseDatabase(#DATABASE)
    Debug "done"
EndIf