src/update.sql
author Krista Bennett <krista@pep-project.org>
Fri, 01 Jun 2018 10:30:19 +0200
branchENGINE-254
changeset 2723 7194b9c8599d
parent 11 3ac4c77f987a
permissions -rw-r--r--
close branch
vb@11
     1
create table trust (
vb@1
     2
   user_id text references person (id) on delete cascade,
vb@1
     3
   pgp_keypair_fpr text references pgp_keypair (fpr) on delete cascade,
vb@1
     4
   comm_type integer not null,
vb@1
     5
   comment text
vb@1
     6
);
vb@1
     7
vb@1
     8
create unique index trust_index on trust (
vb@1
     9
   user_id,
vb@1
    10
   pgp_keypair_fpr
vb@1
    11
);
vb@1
    12
vb@1
    13
insert into trust (user_id, pgp_keypair_fpr, comm_type)
vb@1
    14
    select user_id, main_key_id, comm_type from identity;
vb@1
    15
vb@1
    16
alter table identity rename to identity_old;
vb@1
    17
vb@1
    18
create table identity (
vb@11
    19
    address text primary key,
vb@11
    20
    user_id text
vb@11
    21
        references person (id)
vb@11
    22
        on delete cascade,
vb@11
    23
    main_key_id text
vb@11
    24
        references pgp_keypair (fpr)
vb@11
    25
        on delete set null,
vb@11
    26
    comment text
vb@1
    27
);
vb@1
    28
vb@1
    29
insert into identity (address, user_id, main_key_id)
vb@1
    30
    select address, user_id, main_key_id from identity_old;
vb@1
    31
vb@1
    32
drop table identity_old;