Class: Llmemory::LongTerm::FileBased::Storages::DatabaseStorage

Inherits:
Base
  • Object
show all
Includes:
Crypto::FieldHelpers
Defined in:
lib/llmemory/long_term/file_based/storages/database_storage.rb

Instance Method Summary collapse

Constructor Details

#initialize(database_url: nil, cipher: nil) ⇒ DatabaseStorage

Returns a new instance of DatabaseStorage.



15
16
17
18
19
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 15

def initialize(database_url: nil, cipher: nil)
  @database_url = database_url || Llmemory.configuration.database_url
  @connection = nil
  @cipher = cipher || Llmemory.build_cipher
end

Instance Method Details

#archive_items(user_id, item_ids) ⇒ Object 



172
173
174
175
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 172

def archive_items(user_id, item_ids)
  ensure_tables!
  item_ids.each { |id| conn.exec_params("DELETE FROM llmemory_items WHERE user_id = $1 AND id = $2", [user_id, id]) }
end

#archive_resources(user_id, resource_ids) ⇒ Object 



177
178
179
180
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 177

def archive_resources(user_id, resource_ids)
  ensure_tables!
  resource_ids.each { |id| conn.exec_params("DELETE FROM llmemory_resources WHERE user_id = $1 AND id = $2", [user_id, id]) }
end

#count_items(user_id:) ⇒ Object 



213
214
215
216
217
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 213

def count_items(user_id:)
  ensure_tables!
  result = conn.exec_params("SELECT COUNT(*) AS c FROM llmemory_items WHERE user_id = $1", [user_id])
  result.first["c"].to_i
end

#get_all_items(user_id) ⇒ Object 



122
123
124
125
126
127
128
129
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 122

def get_all_items(user_id)
  ensure_tables!
  rows = conn.exec_params(
    "SELECT id, category, content, source_resource_id, importance, provenance, created_at FROM llmemory_items WHERE user_id = $1 ORDER BY created_at",
    [user_id]
  )
  rows_to_items(rows)
end

#get_all_resources(user_id) ⇒ Object 



131
132
133
134
135
136
137
138
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 131

def get_all_resources(user_id)
  ensure_tables!
  rows = conn.exec_params(
    "SELECT id, text, created_at FROM llmemory_resources WHERE user_id = $1 ORDER BY created_at",
    [user_id]
  )
  rows_to_resources(rows)
end

#get_items_around(user_id, reference, before: 5, after: 5) ⇒ Object 



219
220
221
222
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 219

def get_items_around(user_id, reference, before: 5, after: 5)
  ensure_tables!
  find_around(get_all_items(user_id), reference, before, after)
end

#get_items_older_than(user_id, days:) ⇒ Object 



112
113
114
115
116
117
118
119
120
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 112

def get_items_older_than(user_id, days:)
  ensure_tables!
  cutoff = (Time.now - (days * 86400)).utc.iso8601
  rows = conn.exec_params(
    "SELECT id, category, content, source_resource_id, importance, provenance, created_at FROM llmemory_items WHERE user_id = $1 AND created_at < $2 ORDER BY created_at",
    [user_id, cutoff]
  )
  rows_to_items(rows)
end

#get_items_since(user_id, hours:) ⇒ Object 



140
141
142
143
144
145
146
147
148
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 140

def get_items_since(user_id, hours:)
  ensure_tables!
  cutoff = (Time.now - (hours * 3600)).utc.iso8601
  rows = conn.exec_params(
    "SELECT id, category, content, source_resource_id, importance, provenance, created_at FROM llmemory_items WHERE user_id = $1 AND created_at >= $2 ORDER BY created_at",
    [user_id, cutoff]
  )
  rows_to_items(rows)
end

#get_resources_around(user_id, reference, before: 5, after: 5) ⇒ Object 



224
225
226
227
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 224

def get_resources_around(user_id, reference, before: 5, after: 5)
  ensure_tables!
  find_around(get_all_resources(user_id), reference, before, after)
end

#get_resources_since(user_id, hours:) ⇒ Object 



102
103
104
105
106
107
108
109
110
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 102

def get_resources_since(user_id, hours:)
  ensure_tables!
  cutoff = (Time.now - (hours * 3600)).utc.iso8601
  rows = conn.exec_params(
    "SELECT id, text, created_at FROM llmemory_resources WHERE user_id = $1 AND created_at >= $2 ORDER BY created_at",
    [user_id, cutoff]
  )
  rows_to_resources(rows)
end

#list_categories(user_id) ⇒ Object 



64
65
66
67
68
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 64

def list_categories(user_id)
  ensure_tables!
  conn.exec_params("SELECT category_name FROM llmemory_categories WHERE user_id = $1", [user_id])
    .map { |r| r["category_name"] }
end

#list_items(user_id:, category: nil, limit: nil, offset: nil) ⇒ Object 



198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 198

def list_items(user_id:, category: nil, limit: nil, offset: nil)
  ensure_tables!
  sql = "SELECT id, category, content, source_resource_id, importance, provenance, created_at FROM llmemory_items WHERE user_id = $1"
  params = [user_id]
  if category
    sql += " AND category = $2"
    params << category
  end
  sql += " ORDER BY created_at"
  sql += " LIMIT #{limit.to_i}" if limit && limit.to_i.positive?
  sql += " OFFSET #{offset.to_i}" if offset && offset.to_i.positive?
  rows = params.size == 1 ? conn.exec_params(sql, params) : conn.exec_params(sql, params)
  rows_to_items(rows)
end

