Some basic questions by SQLite Newbie

[Mbee]

I plan to develop a special-purpose file- and folder-name search tool. My current plan calls for obtaining and then storing all the file and folder names present on any of several entire large hard drives, each of which will serve as a fairly typical baseline to search and to update to reflect changes such as file/folder creation, deletion, and rename. I don't care about other changes, and to monitor those changes I plan to use Yashied's RDC v1.1 UDF.

The first problem is that no single array can contain enough entries for a large drive, since the limit for any array is 2^24 entries. So I'll have to create a simple data structure and access funcs to work with a single object that represents multiple arrays -- no big deal (I expect). The second issue is that I certainly don't want all the arrays for all the drives in RAM at the same time, so my guess is that the best approach would be to use one or more SQLite DBs. Does anyone disagree or have other suggestions, please?

In the days when buggy whips were still in everyday use, I developed a few relational databases, but of course they were considerably simpler than anything commonly in use today. And I've never used SQLite or any other modern alternative, so here are a few more basic questions...

(1): From what I can tell, I can create and populate an enormous SQLite "Table" from, say, all the arrays that represent a particular drive and all subfolders, even though this info can't be stored in a single AutoIt array. Correct?

(2) Are such tables easy and fast to search for partial matching strings, either with simple wildcards or with reg exps?  If not, what structures should I use instead or along with to perform such searches?

(3) I'm virtually certain that I can just make the minimal changes to a DB to reflect a few changes without re-creating the DB or re-writing it to disk. But in case I'm wrong, please let me know.

That's all the beginning questions I have at the moment. Thanks for sharing your wisdom!

 

Edited September 9, 2020 by Mbee

[Zedna]

Working with SQLite in AutoIt is easy.

But in this case consider using plain text file as used in DiskDir plugin in Total Commander

https://www.ghisler.com/plugins.htm --> DiskDir

Quote

Creates a list file with all selected files and directories, including subdirs (e.g. as an index for CD-ROMs). It is called like a packer (Files->Pack)! Source available.

 

Here is sample of content of such LST file created by this DiskDir plugin - c:\Program Files (x86)\AutoIt3\Aut2Exe\:

Quote

c:\Program Files (x86)\AutoIt3\
Aut2Exe\    0    2019.1.18    18:26.4
Aut2exe.exe    280064    2008.6.12    10:54.18
Aut2exeA.exe    267264    2008.6.12    10:54.24
Aut2exe_x64.exe    350720    2008.6.12    10:54.30
AutoItASC.bin    509952    2008.6.12    10:50.52
AutoItSC.bin    526336    2008.6.12    10:51.4
AutoItSC_x64.bin    737280    2008.6.12    10:50.26
upx.exe    266240    2008.5.9    0:46.48
Aut2Exe\Icons\    0    2011.7.18    19:7.48
AutoIt_HighColor.ico    25214    2008.5.9    0:47.4
AutoIt_Old1.ico    766    2008.5.9    0:47.4
AutoIt_Old2.ico    766    2008.5.9    0:47.4
AutoIt_Old3.ico    2238    2008.5.9    0:47.4
AutoIt_Old4.ico    4286    2008.5.9    0:47.4
SETUP01.ICO    766    2008.5.9    0:47.4
SETUP02.ICO    766    2008.5.9    0:47.4
SETUP03.ICO    766    2008.5.9    0:47.4
SETUP04.ICO    766    2008.5.9    0:47.4
SETUP05.ICO    766    2008.5.9    0:47.4
SETUP06.ICO    766    2008.5.9    0:47.4
SETUP07.ICO    766    2008.5.9    0:47.4
SETUP08.ICO    766    2008.5.9    0:47.4
SETUP09.ICO    766    2008.5.9    0:47.4
SETUP10.ICO    766    2008.5.9    0:47.4
SETUP11.ICO    766    2008.5.9    0:47.4
SETUP12.ICO    766    2008.5.9    0:47.4

 

Edited September 9, 2020 by Zedna

[jchd]

If you go the SQLite route, no problem. SQLite DBs can easily reach dozens of Tb, so you'll never hit a hard limit in your use case.

Create at least two tables: one for each directory and one for files with a reference to its directory. Depending on your needs you may as well want to split the directory path into drive (or server) and folder. Storing file extensions and/or other attributes in separate column(s) may also help, still depending on your query/update requirements. SQLite supports partial indices natively as well as indices on expressions.

I can provide SQLite loadable libraries to search Unicode text fields in uncommon ways (e.g. unaccented, fuzzy search, and much more) as well as support for PCRE regexps (same as in AutoIt).

