forked from pocketbase/pocketbase
-
Notifications
You must be signed in to change notification settings - Fork 9
/
1640988000_init.go
73 lines (63 loc) · 2.3 KB
/
1640988000_init.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
package logs
import (
"github.com/AlperRehaYAZGAN/postgresbase/tools/migrate"
"github.com/pocketbase/dbx"
)
var LogsMigrations migrate.MigrationsList
func init() {
LogsMigrations.Register(func(db dbx.Builder) error {
_, err := db.NewQuery(`
CREATE OR REPLACE FUNCTION json_extract(json_data json, key text)
RETURNS text AS $$
BEGIN
RETURN json_data ->> key;
END;
$$ LANGUAGE plpgsql;
-- Create a new IMMUTABLE function that wraps date_trunc
CREATE OR REPLACE FUNCTION immutable_date_trunc(text, timestamp with time zone) RETURNS timestamp with time zone AS $$
BEGIN
RETURN date_trunc($1, $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE SEQUENCE IF NOT EXISTS global_id_seq;
CREATE OR REPLACE FUNCTION generate_snowflake(OUT result text) AS $$
DECLARE
our_epoch bigint := 1314220021721;
seq_id bigint;
now_millis bigint;
shard_id int := 5;
resultint bigint;
BEGIN
SELECT nextval('global_id_seq')::bigint % 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
resultint := (now_millis - our_epoch) << 23;
resultint := resultint | (shard_id <<10);
resultint := resultint | (seq_id);
-- convert result from bigint to text
result := resultint::text;
END;
$$ LANGUAGE PLPGSQL;
CREATE TABLE {{_requests}} (
[[id]] VARCHAR(32) PRIMARY KEY DEFAULT generate_snowflake() NOT NULL,
[[url]] TEXT DEFAULT '' NOT NULL,
[[method]] TEXT DEFAULT 'get' NOT NULL,
[[status]] INTEGER DEFAULT 200 NOT NULL,
[[auth]] TEXT DEFAULT 'guest' NOT NULL,
[[ip]] TEXT DEFAULT '127.0.0.1' NOT NULL,
[[referer]] TEXT DEFAULT '' NOT NULL,
[[userAgent]] TEXT DEFAULT '' NOT NULL,
[[meta]] JSON DEFAULT '{}' NOT NULL,
[[created]] TIMESTAMPTZ DEFAULT NOW() NOT NULL,
[[updated]] TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
CREATE INDEX _request_status_idx on {{_requests}} ([[status]]);
CREATE INDEX _request_auth_idx on {{_requests}} ([[auth]]);
CREATE INDEX _request_ip_idx on {{_requests}} ([[ip]]);
CREATE INDEX _request_created_hour_idx on {{_requests}} (immutable_date_trunc('hour', [[created]]));
`).Execute()
return err
}, func(db dbx.Builder) error {
_, err := db.DropTable("_requests").Execute()
return err
})
}