AutoHotkey Community

Posted: **03 Apr 2019, 05:41**

I had recently created a script using sqlite3.exe, but then just realized a few days ago that @kczx3 had ported @just me's Class_SQLiteDB.ahk sql wrapper to AHK_V2. Therefore, I am posting examples for both!

As a dataset, I chose an excerpt from the CC_CEDICT Chinese-English dictionary. sqlite3.exe won't work on the command line by feeding it directly with non-ASCII data, so it needs to be input to sqlite3.exe using the .read "dot-command". All of this is done in the script.

The links to the sample dataset and download pages for sqlite3.exe and sqlite3.dll are included at the top of the source code.

sqlite3_exe_example.ahk
https://pastebin.com/xKjmEJeB

Code: Select all

;**************
; REQUIREMENTS*
;**************

; AutoHotkey Version 2 (AutoHotkey_2.0-a100-52515e2)
; https://www.autohotkey.com/download/2.0/AutoHotkey_2.0-a100-52515e2.zip
; sqlite3.exe located in A_ScriptDir (i.e., the directory that contains this .ahk file)
; https://www.sqlite.org/2019/sqlite-tools-win32-x86-3270200.zip
; Chinese-English glossary file (excerpt from CC-CEDICT) in A_ScriptDir
; https://pastebin.com/BS3KnzRL
; Official link to complete CC-CEDICT
; https://cc-cedict.org/editor/editor_export_cedict.php?c=zip

;*************
; DISCLAIMER:*
;*************

; This software is provided 'as-is', without any express or implied warranty.
; In no event will the authors be held liable for any damages arising from the
; use of this software.

;*******************************************
; SPECIFY NAME FOR DATABASE AND DATA SOURCE*
;*******************************************

db_file := A_ScriptDir . "\ce_glossary.sqlite"
;source_files := A_ScriptDir . "\chinese_english\*.txt"
source_files := A_ScriptDir . "\CC-CEDICT.txt"

;****************************************
; FUNCTIONS FOR EXECUTING SQL STATEMENTS*
;****************************************

ExecuteSql(statement_type, sql_cmd)
{
	; SQLite3.exe user manual with explanation of "dot-commands": https://www.sqlite.org/cli.html
	; Other statement types can be added as needed
	
	global db_file
	db_file := RegExReplace(db_file, "\\", "\\") ; sqlite.exe needs double backslashes
	result := ''
	
	If statement_type = "CREATE"
	{
		sql_cmd_file := ReadInFile(sql_cmd)
		log_file := LogFile()
		RunWait(A_ScriptDir . '\sqlite3.exe ' . db_file . ' ".log ' . log_file . '"' . ' ".read ' . sql_cmd_file . '"',, "Hide")
		CheckForError(log_file)
	}
	Else If (statement_type = "INSERT")
	{
		sql_cmd_file := ReadInFile(sql_cmd)
		log_file := LogFile()
		RunWait(A_ScriptDir . '\sqlite3.exe ' . db_file . ' ".log ' . log_file . '"' . ' ".read ' . sql_cmd_file . '"',, "Hide")
		CheckForError(log_file)
	}
	Else If (statement_type = "QUERY")
	{
		sql_cmd_file := ReadInFile(sql_cmd)
		log_file := LogFile()
		sql_out_file := OutputFile()
		RunWait(A_ScriptDir . '\sqlite3.exe ' . db_file . ' ".log ' . log_file . '"' . ' ".output ' . sql_out_file . '"' . ' ".read ' . sql_cmd_file . '"',, "Hide")
		CheckForError(log_file)
		result := FileRead(sql_out_file, "UTF-8-RAW")
	}
	Else
	{
		MsgBox("The statement type you specified is incorrect or not implemented yet. Aborting...")
		ExitApp()
	}
	Return(result)
}

ReadInFile(sql_cmd)
{
	; Return value used with the sqlite3.exe .read "dot-command"
	
	sql_cmd_file := A_ScriptDir . "\sql_command_file.txt" ; write commands to file for read-in (needed for non-ascii charsets)
	FileDelete(sql_cmd_file)
	FileAppend(sql_cmd, sql_cmd_file, "UTF-8-RAW") ; sqlite3.exe needs UTF-8 without BOM
	sql_cmd_file := RegExReplace(sql_cmd_file, "\\", "\\") ; sqlite3.exe needs double backslashes
	Return(sql_cmd_file)
}

LogFile()
{
	; Return value used with the sqlite3.exe .log "dot-command"
	
	log_file := A_ScriptDir . "\log_file.txt" ; write log messages such as errors to file
	FileDelete(log_file)
	log_file := RegExReplace(log_file, "\\", "\\")
	Return(log_file)
}

