package models import ( "context" "database/sql" "database/sql/driver" "encoding/json" "errors" "fmt" "net/url" "regexp" "time" sq "github.com/Masterminds/squirrel" "github.com/segmentio/ksuid" "netlandish.com/x/gobwebs/database" "netlandish.com/x/gobwebs/timezone" ) // HTMLMeta holds a base URL meta data type HTMLMeta struct { Title string `json:"title"` Description string `json:"description"` Image string `json:"image"` SiteName string `json:"site_name"` } // BaseURLData holds metadata for the BaseURL model type BaseURLData struct { Meta HTMLMeta `json:"meta"` } // Value ... func (b BaseURLData) Value() (driver.Value, error) { return json.Marshal(b) } // Scan ... func (b *BaseURLData) Scan(value interface{}) error { d, ok := value.([]byte) if !ok { return errors.New("type assertion to []byte failed") } return json.Unmarshal(d, &b) } // GetBaseURLs ... func GetBaseURLs(ctx context.Context, opts *database.FilterOptions) ([]*BaseURL, error) { if opts == nil { opts = &database.FilterOptions{} } tz := timezone.ForContext(ctx) urls := make([]*BaseURL, 0) if err := database.WithTx(ctx, database.TxOptionsRO, func(tx *sql.Tx) error { q := opts.GetBuilder(nil) rows, err := q. Columns("b.id", "b.url", "b.title", "b.counter", "b.data", "b.public_ready", "b.hash", "b.created_on", "json_agg(t)::jsonb"). From("base_urls b"). LeftJoin("org_links ol ON ol.base_url_id = b.id"). LeftJoin("tag_links tl ON tl.org_link_id = ol.id"). LeftJoin("tags t ON t.id = tl.tag_id"). GroupBy("b.id"). Distinct(). PlaceholderFormat(sq.Dollar). RunWith(tx). QueryContext(ctx) if err != nil { if err == sql.ErrNoRows { return nil } return err } defer rows.Close() for rows.Next() { var url BaseURL var tags string if err = rows.Scan(&url.ID, &url.URL, &url.Title, &url.Counter, &url.Data, &url.PublicReady, &url.Hash, &url.CreatedOn, &tags); err != nil { return err } re := regexp.MustCompile(`(,\s)?null,?`) tags = re.ReplaceAllString(tags, "") if tags != "[]" { err = json.Unmarshal([]byte(tags), &url.Tags) if err != nil { return err } } err = url.ToLocalTZ(tz) if err != nil { return err } urls = append(urls, &url) } return nil }); err != nil { return nil, err } return urls, nil } // GetBaseURL ... func GetBaseURL(ctx context.Context, id int) (*BaseURL, error) { b := &BaseURL{ID: id} err := b.Load(ctx) return b, err } // Load ... func (b *BaseURL) Load(ctx context.Context) error { if b.ID == 0 { return fmt.Errorf("No BaseURL ID was given") } tz := timezone.ForContext(ctx) err := database.WithTx(ctx, database.TxOptionsRO, func(tx *sql.Tx) error { err := sq. Select("id", "title", "url", "counter", "data", "public_ready", "hash", "created_on"). From("base_urls"). Where("id = ?", b.ID). PlaceholderFormat(sq.Dollar). RunWith(tx). ScanContext(ctx, &b.ID, &b.Title, &b.URL, &b.Counter, &b.Data, &b.PublicReady, &b.Hash, &b.CreatedOn) if err != nil { if err == sql.ErrNoRows { return nil } return err } err = b.ToLocalTZ(tz) return err }) return err } // Store will save an invoice to the db func (b *BaseURL) Store(ctx context.Context) error { err := database.WithTx(ctx, nil, func(tx *sql.Tx) error { var err error if b.ID == 0 { if b.Hash == "" { b.Hash = ksuid.New().String() } row := tx.QueryRowContext(ctx, ` WITH ins AS ( INSERT INTO base_urls (url, hash) VALUES ($1, $2) ON CONFLICT (url) DO UPDATE SET url = NULL WHERE FALSE RETURNING id, url, public_ready, created_on ) SELECT id, url, public_ready, created_on FROM ins UNION ALL SELECT id, url, public_ready, created_on FROM base_urls WHERE url = $1 LIMIT 1;`, b.URL, b.Hash) err := row.Scan(&b.ID, &b.URL, &b.PublicReady, &b.CreatedOn) if err != nil { return err } } else { err = sq. Update("base_urls"). Set("title", b.Title). Set("data", b.Data). Set("public_ready", b.PublicReady). Set("hash", b.Hash). Where("id = ?", b.ID). Suffix(`RETURNING (updated_on)`). PlaceholderFormat(sq.Dollar). RunWith(tx). ScanContext(ctx, &b.UpdatedOn) } return err }) return err } // Delete ... func (b *BaseURL) Delete(ctx context.Context) error { if b.ID == 0 { return fmt.Errorf("BaseURL object is not populated") } err := database.WithTx(ctx, nil, func(tx *sql.Tx) error { _, err := sq. Delete("base_urls"). Where("id = ?", b.ID). PlaceholderFormat(sq.Dollar). RunWith(tx). ExecContext(ctx) return err }) return err } // ToLocalTZ convert UTC date to local tz func (b *BaseURL) ToLocalTZ(tz string) error { loc, err := time.LoadLocation(tz) if err != nil { return err } b.CreatedOn = b.CreatedOn.In(loc) return nil } // UpdateCounter ... func (b *BaseURL) UpdateCounter(ctx context.Context, add bool) error { // XXX Probably should change this to just use a subselect count(*) op := "+" if !add { op = "-" } query := fmt.Sprintf(` UPDATE base_urls SET counter = counter%s1 WHERE id = $1 RETURNING (updated_on) `, op) err := database.WithTx(ctx, nil, func(tx *sql.Tx) error { row := tx.QueryRowContext(ctx, query, b.ID) return row.Scan(&b.UpdatedOn) }) return err } // TagsToString will convert linked tags to a comma separated string (used in forms) func (b *BaseURL) TagsToString() string { return TagsToString(b.Tags) } // QueryParams will return a URL that can auto add information to easily save this BaseURL // to a users bookmarks func (b *BaseURL) QueryParams() url.Values { qs := url.Values{} qs.Set("burlid", b.Hash) return qs }