As usual I recommend building a workbench DB using a good SQLite DB manager (I warmly recommend SQLite Expert). Create tables indices, triggers, queries with more or less real-world data, fine tune the schema until everything works nicely. Only then start coding want you need in AutoIt.

[Mbee]

Wow, thanks a bunch, @jchd !  That's extremely helpful. Regarding the loadable libraries you write of, I certainly don't want to impose on you by asking for them if you haven't coded them already, since it may well be that existing SQL search APIs will be good enough. But perhaps a list of them, their general goal, and perhaps a parameter list if they're ready would be most welcome.

Thanks also for your recommendation of SQLite Expert -- I hate shopping around for such tools because it's so difficult to identify the best one to use, especially for a newb like myself.. So you've saved me a great deal of time and effort! Using it, I've already created a first draft DB that I can now start coding some test scripts to work on.

Please accept my plentiful gratitude!

 

 

[jchd]

Fine.

Here's a first attempt at a useable schema : EDIT: don't trust that, typed too fast without thinking enough!
 

CREATE TABLE "Dirs" (
  "ID" INTEGER PRIMARY KEY, 
  "Drive" CHAR NOT NULL, 
  "Dir" CHAR NOT NULL) WITHOUT ROWID;

CREATE TABLE "Files" (
  "ID" INTEGER PRIMARY KEY, 
  "DirId" INTEGER NOT NULL CONSTRAINT "fkFileDir" REFERENCES "Dirs"("ID") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, 
  "File" CHAR NOT NULL, 
  "Ext" CHAR NOT NULL DEFAULT '') WITHOUT ROWID;

CREATE INDEX "ixFileExt" ON "Files" ("File", "Ext");
CREATE INDEX "ixExt" ON "Files" ("Ext");

Note the foreign key from Files to Dirs.

I recommend using the x86 version of Expert as the extension below are readily pre-compiled as 32-bit DLLs. The C source is included for your convenience.

unifuzz.zip

To load an SQLite extension from AutoIt use this:

You can register auto-loadable extension within Expert. EDIT: this feature is only available in the paid version, sorry for misleading information.

EDIT: wrong schema, see post below.

Edited September 9, 2020 by jchd

[Mbee]

Again, thanks for the incredibly valuable assistance! Especially your suggested starting schema.

I had just been reading about Foreign constraints, because someone on Stack Overflow suggested using them to store arrays. But that doesn't seem right because, as I understand it (and of course I could easily be wrong), their purpose is to ensure that no "missing links" (or non-reflective links) exist among the keyed relationships. I don't see what that has to do with arrays per se.

Which leaves me with the question of how to store text arrays, 1D or 2D. My probably naive idea is to end up with two or three tables...

(1): FileObjectTable, with 6 cols:

1.  A unique ID for each specific file on a given drive (I'm not going to store the drive letter because I'll have a separate DB for each drive)
2. A unique ID that identifies the specific folder that contains the file; an index into the Folder Table
3. The name of the folder specified by #2 (but I may just get this from the Folder Table)
4. The full path name of that folder (possibly also from Folder Table)
5. The filename (no extension)
6. The file extension

(2) FolderTable, with 4 cols:

1. The Unique ID value equal to that in the the FileObjectTable, Column 2
2. The Folder Name (no path)
3. The Full Path to the folder
4. ?? Some way of storing or pointing to a table (FolderContentsTable ?) containing an array listing all the file objects (i.e., files and subfolders) in this folder ??

(3) Possibly a FolderContentsTable with 2 (or n) cols:

1. The number of file objects in this table
2. Either a list of file objects stored as BLOB_TEXT (?) Or what??

So how do you recommend storing the arbitrary-length list (or array) of file objects stored in a specific folder, as in Table 3, please?

Also, because I couldn't help glossing over this, I don't know how to either specify or generate the Unique IDs I need. Can I just use the COLID as long as I specify that it must be unique?

 

Please take your time in replying, kind sir!  I hate imposing on people, especially generous people like you who bear gifts!

Thanks

Edited September 9, 2020 by Mbee
Typo

[jchd]

expandcollapse popup

; Here's a quick example use:

; run a full list from a CMD box
; we should have more or less the same content without disclosing personal details

; can also be done with AutoIt functions, recursively enumerating subfolders and files
; C:\Program Files (x86)\AutoIt3>dir /b /a:-d /s > C:\Users\jc\Documents\AutoMAT\tmp\au3files.txt

#include <File.au3>
#include <SQLite.au3>

Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll

; Init sqlite
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
OnAutoItExitRegister(_SQLite_Shutdown)

