From 1179ecd13d68e76d74ad94e2d3ca22d9681eeffe Mon Sep 17 00:00:00 2001 From: Evan Prodromou Date: Sat, 7 Mar 2009 12:55:09 -0800 Subject: Fix nonce usage in OAuth store The OAuth store was failing on getting a request token, because the token value was forced to be non-null in the DB. Let this value be null, and use the correct primary key (consumer, timestamp, nonce). Drop the reference to token table, and don't ever use it. --- db/laconica.sql | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'db') diff --git a/db/laconica.sql b/db/laconica.sql index c2cd887de..098fa4fd1 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -181,15 +181,14 @@ create table token ( create table nonce ( consumer_key varchar(255) not null comment 'unique identifier, root URL', - tok char(32) not null comment 'identifying value', + tok char(32) null comment 'buggy old value, ignored', nonce char(32) not null comment 'nonce', ts datetime not null comment 'timestamp sent', created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified', - constraint primary key (consumer_key, tok, nonce), - constraint foreign key (consumer_key, tok) references token (consumer_key, tok) + constraint primary key (consumer_key, ts, nonce) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; /* One-to-many relationship of user to openid_url */ -- cgit v1.2.3-54-g00ecf From a4091c878a923d7a0919098665906225ebeeb136 Mon Sep 17 00:00:00 2001 From: CiaranG Date: Sat, 7 Mar 2009 23:28:59 +0000 Subject: PostgreSQL - propogated nonce table fix from MySQL version - see bug #1251 or 1179ecd13d68e76d74ad94e2d3ca22d9681eeffe --- db/laconica_pg.sql | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'db') diff --git a/db/laconica_pg.sql b/db/laconica_pg.sql index 2d83f784a..d9e0c6da7 100644 --- a/db/laconica_pg.sql +++ b/db/laconica_pg.sql @@ -180,14 +180,13 @@ create table token ( create table nonce ( consumer_key varchar(255) not null /* comment 'unique identifier, root URL' */, tok char(32) not null /* comment 'identifying value' */, - nonce char(32) not null /* comment 'nonce' */, + nonce char(32) null /* comment 'buggy old value, ignored */, ts integer not null /* comment 'timestamp sent' values are epoch, and only used internally */, created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */, modified timestamp /* comment 'date this record was modified' */, - primary key (consumer_key, tok, nonce), - foreign key (consumer_key, tok) references token (consumer_key, tok) + primary key (consumer_key, ts, nonce) ); /* One-to-many relationship of user to openid_url */ -- cgit v1.2.3-54-g00ecf From a5f11248305f1ec66eee215e9e12ba010ed97691 Mon Sep 17 00:00:00 2001 From: CiaranG Date: Sun, 8 Mar 2009 15:51:31 +0000 Subject: PostgreSQL - use the specific sequence names required by DB_DataObject, otherwise rebuilding can't work --- db/laconica_pg.sql | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-) (limited to 'db') diff --git a/db/laconica_pg.sql b/db/laconica_pg.sql index d9e0c6da7..f879d7936 100644 --- a/db/laconica_pg.sql +++ b/db/laconica_pg.sql @@ -1,7 +1,8 @@ /* local and remote users have profiles */ +create sequence profile_seq; create table profile ( - id serial primary key /* comment 'unique identifier' */, + id bigint default nextval('profile_seq') primary key /* comment 'unique identifier' */, nickname varchar(64) not null /* comment 'nickname or username' */, fullname varchar(255) /* comment 'display name' */, profileurl varchar(255) /* comment 'URL, cached so we dont regenerate' */, @@ -30,8 +31,9 @@ create table avatar ( ); create index avatar_profile_id_idx on avatar using btree(profile_id); +create sequence sms_carrier_seq; create table sms_carrier ( - id serial primary key /* comment 'primary key for SMS carrier' */, + id bigint default nextval('sms_carrier_seq') primary key /* comment 'primary key for SMS carrier' */, name varchar(64) unique /* comment 'name of the carrier' */, email_pattern varchar(255) not null /* comment 'sprintf pattern for making an email address from a phone number' */, created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */, @@ -101,9 +103,10 @@ create table subscription ( create index subscription_subscriber_idx on subscription using btree(subscriber); create index subscription_subscribed_idx on subscription using btree(subscribed); +create sequence notice_seq; create table notice ( - id serial primary key /* comment 'unique identifier' */, + id bigint default nextval('notice_seq') primary key /* comment 'unique identifier' */, profile_id integer not null /* comment 'who made the update' */ references profile (id) , uri varchar(255) unique /* comment 'universally unique identifier, usually a tag URI' */, content varchar(140) /* comment 'update content' */, @@ -317,9 +320,10 @@ create table invitation ( create index invitation_address_idx on invitation using btree(address,address_type); create index invitation_user_id_idx on invitation using btree(user_id); +create sequence message_seq; create table message ( - id serial primary key /* comment 'unique identifier' */, + id bigint default nextval('message_seq') primary key /* comment 'unique identifier' */, uri varchar(255) unique /* comment 'universally unique identifier' */, from_profile integer not null /* comment 'who the message is from' */ references profile (id), to_profile integer not null /* comment 'who the message is to' */ references profile (id), @@ -367,9 +371,10 @@ create table profile_block ( ); +create sequence user_group_seq; create table user_group ( - id serial primary key /* comment 'unique identifier' */, + id bigint default nextval('user_group_seq') primary key /* comment 'unique identifier' */, nickname varchar(64) unique /* comment 'nickname for addressing' */, fullname varchar(255) /* comment 'display name' */, -- cgit v1.2.3-54-g00ecf