From f6dfb1a3d06b75ef74cfdc295bd7f30390d2a4d8 Mon Sep 17 00:00:00 2001 From: Alexander_Kabui Date: Fri, 24 May 2024 18:38:18 +0300 Subject: sql: update: llm_db_update.sql: New file. --- sql/update/llm_db_update.sql | 37 +++++++++++++++++++++++++++++++++++++ 1 file changed, 37 insertions(+) create mode 100644 sql/update/llm_db_update.sql (limited to 'sql') diff --git a/sql/update/llm_db_update.sql b/sql/update/llm_db_update.sql new file mode 100644 index 0000000..71f7491 --- /dev/null +++ b/sql/update/llm_db_update.sql @@ -0,0 +1,37 @@ +-- llm_db_update.sql --- + +-- Copyright (C) 2022 Alexander kabui + +-- Author: Alexander Kabui + +-- This program is free software; you can redistribute it and/or +-- modify it under the terms of the GNU General Public License +-- as published by the Free Software Foundation; either version 3 +-- of the License, or (at your option) any later version. + +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. + +-- You should have received a copy of the GNU General Public License +-- along with this program. If not, see . + +-- Sql file to create the history table, adding indexing for the history table +-- and adding timestamp column the Rating table + + +CREATE TABLE IF NOT EXISTS history ( + user_id TEXT NOT NULL, + task_id TEXT NOT NULL, + query TEXT NOT NULL, + results TEXT, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (task_id) +) WITHOUT ROWID; + + +CREATE INDEX IF NOT EXISTS idx_tbl_history_cols_task_id_user_id +ON history (task_id, user_id); + +ALTER TABLE Rating ADD COLUMN created_at TIMESTAMP; -- cgit v1.2.3 From 666461bcf6afc811e4c21dd23dbef2711a07049a Mon Sep 17 00:00:00 2001 From: Alexander_Kabui Date: Fri, 24 May 2024 18:55:49 +0300 Subject: Update copyright year and email. --- sql/update/llm_db_update.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql') diff --git a/sql/update/llm_db_update.sql b/sql/update/llm_db_update.sql index 71f7491..6608a90 100644 --- a/sql/update/llm_db_update.sql +++ b/sql/update/llm_db_update.sql @@ -1,6 +1,6 @@ -- llm_db_update.sql --- --- Copyright (C) 2022 Alexander kabui +-- Copyright (C) 2024 Alexander kabui -- Author: Alexander Kabui -- cgit v1.2.3 From a45ab3e62df6e1dfcc6fff03916369fbdaf68ab8 Mon Sep 17 00:00:00 2001 From: Alexander_Kabui Date: Thu, 29 Aug 2024 11:05:39 +0300 Subject: Add default timestamp and and primary key for Rating table. --- gn3/api/llm.py | 4 ++-- sql/update/llm_db_update.sql | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'sql') diff --git a/gn3/api/llm.py b/gn3/api/llm.py index 5901ef5..952a5b9 100644 --- a/gn3/api/llm.py +++ b/gn3/api/llm.py @@ -71,8 +71,8 @@ def rate_queries(task_id): answer TEXT NOT NULL, weight INTEGER NOT NULL DEFAULT 0, task_id TEXT NOT NULL UNIQUE, - created_at TIMESTAMP - )""" + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(task_id))""" cursor.execute(create_table) cursor.execute("""INSERT INTO Rating(user_id, query, answer, weight, task_id, created_at) diff --git a/sql/update/llm_db_update.sql b/sql/update/llm_db_update.sql index 6608a90..a4eb848 100644 --- a/sql/update/llm_db_update.sql +++ b/sql/update/llm_db_update.sql @@ -34,4 +34,4 @@ CREATE TABLE IF NOT EXISTS history ( CREATE INDEX IF NOT EXISTS idx_tbl_history_cols_task_id_user_id ON history (task_id, user_id); -ALTER TABLE Rating ADD COLUMN created_at TIMESTAMP; +ALTER TABLE Rating ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP; -- cgit v1.2.3 From 57b4a4fd5bcb8a2b7f9af856d8f1212c0fbbe0da Mon Sep 17 00:00:00 2001 From: Alexander_Kabui Date: Thu, 29 Aug 2024 11:06:07 +0300 Subject: Add sql file for creating llm db tables. --- sql/update/llm_db_tables.sql | 47 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) create mode 100644 sql/update/llm_db_tables.sql (limited to 'sql') diff --git a/sql/update/llm_db_tables.sql b/sql/update/llm_db_tables.sql new file mode 100644 index 0000000..a6c0479 --- /dev/null +++ b/sql/update/llm_db_tables.sql @@ -0,0 +1,47 @@ +-- llm_db_update.sql --- + +-- Copyright (C) 2024 Alexander kabui + +-- Author: Alexander Kabui + +-- This program is free software; you can redistribute it and/or +-- modify it under the terms of the GNU General Public License +-- as published by the Free Software Foundation; either version 3 +-- of the License, or (at your option) any later version. + +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. + +-- You should have received a copy of the GNU General Public License +-- along with this program. If not, see . + +-- Sql file to create the tables for history rating and adding indexing for the history table +-- this targets setting up a new db +-- and adding timestamp column the Rating table + + +CREATE TABLE IF NOT EXISTS history ( + user_id TEXT NOT NULL, + task_id TEXT NOT NULL, + query TEXT NOT NULL, + results TEXT, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (task_id) +) WITHOUT ROWID; + + +CREATE INDEX IF NOT EXISTS idx_tbl_history_cols_task_id_user_id +ON history (task_id, user_id); + + + +CREATE TABLE IF NOT EXISTS Rating( + user_id TEXT NOT NULL, + query TEXT NOT NULL, + answer TEXT NOT NULL, + weight INTEGER NOT NULL DEFAULT 0, + task_id TEXT NOT NULL UNIQUE, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (task_id)); -- cgit v1.2.3 From c34d75a29b7d144030542b9de4fd0e0d614303a9 Mon Sep 17 00:00:00 2001 From: Alexander_Kabui Date: Tue, 3 Sep 2024 11:08:31 +0300 Subject: Use Jsonb for storing results. --- gn3/api/llm.py | 2 +- sql/update/llm_db_tables.sql | 2 +- sql/update/llm_db_update.sql | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'sql') diff --git a/gn3/api/llm.py b/gn3/api/llm.py index 8d17bc2..9ee4a79 100644 --- a/gn3/api/llm.py +++ b/gn3/api/llm.py @@ -40,7 +40,7 @@ def search(): history(user_id TEXT NOT NULL, task_id TEXT NOT NULL, query TEXT NOT NULL, - results TEXT, + results JSONB, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(task_id)) WITHOUT ROWID""") cursor.execute( diff --git a/sql/update/llm_db_tables.sql b/sql/update/llm_db_tables.sql index a6c0479..b501832 100644 --- a/sql/update/llm_db_tables.sql +++ b/sql/update/llm_db_tables.sql @@ -26,7 +26,7 @@ CREATE TABLE IF NOT EXISTS history ( user_id TEXT NOT NULL, task_id TEXT NOT NULL, query TEXT NOT NULL, - results TEXT, + results JSONB, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (task_id) ) WITHOUT ROWID; diff --git a/sql/update/llm_db_update.sql b/sql/update/llm_db_update.sql index a4eb848..7f1a9f9 100644 --- a/sql/update/llm_db_update.sql +++ b/sql/update/llm_db_update.sql @@ -25,7 +25,7 @@ CREATE TABLE IF NOT EXISTS history ( user_id TEXT NOT NULL, task_id TEXT NOT NULL, query TEXT NOT NULL, - results TEXT, + results JSONB, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (task_id) ) WITHOUT ROWID; -- cgit v1.2.3