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 



159
160
161
162
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 159

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 



164
165
166
167
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 164

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 



200
201
202
203
204
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 200

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 



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

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 



119
120
121
122
123
124
125
126
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 119

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 



206
207
208
209
210
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 206

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

#get_items_older_than(user_id, days:) ⇒ Object 



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

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 



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

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 



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

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

#get_resources_since(user_id, hours:) ⇒ Object 



90
91
92
93
94
95
96
97
98
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 90

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 



185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 185

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 



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

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 



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

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 



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 138

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, created_at) VALUES ($1, $2, $3, $4, $5, $6)",
    [
      id,
      user_id,
      merged_item[:category],
      enc(merged_item[:content]),
      merged_item[:source_resource_id],
      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, created_at) VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb, $8)",
    [id, user_id, category, enc(content), source_resource_id, importance.to_f, provenance_json(provenance), 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, created_at) VALUES ($1, $2, $3, $4)",
    [id, user_id, enc(text), Time.now.utc.iso8601]
  )
  id
end

#search_items(user_id, query) ⇒ Object 



70
71
72
73
74
75
76
77
78
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 70

def search_items(user_id, query)
  ensure_tables!
  suffix, params = token_filter("content", query, 2)
  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]
  )
  rows_to_items(rows)
end

#search_resources(user_id, query) ⇒ Object 



80
81
82
83
84
85
86
87
88
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 80

def search_resources(user_id, query)
  ensure_tables!
  suffix, params = token_filter("text", query, 2)
  rows = conn.exec_params(
    "SELECT id, text, created_at FROM llmemory_resources WHERE user_id = $1#{suffix}",
    [user_id, *params]
  )
  rows_to_resources(rows)
end