database with problem model

4 files changed, 24 insertions, 0 deletions

diff --git a/src/database/sql/delete_problem.sql b/src/database/sql/delete_problem.sql
new file mode 100644
index 0000000..dcf0088
--- /dev/null
+++ b/src/database/sql/delete_problem.sql
@@ -0,0 +1 @@
+DELETE FROM problem WHERE id = ?1;
diff --git a/src/database/sql/fetch_problems.sql b/src/database/sql/fetch_problems.sql
new file mode 100644
index 0000000..1320cda
--- /dev/null
+++ b/src/database/sql/fetch_problems.sql
@@ -0,0 +1 @@
+SELECT * FROM problem;
diff --git a/src/database/sql/initialize.sql b/src/database/sql/initialize.sql
new file mode 100644
index 0000000..3baf4ff
--- /dev/null
+++ b/src/database/sql/initialize.sql
@@ -0,0 +1,21 @@
+CREATE TABLE IF NOT EXISTS problem (
+    id          INTEGER PRIMARY KEY,
+    title       TEXT NOT NULL,
+    description TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS user (
+    id            INTEGER PRIMARY KEY,
+    email         TEXT UNIQUE NOT NULL,
+    username      TEXT NOT NULL,
+    password_hash TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS submission (
+    id         INTEGER PRIMARY KEY,
+    problem_id INTEGER NOT NULL,
+    user_id    INTEGER NOT NULL,
+    language   TEXT NOT NULL,
+    code       TEXT NOT NULL,
+    validated  INTEGER NOT NULL
+);
diff --git a/src/database/sql/insert_problem.sql b/src/database/sql/insert_problem.sql
new file mode 100644
index 0000000..b5997bd
--- /dev/null
+++ b/src/database/sql/insert_problem.sql
@@ -0,0 +1 @@
+INSERT INTO problem (title, description) VALUES (?1, ?2) RETURNING id;