Skip to content

chore: split queries.sql into files by table #762

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 9 commits into from
Apr 1, 2022
Merged

chore: split queries.sql into files by table #762

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
8f65e10
chore: split queries.sql into files by table
coadler Mar 31, 2022
b479668
remove underscores from go filenames
coadler Mar 31, 2022
03b8005
queries in one file
coadler Mar 31, 2022
2ef8d8a
install goimports
coadler Mar 31, 2022
7e32318
comment generate.sh
coadler Mar 31, 2022
5aeb544
try go mod tidy
coadler Mar 31, 2022
449abba
mod download instead
coadler Mar 31, 2022
303297c
fix schema
coadler Apr 1, 2022
55476d7
generate.sh comment
coadler Apr 1, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions .github/workflows/coder.yaml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -89,6 +89,7 @@ jobs:

- run: go install google.golang.org/protobuf/cmd/protoc-gen-go@v1.26
- run: go install storj.io/drpc/cmd/protoc-gen-go-drpc@v0.0.26
- run: go install golang.org/x/tools/cmd/goimports@latest
- run: "make --output-sync -j gen"
- run: ./scripts/check_unstaged.sh

Expand Down
25 changes: 14 additions & 11 deletions Makefile
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -15,10 +15,8 @@ coderd/database/dump.sql: $(wildcard coderd/database/migrations/*.sql)
.PHONY: coderd/database/dump.sql

# Generates Go code for querying the database.
coderd/database/generate: fmt/sql coderd/database/dump.sql coderd/database/query.sql
cd coderd/database && sqlc generate && rm db_tmp.go
cd coderd/database && gofmt -w -r 'Querier -> querier' *.go
cd coderd/database && gofmt -w -r 'Queries -> sqlQuerier' *.go
coderd/database/generate: fmt/sql coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
coderd/database/generate.sh
.PHONY: coderd/database/generate

fmt/prettier:
Expand All @@ -31,13 +29,18 @@ else
endif
.PHONY: fmt/prettier

fmt/sql: ./coderd/database/query.sql
npx sql-formatter \
--language postgresql \
--lines-between-queries 2 \
./coderd/database/query.sql \
--output ./coderd/database/query.sql
sed -i 's/@ /@/g' ./coderd/database/query.sql
fmt/sql: $(wildcard coderd/database/queries/*.sql)
# TODO: this is slightly slow
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Could we use bash wait to make this faster?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

The biggest problem is that it has to attempt to install via npx every time, otherwise it's pretty fast! Can npx be called concurrently?

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Ohhhhhh. I say we require sql-formatter installed globally then.

for fi in coderd/database/queries/*.sql; do \
npx sql-formatter \
--language postgresql \
--lines-between-queries 2 \
--tab-indent \
$$fi \
--output $$fi; \
done

sed -i 's/@ /@/g' ./coderd/database/queries/*.sql

fmt: fmt/prettier fmt/sql
.PHONY: fmt
Expand Down
4 changes: 4 additions & 0 deletions coderd/database/dump.sql
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

41 changes: 41 additions & 0 deletions coderd/database/generate.sh
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,41 @@
#!/usr/bin/env bash

# This script turns many *.sql.go files into a single queries.sql.go file. This
# is due to sqlc's behavior when using multiple sql files to output them to
# multiple Go files. We decided it would be cleaner to move these to a single
# file for readability. We should probably contribute the option to do this
# upstream instead, because this is quite janky.

set -euo pipefail

cd "$(dirname "$0")"

sqlc generate

first=true
for fi in queries/*.sql.go; do
# Find the last line from the imports section and add 1.
cut=$(grep -n ')' "$fi" | head -n 1 | cut -d: -f1)
cut=$((cut + 1))

# Copy the header from the first file only, ignoring the source comment.
if $first; then
head -n 4 < "$fi" | grep -v "source" > queries.sql.go
first=false
fi

# Append the file past the imports section into queries.sql.go.
tail -n "+$cut" < "$fi" >> queries.sql.go
done

# Remove temporary go files.
rm -f queries/*.go

# Fix struct/interface names.
gofmt -w -r 'Querier -> querier' -- *.go
gofmt -w -r 'Queries -> sqlQuerier' -- *.go

# Ensure correct imports exist. Modules must all be downloaded so we get correct
# suggestions.
go mod download
goimports -w queries.sql.go
1 change: 1 addition & 0 deletions coderd/database/migrations/000002_projects.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -33,6 +33,7 @@ CREATE TABLE projects (

-- Enforces no active projects have the same name.
CREATE UNIQUE INDEX ON projects (organization_id, name) WHERE deleted = FALSE;
CREATE UNIQUE INDEX idx_projects_name_lower ON projects USING btree (lower(name));
Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@kylecarbs we seemed to be querying these by their lowercase names so I added indexes for them as well.

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Ahh wise. Good change!


-- Project Versions store historical project data. When a Project Version is imported,
-- an "import" job is queued to parse parameters. A Project Version
Expand Down
3 changes: 2 additions & 1 deletion coderd/database/migrations/000003_workspaces.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -12,7 +12,8 @@ CREATE TABLE workspaces (
);

-- Enforces no active workspaces have the same name.
CREATE UNIQUE INDEX ON workspaces (owner_id, name) WHERE deleted = FALSE;
CREATE UNIQUE INDEX ON workspaces USING btree (owner_id, name) WHERE deleted = FALSE;
CREATE UNIQUE INDEX idx_workspaces_name_lower ON workspaces USING btree (lower(name));

CREATE TYPE workspace_transition AS ENUM (
'start',
Expand Down
Loading