#list_resources(user_id:, limit: nil, offset: nil) ⇒ Object 



189
190
191
192
193
194
195
196
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 189

def list_resources(user_id:, limit: nil, offset: nil)
  ensure_tables!
  sql = "SELECT id, text, created_at FROM llmemory_resources WHERE user_id = $1 ORDER BY created_at"
  sql += " LIMIT #{limit.to_i}" if limit && limit.to_i.positive?
  sql += " OFFSET #{offset.to_i}" if offset && offset.to_i.positive?
  rows = conn.exec_params(sql, [user_id])
  rows_to_resources(rows)
end

#list_users ⇒ Object 



182
183
184
185
186
187
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 182

def list_users
  ensure_tables!
  (conn.exec("SELECT DISTINCT user_id FROM llmemory_resources").map { |r| r["user_id"] } +
   conn.exec("SELECT DISTINCT user_id FROM llmemory_items").map { |r| r["user_id"] } +
   conn.exec("SELECT DISTINCT user_id FROM llmemory_categories").map { |r| r["user_id"] }).uniq
end

#load_category(user_id, category_name) ⇒ Object 



41
42
43
44
45
46
47
48
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 41

def load_category(user_id, category_name)
  ensure_tables!
  result = conn.exec_params(
    "SELECT content FROM llmemory_categories WHERE user_id = $1 AND category_name = $2",
    [user_id, category_name]
  )
  result.any? ? dec(result.first["content"].to_s) : ""
end

#replace_items(user_id, ids_to_remove, merged_item) ⇒ Object 



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 150

def replace_items(user_id, ids_to_remove, merged_item)
  ensure_tables!
  ids_to_remove.each do |id|
    conn.exec_params("DELETE FROM llmemory_items WHERE user_id = $1 AND id = $2", [user_id, id])
  end
  created_at = merged_item[:created_at] || Time.now
  created_at = created_at.utc.iso8601 if created_at.respond_to?(:utc)
  id = "item_#{SecureRandom.hex(8)}"
  conn.exec_params(
    "INSERT INTO llmemory_items (id, user_id, category, content, source_resource_id, search_tokens, created_at) VALUES ($1, $2, $3, $4, $5, $6, $7)",
    [
      id,
      user_id,
      merged_item[:category],
      enc(merged_item[:content]),
      merged_item[:source_resource_id],
      search_tokens_for(merged_item[:content]),
      created_at
    ]
  )
end

#save_category(user_id, category_name, content) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 50

def save_category(user_id, category_name, content)
  ensure_tables!
  conn.exec_params(
    <<~SQL,
      INSERT INTO llmemory_categories (user_id, category_name, content, updated_at)
      VALUES ($1, $2, $3, $4)
      ON CONFLICT (user_id, category_name)
      DO UPDATE SET content = $3, updated_at = $4
    SQL
    [user_id, category_name, enc(content), Time.now.utc.iso8601]
  )
  true
end

#save_item(user_id, category:, content:, source_resource_id:, importance: 0.7, provenance: nil) ⇒ Object 



31
32
33
34
35
36
37
38
39
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 31

def save_item(user_id, category:, content:, source_resource_id:, importance: 0.7, provenance: nil)
  ensure_tables!
  id = "item_#{SecureRandom.hex(8)}"
  conn.exec_params(
    "INSERT INTO llmemory_items (id, user_id, category, content, source_resource_id, importance, provenance, search_tokens, created_at) VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb, $8, $9)",
    [id, user_id, category, enc(content), source_resource_id, importance.to_f, provenance_json(provenance), search_tokens_for(content), Time.now.utc.iso8601]
  )
  id
end

#save_resource(user_id, text) ⇒ Object 



21
22
23
24
25
26
27
28
29
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 21

def save_resource(user_id, text)
  ensure_tables!
  id = "res_#{SecureRandom.hex(8)}"
  conn.exec_params(
    "INSERT INTO llmemory_resources (id, user_id, text, search_tokens, created_at) VALUES ($1, $2, $3, $4, $5)",
    [id, user_id, enc(text), search_tokens_for(text), Time.now.utc.iso8601]
  )
  id
end

#search_items(user_id, query) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 70

def search_items(user_id, query)
  ensure_tables!
  tokens = Llmemory::Tokenizer.tokenize(query)
  return get_all_items(user_id) if tokens.empty?

  suffix, params = blind_token_filter("content", query, 2, search_tokens_column: cipher.enabled? ? "search_tokens" : nil)
  rows = conn.exec_params(
    "SELECT id, category, content, source_resource_id, importance, provenance, created_at FROM llmemory_items WHERE user_id = $1#{suffix}",
    [user_id, *params]
  )
  items = rows_to_items(rows)
  return items unless cipher.enabled?

  merge_legacy_search(items, legacy_item_rows(user_id), query, text_key: :content)
end

#search_resources(user_id, query) ⇒ Object 



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 86

def search_resources(user_id, query)
  ensure_tables!
  tokens = Llmemory::Tokenizer.tokenize(query)
  return get_all_resources(user_id) if tokens.empty?

  suffix, params = blind_token_filter("text", query, 2, search_tokens_column: cipher.enabled? ? "search_tokens" : nil)
  rows = conn.exec_params(
    "SELECT id, text, created_at FROM llmemory_resources WHERE user_id = $1#{suffix}",
    [user_id, *params]
  )
  resources = rows_to_resources(rows)
  return resources unless cipher.enabled?

  merge_legacy_search(resources, legacy_resource_rows(user_id), query, text_key: :text)
end