Initial commit.

1 files changed, 200 insertions, 0 deletions

diff --git a/database.go b/database.go
new file mode 100644
index 0000000..044fa08
--- /dev/null
+++ b/database.go
@@ -0,0 +1,200 @@
+package main
+
+import (
+/*
+	"database/sql"
+	"fmt"
+	"strings"
+*/
+)
+
+func (app *App) createTables() error {
+	/**
+	 * Copied from schema.sql
+	 * TODO: Load this from the file itself.
+	 */
+	query := `
+CREATE TABLE IF NOT EXISTS platform (
+	id INT PRIMARY KEY AUTO_INCREMENT,
+	name VARCHAR(255) UNIQUE NOT NULL,
+	url VARCHAR(255) UNIQUE NOT NULL
+) CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS language (
+	id INT PRIMARY KEY AUTO_INCREMENT,
+	name VARCHAR(255) UNIQUE NOT NULL
+) CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS update_period (
+	id INT PRIMARY KEY AUTO_INCREMENT,
+	name VARCHAR(255) UNIQUE NOT NULL
+) CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS owner (
+	id INT PRIMARY KEY AUTO_INCREMENT,
+	name VARCHAR(255) NOT NULL,
+	url VARCHAR(255) NOT NULL UNIQUE,
+	platform INT NOT NULL,
+	CONSTRAINT fk_owner_platform FOREIGN KEY (platform) REFERENCES platform(id)
+) CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS entry (
+	id INT PRIMARY KEY AUTO_INCREMENT,
+	title VARCHAR(255) NOT NULL,
+	synopsis VARCHAR(255) NOT NULL,
+	owner INT NOT NULL,
+	platform INT NOT NULL,
+	url VARCHAR(255) UNIQUE NOT NULL,
+	language INT NOT NULL,
+	stars VARCHAR(255) NOT NULL,
+	update_period INT NOT NULL,
+	created_at TIMESTAMP NOT NULL,
+	CONSTRAINT fk_entry_owner FOREIGN KEY (owner) REFERENCES owner(id),
+	CONSTRAINT fk_entry_platform FOREIGN KEY (platform) REFERENCES platform(id),
+	CONSTRAINT fk_entry_language FOREIGN KEY (language) REFERENCES language(id),
+	CONSTRAINT fk_entry_period FOREIGN KEY (update_period) REFERENCES update_period(id)
+) CHARSET=utf8;
+	`
+
+	_, err := app.DB.Exec(query)
+	if err != nil {
+		return err
+	}
+
+	/**
+	 * Populates database with first entries.
+	 * TODO: Make this customizable.
+	 */
+	init_platform_query := `
+		INSERT IGNORE INTO platform (id, name, url) VALUES (
+			NULL,
+			"Github",
+			"https://github.com/trending"
+		);
+	`
+	_, err = app.DB.Exec(init_platform_query)
+	if err != nil {
+		return err
+	}
+
+	init_language_query := `
+		INSERT IGNORE INTO language (name) VALUES (
+			"Go"
+		),(
+			"PHP"
+		), (
+			"Javascript"
+		), (
+			"CSS"
+		), (
+			"HTML"
+		), (
+			"Java"
+		), (
+			"SQL"
+		), (
+			"Python"
+		);
+	`
+	_, err = app.DB.Exec(init_language_query)
+	if err != nil {
+		return err
+	}
+
+	init_period_query := `
+		INSERT IGNORE INTO update_period (name) VALUES (
+			"Daily"
+		),(
+			"Weekly"
+		), (
+			"Monthly"
+		);
+	`
+	_, err = app.DB.Exec(init_period_query)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func (app *App) SaveEntries(entries []Entry) error {
+	query := `
+		INSERT IGNORE INTO entry (
+			id,
+			title,
+			synopsis,
+			owner,
+			platform,
+			url,
+			language,
+			stars,
+			update_period,
+			created_at
+		) VALUES (
+			NULL,
+			?,
+			?,
+			?,
+			?,
+			?,
+			?,
+			?,
+			?,
+			?,
+		);
+	`
+	stmt, err := app.DB.Prepare(query)
+	if err != nil {
+		Warn(err, "SaveEntries: Preparing query failed")
+		return err
+	}
+	defer stmt.Close()
+
+	for _, e := range entries {
+
+		err = app.SaveOwner(*e.Owner)
+		if err != nil {
+			continue
+		}
+		_, err = stmt.Exec(e.Title, e.Synopsis, e.Owner.ID, e.Platform.ID, e.URL, e.Language.ID, e.Stars, e.UpdatePeriod.ID, app.Now)
+
+		if err != nil {
+			Warn(err, "SaveEntries: Statement execution failed")
+			return err
+		}
+
+	}
+
+	return nil
+}
+
+func (app *App) SaveOwner(owner Owner) error {
+	query := `
+		INSERT IGNORE owner (
+			id,
+			name,
+			url,
+			platform
+		) VALUES (
+			NULL,
+			?,
+			?,
+			?,
+		);`
+
+	stmt, err := app.DB.Prepare(query)
+	if err != nil {
+		Warn(err, "SaveOwner: Preparing query failed")
+		return err
+	}
+	defer stmt.Close()
+
+	_, err = stmt.Exec(owner.Name, owner.URL, owner.Platform.ID)
+	if err != nil {
+		Warn(err, "SaveOwner: Statement execution failed")
+		return err
+	}
+
+	return nil
+}