repos / pico

commit

221a7b7

parent

dae7622

author

Eric Bower

date

2022-08-03 00:01:20 +0000 UTC

feat: dates migration and reorg one-off scripts

Since we've been needing to perform more complex migrations that depend
on golang, I thought it would be a good idea to have a "home" for our
one-off scripts.

I like the idea of keeping them in git so we can reference them for
future scripts or just for historical reasons.

1@@ -68,7 +68,7 @@ latest:
2 .PHONY: latest
3 
4 psql:
5-	docker exec -it $(DB_CONTAINER) psql -U $(PGUSER)
6+	docker exec -it $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE)
7 .PHONY: psql
8 
9 dump:

  1@@ -0,0 +1,134 @@
  2+package main
  3+
  4+import (
  5+	"context"
  6+	"database/sql"
  7+	"log"
  8+	"os"
  9+	"time"
 10+
 11+	"git.sr.ht/~erock/pico/lists"
 12+	"git.sr.ht/~erock/pico/prose"
 13+	"git.sr.ht/~erock/pico/wish/cms/config"
 14+	"git.sr.ht/~erock/pico/wish/cms/db"
 15+	"git.sr.ht/~erock/pico/wish/cms/db/postgres"
 16+	"go.uber.org/zap"
 17+)
 18+
 19+func createLogger() *zap.SugaredLogger {
 20+	logger, err := zap.NewProduction()
 21+	if err != nil {
 22+		log.Fatal(err)
 23+	}
 24+
 25+	return logger.Sugar()
 26+}
 27+
 28+func findPosts(dbpool *sql.DB) ([]*db.Post, error) {
 29+	var posts []*db.Post
 30+	rs, err := dbpool.Query(`SELECT
 31+		id, user_id, filename, title, text, description,
 32+		created_at, publish_at, updated_at, hidden, cur_space
 33+		FROM posts
 34+		WHERE cur_space = 'prose' OR cur_space = 'lists'
 35+	`)
 36+	if err != nil {
 37+		return posts, err
 38+	}
 39+	for rs.Next() {
 40+		post := &db.Post{}
 41+		err := rs.Scan(
 42+			&post.ID,
 43+			&post.UserID,
 44+			&post.Filename,
 45+			&post.Title,
 46+			&post.Text,
 47+			&post.Description,
 48+			&post.CreatedAt,
 49+			&post.PublishAt,
 50+			&post.UpdatedAt,
 51+			&post.Hidden,
 52+			&post.Space,
 53+		)
 54+		if err != nil {
 55+			return posts, err
 56+		}
 57+
 58+		posts = append(posts, post)
 59+	}
 60+	if rs.Err() != nil {
 61+		return posts, rs.Err()
 62+	}
 63+	return posts, nil
 64+}
 65+
 66+func updateDates(tx *sql.Tx, postID string, date *time.Time) error {
 67+	_, err := tx.Exec("UPDATE posts SET publish_at = $1 WHERE id = $2", date, postID)
 68+	return err
 69+}
 70+
 71+func main() {
 72+	logger := createLogger()
 73+
 74+	picoCfg := config.NewConfigCms()
 75+	picoCfg.Logger = logger
 76+	picoCfg.DbURL = os.Getenv("DATABASE_URL")
 77+	picoDb := postgres.NewDB(picoCfg)
 78+
 79+	logger.Info("fetching all posts")
 80+	posts, err := findPosts(picoDb.Db)
 81+	if err != nil {
 82+		panic(err)
 83+	}
 84+	logger.Infof("found (%d) posts", len(posts))
 85+
 86+	ctx := context.Background()
 87+	tx, err := picoDb.Db.BeginTx(ctx, nil)
 88+	if err != nil {
 89+		panic(err)
 90+	}
 91+
 92+	defer func() {
 93+		tx.Rollback()
 94+	}()
 95+
 96+	datesFixed := []string{}
 97+	logger.Info("updating dates")
 98+	for _, post := range posts {
 99+		if post.Space == "prose" {
100+			parsed, err := prose.ParseText(post.Text)
101+			if err != nil {
102+				logger.Error(err)
103+				continue
104+			}
105+
106+			if parsed.PublishAt != nil && !parsed.PublishAt.IsZero() {
107+				err = updateDates(tx, post.ID, parsed.MetaData.PublishAt)
108+				if err != nil {
109+					logger.Error(err)
110+					continue
111+				}
112+
113+				if !parsed.MetaData.PublishAt.Equal(*post.PublishAt) {
114+					datesFixed = append(datesFixed, post.ID)
115+				}
116+			}
117+		} else if post.Space == "lists" {
118+			parsed := lists.ParseText(post.Text)
119+
120+			if parsed.MetaData.PublishAt != nil && !parsed.MetaData.PublishAt.IsZero() {
121+				err = updateDates(tx, post.ID, parsed.MetaData.PublishAt)
122+				if err != nil {
123+					logger.Error(err)
124+					continue
125+				}
126+				if !parsed.MetaData.PublishAt.Equal(*post.PublishAt) {
127+					datesFixed = append(datesFixed, post.ID)
128+				}
129+			}
130+		}
131+	}
132+
133+	tx.Commit()
134+	logger.Infof("(%d) dates fixed!", len(datesFixed))
135+}