Details | Last modification | View Log | RSS feed
| Rev | Author | Line No. | Line |
|---|---|---|---|
| 1 | espaco | 1 | /*========tur======================================================*/ |
| 2 | /* DBMS name: PostgreSQL 8 */ |
||
| 3 | /* Criado em: 18/12/2014 */ |
||
| 4 | /* Autor: Bruno Lopes Peixoto */ |
||
| 5 | /*==============================================================*/ |
||
| 6 | |||
| 7 | SET search_path = sc_sec; |
||
| 8 | |||
| 9 | ALTER TABLE sec_usuario ADD COLUMN ind_financeiro BOOL; |
||
| 10 | UPDATE sec_usuario SET ind_financeiro=TRUE; |
||
| 11 | ALTER TABLE sec_usuario ALTER COLUMN ind_financeiro SET NOT NULL; |
||
| 12 | |||
| 13 | ALTER TABLE sec_forma_pagamento RENAME seq_formapagament TO cod_formapagament; |
||
| 14 | ALTER TABLE sec_venda_formapagament RENAME seq_formapagament TO cod_formapagament; |
||
| 15 | |||
| 16 | ALTER TABLE sec_forma_pagamento ALTER COLUMN cod_formapagament SET DEFAULT NULL; |
||
| 17 | |||
| 18 | DROP SEQUENCE sq_formapagament; |
||
| 19 | |||
| 20 | /* NOVOS */ |
||
| 21 | |||
| 22 | CREATE SEQUENCE sq_contaapagar |
||
| 23 | INCREMENT 1 |
||
| 24 | minvalue 1 |
||
| 25 | maxvalue 99999999 |
||
| 26 | START 1; |
||
| 27 | ALTER TABLE sq_contaapagar owner TO sc_sec; |
||
| 28 | |||
| 29 | CREATE SEQUENCE sq_contaareceber |
||
| 30 | INCREMENT 1 |
||
| 31 | minvalue 1 |
||
| 32 | maxvalue 99999999 |
||
| 33 | START 1; |
||
| 34 | ALTER TABLE sq_contaareceber owner TO sc_sec; |
||
| 35 | |||
| 36 | CREATE SEQUENCE sq_histconta |
||
| 37 | INCREMENT 1 |
||
| 38 | minvalue 1 |
||
| 39 | maxvalue 99999999 |
||
| 40 | START 1; |
||
| 41 | ALTER TABLE sq_histconta owner TO sc_sec; |
||
| 42 | |||
| 43 | CREATE SEQUENCE sq_parcela |
||
| 44 | INCREMENT 1 |
||
| 45 | minvalue 1 |
||
| 46 | maxvalue 99999999 |
||
| 47 | START 1; |
||
| 48 | ALTER TABLE sq_parcela owner TO sc_sec; |
||
| 49 | |||
| 50 | CREATE SEQUENCE sq_responsavel |
||
| 51 | INCREMENT 1 |
||
| 52 | minvalue 1 |
||
| 53 | maxvalue 99999999 |
||
| 54 | START 1; |
||
| 55 | ALTER TABLE sq_responsavel owner TO sc_sec; |
||
| 56 | |||
| 57 | /*==============================================================*/ |
||
| 58 | /* Table: sec_conta_a_pagar */ |
||
| 59 | /*==============================================================*/ |
||
| 60 | CREATE TABLE sec_conta_a_pagar ( |
||
| 61 | seq_conta_a_pagar INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_contaapagar'::regclass), |
||
| 62 | seq_responsavel INT8 NULL, |
||
| 63 | dsc_observacao VARCHAR(120) NULL, |
||
| 64 | tip_frequencia CHAR(1) NOT NULL |
||
| 65 | CONSTRAINT ck_contaapagar_frequencia CHECK (tip_frequencia IN ('U','D','S','M','A')), |
||
| 66 | ind_ativo BOOL NOT NULL, |
||
| 67 | CONSTRAINT pk_contaapagar PRIMARY KEY (seq_conta_a_pagar) |
||
| 68 | ); |
||
| 69 | |||
| 70 | comment ON COLUMN sec_conta_a_pagar.tip_frequencia IS |
||
| 71 | 'Valores possíveis: |
||
| 72 | U - Única Vez |
||
| 73 | D - Diariamente |
||
| 74 | S - Semanal |
||
| 75 | M - Mensal |
||
| 76 | A - Anual'; |
||
| 77 | |||
| 78 | -- set table ownership |
||
| 79 | ALTER TABLE sec_conta_a_pagar owner TO sc_sec |
||
| 80 | ; |
||
| 81 | /*==============================================================*/ |
||
| 82 | /* Index: ix_contaapagar_responsavel */ |
||
| 83 | /*==============================================================*/ |
||
| 84 | CREATE INDEX ix_contaapagar_responsavel ON sec_conta_a_pagar ( |
||
| 85 | seq_responsavel |
||
| 86 | ); |
||
| 87 | |||
| 88 | /*==============================================================*/ |
||
| 89 | /* Table: sec_conta_a_receber */ |
||
| 90 | /*==============================================================*/ |
||
| 91 | CREATE TABLE sec_conta_a_receber ( |
||
| 92 | seq_conta_a_receber INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_contaareceber'::regclass), |
||
| 93 | seq_responsavel INT8 NULL DEFAULT NEXTVAL('sc_sec.sq_responsavel'::regclass), |
||
| 94 | seq_venda INT8 NULL DEFAULT NEXTVAL('sc_sec.sq_venda'::regclass), |
||
| 95 | dsc_observacao VARCHAR(120) NULL, |
||
| 96 | ind_ativo BOOL NOT NULL, |
||
| 97 | CONSTRAINT pk_contaareceber PRIMARY KEY (seq_conta_a_receber) |
||
| 98 | ); |
||
| 99 | |||
| 100 | -- set table ownership |
||
| 101 | ALTER TABLE sec_conta_a_receber owner TO sc_sec |
||
| 102 | ; |
||
| 103 | /*==============================================================*/ |
||
| 104 | /* Index: ix_contaareceber_venda */ |
||
| 105 | /*==============================================================*/ |
||
| 106 | CREATE INDEX ix_contaareceber_venda ON sec_conta_a_receber ( |
||
| 107 | seq_venda |
||
| 108 | ); |
||
| 109 | |||
| 110 | /*==============================================================*/ |
||
| 111 | /* Index: ix_contaareceber_responsavel */ |
||
| 112 | /*==============================================================*/ |
||
| 113 | CREATE INDEX ix_contaareceber_responsavel ON sec_conta_a_receber ( |
||
| 114 | seq_responsavel |
||
| 115 | ); |
||
| 116 | |||
| 117 | /*==============================================================*/ |
||
| 118 | /* Table: sec_conta_bancaria */ |
||
| 119 | /*==============================================================*/ |
||
| 120 | CREATE TABLE sec_conta_bancaria ( |
||
| 121 | cod_conta_bancaria INT8 NOT NULL, |
||
| 122 | dsc_descricao VARCHAR(120) NULL, |
||
| 123 | val_saldo DECIMAL(10,2) NULL, |
||
| 124 | ind_ativo BOOL NOT NULL, |
||
| 125 | CONSTRAINT pk_contabancaria PRIMARY KEY (cod_conta_bancaria) |
||
| 126 | ); |
||
| 127 | |||
| 128 | -- set table ownership |
||
| 129 | ALTER TABLE sec_conta_bancaria owner TO sc_sec |
||
| 130 | ; |
||
| 131 | |||
| 132 | /*==============================================================*/ |
||
| 133 | /* Table: sec_hist_conta */ |
||
| 134 | /*==============================================================*/ |
||
| 135 | CREATE TABLE sec_hist_conta ( |
||
| 136 | seq_hist_conta INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_histconta'::regclass), |
||
| 137 | seq_conta_a_receber INT8 NULL, |
||
| 138 | seq_conta_a_pagar INT8 NULL, |
||
| 139 | ind_ativo BOOL NOT NULL, |
||
| 140 | CONSTRAINT pk_histconta PRIMARY KEY (seq_hist_conta) |
||
| 141 | ); |
||
| 142 | |||
| 143 | -- set table ownership |
||
| 144 | ALTER TABLE sec_hist_conta owner TO sc_sec |
||
| 145 | ; |
||
| 146 | /*==============================================================*/ |
||
| 147 | /* Index: ix_histconta_contaareceber */ |
||
| 148 | /*==============================================================*/ |
||
| 149 | CREATE INDEX ix_histconta_contaareceber ON sec_hist_conta ( |
||
| 150 | seq_conta_a_receber |
||
| 151 | ); |
||
| 152 | |||
| 153 | /*==============================================================*/ |
||
| 154 | /* Index: ix_histconta_contaapagar */ |
||
| 155 | /*==============================================================*/ |
||
| 156 | CREATE INDEX ix_histconta_contaapagar ON sec_hist_conta ( |
||
| 157 | seq_conta_a_pagar |
||
| 158 | ); |
||
| 159 | |||
| 160 | /*==============================================================*/ |
||
| 161 | /* Table: sec_parcela */ |
||
| 162 | /*==============================================================*/ |
||
| 163 | CREATE TABLE sec_parcela ( |
||
| 164 | seq_parcela INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_parcela'::regclass), |
||
| 165 | seq_conta_a_receber INT8 NULL, |
||
| 166 | seq_conta_a_pagar INT8 NULL, |
||
| 167 | cod_conta_bancaria INT8 NULL, |
||
| 168 | dth_emissao TIMESTAMP NOT NULL, |
||
| 169 | dat_vencimento DATE NULL, |
||
| 170 | dat_pagamento DATE NULL, |
||
| 171 | val_valor DECIMAL(10,2) NULL, |
||
| 172 | dsc_observacao VARCHAR(120) NULL, |
||
| 173 | ind_ativo BOOL NOT NULL, |
||
| 174 | CONSTRAINT pk_parcela PRIMARY KEY (seq_parcela) |
||
| 175 | ); |
||
| 176 | |||
| 177 | -- set table ownership |
||
| 178 | ALTER TABLE sec_parcela owner TO sc_sec |
||
| 179 | ; |
||
| 180 | /*==============================================================*/ |
||
| 181 | /* Index: ix_parcela_contabancaria */ |
||
| 182 | /*==============================================================*/ |
||
| 183 | CREATE INDEX ix_parcela_contabancaria ON sec_parcela ( |
||
| 184 | cod_conta_bancaria |
||
| 185 | ); |
||
| 186 | |||
| 187 | /*==============================================================*/ |
||
| 188 | /* Index: ix_parcela_contaareceber */ |
||
| 189 | /*==============================================================*/ |
||
| 190 | CREATE INDEX ix_parcela_contaareceber ON sec_parcela ( |
||
| 191 | seq_conta_a_receber |
||
| 192 | ); |
||
| 193 | |||
| 194 | /*==============================================================*/ |
||
| 195 | /* Index: ix_parcela_contaapagar */ |
||
| 196 | /*==============================================================*/ |
||
| 197 | CREATE INDEX ix_parcela_contaapagar ON sec_parcela ( |
||
| 198 | seq_conta_a_pagar |
||
| 199 | ); |
||
| 200 | |||
| 201 | |||
| 202 | /*==============================================================*/ |
||
| 203 | /* Table: sec_responsavel */ |
||
| 204 | /*==============================================================*/ |
||
| 205 | CREATE TABLE sec_responsavel ( |
||
| 206 | seq_responsavel INT8 NOT NULL DEFAULT NEXTVAL('sc_sec.sq_responsavel'::regclass), |
||
| 207 | nom_responsavel VARCHAR(120) NULL, |
||
| 208 | ind_ativo BOOL NOT NULL, |
||
| 209 | CONSTRAINT pk_responsavel PRIMARY KEY (seq_responsavel) |
||
| 210 | ); |
||
| 211 | |||
| 212 | -- set table ownership |
||
| 213 | ALTER TABLE sec_responsavel owner TO sc_sec |
||
| 214 | ; |
||
| 215 | |||
| 216 | ALTER TABLE sec_conta_a_pagar |
||
| 217 | ADD CONSTRAINT fk_contaapagar_responsavel FOREIGN KEY (seq_responsavel) |
||
| 218 | REFERENCES sec_responsavel (seq_responsavel) |
||
| 219 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 220 | |||
| 221 | ALTER TABLE sec_conta_a_receber |
||
| 222 | ADD CONSTRAINT fk_contaareceber_responsavel FOREIGN KEY (seq_responsavel) |
||
| 223 | REFERENCES sec_responsavel (seq_responsavel) |
||
| 224 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 225 | |||
| 226 | ALTER TABLE sec_conta_a_receber |
||
| 227 | ADD CONSTRAINT fk_contaareceber_venda FOREIGN KEY (seq_venda) |
||
| 228 | REFERENCES sec_venda (seq_venda) |
||
| 229 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 230 | |||
| 231 | ALTER TABLE sec_hist_conta |
||
| 232 | ADD CONSTRAINT fk_histconta_contaapagar FOREIGN KEY (seq_conta_a_pagar) |
||
| 233 | REFERENCES sec_conta_a_pagar (seq_conta_a_pagar) |
||
| 234 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 235 | |||
| 236 | ALTER TABLE sec_hist_conta |
||
| 237 | ADD CONSTRAINT fk_histconta_contaareceber FOREIGN KEY (seq_conta_a_receber) |
||
| 238 | REFERENCES sec_conta_a_receber (seq_conta_a_receber) |
||
| 239 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 240 | |||
| 241 | ALTER TABLE sec_parcela |
||
| 242 | ADD CONSTRAINT fk_parcela_contaapagar FOREIGN KEY (seq_conta_a_pagar) |
||
| 243 | REFERENCES sec_conta_a_pagar (seq_conta_a_pagar) |
||
| 244 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 245 | |||
| 246 | ALTER TABLE sec_parcela |
||
| 247 | ADD CONSTRAINT fk_parcela_contaareceber FOREIGN KEY (seq_conta_a_receber) |
||
| 248 | REFERENCES sec_conta_a_receber (seq_conta_a_receber) |
||
| 249 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 250 | |||
| 251 | ALTER TABLE sec_parcela |
||
| 252 | ADD CONSTRAINT fk_parcela_contabancaria FOREIGN KEY (cod_conta_bancaria) |
||
| 253 | REFERENCES sec_conta_bancaria (cod_conta_bancaria) |
||
| 254 | ON DELETE RESTRICT ON UPDATE RESTRICT; |
||
| 255 | |||
| 256 | INSERT INTO sc_sec.sec_conta_bancaria(cod_conta_bancaria, dsc_descricao, val_saldo, ind_ativo) VALUES (1, 'BANCO', 0.0, TRUE); |
||
| 257 | INSERT INTO sc_sec.sec_conta_bancaria(cod_conta_bancaria, dsc_descricao, val_saldo, ind_ativo) VALUES (2, 'BRUNO', 0.0, TRUE); |
||
| 258 | INSERT INTO sc_sec.sec_conta_bancaria(cod_conta_bancaria, dsc_descricao, val_saldo, ind_ativo) VALUES (3, 'CAIXA', 0.0, TRUE); |
||
| 259 | |||
| 260 | SET search_path = public; |