Small improvements regarding the database structure.

1 files changed, 24 insertions, 1 deletions

diff --git a/database.go b/database.go
index fda19f9..9092da2 100644
--- a/database.go
+++ b/database.go
@@ -52,7 +52,30 @@ CREATE TABLE IF NOT EXISTS entry (
 	CONSTRAINT fk_entry_language FOREIGN KEY (language) REFERENCES language(id),
 	CONSTRAINT fk_entry_period FOREIGN KEY (update_period) REFERENCES update_period(id)
 ) CHARSET=utf8;
-	`}
+	`,
+		`
+		CREATE OR REPLACE VIEW view_entry AS
+SELECT
+        entry.id,
+        title,
+        synopsis,
+        owner.name,
+        platform.name AS platform,
+        platform.url AS platform_url,
+        CONCAT( platform.url, entry.url) AS url,
+        language.name AS language,
+        stars,
+        created_at
+FROM entry
+JOIN owner ON
+        entry.owner = owner.id
+JOIN platform on
+        entry.platform = platform.id
+JOIN language
+        ON entry.language = language.id
+ORDER BY created_at DESC;
+	`,
+	}
 
 	for _, query := range queries {
 		_, err := app.DB.Exec(query)