summaryrefslogtreecommitdiffstats
path: root/lib/dbmodelfunctions.lua
blob: d333548924d59cf3c0dd178d48d395ef84f98c7a (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
local mymodule = {}

db = require("acf.db")

-- Helper library for model functions for viewing and editing database entries
-- Relies heavily on the acf-lib/db.lua library (db object passed into each function)

local function get_connection(dbase, self, clientdata)
	local retval = cfe({ type="group", value={} })
	retval.value.connection = cfe({ type="group", value={}, label="Database Connection", seq=0 })
	retval.value.connection.value.engine = cfe({ type="select", label="Database Engine", option={}, key=true, seq=1 })
	for n,v in pairs(db.engine) do
		retval.value.connection.value.engine.option[#retval.value.connection.value.engine.option+1] = {label=n, value=tostring(v)}
	end
	retval.value.connection.value.database = cfe({ label="Database", key=true, seq=2 })
	retval.value.connection.value.user = cfe({ label="User", key=true, seq=3 })
	retval.value.connection.value.password = cfe({ type="password", label="Password", key=true, seq=4 })
	retval.value.connection.value.host = cfe({ label="Host", key=true, seq=5 })
	retval.value.connection.value.port = cfe({ label="Port", key=true, seq=6 })

	self.handle_clientdata(retval, clientdata)

	-- If dbase has a default, remove the cfe. Otherwise, fill in with the value from the cfe.
	for n,v in pairs(retval.value.connection.value) do
		if dbase[n] then
			retval.value.connection.value[n] = nil
		elseif n == "engine" or n == "port" then
			dbase[n] = tonumber(v.value)
		else
			dbase[n] = v.value
		end
	end

	return retval
end

local function fill_connection(dbase, self, retval)
	-- Fill in dbase with the values from the cfe retval
	for n,v in pairs(retval.value.connection.value) do
		if n == "engine" or n == "port" then
			dbase[n] = tonumber(v.value)
		else
			dbase[n] = v.value
		end
	end

	return retval
end

function mymodule.list_databases(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "List of Databases"
	retval.value.databases = cfe({ type="list", value={}, label="List of Databases" })
	local res, err = pcall(function()
		retval.value.databases.value = db.listdatabases()
	end)
	if not res and err then
		retval.value.connection.errtxt = err
	end

	return retval
end

function mymodule.list_tables(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "List of Database Tables"
	retval.value.tables = cfe({ type="list", value={}, label="List of Database Tables" })
	local res, err = pcall(function()
		local connected = db.databaseconnect()
		retval.value.tables.value = db.listtables()
		if connected then db.databasedisconnect() end
	end)
	if not res and err then
		retval.value.connection.errtxt = err
	end
	return retval
end

function mymodule.list_table_entries(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "Database Table Entries"
	-- Need to specify the table
	retval.value.table = cfe({ label="Table", key=true })
	-- Here is the pagination/filtering info
        retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true })
	retval.value.pagesize = cfe({ value=10, label="Page Size", key=true })
	retval.value.rowcount = cfe({ value=0, label="Row Count" })
	-- orderby must be an array of tables with column name and direction
	retval.value.orderby = cfe({ type="structure", value={}, label="Order By", key=true })
	-- filter is a table with a string filter for each column
	retval.value.filter = cfe({ type="structure", value={}, label="Filter", key=true })
	self.handle_clientdata(retval, clientdata)

	-- Here is the result
	retval.value.fields = cfe({ type="list", value={}, label="List of Table Fields" })
	retval.value.keyfields = cfe({ type="list", value={}, label="List of Key Table Fields" })
	retval.value.entries = cfe({ type="structure", value={}, label="List of Database Entries" })

        -- Process the incoming page data
	local page = tonumber(retval.value.page.value) or 0
	retval.value.page.value = page
	local pagesize = tonumber(retval.value.pagesize.value) or 10
	retval.value.pagesize.value = pagesize

	local res, err = pcall(function()
		local connected = db.databaseconnect()
		local tables = db.listtables()
		retval.value.table.errtxt = "Table does not exist"
		retval.errtxt = "Table does not exist"
		for i,t in ipairs(tables) do
			if t == retval.value.table.value then
				retval.value.table.errtxt = nil
				retval.errtxt = nil
				-- FIXME - in the future, we should be able to do next these three things in one function call to db
				retval.value.fields.value = db.listcolumns(t) or {}
				retval.value.keyfields.value = db.listkeycolumns(t) or {}
				-- this will not work for sqlite and the results are database-specific
				local sql = "SELECT data_type FROM information_schema.columns WHERE table_name = '"..db.escape(t).."' ORDER BY ordinal_position"
				local types = db.getselectresponse(sql)
				local fieldtypes = {}
				for i,f in ipairs(types) do fieldtypes[retval.value.fields.value[i]] = f.data_type end

				local orderby = {}
				local columns = {}
				for i,c in ipairs(retval.value.fields.value) do columns[c] = c end
				local directions = {asc="ASC", desc="DESC", ASC="ASC", DESC="DESC"}
				for i,o in ipairs(retval.value.orderby.value) do
					if columns[o.column] and directions[o.direction] then
						orderby[#orderby+1] = columns[o.column].." "..directions[o.direction]
					end
				end

				local filter = {}
				-- FIXME - the following has not been exhaustively tested for all column types and databases
				for c,f in pairs(retval.value.filter.value) do
					if columns[c] and f ~= "" then
						if string.find(fieldtypes[c]:lower(), "char") or string.find(fieldtypes[c]:lower(), "text") then
							filter[#filter+1] = columns[c].."~'"..db.escape(f).."'"
						elseif tonumber(f) then
							filter[#filter+1] = columns[c].."="..tonumber(f)
						else
							filter[#filter+1] = columns[c].."='"..db.escape(f).."'"
						end
					end
				end

				sql = " FROM "..db.escape(t)
				if #filter>0 then
					sql = sql.." WHERE "..table.concat(filter, " AND ")
				end
				if page > 0 then
					local count = db.getselectresponse("SELECT count(*)"..sql)
					retval.value.rowcount.value = count[1].count
				end
				if #orderby>0 then
					sql = sql.." ORDER BY "..table.concat(orderby, ", ")
				end
				if page > 0 then
					sql = sql.." LIMIT "..pagesize.." OFFSET "..(page - 1)*pagesize
				end
				retval.value.entries.value = db.getselectresponse("SELECT *"..sql) or {}
				if page <= 0 then
					retval.value.rowcount.value = #retval.value.entries.value
				end
			end
		end
		if connected then db.databasedisconnect() end
	end)
	if not res and err then
		retval.value.connection.errtxt = err
	end

	return retval
end

function mymodule.get_new_table_entry(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "Database Table Entry"

	retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 })
	self.handle_clientdata(retval, clientdata)
	retval.errtxt = "Table does not exist"
	if retval.value.table.value ~= "" then
		local res, err = pcall(function()
			local connected = db.databaseconnect()
			local tables = db.listtables()
			for i,t in ipairs(tables) do
				if t == retval.value.table.value then
					retval.value.table.errtxt = nil
					retval.value.table.readonly = true
					retval.errtxt = nil
					for n,v in pairs(retval.value.connection.value) do
						v.readonly = true
					end
					break
				end
			end
			if not errtxt then
				retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 })
				retval.value.nulls = cfe({ type="group", value={}, label="Null Value Table Fields", seq=3 })
				retval.value.defaults = cfe({ type="group", value={}, label="Default Value Table Fields", seq=4 })
				local fields, defaults, nullable = db.listcolumns(retval.value.table.value)
				for i,f in ipairs(fields) do
					retval.value.fields.value[f] = cfe({ label=f, seq=i })
					if nullable[f] then
						retval.value.nulls.value[f] = cfe({ type="boolean", value=false, label=f, seq=i })
					end
					if defaults[f] then
						retval.value.defaults.value[f] = cfe({ type="boolean", value=true, label=f, default=defaults[f], seq=i })
					end
				end
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			retval.value.connection.errtxt = err
		end
	end

	return retval
end

local function get_field_value(dbase, self, entry, field)
	local retval
	if entry.value.defaults and entry.value.defaults.value[field] and entry.value.defaults.value[field].value then
		retval = "DEFAULT"
	elseif entry.value.nulls and entry.value.nulls.value[field] and entry.value.nulls.value[field].value then
		retval = "NULL"
	elseif entry.value.fields and entry.value.fields.value[field] then
		retval = "'"..dbase.escape(entry.value.fields.value[field].value).."'"
	end
	return retval
end

function mymodule.get_table_entry(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = mymodule.get_new_table_entry(db, self, clientdata)
	if retval.value.fields and retval.value.defaults then
		for n,v in pairs(retval.value.fields.value) do
			if retval.value.defaults.value[n] then
				retval.value.defaults.value[n].value = false
			end
		end
	end
	self.handle_clientdata(retval, clientdata) -- Fill in the fields
	if not retval.value.connection.errtxt and not retval.value.table.errtxt and retval.value.fields then
		local res, err = pcall(function()
			local connected = db.databaseconnect()
			local keyfields = db.listkeycolumns(retval.value.table.value)
			local reversekeyfields = {}
			local fieldchecks = {}
			for i,f in ipairs(keyfields) do
				reversekeyfields[f] = i
				retval.value.fields.value[f].errtxt = "Entry does not exist"
				fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, retval, f)
			end
			retval.errtxt = "Entry does not exist"
			local sql = "SELECT * FROM "..db.escape(retval.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
			local entry = db.getselectresponse(sql)
			if entry and #entry > 0 then
				retval.errtxt = nil
				--for n,v in pairs(entry[1]) do
				for n,v in pairs(retval.value.fields.value) do
					if not entry[1][n] then
						retval.value.nulls.value[n].value = true
					else
						retval.value.fields.value[n].value = entry[1][n]
					end
					if reversekeyfields[n] then
						retval.value.fields.value[n].readonly = true
						retval.value.fields.value[n].errtxt = nil
					end
				end
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			retval.errtxt = err
		end
	end

	return retval
end

function mymodule.create_table_entry(dbase, self, entry, action)
        return mymodule.update_table_entry(dbase, self, entry, action, true)
end

function mymodule.update_table_entry(dbase, self, entry, action, create)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end
	fill_connection(db, self, entry)

	local success = true
	local errtxt
	-- Validate the settings
	-- relying on get_table_entry to do the validation of table
	if entry.value.table.value == "" or entry.value.table.errtxt or nil == entry.value.fields then
		success = false
		entry.value.table.errtxt = "Table does not exist"
	end
	if success then
        	local res, err = pcall(function()
			local connected = db.databaseconnect()
			local keyfields = db.listkeycolumns(entry.value.table.value)
			local reversekeyfields = {}
			local fieldchecks = {}
			for i,f in ipairs(keyfields) do
				reversekeyfields[f] = i
				fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, entry, f)
			end
			-- We want to catch syntax errors, because they just indicate the entry does not exist
			local res2, err2 = pcall(function()
				local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
				local tmp = db.getselectresponse(sql)
				if not create and (not tmp or #tmp == 0) then
					success = false
					for i,n in ipairs(keyfields) do
						entry.value.fields.value[n].errtxt = "Entry does not exist"
				end
				elseif create and tmp and #tmp>0 then
					success = false
					for i,n in ipairs(keyfields) do
						entry.value.fields.value[n].errtxt = "Entry already exists"
					end
				end
			end)
			if not res2 and err2 then
				if not create then
					success = false
					errtxt = err
					for i,n in ipairs(keyfields) do
						entry.value.fields.value[n].errtxt = "Entry does not exist"
					end
				end
			end
			if success then
				local sql
				local names = {}
				local values = {}
				if create then
					for n,v in pairs(entry.value.fields.value) do
						names[#names+1] = n
						values[#values+1] = get_field_value(db, self, entry, n)
					end
					sql = "INSERT INTO "..db.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES("..table.concat(values, ", ")..")"
				else
					for n,v in pairs(entry.value.fields.value) do
						if not reversekeyfields[n] then
							values[#values+1] = n.."="..get_field_value(db, self, entry, n)
						end
					end
					sql = "UPDATE "..db.escape(entry.value.table.value).." SET "..table.concat(values, ", ").." WHERE "..table.concat(fieldchecks, " AND ")
				end
				db.runsqlcommand(sql)
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			entry.errtxt = errtxt or "Failed to create entry"
		else
			entry.errtxt = errtxt or "Failed to save entry"
		end
	end
	return entry
end

function mymodule.get_delete_table_entry(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "Delete Database Table Entry"

	retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 })
	self.handle_clientdata(retval, clientdata)
	retval.errtxt = "Table does not exist"
	if retval.value.table.value ~= "" then
		local res, err = pcall(function()
			local connected = db.databaseconnect()
			local tables = db.listtables()
			for i,t in ipairs(tables) do
				if t == retval.value.table.value then
					retval.value.table.errtxt = nil
					retval.value.table.readonly = true
					retval.errtxt = nil
					for n,v in pairs(retval.value.connection.value) do
						v.readonly = true
					end
					break
				end
			end
			if not errtxt then
				local keyfields = db.listkeycolumns(retval.value.table.value)
				retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 })
				retval.value.nulls = cfe({ type="group", value={}, label="Null Value Table Fields", seq=3 })
				for i,f in ipairs(keyfields) do
					retval.value.fields.value[f] = cfe({ label=f, seq=i })
					retval.value.nulls.value[f] = cfe({ type="boolean", value=false, label=f, seq=i })
				end
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			retval.value.connection.errtxt = err
		end
	end

	return retval
end

function mymodule.delete_table_entry(dbase, self, entry)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end
	fill_connection(db, self, entry)

	entry.errtxt = "Failed to delete table entry"
	if entry.value.table.value == "" then
		entry.value.table.errtxt = "Invalid table"
	else
	       	local res, err = pcall(function()
			local connected = db.databaseconnect()
			entry.value.table.errtxt = "Invalid table"
			local tables = db.listtables()
			for i,t in ipairs(tables) do
				if t == entry.value.table.value then
					entry.value.table.errtxt = nil
					break
				end
			end
			if not entry.value.table.errtxt then
				local keyfields = db.listkeycolumns(entry.value.table.value)
				local fieldchecks = {}
				for i,f in ipairs(keyfields) do
					fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, entry, f)
				end
				local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
				db.runsqlcommand(sql)
				entry.errtxt = nil
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			entry.errtxt = err
		end
	end

	return entry
end

return mymodule