39 lines
1.3 KiB
MySQL
39 lines
1.3 KiB
MySQL
|
CREATE OR REPLACE FUNCTION now_utc_micro_seconds() RETURNS BIGINT AS
|
||
|
|
$$
|
||
|
|
SELECT CAST(extract(EPOCH from now() at time zone 'utc') * 1000000 as BIGINT) ;
|
||
|
|
$$ language sql;
|
||
|
|
|
||
|
|
-- We can reuse this func to create triggers in other tables.
|
||
|
|
CREATE OR REPLACE FUNCTION trigger_updated_at_microseconds_column()
|
||
|
|
RETURNS TRIGGER AS
|
||
|
|
$$
|
||
|
|
BEGIN
|
||
|
|
NEW.updated_at = now_utc_micro_seconds();
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ language 'plpgsql';
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS location_tag
|
||
|
|
(
|
||
|
|
id uuid PRIMARY KEY NOT NULL,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
provider TEXT NOT NULL DEFAULT 'USER',
|
||
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
|
||
|
|
updated_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
|
||
|
|
encrypted_key TEXT NOT NULL,
|
||
|
|
key_decryption_nonce TEXT NOT NULL,
|
||
|
|
attributes JSONB NOT NULL,
|
||
|
|
CONSTRAINT fk_location_tag_user_id
|
||
|
|
FOREIGN KEY (user_id)
|
||
|
|
REFERENCES users (user_id)
|
||
|
|
ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TRIGGER update_location_tag_updated_at
|
||
|
|
BEFORE UPDATE
|
||
|
|
ON location_tag
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE PROCEDURE
|
||
|
|
trigger_updated_at_microseconds_column();
|