About Invoke-Locate, a PowerShell port of GNU findutils' locate and updatedb.

Tonight, I published Invoke-Locate.ps1 to ScriptCenter because I was tired of mounting my Windows drive to my Linux servers to quickly and reliably find files. Originally, it was going to be a small script, but then I decided to trick it out, add an installer and automatic Task Scheduling to run updatedb.

The installer gets you going pretty quickly. I've always appreciated how easy WordPress is to setup, and tried to follow suit.

install

That's it. Then you run your locate or updatedb.

search

The script is super fast (it had to be, because GNU locate is) and each query takes about 300ms.

measure

A task in created in Task Scheduler, which rebuilds the index every 6 hours, kinda like what cron does for locate. Originally, I thought I'd use SQL Server Compact Edition (CE) but was super disappointed when the queries took FOUR SECONDS, as opposed to SQLite which takes 0.3 seconds per query!

Where it all started

This is the heart of the code. I'll include both SQLite and the SQL Server CE editions, for those interested.

The original test using SQL Server CE
  1if ($PSScriptRoot) { $location = $PSScriptRoot } else { $location = (Get-Location).Path }
  2$dll = "$location\System.Data.SqlServerCe.dll"
  3
  4# look into SQLiteDataAdapter
  5if ($GetDll) {
  6    $wc = New-Object System.Net.WebClient
  7    $url = "https://netnerds.net/System.Data.SqlServerCe.dll"
  8    $wc.DownloadFile($url,$dll)
  9}
 10
 11if (!(Test-Path $dll)) { throw "System.Data.SqlServerCe.dll not found. You can isntall SQL Server CE, or automatically download the DLL using -GetDll" }
 12
 13# Build database
 14[void][Reflection.Assembly]::LoadFile($dll)
 15$database = "$location\locate.sdf"
 16$connectionString = "Data Source=$database;"
 17
 18if (!(Test-Path $database)) {
 19	# Create database
 20	$engine = New-Object System.Data.SqlServerCe.SqlCeEngine $connectionString
 21	$engine.CreateDatabase()
 22	$engine.Dispose()
 23	
 24	$connection = New-Object System.Data.SqlServerCe.SqlCeConnection $connectionString
 25	$connection.Open()
 26	$command = New-Object System.Data.SqlServerCe.SqlCeCommand 
 27	$command.Connection = $connection
 28
 29	# Create table, check if primary key is automatically unique
 30	$table = "CREATE TABLE [Files] ([Name] nvarchar(450) PRIMARY KEY)"
 31	$command.CommandText = $table
 32	[void]$command.ExecuteNonQuery()
 33
 34	$command.Dispose()
 35	$connection.Close()
 36	$connection.Dispose()
 37}      
 38
 39# Connect to database and do the work (updatedb)
 40$connection = New-Object System.Data.SqlServerCe.SqlCeConnection $connectionString
 41$connection.Open()
 42$command = New-Object System.Data.SqlServerCe.SqlCeCommand 
 43$command.Connection = $connection
 44
 45# delete table
 46$command.CommandText = "DROP TABLE [Files]"
 47[void]$command.ExecuteNonQuery()
 48$command.CommandText = "CREATE TABLE [Files] ([Name] nvarchar(450) PRIMARY KEY)"
 49[void]$command.ExecuteNonQuery()       
 50
 51$transaction = $connection.BeginTransaction()
 52	
 53Function Get-Filenames ($path) {
 54	Set-Variable -ErrorAction SilentlyContinue -Name files
 55	
 56	# Get Directories
 57	try
 58	{
 59	   $files = [IO.Directory]::GetFiles($path)
 60	   [System.IO.DirectoryInfo]$directoryInfo = New-Object IO.DirectoryInfo($path)
 61	   $folders = $directoryInfo.GetDirectories() | Where-Object {$_.Name -ne "`$Recycle.Bin" -and $folder -ne "System Volume Information" }
 62		  
 63	} catch { $folders = @()}
 64	
 65	foreach($filename in $files) 
 66		{
 67			$filename = $filename.replace('\\','\')
 68			$filename = $filename.replace("'","''")
 69			$command.CommandText = "insert into files values ('$filename')"
 70			[void]$command.ExecuteNonQuery()
 71		}
 72
 73	# Create list of non-indexed directories
 74	$exculde = @($env:APPDATA)
 75	$exclude += $env:LOCALAPPDATA
 76	$exclude += $env:ProgramData
 77	$exclude += $env:TMP
 78	$exclude += $env:TEMP
 79	
 80	try {
 81		foreach($folder in $folders)
 82		{ 
 83			if ($exclude -notcontains "$path$folder") { Get-Filenames("$path\$folder")}
 84		}
 85	} catch {}
 86	Remove-Variable -ErrorAction SilentlyContinue -Name files 
 87}
 88
 89Measure-command { Get-Filenames("C:\") }
 90$transaction.Commit()
 91
 92$command.Dispose()
 93$connection.Close()
 94$connection.Dispose()
 95
 96# Perform the query
 97$connection = New-Object System.Data.SqlServerCe.SqlCeConnection $connectionString
 98$connection.Open()
 99$command = New-Object System.Data.SqlServerCe.SqlCeCommand 
100$command.Connection = $connection
101
102Measure-Command {
103	$file = "SqlServerCe"
104	$sql = "select name from files where name like '%$file%'"
105	$command.CommandText = $sql
106	$datatable = New-Object System.Data.DataTable
107	$datatable.load($command.ExecuteReader())
108}
109$command.Dispose()
110$connection.Close()
111$connection.Dispose()

It's likely that the slower results is because of the fact that SQL Server CE was not intended to be super fast, it was intended to be compatible with larger SQL Server-based projects.

The better test using SQLite
  1$GetDll = $true
  2if ($PSScriptRoot) { $location = $PSScriptRoot } else { $location = (Get-Location).Path }
  3$dll = "$location\System.Data.SQLite.dll"
  4
  5if ($GetDll) {
  6	$wc = New-Object System.Net.WebClient
  7    $url = "https://netnerds.net/System.Data.SQLite.dll"
  8    $wc.DownloadFile($url,$dll)
  9}
 10
 11if (!(Test-Path $dll)) { throw "System.Data.SQLite.dll not found. You can isntall SQL Lite, or automatically download the DLL using -GetDll" }
 12
 13[void][Reflection.Assembly]::LoadFile($dll)
 14$database = "$location\locate.sqlite"
 15$connString = "Data Source=$database"
 16
 17# Create database
 18if (!(Test-Path $database)) {
 19	# Create database
 20	[void][System.Data.SQLite.SQLiteConnection]::CreateFile($database); 
 21	$connection = New-Object System.Data.SQLite.SQLiteConnection($connString)
 22	$connection.Open()
 23
 24	# Create table, check if primary key is automatically unique
 25	$table = "CREATE TABLE [Files] ([Name] nvarchar(450) PRIMARY KEY)"
 26	$command = $connection.CreateCommand()
 27	$command.CommandText = $table
 28	$null = $command.ExecuteNonQuery()
 29	$command.Dispose()
 30	$connection.Close()
 31	$connection.Dispose()
 32}      
 33
 34# updatedb
 35$connection = New-Object System.Data.SQLite.SQLiteConnection($connString)
 36$connection.Open()
 37$command = $connection.CreateCommand()
 38
 39# delete table
 40$command.CommandText = "DROP TABLE [Files]"
 41[void]$command.ExecuteNonQuery()
 42$command.CommandText = "CREATE TABLE [Files] ([Name] nvarchar(450) PRIMARY KEY)"
 43[void]$command.ExecuteNonQuery()       
 44
 45$transaction = $connection.BeginTransaction()	
 46Function Get-Filenames ($path) {
 47	Set-Variable -ErrorAction SilentlyContinue -Name files
 48	
 49	# Get Directories
 50	try
 51	{
 52	   $files = [IO.Directory]::GetFiles($path)
 53	   [System.IO.DirectoryInfo]$directoryInfo = New-Object IO.DirectoryInfo($path)
 54	   $folders = $directoryInfo.GetDirectories() | Where-Object {$_.Name -ne "`$Recycle.Bin" -and $folder -ne "System Volume Information" }
 55		  
 56	} catch { $folders = @()}
 57	
 58	foreach($filename in $files) 
 59		{
 60			$filename = $filename.replace('\\','\')
 61			$filename = $filename.replace("'","''")
 62			$command.CommandText = "insert into files values ('$filename')"
 63			[void]$command.ExecuteNonQuery()
 64		}
 65
 66	# Create list of non-indexed directories
 67	$exculde = @($env:APPDATA)
 68	$exclude += $env:LOCALAPPDATA
 69	$exclude += $env:ProgramData
 70	$exclude += $env:TMP
 71	$exclude += $env:TEMP
 72	
 73	try {
 74		foreach($folder in $folders)
 75		{ 
 76			if ($exclude -notcontains "$path$folder") { Get-Filenames("$path\$folder")}
 77		}
 78	} catch {}
 79	Remove-Variable -ErrorAction SilentlyContinue -Name files 
 80}
 81
 82Measure-command { Get-Filenames("C:\") }
 83$transaction.Commit()
 84
 85$command.Dispose()
 86$connection.Close()
 87$connection.Dispose()
 88
 89# Perform search
 90$connection = New-Object System.Data.SQLite.SQLiteConnection($connString)
 91$connection.Open()
 92$command = $connection.CreateCommand()
 93Measure-Command {
 94	$file = "SqlServerCe"
 95	$file = $file.Replace("`*","`%")
 96	$sql = "select name from files where name like '%$file%'"
 97	$command.CommandText = $sql
 98	$datatable = New-Object System.Data.DataTable
 99	$datatable.load($command.ExecuteReader())
100}
101$command.Dispose()
102$connection.Close()
103$connection.Dispose()
104
105$datatable.name

This query took an incredible 0.3 seconds. I recommend using SQLite for any small projects, if at all possible. If you're interested in the formal script, you can download it on ScriptCenter