OutputFile()
{
	; Return value used with the sqlite3.exe .output "dot-command"
	
	sql_out_file := A_ScriptDir . "\sql_output_file.txt" ; writes output to file (needed for non-ascii charsets)
	FileDelete(sql_out_file)
	sql_out_file := RegExReplace(sql_out_file, "\\", "\\")
	Return(sql_out_file)
}

CheckForError(log_file)
{
		
	; SQLite3 result and error codes: https://sqlite.org/rescode.html
	; We log notices and warnings (codes 27, 28 respectively), but log and abort for all other codes
	
	If (FileExist(log_file) And FileGetSize(log_file))
	{
		sqlite_error := FileRead(log_file, "UTF-8-RAW")
		If RegExMatch(sqlite_error, "m)^\(((?:(?!27\))(?!28\))\d)\d*)\)") ; check for any error code except 27 or 28
		{
			MsgBox("There was an sqlite3.exe error:`n" . sqlite_error)
			FileAppend("`n" . A_Now . "`n" . sqlite_error, A_ScriptDir . "\sqlite_log.txt", "UTF-8-RAW")
			ExitApp()
		}
		Else
		{
			FileAppend("`n" . A_Now . "`n" . sqlite_error, A_ScriptDir . "\sqlite_log.txt", "UTF-8-RAW")
			MsgBox("Check your log file for notices and warnings!")
		}
	}
}

CleanUp()
{
	FileDelete(A_ScriptDir . "\sql_command_file.txt")
	FileDelete(A_ScriptDir . "\log_file.txt")
	FileDelete(A_ScriptDir . "\sql_output_file.txt")
}

;***********************************************************************************
; READ IN DATA (COULD BE MEMORY INTENSIVE STORING ALL ENTRIES IN ASSOCIATIVE ARRAY)*
;***********************************************************************************

dict := {} ; use associative array to remove duplicates
FileEncoding("UTF-8")
Loop Files, source_files
{
	Loop Read, A_LoopFileFullPath
	{
		dict[A_LoopReadLine] := 1
	}
}

;***************************
; CREATE DATABASE AND TABLE*
;***************************

sql_cmd := "CREATE TABLE IF NOT EXISTS glossary (id INTEGER PRIMARY KEY, chinese TEXT, english TEXT)"
ExecuteSql("CREATE", sql_cmd)

;**********************************************************
; CREATE INSERT STATEMENT (COULD ALSO BE MEMORY INTENSIVE)*
;**********************************************************

counter := 0
insert_query := "INSERT INTO glossary (chinese, english) VALUES"
For key, value In dict
{
	counter++
	c_e := StrSplit(key, A_Tab,, 2)
	insert_query .= '("' . c_e[1] . '", "' . c_e[2] . '"),'
}
insert_query := RegExReplace(insert_query, ',$', ';') ; replace trailing comma with semicolon

;**************************
; EXECUTE INSERT STATEMENT*
;**************************

sql_cmd := insert_query
ExecuteSql("INSERT", sql_cmd)
MsgBox(counter . " row(s) inserted.")

;*****************************
; MAKE A QUERY (FUZZY SEARCH)*
;*****************************

;search_query := Clipboard ; could set up a hotkey and then grab search query from clipboard
search_query := '職業'
sql_cmd := "SELECT chinese, english FROM glossary WHERE chinese LIKE '%" . search_query . "%'"
result := ExecuteSql("QUERY", sql_cmd)
;MsgBox(result)

;***********************************
; PROCESS RESULTS IN AHK (OPTIONAL)*
;***********************************

counter := 0
result_string := ' search query results for: ' . search_query . '`n'
Loop Parse, result, "`n", "`r"
{
	If InStr(A_LoopField, '|')
	{
		counter++
		c_e := StrSplit(A_LoopField, '|',, 2)
		result_string .= A_Tab . c_e[1] . A_Tab . c_e[2] . '`n'
	}
}
result_string := counter . result_string
MsgBox(result_string)

;****************************************
; CLEAN UP FILES PRODUCED BY SQLITE3.EXE*
;****************************************

CleanUp()

sqlite3_class_sqlitedb_example.ahk
https://pastebin.com/mbrcBNUz

Code: Select all

;**************
; REQUIREMENTS*
;**************

