diff options
| -rw-r--r-- | database.go | 4 | ||||
| -rw-r--r-- | schema.sql | 21 |
2 files changed, 23 insertions, 2 deletions
diff --git a/database.go b/database.go index c6434ff..99fa2a7 100644 --- a/database.go +++ b/database.go @@ -54,12 +54,12 @@ CREATE TABLE IF NOT EXISTS entry ( ) CHARSET=utf8; `, ` - CREATE OR REPLACE VIEW view_entry AS + CREATE OR REPLACE VIEW entry_view AS SELECT entry.id, title, synopsis, - owner.name, + owner.name AS owner, platform.name AS platform, platform.url AS platform_url, CONCAT( platform.url, entry.url) AS url, @@ -38,3 +38,24 @@ 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 entry_view AS +SELECT + entry.id, + title, + synopsis, + owner.name AS owner, + 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; |