Skip to content

Commit

Permalink
Feature/pure go sqlite (#339)
Browse files Browse the repository at this point in the history
* Switched to modernc.org/sqlite

* No longer need GCC, since we removed CGO

* zip is in the base image

* Explicitly disable CGO

* Use explicit transactions

* Small cleanup
  • Loading branch information
chadweimer authored Jul 6, 2022
1 parent 873f4d1 commit 6116b47
Show file tree
Hide file tree
Showing 22 changed files with 156 additions and 47 deletions.
5 changes: 0 additions & 5 deletions .devcontainer/Dockerfile
Original file line number Diff line number Diff line change
Expand Up @@ -14,11 +14,6 @@ RUN apt-get update \
zip \
binfmt-support \
qemu-user-static \
gcc-arm-linux-gnueabihf \
libc6-dev-armhf-cross \
gcc-aarch64-linux-gnu \
libc6-dev-arm64-cross \
gcc-mingw-w64-x86-64 \
# openapi-codegen dependencies
openjdk-11-jdk-headless \
# Puppeteer dependencies
Expand Down
1 change: 0 additions & 1 deletion .github/workflows/ci.yml
Original file line number Diff line number Diff line change
Expand Up @@ -26,7 +26,6 @@ jobs:
with:
username: ${{ secrets.DOCKERHUB_USERNAME }}
password: ${{ secrets.DOCKERHUB_TOKEN }}
- run: sudo apt-get update && sudo apt-get install zip gcc-arm-linux-gnueabihf libc6-dev-armhf-cross gcc-aarch64-linux-gnu libc6-dev-arm64-cross gcc-mingw-w64-x86-64
- run: make install
- run: make lint
- run: make build docker BUILD_VERSION="v0.0.0 (master)" DOCKER_TAG=latest
Expand Down
34 changes: 13 additions & 21 deletions Makefile
Original file line number Diff line number Diff line change
Expand Up @@ -16,14 +16,10 @@ MODELS_CODEGEN_FILE=models/models.gen.go
API_CODEGEN_FILE=api/routes.gen.go
CODEGEN_FILES=$(API_CODEGEN_FILE) $(MODELS_CODEGEN_FILE)

GO_FLAGS=--tags "sqlite_foreign_keys"
GO_VERSION_FLAGS=-X 'github.com/chadweimer/gomp/metadata.BuildVersion=$(BUILD_VERSION)'
GO_LD_FLAGS=-ldflags "$(GO_VERSION_FLAGS) -extldflags '-static -static-libgcc'"
GO_WIN_LD_FLAGS=-ldflags "$(GO_VERSION_FLAGS)"
GO_ENV_LIN_AMD64=GOOS=linux GOARCH=amd64 CGO_ENABLED=1
GO_ENV_LIN_ARM=GOOS=linux GOARCH=arm CGO_ENABLED=1 CC=arm-linux-gnueabihf-gcc
GO_ENV_LIN_ARM64=GOOS=linux GOARCH=arm64 CGO_ENABLED=1 CC=aarch64-linux-gnu-gcc
GO_ENV_WIN_AMD64=GOOS=windows GOARCH=amd64 CGO_ENABLED=1 CC=x86_64-w64-mingw32-gcc
GOOS := linux
GOARCH := amd64
GO_ENV=GOOS=$(GOOS) GOARCH=$(GOARCH) CGO_ENABLED=0
GO_LD_FLAGS=-ldflags "-X 'github.com/chadweimer/gomp/metadata.BuildVersion=$(BUILD_VERSION)'"

GO_FILES := $(shell find . -type f -name "*.go" ! -name "*.gen.go")
DB_MIGRATION_FILES := $(shell find db/migrations -type f -name "*.*")
Expand Down Expand Up @@ -74,7 +70,6 @@ lint-server: $(CODEGEN_FILES)
gosec -severity medium ./...



# ---- BUILD ----

.PHONY: build
Expand All @@ -98,9 +93,11 @@ $(BUILD_DIR)/%/static: $(CLIENT_BUILD_DIR)
rm -rf $@ && mkdir -p $@ && cp -R $</* $@

$(BUILD_DIR)/linux/%/gomp: go.mod $(CODEGEN_FILES) $(GO_FILES)
$(GO_ENV) go build $(GO_FLAGS) -o $@ $(GO_LD_FLAGS)
$(GO_ENV) go build -o $@ $(GO_LD_FLAGS)

$(BUILD_DIR)/windows/%/gomp.exe: GOOS := windows
$(BUILD_DIR)/windows/%/gomp.exe: go.mod $(CODEGEN_FILES) $(GO_FILES)
$(GO_ENV) go build $(GO_FLAGS) -o $@ $(GO_WIN_LD_FLAGS)
$(GO_ENV) go build -o $@ $(GO_LD_FLAGS)

.PHONY: clean-$(BUILD_DIR)/%
clean-$(BUILD_DIR)/%:
Expand All @@ -109,47 +106,42 @@ clean-$(BUILD_DIR)/%:
.PHONY: clean-$(BUILD_DIR)/linux/%/gomp clean-$(BUILD_DIR)/windows/%/gomp.exe
clean-$(BUILD_DIR)/linux/%/gomp:
$(GO_ENV) go clean -i ./...
clean-$(BUILD_DIR)/windows/%/gomp.exe: GOOS := windows
clean-$(BUILD_DIR)/windows/%/gomp.exe:
$(GO_ENV) go clean -i ./...

# - AMD64 -

$(BUILD_LIN_AMD64_DIR): $(BUILD_LIN_AMD64_DIR)/gomp $(BUILD_LIN_AMD64_DIR)/db/migrations $(BUILD_LIN_AMD64_DIR)/static

$(BUILD_LIN_AMD64_DIR)/gomp: GO_ENV := $(GO_ENV_LIN_AMD64)

.PHONY: clean-linux-amd64
clean-linux-amd64: GO_ENV := $(GO_ENV_LIN_AMD64)
clean-linux-amd64: clean-$(BUILD_LIN_AMD64_DIR)/gomp clean-$(BUILD_LIN_AMD64_DIR) clean-$(BUILD_DIR)/gomp-linux-amd64.tar.gz

# - ARM32 -

$(BUILD_LIN_ARM_DIR): $(BUILD_LIN_ARM_DIR)/gomp $(BUILD_LIN_ARM_DIR)/db/migrations $(BUILD_LIN_ARM_DIR)/static

$(BUILD_LIN_ARM_DIR)/gomp: GO_ENV := $(GO_ENV_LIN_ARM)
$(BUILD_LIN_ARM_DIR)/gomp: GOARCH := arm

.PHONY: clean-linux-arm
clean-linux-arm: GO_ENV := $(GO_ENV_LIN_ARM)
clean-linux-arm: GOARCH := arm
clean-linux-arm: clean-$(BUILD_LIN_ARM_DIR)/gomp clean-$(BUILD_LIN_ARM_DIR) clean-$(BUILD_DIR)/gomp-linux-arm.tar.gz

# - ARM64 -

$(BUILD_LIN_ARM64_DIR): $(BUILD_LIN_ARM64_DIR)/gomp $(BUILD_LIN_ARM64_DIR)/db/migrations $(BUILD_LIN_ARM64_DIR)/static

$(BUILD_LIN_ARM64_DIR)/gomp: GO_ENV := $(GO_ENV_LIN_ARM64)
$(BUILD_LIN_ARM64_DIR)/gomp: GOARCH := arm64

.PHONY: clean-linux-arm64
clean-linux-arm64: GO_ENV := $(GO_ENV_LIN_ARM64)
clean-linux-arm64: GOARCH := arm64
clean-linux-arm64: clean-$(BUILD_LIN_ARM64_DIR)/gomp clean-$(BUILD_LIN_ARM64_DIR) clean-$(BUILD_DIR)/gomp-linux-arm64.tar.gz

# - WINDOWS -

$(BUILD_WIN_AMD64_DIR): $(BUILD_WIN_AMD64_DIR)/gomp.exe $(BUILD_WIN_AMD64_DIR)/db/migrations $(BUILD_WIN_AMD64_DIR)/static

$(BUILD_WIN_AMD64_DIR)/gomp.exe: GO_ENV := $(GO_ENV_WIN_AMD64)

.PHONY: clean-windows-amd64
clean-windows-amd64: GO_ENV := $(GO_ENV_WIN_AMD64)
clean-windows-amd64: clean-$(BUILD_WIN_AMD64_DIR)/gomp.exe clean-$(BUILD_WIN_AMD64_DIR) clean-$(BUILD_DIR)/gomp-windows-amd64.zip


Expand Down
24 changes: 12 additions & 12 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -128,18 +128,18 @@ spec:

The following table summarizes the available configuration settings, which are settable through environment variables.

ENV |Value(s) |Default |Description
------------------------|-----------------|-----------------|------------
BASE_ASSETS_PATH |string |static |The base path to the client assets.
DATABASE_DRIVER |postgres, sqlite3|&lt;empty&gt; |Which database/sql driver to use. If blank, the app will attempt to infer it based on the value of DATABASE_URL.
DATABASE_URL |string |file:data/data.db|The url (or path, connection string, etc) to use with the associated database driver when opening the database connection.
IS_DEVELOPMENT |0, 1 |0 |Defines whether to run the application in "development mode". Development mode turns on additional features, such as logging, that may not be desirable in a production environment.
MIGRATIONS_FORCE_VERSION|int |-1 |A version to force the migrations to on startup (will not run any of the migrations themselves). Set to a negative number to skip forcing a version.
MIGRATIONS_TABLE_NAME |string |&lt;empty&gt; |The name of the database migrations table to use. Leave blank to use the default from <https://github.com/golang-migrate/migrate.>
PORT |uint |5000 |The port number under which the site is being hosted.
SECURE_KEY |[]string |ChangeMe |Used for session authentication. Recommended to be 32 or 64 ASCII characters. Multiple keys can be separated by commas.
UPLOAD_DRIVER |fs, s3 |fs |Used to select which backend data store is used for file uploads.
UPLOAD_PATH |string |data/uploads |The path (full or relative) under which to store uploads. When using Amazon S3, this should be set to the bucket name.
ENV |Value(s) |Default |Description
------------------------|----------------|-----------------------------------------|------------
BASE_ASSETS_PATH |string |static |The base path to the client assets.
DATABASE_DRIVER |postgres, sqlite|&lt;empty&gt; |Which database/sql driver to use. If blank, the app will attempt to infer it based on the value of DATABASE_URL. The value 'sqlite3' can also be used, but is deprecated, and is equivalent to 'sqlite'.
DATABASE_URL |string |file:data/data.db?_pragma=foreign_keys(1)|The url (or path, connection string, etc) to use with the associated database driver when opening the database connection.
IS_DEVELOPMENT |0, 1 |0 |Defines whether to run the application in "development mode". Development mode turns on additional features, such as logging, that may not be desirable in a production environment.
MIGRATIONS_FORCE_VERSION|int |-1 |A version to force the migrations to on startup (will not run any of the migrations themselves). Set to a negative number to skip forcing a version.
MIGRATIONS_TABLE_NAME |string |&lt;empty&gt; |The name of the database migrations table to use. Leave blank to use the default from <https://github.com/golang-migrate/migrate.>
PORT |uint |5000 |The port number under which the site is being hosted.
SECURE_KEY |[]string |ChangeMe |Used for session authentication. Recommended to be 32 or 64 ASCII characters. Multiple keys can be separated by commas.
UPLOAD_DRIVER |fs, s3 |fs |Used to select which backend data store is used for file uploads.
UPLOAD_PATH |string |data/uploads |The path (full or relative) under which to store uploads. When using Amazon S3, this should be set to the bucket name.

All environment variables can also be prefixed with "GOMP_" (e.g., GOMP_IS_DEVELOPMENT=1) in cases where there is a need to avoid collisions with other applications.
The name with "GOMP_" is prefered if both are present.
Expand Down
14 changes: 12 additions & 2 deletions conf/conf.go
Original file line number Diff line number Diff line change
Expand Up @@ -57,7 +57,12 @@ type Config struct {
BaseAssetsPath string
}

const defaultSecureKey string = "ChangeMe"
const (
defaultSecureKey = "ChangeMe"

// Needed for backward compatibility
sqliteLegacyDriverName = "sqlite3"
)

// Load reads the configuration file from the specified path
func Load() *Config {
Expand All @@ -68,7 +73,7 @@ func Load() *Config {
IsDevelopment: false,
SecureKeys: []string{defaultSecureKey},
DatabaseDriver: "",
DatabaseUrl: "file:" + filepath.Join("data", "data.db"),
DatabaseUrl: "file:" + filepath.Join("data", "data.db") + "?_pragma=foreign_keys(1)",
MigrationsTableName: "",
MigrationsForceVersion: -1,
BaseAssetsPath: "static",
Expand Down Expand Up @@ -104,6 +109,11 @@ func Load() *Config {
} else {
log.Warn().Msg("Unable to infer a value for DATABASE_DRIVER; an error will likely follow")
}
} else if c.DatabaseDriver == sqliteLegacyDriverName {
// If the old driver name for sqlite is being used,
// we'll allow it and map it to the new one
log.Debug().Msgf("Detected DATABASE_DRIVER legacy value '%s'. Setting to '%s'", sqliteLegacyDriverName, db.SQLiteDriverName)
c.DatabaseDriver = db.SQLiteDriverName
}

logCtx := log.Info().
Expand Down
9 changes: 5 additions & 4 deletions db/driver-sqlite.go
Original file line number Diff line number Diff line change
Expand Up @@ -10,18 +10,18 @@ import (

"github.com/chadweimer/gomp/models"
"github.com/golang-migrate/migrate/v4"
"github.com/golang-migrate/migrate/v4/database/sqlite3"
"github.com/golang-migrate/migrate/v4/database/sqlite"
"github.com/jmoiron/sqlx"

// sqlite database driver
_ "github.com/mattn/go-sqlite3"
_ "modernc.org/sqlite"

// File source for db migration
_ "github.com/golang-migrate/migrate/v4/source/file"
)

// SQLiteDriverName is the name to use for this driver
const SQLiteDriverName string = "sqlite3"
const SQLiteDriverName string = "sqlite"

type sqliteRecipeDriverAdapter struct{}

Expand Down Expand Up @@ -81,8 +81,9 @@ func migrateSqliteDatabase(db *sqlx.DB, migrationsTableName string, migrationsFo
}
defer conn.Close()

driver, err := sqlite3.WithInstance(db.DB, &sqlite3.Config{
driver, err := sqlite.WithInstance(db.DB, &sqlite.Config{
MigrationsTable: migrationsTableName,
NoTxWrap: true,
})
if err != nil {
return err
Expand Down
Original file line number Diff line number Diff line change
@@ -1,3 +1,5 @@
BEGIN;

DROP TYPE user_level;
DROP TYPE recipe_state;

Expand All @@ -9,3 +11,5 @@ DROP TABLE recipe_note;
DROP TABLE recipe_rating;
DROP TABLE recipe_image;
DROP TABLE recipe_link;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,3 +1,5 @@
BEGIN;

-- Schema

CREATE TABLE app_user (
Expand Down Expand Up @@ -86,3 +88,5 @@ INSERT INTO app_user (username, password_hash, access_level)
VALUES('[email protected]', '$2a$08$1C0IMQAwkxLQcYvL/03jpuwOZjyF/6BCXgxHhkoarRoVp1wmiGwAS', 'admin');

INSERT INTO app_user_settings(user_id) SELECT id FROM app_user;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1 +1,5 @@
BEGIN;

DROP TABLE app_configuration;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,3 +1,4 @@
BEGIN;

-- Schema

Expand All @@ -8,3 +9,5 @@ CREATE TABLE app_configuration (
-- Seed data

INSERT INTO app_configuration (title) VALUES('GOMP: Go Meal Planner');

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,5 +1,9 @@
BEGIN;

DROP TRIGGER on_recipe_update;
DROP TRIGGER on_recipe_tag_insert;
DROP TRIGGER on_recipe_tag_delete;
DROP TRIGGER on_recipe_note_update;
DROP TRIGGER on_recipe_image_update;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,3 +1,5 @@
BEGIN;

-- recipe
CREATE TRIGGER on_recipe_update
AFTER UPDATE ON recipe
Expand Down Expand Up @@ -30,3 +32,5 @@ CREATE TRIGGER on_recipe_image_update
BEGIN
UPDATE recipe_image SET modified_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,2 +1,6 @@
BEGIN;

ALTER TABLE recipe
DROP COLUMN storage_instructions;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,2 +1,6 @@
BEGIN;

ALTER TABLE recipe
ADD COLUMN storage_instructions TEXT NOT NULL DEFAULT '';

COMMIT;
Original file line number Diff line number Diff line change
@@ -1 +1,5 @@
BEGIN;

DROP TABLE app_user_favorite_tag;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,7 +1,11 @@
BEGIN;

CREATE TABLE app_user_favorite_tag (
user_id INTEGER NOT NULL,
tag TEXT NOT NULL,
FOREIGN KEY(user_id) REFERENCES app_user(id) ON DELETE CASCADE
);
CREATE INDEX app_user_favorite_tag_idx ON app_user_favorite_tag(tag);
CREATE INDEX app_user_favorite_tag_user_id_idx ON app_user_favorite_tag(user_id);

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,4 +1,8 @@
BEGIN;

DROP TABLE search_filter;
DROP TABLE search_filter_field;
DROP TABLE search_filter_state;
DROP TABLE search_filter_tag;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,3 +1,5 @@
BEGIN;

CREATE TABLE search_filter (
id INTEGER NOT NULL PRIMARY KEY,
user_id INTEGER NOT NULL,
Expand Down Expand Up @@ -34,3 +36,5 @@ CREATE TABLE search_filter_tag (
FOREIGN KEY(search_filter_id) REFERENCES search_filter(id) ON DELETE CASCADE
);
CREATE INDEX search_filter_tag_search_filter_id_idx ON search_filter_tag(search_filter_id);

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,5 +1,9 @@
BEGIN;

ALTER TABLE app_user DROP COLUMN created_at;
ALTER TABLE app_user DROP COLUMN modified_at;

DROP TRIGGER on_app_user_update;
DROP TRIGGER on_app_user_insert;

COMMIT;
Original file line number Diff line number Diff line change
@@ -1,4 +1,6 @@
PRAGMA defer_foreign_keys=on;
PRAGMA foreign_keys=off;

BEGIN;

CREATE TABLE app_user_new (
id INTEGER NOT NULL PRIMARY KEY,
Expand Down Expand Up @@ -32,3 +34,7 @@ CREATE TRIGGER on_app_user_insert
BEGIN
INSERT INTO app_user_settings(user_id) VALUES(NEW.id);
END;

COMMIT;

PRAGMA foreign_keys=on;
17 changes: 16 additions & 1 deletion go.mod
Original file line number Diff line number Diff line change
Expand Up @@ -11,21 +11,36 @@ require (
github.com/jmoiron/sqlx v1.3.5
github.com/jszwec/s3fs v0.3.2
github.com/lib/pq v1.10.6
github.com/mattn/go-sqlite3 v1.14.13
github.com/rs/zerolog v1.27.0
golang.org/x/crypto v0.0.0-20220525230936-793ad666bf5e
modernc.org/sqlite v1.17.3
)

require (
github.com/hashicorp/errwrap v1.1.0 // indirect
github.com/hashicorp/go-multierror v1.1.1 // indirect
github.com/jmespath/go-jmespath v0.4.0 // indirect
github.com/kballard/go-shellquote v0.0.0-20180428030007-95032a82bc51 // indirect
github.com/mattn/go-colorable v0.1.12 // indirect
github.com/mattn/go-isatty v0.0.14 // indirect
github.com/mattn/go-sqlite3 v1.14.13 // indirect
github.com/remyoudompheng/bigfft v0.0.0-20200410134404-eec4a21b6bb0 // indirect
github.com/rs/xid v1.3.0 // indirect
go.uber.org/atomic v1.9.0 // indirect
golang.org/x/image v0.0.0-20220601225756-64ec528b34cd // indirect
golang.org/x/mod v0.6.0-dev.0.20220106191415-9b9b3d81d5e3 // indirect
golang.org/x/sys v0.0.0-20220513210249-45d2b4557a2a // indirect
golang.org/x/tools v0.1.10 // indirect
golang.org/x/xerrors v0.0.0-20220411194840-2f41105eb62f // indirect
lukechampine.com/uint128 v1.1.1 // indirect
modernc.org/cc/v3 v3.36.0 // indirect
modernc.org/ccgo/v3 v3.16.6 // indirect
modernc.org/libc v1.16.7 // indirect
modernc.org/mathutil v1.4.1 // indirect
modernc.org/memory v1.1.1 // indirect
modernc.org/opt v0.1.1 // indirect
modernc.org/strutil v1.1.1 // indirect
modernc.org/token v1.0.0 // indirect
)

go 1.18
Loading

0 comments on commit 6116b47

Please sign in to comment.