Skip to content
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

Adding SQL format checking via pg_format / pgFormatter #3740

Merged
merged 13 commits into from
Aug 2, 2023
Merged

Adding SQL format checking via pg_format / pgFormatter #3740

Show file tree
Hide file tree
Changes from 6 commits
Commits
Show all changes
13 commits
Select commit Hold shift + click to select a range
eee7875
SQL format checking, 1.
dessalines Jul 27, 2023
402fef7
SQL format checking, 2.
dessalines Jul 27, 2023
3bc4e21
SQL format checking, 3.
dessalines Jul 27, 2023
61a054f
SQL format checking, 4.
dessalines Jul 27, 2023
8c9d855
SQL format checking, 5.
dessalines Jul 27, 2023
91c899e
Running pg_format
dessalines Jul 27, 2023
7f9aff7
Getting rid of comment.
dessalines Jul 27, 2023
b05fd91
Upping pg_format version.
dessalines Jul 27, 2023
d3f040e
Using git ls-files for sql format check.
dessalines Jul 27, 2023
783e992
Merge branch 'main' into sql_format
dessalines Jul 28, 2023
666cb0b
Fixing sql lints.
dessalines Jul 28, 2023
1c27070
Addressing PR comments.
dessalines Aug 2, 2023
a462971
Merge remote-tracking branch 'origin/main' into sql_format
dessalines Aug 2, 2023
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
The table of contents is too big for display.
Diff view
Diff view
  •  
  •  
  •  
12 changes: 12 additions & 0 deletions .woodpecker.yml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -68,6 +68,18 @@ pipeline:
# when:
# platform: linux/amd64

sql_fmt:
image: alpine:3
commands:
- apk add bash wget perl make
- wget https://github.com/darold/pgFormatter/archive/refs/tags/v5.3.tar.gz
- tar xzf v5.3.tar.gz
- cd pgFormatter-5.3
- perl Makefile.PL
- make && make install
- cd ..
- ./scripts/./sql_format_check.sh

# make sure api builds with default features (used by other crates relying on lemmy api)
check_api_common_default_features:
image: *muslrust_image
Expand Down
5 changes: 3 additions & 2 deletions migrations/00000000000000_diesel_initial_setup/down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,6 +1,7 @@
-- This file was automatically created by Diesel to setup helper functions
-- and other internal bookkeeping. This file is safe to edit, any future
-- changes will be added to existing projects as new migrations.
DROP FUNCTION IF EXISTS diesel_manage_updated_at (_tbl regclass);

DROP FUNCTION IF EXISTS diesel_set_updated_at ();

DROP FUNCTION IF EXISTS diesel_manage_updated_at(_tbl regclass);
DROP FUNCTION IF EXISTS diesel_set_updated_at();
26 changes: 13 additions & 13 deletions migrations/00000000000000_diesel_initial_setup/up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,10 +1,6 @@
-- This file was automatically created by Diesel to setup helper functions
-- and other internal bookkeeping. This file is safe to edit, any future
-- changes will be added to existing projects as new migrations.




