revamp database structure

6 files changed, 224 insertions, 61 deletions

diff --git a/Cargo.lock b/Cargo.lock
index 994321a..3b511dd 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -94,10 +94,11 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 
 [[package]]
 name = "chrono"
-version = "0.4.0"
+version = "0.4.1"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 dependencies = [
- "num 0.1.42 (registry+https://github.com/rust-lang/crates.io-index)",
+ "num-integer 0.1.36 (registry+https://github.com/rust-lang/crates.io-index)",
+ "num-traits 0.2.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "serde 1.0.27 (registry+https://github.com/rust-lang/crates.io-index)",
  "time 0.1.39 (registry+https://github.com/rust-lang/crates.io-index)",
 ]
@@ -153,6 +154,7 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 dependencies = [
  "bitflags 1.0.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "byteorder 1.2.1 (registry+https://github.com/rust-lang/crates.io-index)",
+ "chrono 0.4.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "diesel_derives 1.1.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "pq-sys 0.4.4 (registry+https://github.com/rust-lang/crates.io-index)",
 ]
@@ -472,16 +474,6 @@ dependencies = [
 ]
 
 [[package]]
-name = "num"
-version = "0.1.42"
-source = "registry+https://github.com/rust-lang/crates.io-index"
-dependencies = [
- "num-integer 0.1.36 (registry+https://github.com/rust-lang/crates.io-index)",
- "num-iter 0.1.35 (registry+https://github.com/rust-lang/crates.io-index)",
- "num-traits 0.2.0 (registry+https://github.com/rust-lang/crates.io-index)",
-]
-
-[[package]]
 name = "num-integer"
 version = "0.1.36"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -490,15 +482,6 @@ dependencies = [
 ]
 
 [[package]]
-name = "num-iter"
-version = "0.1.35"
-source = "registry+https://github.com/rust-lang/crates.io-index"
-dependencies = [
- "num-integer 0.1.36 (registry+https://github.com/rust-lang/crates.io-index)",
- "num-traits 0.2.0 (registry+https://github.com/rust-lang/crates.io-index)",
-]
-
-[[package]]
 name = "num-traits"
 version = "0.1.43"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -809,7 +792,7 @@ dependencies = [
  "base64 0.9.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "bitflags 1.0.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "byteorder 1.2.1 (registry+https://github.com/rust-lang/crates.io-index)",
- "chrono 0.4.0 (registry+https://github.com/rust-lang/crates.io-index)",
+ "chrono 0.4.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "evzht9h3nznqzwl 0.0.3 (registry+https://github.com/rust-lang/crates.io-index)",
  "flate2 1.0.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "hyper 0.10.13 (registry+https://github.com/rust-lang/crates.io-index)",
@@ -923,7 +906,7 @@ name = "thulani"
 version = "0.1.0"
 dependencies = [
  "cfg-if 0.1.2 (registry+https://github.com/rust-lang/crates.io-index)",
- "chrono 0.4.0 (registry+https://github.com/rust-lang/crates.io-index)",
+ "chrono 0.4.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "ctrlc 3.1.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "diesel 1.1.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "dotenv 0.11.0 (registry+https://github.com/rust-lang/crates.io-index)",
@@ -1093,7 +1076,7 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 "checksum byteorder 1.2.1 (registry+https://github.com/rust-lang/crates.io-index)" = "652805b7e73fada9d85e9a6682a4abd490cb52d96aeecc12e33a0de34dfd0d23"
 "checksum cc 1.0.4 (registry+https://github.com/rust-lang/crates.io-index)" = "deaf9ec656256bb25b404c51ef50097207b9cbb29c933d31f92cae5a8a0ffee0"
 "checksum cfg-if 0.1.2 (registry+https://github.com/rust-lang/crates.io-index)" = "d4c819a1287eb618df47cc647173c5c4c66ba19d888a6e50d605672aed3140de"
-"checksum chrono 0.4.0 (registry+https://github.com/rust-lang/crates.io-index)" = "7c20ebe0b2b08b0aeddba49c609fe7957ba2e33449882cb186a180bc60682fa9"
+"checksum chrono 0.4.1 (registry+https://github.com/rust-lang/crates.io-index)" = "ba5f60682a4c264e7f8d77b82e7788938a76befdf949d4a98026d19099c9d873"
 "checksum colored 1.6.0 (registry+https://github.com/rust-lang/crates.io-index)" = "b0aa3473e85a3161b59845d6096b289bb577874cafeaf75ea1b1beaa6572c7fc"
 "checksum core-foundation 0.2.3 (registry+https://github.com/rust-lang/crates.io-index)" = "25bfd746d203017f7d5cbd31ee5d8e17f94b6521c7af77ece6c9e4b2d4b16c67"
 "checksum core-foundation-sys 0.2.3 (registry+https://github.com/rust-lang/crates.io-index)" = "065a5d7ffdcbc8fa145d6f0746f3555025b9097a9e9cda59f7467abae670c78d"
@@ -1136,9 +1119,7 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 "checksum multipart 0.13.6 (registry+https://github.com/rust-lang/crates.io-index)" = "92f54eb45230c3aa20864ccf0c277eeaeadcf5e437e91731db498dbf7fbe0ec6"
 "checksum native-tls 0.1.5 (registry+https://github.com/rust-lang/crates.io-index)" = "f74dbadc8b43df7864539cedb7bc91345e532fdd913cfdc23ad94f4d2d40fbc0"
 "checksum nix 0.9.0 (registry+https://github.com/rust-lang/crates.io-index)" = "a2c5afeb0198ec7be8569d666644b574345aad2e95a53baf3a532da3e0f3fb32"
-"checksum num 0.1.42 (registry+https://github.com/rust-lang/crates.io-index)" = "4703ad64153382334aa8db57c637364c322d3372e097840c72000dabdcf6156e"
 "checksum num-integer 0.1.36 (registry+https://github.com/rust-lang/crates.io-index)" = "f8d26da319fb45674985c78f1d1caf99aa4941f785d384a2ae36d0740bc3e2fe"
-"checksum num-iter 0.1.35 (registry+https://github.com/rust-lang/crates.io-index)" = "4b226df12c5a59b63569dd57fafb926d91b385dfce33d8074a412411b689d593"
 "checksum num-traits 0.1.43 (registry+https://github.com/rust-lang/crates.io-index)" = "92e5113e9fd4cc14ded8e499429f396a20f98c772a47cc8622a736e1ec843c31"
 "checksum num-traits 0.2.0 (registry+https://github.com/rust-lang/crates.io-index)" = "e7de20f146db9d920c45ee8ed8f71681fd9ade71909b48c3acbd766aa504cf10"
 "checksum num_cpus 1.8.0 (registry+https://github.com/rust-lang/crates.io-index)" = "c51a3322e4bca9d212ad9a158a02abc6934d005490c054a2778df73a70aa0a30"
diff --git a/Cargo.toml b/Cargo.toml
index 62eacbb..9444eb0 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -14,9 +14,9 @@ typemap = "0.3.3"
 url = "1.6.0"
 dotenv = "0.11.0"
 dotenv_codegen = "0.11.0"
-chrono = "0.4"
+chrono = "0.4.1"
 fern = { version = "0.5", features = ["colored"] }
-diesel = { version = "1.0.0", features = ["postgres"], optional = true }
+diesel = { version = "1.0.0", features = ["postgres", "chrono"], optional = true }
 cfg-if = "0.1"
 ctrlc = { version = "3.0", features = ["termination"] }
 
diff --git a/migrations/2018-02-15-031841_create_memes/down.sql b/migrations/2018-02-15-031841_create_memes/down.sql
index 8de6d95..91d88ab 100644
--- a/migrations/2018-02-15-031841_create_memes/down.sql
+++ b/migrations/2018-02-15-031841_create_memes/down.sql
@@ -1,3 +1,18 @@
-DROP TABLE text_memes;

-DROP TABLE image_memes;

 DROP TABLE audio_memes;

+DROP TABLE image_memes;

+DROP TABLE text_memes;

+

+DROP TABLE audio;

+DROP TABLE images;

+

+DROP INDEX audit_updated;

+DROP INDEX audit_updated_by;

+DROP INDEX audit_metadata;

+DROP INDEX audit_metadata_updated_by;

+

+DROP TABLE audit_records;

+

+DROP INDEX metadata_created;

+DROP INDEX metadata_created_by;

+

+DROP TABLE metadata;

diff --git a/migrations/2018-02-15-031841_create_memes/up.sql b/migrations/2018-02-15-031841_create_memes/up.sql
index 38097e7..dc24710 100644
--- a/migrations/2018-02-15-031841_create_memes/up.sql
+++ b/migrations/2018-02-15-031841_create_memes/up.sql
@@ -1,14 +1,73 @@
+CREATE TABLE metadata (

+    id          SERIAL PRIMARY KEY,

+

+    created     TIMESTAMP NOT NULL DEFAULT current_timestamp,

+    created_by  BIGINT NOT NULL

+);

+

+CREATE INDEX metadata_created on metadata (created);

+CREATE INDEX metadata_created_by on metadata (created_by);

+

+

+CREATE TABLE audit_records (

+    id          SERIAL PRIMARY KEY,

+

+    updated     TIMESTAMP NOT NULL DEFAULT current_timestamp,

+    updated_by  BIGINT NOT NULL,

+

+    metadata_id INTEGER REFERENCES metadata NOT NULL

+);

+

+CREATE INDEX audit_updated on audit_records (updated);

+CREATE INDEX audit_updated_by on audit_records (updated_by);

+CREATE INDEX audit_metadata on audit_records (metadata_id);

+

+CREATE INDEX audit_metadata_updated_by on audit_records (metadata_id, updated_by);

+

+

+CREATE TABLE images (

+    id    SERIAL PRIMARY KEY,

+    data  bytea NOT NULL,

+

+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL

+);

+

+

+CREATE TABLE audio (

+    id    SERIAL PRIMARY KEY,

+    data  bytea NOT NULL,

+

+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL

+);

+

+

 CREATE TABLE text_memes (

     id          SERIAL PRIMARY KEY,

     title       varchar UNIQUE NOT NULL,

     content     TEXT NOT NULL,

-    pic_related varchar NULL,

-    UNIQUE(content, pic_related)

+    image_id    INTEGER REFERENCES images NULL,

+    audio_id    INTEGER REFERENCES audio NULL,

+

+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL,

+    UNIQUE(content, image_id, audio_id)

 );

 

-CREATE TABLE audio_memes (

-    id      SERIAL PRIMARY KEY,

-    title   varchar UNIQUE NOT NULL,

-    link    varchar UNIQUE NOT NULL

+

+CREATE TABLE image_memes (

+    id          SERIAL PRIMARY KEY,

+    title       varchar UNIQUE NOT NULL,

+    image_id    INTEGER REFERENCES images NOT NULL,

+

+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL,

+    UNIQUE(title, image_id)

 );

 

+

+CREATE TABLE audio_memes (

+    id          SERIAL PRIMARY KEY,

+    title       varchar UNIQUE NOT NULL,

+    audio_id    INTEGER REFERENCES audio NOT NULL,

+

+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL,

+    UNIQUE(title, audio_id)

+);

diff --git a/src/db/models.rs b/src/db/models.rs
index 899fa1e..c06c41e 100644
--- a/src/db/models.rs
+++ b/src/db/models.rs
@@ -1,18 +1,65 @@
 use super::schema::*;

+use chrono::naive::NaiveDateTime;

 

-#[derive(Insertable, Queryable, Identifiable, AsChangeset)]

+#[derive(Insertable, Queryable, Identifiable, AsChangeset, Debug, Associations)]

+#[belongs_to(Audio)]

+#[belongs_to(Image)]

+#[belongs_to(TextMeme)]

+#[belongs_to(ImageMeme)]

+#[belongs_to(TextMeme)]

+#[table_name="metadata"]

+pub struct Metadata {

+    pub id: i32,

+    pub created: NaiveDateTime,

+    pub created_by: i64,

+}

+

+#[derive(Insertable, Queryable, Identifiable, PartialEq, AsChangeset, Debug, Associations)]

+#[belongs_to(AudioMeme)]

+#[belongs_to(TextMeme)]

+#[table_name="audio"]

+pub struct Audio {

+    pub id: i32,

+    pub data: Vec<u8>,

+    pub metadata_id: i32,

+}

+

+#[derive(Insertable, Queryable, Identifiable, PartialEq, AsChangeset, Debug, Associations)]

+#[belongs_to(ImageMeme)]

+#[belongs_to(TextMeme)]

+#[table_name="images"]

+pub struct Image {

+    pub id: i32,

+    pub data: Vec<u8>,

+    pub metadata_id: i32,

+}

+

+#[derive(Insertable, Queryable, Identifiable, PartialEq, AsChangeset, Debug)]

 #[table_name="audio_memes"]

 pub struct AudioMeme {

     pub id: i32,

     pub title: String,

-    pub link: String,

+    pub audio_id: i32,

+    pub metadata_id: i32,

 }

 

-#[derive(Insertable, Queryable, Identifiable, AsChangeset)]

+#[derive(Insertable, Queryable, Identifiable, PartialEq, AsChangeset, Debug)]

 #[table_name="text_memes"]

 pub struct TextMeme {

     pub id: i32,

     pub title: String,

     pub content: String,

-    pub pic_related: String,

+    pub image_id: Option<i32>,

+    pub audio_id: Option<i32>,

+    pub metadata_id: i32,

+}

+

+#[derive(Insertable, Queryable, Identifiable, PartialEq, AsChangeset, Debug, Associations)]

+#[belongs_to(Metadata)]

+#[table_name="audit_records"]

+pub struct AuditRecord {

+    pub id: i32,

+    pub updated: NaiveDateTime,

+    pub updated_by: i64,

+    pub metadata_id: i32,

 }

diff --git a/src/db/schema.rs b/src/db/schema.rs
index b29a1ca..40891a5 100644
--- a/src/db/schema.rs
+++ b/src/db/schema.rs
@@ -1,21 +1,82 @@
-table! {

-    audio_memes (id) {

-        id -> Int4,

-        title -> Varchar,

-        link -> Varchar,

-    }

-}

-

-table! {

-    text_memes (id) {

-        id -> Int4,

-        title -> Varchar,

-        content -> Text,

-        pic_related -> Varchar,

-    }

-}

-

-allow_tables_to_appear_in_same_query!(

-    audio_memes,

-    text_memes,

-);

+table! {
+    audio (id) {
+        id -> Int4,
+        data -> Bytea,
+        metadata_id -> Int4,
+    }
+}
+
+table! {
+    audio_memes (id) {
+        id -> Int4,
+        title -> Varchar,
+        audio_id -> Int4,
+        metadata_id -> Int4,
+    }
+}
+
+table! {
+    audit_records (id) {
+        id -> Int4,
+        updated -> Timestamp,
+        updated_by -> Int8,
+        metadata_id -> Int4,
+    }
+}
+
+table! {
+    image_memes (id) {
+        id -> Int4,
+        title -> Varchar,
+        image_id -> Int4,
+        metadata_id -> Int4,
+    }
+}
+
+table! {
+    images (id) {
+        id -> Int4,
+        data -> Bytea,
+        metadata_id -> Int4,
+    }
+}
+
+table! {
+    metadata (id) {
+        id -> Int4,
+        created -> Timestamp,
+        created_by -> Int8,
+    }
+}
+
+table! {
+    text_memes (id) {
+        id -> Int4,
+        title -> Varchar,
+        content -> Text,
+        image_id -> Nullable<Int4>,
+        audio_id -> Nullable<Int4>,
+        metadata_id -> Int4,
+    }
+}
+
+joinable!(audio -> metadata (metadata_id));
+joinable!(audio_memes -> audio (audio_id));
+joinable!(audio_memes -> metadata (metadata_id));
+joinable!(audit_records -> metadata (metadata_id));
+joinable!(image_memes -> images (image_id));
+joinable!(image_memes -> metadata (metadata_id));
+joinable!(images -> metadata (metadata_id));
+joinable!(text_memes -> audio (audio_id));
+joinable!(text_memes -> images (image_id));
+joinable!(text_memes -> metadata (metadata_id));
+
+allow_tables_to_appear_in_same_query!(
+    audio,
+    audio_memes,
+    audit_records,
+    image_memes,
+    images,
+    metadata,
+    text_memes,
+);