switch to sqlite

7 files changed, 344 insertions, 9 deletions

diff --git a/internal/builder/builder.go b/internal/builder/builder.go
index 68f970f..266ce56 100644
--- a/internal/builder/builder.go
+++ b/internal/builder/builder.go
@@ -16,7 +16,7 @@ import (
 	"go.alanpearce.eu/website/internal/content"
 	"go.alanpearce.eu/website/internal/sitemap"
 	"go.alanpearce.eu/website/internal/storage"
-	"go.alanpearce.eu/website/internal/storage/files"
+	"go.alanpearce.eu/website/internal/storage/sqlite"
 	"go.alanpearce.eu/website/templates"
 	"go.alanpearce.eu/x/log"
 
@@ -27,6 +27,7 @@ import (
 type IOConfig struct {
 	Source      string `conf:"default:.,short:s,flag:src"`
 	Destination string `conf:"default:public,short:d,flag:dest"`
+	DBPath      string `conf:"default:site.db,flag:db"`
 	Development bool   `conf:"default:false,flag:dev"`
 }
 
@@ -267,11 +268,12 @@ func BuildSite(ioConfig *IOConfig, cfg *config.Config, log *log.Logger) (*Result
 	templates.Setup()
 	loadCSS(ioConfig.Source)
 
-	storage, err := files.NewWriter(ioConfig.Destination, log, &files.Options{
+	var storage storage.Writer
+	storage, err := sqlite.NewWriter(ioConfig.DBPath, log, &sqlite.Options{
 		Compress: !ioConfig.Development,
 	})
 	if err != nil {
-		return nil, errors.WithMessage(err, "could not create storage writer")
+		return nil, errors.WithMessage(err, "could not create storage")
 	}
 
 	return build(storage, ioConfig, cfg, log)
diff --git a/internal/server/server.go b/internal/server/server.go
index e4ff63b..3c26a7f 100644
--- a/internal/server/server.go
+++ b/internal/server/server.go
@@ -16,7 +16,7 @@ import (
 
 	"go.alanpearce.eu/website/internal/builder"
 	cfg "go.alanpearce.eu/website/internal/config"
-	"go.alanpearce.eu/website/internal/storage/files"
+	"go.alanpearce.eu/website/internal/storage/sqlite"
 	"go.alanpearce.eu/website/internal/vcs"
 	"go.alanpearce.eu/website/internal/website"
 	"go.alanpearce.eu/x/log"
@@ -33,6 +33,7 @@ var (
 )
 
 type Config struct {
+	DBPath        string `conf:"default:site.db"`
 	Root          string `conf:"default:public"`
 	Redirect      bool   `conf:"default:true"`
 	ListenAddress string `conf:"default:localhost"`
@@ -177,9 +178,9 @@ func New(runtimeConfig *Config, log *log.Logger) (*Server, error) {
 	loggingMux := http.NewServeMux()
 
 	log.Debug("registering content files", "root", runtimeConfig.Root)
-	reader, err := files.NewReader(runtimeConfig.Root, log.Named("files"))
+	reader, err := sqlite.NewReader(runtimeConfig.DBPath, log.Named("sqlite"))
 	if err != nil {
-		return nil, errors.WithMessage(err, "could not create file reader")
+		return nil, errors.WithMessage(err, "could not create sqlite reader")
 	}
 
 	mux, err := website.NewMux(config, reader, log.Named("website"))
diff --git a/internal/storage/interface.go b/internal/storage/interface.go
index c167a49..282a33b 100644
--- a/internal/storage/interface.go
+++ b/internal/storage/interface.go
@@ -6,7 +6,7 @@ import (
 
 type Reader interface {
 	GetFile(path string) (*File, error)
-	CanonicalisePath(path string) (cPath string, differs bool)
+	CanonicalisePath(path string) (string, bool)
 }
 
 type Writer interface {
diff --git a/internal/storage/sqlite/file.go b/internal/storage/sqlite/file.go
new file mode 100644
index 0000000..328d538
--- /dev/null
+++ b/internal/storage/sqlite/file.go
@@ -0,0 +1,20 @@
+package sqlite
+
+import (
+	"strings"
+)
+
+func pathNameToFileName(pathname string) string {
+	if strings.HasSuffix(pathname, "/") {
+		pathname = pathname + "index.html"
+	}
+
+	return pathname
+}
+
+func fileNameToPathName(filename string) string {
+	pathname := strings.TrimSuffix(filename, "index.html")
+	pathname = strings.TrimPrefix(pathname, "/")
+
+	return "/" + pathname
+}
diff --git a/internal/storage/sqlite/reader.go b/internal/storage/sqlite/reader.go
new file mode 100644
index 0000000..fe5da7e
--- /dev/null
+++ b/internal/storage/sqlite/reader.go
@@ -0,0 +1,99 @@
+package sqlite
+
+import (
+	"database/sql"
+	"strings"
+	"time"
+
+	"gitlab.com/tozd/go/errors"
+	"go.alanpearce.eu/website/internal/buffer"
+	"go.alanpearce.eu/website/internal/storage"
+	"go.alanpearce.eu/x/log"
+)
+
+type Reader struct {
+	db      *sql.DB
+	log     *log.Logger
+	queries struct {
+		getFile *sql.Stmt
+	}
+}
+
+func NewReader(dbPath string, log *log.Logger) (r *Reader, err error) {
+	db, err := openDB(dbPath)
+	if err != nil {
+		return nil, errors.WithMessage(err, "could not open SQLite database")
+	}
+
+	r = &Reader{
+		log: log,
+		db:  db,
+	}
+	r.queries.getFile, err = r.db.Prepare(`
+		SELECT
+			file.content_type,
+			file.last_modified,
+			file.etag,
+			content.body
+		FROM url
+		INNER JOIN file
+			ON file.url_id = url.id
+		INNER JOIN content
+			ON content.file_id = file.id
+		WHERE
+			url.path = ?
+		AND
+			content.encoding = 'identity'
+	`)
+	if err != nil {
+		return nil, errors.WithMessage(err, "preparing select statement")
+	}
+
+	return r, nil
+}
+
+func (r *Reader) GetFile(filename string) (*storage.File, error) {
+	file := &storage.File{
+		Encodings: make(map[string]*buffer.Buffer, 1),
+	}
+	content := []byte{}
+	var unixTime int64
+	err := r.queries.getFile.QueryRow(filename).Scan(
+		&file.ContentType,
+		&unixTime,
+		&file.Etag,
+		&content,
+	)
+	if err != nil {
+		if err == sql.ErrNoRows {
+			return nil, nil
+		}
+		return nil, errors.WithMessage(err, "querying database")
+	}
+
+	file.LastModified = time.Unix(unixTime, 0)
+	file.Encodings["identity"] = buffer.NewBuffer(content)
+
+	return file, nil
+}
+
+// TODO write specialised query
+func (r *Reader) CanonicalisePath(path string) (cPath string, differs bool) {
+	cPath = path
+	if strings.HasSuffix(path, "/index.html") {
+		cPath, differs = strings.CutSuffix(path, "index.html")
+	} else if !strings.HasSuffix(path, "/") {
+		file, err := r.GetFile(path + "/")
+		if err != nil {
+			r.log.Warn("error canonicalising path", "path", path, "error", err)
+
+			return
+		}
+
+		if file != nil {
+			cPath, differs = path+"/", true
+		}
+	}
+
+	return
+}
diff --git a/internal/storage/sqlite/writer.go b/internal/storage/sqlite/writer.go
new file mode 100644
index 0000000..c35494d
--- /dev/null
+++ b/internal/storage/sqlite/writer.go
@@ -0,0 +1,212 @@
+package sqlite
+
+import (
+	"database/sql"
+	"fmt"
+	"hash/fnv"
+	"mime"
+	"path/filepath"
+	"time"
+
+	"go.alanpearce.eu/website/internal/buffer"
+	"go.alanpearce.eu/website/internal/storage"
+	"go.alanpearce.eu/x/log"
+
+	"gitlab.com/tozd/go/errors"
+	_ "modernc.org/sqlite"
+)
+
+type Writer struct {
+	db *sql.DB
+
+	options *Options
+	log     *log.Logger
+	queries struct {
+		insertURL     *sql.Stmt
+		insertFile    *sql.Stmt
+		insertContent *sql.Stmt
+	}
+}
+
+type Options struct {
+	Compress bool
+}
+
+func openDB(dbPath string) (*sql.DB, error) {
+	return sql.Open(
+		"sqlite",
+		fmt.Sprintf(
+			"file:%s?mode=%s&_pragma=foreign_keys(1)&_pragma=mmap_size(%d)",
+			dbPath,
+			"rwc",
+			16*1024*1024,
+		),
+	)
+}
+
+func NewWriter(dbPath string, logger *log.Logger, opts *Options) (*Writer, error) {
+	db, err := openDB(dbPath)
+	if err != nil {
+		return nil, errors.WithMessage(err, "opening sqlite database")
+	}
+
+	// WIP: only memory database for now
+	_, err = db.Exec(`
+		CREATE TABLE IF NOT EXISTS url (
+			id INTEGER PRIMARY KEY,
+			path TEXT NOT NULL
+		);
+		CREATE UNIQUE INDEX IF NOT EXISTS url_path
+			ON url (path);
+
+		CREATE TABLE IF NOT EXISTS file (
+			id INTEGER PRIMARY KEY,
+			url_id INTEGER NOT NULL,
+			content_type TEXT NOT NULL,
+			last_modified INTEGER NOT NULL,
+			etag TEXT NOT NULL,
+			FOREIGN KEY (url_id) REFERENCES url (id)
+		);
+		CREATE UNIQUE INDEX IF NOT EXISTS file_url_content_type
+			ON file (url_id, content_type);
+
+		CREATE TABLE IF NOT EXISTS content (
+			file_id INTEGER NOT NULL,
+			encoding TEXT NOT NULL,
+			body BLOB NOT NULL,
+			FOREIGN KEY (file_id) REFERENCES file (id)
+		);
+		CREATE UNIQUE INDEX IF NOT EXISTS file_content
+			ON content (file_id, encoding);
+
+		CREATE TABLE IF NOT EXISTS redirect (
+			id INTEGER PRIMARY KEY,
+			url_id INTEGER NOT NULL,
+			permanent BOOLEAN NOT NULL,
+			expires INTEGER,
+			FOREIGN KEY (url_id) REFERENCES url (id)
+		);
+	`)
+	if err != nil {
+		return nil, errors.WithMessage(err, "creating tables")
+	}
+
+	w := &Writer{
+		db:      db,
+		log:     logger,
+		options: opts,
+	}
+
+	w.queries.insertURL, err = db.Prepare(`INSERT INTO url (path) VALUES (?)`)
+	if err != nil {
+		return nil, errors.WithMessage(err, "preparing insert URL statement")
+	}
+
+	w.queries.insertFile, err = db.Prepare(`
+		INSERT INTO file (url_id, content_type, last_modified, etag)
+		VALUES (:url_id, :content_type, :last_modified, :etag)
+	`)
+	if err != nil {
+		return nil, errors.WithMessage(err, "preparing insert file statement")
+	}
+
+	w.queries.insertContent, err = db.Prepare(`
+		INSERT INTO content (file_id, encoding, body)
+		VALUES (:file_id, :encoding, :body)
+	`)
+	if err != nil {
+		return nil, errors.WithMessage(err, "preparing insert content statement")
+	}
+
+	return w, nil
+}
+
+func (s *Writer) Mkdirp(path string) error {
+	return nil
+}
+
+func (s *Writer) storeURL(path string) (int64, error) {
+	r, err := s.queries.insertURL.Exec(path)
+	if err != nil {
+		return 0, errors.WithMessage(err, "inserting URL into database")
+	}
+
+	return r.LastInsertId()
+}
+
+func (s *Writer) storeFile(urlID int64, file *storage.File) (int64, error) {
+	r, err := s.queries.insertFile.Exec(
+		sql.Named("url_id", urlID),
+		sql.Named("content_type", file.ContentType),
+		sql.Named("last_modified", file.LastModified.Unix()),
+		sql.Named("etag", file.Etag),
+	)
+	if err != nil {
+		return 0, errors.WithMessage(err, "inserting file into database")
+	}
+
+	return r.LastInsertId()
+}
+
+func (s *Writer) storeEncoding(fileID int64, encoding string, data []byte) error {
+	_, err := s.queries.insertContent.Exec(
+		sql.Named("file_id", fileID),
+		sql.Named("encoding", encoding),
+		sql.Named("body", data),
+	)
+	if err != nil {
+		return errors.WithMessage(err, "inserting encoding into database")
+	}
+
+	return nil
+}
+
+func etag(content []byte) (string, error) {
+	hash := fnv.New64a()
+	hash.Write(content)
+
+	return fmt.Sprintf(`W/"%x"`, hash.Sum(nil)), nil
+}
+
+func contentType(pathname string) string {
+	return mime.TypeByExtension(filepath.Ext(pathNameToFileName(pathname)))
+}
+
+func (s *Writer) Write(pathname string, content *buffer.Buffer) error {
+	s.log.Debug("storing content", "pathname", pathname)
+	bytes := content.Bytes()
+
+	urlID, err := s.storeURL(pathname)
+	if err != nil {
+		return errors.WithMessage(err, "storing URL")
+	}
+
+	etag, err := etag(bytes)
+	if err != nil {
+		return errors.WithMessage(err, "calculating etag")
+	}
+
+	file := &storage.File{
+		Path:         pathname,
+		ContentType:  contentType(pathname),
+		LastModified: time.Now(),
+		Etag:         etag,
+	}
+
+	fileID, err := s.storeFile(urlID, file)
+	if err != nil {
+		return errors.WithMessage(err, "storing file")
+	}
+
+	err = s.storeEncoding(fileID, "identity", bytes)
+	if err != nil {
+		return errors.WithMessagef(
+			err,
+			"storing encoding file_id: %d, encoding: %s",
+			fileID,
+			"identity",
+		)
+	}
+
+	return nil
+}
diff --git a/internal/website/mux.go b/internal/website/mux.go
index 784eb8c..05f6272 100644
--- a/internal/website/mux.go
+++ b/internal/website/mux.go
@@ -7,7 +7,7 @@ import (
 
 	"go.alanpearce.eu/website/internal/config"
 	ihttp "go.alanpearce.eu/website/internal/http"
-	"go.alanpearce.eu/website/internal/storage/files"
+	"go.alanpearce.eu/website/internal/storage"
 	"go.alanpearce.eu/website/templates"
 	"go.alanpearce.eu/x/log"
 
@@ -53,7 +53,7 @@ func (fn WrappedWebHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) {
 
 func NewMux(
 	cfg *config.Config,
-	reader *files.Reader,
+	reader storage.Reader,
 	log *log.Logger,
 ) (mux *http.ServeMux, err error) {
 	mux = &http.ServeMux{}
@@ -68,6 +68,7 @@ func NewMux(
 		}
 		file, err := reader.GetFile(urlPath)
 		if err != nil {
+			log.Error("error getting file from reader", "err", err)
 			return &ihttp.Error{
 				Message: "Error reading file",
 				Code:    http.StatusInternalServerError,