repos / pico

pico services - prose.sh, pastes.sh, imgs.sh, feeds.sh, pgs.sh
git clone https://github.com/picosh/pico.git
commit
90d8d20
parent
90d8d20
author
Eric Bower
date
2022-07-27 16:00:54 +0000 UTC
init
16 files changed,  +530, -0
A .gitignore
A db/migrations/20220310_init.sql
A db/migrations/20220422_add_desc_to_user_and_post.sql
A db/migrations/20220426_add_index_for_filename.sql
A db/migrations/20220427_username_to_lower.sql
A db/migrations/20220523_timestamp_with_tz.sql
A db/migrations/20220721_analytics.sql
A db/migrations/20220722_post_hidden.sql
A db/migrations/20220727_post_change_post_contraints.sql
A db/setup.sql
A db/teardown.sql
A docker-compose.yml
A go.mod
A go.sum
A main.go
A makefile
A .gitignore
+14, -0 
 1@@ -0,0 +1,14 @@
 2+*.log
 3+*.swp
 4+.env
 5+.envrc
 6+build/*
 7+!build/.gitkeep
 8+ssh_data/*
 9+!ssh_data/.gitkeep
10+caddy_data/*
11+!caddy_data/.gitkeep
12+caddy_config/*
13+!caddy_config/.gitkeep
14+.env.prod
15+*.bak
A db/migrations/20220310_init.sql
+40, -0 
 1@@ -0,0 +1,40 @@
 2+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 3+
 4+CREATE TABLE IF NOT EXISTS app_users (
 5+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 6+  name character varying(50),
 7+  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
 8+  CONSTRAINT unique_name UNIQUE (name),
 9+  CONSTRAINT app_user_pkey PRIMARY KEY (id)
10+);
11+
12+CREATE TABLE IF NOT EXISTS public_keys (
13+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
14+  user_id uuid NOT NULL,
15+  public_key varchar(2048) NOT NULL,
16+  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
17+  CONSTRAINT user_public_keys_pkey PRIMARY KEY (id),
18+  CONSTRAINT unique_key_for_user UNIQUE (user_id, public_key),
19+  CONSTRAINT fk_user_public_keys_owner
20+    FOREIGN KEY(user_id)
21+  REFERENCES app_users(id)
22+  ON DELETE CASCADE
23+  ON UPDATE CASCADE
24+);
25+
26+CREATE TABLE IF NOT EXISTS posts (
27+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
28+  user_id uuid NOT NULL,
29+  title character varying(255) NOT NULL,
30+  text text NOT NULL DEFAULT '',
31+  publish_at timestamp without time zone NOT NULL DEFAULT NOW(),
32+  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
33+  updated_at timestamp without time zone NOT NULL DEFAULT NOW(),
34+  CONSTRAINT posts_pkey PRIMARY KEY (id),
35+  CONSTRAINT unique_title_for_user UNIQUE (user_id, title),
36+  CONSTRAINT fk_posts_app_users
37+    FOREIGN KEY(user_id)
38+  REFERENCES app_users(id)
39+  ON DELETE CASCADE
40+  ON UPDATE CASCADE
41+);
A db/migrations/20220422_add_desc_to_user_and_post.sql
+8, -0 
1@@ -0,0 +1,8 @@
2+ALTER TABLE app_users ADD COLUMN bio character varying(150) NOT NULL DEFAULT '';
3+ALTER TABLE posts ADD COLUMN description character varying(150) NOT NULL DEFAULT '';
4+ALTER TABLE posts ADD COLUMN filename character varying(255);
5+
6+UPDATE posts SET filename = title;
7+
8+ALTER TABLE posts ADD CONSTRAINT unique_filename_for_user UNIQUE (user_id, filename);
9+ALTER TABLE posts DROP CONSTRAINT unique_title_for_user;
A db/migrations/20220426_add_index_for_filename.sql
+2, -0 
1@@ -0,0 +1,2 @@
2+CREATE INDEX posts_filename ON posts USING btree(filename);
3+ALTER TABLE app_users DROP COLUMN bio;
A db/migrations/20220427_username_to_lower.sql
+1, -0 
1@@ -0,0 +1 @@
2+UPDATE app_users SET name = LOWER(name) WHERE name != LOWER(name);
A db/migrations/20220523_timestamp_with_tz.sql
+5, -0 
1@@ -0,0 +1,5 @@
2+ALTER TABLE posts ALTER COLUMN updated_at TYPE timestamp WITH TIME ZONE USING updated_at AT TIME ZONE 'UTC';
3+ALTER TABLE posts ALTER COLUMN publish_at TYPE timestamp WITH TIME ZONE USING publish_at AT TIME ZONE 'UTC';
4+ALTER TABLE posts ALTER COLUMN created_at TYPE timestamp WITH TIME ZONE USING created_at AT TIME ZONE 'UTC';
5+ALTER TABLE app_users ALTER COLUMN created_at TYPE timestamp WITH TIME ZONE USING created_at AT TIME ZONE 'UTC';
6+ALTER TABLE public_keys ALTER COLUMN created_at TYPE timestamp WITH TIME ZONE USING created_at AT TIME ZONE 'UTC';
A db/migrations/20220721_analytics.sql
+12, -0 
 1@@ -0,0 +1,12 @@
 2+CREATE TABLE IF NOT EXISTS post_analytics (
 3+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 4+  post_id uuid NOT NULL,
 5+  views int DEFAULT 0,
 6+  updated_at timestamp without time zone NOT NULL DEFAULT NOW(),
 7+  CONSTRAINT analytics_pkey PRIMARY KEY (id),
 8+  CONSTRAINT fk_analytics_posts
 9+    FOREIGN KEY(post_id)
10+  REFERENCES posts(id)
11+  ON DELETE CASCADE
12+  ON UPDATE CASCADE
13+);
A db/migrations/20220722_post_hidden.sql
+2, -0 
1@@ -0,0 +1,2 @@
2+ALTER TABLE posts ADD COLUMN hidden boolean NOT NULL DEFAULT FALSE;
3+UPDATE posts SET hidden = TRUE WHERE filename LIKE E'\\_%';
A db/migrations/20220727_post_change_post_contraints.sql
+8, -0 
1@@ -0,0 +1,8 @@
2+CREATE TYPE space AS ENUM ('prose', 'lists', 'pastes', 'imgs');
3+
4+ALTER TABLE posts ADD COLUMN cur_space space;
5+ALTER TABLE posts ADD COLUMN views int DEFAULT 0;
6+ALTER TABLE posts DROP CONSTRAINT unique_filename_for_user;
7+ALTER TABLE posts ADD CONSTRAINT unique_filename_for_user UNIQUE (user_id, cur_space, filename);
8+
9+DROP TABLE post_analytics;
A db/setup.sql
+1, -0 
1@@ -0,0 +1 @@
2+CREATE DATABASE "pico" OWNER "postgres";
A db/teardown.sql
+3, -0 
1@@ -0,0 +1,3 @@
2+DROP TABLE posts CASCADE;
3+DROP TABLE app_users CASCADE;
4+DROP TABLE public_keys CASCADE;
A docker-compose.yml
+9, -0 
 1@@ -0,0 +1,9 @@
 2+version: "3.4"
 3+services:
 4+  db:
 5+    image: postgres
 6+    restart: always
 7+    ports:
 8+      - "5432:5432"
 9+    env_file:
10+      - .env
A go.mod
+17, -0 
 1@@ -0,0 +1,17 @@
 2+module git.sr.ht/~erock/pico
 3+
 4+go 1.18
 5+
 6+replace git.sr.ht/~erock/wish => /home/erock/pico/wish
 7+
 8+require (
 9+	git.sr.ht/~erock/wish v0.0.0-20220723165654-ad295e939d88
10+	go.uber.org/zap v1.21.0
11+)
12+
13+require (
14+	github.com/lib/pq v1.10.6 // indirect
15+	go.uber.org/atomic v1.7.0 // indirect
16+	go.uber.org/multierr v1.6.0 // indirect
17+	golang.org/x/exp v0.0.0-20220613132600-b0d781184e0d // indirect
18+)
A go.sum
+61, -0 
 1@@ -0,0 +1,61 @@
 2+github.com/benbjohnson/clock v1.1.0 h1:Q92kusRqC1XV2MjkWETPvjJVqKetz1OzxZB7mHJLju8=
 3+github.com/benbjohnson/clock v1.1.0/go.mod h1:J11/hYXuz8f4ySSvYwY0FKfm+ezbsZBKZxNJlLklBHA=
 4+github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 5+github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
 6+github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 7+github.com/kr/pretty v0.1.0/go.mod h1:dAy3ld7l9f0ibDNOQOHHMYYIIbhfbHSm3C4ZsoJORNo=
 8+github.com/kr/pty v1.1.1/go.mod h1:pFQYn66WHrOpPYNljwOMqo10TkYh1fy3cYio2l3bCsQ=
 9+github.com/kr/text v0.1.0/go.mod h1:4Jbv+DJW3UT/LiOwJeYQe1efqtUx/iVham/4vfdArNI=
10+github.com/lib/pq v1.10.6 h1:jbk+ZieJ0D7EVGJYpL9QTz7/YW6UHbmdnZWYyK5cdBs=
11+github.com/lib/pq v1.10.6/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o=
12+github.com/pkg/errors v0.8.1 h1:iURUrRGxPUNPdy5/HRSm+Yj6okJ6UtLINN0Q9M4+h3I=
13+github.com/pkg/errors v0.8.1/go.mod h1:bwawxfHBFNV+L2hUp1rHADufV3IMtnDRdf1r5NINEl0=
14+github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
15+github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
16+github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
17+github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
18+github.com/stretchr/testify v1.7.0 h1:nwc3DEeHmmLAfoZucVR881uASk0Mfjw8xYJ99tb5CcY=
19+github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
20+github.com/yuin/goldmark v1.3.5/go.mod h1:mwnBkeHKe2W/ZEtQ+71ViKU8L12m81fl3OWwC1Zlc8k=
21+go.uber.org/atomic v1.7.0 h1:ADUqmZGgLDDfbSL9ZmPxKTybcoEYHgpYfELNoN+7hsw=
22+go.uber.org/atomic v1.7.0/go.mod h1:fEN4uk6kAWBTFdckzkM89CLk9XfWZrxpCo0nPH17wJc=
23+go.uber.org/goleak v1.1.11 h1:wy28qYRKZgnJTxGxvye5/wgWr1EKjmUDGYox5mGlRlI=
24+go.uber.org/goleak v1.1.11/go.mod h1:cwTWslyiVhfpKIDGSZEM2HlOvcqm+tG4zioyIeLoqMQ=
25+go.uber.org/multierr v1.6.0 h1:y6IPFStTAIT5Ytl7/XYmHvzXQ7S3g/IeZW9hyZ5thw4=
26+go.uber.org/multierr v1.6.0/go.mod h1:cdWPpRnG4AhwMwsgIHip0KRBQjJy5kYEpYjJxpXp9iU=
27+go.uber.org/zap v1.21.0 h1:WefMeulhovoZ2sYXz7st6K0sLj7bBhpiFaud4r4zST8=
28+go.uber.org/zap v1.21.0/go.mod h1:wjWOCqI0f2ZZrJF/UufIOkiC8ii6tm1iqIsLo76RfJw=
29+golang.org/x/crypto v0.0.0-20190308221718-c2843e01d9a2/go.mod h1:djNgcEr1/C05ACkg1iLfiJU5Ep61QUkGW8qpdssI0+w=
30+golang.org/x/crypto v0.0.0-20191011191535-87dc89f01550/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
31+golang.org/x/exp v0.0.0-20220613132600-b0d781184e0d h1:vtUKgx8dahOomfFzLREU8nSv25YHnTgLBn4rDnWZdU0=
32+golang.org/x/exp v0.0.0-20220613132600-b0d781184e0d/go.mod h1:Kr81I6Kryrl9sr8s2FK3vxD90NdsKWRuOIl2O4CvYbA=
33+golang.org/x/lint v0.0.0-20190930215403-16217165b5de/go.mod h1:6SW0HCj/g11FgYtHlgUYUwCkIfeOF89ocIRzGO/8vkc=
34+golang.org/x/mod v0.4.2/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
35+golang.org/x/net v0.0.0-20190311183353-d8887717615a/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
36+golang.org/x/net v0.0.0-20190404232315-eb5bcb51f2a3/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
37+golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
38+golang.org/x/net v0.0.0-20210405180319-a5a99cb37ef4/go.mod h1:p54w0d4576C0XHj96bSt6lcn1PtDYWL6XObtHCRCNQM=
39+golang.org/x/sync v0.0.0-20190423024810-112230192c58/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
40+golang.org/x/sync v0.0.0-20210220032951-036812b2e83c/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
41+golang.org/x/sys v0.0.0-20190215142949-d0b11bdaac8a/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
42+golang.org/x/sys v0.0.0-20190412213103-97732733099d/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
43+golang.org/x/sys v0.0.0-20201119102817-f84b799fce68/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
44+golang.org/x/sys v0.0.0-20210330210617-4fbd30eecc44/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
45+golang.org/x/sys v0.0.0-20210510120138-977fb7262007/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
46+golang.org/x/term v0.0.0-20201126162022-7de9c90e9dd1/go.mod h1:bj7SfCRtBDWHUb9snDiAeCFNEtKQo2Wmx5Cou7ajbmo=
47+golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
48+golang.org/x/text v0.3.3/go.mod h1:5Zoc/QRtKVWzQhOtBMvqHzDpF6irO9z98xDceosuGiQ=
49+golang.org/x/tools v0.0.0-20180917221912-90fa682c2a6e/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
50+golang.org/x/tools v0.0.0-20190311212946-11955173bddd/go.mod h1:LCzVGOaR6xXOjkQ3onu1FJEFr0SW1gC7cKk1uF8kGRs=
51+golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
52+golang.org/x/tools v0.1.5/go.mod h1:o0xws9oXOQQZyjljx8fwUC0k7L1pTE6eaCbjGeHmOkk=
53+golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
54+golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
55+golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
56+gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
57+gopkg.in/check.v1 v1.0.0-20180628173108-788fd7840127/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
58+gopkg.in/yaml.v2 v2.2.8 h1:obN1ZagJSUGI0Ek/LBmuj4SNLPfIny3KsKFopxRdj10=
59+gopkg.in/yaml.v2 v2.2.8/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
60+gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
61+gopkg.in/yaml.v3 v3.0.0-20210107192922-496545a6307b h1:h8qDotaEPuJATrMmW04NCwg7v22aHH28wwpauUhK9Oo=
62+gopkg.in/yaml.v3 v3.0.0-20210107192922-496545a6307b/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
A main.go
+268, -0 
  1@@ -0,0 +1,268 @@
  2+package main
  3+
  4+import (
  5+	"context"
  6+	"database/sql"
  7+	"fmt"
  8+	"log"
  9+	"os"
 10+
 11+	"git.sr.ht/~erock/wish/cms/config"
 12+	"git.sr.ht/~erock/wish/cms/db"
 13+	"git.sr.ht/~erock/wish/cms/db/postgres"
 14+	"go.uber.org/zap"
 15+)
 16+
 17+func createLogger() *zap.SugaredLogger {
 18+	logger, err := zap.NewProduction()
 19+	if err != nil {
 20+		log.Fatal(err)
 21+	}
 22+
 23+	return logger.Sugar()
 24+}
 25+
 26+func InsertUser(tx *sql.Tx, user *db.User) error {
 27+	_, err := tx.Exec(
 28+		"INSERT INTO app_users (id, name, created_at) VALUES($1, $2, $3)",
 29+		user.ID,
 30+		user.Name,
 31+		user.CreatedAt,
 32+	)
 33+	return err
 34+}
 35+
 36+func InsertPublicKey(tx *sql.Tx, pk *db.PublicKey) error {
 37+	_, err := tx.Exec(
 38+		"INSERT INTO public_keys (id, user_id, public_key, created_at) VALUES ($1, $2, $3, $4)",
 39+		pk.ID,
 40+		pk.UserID,
 41+		pk.Key,
 42+		pk.CreatedAt,
 43+	)
 44+	return err
 45+}
 46+
 47+func InsertPost(tx *sql.Tx, post *db.Post) error {
 48+	_, err := tx.Exec(
 49+		`INSERT INTO posts
 50+			(id, user_id, title, text, created_at, publish_at, updated_at, description, filename, hidden, cur_space)
 51+			VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)`,
 52+		post.ID,
 53+		post.UserID,
 54+		post.Title,
 55+		post.Text,
 56+		post.CreatedAt,
 57+		post.PublishAt,
 58+		post.UpdatedAt,
 59+		post.Description,
 60+		post.Filename,
 61+		post.Hidden,
 62+		post.Space,
 63+	)
 64+	return err
 65+}
 66+
 67+type ConflictData struct {
 68+	User          *db.User
 69+	Pks           []*db.PublicKey
 70+	ReplaceWithID string
 71+}
 72+
 73+func main() {
 74+	logger := createLogger()
 75+
 76+	listsCfg := config.NewConfigCms()
 77+	listsCfg.Logger = logger
 78+	listsCfg.DbURL = os.Getenv("LISTS_DB_URL")
 79+	listsDb := postgres.NewDB(listsCfg)
 80+
 81+	proseCfg := config.NewConfigCms()
 82+	proseCfg.DbURL = os.Getenv("PROSE_DB_URL")
 83+	proseCfg.Logger = logger
 84+	proseDb := postgres.NewDB(proseCfg)
 85+
 86+	picoCfg := config.NewConfigCms()
 87+	picoCfg.Logger = logger
 88+	picoCfg.DbURL = os.Getenv("PICO_DB_URL")
 89+	picoDb := postgres.NewDB(picoCfg)
 90+
 91+	ctx := context.Background()
 92+	tx, err := picoDb.Db.BeginTx(ctx, nil)
 93+	if err != nil {
 94+		panic(err)
 95+	}
 96+	rollback := func() {
 97+		// Defer a rollback in case anything fails.
 98+		defer tx.Rollback()
 99+	}
100+	defer rollback()
101+
102+	logger.Info("delete users where name is null")
103+	_, err = tx.Exec("DELETE FROM app_users WHERE name IS NULL")
104+	if err != nil {
105+		panic(err)
106+	}
107+
108+	proseUsers, err := proseDb.FindUsers()
109+	if err != nil {
110+		panic(err)
111+	}
112+
113+	listUsers, err := listsDb.FindUsers()
114+	if err != nil {
115+		panic(err)
116+	}
117+
118+	userMap := map[string]*db.User{}
119+	for _, proseUser := range proseUsers {
120+		userMap[proseUser.Name] = proseUser
121+
122+		err = InsertUser(tx, proseUser)
123+		if err != nil {
124+			panic(err)
125+		}
126+	}
127+
128+	noconflicts := []*ConflictData{}
129+	conflicts := []*ConflictData{}
130+	updateIDs := []*ConflictData{}
131+	logger.Info("Finding conflicts")
132+	for _, listUser := range listUsers {
133+		listKeys, err := listsDb.FindKeysForUser(listUser)
134+		if err != nil {
135+			panic(err)
136+		}
137+
138+		data := &ConflictData{
139+			User: listUser,
140+			Pks:  listKeys,
141+		}
142+
143+		if userMap[listUser.Name] == nil {
144+			noconflicts = append(noconflicts, data)
145+			continue
146+		} else {
147+			proseUser := userMap[listUser.Name]
148+			proseKeys, err := proseDb.FindKeysForUser(proseUser)
149+			if err != nil {
150+				panic(err)
151+			}
152+
153+			if len(listKeys) != len(proseKeys) {
154+				conflicts = append(conflicts, data)
155+				continue
156+			}
157+
158+			pkMap := map[string]bool{}
159+			for _, prosePK := range proseKeys {
160+				pkMap[prosePK.Key] = true
161+
162+				err = InsertPublicKey(tx, prosePK)
163+				if err != nil {
164+					panic(err)
165+				}
166+			}
167+
168+			conflicted := false
169+			for _, listPK := range listKeys {
170+				if !pkMap[listPK.Key] {
171+					conflicted = true
172+					conflicts = append(conflicts, data)
173+					break
174+				}
175+			}
176+
177+			if !conflicted {
178+				data.ReplaceWithID = proseUser.ID
179+				updateIDs = append(updateIDs, data)
180+			}
181+		}
182+	}
183+
184+	logger.Infof("Adding records with no conflicts (%d)", len(noconflicts))
185+	for _, data := range noconflicts {
186+		err = InsertUser(tx, data.User)
187+		if err != nil {
188+			panic(err)
189+		}
190+
191+		for _, pk := range data.Pks {
192+			err = InsertPublicKey(tx, pk)
193+			if err != nil {
194+				panic(err)
195+			}
196+		}
197+	}
198+
199+	logger.Infof("Adding records with conflicts (%d)", len(conflicts))
200+	for _, data := range conflicts {
201+		data.User.Name = fmt.Sprintf("%stmp", data.User.Name)
202+		err = InsertUser(tx, data.User)
203+		if err != nil {
204+			panic(err)
205+		}
206+
207+		for _, pk := range data.Pks {
208+			err = InsertPublicKey(tx, pk)
209+			if err != nil {
210+				panic(err)
211+			}
212+		}
213+	}
214+
215+	prosePosts, err := proseDb.FindPosts()
216+	if err != nil {
217+		panic(err)
218+	}
219+
220+	logger.Info("Adding posts from prose.sh")
221+	for _, post := range prosePosts {
222+		post.Space = "prose"
223+		err = InsertPost(tx, post)
224+		if err != nil {
225+			panic(err)
226+		}
227+	}
228+
229+	listPosts, err := listsDb.FindPosts()
230+	if err != nil {
231+		panic(err)
232+	}
233+
234+	logger.Info("Adding posts from lists.sh")
235+	for _, post := range listPosts {
236+		updated := false
237+		for _, alreadyAdded := range updateIDs {
238+			if post.UserID == alreadyAdded.User.ID {
239+				// we need to change the ID for these posts to the prose user id
240+				// because we were able to determine it was the same user
241+				post.UserID = alreadyAdded.ReplaceWithID
242+				post.Space = "lists"
243+				err = InsertPost(tx, post)
244+				if err != nil {
245+					fmt.Println(post.Filename)
246+					panic(err)
247+				}
248+				updated = true
249+				break
250+			}
251+		}
252+
253+		if updated {
254+			continue
255+		}
256+
257+		post.Space = "lists"
258+		err = InsertPost(tx, post)
259+		if err != nil {
260+			panic(err)
261+		}
262+	}
263+
264+	logger.Info("Committing transactions to PICO db")
265+	// Commit the transaction.
266+	/* if err = tx.Commit(); err != nil {
267+		panic(err)
268+	} */
269+}
A makefile
+79, -0 
 1@@ -0,0 +1,79 @@
 2+PGDATABASE?="pico"
 3+PGHOST?="db"
 4+PGUSER?="postgres"
 5+PORT?="5432"
 6+DB_CONTAINER?=pico-services_db_1
 7+
 8+test:
 9+	docker run --rm -v $(shell pwd):/app -w /app golangci/golangci-lint:latest golangci-lint run -E goimports -E godot