; AutoHotkey Version 2 (AutoHotkey_2.0-a100-52515e2)
; https://www.autohotkey.com/download/2.0/AutoHotkey_2.0-a100-52515e2.zip
; sqlite3.dll located in A_ScriptDir (i.e., the directory that contains this .ahk file)
; https://sqlite.org/2019/sqlite-dll-win32-x86-3270200.zip (32-bit)
; Or
; https://sqlite.org/2019/sqlite-dll-win64-x64-3270200.zip (64-bit)
; Class_SQLiteDB.ahk located in A_ScriptDir
; https://pastebin.com/mJ4gFVCv
; Chinese-English glossary file (excerpt from CC-CEDICT) in A_ScriptDir
; https://pastebin.com/BS3KnzRL
; Official link to complete CC-CEDICT
; https://cc-cedict.org/editor/editor_export_cedict.php?c=zip

;*************
; DISCLAIMER:*
;*************

; This software is provided 'as-is', without any express or implied warranty.
; In no event will the authors be held liable for any damages arising from the
; use of this software.

;************************
; INCLUDE SQLITE WRAPPER*
;************************

#Include Class_SQLiteDB.ahk

;*******************************************
; SPECIFY NAME FOR DATABASE AND DATA SOURCE*
;*******************************************

db_file := A_ScriptDir . "\ce_glossary.sqlite"
;source_files := A_ScriptDir . "\chinese_english\*.txt"
source_files := A_ScriptDir . "\CC-CEDICT.txt"

;***********************************************************************************
; READ IN DATA (COULD BE MEMORY INTENSIVE STORING ALL ENTRIES IN ASSOCIATIVE ARRAY)*
;***********************************************************************************

dict := {} ; use associative array to remove duplicates
FileEncoding("UTF-8")
Loop Files, source_files
{
	Loop Read, A_LoopFileFullPath
	{
		dict[A_LoopReadLine] := 1
	}
}

;***********************************
; OPEN DATABASE OR CREATE A NEW ONE*
;***********************************

db := New SQLiteDb
If !db.OpenDb(db_file)
{
	MsgBox("SQLite Error OpenDb`n`nMessage: " . db.ErrorMsg . "`nCode: " . db.ErrorCode . "`nFile: " . db_file)
	ExitApp()
}

;***********************
; CREATE DATABASE TABLE*
;***********************

sql_cmd := "CREATE TABLE glossary (id INTEGER PRIMARY KEY, chinese TEXT, english TEXT)"
If !db.Exec(sql_cmd)
{
	MsgBox("SQLite CREATE Error`n`nMessage: " . db.ErrorMsg . "`nCode: " . db.ErrorCode . "`nQuery: " . sql_cmd)
	ExitApp()
}

;**********************************************************
; CREATE INSERT STATEMENT (COULD ALSO BE MEMORY INTENSIVE)*
;**********************************************************

insert_query := "INSERT INTO glossary (chinese, english) VALUES"
For key, value In dict
{
	c_e := StrSplit(key, A_Tab,, 2)
	insert_query .= '("' . c_e[1] . '", "' . c_e[2] . '"),'
}
insert_query := RegExReplace(insert_query, ',$', ';') ; replace trailing comma with semicolon

;**************************
; EXECUTE INSERT STATEMENT*
;**************************

sql_cmd := insert_query
result := db.Exec(sql_cmd)
If (result)
{
	MsgBox("Inserted " . db.Changes . " rows.")
}
Else
{
	MsgBox("SQLite Insert Error`n`nMessage: " . db.ErrorMsg . "`nCode: " . db.ErrorCode . "`nQuery: " . sql_cmd)
	ExitApp()
}

;*****************************
; MAKE A QUERY (FUZZY SEARCH)*
;*****************************

;search_query := Clipboard ; could set up a hotkey and then grab search query from clipboard
search_query := '職業'
query := "SELECT chinese, english FROM glossary WHERE chinese LIKE '%" . search_query . "%'"
sql_cmd := query
If !db.Query(sql_cmd, query_result)
{
	MsgBox("SQLite QUERY Error`n`nMessage: " . db.ErrorMsg . "`nCode: " . db.ErrorCode . "`nFile: " . db_file . "`nQuery: " . sql_cmd)
	ExitApp
}
search_result := ''
Loop
{
	result := query_result.Next(row)
	If !result
	{
		MsgBox("SQLite NEXT Error`n`nMessage: " . db.ErrorMsg . "`nCode: " . db.ErrorCode)
		ExitApp
	}
	If result = -1
	{
		Break
	}
	Loop query_result.ColumnCount
	{
		search_result .= row[A_Index] . A_Tab
	}
	search_result .= '`n'
}
query_result.Free()
MsgBox(search_result)

AutoHotkey Community

[AHK_V2] sqlite3.exe and Class_SQLiteDB.ahk examples

[AHK_V2] sqlite3.exe and Class_SQLiteDB.ahk examples