Class: KBS::Blackboard::Persistence::SqliteStore

Inherits:
Store
  • Object
show all
Defined in:
lib/kbs/blackboard/persistence/sqlite_store.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_path: ':memory:', session_id: nil) ⇒ SqliteStore

Returns a new instance of SqliteStore.



13
14
15
16
17
18
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 13

def initialize(db_path: ':memory:', session_id: nil)
  @db_path = db_path
  @session_id = session_id
  @transaction_depth = 0
  setup_database
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



11
12
13
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 11

def db
  @db
end

#db_path ⇒ Object (readonly)

Returns the value of attribute db_path.



11
12
13
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 11

def db_path
  @db_path
end

#session_id ⇒ Object (readonly)

Returns the value of attribute session_id.



11
12
13
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 11

def session_id
  @session_id
end

Instance Method Details

#add_fact(uuid, type, attributes) ⇒ Object 



78
79
80
81
82
83
84
85
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 78

def add_fact(uuid, type, attributes)
  attributes_json = JSON.generate(attributes)

  @db.execute(
    "INSERT INTO facts (uuid, fact_type, attributes, session_id) VALUES (?, ?, ?, ?)",
    [uuid, type.to_s, attributes_json, @session_id]
  )
end

#clear_session(session_id) ⇒ Object 



180
181
182
183
184
185
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 180

def clear_session(session_id)
  @db.execute(
    "UPDATE facts SET retracted = 1, retracted_at = CURRENT_TIMESTAMP WHERE session_id = ?",
    [session_id]
  )
end

#close ⇒ Object 



216
217
218
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 216

def close
  @db.close if @db
end

#create_indexes ⇒ Object 



57
58
59
60
61
62
63
64
65
66
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 57

def create_indexes
  @db.execute_batch "    CREATE INDEX IF NOT EXISTS idx_facts_type ON facts(fact_type);\n    CREATE INDEX IF NOT EXISTS idx_facts_session ON facts(session_id);\n    CREATE INDEX IF NOT EXISTS idx_facts_retracted ON facts(retracted);\n    CREATE INDEX IF NOT EXISTS idx_facts_timestamp ON facts(fact_timestamp);\n    CREATE INDEX IF NOT EXISTS idx_facts_market_timestamp ON facts(market_timestamp);\n    CREATE INDEX IF NOT EXISTS idx_facts_market_session ON facts(market_session);\n  SQL\nend\n"

#create_tables ⇒ Object 



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
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 28

def create_tables
  @db.execute_batch "    CREATE TABLE IF NOT EXISTS facts (\n      id INTEGER PRIMARY KEY AUTOINCREMENT,\n      uuid TEXT UNIQUE NOT NULL,\n      fact_type TEXT NOT NULL,\n      attributes TEXT NOT NULL,\n      fact_timestamp TIMESTAMP,\n      market_timestamp TIMESTAMP,\n      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n      session_id TEXT,\n      retracted BOOLEAN DEFAULT 0,\n      retracted_at TIMESTAMP,\n      data_source TEXT,\n      market_session TEXT\n    );\n\n    CREATE TABLE IF NOT EXISTS knowledge_sources (\n      id INTEGER PRIMARY KEY AUTOINCREMENT,\n      name TEXT UNIQUE NOT NULL,\n      description TEXT,\n      topics TEXT,\n      active BOOLEAN DEFAULT 1,\n      registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n    );\n  SQL\nend\n"

#get_fact(uuid) ⇒ Object 



117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 117

def get_fact(uuid)
  result = @db.get_first_row(
    "SELECT * FROM facts WHERE uuid = ? AND retracted = 0",
    [uuid]
  )

  if result
    {
      uuid: result['uuid'],
      type: result['fact_type'].to_sym,
      attributes: JSON.parse(result['attributes'], symbolize_names: true)
    }
  end
end

#get_facts(type = nil, pattern = {}) ⇒ Object 