-- Sets up a trigger for the given table to automatically set a column called
-- `updated_at` whenever the row is modified (unless `updated_at` was included
-- in the modified columns)
Expand All @@ -16,21 +12,25 @@
--
-- SELECT diesel_manage_updated_at('users');
-- ```
CREATE OR REPLACE FUNCTION diesel_manage_updated_at(_tbl regclass) RETURNS VOID AS $$
CREATE OR REPLACE FUNCTION diesel_manage_updated_at (_tbl regclass)
RETURNS VOID
AS $$
BEGIN
EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl);
END;
$$ LANGUAGE plpgsql;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$
CREATE OR REPLACE FUNCTION diesel_set_updated_at ()
RETURNS TRIGGER
AS $$
BEGIN
IF (
NEW IS DISTINCT FROM OLD AND
NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at
) THEN
NEW.updated_at := current_timestamp;
IF (NEW IS DISTINCT FROM OLD AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at) THEN
NEW.updated_at := CURRENT_TIMESTAMP;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
$$
LANGUAGE plpgsql;

6 changes: 4 additions & 2 deletions migrations/2019-02-26-002946_create_user/down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,2 +1,4 @@
drop table user_ban;
drop table user_;
DROP TABLE user_ban;

DROP TABLE user_;

40 changes: 21 additions & 19 deletions migrations/2019-02-26-002946_create_user/up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,23 +1,25 @@
create table user_ (
id serial primary key,
name varchar(20) not null,
fedi_name varchar(40) not null,
preferred_username varchar(20),
password_encrypted text not null,
email text unique,
icon bytea,
admin boolean default false not null,
banned boolean default false not null,
published timestamp not null default now(),
updated timestamp,
unique(name, fedi_name)
CREATE TABLE user_ (
id serial PRIMARY KEY,
name varchar(20) NOT NULL,
fedi_name varchar(40) NOT NULL,
preferred_username varchar(20),
password_encrypted text NOT NULL,
email text UNIQUE,
icon bytea,
admin boolean DEFAULT FALSE NOT NULL,
banned boolean DEFAULT FALSE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
updated timestamp,
UNIQUE (name, fedi_name)
);

create table user_ban (
id serial primary key,
user_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
unique (user_id)
CREATE TABLE user_ban (
id serial PRIMARY KEY,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (user_id)
);

insert into user_ (name, fedi_name, password_encrypted) values ('admin', 'TBD', 'TBD');
INSERT INTO user_ (name, fedi_name, password_encrypted)
VALUES ('admin', 'TBD', 'TBD');

20 changes: 14 additions & 6 deletions migrations/2019-02-27-170003_create_community/down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,6 +1,14 @@
drop table site;
drop table community_user_ban;;
drop table community_moderator;
drop table community_follower;
drop table community;
drop table category;
DROP TABLE site;

DROP TABLE community_user_ban;

;

DROP TABLE community_moderator;

DROP TABLE community_follower;

DROP TABLE community;

DROP TABLE category;

134 changes: 68 additions & 66 deletions migrations/2019-02-27-170003_create_community/up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,79 +1,81 @@
create table category (
id serial primary key,
name varchar(100) not null unique
CREATE TABLE category (
id serial PRIMARY KEY,
name varchar(100) NOT NULL UNIQUE
);

insert into category (name) values
('Discussion'),
('Humor/Memes'),
('Gaming'),
('Movies'),
('TV'),
('Music'),
('Literature'),
('Comics'),
('Photography'),
('Art'),
('Learning'),
('DIY'),
('Lifestyle'),
('News'),
('Politics'),
('Society'),
('Gender/Identity/Sexuality'),
('Race/Colonisation'),
('Religion'),
('Science/Technology'),
('Programming/Software'),
('Health/Sports/Fitness'),
('Porn'),
('Places'),
('Meta'),
('Other');
INSERT INTO category (name)
VALUES ('Discussion'),
('Humor/Memes'),
('Gaming'),
('Movies'),
('TV'),
('Music'),
('Literature'),
('Comics'),
('Photography'),
('Art'),
('Learning'),
('DIY'),
('Lifestyle'),
('News'),
('Politics'),
('Society'),
('Gender/Identity/Sexuality'),
('Race/Colonisation'),
('Religion'),
('Science/Technology'),
('Programming/Software'),
('Health/Sports/Fitness'),
('Porn'),
('Places'),
('Meta'),
('Other');

create table community (
id serial primary key,
name varchar(20) not null unique,
title varchar(100) not null,
description text,
category_id int references category on update cascade on delete cascade not null,
creator_id int references user_ on update cascade on delete cascade not null,
removed boolean default false not null,
published timestamp not null default now(),
updated timestamp
CREATE TABLE community (
id serial PRIMARY KEY,
name varchar(20) NOT NULL UNIQUE,
title varchar(100) NOT NULL,
description text,
category_id int REFERENCES category ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
removed boolean DEFAULT FALSE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
updated timestamp
);

create table community_moderator (
id serial primary key,
community_id int references community on update cascade on delete cascade not null,
user_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
unique (community_id, user_id)
CREATE TABLE community_moderator (
id serial PRIMARY KEY,
community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (community_id, user_id)
);

create table community_follower (
id serial primary key,
community_id int references community on update cascade on delete cascade not null,
user_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
unique (community_id, user_id)
CREATE TABLE community_follower (
id serial PRIMARY KEY,
community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (community_id, user_id)
);

create table community_user_ban (
id serial primary key,
community_id int references community on update cascade on delete cascade not null,
user_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
unique (community_id, user_id)
CREATE TABLE community_user_ban (
id serial PRIMARY KEY,
community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (community_id, user_id)
);

insert into community (name, title, category_id, creator_id) values ('main', 'The Default Community', 1, 1);
INSERT INTO community (name, title, category_id, creator_id)
VALUES ('main', 'The Default Community', 1, 1);

create table site (
id serial primary key,
name varchar(20) not null unique,
description text,
creator_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
updated timestamp
CREATE TABLE site (
id serial PRIMARY KEY,
name varchar(20) NOT NULL UNIQUE,
description text,
creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
updated timestamp
);

12 changes: 8 additions & 4 deletions migrations/2019-03-03-163336_create_post/down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,8 @@
drop table post_read;
drop table post_saved;
drop table post_like;
drop table post;
DROP TABLE post_read;

DROP TABLE post_saved;

DROP TABLE post_like;

DROP TABLE post;

61 changes: 31 additions & 30 deletions migrations/2019-03-03-163336_create_post/up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,37 +1,38 @@
create table post (
id serial primary key,
name varchar(100) not null,
url text, -- These are both optional, a post can just have a title
body text,
creator_id int references user_ on update cascade on delete cascade not null,
community_id int references community on update cascade on delete cascade not null,
removed boolean default false not null,
locked boolean default false not null,
published timestamp not null default now(),
updated timestamp
CREATE TABLE post (
id serial PRIMARY KEY,
name varchar(100) NOT NULL,
url text, -- These are both optional, a post can just have a title
body text,
creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
removed boolean DEFAULT FALSE NOT NULL,
locked boolean DEFAULT FALSE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
updated timestamp
);

create table post_like (
id serial primary key,
post_id int references post on update cascade on delete cascade not null,
user_id int references user_ on update cascade on delete cascade not null,
score smallint not null, -- -1, or 1 for dislike, like, no row for no opinion
published timestamp not null default now(),
unique(post_id, user_id)
CREATE TABLE post_like (
id serial PRIMARY KEY,
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
score smallint NOT NULL, -- -1, or 1 for dislike, like, no row for no opinion
published timestamp NOT NULL DEFAULT now(),
UNIQUE (post_id, user_id)
);

create table post_saved (
id serial primary key,
post_id int references post on update cascade on delete cascade not null,
user_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
unique(post_id, user_id)
CREATE TABLE post_saved (
id serial PRIMARY KEY,
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (post_id, user_id)
);

create table post_read (
id serial primary key,
post_id int references post on update cascade on delete cascade not null,
user_id int references user_ on update cascade on delete cascade not null,
published timestamp not null default now(),
unique(post_id, user_id)
CREATE TABLE post_read (
id serial PRIMARY KEY,
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (post_id, user_id)
);

9 changes: 6 additions & 3 deletions migrations/2019-03-05-233828_create_comment/down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,3 +1,6 @@
drop table comment_saved;
drop table comment_like;
drop table comment;
DROP TABLE comment_saved;

DROP TABLE comment_like;

DROP TABLE comment;

Loading