ConsoleWrite("SQlite version " & _SQLite_LibVersion() & @LF & @LF)  ; optional!

Local $hDB = _SQLite_Open("AllFiles.sq3")
If @error Then Exit
OnAutoItExitRegister(SQ3Close)

; as requested, drive isn't stored
_SQLite_Exec($hDB, "CREATE TABLE if not exists Dirs (" & _
    "ID INTEGER PRIMARY KEY, " & _
    "Dir CHAR NOT NULL UNIQUE)")

_SQLite_Exec($hDB, "CREATE TABLE if not exists Files (" & _
    "ID INTEGER PRIMARY KEY, " & _
    "DirId INTEGER NOT NULL CONSTRAINT fkFileDir REFERENCES Dirs (ID) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, " & _
    "File CHAR NOT NULL, " & _
    "Ext CHAR NOT NULL DEFAULT '')")

_SQLite_Exec($hDB, "CREATE INDEX if not exists ixFileExt ON Files (File, Ext);")
_SQLite_Exec($hDB, "CREATE INDEX if not exists ixExt ON Files (Ext);")

; can also be done with AutoIt functions, recursively enumerating subfolders and files
Local $aFiles = FileReadToArray("C:\Users\jc\Documents\AutoMAT\tmp\au3files.txt")

Local $aRow, $iId, $sDrive, $sDir, $sFile, $sExt
; Make the whole insert block into a transaction to speed up things.
_SQLite_Exec($hDB, "begin")

For $v In $aFiles
    _PathSplit($v, $sDrive, $sDir, $sFile, $sExt)
    _SQLite_QuerySingleRow($hDB, "select id from dirs where dir = " & _SQLite_FastEscape($sDir), $aRow)
    If @error Then
        _SQLite_Exec($hDB, "insert into dirs (dir) values (" & _SQLite_FastEscape($sDir) & ")")
        $iId = _SQLite_LastInsertRowID($hDB)
    Else
        $iId = $aRow[0]
    EndIf
    If $sFile = '' Then ContinueLoop
    _SQLite_Exec($hDB, "insert into files (dirid, file, ext) values (" & $iId & ", " & _SQLite_FastEscape($sFile) & ", " & _SQLite_FastEscape($sExt) & ")")
Next

; commit
_SQLite_Exec($hDB, "end")

Func SQ3Close()
    _SQLite_Close($hDB)
EndFunc

; preferably use Expert to query the DB. Copy this query into a SQL tab and execute it
; select dir || file || ext "File" from dirs D join files F on d.id = f.dirid where dir not like '%beta%' and file like '%extended%' and ext = '.au3'

This is a basic schema.

SQL Foreign key reference work the opposite of pointers in conventional programming! An entry in the Dirs table holds a DirId (the unique identifier of the directory where the file resides).  This is pretty standard SQL and how most tables in large DBs relate to each other.

Put it otherwise, you don't store ID of every children to every parent, you store the ID of mother and father in each child entry.

Run the sample code (adjust paths if necessary) and use Expert to see what's stored where. Then use an SQL tab to launch the sample query in final comment of the code to see what you get.

You may want to create an extra table Params containing for instance: the drive for that DB, a schema version in case you need to upgrade to a new schema, the timestamp of last data retrieval or update, whatever information you find useful.

Don't store counts, use SQL to retrieve counts by using AutoIt or (simpler) another Expert SQL tab (I get 47 but I've some more than bare minimum):

