aboutsummaryrefslogtreecommitdiffstats
path: root/adenosine-pds/src/atp_db.sql
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2022-11-22 12:00:38 -0800
committerBryan Newbold <bnewbold@robocracy.org>2022-11-22 12:00:38 -0800
commit29175cf5183331ca401670203b35a5a2cc93f196 (patch)
tree70209d0fbd1cf7c0e297abf34a9e14d3ee2e2154 /adenosine-pds/src/atp_db.sql
parent35790cec824fb8d24ec93a8345f226f5367822ad (diff)
downloadadenosine-29175cf5183331ca401670203b35a5a2cc93f196.tar.gz
adenosine-29175cf5183331ca401670203b35a5a2cc93f196.zip
pds: SQL schema to use more ISO-style timestamps
Diffstat (limited to 'adenosine-pds/src/atp_db.sql')
-rw-r--r--adenosine-pds/src/atp_db.sql14
1 files changed, 7 insertions, 7 deletions
diff --git a/adenosine-pds/src/atp_db.sql b/adenosine-pds/src/atp_db.sql
index 9a6e30c..080eae7 100644
--- a/adenosine-pds/src/atp_db.sql
+++ b/adenosine-pds/src/atp_db.sql
@@ -7,7 +7,7 @@ CREATE TABLE account(
email TEXT NOT NULL,
password_bcrypt TEXT NOT NULL,
recovery_pubkey TEXT NOT NULL,
- created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') )
+ created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') )
);
CREATE UNIQUE INDEX account_handle_uniq_idx on account(lower(handle));
CREATE UNIQUE INDEX account_email_uniq_idx on account(lower(email));
@@ -16,13 +16,13 @@ CREATE TABLE did_doc(
did TEXT PRIMARY KEY NOT NULL,
-- TODO: handle TEXT NOT NULL,
doc_json TEXT NOT NULL,
- indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') )
+ indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') )
);
CREATE TABLE session(
did TEXT NOT NULL,
jwt TEXT NOT NULL,
- created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ),
+ created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') ),
PRIMARY KEY(did, jwt)
);
@@ -36,7 +36,7 @@ CREATE TABLE bsky_post(
reply_to_parent_uri TEXT,
reply_to_root_uri TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
- indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ),
+ indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') ),
PRIMARY KEY(did, tid)
);
CREATE INDEX bsky_post_reply_to_parent_uri_idx on bsky_post(reply_to_parent_uri);
@@ -50,7 +50,7 @@ CREATE TABLE bsky_ref(
-- TODO: NOT NULL on subject_cid
subject_cid TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
- indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ),
+ indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') ),
PRIMARY KEY(ref_type, did, tid)
);
CREATE INDEX bsky_ref_subject_uri_idx on bsky_ref(subject_uri);
@@ -62,7 +62,7 @@ CREATE TABLE bsky_follow(
-- TODO: NOT NULL on subject_cid
subject_cid TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
- indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ),
+ indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') ),
PRIMARY KEY(did, tid)
);
CREATE INDEX bsky_follow_subject_did_idx on bsky_follow(subject_did);
@@ -76,5 +76,5 @@ CREATE TABLE bsky_notification(
subject_cid TEXT,
reason TEXT NOT NULL,
seen_at TIMESTAMP WITH TIME ZONE,
- indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') )
+ indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%SZ', 'now') )
);