· 6 years ago · Apr 03, 2019, 04:20 AM
1;**************
2; REQUIREMENTS*
3;**************
4
5; AutoHotkey Version 2 (AutoHotkey_2.0-a100-52515e2)
6; https://www.autohotkey.com/download/2.0/AutoHotkey_2.0-a100-52515e2.zip
7; sqlite3.exe located in A_ScriptDir (i.e., the directory that contains this .ahk file)
8; https://www.sqlite.org/2019/sqlite-tools-win32-x86-3270200.zip
9; Chinese-English glossary file (excerpt from CC-CEDICT) in A_ScriptDir
10; https://pastebin.com/BS3KnzRL
11; Official link to complete CC-CEDICT
12; https://cc-cedict.org/editor/editor_export_cedict.php?c=zip
13
14;*************
15; DISCLAIMER:*
16;*************
17
18; This software is provided 'as-is', without any express or implied warranty.
19; In no event will the authors be held liable for any damages arising from the
20; use of this software.
21
22;*******************************************
23; SPECIFY NAME FOR DATABASE AND DATA SOURCE*
24;*******************************************
25
26db_file := A_ScriptDir . "\ce_glossary.sqlite"
27;source_files := A_ScriptDir . "\chinese_english\*.txt"
28source_files := A_ScriptDir . "\CC-CEDICT.txt"
29
30;****************************************
31; FUNCTIONS FOR EXECUTING SQL STATEMENTS*
32;****************************************
33
34ExecuteSql(statement_type, sql_cmd)
35{
36 ; SQLite3.exe user manual with explanation of "dot-commands": https://www.sqlite.org/cli.html
37 ; Other statement types can be added as needed
38
39 global db_file
40 db_file := RegExReplace(db_file, "\\", "\\") ; sqlite.exe needs double backslashes
41 result := ''
42
43 If statement_type = "CREATE"
44 {
45 sql_cmd_file := ReadInFile(sql_cmd)
46 log_file := LogFile()
47 RunWait(A_ScriptDir . '\sqlite3.exe ' . db_file . ' ".log ' . log_file . '"' . ' ".read ' . sql_cmd_file . '"',, "Hide")
48 CheckForError(log_file)
49 }
50 Else If (statement_type = "INSERT")
51 {
52 sql_cmd_file := ReadInFile(sql_cmd)
53 log_file := LogFile()
54 RunWait(A_ScriptDir . '\sqlite3.exe ' . db_file . ' ".log ' . log_file . '"' . ' ".read ' . sql_cmd_file . '"',, "Hide")
55 CheckForError(log_file)
56 }
57 Else If (statement_type = "QUERY")
58 {
59 sql_cmd_file := ReadInFile(sql_cmd)
60 log_file := LogFile()
61 sql_out_file := OutputFile()
62 RunWait(A_ScriptDir . '\sqlite3.exe ' . db_file . ' ".log ' . log_file . '"' . ' ".output ' . sql_out_file . '"' . ' ".read ' . sql_cmd_file . '"',, "Hide")
63 CheckForError(log_file)
64 result := FileRead(sql_out_file, "UTF-8-RAW")
65 }
66 Else
67 {
68 MsgBox("The statement type you specified is incorrect or not implemented yet. Aborting...")
69 ExitApp()
70 }
71 Return(result)
72}
73
74ReadInFile(sql_cmd)
75{
76 ; Return value used with the sqlite3.exe .read "dot-command"
77
78 sql_cmd_file := A_ScriptDir . "\sql_command_file.txt" ; write commands to file for read-in (needed for non-ascii charsets)
79 FileDelete(sql_cmd_file)
80 FileAppend(sql_cmd, sql_cmd_file, "UTF-8-RAW") ; sqlite3.exe needs UTF-8 without BOM
81 sql_cmd_file := RegExReplace(sql_cmd_file, "\\", "\\") ; sqlite3.exe needs double backslashes
82 Return(sql_cmd_file)
83}
84
85LogFile()
86{
87 ; Return value used with the sqlite3.exe .log "dot-command"
88
89 log_file := A_ScriptDir . "\log_file.txt" ; write log messages such as errors to file
90 FileDelete(log_file)
91 log_file := RegExReplace(log_file, "\\", "\\")
92 Return(log_file)
93}
94
95OutputFile()
96{
97 ; Return value used with the sqlite3.exe .output "dot-command"
98
99 sql_out_file := A_ScriptDir . "\sql_output_file.txt" ; writes output to file (needed for non-ascii charsets)
100 FileDelete(sql_out_file)
101 sql_out_file := RegExReplace(sql_out_file, "\\", "\\")
102 Return(sql_out_file)
103}
104
105CheckForError(log_file)
106{
107
108 ; SQLite3 result and error codes: https://sqlite.org/rescode.html
109 ; We log notices and warnings (codes 27, 28 respectively), but log and abort for all other codes
110
111 If (FileExist(log_file) And FileGetSize(log_file))
112 {
113 sqlite_error := FileRead(log_file, "UTF-8-RAW")
114 If RegExMatch(sqlite_error, "m)^\(((?:(?!27\))(?!28\))\d)\d*)\)") ; check for any error code except 27 or 28
115 {
116 MsgBox("There was an sqlite3.exe error:`n" . sqlite_error)
117 FileAppend("`n" . A_Now . "`n" . sqlite_error, A_ScriptDir . "\sqlite_log.txt", "UTF-8-RAW")
118 ExitApp()
119 }
120 Else
121 {
122 FileAppend("`n" . A_Now . "`n" . sqlite_error, A_ScriptDir . "\sqlite_log.txt", "UTF-8-RAW")
123 MsgBox("Check your log file for notices and warnings!")
124 }
125 }
126}
127
128CleanUp()
129{
130 FileDelete(A_ScriptDir . "\sql_command_file.txt")
131 FileDelete(A_ScriptDir . "\log_file.txt")
132 FileDelete(A_ScriptDir . "\sql_output_file.txt")
133}
134
135;***********************************************************************************
136; READ IN DATA (COULD BE MEMORY INTENSIVE STORING ALL ENTRIES IN ASSOCIATIVE ARRAY)*
137;***********************************************************************************
138
139dict := {} ; use associative array to remove duplicates
140FileEncoding("UTF-8")
141Loop Files, source_files
142{
143 Loop Read, A_LoopFileFullPath
144 {
145 dict[A_LoopReadLine] := 1
146 }
147}
148
149;***************************
150; CREATE DATABASE AND TABLE*
151;***************************
152
153sql_cmd := "CREATE TABLE IF NOT EXISTS glossary (id INTEGER PRIMARY KEY, chinese TEXT, english TEXT)"
154ExecuteSql("CREATE", sql_cmd)
155
156;**********************************************************
157; CREATE INSERT STATEMENT (COULD ALSO BE MEMORY INTENSIVE)*
158;**********************************************************
159
160counter := 0
161insert_query := "INSERT INTO glossary (chinese, english) VALUES"
162For key, value In dict
163{
164 counter++
165 c_e := StrSplit(key, A_Tab,, 2)
166 insert_query .= '("' . c_e[1] . '", "' . c_e[2] . '"),'
167}
168insert_query := RegExReplace(insert_query, ',$', ';') ; replace trailing comma with semicolon
169
170;**************************
171; EXECUTE INSERT STATEMENT*
172;**************************
173
174sql_cmd := insert_query
175ExecuteSql("INSERT", sql_cmd)
176MsgBox(counter . " row(s) inserted.")
177
178;*****************************
179; MAKE A QUERY (FUZZY SEARCH)*
180;*****************************
181
182;search_query := Clipboard ; could set up a hotkey and then grab search query from clipboard
183search_query := 'è·æ¥'
184sql_cmd := "SELECT chinese, english FROM glossary WHERE chinese LIKE '%" . search_query . "%'"
185result := ExecuteSql("QUERY", sql_cmd)
186;MsgBox(result)
187
188;***********************************
189; PROCESS RESULTS IN AHK (OPTIONAL)*
190;***********************************
191
192counter := 0
193result_string := ' search query results for: ' . search_query . '`n'
194Loop Parse, result, "`n", "`r"
195{
196 If InStr(A_LoopField, '|')
197 {
198 counter++
199 c_e := StrSplit(A_LoopField, '|',, 2)
200 result_string .= A_Tab . c_e[1] . A_Tab . c_e[2] . '`n'
201 }
202}
203result_string := counter . result_string
204MsgBox(result_string)
205
206;****************************************
207; CLEAN UP FILES PRODUCED BY SQLITE3.EXE*
208;****************************************
209
210CleanUp()