132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 132

def get_facts(type = nil, pattern = {})
  query = "SELECT * FROM facts WHERE retracted = 0"
  params = []

  if type
    query += " AND fact_type = ?"
    params << type.to_s
  end

  results = @db.execute(query, params)

  results.map do |row|
    attributes = JSON.parse(row['attributes'], symbolize_names: true)

    if matches_pattern?(attributes, pattern)
      {
        uuid: row['uuid'],
        type: row['fact_type'].to_sym,
        attributes: attributes
      }
    end
  end.compact
end

#query_facts(sql_conditions = nil, params = []) ⇒ Object 



156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 156

def query_facts(sql_conditions = nil, params = [])
  query = "SELECT * FROM facts WHERE retracted = 0"
  query += " AND #{sql_conditions}" if sql_conditions

  results = @db.execute(query, params)

  results.map do |row|
    {
      uuid: row['uuid'],
      type: row['fact_type'].to_sym,
      attributes: JSON.parse(row['attributes'], symbolize_names: true)
    }
  end
end

#register_knowledge_source(name, description: nil, topics: []) ⇒ Object 



171
172
173
174
175
176
177
178
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 171

def register_knowledge_source(name, description: nil, topics: [])
  topics_json = JSON.generate(topics)

  @db.execute(
    "INSERT OR REPLACE INTO knowledge_sources (name, description, topics) VALUES (?, ?, ?)",
    [name, description, topics_json]
  )
end

#remove_fact(uuid) ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 87

def remove_fact(uuid)
  result = @db.get_first_row(
    "SELECT fact_type, attributes FROM facts WHERE uuid = ? AND retracted = 0",
    [uuid]
  )

  if result
    @db.execute(
      "UPDATE facts SET retracted = 1, retracted_at = CURRENT_TIMESTAMP WHERE uuid = ?",
      [uuid]
    )

    {
      type: result['fact_type'].to_sym,
      attributes: JSON.parse(result['attributes'], symbolize_names: true)
    }
  end
end

#setup_database ⇒ Object 



20
21
22
23
24
25
26
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 20

def setup_database
  @db = SQLite3::Database.new(@db_path)
  @db.results_as_hash = true
  create_tables
  create_indexes
  setup_triggers
end

#setup_triggers ⇒ Object 



68
69
70
71
72
73
74
75
76
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 68

def setup_triggers
  @db.execute_batch "    CREATE TRIGGER IF NOT EXISTS update_fact_timestamp\n    AFTER UPDATE ON facts\n    BEGIN\n      UPDATE facts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;\n    END;\n  SQL\nend\n"

#stats ⇒ Object 



191
192
193
194
195
196
197
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 191

def stats
  {
    total_facts: @db.get_first_value("SELECT COUNT(*) FROM facts"),
    active_facts: @db.get_first_value("SELECT COUNT(*) FROM facts WHERE retracted = 0"),
    knowledge_sources: @db.get_first_value("SELECT COUNT(*) FROM knowledge_sources WHERE active = 1")
  }
end

#transaction(&block) ⇒ Object 



199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 199

def transaction(&block)
  @transaction_depth += 1
  result = nil
  begin
    if @transaction_depth == 1
      @db.transaction do
        result = yield
      end
    else
      result = yield
    end
  ensure
    @transaction_depth -= 1
  end
  result
end

#update_fact(uuid, attributes) ⇒ Object 



106
107
108
109
110
111
112
113
114
115
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 106

def update_fact(uuid, attributes)
  attributes_json = JSON.generate(attributes)

  @db.execute(
    "UPDATE facts SET attributes = ? WHERE uuid = ? AND retracted = 0",
    [attributes_json, uuid]
  )

  get_fact_type(uuid)
end

#vacuum ⇒ Object 



187
188
189
 
# File 'lib/kbs/blackboard/persistence/sqlite_store.rb', line 187

def vacuum
  @db.execute("VACUUM")
end