145 lines
2.6 KiB
Go
145 lines
2.6 KiB
Go
package main
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
|
|
abcex "g.arns.lt/zordsdavini/abcex/v3"
|
|
_ "github.com/mattn/go-sqlite3"
|
|
)
|
|
|
|
var migrations = []string{}
|
|
|
|
func connectDB() (*sql.DB, error) {
|
|
db, err := sql.Open("sqlite3", "./main.db")
|
|
if err != nil {
|
|
return db, err
|
|
}
|
|
|
|
err = checkPreinstall(db)
|
|
if err != nil {
|
|
return db, err
|
|
}
|
|
|
|
return db, nil
|
|
}
|
|
|
|
func checkPreinstall(db *sql.DB) error {
|
|
sqlStmt := `
|
|
CREATE TABLE IF NOT EXISTS Urls (id INTEGER NOT NULL PRIMARY KEY, url TEXT NOT NULL, short_url TEXT NULL);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS url_idx ON Urls (short_url);
|
|
|
|
CREATE TABLE IF NOT EXISTS Stats (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, count INT, comment TEXT);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS stats_idx ON Stats (name);
|
|
INSERT OR IGNORE INTO Stats (name, count) VALUES ('last_migration', 0);
|
|
`
|
|
_, err := db.Exec(sqlStmt)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
err = runMigrations(db)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func runMigrations(db *sql.DB) error {
|
|
lastMigration, err := getStatsCounter(db, "last_migration")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
for i := lastMigration; i < len(migrations); i++ {
|
|
sqlStmt := migrations[i]
|
|
_, err := db.Exec(sqlStmt)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
db.Exec("UPDATE Stats SET count=count+1 WHERE name='last_migration';")
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func getStatsCounter(db *sql.DB, name string) (int, error) {
|
|
stmt, err := db.Prepare("SELECT count FROM Stats WHERE name = ?")
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
defer stmt.Close()
|
|
|
|
var count int
|
|
err = stmt.QueryRow(name).Scan(&count)
|
|
if err != nil {
|
|
return 0, nil
|
|
}
|
|
|
|
return count, nil
|
|
}
|
|
|
|
func getStatsComment(db *sql.DB, name string) (string, error) {
|
|
stmt, err := db.Prepare("SELECT comment FROM Stats WHERE name = ?")
|
|
if err != nil {
|
|
return "", err
|
|
}
|
|
defer stmt.Close()
|
|
|
|
var comment string
|
|
err = stmt.QueryRow(name).Scan(&comment)
|
|
if err != nil {
|
|
return "", nil
|
|
}
|
|
|
|
return comment, nil
|
|
}
|
|
|
|
func getShortUrl(db *sql.DB, url string) string {
|
|
if len(url) == 0 {
|
|
return ""
|
|
}
|
|
|
|
fmt.Println(url)
|
|
res, err := db.Exec(
|
|
"INSERT INTO Urls (url) VALUES (?);",
|
|
url,
|
|
)
|
|
if err != nil {
|
|
return ""
|
|
}
|
|
|
|
lid, err := res.LastInsertId()
|
|
if err != nil {
|
|
return ""
|
|
}
|
|
|
|
shortUrl := abcex.Encode(lid)
|
|
fmt.Println(lid, shortUrl)
|
|
|
|
db.Exec(
|
|
"UPDATE Urls SET short_url=? WHERE id=?",
|
|
shortUrl,
|
|
lid,
|
|
)
|
|
|
|
return shortUrl
|
|
}
|
|
|
|
func getRedirectUrl(db *sql.DB, shortUrl string) string {
|
|
stmt, err := db.Prepare("SELECT url FROM Urls WHERE short_url = ?")
|
|
if err != nil {
|
|
return ""
|
|
}
|
|
defer stmt.Close()
|
|
|
|
var url string
|
|
err = stmt.QueryRow(shortUrl).Scan(&url)
|
|
if err != nil {
|
|
return ""
|
|
}
|
|
|
|
return url
|
|
}
|