database.lua 11.3 KB
Newer Older
1
local lsqlite = require "lsqlite3"
2
3
4
5

local lfs = require "lfs"
local getattribute = lfs.attributes

6
local log = require "openbus.util.logger"
7
8
9
10

local oo = require "openbus.util.oo"
local class = oo.class

11
local DataBase = class()
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
local SQL_create_tables = [[
  CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS category (
    id   TEXT PRIMARY KEY, 
    name TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS certificate (
    entity TEXT PRIMARY KEY,
    certificate BLOB NOT NULL
  );

  CREATE TABLE IF NOT EXISTS entity (
    id          TEXT PRIMARY KEY,
    name        TEXT,
    category TEXT NOT NULL
      REFERENCES category(id)
      ON DELETE CASCADE    
  );

  CREATE TABLE IF NOT EXISTS login (
    id                  TEXT PRIMARY KEY,
    entity              TEXT NOT NULL,
    encodedkey          BLOB NOT NULL,
    allowLegacyDelegate INTEGER NOT NULL
  );

  CREATE TABLE IF NOT EXISTS loginObserver (
    id       TEXT PRIMARY KEY,
    ior      TEXT NOT NULL,
    legacy   INTEGER,
    login TEXT NOT NULL
      REFERENCES login(id)
      ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS watchedLogin (
    login_observer TEXT NOT NULL
      REFERENCES loginObserver(id)
      ON DELETE CASCADE,
    login TEXT NOT NULL
      REFERENCES login(id)
      ON DELETE CASCADE,
    CONSTRAINT pkey PRIMARY KEY (
      login_observer,
      login)
  );

  CREATE TABLE IF NOT EXISTS interface (
    repid TEXT PRIMARY KEY
  );

  CREATE TABLE IF NOT EXISTS entityInterface (
    entity    TEXT
      REFERENCES entity(id)
      ON DELETE CASCADE,
    interface TEXT
      REFERENCES interface(repid)
      ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS facet (
    name           TEXT,
    interface_name TEXT,
    offer          TEXT
      REFERENCES offer(id)
      ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS propertyOffer (
    name     TEXT NOT NULL,
    value    TEXT NOT NULL,
    offer    TEXT NOT NULL
      REFERENCES offer(id)
      ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS propertyOfferRegistryObserver (
    name                       TEXT NOT NULL,
    value                      TEXT NOT NULL,
    offer_registry_observer    TEXT NOT NULL
      REFERENCES offerRegistryObserver(id)
      ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS offer (
    id                      TEXT PRIMARY KEY,
    service_ref             TEXT NOT NULL,
    entity                  TEXT NOT NULL,
    login                   TEXT NOT NULL,
    timestamp               TEXT NOT NULL,
    day                     TEXT NOT NULL,
    month                   TEXT NOT NULL,
    year                    TEXT NOT NULL,
    hour                    TEXT NOT NULL,
    minute                  TEXT NOT NULL,
    second                  TEXT NOT NULL,
    component_name          TEXT NOT NULL,
    component_major_version TEXT NOT NULL,
    component_minor_version TEXT NOT NULL,
    component_patch_version TEXT NOT NULL,
    component_platform_spec TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS offerObserver (
    id       TEXT PRIMARY KEY,
    login    TEXT NOT NULL,
    observer TEXT NOT NULL,
    offer    TEXT NOT NULL
      REFERENCES offer(id)
      ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS offerRegistryObserver (
    id       TEXT PRIMARY KEY,
    login    TEXT NOT NULL,
    observer TEXT NOT NULL
  );
]]

local actions = {
  -- INSERT
  { name = "addCategory",
    values = { "id", "name" } },
  { name="addEntity",
    values = { "id", "name", "category" } },
  { name="addInterface",
    values = { "repid" } },
  { name="addEntityInterface",
    values = { "entity", "interface" } },
  { name="addOffer",
    values = { "id", "service_ref", "entity", "login", "timestamp",
	       "day", "month", "year", "hour", "minute",
	       "second", "component_name", "component_major_version",
	       "component_minor_version", "component_patch_version",
	       "component_platform_spec" } },
  { name="addPropertyOffer",
    values = { "name", "value", "offer" } },
  { name="addPropertyOfferRegistryObserver",
    values = { "name", "value", "offer_registry_observer" } },
  { name="addFacet",
    values = { "name", "interface_name", "offer" } },
  { name="addOfferObserver",
    values = { "id", "login", "observer", "offer" } },
  { name="addOfferRegistryObserver",
    values = { "id", "login", "observer" } },
  { name="addSettings",
    values = { "key", "value" } },
  { name="addLogin",
    values = { "id", "entity", "encodedKey", "allowLegacyDelegate" } },
  { name="addLoginObserver",
    values = { "id", "ior", "legacy", "login" } },
  { name="addWatchedLogin",
    values = { "login_observer", "login" } },
  { name="addCertificate",
    values = { "certificate", "entity" } },

  -- DELETE
  { name="delCategory",
    where = { "id" } },
  { name="delEntity",
    where = { "id" } },
  { name="delEntityInterface",
    where = { "entity", "interface" } },
  { name="delInterface",
    where = { "repid" } },
  { name="delOffer",
    where = { "id" } },
  { name="delPropertyOffer",
    where = { "offer" } },
187
188
  { name="delFacet",
    where = { "offer" } },
189
190
191
192
  { name="delOfferObserver",
    where = { "id" } },
  { name="delOfferRegistryObserver",
    where = { "id" } },
193
194
  { name="delPropertyOfferRegistryObserver",
    where = { "offer_registry_observer" } },
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
  { name="delLogin",
    where = { "id" } },
  { name="delLoginObserver",
    where = { "id" } },
  { name="delWatchedLogin",
    where = { "login_observer", "login" } },
  { name="delCertificate",
    where = { "entity" } },

  -- UPDATE
  { name="setCategory",
    set="name",
    where="id" },
  { name="setEntity",
    set="name",
    where="id" },
  { name="setCertificate",
    set="certificate",
    where="entity" },

  -- SELECT
  { name="getCategory",
    select = { "id", "name" } },
  { name="getCertificate",
    select = { "entity, certificate" } },
  { name="getEntity",
    select = { "id", "name", "category" }, 
    from = { "entity" } },
  { name="getEntityById",
    select = { "id" }, 
    from = { "entity" },
    where = { "id" } },
  { name="getEntityWithCerts",
    select = { "entity" }, 
    from = { "certificate" } },
  { name="getInterface",
    select = { "repid" },
    from = { "interface" } },
  { name="getAuthorizedInterface",
    select = { "interface.repid" },
    from = { "entityInterface", "interface" },
    where_hc = { "interface.repid = entityInterface.interface" },
    where = { "entityInterface.entity" } },
  { name="getOffer",
    select = { "*" },
    from = { "offer" } },
  { name="getPropertyOffer",
    select = { "*" },
    from = { "propertyOffer" },
    where = { "offer" } },
  { name="getFacet",
    select = { "*" },
    from = { "facet" },
    where = { "offer" } },
  { name="getOfferObserver",
    select = { "*" },
    from = { "offerObserver" },
    where = { "offer" } },
  { name="getOfferRegistryObserver",
    select = { "*" },
    from = { "offerRegistryObserver" },
  },
257
258
259
260
261
  { name="getPropertyOfferRegistryObserver",
    select = { "*" },
    from = { "propertyOfferRegistryObserver" },
    where = { "offer_registry_observer" },
  },
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
  { name="getSettings",
    select = { "value" },
    from = { "settings" },
    where = { "key" }
  },
  { name="getLogin",
    select = { "*" },
    from = { "login" }
  },
  { name="getLoginObserver",
    select = { "*" },
    from = { "loginObserver" }
  },
  { name="getWatchedLoginByObserver",
    select = { "login" },
    from = { "watchedLogin" },
    where = { "login_observer" },
  },
  { name="getAuthorizedInterfaces",
    select = { "interface.repid" },
    from = { "entityInterface", "interface" },
    where_hc = { "interface.repid = entityInterface.interface" },
    where = { "entityInterface.entity" },
  },
}

local emsgprefix = "SQLite error with code="

local function herror(code, extmsg)
  if code and code ~= lsqlite.OK then
    local msg = emsgprefix..tostring(code)
    if extmsg then
      msg = msg.."; "..extmsg  
295
    end
296
    return nil, msg
297
298
  end
  return true
299
300
end

301
302
local function gsubSQL(sql)
  return "SQL: [["..string.gsub(sql, '%s+', ' ').."]]"
303
304
end

305
306
307
local function iClause(sql, clause, entries, sep, suf)
  if not entries then
    return sql
308
  end
309
310
311
312
  if sql then
    sql = sql.." "
  else
    sql = ""
313
  end
314
315
316
317
  if not string.find(sql, clause) then 
     sql = sql..clause.." "
  else
     sql = sql.." AND "
318
  end
319
320
321
322
  for i, col in ipairs(entries) do
    if i > 1 then sql = sql..sep.." " end
    sql = sql..col
    if suf then sql = sql.." "..suf.." " end
323
  end
324
  return sql
325
326
end

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
local function buildSQL(action)
  local name = action.name
  local verb = string.sub(name, 1, 3)
  local sql
  local stable = string.lower(string.sub(name, 4, 4))
     ..string.sub(name, 5, -1)
  if "add" == verb then
    sql = "INSERT INTO "..stable.." ("
    local values = action.values
    sql = sql..table.concat(values, ",")
    sql = sql..") VALUES ("
    sql = sql..string.rep("?", #values, ",")
    sql = sql..")"
  elseif "del" == verb then
    sql = "DELETE FROM "..stable.." "
    sql = iClause(sql, "WHERE", action.where, "AND", "= ?") 
  elseif "set" == verb then
    local stable = action.table or stable
    sql = "UPDATE "..stable.. " "
    sql = sql.."SET "..action.set.. " = ? "
    sql = sql.."WHERE "..action.where.." = ?"
  elseif "get" == verb then
    sql = iClause(nil, "SELECT", action.select, ",")
    local from = action.from
    if from then 
      sql = iClause(sql, "FROM", action.from, ",")
    else
      sql = sql.." FROM "..stable
    end
    sql = iClause(sql, "WHERE", action.where, "AND", "= ?") 
    sql = iClause(sql, "WHERE", action.where_hc, "AND") 
358
  end
359
  return sql
360
361
end

362
363
364
365
function DataBase:aexec(sql)
  local gsql = gsubSQL(sql)
  log:database(sql)
  assert(herror(self.conn:exec(sql), gsql))
366
367
end

368
local stmts = {}
369
370

function DataBase:__init()
371
372
373
374
375
376
377
378
379
380
  local conn = self.conn
  self:aexec("BEGIN;")
  self:aexec(SQL_create_tables)
  self:aexec("PRAGMA foreign_keys=ON;")
  local pstmts = {}
  for _, action in ipairs(actions) do
    local sql = buildSQL(action)
    local res, errcode = conn:prepare(sql)
    if not res then 
      assert(herror(errcode, gsubSQL(sql)))
381
    end
382
383
384
    local key = action.name
    pstmts[key] = res
    stmts[key] = sql
385
  end
386
387
  self:aexec("COMMIT;")
  self.pstmts = pstmts
388
389
end

390
391
392
393
394
395
396
397
398
function DataBase:exec(stmt)
  local gsql = gsubSQL(stmt)
  log:database(stmt)
  local res, errmsg = herror(self.conn:exec(stmt))
  errmsg = gsql.." "..emsgprefix..tostring(errcode)
  if errcode == lsqlite.DONE then      
    return true
  elseif errcode == lsqlite.ERROR then
    return nil, errmsg.."; "..self.conn:errmsg()
399
  end
400
  return nil, errmsg
401
402
end

403
404
405
406
407
408
function DataBase:pexec(action, ...)
  local pstmt = self.pstmts[action]
  local sql = gsubSQL(stmts[action]).." "
  local res, errmsg = herror(pstmt:bind_values(...))
  if not res then
    return nil, sql..errmsg
409
  end
410
411
412
413
414
415
416
417
418
419
420
421
422
  local errcode = pstmt:step()
  log:database(sql.." with values {"
  		  ..table.concat({...}, ", ").."}")
  errmsg = sql..emsgprefix..tostring(errcode)
  if errcode == lsqlite.DONE then
    pstmt:reset()
    return true, errcode
  elseif errcode == lsqlite.ROW then
    return true, errcode
  elseif errcode == lsqlite.ERROR then
    errmsg = errmsg.."; "..self.conn:errmsg()
  end  
  return nil, errmsg
423
424
425
426
427
end

local module = {}

function module.open(path)
428
429
430
  local conn, errcode, errmsg = lsqlite.open(path)
  if not conn then return herror(errcode, errmsg) end
  return DataBase{ conn = conn }
431
432
433
end

return module