diff options
| -rw-r--r-- | database.go | 25 | ||||
| -rw-r--r-- | github.go | 2 |
2 files changed, 25 insertions, 2 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) @@ -27,7 +27,7 @@ func (app *App) ScrapeGithub(platform Platform) []Entry { owner := Owner{} e.ForEach("div > h3", func(i int, e *colly.HTMLElement) { - entry.URL = URL + e.ChildAttr("a", "href") + entry.URL = e.ChildAttr("a", "href") owner.Name = strings.TrimSuffix(e.ChildText("a > span"), " /") owner.Name = strings.TrimSpace(owner.Name) entry.Title = strings.TrimPrefix(e.ChildText("a"), owner.Name+" /") |