PrivacyChain/script.sql at main · abmorte/PrivacyChain · GitHub

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
-- PrivacyChain framework database script
-- Name: mestrado-ifpb; Type: DATABASE; Schema: -; Owner: postgres

-- DROP DATABASE "mestrado-ifpb";
CREATE DATABASE "mestrado-ifpb"
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

\connect "mestrado-ifpb"

-- DROP SCHEMA "privacychain" ;
CREATE SCHEMA "privacychain"
    AUTHORIZATION postgres;

-- DROP TABLE "privacychain".tracking;
CREATE TABLE "privacychain".tracking
(
    tracking_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    canonical_data character varying COLLATE pg_catalog."default",
    anonymized_data character varying COLLATE pg_catalog."default",
    blockchain_id integer,
    transaction_id character varying COLLATE pg_catalog."default",
    salt character varying COLLATE pg_catalog."default",
    hash_method character varying COLLATE pg_catalog."default",
    tracking_dt timestamp without time zone DEFAULT now(),
    locator character varying COLLATE pg_catalog."default",
    CONSTRAINT tracking_pkey PRIMARY KEY (tracking_id)
)

TABLESPACE pg_default;

ALTER TABLE "privacychain".tracking
    OWNER to postgres;

COMMENT ON TABLE "privacychain".tracking
    IS 'Metadata repository for tracking personal data entered in the blockchain';

COMMENT ON COLUMN "privacychain".tracking.tracking_id
    IS 'Sequential code of the personal data tracking table';

COMMENT ON COLUMN "privacychain".tracking.canonical_data
    IS 'personal data in canonical format';

COMMENT ON COLUMN "privacychain".tracking.anonymized_data
    IS 'anonymized personal data';

COMMENT ON COLUMN "privacychain".tracking.blockchain_id
    IS 'blockchain identifier where anonymized personal data is persisted';

COMMENT ON COLUMN "privacychain".tracking.transaction_id
    IS 'transaction identifier on the blockchain where anonymized personal data is persisted';

COMMENT ON COLUMN "privacychain".tracking.salt
    IS '36-character random string generated using the uuid4 function - see RFC 4122';

COMMENT ON COLUMN "privacychain".tracking.hash_method
    IS 'String identifying the method used in the anonymization of personal data. Domain: MD5, SHA1, SHA256, SHA512';

COMMENT ON COLUMN "privacychain".tracking.tracking_dt
    IS 'timestamp identifying when the tuple is persisted in the database';

COMMENT ON COLUMN "privacychain".tracking.locator
    IS 'identification of entity holding personal data';