PureBasic Forums - English

Posted: **Wed Dec 28, 2016 5:46 pm**

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

Posted: **Wed Dec 28, 2016 6:08 pm**

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?

Posted: **Wed Dec 28, 2016 6:10 pm**

Did you try with the IDE session history disabled?

Posted: **Wed Dec 28, 2016 6:28 pm**

skywalk wrote: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:

Posted: **Wed Dec 28, 2016 6:48 pm**

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

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

Posted: **Wed Dec 28, 2016 7:28 pm**

the.weavster wrote: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

PureBasic Forums - English

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

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

Re: SQLite + many UPDATE : Purebasic process stand still

Re: SQLite + many UPDATE : Purebasic process stand still

Re: SQLite + many UPDATE : Purebasic process stand still

Re: SQLite + many UPDATE : Purebasic process stand still

Re: SQLite + many UPDATE : Purebasic process stand still