Rev 777 | Details | Compare with Previous | Last modification | View Log | RSS feed
| Rev | Author | Line No. | Line |
|---|---|---|---|
| 760 | blopes | 1 | /*==============================================================*/ |
| 2 | /* DBMS name: PostgreSQL 8 */ |
||
| 3 | /* Criado em: 22/11/2025 */ |
||
| 4 | /* Autor: Bruno Lopes Peixoto */ |
||
| 5 | /*==============================================================*/ |
||
| 6 | |||
| 7 | SET search_path = sc_slp; |
||
| 8 | |||
| 9 | CREATE SEQUENCE sq_pessoa |
||
| 10 | INCREMENT 1 |
||
| 11 | minvalue 1 |
||
| 12 | maxvalue 99999999 |
||
| 13 | START 1; |
||
| 14 | |||
| 15 | /*==============================================================*/ |
||
| 16 | /* Table: slp_pessoa */ |
||
| 17 | /*==============================================================*/ |
||
| 18 | CREATE TABLE slp_pessoa ( |
||
| 19 | seq_pessoa INT8 NOT NULL DEFAULT NEXTVAL('sc_slp.sq_pessoa'::regclass), |
||
| 20 | dsc_nome VARCHAR(120) NOT NULL, |
||
| 21 | ind_ativo BOOL NOT NULL, |
||
| 22 | CONSTRAINT pk_pessoa PRIMARY KEY (seq_pessoa) |
||
| 23 | ); |
||
| 24 | |||
| 25 | -- set table ownership |
||
| 26 | ALTER TABLE slp_pessoa owner TO sc_slp |
||
| 27 | ; |
||
| 28 | /*==============================================================*/ |
||
| 29 | /* Index: ix_pessoa */ |
||
| 30 | /*==============================================================*/ |
||
| 31 | CREATE UNIQUE INDEX ix_pessoa ON slp_pessoa ( |
||
| 32 | seq_pessoa |
||
| 33 | ); |
||
| 34 | |||
| 35 | CREATE SEQUENCE sq_usuario |
||
| 36 | INCREMENT 1 |
||
| 37 | minvalue 1 |
||
| 38 | maxvalue 99999999 |
||
| 39 | START 1; |
||
| 40 | |||
| 41 | /*==============================================================*/ |
||
| 42 | /* Table: slp_usuario */ |
||
| 43 | /*==============================================================*/ |
||
| 44 | CREATE TABLE slp_usuario ( |
||
| 45 | seq_usuario INT8 NOT NULL DEFAULT NEXTVAL('sc_slp.sq_usuario'::regclass), |
||
| 46 | seq_pessoa INT8 NULL, |
||
| 47 | dsc_login VARCHAR(32) NOT NULL, |
||
| 48 | dsc_nome VARCHAR(120) NULL, |
||
| 49 | dsc_senha VARCHAR(32) NOT NULL, |
||
| 50 | dth_ultimo_acesso TIMESTAMP NULL, |
||
| 51 | ind_ativo BOOL NOT NULL, |
||
| 52 | CONSTRAINT pk_usuario PRIMARY KEY (seq_usuario) |
||
| 53 | ); |
||
| 54 | |||
| 55 | -- set table ownership |
||
| 56 | ALTER TABLE slp_usuario owner TO labse_sc_lse |
||
| 57 | ; |
||
| 58 | /*==============================================================*/ |
||
| 59 | /* Index: ix_usuario */ |
||
| 60 | /*==============================================================*/ |
||
| 61 | CREATE UNIQUE INDEX ix_usuario ON slp_usuario ( |
||
| 62 | seq_usuario |
||
| 63 | ); |
||
| 64 | |||
| 65 | /*==============================================================*/ |
||
| 66 | /* Index: ix_usuario_pessoa */ |
||
| 67 | /*==============================================================*/ |
||
| 68 | CREATE INDEX ix_usuario_pessoa ON slp_usuario ( |
||
| 69 | seq_pessoa |
||
| 70 | ); |
||
| 71 | |||
| 72 | CREATE SEQUENCE sq_ativo |
||
| 73 | INCREMENT 1 |
||
| 74 | minvalue 1 |
||
| 75 | maxvalue 99999999 |
||
| 76 | START 1; |
||
| 77 | |||
| 78 | /*==============================================================*/ |
||
| 79 | /* Table: slp_ativo */ |
||
| 80 | /*==============================================================*/ |
||
| 81 | CREATE TABLE slp_ativo ( |
||
| 82 | seq_ativo INT8 NOT NULL DEFAULT NEXTVAL('sc_slp.sq_ativo'::regclass), |
||
| 83 | dsc_ativo VARCHAR(120) NULL, |
||
| 777 | blopes | 84 | tip_tendencia CHAR(1) NOT NULL |
| 85 | CONSTRAINT ck_tendencia_ativo CHECK (tip_tendencia IN ('A','B','N')), |
||
| 760 | blopes | 86 | ind_ativo BOOL NOT NULL, |
| 87 | CONSTRAINT pk_ativo PRIMARY KEY (seq_ativo) |
||
| 88 | ); |
||
| 89 | |||
| 777 | blopes | 90 | comment ON COLUMN slp_ativo.tip_tendencia IS |
| 91 | 'Valores possíveis: |
||
| 92 | A - Alta |
||
| 93 | B - Baixa |
||
| 94 | N - Não Definida'; |
||
| 95 | |||
| 760 | blopes | 96 | -- set table ownership |
| 97 | ALTER TABLE slp_ativo owner TO sc_slp |
||
| 98 | ; |
||
| 99 | /*==============================================================*/ |
||
| 100 | /* Index: ix_ativo */ |
||
| 101 | /*==============================================================*/ |
||
| 102 | CREATE UNIQUE INDEX ix_ativo ON slp_ativo ( |
||
| 103 | seq_ativo |
||
| 104 | ); |
||
| 105 | |||
| 106 | CREATE SEQUENCE sq_candle |
||
| 107 | INCREMENT 1 |
||
| 108 | minvalue 1 |
||
| 109 | maxvalue 99999999 |
||
| 110 | START 1; |
||
| 111 | |||
| 112 | /*==============================================================*/ |
||
| 113 | /* Table: slp_candle */ |
||
| 114 | /*==============================================================*/ |
||
| 115 | CREATE TABLE slp_candle ( |
||
| 116 | seq_candle INT8 NOT NULL DEFAULT NEXTVAL('sc_slp.sq_candle'::regclass), |
||
| 117 | seq_ativo INT8 NOT NULL, |
||
| 766 | blopes | 118 | val_contador INT8 NULL, |
| 760 | blopes | 119 | dth_datahora TIMESTAMP NOT NULL, |
| 120 | val_abertura NUMERIC(20,6) NOT NULL, |
||
| 121 | val_maxima NUMERIC(20,6) NOT NULL, |
||
| 122 | val_minima NUMERIC(20,6) NOT NULL, |
||
| 123 | val_fechamento NUMERIC(20,6) NOT NULL, |
||
| 124 | val_volume NUMERIC(20,6) NULL, |
||
| 125 | tip_periodo VARCHAR(2) NOT NULL, |
||
| 766 | blopes | 126 | ind_analisado BOOL NOT NULL, |
| 760 | blopes | 127 | CONSTRAINT pk_candle PRIMARY KEY (seq_candle) |
| 128 | ); |
||
| 129 | |||
| 130 | -- set table ownership |
||
| 131 | ALTER TABLE slp_candle owner TO sc_slp |
||
| 132 | ; |
||
| 133 | /*==============================================================*/ |
||
| 134 | /* Index: ix_candle */ |
||
| 135 | /*==============================================================*/ |
||
| 136 | CREATE UNIQUE INDEX ix_candle ON slp_candle ( |
||
| 137 | seq_candle |
||
| 138 | ); |
||
| 139 | |||
| 140 | /*==============================================================*/ |
||
| 141 | /* Index: ix_candle_ativo */ |
||
| 142 | /*==============================================================*/ |
||
| 143 | CREATE INDEX ix_candle_ativo ON slp_candle ( |
||
| 144 | seq_ativo |
||
| 145 | ); |
||
| 146 | |||
| 147 | ALTER TABLE slp_candle |
||
| 148 | ADD CONSTRAINT fk_candleativo_candle FOREIGN KEY (seq_ativo) |
||
| 149 | REFERENCES slp_ativo (seq_ativo) |
||
| 150 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 779 | blopes | 151 | |
| 152 | |||
| 153 | /*==============================================================*/ |
||
| 154 | /* Table: slp_sinal */ |
||
| 155 | /*==============================================================*/ |
||
| 156 | CREATE TABLE slp_sinal ( |
||
| 157 | cod_sinal VARCHAR(30) NOT NULL, |
||
| 158 | seq_candleref INT8 NOT NULL, |
||
| 159 | seq_candleg1 INT8 NULL, |
||
| 160 | seq_candleg2 INT8 NULL, |
||
| 161 | seq_candleg3 INT8 NULL, |
||
| 162 | seq_candleg4 INT8 NULL, |
||
| 163 | dth_sinal DATE NOT NULL, |
||
| 164 | tip_sinal CHAR(1) NOT NULL, |
||
| 165 | tip_situacao_sinal CHAR(1) NOT NULL, |
||
| 166 | val_entrada2 NUMERIC(10,2) NULL, |
||
| 167 | val_alvo1 NUMERIC(10,2) NULL, |
||
| 168 | val_entrada1 NUMERIC(10,2) NULL, |
||
| 169 | val_alvo2 NUMERIC(10,2) NULL, |
||
| 170 | val_stop1 NUMERIC(10,2) NULL, |
||
| 171 | val_stop2 NUMERIC(10,2) NULL, |
||
| 172 | CONSTRAINT pk_sinal PRIMARY KEY (cod_sinal) |
||
| 173 | ); |
||
| 760 | blopes | 174 | |
| 779 | blopes | 175 | -- set table ownership |
| 176 | ALTER TABLE slp_sinal owner TO sc_slp |
||
| 177 | ; |
||
| 178 | ALTER TABLE slp_sinal |
||
| 179 | ADD CONSTRAINT fk_sinal_candleg1 FOREIGN KEY (seq_candleg1) |
||
| 180 | REFERENCES slp_candle (seq_candle) |
||
| 181 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 760 | blopes | 182 | |
| 779 | blopes | 183 | ALTER TABLE slp_sinal |
| 184 | ADD CONSTRAINT fk_sinal_candleg2 FOREIGN KEY (seq_candleg2) |
||
| 185 | REFERENCES slp_candle (seq_candle) |
||
| 186 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 187 | |||
| 188 | ALTER TABLE slp_sinal |
||
| 189 | ADD CONSTRAINT fk_sinal_candleg3 FOREIGN KEY (seq_candleg3) |
||
| 190 | REFERENCES slp_candle (seq_candle) |
||
| 191 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 192 | |||
| 193 | ALTER TABLE slp_sinal |
||
| 194 | ADD CONSTRAINT fk_sinal_candleg4 FOREIGN KEY (seq_candleg4) |
||
| 195 | REFERENCES slp_candle (seq_candle) |
||
| 196 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 197 | |||
| 198 | ALTER TABLE slp_sinal |
||
| 199 | ADD CONSTRAINT fk_sinal_candleref FOREIGN KEY (seq_candleref) |
||
| 200 | REFERENCES slp_candle (seq_candle) |
||
| 201 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 202 | |||
| 203 | |||
| 204 | |||
| 760 | blopes | 205 | SET search_path = public; |
| 206 | |||
| 207 | |||
| 208 | -- PERMISSÕES |
||
| 209 | GRANT USAGE ON SCHEMA sc_slp TO sc_slp; |
||
| 210 | |||
| 211 | GRANT SELECT, INSERT, UPDATE, DELETE |
||
| 212 | ON ALL TABLES IN SCHEMA sc_slp |
||
| 213 | TO sc_slp; |
||
| 214 | |||
| 215 | ALTER DEFAULT PRIVILEGES IN SCHEMA sc_slp |
||
| 216 | GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sc_slp; |