10+.PHONY: test
11+
12+build:
13+	go build -o build/web ./cmd/web
14+	go build -o build/ssh ./cmd/ssh
15+.PHONY: build
16+
17+format:
18+	go fmt ./...
19+.PHONY: format
20+
21+create:
22+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) < ./db/setup.sql
23+.PHONY: create
24+
25+teardown:
26+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/teardown.sql
27+.PHONY: teardown
28+
29+migrate:
30+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220310_init.sql
31+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220422_add_desc_to_user_and_post.sql
32+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220426_add_index_for_filename.sql
33+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220427_username_to_lower.sql
34+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220523_timestamp_with_tz.sql
35+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220721_analytics.sql
36+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220722_post_hidden.sql
37+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220727_post_change_post_contraints.sql
38+.PHONY: migrate
39+
40+latest:
41+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220727_post_change_post_contraints.sql
42+.PHONY: latest
43+
44+psql:
45+	docker exec -it $(DB_CONTAINER) psql -U $(PGUSER)
46+.PHONY: psql
47+
48+dump:
49+	docker exec -it $(DB_CONTAINER) pg_dump -U $(PGUSER) $(PGDATABASE) > ./backup.sql
50+.PHONY: dump
51+
52+restore:
53+	docker cp ./backup.sql $(DB_CONTAINER):/backup.sql
54+	docker exec -it $(DB_CONTAINER) /bin/bash
55+	# psql postgres -U postgres < /backup.sql
56+.PHONY: restore
57+
58+bp-caddy:
59+	docker build -t neurosnap/prose-caddy -f Dockerfile.caddy .
60+	docker push neurosnap/prose-caddy
61+.PHONY: bp-caddy
62+
63+bp-ssh:
64+	docker build -t neurosnap/prose-ssh --target ssh .
65+	docker push neurosnap/prose-ssh
66+.PHONY: bp-ssh
67+
68+bp-web:
69+	docker build -t neurosnap/prose-web --target web .
70+	docker push neurosnap/prose-web
71+.PHONY: bp-web
72+
73+bp: bp-ssh bp-web bp-caddy
74+.PHONY: bp
75+
76+deploy:
77+	docker system prune -f
78+	docker-compose -f production.yml pull --ignore-pull-failures
79+	docker-compose -f production.yml up --no-deps -d
80+.PHONY: deploy