_SQLite_QuerySingleRow($hDB, "select count(*) from files where ext = '.exe', $aRow)
count is then in $aRow[0]

A golden rule in SQL schema design: don't duplicate information, never, no, no. Create as many tables as required, and make relationships explicit (using foreign keys). Dirs table should contain information pertinent to directory entries, Files should contain information for files and hold a FK to the Dir where it lives.

Edited September 9, 2020 by jchd

[Mbee]

@jchd, you are a god! You essentially developed the core code I needed for me, and then some! I don't know how to thank you enough, oh kind and generous one🤩

I still need considerably more studying before I fully grasp foreign keys, but based on the number of google hits on that topic, I'm not alone. But I'm determined to learn as much as I can by myself.

This reply originally also held another question about a problem I was having, but after researching a bit more, I worked it out for myself.  However, I'm certain I'll have more questions. If I become a bother, please let me know!

Edited September 13, 2020 by Mbee
Solved problem & deleted the question

[jchd]

Nice to see you find SQLite a good basis for solving your problem.

Chime again as needed.

[JockoDundee]

Quote

SQL Foreign key reference work the opposite of pointers in conventional programming! An entry in the Dirs table holds a DirId (the unique identifier of the directory where the file resides).  This is pretty standard SQL and how most tables in large DBs relate to each other.

yes, but where is the foreign key relationship between dir and subdir?

are subdirs also stored in the Files table?

 

[jchd]

On 9/9/2020 at 11:07 PM, jchd said:

DirId INTEGER NOT NULL CONSTRAINT fkFileDir REFERENCES Dirs (ID) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED

This is the FK.
Every directory path has to be stored in full in Dirs. We could only store each _{dir individually alone with a FK to its parent in Dirs, but that would mean painful recursion into Dirs to retrieve the full pah of a file.}

[JockoDundee]

1 hour ago, jchd said:

Every directory path has to be stored in full in Dirs. We could only store each _{dir individually alone with a FK to its parent in Dirs, but that would mean painful recursion into Dirs to retrieve the full pah of a file.}

Fair enough.  
However, without such a constraint, what prevents directory “\abc\xyz” from being inserted into a table without a “\abc” row already existing?
 

[jchd]

You don't care. We're not re-building the directory tree here: we're enumerating files along with their hosting folder.
Depending on the criterion you use to select files (e.g. only .DLL) you not always have to store each folder by itself, but only those paths which contains desired files.

(IDK why some text got changed to index form)

[JockoDundee]

5 hours ago, jchd said:

We're not re-building the directory tree here: we're enumerating files along with their hosting folder.

Except that folders *are* files; they’re just lists of other files.  OP says his current plan calls for

On 9/8/2020 at 7:38 PM, Mbee said:

storing all the file and folder names present on any of several entire large hard drives, each of which will serve as a fairly typical baseline to search and to update to reflect changes such as file/folder creation, deletion, and rename.

which makes it sound, imho, like files and folders will be searched in much the same way. 

imho, and esp. since you say you are not rebuilding the directory tree, the schema may as well be a single table, with just a filetype attribute to distinguish files from directories.  This will vastly simplify the searching.  
imho, files and directories and more alike than not, and unless you are establishing transactional integrity over a schema, creating separate tables for directories and files just to search will be a PITA.

 

[jchd]

There can be no integrity issue with the sample schema I posted, provided FK are explicitely enabled.

One can as well merge the two tables, have a FK referencing a file's folder in same table. It's almost the same PITA BTW: you just replace "from files join dirs" by "from files join files". And no need to add a "folder" attribute: just make the FK NULL (and allow that in create statement).

It all depends on the precise requirements of the OP to fine-tune the schema.

 

[Mbee]

16 hours ago, jchd said:

Nice to see you find SQLite a good basis for solving your problem.

Chime again as needed.

Thanks again, kind sir.

I'm running into a problem inserting the files in the drive's root folder into the dirs table because the dir value cannot be null, and it doesn't seem to like a string with just a backslash, either. Both trigger the SQ3Close() exit handler, even though I explicitly test '@error' and the return codes of all the _SQLite_Exec() calls.

Allowing nulls seems like a very bad solution, so I was thinking of substituting the string "<root>" as the dir string value, since it is an invalid folder name. How would you recommend I resolve this?

While I'm here, I have another issue that I would appreciate your suggested resolutions:  I've added two columns in the Files table for the creation and modification dates as INTEGER values holding the date & time relative to the Unix Epoch. But one of the things I want to do with my tool is the ability to find files/folders with the latest of these two dates as the default, but it must also allow for searching specifically on the creation or modification date-times.

I definitely took to heart your emphasis on never duplicating data, otherwise I'd store all three (created, modified, latest of the two). What would you insert into the db to provide those date/time search features?

Thanks!!

 

 

[jchd]

On 9/14/2020 at 6:50 PM, Mbee said:

it doesn't seem to like a string with just a backslash

Untrue! Post code since a string can be anything (except NUL = 0x00).

On 9/14/2020 at 6:50 PM, Mbee said:

so I was thinking of substituting the string "<root>" as the dir string value, since it is an invalid folder name. How would you recommend I resolve this?

Setting dir = '\' works fine.

On 9/14/2020 at 6:50 PM, Mbee said:

I've added two columns in the Files table for the creation and modification dates as INTEGER values holding the date & time relative to the Unix Epoch. But one of the things I want to do with my tool is the ability to find files/folders with the latest of these two dates as the default, but it must also allow for searching specifically on the creation or modification date-times.

Set your columns to hold files' creation date, modify date and last access date. The later is the most recent. Be aware that SQLite dates are UTC by default.

EDIT: sorry for delay!

Edited September 25, 2020 by jchd