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

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

Instance Method Summary collapse

Constructor Details

#initialize(database_url: nil) ⇒ DatabaseStorage

Returns a new instance of DatabaseStorage.



12
13
14
15
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 12

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

Instance Method Details

#archive_items(user_id, item_ids) ⇒ Object 



155
156
157
158
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 155

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 



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

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 



194
195
196
197
198
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 194

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 



106
107
108
109
110
111
112
113
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 106

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 



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

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 



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

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 



96
97
98
99
100
101
102
103
104
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 96

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 



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

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 



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

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 



86
87
88
89
90
91
92
93
94
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 86

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 



60
61
62
63
64
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 60

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) ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
191
192
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 180

def list_items(user_id:, category: nil, limit: 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?
  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) ⇒ Object 



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

def list_resources(user_id:, limit: 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?
  rows = conn.exec_params(sql, [user_id])
  rows_to_resources(rows)
end

#list_users ⇒ Object 



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

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 



37
38
39
40
41
42
43
44
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 37

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? ? result.first["content"].to_s : ""
end

#replace_items(user_id, ids_to_remove, merged_item) ⇒ Object 



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 134

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],
      merged_item[:content],
      merged_item[:source_resource_id],
      created_at
    ]
  )
end

#save_category(user_id, category_name, content) ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 46

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, content, Time.now.utc.iso8601]
  )
  true
end

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



27
28
29
30
31
32
33
34
35
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 27

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, content, source_resource_id, importance.to_f, provenance ? JSON.generate(provenance) : nil, Time.now.utc.iso8601]
  )
  id
end

#save_resource(user_id, text) ⇒ Object 



17
18
19
20
21
22
23
24
25
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 17

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, text, Time.now.utc.iso8601]
  )
  id
end

#search_items(user_id, query) ⇒ Object 



66
67
68
69
70
71
72
73
74
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 66

def search_items(user_id, query)
  ensure_tables!
  pattern = "%#{conn.escape_string(query.to_s.downcase)}%"
  rows = conn.exec_params(
    "SELECT id, category, content, source_resource_id, importance, provenance, created_at FROM llmemory_items WHERE user_id = $1 AND LOWER(content) LIKE $2",
    [user_id, pattern]
  )
  rows_to_items(rows)
end

#search_resources(user_id, query) ⇒ Object 



76
77
78
79
80
81
82
83
84
 
# File 'lib/llmemory/long_term/file_based/storages/database_storage.rb', line 76

def search_resources(user_id, query)
  ensure_tables!
  pattern = "%#{conn.escape_string(query.to_s.downcase)}%"
  rows = conn.exec_params(
    "SELECT id, text, created_at FROM llmemory_resources WHERE user_id = $1 AND LOWER(text) LIKE $2",
    [user_id, pattern]
  )
  rows_to_resources(rows)
end