ThQPd9LC

1doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]
2
3
4
5
6                                                              
7
8
9                    Application Migrations                    
10
11
12                                                              
13
14
15
16
17Migrating up to 20191009125712 from 0
18
19
20
21
22  ++ migrating 20191009125712
23
24
25
26
27     -> CREATE SEQUENCE accounts_id_seq INCREMENT BY 1 MINVALUE 1 START 1
28
29
30     -> alter sequence accounts_id_seq owner to intercon_user;
31
32
33     -> CREATE SEQUENCE AP_V_USER_OFFICE_GOODS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
34
35
36     -> alter sequence AP_V_USER_OFFICE_GOODS_id_seq owner to intercon_user;
37
38
39     -> CREATE SEQUENCE ap_v_services_id_seq INCREMENT BY 1 MINVALUE 1 START 1
40
41
42     -> alter sequence ap_v_services_id_seq owner to intercon_user;
43
44
45     -> CREATE SEQUENCE AP_V_PERSONS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
46
47
48     -> alter sequence AP_V_PERSONS_id_seq owner to intercon_user;
49
50
51     -> CREATE SEQUENCE sessions_id_seq INCREMENT BY 1 MINVALUE 1 START 1
52
53
54     -> alter sequence sessions_id_seq owner to intercon_user;
55
56
57     -> create table if not exists AP_V_SERVICES
58
59
60(
61
62
63    N_SUBJ_SERV_ID         numeric,
64
65
66    N_SUBJ_SERV_TYPE_ID    numeric,
67
68
69    N_SERVICE_ID           numeric,
70
71
72    N_OBJECT_ID            numeric,
73
74
75    N_SUBJECT_ID           numeric,
76
77
78    VC_SERVICE             VARCHAR(255),
79
80
81    VC_OBJECT              VARCHAR(255),
82
83
84    N_OBJ_GOOD_ID          numeric,
85
86
87    VC_OBJ_GOOD            VARCHAR(255),
88
89
90    N_OBJ_ADDRESS_ID       numeric,
91
92
93    N_PORT_NO              numeric,
94
95
96    VC_LOGIN               VARCHAR(255),
97
98
99    VC_LOGIN_REAL          VARCHAR(255),
100
101
102    C_FL_EMPTY_PASS        VARCHAR(255),
103
104
105    VC_VALUE               VARCHAR(255),
106
107
108    N_VALUE                numeric,
109
110
111    C_CANT_CHANGE          CHAR,
112
113
114    N_SERV_AUTH_TYPE       numeric,
115
116
117    N_AUTH_TYPE_ID         numeric,
118
119
120    C_FL_APP               VARCHAR(255),
121
122
123    C_FL_PASS_TESTING      VARCHAR(255),
124
125
126    C_FL_PLAINTEXT_PASS    CHAR,
127
128
129    N_DEFAULT_HASH_TYPE_ID numeric,
130
131
132    VC_PASSWORD_PATTERN    VARCHAR(255),
133
134
135    password               varchar(180),
136
137
138    roles                  json
139
140
141);
142
143
144     -> 
145
146
147alter table AP_V_SERVICES
148
149
150    owner to intercon_user;
151
152
153     -> create table if not exists AP_V_PERSONAL_ACCOUNTS
154
155
156(
157
158
159    N_SUBJECT_ID                 numeric,
160
161
162    VC_SUBJ_CODE                 varchar(255),
163
164
165    VC_SUBJ_NAME                 varchar(255),
166
167
168    N_ACCOUNT_ID                 numeric,
169
170
171    N_CURRENCY_ID                numeric,
172
173
174    VC_CURRENCY                  varchar(255),
175
176
177    VC_CURRENCY_CODE             varchar(255),
178
179
180    VC_CODE                      varchar(255),
181
182
183    VC_NAME                      varchar(255),
184
185
186    VC_ACCOUNT                   varchar(255),
187
188
189    N_OVERDRAFT                  numeric,
190
191
192    D_OVERDRAFT_END              DATE,
193
194
195    N_PERMANENT_OVERDRAFT        numeric,
196
197
198    N_TEMPORAL_OVERDRAFT         numeric,
199
200
201    D_TEMP_OVERDRAFT_END         DATE,
202
203
204    N_SCHED_SERV_OVERDRAFT       numeric,
205
206
207    D_SCHED_SERV_OVERDRAFT_END   DATE,
208
209
210    N_UNSCHED_SERV_OVERDRAFT     numeric,
211
212
213    D_UNSCHED_SERV_OVERDRAFT_END DATE,
214
215
216    N_SUM_BAL                    numeric,
217
218
219    N_SUM_RESERVED               numeric,
220
221
222    N_SUM_RESERVED_CUR           numeric,
223
224
225    N_SUM_FREE                   numeric,
226
227
228    D_ACCOUNTING_BEGIN           DATE,
229
230
231    D_ACCOUNTING_END             DATE,
232
233
234    N_LAST_PAYMENT_SUM           numeric,
235
236
237    D_LAST_PAYMENT               DATE,
238
239
240    VC_LAST_PAYMENT_TYPE         varchar(255),
241
242
243    N_LAST_PAYMENT_BANK_ID       numeric,
244
245
246    VC_LAST_PAYMENT_BANK         varchar(255),
247
248
249    N_RECOMMENDED_PAY            numeric,
250
251
252    C_FL_PROMISED_PAYMENT        varchar(255),
253
254
255    N_CURRENT_PROMISED_PAY_SUM   numeric,
256
257
258    D_CURRENT_PROMISED_PAY_DAY   DATE
259
260
261);
262
263
264     -> 
265
266
267alter table AP_V_PERSONAL_ACCOUNTS
268
269
270    owner to intercon_user;
271
272
273     -> create table if not exists AP_V_USER_OFFICE_GOODS
274
275
276(
277
278
279    N_SUBJ_GOOD_ID            numeric,
280
281
282    N_LINE_NO                 numeric,
283
284
285    N_PAR_SUBJ_GOOD_ID        numeric,
286
287
288    N_GOOD_ID                 numeric,
289
290
291    VC_GOOD                   VARCHAR(255),
292
293
294    N_ACCOUNT_ID              numeric,
295
296
297    VC_ACCOUNT                VARCHAR(255),
298
299
300    N_OBJECT_ID               numeric,
301
302
303    VC_OBJECT                 VARCHAR(255),
304
305
306    N_PAY_DAY                 numeric,
307
308
309    N_SUBSCR_PERMISSION_ID    numeric,
310
311
312    N_SERV_TYPE_ID            numeric,
313
314
315    N_UNSUBSCR_PERMISSION_ID  numeric,
316
317
318    N_SERV_END_CHARGE_TYPE_ID numeric,
319
320
321    N_DOC_ID                  numeric,
322
323
324    N_DOC_TYPE_ID             numeric,
325
326
327    VC_DOC_NAME               VARCHAR(255),
328
329
330    VC_DOC_CODE               VARCHAR(255),
331
332
333    D_BEGIN                   DATE,
334
335
336    D_END                     DATE,
337
338
339    N_INVOICE_ID              numeric,
340
341
342    VC_INVOICE_CODE           VARCHAR(255),
343
344
345    VC_INVOICE_NAME           VARCHAR(255)
346
347
348);
349
350
351     -> 
352
353
354alter table AP_V_USER_OFFICE_GOODS
355
356
357    owner to intercon_user;
358
359
360     -> create table if not exists AP_V_PERSONS
361
362
363(
364
365
366    N_PERSON_ID    numeric      not null
367
368
369        constraint profile_pkey
370
371
372            primary key,
373
374
375    VC_FIRST_NAME  VARCHAR(255) not null,
376
377
378    VC_SURNAME     VARCHAR(255) not null,
379
380
381    VC_SECOND_NAME VARCHAR(255) not null,
382
383
384    VC_NAME        VARCHAR(255),
385
386
387    N_SEX_ID       NUMERIC,
388
389
390    D_BIRTH        DATE
391
392
393);
394
395
396     -> 
397
398
399alter table AP_V_PERSONS
400
401
402    owner to intercon_user;
403
404
405     -> create table if not exists AP_V_USER_OFFICE_ACCESS_SERVS
406
407
408(
409
410
411    N_DOC_ID                numeric,
412
413
414    N_USER_ID               numeric,
415
416
417    N_GOOD_ID               NUMERIC,
418
419
420    N_GOOD_TYPE_ID          numeric,
421
422
423    VC_NAME                 VARCHAR(255),
424
425
426    VC_CODE                 VARCHAR(255),
427
428
429    N_PRICE                 numeric,
430
431
432    N_CURRENCY_ID           numeric,
433
434
435    VC_CURRENCY             VARCHAR(255),
436
437
438    N_PAR_PRICE_LINE_ID     numeric,
439
440
441    N_SUBSCR_PERMISSION_ID  numeric,
442
443
444    VC_SUBSCR_PERMISSION_ID VARCHAR(255),
445
446
447    N_PARENT_GOOD_ID        numeric,
448
449
450    VC_PARENT_GOOD          VARCHAR(255),
451
452
453    VC_USER_REM             VARCHAR(255),
454
455
456    N_OBJECT_ID             numeric,
457
458
459    C_FL_ACTIVE             VARCHAR(255)
460
461
462);
463
464
465     -> 
466
467
468alter table AP_V_USER_OFFICE_ACCESS_SERVS
469
470
471    owner to intercon_user;
472
473
474     -> create table if not exists AP_V_USER_OFFICE_DEVICES
475
476
477(
478
479
480    N_DEVICE_ID       numeric,
481
482
483    N_USE_DEVICE_ID   numeric,
484
485
486    VC_ADDR_CODE      VARCHAR(255),
487
488
489    VC_DEVICE_CODE    VARCHAR(255),
490
491
492    N_CATALOG_ITEM_ID numeric,
493
494
495    N_DEV_STATE_ID    numeric,
496
497
498    VC_DEV_STATE      VARCHAR(255),
499
500
501    VC_PORT_CODE      VARCHAR(255),
502
503
504    VC_IP_CODE        VARCHAR(255),
505
506
507    VC_SUBNET_MASK    VARCHAR(255),
508
509
510    VC_SUBNET_CODE    VARCHAR(255),
511
512
513    VC_MAC_CODE       VARCHAR(255)
514
515
516);
517
518
519     -> 
520
521
522alter table AP_V_USER_OFFICE_DEVICES
523
524
525    owner to intercon_user;
526
527
528     -> CREATE TABLE public.sessions
529
530
531(
532
533
534    id           character varying(180) NOT NULL,
535
536
537    user_id      numeric                NOT NULL,
538
539
540    curr_session boolean
541
542
543);
544
545
546     -> 
547
548
549ALTER TABLE public.sessions
550
551
552    OWNER TO intercon_user;
553
554
555     -> 
556
557
558ALTER TABLE ONLY public.sessions
559
560
561    ADD CONSTRAINT sessions_pk PRIMARY KEY (id);
562
563
564     -> CREATE table if not exists SI_V_ENTITIES_TAGS
565
566
567(
568
569
570    N_ENTITY_TAG_ID  numeric,
571
572
573    N_TAG_ID         numeric,
574
575
576    N_ENTITY_ID      NUMERIC,
577
578
579    N_ENTITY_TYPE_ID numeric,
580
581
582    N_LINE_NO        numeric,
583
584
585    VC_CODE          VARCHAR(255),
586
587
588    C_FL_EDITABLE    CHAR
589
590
591);
592
593
594     -> 
595
596
597alter table SI_V_ENTITIES_TAGS
598
599
600    owner to intercon_user;
601
602
603     -> 
604
605
606create function func_login(login character varying, pass character varying, session_id character varying) returns bool
607
608
609    language plpgsql
610
611
612as
613
614
615$$
616
617
618DECLARE
619
620
621    count_session_id int;
622
623
624BEGIN
625
626
627    count_session_id := (SELECT count(s) FROM sessions as s WHERE s.id LIKE session_id);
628
629
630    IF func_get_user_id(login, pass) THEN
631
632
633        IF count_session_id > 0 THEN
634
635
636            return false;
637
638
639        end if;
640
641
642
643
644        INSERT into sessions(id, user_id, curr_session)
645
646
647        values (session_id, (SELECT au.N_SUBJ_SERV_ID
648
649
650                             FROM AP_V_SERVICES as au
651
652
653                             WHERE au.VC_LOGIN_REAL = login
654
655
656                               and au.password = pass
657
658
659                             LIMIT 1), true);
660
661
662        return true;
663
664
665    ELSE
666
667
668        return false;
669
670
671    end if;
672
673
674end ;
675
676
677$$;
678
679
680     -> 
681
682
683alter function func_login(VARCHAR, VARCHAR, VARCHAR) owner to intercon_user;
684
685
686     -> create function func_get_user_id(login character varying, pass character varying) returns boolean
687
688
689    language plpgsql
690
691
692as
693
694
695$$
696
697
698DECLARE
699
700
701    id numeric;
702
703
704BEGIN
705
706
707    id := (
708
709
710        SELECT au.N_SUBJ_SERV_ID
711
712
713        FROM AP_V_SERVICES as au
714
715
716        WHERE au.VC_LOGIN_REAL = login
717
718
719          and au.password = pass
720
721
722        LIMIT 1);
723
724
725    IF id IS NOT NULL then
726
727
728        return true;
729
730
731    else
732
733
734        return false;
735
736
737    end if;
738
739
740
741
742end ;
743
744
745$$;
746
747
748     -> alter function func_get_user_id(VARCHAR, VARCHAR) owner to intercon_user;
749
750
751     -> CREATE OR REPLACE FUNCTION switch_context(session_id VARCHAR(180))
752
753
754    returns void
755
756
757AS
758
759
760$$
761
762
763BEGIN
764
765
766    UPDATE sessions as ses SET curr_session = false Where ses.id <> session_id;
767
768
769    UPDATE sessions as ses SET curr_session = true WHERE ses.id = session_id;
770
771
772end;
773
774
775$$
776
777
778    LANGUAGE PLpgSQL;
779
780
781     -> CREATE OR REPLACE FUNCTION logoff(session_id VARCHAR(180))
782
783
784    returns void
785
786
787AS
788
789
790$$
791
792
793BEGIN
794
795
796    DELETE FROM sessions WHERE id = session_id;
797
798
799end;
800
801
802$$
803
804
805    LANGUAGE PLpgSQL;
806
807
808     -> create function suspend_service(good_id numeric, instant boolean) returns varchar
809
810
811    language plpgsql
812
813
814as
815
816
817$$
818
819
820DECLARE
821
822
823    flag varchar;
824
825
826BEGIN
827
828
829    IF instant THEN
830
831
832        flag = 'Y';
833
834
835        UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
836
837
838        return 1;
839
840
841    ELSE
842
843
844        flag = 'N';
845
846
847        return 2;
848
849
850    end if;
851
852
853
854
855end;
856
857
858$$;
859
860
861     -> alter function suspend_service(numeric, boolean) owner to intercon_user;
862
863
864     -> create function stop_temp_blocking(good_id numeric, date_stop date) returns character varying
865
866
867    language plpgsql
868
869
870as
871
872
873$$
874
875
876DECLARE
877
878
879    flag varchar;
880
881
882BEGIN
883
884
885    flag = (Select c_fl_active
886
887
888            from AP_V_USER_OFFICE_ACCESS_SERVS as uoas
889
890
891            where N_GOOD_ID = good_id);
892
893
894    if flag = 'N' then
895
896
897        return 'null';
898
899
900    end if;
901
902
903    UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
904
905
906    return '1';
907
908
909end;
910
911
912$$;
913
914
915     -> alter function stop_temp_blocking(numeric) owner to intercon_user;
916
917
918Migration 20191009125712 failed during Execution. Error An exception occurred while executing 'alter function stop_temp_blocking(numeric) owner to intercon_user;':
919
920
921
922
923SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(numeric) does not exist
924
925
926
927
928In AbstractPostgreSQLDriver.php line 79:
929
930
931                                                                               
932
933
934  An exception occurred while executing 'alter function stop_temp_blocking(nu  
935
936
937  meric) owner to intercon_user;':                                             
938
939
940                                                                               
941
942
943  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
944
945
946  numeric) does not exist                                                      
947
948
949                                                                               
950
951
952
953
954In PDOConnection.php line 80:
955
956
957                                                                               
958
959
960  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
961
962
963  numeric) does not exist                                                      
964
965
966                                                                               
967
968
969
970
971In PDOConnection.php line 75:
972
973
974                                                                               
975
976
977  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
978
979
980  numeric) does not exist                                                      
981
982
983                                                                               
984
985
986
987
988doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]
989
990
991
992
993                                                              
994
995
996                    Application Migrations                    
997
998
999                                                              
1000
1001
1002
1003
1004Migrating up to 20191009125712 from 0
1005
1006
1007
1008
1009  ++ migrating 20191009125712
1010
1011
1012
1013
1014     -> CREATE SEQUENCE accounts_id_seq INCREMENT BY 1 MINVALUE 1 START 1
1015
1016
1017     -> alter sequence accounts_id_seq owner to intercon_user;
1018
1019
1020     -> CREATE SEQUENCE AP_V_USER_OFFICE_GOODS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
1021
1022
1023     -> alter sequence AP_V_USER_OFFICE_GOODS_id_seq owner to intercon_user;
1024
1025
1026     -> CREATE SEQUENCE ap_v_services_id_seq INCREMENT BY 1 MINVALUE 1 START 1
1027
1028
1029     -> alter sequence ap_v_services_id_seq owner to intercon_user;
1030
1031
1032     -> CREATE SEQUENCE AP_V_PERSONS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
1033
1034
1035     -> alter sequence AP_V_PERSONS_id_seq owner to intercon_user;
1036
1037
1038     -> CREATE SEQUENCE sessions_id_seq INCREMENT BY 1 MINVALUE 1 START 1
1039
1040
1041     -> alter sequence sessions_id_seq owner to intercon_user;
1042
1043
1044     -> create table if not exists AP_V_SERVICES
1045
1046
1047(
1048
1049
1050    N_SUBJ_SERV_ID         numeric,
1051
1052
1053    N_SUBJ_SERV_TYPE_ID    numeric,
1054
1055
1056    N_SERVICE_ID           numeric,
1057
1058
1059    N_OBJECT_ID            numeric,
1060
1061
1062    N_SUBJECT_ID           numeric,
1063
1064
1065    VC_SERVICE             VARCHAR(255),
1066
1067
1068    VC_OBJECT              VARCHAR(255),
1069
1070
1071    N_OBJ_GOOD_ID          numeric,
1072
1073
1074    VC_OBJ_GOOD            VARCHAR(255),
1075
1076
1077    N_OBJ_ADDRESS_ID       numeric,
1078
1079
1080    N_PORT_NO              numeric,
1081
1082
1083    VC_LOGIN               VARCHAR(255),
1084
1085
1086    VC_LOGIN_REAL          VARCHAR(255),
1087
1088
1089    C_FL_EMPTY_PASS        VARCHAR(255),
1090
1091
1092    VC_VALUE               VARCHAR(255),
1093
1094
1095    N_VALUE                numeric,
1096
1097
1098    C_CANT_CHANGE          CHAR,
1099
1100
1101    N_SERV_AUTH_TYPE       numeric,
1102
1103
1104    N_AUTH_TYPE_ID         numeric,
1105
1106
1107    C_FL_APP               VARCHAR(255),
1108
1109
1110    C_FL_PASS_TESTING      VARCHAR(255),
1111
1112
1113    C_FL_PLAINTEXT_PASS    CHAR,
1114
1115
1116    N_DEFAULT_HASH_TYPE_ID numeric,
1117
1118
1119    VC_PASSWORD_PATTERN    VARCHAR(255),
1120
1121
1122    password               varchar(180),
1123
1124
1125    roles                  json
1126
1127
1128);
1129
1130
1131     -> 
1132
1133
1134alter table AP_V_SERVICES
1135
1136
1137    owner to intercon_user;
1138
1139
1140     -> create table if not exists AP_V_PERSONAL_ACCOUNTS
1141
1142
1143(
1144
1145
1146    N_SUBJECT_ID                 numeric,
1147
1148
1149    VC_SUBJ_CODE                 varchar(255),
1150
1151
1152    VC_SUBJ_NAME                 varchar(255),
1153
1154
1155    N_ACCOUNT_ID                 numeric,
1156
1157
1158    N_CURRENCY_ID                numeric,
1159
1160
1161    VC_CURRENCY                  varchar(255),
1162
1163
1164    VC_CURRENCY_CODE             varchar(255),
1165
1166
1167    VC_CODE                      varchar(255),
1168
1169
1170    VC_NAME                      varchar(255),
1171
1172
1173    VC_ACCOUNT                   varchar(255),
1174
1175
1176    N_OVERDRAFT                  numeric,
1177
1178
1179    D_OVERDRAFT_END              DATE,
1180
1181
1182    N_PERMANENT_OVERDRAFT        numeric,
1183
1184
1185    N_TEMPORAL_OVERDRAFT         numeric,
1186
1187
1188    D_TEMP_OVERDRAFT_END         DATE,
1189
1190
1191    N_SCHED_SERV_OVERDRAFT       numeric,
1192
1193
1194    D_SCHED_SERV_OVERDRAFT_END   DATE,
1195
1196
1197    N_UNSCHED_SERV_OVERDRAFT     numeric,
1198
1199
1200    D_UNSCHED_SERV_OVERDRAFT_END DATE,
1201
1202
1203    N_SUM_BAL                    numeric,
1204
1205
1206    N_SUM_RESERVED               numeric,
1207
1208
1209    N_SUM_RESERVED_CUR           numeric,
1210
1211
1212    N_SUM_FREE                   numeric,
1213
1214
1215    D_ACCOUNTING_BEGIN           DATE,
1216
1217
1218    D_ACCOUNTING_END             DATE,
1219
1220
1221    N_LAST_PAYMENT_SUM           numeric,
1222
1223
1224    D_LAST_PAYMENT               DATE,
1225
1226
1227    VC_LAST_PAYMENT_TYPE         varchar(255),
1228
1229
1230    N_LAST_PAYMENT_BANK_ID       numeric,
1231
1232
1233    VC_LAST_PAYMENT_BANK         varchar(255),
1234
1235
1236    N_RECOMMENDED_PAY            numeric,
1237
1238
1239    C_FL_PROMISED_PAYMENT        varchar(255),
1240
1241
1242    N_CURRENT_PROMISED_PAY_SUM   numeric,
1243
1244
1245    D_CURRENT_PROMISED_PAY_DAY   DATE
1246
1247
1248);
1249
1250
1251     -> 
1252
1253
1254alter table AP_V_PERSONAL_ACCOUNTS
1255
1256
1257    owner to intercon_user;
1258
1259
1260     -> create table if not exists AP_V_USER_OFFICE_GOODS
1261
1262
1263(
1264
1265
1266    N_SUBJ_GOOD_ID            numeric,
1267
1268
1269    N_LINE_NO                 numeric,
1270
1271
1272    N_PAR_SUBJ_GOOD_ID        numeric,
1273
1274
1275    N_GOOD_ID                 numeric,
1276
1277
1278    VC_GOOD                   VARCHAR(255),
1279
1280
1281    N_ACCOUNT_ID              numeric,
1282
1283
1284    VC_ACCOUNT                VARCHAR(255),
1285
1286
1287    N_OBJECT_ID               numeric,
1288
1289
1290    VC_OBJECT                 VARCHAR(255),
1291
1292
1293    N_PAY_DAY                 numeric,
1294
1295
1296    N_SUBSCR_PERMISSION_ID    numeric,
1297
1298
1299    N_SERV_TYPE_ID            numeric,
1300
1301
1302    N_UNSUBSCR_PERMISSION_ID  numeric,
1303
1304
1305    N_SERV_END_CHARGE_TYPE_ID numeric,
1306
1307
1308    N_DOC_ID                  numeric,
1309
1310
1311    N_DOC_TYPE_ID             numeric,
1312
1313
1314    VC_DOC_NAME               VARCHAR(255),
1315
1316
1317    VC_DOC_CODE               VARCHAR(255),
1318
1319
1320    D_BEGIN                   DATE,
1321
1322
1323    D_END                     DATE,
1324
1325
1326    N_INVOICE_ID              numeric,
1327
1328
1329    VC_INVOICE_CODE           VARCHAR(255),
1330
1331
1332    VC_INVOICE_NAME           VARCHAR(255)
1333
1334
1335);
1336
1337
1338     -> 
1339
1340
1341alter table AP_V_USER_OFFICE_GOODS
1342
1343
1344    owner to intercon_user;
1345
1346
1347     -> create table if not exists AP_V_PERSONS
1348
1349
1350(
1351
1352
1353    N_PERSON_ID    numeric      not null
1354
1355
1356        constraint profile_pkey
1357
1358
1359            primary key,
1360
1361
1362    VC_FIRST_NAME  VARCHAR(255) not null,
1363
1364
1365    VC_SURNAME     VARCHAR(255) not null,
1366
1367
1368    VC_SECOND_NAME VARCHAR(255) not null,
1369
1370
1371    VC_NAME        VARCHAR(255),
1372
1373
1374    N_SEX_ID       NUMERIC,
1375
1376
1377    D_BIRTH        DATE
1378
1379
1380);
1381
1382
1383     -> 
1384
1385
1386alter table AP_V_PERSONS
1387
1388
1389    owner to intercon_user;
1390
1391
1392     -> create table if not exists AP_V_USER_OFFICE_ACCESS_SERVS
1393
1394
1395(
1396
1397
1398    N_DOC_ID                numeric,
1399
1400
1401    N_USER_ID               numeric,
1402
1403
1404    N_GOOD_ID               NUMERIC,
1405
1406
1407    N_GOOD_TYPE_ID          numeric,
1408
1409
1410    VC_NAME                 VARCHAR(255),
1411
1412
1413    VC_CODE                 VARCHAR(255),
1414
1415
1416    N_PRICE                 numeric,
1417
1418
1419    N_CURRENCY_ID           numeric,
1420
1421
1422    VC_CURRENCY             VARCHAR(255),
1423
1424
1425    N_PAR_PRICE_LINE_ID     numeric,
1426
1427
1428    N_SUBSCR_PERMISSION_ID  numeric,
1429
1430
1431    VC_SUBSCR_PERMISSION_ID VARCHAR(255),
1432
1433
1434    N_PARENT_GOOD_ID        numeric,
1435
1436
1437    VC_PARENT_GOOD          VARCHAR(255),
1438
1439
1440    VC_USER_REM             VARCHAR(255),
1441
1442
1443    N_OBJECT_ID             numeric,
1444
1445
1446    C_FL_ACTIVE             VARCHAR(255)
1447
1448
1449);
1450
1451
1452     -> 
1453
1454
1455alter table AP_V_USER_OFFICE_ACCESS_SERVS
1456
1457
1458    owner to intercon_user;
1459
1460
1461     -> create table if not exists AP_V_USER_OFFICE_DEVICES
1462
1463
1464(
1465
1466
1467    N_DEVICE_ID       numeric,
1468
1469
1470    N_USE_DEVICE_ID   numeric,
1471
1472
1473    VC_ADDR_CODE      VARCHAR(255),
1474
1475
1476    VC_DEVICE_CODE    VARCHAR(255),
1477
1478
1479    N_CATALOG_ITEM_ID numeric,
1480
1481
1482    N_DEV_STATE_ID    numeric,
1483
1484
1485    VC_DEV_STATE      VARCHAR(255),
1486
1487
1488    VC_PORT_CODE      VARCHAR(255),
1489
1490
1491    VC_IP_CODE        VARCHAR(255),
1492
1493
1494    VC_SUBNET_MASK    VARCHAR(255),
1495
1496
1497    VC_SUBNET_CODE    VARCHAR(255),
1498
1499
1500    VC_MAC_CODE       VARCHAR(255)
1501
1502
1503);
1504
1505
1506     -> 
1507
1508
1509alter table AP_V_USER_OFFICE_DEVICES
1510
1511
1512    owner to intercon_user;
1513
1514
1515     -> CREATE TABLE public.sessions
1516
1517
1518(
1519
1520
1521    id           character varying(180) NOT NULL,
1522
1523
1524    user_id      numeric                NOT NULL,
1525
1526
1527    curr_session boolean
1528
1529
1530);
1531
1532
1533     -> 
1534
1535
1536ALTER TABLE public.sessions
1537
1538
1539    OWNER TO intercon_user;
1540
1541
1542     -> 
1543
1544
1545ALTER TABLE ONLY public.sessions
1546
1547
1548    ADD CONSTRAINT sessions_pk PRIMARY KEY (id);
1549
1550
1551     -> CREATE table if not exists SI_V_ENTITIES_TAGS
1552
1553
1554(
1555
1556
1557    N_ENTITY_TAG_ID  numeric,
1558
1559
1560    N_TAG_ID         numeric,
1561
1562
1563    N_ENTITY_ID      NUMERIC,
1564
1565
1566    N_ENTITY_TYPE_ID numeric,
1567
1568
1569    N_LINE_NO        numeric,
1570
1571
1572    VC_CODE          VARCHAR(255),
1573
1574
1575    C_FL_EDITABLE    CHAR
1576
1577
1578);
1579
1580
1581     -> 
1582
1583
1584alter table SI_V_ENTITIES_TAGS
1585
1586
1587    owner to intercon_user;
1588
1589
1590     -> 
1591
1592
1593create function func_login(login character varying, pass character varying, session_id character varying) returns bool
1594
1595
1596    language plpgsql
1597
1598
1599as
1600
1601
1602$$
1603
1604
1605DECLARE
1606
1607
1608    count_session_id int;
1609
1610
1611BEGIN
1612
1613
1614    count_session_id := (SELECT count(s) FROM sessions as s WHERE s.id LIKE session_id);
1615
1616
1617    IF func_get_user_id(login, pass) THEN
1618
1619
1620        IF count_session_id > 0 THEN
1621
1622
1623            return false;
1624
1625
1626        end if;
1627
1628
1629
1630
1631        INSERT into sessions(id, user_id, curr_session)
1632
1633
1634        values (session_id, (SELECT au.N_SUBJ_SERV_ID
1635
1636
1637                             FROM AP_V_SERVICES as au
1638
1639
1640                             WHERE au.VC_LOGIN_REAL = login
1641
1642
1643                               and au.password = pass
1644
1645
1646                             LIMIT 1), true);
1647
1648
1649        return true;
1650
1651
1652    ELSE
1653
1654
1655        return false;
1656
1657
1658    end if;
1659
1660
1661end ;
1662
1663
1664$$;
1665
1666
1667     -> 
1668
1669
1670alter function func_login(VARCHAR, VARCHAR, VARCHAR) owner to intercon_user;
1671
1672
1673     -> create function func_get_user_id(login character varying, pass character varying) returns boolean
1674
1675
1676    language plpgsql
1677
1678
1679as
1680
1681
1682$$
1683
1684
1685DECLARE
1686
1687
1688    id numeric;
1689
1690
1691BEGIN
1692
1693
1694    id := (
1695
1696
1697        SELECT au.N_SUBJ_SERV_ID
1698
1699
1700        FROM AP_V_SERVICES as au
1701
1702
1703        WHERE au.VC_LOGIN_REAL = login
1704
1705
1706          and au.password = pass
1707
1708
1709        LIMIT 1);
1710
1711
1712    IF id IS NOT NULL then
1713
1714
1715        return true;
1716
1717
1718    else
1719
1720
1721        return false;
1722
1723
1724    end if;
1725
1726
1727
1728
1729end ;
1730
1731
1732$$;
1733
1734
1735     -> alter function func_get_user_id(VARCHAR, VARCHAR) owner to intercon_user;
1736
1737
1738     -> CREATE OR REPLACE FUNCTION switch_context(session_id VARCHAR(180))
1739
1740
1741    returns void
1742
1743
1744AS
1745
1746
1747$$
1748
1749
1750BEGIN
1751
1752
1753    UPDATE sessions as ses SET curr_session = false Where ses.id <> session_id;
1754
1755
1756    UPDATE sessions as ses SET curr_session = true WHERE ses.id = session_id;
1757
1758
1759end;
1760
1761
1762$$
1763
1764
1765    LANGUAGE PLpgSQL;
1766
1767
1768     -> CREATE OR REPLACE FUNCTION logoff(session_id VARCHAR(180))
1769
1770
1771    returns void
1772
1773
1774AS
1775
1776
1777$$
1778
1779
1780BEGIN
1781
1782
1783    DELETE FROM sessions WHERE id = session_id;
1784
1785
1786end;
1787
1788
1789$$
1790
1791
1792    LANGUAGE PLpgSQL;
1793
1794
1795     -> create function suspend_service(good_id numeric, instant boolean) returns varchar
1796
1797
1798    language plpgsql
1799
1800
1801as
1802
1803
1804$$
1805
1806
1807DECLARE
1808
1809
1810    flag varchar;
1811
1812
1813BEGIN
1814
1815
1816    IF instant THEN
1817
1818
1819        flag = 'Y';
1820
1821
1822        UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
1823
1824
1825        return 1;
1826
1827
1828    ELSE
1829
1830
1831        flag = 'N';
1832
1833
1834        return 2;
1835
1836
1837    end if;
1838
1839
1840
1841
1842end;
1843
1844
1845$$;
1846
1847
1848     -> alter function suspend_service(numeric, boolean) owner to intercon_user;
1849
1850
1851     -> create function stop_temp_blocking(good_id numeric, date_stop date) returns character varying
1852
1853
1854    language plpgsql
1855
1856
1857as
1858
1859
1860$$
1861
1862
1863DECLARE
1864
1865
1866    flag varchar;
1867
1868
1869BEGIN
1870
1871
1872    flag = (Select c_fl_active
1873
1874
1875            from AP_V_USER_OFFICE_ACCESS_SERVS as uoas
1876
1877
1878            where N_GOOD_ID = good_id);
1879
1880
1881    if flag = 'N' then
1882
1883
1884        return 'null';
1885
1886
1887    end if;
1888
1889
1890    UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
1891
1892
1893    return '1';
1894
1895
1896end;
1897
1898
1899$$;
1900
1901
1902     -> alter function stop_temp_blocking(numeric) owner to intercon_user;
1903
1904
1905Migration 20191009125712 failed during Execution. Error An exception occurred while executing 'alter function stop_temp_blocking(numeric) owner to intercon_user;':
1906
1907
1908
1909
1910SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(numeric) does not exist
1911
1912
1913
1914
1915In AbstractPostgreSQLDriver.php line 79:
1916
1917
1918                                                                               
1919
1920
1921  An exception occurred while executing 'alter function stop_temp_blocking(nu  
1922
1923
1924  meric) owner to intercon_user;':                                             
1925
1926
1927                                                                               
1928
1929
1930  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
1931
1932
1933  numeric) does not exist                                                      
1934
1935
1936                                                                               
1937
1938
1939
1940
1941In PDOConnection.php line 80:
1942
1943
1944                                                                               
1945
1946
1947  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
1948
1949
1950  numeric) does not exist                                                      
1951
1952
1953                                                                               
1954
1955
1956
1957
1958In PDOConnection.php line 75:
1959
1960
1961                                                                               
1962
1963
1964  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
1965
1966
1967  numeric) does not exist                                                      
1968
1969
1970                                                                               
1971
1972
1973
1974
1975doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]
1976
1977
1978
1979
1980                                                              
1981
1982
1983                    Application Migrations                    
1984
1985
1986                                                              
1987
1988
1989
1990
1991Migrating up to 20191009125712 from 0
1992
1993
1994
1995
1996  ++ migrating 20191009125712
1997
1998
1999
2000
2001     -> CREATE SEQUENCE accounts_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2002
2003
2004     -> alter sequence accounts_id_seq owner to intercon_user;
2005
2006
2007     -> CREATE SEQUENCE AP_V_USER_OFFICE_GOODS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2008
2009
2010     -> alter sequence AP_V_USER_OFFICE_GOODS_id_seq owner to intercon_user;
2011
2012
2013     -> CREATE SEQUENCE ap_v_services_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2014
2015
2016     -> alter sequence ap_v_services_id_seq owner to intercon_user;
2017
2018
2019     -> CREATE SEQUENCE AP_V_PERSONS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2020
2021
2022     -> alter sequence AP_V_PERSONS_id_seq owner to intercon_user;
2023
2024
2025     -> CREATE SEQUENCE sessions_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2026
2027
2028     -> alter sequence sessions_id_seq owner to intercon_user;
2029
2030
2031     -> create table if not exists AP_V_SERVICES
2032
2033
2034(
2035
2036
2037    N_SUBJ_SERV_ID         numeric,
2038
2039
2040    N_SUBJ_SERV_TYPE_ID    numeric,
2041
2042
2043    N_SERVICE_ID           numeric,
2044
2045
2046    N_OBJECT_ID            numeric,
2047
2048
2049    N_SUBJECT_ID           numeric,
2050
2051
2052    VC_SERVICE             VARCHAR(255),
2053
2054
2055    VC_OBJECT              VARCHAR(255),
2056
2057
2058    N_OBJ_GOOD_ID          numeric,
2059
2060
2061    VC_OBJ_GOOD            VARCHAR(255),
2062
2063
2064    N_OBJ_ADDRESS_ID       numeric,
2065
2066
2067    N_PORT_NO              numeric,
2068
2069
2070    VC_LOGIN               VARCHAR(255),
2071
2072
2073    VC_LOGIN_REAL          VARCHAR(255),
2074
2075
2076    C_FL_EMPTY_PASS        VARCHAR(255),
2077
2078
2079    VC_VALUE               VARCHAR(255),
2080
2081
2082    N_VALUE                numeric,
2083
2084
2085    C_CANT_CHANGE          CHAR,
2086
2087
2088    N_SERV_AUTH_TYPE       numeric,
2089
2090
2091    N_AUTH_TYPE_ID         numeric,
2092
2093
2094    C_FL_APP               VARCHAR(255),
2095
2096
2097    C_FL_PASS_TESTING      VARCHAR(255),
2098
2099
2100    C_FL_PLAINTEXT_PASS    CHAR,
2101
2102
2103    N_DEFAULT_HASH_TYPE_ID numeric,
2104
2105
2106    VC_PASSWORD_PATTERN    VARCHAR(255),
2107
2108
2109    password               varchar(180),
2110
2111
2112    roles                  json
2113
2114
2115);
2116
2117
2118     -> 
2119
2120
2121alter table AP_V_SERVICES
2122
2123
2124    owner to intercon_user;
2125
2126
2127     -> create table if not exists AP_V_PERSONAL_ACCOUNTS
2128
2129
2130(
2131
2132
2133    N_SUBJECT_ID                 numeric,
2134
2135
2136    VC_SUBJ_CODE                 varchar(255),
2137
2138
2139    VC_SUBJ_NAME                 varchar(255),
2140
2141
2142    N_ACCOUNT_ID                 numeric,
2143
2144
2145    N_CURRENCY_ID                numeric,
2146
2147
2148    VC_CURRENCY                  varchar(255),
2149
2150
2151    VC_CURRENCY_CODE             varchar(255),
2152
2153
2154    VC_CODE                      varchar(255),
2155
2156
2157    VC_NAME                      varchar(255),
2158
2159
2160    VC_ACCOUNT                   varchar(255),
2161
2162
2163    N_OVERDRAFT                  numeric,
2164
2165
2166    D_OVERDRAFT_END              DATE,
2167
2168
2169    N_PERMANENT_OVERDRAFT        numeric,
2170
2171
2172    N_TEMPORAL_OVERDRAFT         numeric,
2173
2174
2175    D_TEMP_OVERDRAFT_END         DATE,
2176
2177
2178    N_SCHED_SERV_OVERDRAFT       numeric,
2179
2180
2181    D_SCHED_SERV_OVERDRAFT_END   DATE,
2182
2183
2184    N_UNSCHED_SERV_OVERDRAFT     numeric,
2185
2186
2187    D_UNSCHED_SERV_OVERDRAFT_END DATE,
2188
2189
2190    N_SUM_BAL                    numeric,
2191
2192
2193    N_SUM_RESERVED               numeric,
2194
2195
2196    N_SUM_RESERVED_CUR           numeric,
2197
2198
2199    N_SUM_FREE                   numeric,
2200
2201
2202    D_ACCOUNTING_BEGIN           DATE,
2203
2204
2205    D_ACCOUNTING_END             DATE,
2206
2207
2208    N_LAST_PAYMENT_SUM           numeric,
2209
2210
2211    D_LAST_PAYMENT               DATE,
2212
2213
2214    VC_LAST_PAYMENT_TYPE         varchar(255),
2215
2216
2217    N_LAST_PAYMENT_BANK_ID       numeric,
2218
2219
2220    VC_LAST_PAYMENT_BANK         varchar(255),
2221
2222
2223    N_RECOMMENDED_PAY            numeric,
2224
2225
2226    C_FL_PROMISED_PAYMENT        varchar(255),
2227
2228
2229    N_CURRENT_PROMISED_PAY_SUM   numeric,
2230
2231
2232    D_CURRENT_PROMISED_PAY_DAY   DATE
2233
2234
2235);
2236
2237
2238     -> 
2239
2240
2241alter table AP_V_PERSONAL_ACCOUNTS
2242
2243
2244    owner to intercon_user;
2245
2246
2247     -> create table if not exists AP_V_USER_OFFICE_GOODS
2248
2249
2250(
2251
2252
2253    N_SUBJ_GOOD_ID            numeric,
2254
2255
2256    N_LINE_NO                 numeric,
2257
2258
2259    N_PAR_SUBJ_GOOD_ID        numeric,
2260
2261
2262    N_GOOD_ID                 numeric,
2263
2264
2265    VC_GOOD                   VARCHAR(255),
2266
2267
2268    N_ACCOUNT_ID              numeric,
2269
2270
2271    VC_ACCOUNT                VARCHAR(255),
2272
2273
2274    N_OBJECT_ID               numeric,
2275
2276
2277    VC_OBJECT                 VARCHAR(255),
2278
2279
2280    N_PAY_DAY                 numeric,
2281
2282
2283    N_SUBSCR_PERMISSION_ID    numeric,
2284
2285
2286    N_SERV_TYPE_ID            numeric,
2287
2288
2289    N_UNSUBSCR_PERMISSION_ID  numeric,
2290
2291
2292    N_SERV_END_CHARGE_TYPE_ID numeric,
2293
2294
2295    N_DOC_ID                  numeric,
2296
2297
2298    N_DOC_TYPE_ID             numeric,
2299
2300
2301    VC_DOC_NAME               VARCHAR(255),
2302
2303
2304    VC_DOC_CODE               VARCHAR(255),
2305
2306
2307    D_BEGIN                   DATE,
2308
2309
2310    D_END                     DATE,
2311
2312
2313    N_INVOICE_ID              numeric,
2314
2315
2316    VC_INVOICE_CODE           VARCHAR(255),
2317
2318
2319    VC_INVOICE_NAME           VARCHAR(255)
2320
2321
2322);
2323
2324
2325     -> 
2326
2327
2328alter table AP_V_USER_OFFICE_GOODS
2329
2330
2331    owner to intercon_user;
2332
2333
2334     -> create table if not exists AP_V_PERSONS
2335
2336
2337(
2338
2339
2340    N_PERSON_ID    numeric      not null
2341
2342
2343        constraint profile_pkey
2344
2345
2346            primary key,
2347
2348
2349    VC_FIRST_NAME  VARCHAR(255) not null,
2350
2351
2352    VC_SURNAME     VARCHAR(255) not null,
2353
2354
2355    VC_SECOND_NAME VARCHAR(255) not null,
2356
2357
2358    VC_NAME        VARCHAR(255),
2359
2360
2361    N_SEX_ID       NUMERIC,
2362
2363
2364    D_BIRTH        DATE
2365
2366
2367);
2368
2369
2370     -> 
2371
2372
2373alter table AP_V_PERSONS
2374
2375
2376    owner to intercon_user;
2377
2378
2379     -> create table if not exists AP_V_USER_OFFICE_ACCESS_SERVS
2380
2381
2382(
2383
2384
2385    N_DOC_ID                numeric,
2386
2387
2388    N_USER_ID               numeric,
2389
2390
2391    N_GOOD_ID               NUMERIC,
2392
2393
2394    N_GOOD_TYPE_ID          numeric,
2395
2396
2397    VC_NAME                 VARCHAR(255),
2398
2399
2400    VC_CODE                 VARCHAR(255),
2401
2402
2403    N_PRICE                 numeric,
2404
2405
2406    N_CURRENCY_ID           numeric,
2407
2408
2409    VC_CURRENCY             VARCHAR(255),
2410
2411
2412    N_PAR_PRICE_LINE_ID     numeric,
2413
2414
2415    N_SUBSCR_PERMISSION_ID  numeric,
2416
2417
2418    VC_SUBSCR_PERMISSION_ID VARCHAR(255),
2419
2420
2421    N_PARENT_GOOD_ID        numeric,
2422
2423
2424    VC_PARENT_GOOD          VARCHAR(255),
2425
2426
2427    VC_USER_REM             VARCHAR(255),
2428
2429
2430    N_OBJECT_ID             numeric,
2431
2432
2433    C_FL_ACTIVE             VARCHAR(255)
2434
2435
2436);
2437
2438
2439     -> 
2440
2441
2442alter table AP_V_USER_OFFICE_ACCESS_SERVS
2443
2444
2445    owner to intercon_user;
2446
2447
2448     -> create table if not exists AP_V_USER_OFFICE_DEVICES
2449
2450
2451(
2452
2453
2454    N_DEVICE_ID       numeric,
2455
2456
2457    N_USE_DEVICE_ID   numeric,
2458
2459
2460    VC_ADDR_CODE      VARCHAR(255),
2461
2462
2463    VC_DEVICE_CODE    VARCHAR(255),
2464
2465
2466    N_CATALOG_ITEM_ID numeric,
2467
2468
2469    N_DEV_STATE_ID    numeric,
2470
2471
2472    VC_DEV_STATE      VARCHAR(255),
2473
2474
2475    VC_PORT_CODE      VARCHAR(255),
2476
2477
2478    VC_IP_CODE        VARCHAR(255),
2479
2480
2481    VC_SUBNET_MASK    VARCHAR(255),
2482
2483
2484    VC_SUBNET_CODE    VARCHAR(255),
2485
2486
2487    VC_MAC_CODE       VARCHAR(255)
2488
2489
2490);
2491
2492
2493     -> 
2494
2495
2496alter table AP_V_USER_OFFICE_DEVICES
2497
2498
2499    owner to intercon_user;
2500
2501
2502     -> CREATE TABLE public.sessions
2503
2504
2505(
2506
2507
2508    id           character varying(180) NOT NULL,
2509
2510
2511    user_id      numeric                NOT NULL,
2512
2513
2514    curr_session boolean
2515
2516
2517);
2518
2519
2520     -> 
2521
2522
2523ALTER TABLE public.sessions
2524
2525
2526    OWNER TO intercon_user;
2527
2528
2529     -> 
2530
2531
2532ALTER TABLE ONLY public.sessions
2533
2534
2535    ADD CONSTRAINT sessions_pk PRIMARY KEY (id);
2536
2537
2538     -> CREATE table if not exists SI_V_ENTITIES_TAGS
2539
2540
2541(
2542
2543
2544    N_ENTITY_TAG_ID  numeric,
2545
2546
2547    N_TAG_ID         numeric,
2548
2549
2550    N_ENTITY_ID      NUMERIC,
2551
2552
2553    N_ENTITY_TYPE_ID numeric,
2554
2555
2556    N_LINE_NO        numeric,
2557
2558
2559    VC_CODE          VARCHAR(255),
2560
2561
2562    C_FL_EDITABLE    CHAR
2563
2564
2565);
2566
2567
2568     -> 
2569
2570
2571alter table SI_V_ENTITIES_TAGS
2572
2573
2574    owner to intercon_user;
2575
2576
2577     -> 
2578
2579
2580create function func_login(login character varying, pass character varying, session_id character varying) returns bool
2581
2582
2583    language plpgsql
2584
2585
2586as
2587
2588
2589$$
2590
2591
2592DECLARE
2593
2594
2595    count_session_id int;
2596
2597
2598BEGIN
2599
2600
2601    count_session_id := (SELECT count(s) FROM sessions as s WHERE s.id LIKE session_id);
2602
2603
2604    IF func_get_user_id(login, pass) THEN
2605
2606
2607        IF count_session_id > 0 THEN
2608
2609
2610            return false;
2611
2612
2613        end if;
2614
2615
2616
2617
2618        INSERT into sessions(id, user_id, curr_session)
2619
2620
2621        values (session_id, (SELECT au.N_SUBJ_SERV_ID
2622
2623
2624                             FROM AP_V_SERVICES as au
2625
2626
2627                             WHERE au.VC_LOGIN_REAL = login
2628
2629
2630                               and au.password = pass
2631
2632
2633                             LIMIT 1), true);
2634
2635
2636        return true;
2637
2638
2639    ELSE
2640
2641
2642        return false;
2643
2644
2645    end if;
2646
2647
2648end ;
2649
2650
2651$$;
2652
2653
2654     -> 
2655
2656
2657alter function func_login(VARCHAR, VARCHAR, VARCHAR) owner to intercon_user;
2658
2659
2660     -> create function func_get_user_id(login character varying, pass character varying) returns boolean
2661
2662
2663    language plpgsql
2664
2665
2666as
2667
2668
2669$$
2670
2671
2672DECLARE
2673
2674
2675    id numeric;
2676
2677
2678BEGIN
2679
2680
2681    id := (
2682
2683
2684        SELECT au.N_SUBJ_SERV_ID
2685
2686
2687        FROM AP_V_SERVICES as au
2688
2689
2690        WHERE au.VC_LOGIN_REAL = login
2691
2692
2693          and au.password = pass
2694
2695
2696        LIMIT 1);
2697
2698
2699    IF id IS NOT NULL then
2700
2701
2702        return true;
2703
2704
2705    else
2706
2707
2708        return false;
2709
2710
2711    end if;
2712
2713
2714
2715
2716end ;
2717
2718
2719$$;
2720
2721
2722     -> alter function func_get_user_id(VARCHAR, VARCHAR) owner to intercon_user;
2723
2724
2725     -> CREATE OR REPLACE FUNCTION switch_context(session_id VARCHAR(180))
2726
2727
2728    returns void
2729
2730
2731AS
2732
2733
2734$$
2735
2736
2737BEGIN
2738
2739
2740    UPDATE sessions as ses SET curr_session = false Where ses.id <> session_id;
2741
2742
2743    UPDATE sessions as ses SET curr_session = true WHERE ses.id = session_id;
2744
2745
2746end;
2747
2748
2749$$
2750
2751
2752    LANGUAGE PLpgSQL;
2753
2754
2755     -> CREATE OR REPLACE FUNCTION logoff(session_id VARCHAR(180))
2756
2757
2758    returns void
2759
2760
2761AS
2762
2763
2764$$
2765
2766
2767BEGIN
2768
2769
2770    DELETE FROM sessions WHERE id = session_id;
2771
2772
2773end;
2774
2775
2776$$
2777
2778
2779    LANGUAGE PLpgSQL;
2780
2781
2782     -> create function suspend_service(good_id numeric, instant boolean) returns varchar
2783
2784
2785    language plpgsql
2786
2787
2788as
2789
2790
2791$$
2792
2793
2794DECLARE
2795
2796
2797    flag varchar;
2798
2799
2800BEGIN
2801
2802
2803    IF instant THEN
2804
2805
2806        flag = 'Y';
2807
2808
2809        UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
2810
2811
2812        return 1;
2813
2814
2815    ELSE
2816
2817
2818        flag = 'N';
2819
2820
2821        return 2;
2822
2823
2824    end if;
2825
2826
2827
2828
2829end;
2830
2831
2832$$;
2833
2834
2835     -> alter function suspend_service(numeric, boolean) owner to intercon_user;
2836
2837
2838     -> create function stop_temp_blocking(good_id numeric, date_stop date) returns character varying
2839
2840
2841    language plpgsql
2842
2843
2844as
2845
2846
2847$$
2848
2849
2850DECLARE
2851
2852
2853    flag varchar;
2854
2855
2856BEGIN
2857
2858
2859    flag = (Select c_fl_active
2860
2861
2862            from AP_V_USER_OFFICE_ACCESS_SERVS as uoas
2863
2864
2865            where N_GOOD_ID = good_id);
2866
2867
2868    if flag = 'N' then
2869
2870
2871        return 'null';
2872
2873
2874    end if;
2875
2876
2877    UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
2878
2879
2880    return '1';
2881
2882
2883end;
2884
2885
2886$$;
2887
2888
2889     -> alter function stop_temp_blocking(numeric) owner to intercon_user;
2890
2891
2892Migration 20191009125712 failed during Execution. Error An exception occurred while executing 'alter function stop_temp_blocking(numeric) owner to intercon_user;':
2893
2894
2895
2896
2897SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(numeric) does not exist
2898
2899
2900
2901
2902In AbstractPostgreSQLDriver.php line 79:
2903
2904
2905                                                                               
2906
2907
2908  An exception occurred while executing 'alter function stop_temp_blocking(nu  
2909
2910
2911  meric) owner to intercon_user;':                                             
2912
2913
2914                                                                               
2915
2916
2917  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
2918
2919
2920  numeric) does not exist                                                      
2921
2922
2923                                                                               
2924
2925
2926
2927
2928In PDOConnection.php line 80:
2929
2930
2931                                                                               
2932
2933
2934  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
2935
2936
2937  numeric) does not exist                                                      
2938
2939
2940                                                                               
2941
2942
2943
2944
2945In PDOConnection.php line 75:
2946
2947
2948                                                                               
2949
2950
2951  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
2952
2953
2954  numeric) does not exist                                                      
2955
2956
2957                                                                               
2958
2959
2960
2961
2962doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]
2963
2964
2965
2966
2967                                                              
2968
2969
2970                    Application Migrations                    
2971
2972
2973                                                              
2974
2975
2976
2977
2978Migrating up to 20191009125712 from 0
2979
2980
2981
2982
2983  ++ migrating 20191009125712
2984
2985
2986
2987
2988     -> CREATE SEQUENCE accounts_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2989
2990
2991     -> alter sequence accounts_id_seq owner to intercon_user;
2992
2993
2994     -> CREATE SEQUENCE AP_V_USER_OFFICE_GOODS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
2995
2996
2997     -> alter sequence AP_V_USER_OFFICE_GOODS_id_seq owner to intercon_user;
2998
2999
3000     -> CREATE SEQUENCE ap_v_services_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3001
3002
3003     -> alter sequence ap_v_services_id_seq owner to intercon_user;
3004
3005
3006     -> CREATE SEQUENCE AP_V_PERSONS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3007
3008
3009     -> alter sequence AP_V_PERSONS_id_seq owner to intercon_user;
3010
3011
3012     -> CREATE SEQUENCE sessions_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3013
3014
3015     -> alter sequence sessions_id_seq owner to intercon_user;
3016
3017
3018     -> create table if not exists AP_V_SERVICES
3019
3020
3021(
3022
3023
3024    N_SUBJ_SERV_ID         numeric,
3025
3026
3027    N_SUBJ_SERV_TYPE_ID    numeric,
3028
3029
3030    N_SERVICE_ID           numeric,
3031
3032
3033    N_OBJECT_ID            numeric,
3034
3035
3036    N_SUBJECT_ID           numeric,
3037
3038
3039    VC_SERVICE             VARCHAR(255),
3040
3041
3042    VC_OBJECT              VARCHAR(255),
3043
3044
3045    N_OBJ_GOOD_ID          numeric,
3046
3047
3048    VC_OBJ_GOOD            VARCHAR(255),
3049
3050
3051    N_OBJ_ADDRESS_ID       numeric,
3052
3053
3054    N_PORT_NO              numeric,
3055
3056
3057    VC_LOGIN               VARCHAR(255),
3058
3059
3060    VC_LOGIN_REAL          VARCHAR(255),
3061
3062
3063    C_FL_EMPTY_PASS        VARCHAR(255),
3064
3065
3066    VC_VALUE               VARCHAR(255),
3067
3068
3069    N_VALUE                numeric,
3070
3071
3072    C_CANT_CHANGE          CHAR,
3073
3074
3075    N_SERV_AUTH_TYPE       numeric,
3076
3077
3078    N_AUTH_TYPE_ID         numeric,
3079
3080
3081    C_FL_APP               VARCHAR(255),
3082
3083
3084    C_FL_PASS_TESTING      VARCHAR(255),
3085
3086
3087    C_FL_PLAINTEXT_PASS    CHAR,
3088
3089
3090    N_DEFAULT_HASH_TYPE_ID numeric,
3091
3092
3093    VC_PASSWORD_PATTERN    VARCHAR(255),
3094
3095
3096    password               varchar(180),
3097
3098
3099    roles                  json
3100
3101
3102);
3103
3104
3105     -> 
3106
3107
3108alter table AP_V_SERVICES
3109
3110
3111    owner to intercon_user;
3112
3113
3114     -> create table if not exists AP_V_PERSONAL_ACCOUNTS
3115
3116
3117(
3118
3119
3120    N_SUBJECT_ID                 numeric,
3121
3122
3123    VC_SUBJ_CODE                 varchar(255),
3124
3125
3126    VC_SUBJ_NAME                 varchar(255),
3127
3128
3129    N_ACCOUNT_ID                 numeric,
3130
3131
3132    N_CURRENCY_ID                numeric,
3133
3134
3135    VC_CURRENCY                  varchar(255),
3136
3137
3138    VC_CURRENCY_CODE             varchar(255),
3139
3140
3141    VC_CODE                      varchar(255),
3142
3143
3144    VC_NAME                      varchar(255),
3145
3146
3147    VC_ACCOUNT                   varchar(255),
3148
3149
3150    N_OVERDRAFT                  numeric,
3151
3152
3153    D_OVERDRAFT_END              DATE,
3154
3155
3156    N_PERMANENT_OVERDRAFT        numeric,
3157
3158
3159    N_TEMPORAL_OVERDRAFT         numeric,
3160
3161
3162    D_TEMP_OVERDRAFT_END         DATE,
3163
3164
3165    N_SCHED_SERV_OVERDRAFT       numeric,
3166
3167
3168    D_SCHED_SERV_OVERDRAFT_END   DATE,
3169
3170
3171    N_UNSCHED_SERV_OVERDRAFT     numeric,
3172
3173
3174    D_UNSCHED_SERV_OVERDRAFT_END DATE,
3175
3176
3177    N_SUM_BAL                    numeric,
3178
3179
3180    N_SUM_RESERVED               numeric,
3181
3182
3183    N_SUM_RESERVED_CUR           numeric,
3184
3185
3186    N_SUM_FREE                   numeric,
3187
3188
3189    D_ACCOUNTING_BEGIN           DATE,
3190
3191
3192    D_ACCOUNTING_END             DATE,
3193
3194
3195    N_LAST_PAYMENT_SUM           numeric,
3196
3197
3198    D_LAST_PAYMENT               DATE,
3199
3200
3201    VC_LAST_PAYMENT_TYPE         varchar(255),
3202
3203
3204    N_LAST_PAYMENT_BANK_ID       numeric,
3205
3206
3207    VC_LAST_PAYMENT_BANK         varchar(255),
3208
3209
3210    N_RECOMMENDED_PAY            numeric,
3211
3212
3213    C_FL_PROMISED_PAYMENT        varchar(255),
3214
3215
3216    N_CURRENT_PROMISED_PAY_SUM   numeric,
3217
3218
3219    D_CURRENT_PROMISED_PAY_DAY   DATE
3220
3221
3222);
3223
3224
3225     -> 
3226
3227
3228alter table AP_V_PERSONAL_ACCOUNTS
3229
3230
3231    owner to intercon_user;
3232
3233
3234     -> create table if not exists AP_V_USER_OFFICE_GOODS
3235
3236
3237(
3238
3239
3240    N_SUBJ_GOOD_ID            numeric,
3241
3242
3243    N_LINE_NO                 numeric,
3244
3245
3246    N_PAR_SUBJ_GOOD_ID        numeric,
3247
3248
3249    N_GOOD_ID                 numeric,
3250
3251
3252    VC_GOOD                   VARCHAR(255),
3253
3254
3255    N_ACCOUNT_ID              numeric,
3256
3257
3258    VC_ACCOUNT                VARCHAR(255),
3259
3260
3261    N_OBJECT_ID               numeric,
3262
3263
3264    VC_OBJECT                 VARCHAR(255),
3265
3266
3267    N_PAY_DAY                 numeric,
3268
3269
3270    N_SUBSCR_PERMISSION_ID    numeric,
3271
3272
3273    N_SERV_TYPE_ID            numeric,
3274
3275
3276    N_UNSUBSCR_PERMISSION_ID  numeric,
3277
3278
3279    N_SERV_END_CHARGE_TYPE_ID numeric,
3280
3281
3282    N_DOC_ID                  numeric,
3283
3284
3285    N_DOC_TYPE_ID             numeric,
3286
3287
3288    VC_DOC_NAME               VARCHAR(255),
3289
3290
3291    VC_DOC_CODE               VARCHAR(255),
3292
3293
3294    D_BEGIN                   DATE,
3295
3296
3297    D_END                     DATE,
3298
3299
3300    N_INVOICE_ID              numeric,
3301
3302
3303    VC_INVOICE_CODE           VARCHAR(255),
3304
3305
3306    VC_INVOICE_NAME           VARCHAR(255)
3307
3308
3309);
3310
3311
3312     -> 
3313
3314
3315alter table AP_V_USER_OFFICE_GOODS
3316
3317
3318    owner to intercon_user;
3319
3320
3321     -> create table if not exists AP_V_PERSONS
3322
3323
3324(
3325
3326
3327    N_PERSON_ID    numeric      not null
3328
3329
3330        constraint profile_pkey
3331
3332
3333            primary key,
3334
3335
3336    VC_FIRST_NAME  VARCHAR(255) not null,
3337
3338
3339    VC_SURNAME     VARCHAR(255) not null,
3340
3341
3342    VC_SECOND_NAME VARCHAR(255) not null,
3343
3344
3345    VC_NAME        VARCHAR(255),
3346
3347
3348    N_SEX_ID       NUMERIC,
3349
3350
3351    D_BIRTH        DATE
3352
3353
3354);
3355
3356
3357     -> 
3358
3359
3360alter table AP_V_PERSONS
3361
3362
3363    owner to intercon_user;
3364
3365
3366     -> create table if not exists AP_V_USER_OFFICE_ACCESS_SERVS
3367
3368
3369(
3370
3371
3372    N_DOC_ID                numeric,
3373
3374
3375    N_USER_ID               numeric,
3376
3377
3378    N_GOOD_ID               NUMERIC,
3379
3380
3381    N_GOOD_TYPE_ID          numeric,
3382
3383
3384    VC_NAME                 VARCHAR(255),
3385
3386
3387    VC_CODE                 VARCHAR(255),
3388
3389
3390    N_PRICE                 numeric,
3391
3392
3393    N_CURRENCY_ID           numeric,
3394
3395
3396    VC_CURRENCY             VARCHAR(255),
3397
3398
3399    N_PAR_PRICE_LINE_ID     numeric,
3400
3401
3402    N_SUBSCR_PERMISSION_ID  numeric,
3403
3404
3405    VC_SUBSCR_PERMISSION_ID VARCHAR(255),
3406
3407
3408    N_PARENT_GOOD_ID        numeric,
3409
3410
3411    VC_PARENT_GOOD          VARCHAR(255),
3412
3413
3414    VC_USER_REM             VARCHAR(255),
3415
3416
3417    N_OBJECT_ID             numeric,
3418
3419
3420    C_FL_ACTIVE             VARCHAR(255)
3421
3422
3423);
3424
3425
3426     -> 
3427
3428
3429alter table AP_V_USER_OFFICE_ACCESS_SERVS
3430
3431
3432    owner to intercon_user;
3433
3434
3435     -> create table if not exists AP_V_USER_OFFICE_DEVICES
3436
3437
3438(
3439
3440
3441    N_DEVICE_ID       numeric,
3442
3443
3444    N_USE_DEVICE_ID   numeric,
3445
3446
3447    VC_ADDR_CODE      VARCHAR(255),
3448
3449
3450    VC_DEVICE_CODE    VARCHAR(255),
3451
3452
3453    N_CATALOG_ITEM_ID numeric,
3454
3455
3456    N_DEV_STATE_ID    numeric,
3457
3458
3459    VC_DEV_STATE      VARCHAR(255),
3460
3461
3462    VC_PORT_CODE      VARCHAR(255),
3463
3464
3465    VC_IP_CODE        VARCHAR(255),
3466
3467
3468    VC_SUBNET_MASK    VARCHAR(255),
3469
3470
3471    VC_SUBNET_CODE    VARCHAR(255),
3472
3473
3474    VC_MAC_CODE       VARCHAR(255)
3475
3476
3477);
3478
3479
3480     -> 
3481
3482
3483alter table AP_V_USER_OFFICE_DEVICES
3484
3485
3486    owner to intercon_user;
3487
3488
3489     -> CREATE TABLE public.sessions
3490
3491
3492(
3493
3494
3495    id           character varying(180) NOT NULL,
3496
3497
3498    user_id      numeric                NOT NULL,
3499
3500
3501    curr_session boolean
3502
3503
3504);
3505
3506
3507     -> 
3508
3509
3510ALTER TABLE public.sessions
3511
3512
3513    OWNER TO intercon_user;
3514
3515
3516     -> 
3517
3518
3519ALTER TABLE ONLY public.sessions
3520
3521
3522    ADD CONSTRAINT sessions_pk PRIMARY KEY (id);
3523
3524
3525     -> CREATE table if not exists SI_V_ENTITIES_TAGS
3526
3527
3528(
3529
3530
3531    N_ENTITY_TAG_ID  numeric,
3532
3533
3534    N_TAG_ID         numeric,
3535
3536
3537    N_ENTITY_ID      NUMERIC,
3538
3539
3540    N_ENTITY_TYPE_ID numeric,
3541
3542
3543    N_LINE_NO        numeric,
3544
3545
3546    VC_CODE          VARCHAR(255),
3547
3548
3549    C_FL_EDITABLE    CHAR
3550
3551
3552);
3553
3554
3555     -> 
3556
3557
3558alter table SI_V_ENTITIES_TAGS
3559
3560
3561    owner to intercon_user;
3562
3563
3564     -> 
3565
3566
3567create function func_login(login character varying, pass character varying, session_id character varying) returns bool
3568
3569
3570    language plpgsql
3571
3572
3573as
3574
3575
3576$$
3577
3578
3579DECLARE
3580
3581
3582    count_session_id int;
3583
3584
3585BEGIN
3586
3587
3588    count_session_id := (SELECT count(s) FROM sessions as s WHERE s.id LIKE session_id);
3589
3590
3591    IF func_get_user_id(login, pass) THEN
3592
3593
3594        IF count_session_id > 0 THEN
3595
3596
3597            return false;
3598
3599
3600        end if;
3601
3602
3603
3604
3605        INSERT into sessions(id, user_id, curr_session)
3606
3607
3608        values (session_id, (SELECT au.N_SUBJ_SERV_ID
3609
3610
3611                             FROM AP_V_SERVICES as au
3612
3613
3614                             WHERE au.VC_LOGIN_REAL = login
3615
3616
3617                               and au.password = pass
3618
3619
3620                             LIMIT 1), true);
3621
3622
3623        return true;
3624
3625
3626    ELSE
3627
3628
3629        return false;
3630
3631
3632    end if;
3633
3634
3635end ;
3636
3637
3638$$;
3639
3640
3641     -> 
3642
3643
3644alter function func_login(VARCHAR, VARCHAR, VARCHAR) owner to intercon_user;
3645
3646
3647     -> create function func_get_user_id(login character varying, pass character varying) returns boolean
3648
3649
3650    language plpgsql
3651
3652
3653as
3654
3655
3656$$
3657
3658
3659DECLARE
3660
3661
3662    id numeric;
3663
3664
3665BEGIN
3666
3667
3668    id := (
3669
3670
3671        SELECT au.N_SUBJ_SERV_ID
3672
3673
3674        FROM AP_V_SERVICES as au
3675
3676
3677        WHERE au.VC_LOGIN_REAL = login
3678
3679
3680          and au.password = pass
3681
3682
3683        LIMIT 1);
3684
3685
3686    IF id IS NOT NULL then
3687
3688
3689        return true;
3690
3691
3692    else
3693
3694
3695        return false;
3696
3697
3698    end if;
3699
3700
3701
3702
3703end ;
3704
3705
3706$$;
3707
3708
3709     -> alter function func_get_user_id(VARCHAR, VARCHAR) owner to intercon_user;
3710
3711
3712     -> CREATE OR REPLACE FUNCTION switch_context(session_id VARCHAR(180))
3713
3714
3715    returns void
3716
3717
3718AS
3719
3720
3721$$
3722
3723
3724BEGIN
3725
3726
3727    UPDATE sessions as ses SET curr_session = false Where ses.id <> session_id;
3728
3729
3730    UPDATE sessions as ses SET curr_session = true WHERE ses.id = session_id;
3731
3732
3733end;
3734
3735
3736$$
3737
3738
3739    LANGUAGE PLpgSQL;
3740
3741
3742     -> CREATE OR REPLACE FUNCTION logoff(session_id VARCHAR(180))
3743
3744
3745    returns void
3746
3747
3748AS
3749
3750
3751$$
3752
3753
3754BEGIN
3755
3756
3757    DELETE FROM sessions WHERE id = session_id;
3758
3759
3760end;
3761
3762
3763$$
3764
3765
3766    LANGUAGE PLpgSQL;
3767
3768
3769     -> create function suspend_service(good_id numeric, instant boolean) returns varchar
3770
3771
3772    language plpgsql
3773
3774
3775as
3776
3777
3778$$
3779
3780
3781DECLARE
3782
3783
3784    flag varchar;
3785
3786
3787BEGIN
3788
3789
3790    IF instant THEN
3791
3792
3793        flag = 'Y';
3794
3795
3796        UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
3797
3798
3799        return 1;
3800
3801
3802    ELSE
3803
3804
3805        flag = 'N';
3806
3807
3808        return 2;
3809
3810
3811    end if;
3812
3813
3814
3815
3816end;
3817
3818
3819$$;
3820
3821
3822     -> alter function suspend_service(numeric, boolean) owner to intercon_user;
3823
3824
3825     -> create function stop_temp_blocking(good_id numeric, date_stop date) returns character varying
3826
3827
3828    language plpgsql
3829
3830
3831as
3832
3833
3834$$
3835
3836
3837DECLARE
3838
3839
3840    flag varchar;
3841
3842
3843BEGIN
3844
3845
3846    flag = (Select c_fl_active
3847
3848
3849            from AP_V_USER_OFFICE_ACCESS_SERVS as uoas
3850
3851
3852            where N_GOOD_ID = good_id);
3853
3854
3855    if flag = 'N' then
3856
3857
3858        return 'null';
3859
3860
3861    end if;
3862
3863
3864    UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
3865
3866
3867    return '1';
3868
3869
3870end;
3871
3872
3873$$;
3874
3875
3876     -> alter function stop_temp_blocking(numeric) owner to intercon_user;
3877
3878
3879Migration 20191009125712 failed during Execution. Error An exception occurred while executing 'alter function stop_temp_blocking(numeric) owner to intercon_user;':
3880
3881
3882
3883
3884SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(numeric) does not exist
3885
3886
3887
3888
3889In AbstractPostgreSQLDriver.php line 79:
3890
3891
3892                                                                               
3893
3894
3895  An exception occurred while executing 'alter function stop_temp_blocking(nu  
3896
3897
3898  meric) owner to intercon_user;':                                             
3899
3900
3901                                                                               
3902
3903
3904  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
3905
3906
3907  numeric) does not exist                                                      
3908
3909
3910                                                                               
3911
3912
3913
3914
3915In PDOConnection.php line 80:
3916
3917
3918                                                                               
3919
3920
3921  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
3922
3923
3924  numeric) does not exist                                                      
3925
3926
3927                                                                               
3928
3929
3930
3931
3932In PDOConnection.php line 75:
3933
3934
3935                                                                               
3936
3937
3938  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
3939
3940
3941  numeric) does not exist                                                      
3942
3943
3944                                                                               
3945
3946
3947
3948
3949doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]
3950
3951
3952
3953
3954                                                              
3955
3956
3957                    Application Migrations                    
3958
3959
3960                                                              
3961
3962
3963
3964
3965Migrating up to 20191009125712 from 0
3966
3967
3968
3969
3970  ++ migrating 20191009125712
3971
3972
3973
3974
3975     -> CREATE SEQUENCE accounts_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3976
3977
3978     -> alter sequence accounts_id_seq owner to intercon_user;
3979
3980
3981     -> CREATE SEQUENCE AP_V_USER_OFFICE_GOODS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3982
3983
3984     -> alter sequence AP_V_USER_OFFICE_GOODS_id_seq owner to intercon_user;
3985
3986
3987     -> CREATE SEQUENCE ap_v_services_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3988
3989
3990     -> alter sequence ap_v_services_id_seq owner to intercon_user;
3991
3992
3993     -> CREATE SEQUENCE AP_V_PERSONS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
3994
3995
3996     -> alter sequence AP_V_PERSONS_id_seq owner to intercon_user;
3997
3998
3999     -> CREATE SEQUENCE sessions_id_seq INCREMENT BY 1 MINVALUE 1 START 1
4000
4001
4002     -> alter sequence sessions_id_seq owner to intercon_user;
4003
4004
4005     -> create table if not exists AP_V_SERVICES
4006
4007
4008(
4009
4010
4011    N_SUBJ_SERV_ID         numeric,
4012
4013
4014    N_SUBJ_SERV_TYPE_ID    numeric,
4015
4016
4017    N_SERVICE_ID           numeric,
4018
4019
4020    N_OBJECT_ID            numeric,
4021
4022
4023    N_SUBJECT_ID           numeric,
4024
4025
4026    VC_SERVICE             VARCHAR(255),
4027
4028
4029    VC_OBJECT              VARCHAR(255),
4030
4031
4032    N_OBJ_GOOD_ID          numeric,
4033
4034
4035    VC_OBJ_GOOD            VARCHAR(255),
4036
4037
4038    N_OBJ_ADDRESS_ID       numeric,
4039
4040
4041    N_PORT_NO              numeric,
4042
4043
4044    VC_LOGIN               VARCHAR(255),
4045
4046
4047    VC_LOGIN_REAL          VARCHAR(255),
4048
4049
4050    C_FL_EMPTY_PASS        VARCHAR(255),
4051
4052
4053    VC_VALUE               VARCHAR(255),
4054
4055
4056    N_VALUE                numeric,
4057
4058
4059    C_CANT_CHANGE          CHAR,
4060
4061
4062    N_SERV_AUTH_TYPE       numeric,
4063
4064
4065    N_AUTH_TYPE_ID         numeric,
4066
4067
4068    C_FL_APP               VARCHAR(255),
4069
4070
4071    C_FL_PASS_TESTING      VARCHAR(255),
4072
4073
4074    C_FL_PLAINTEXT_PASS    CHAR,
4075
4076
4077    N_DEFAULT_HASH_TYPE_ID numeric,
4078
4079
4080    VC_PASSWORD_PATTERN    VARCHAR(255),
4081
4082
4083    password               varchar(180),
4084
4085
4086    roles                  json
4087
4088
4089);
4090
4091
4092     -> 
4093
4094
4095alter table AP_V_SERVICES
4096
4097
4098    owner to intercon_user;
4099
4100
4101     -> create table if not exists AP_V_PERSONAL_ACCOUNTS
4102
4103
4104(
4105
4106
4107    N_SUBJECT_ID                 numeric,
4108
4109
4110    VC_SUBJ_CODE                 varchar(255),
4111
4112
4113    VC_SUBJ_NAME                 varchar(255),
4114
4115
4116    N_ACCOUNT_ID                 numeric,
4117
4118
4119    N_CURRENCY_ID                numeric,
4120
4121
4122    VC_CURRENCY                  varchar(255),
4123
4124
4125    VC_CURRENCY_CODE             varchar(255),
4126
4127
4128    VC_CODE                      varchar(255),
4129
4130
4131    VC_NAME                      varchar(255),
4132
4133
4134    VC_ACCOUNT                   varchar(255),
4135
4136
4137    N_OVERDRAFT                  numeric,
4138
4139
4140    D_OVERDRAFT_END              DATE,
4141
4142
4143    N_PERMANENT_OVERDRAFT        numeric,
4144
4145
4146    N_TEMPORAL_OVERDRAFT         numeric,
4147
4148
4149    D_TEMP_OVERDRAFT_END         DATE,
4150
4151
4152    N_SCHED_SERV_OVERDRAFT       numeric,
4153
4154
4155    D_SCHED_SERV_OVERDRAFT_END   DATE,
4156
4157
4158    N_UNSCHED_SERV_OVERDRAFT     numeric,
4159
4160
4161    D_UNSCHED_SERV_OVERDRAFT_END DATE,
4162
4163
4164    N_SUM_BAL                    numeric,
4165
4166
4167    N_SUM_RESERVED               numeric,
4168
4169
4170    N_SUM_RESERVED_CUR           numeric,
4171
4172
4173    N_SUM_FREE                   numeric,
4174
4175
4176    D_ACCOUNTING_BEGIN           DATE,
4177
4178
4179    D_ACCOUNTING_END             DATE,
4180
4181
4182    N_LAST_PAYMENT_SUM           numeric,
4183
4184
4185    D_LAST_PAYMENT               DATE,
4186
4187
4188    VC_LAST_PAYMENT_TYPE         varchar(255),
4189
4190
4191    N_LAST_PAYMENT_BANK_ID       numeric,
4192
4193
4194    VC_LAST_PAYMENT_BANK         varchar(255),
4195
4196
4197    N_RECOMMENDED_PAY            numeric,
4198
4199
4200    C_FL_PROMISED_PAYMENT        varchar(255),
4201
4202
4203    N_CURRENT_PROMISED_PAY_SUM   numeric,
4204
4205
4206    D_CURRENT_PROMISED_PAY_DAY   DATE
4207
4208
4209);
4210
4211
4212     -> 
4213
4214
4215alter table AP_V_PERSONAL_ACCOUNTS
4216
4217
4218    owner to intercon_user;
4219
4220
4221     -> create table if not exists AP_V_USER_OFFICE_GOODS
4222
4223
4224(
4225
4226
4227    N_SUBJ_GOOD_ID            numeric,
4228
4229
4230    N_LINE_NO                 numeric,
4231
4232
4233    N_PAR_SUBJ_GOOD_ID        numeric,
4234
4235
4236    N_GOOD_ID                 numeric,
4237
4238
4239    VC_GOOD                   VARCHAR(255),
4240
4241
4242    N_ACCOUNT_ID              numeric,
4243
4244
4245    VC_ACCOUNT                VARCHAR(255),
4246
4247
4248    N_OBJECT_ID               numeric,
4249
4250
4251    VC_OBJECT                 VARCHAR(255),
4252
4253
4254    N_PAY_DAY                 numeric,
4255
4256
4257    N_SUBSCR_PERMISSION_ID    numeric,
4258
4259
4260    N_SERV_TYPE_ID            numeric,
4261
4262
4263    N_UNSUBSCR_PERMISSION_ID  numeric,
4264
4265
4266    N_SERV_END_CHARGE_TYPE_ID numeric,
4267
4268
4269    N_DOC_ID                  numeric,
4270
4271
4272    N_DOC_TYPE_ID             numeric,
4273
4274
4275    VC_DOC_NAME               VARCHAR(255),
4276
4277
4278    VC_DOC_CODE               VARCHAR(255),
4279
4280
4281    D_BEGIN                   DATE,
4282
4283
4284    D_END                     DATE,
4285
4286
4287    N_INVOICE_ID              numeric,
4288
4289
4290    VC_INVOICE_CODE           VARCHAR(255),
4291
4292
4293    VC_INVOICE_NAME           VARCHAR(255)
4294
4295
4296);
4297
4298
4299     -> 
4300
4301
4302alter table AP_V_USER_OFFICE_GOODS
4303
4304
4305    owner to intercon_user;
4306
4307
4308     -> create table if not exists AP_V_PERSONS
4309
4310
4311(
4312
4313
4314    N_PERSON_ID    numeric      not null
4315
4316
4317        constraint profile_pkey
4318
4319
4320            primary key,
4321
4322
4323    VC_FIRST_NAME  VARCHAR(255) not null,
4324
4325
4326    VC_SURNAME     VARCHAR(255) not null,
4327
4328
4329    VC_SECOND_NAME VARCHAR(255) not null,
4330
4331
4332    VC_NAME        VARCHAR(255),
4333
4334
4335    N_SEX_ID       NUMERIC,
4336
4337
4338    D_BIRTH        DATE
4339
4340
4341);
4342
4343
4344     -> 
4345
4346
4347alter table AP_V_PERSONS
4348
4349
4350    owner to intercon_user;
4351
4352
4353     -> create table if not exists AP_V_USER_OFFICE_ACCESS_SERVS
4354
4355
4356(
4357
4358
4359    N_DOC_ID                numeric,
4360
4361
4362    N_USER_ID               numeric,
4363
4364
4365    N_GOOD_ID               NUMERIC,
4366
4367
4368    N_GOOD_TYPE_ID          numeric,
4369
4370
4371    VC_NAME                 VARCHAR(255),
4372
4373
4374    VC_CODE                 VARCHAR(255),
4375
4376
4377    N_PRICE                 numeric,
4378
4379
4380    N_CURRENCY_ID           numeric,
4381
4382
4383    VC_CURRENCY             VARCHAR(255),
4384
4385
4386    N_PAR_PRICE_LINE_ID     numeric,
4387
4388
4389    N_SUBSCR_PERMISSION_ID  numeric,
4390
4391
4392    VC_SUBSCR_PERMISSION_ID VARCHAR(255),
4393
4394
4395    N_PARENT_GOOD_ID        numeric,
4396
4397
4398    VC_PARENT_GOOD          VARCHAR(255),
4399
4400
4401    VC_USER_REM             VARCHAR(255),
4402
4403
4404    N_OBJECT_ID             numeric,
4405
4406
4407    C_FL_ACTIVE             VARCHAR(255)
4408
4409
4410);
4411
4412
4413     -> 
4414
4415
4416alter table AP_V_USER_OFFICE_ACCESS_SERVS
4417
4418
4419    owner to intercon_user;
4420
4421
4422     -> create table if not exists AP_V_USER_OFFICE_DEVICES
4423
4424
4425(
4426
4427
4428    N_DEVICE_ID       numeric,
4429
4430
4431    N_USE_DEVICE_ID   numeric,
4432
4433
4434    VC_ADDR_CODE      VARCHAR(255),
4435
4436
4437    VC_DEVICE_CODE    VARCHAR(255),
4438
4439
4440    N_CATALOG_ITEM_ID numeric,
4441
4442
4443    N_DEV_STATE_ID    numeric,
4444
4445
4446    VC_DEV_STATE      VARCHAR(255),
4447
4448
4449    VC_PORT_CODE      VARCHAR(255),
4450
4451
4452    VC_IP_CODE        VARCHAR(255),
4453
4454
4455    VC_SUBNET_MASK    VARCHAR(255),
4456
4457
4458    VC_SUBNET_CODE    VARCHAR(255),
4459
4460
4461    VC_MAC_CODE       VARCHAR(255)
4462
4463
4464);
4465
4466
4467     -> 
4468
4469
4470alter table AP_V_USER_OFFICE_DEVICES
4471
4472
4473    owner to intercon_user;
4474
4475
4476     -> CREATE TABLE public.sessions
4477
4478
4479(
4480
4481
4482    id           character varying(180) NOT NULL,
4483
4484
4485    user_id      numeric                NOT NULL,
4486
4487
4488    curr_session boolean
4489
4490
4491);
4492
4493
4494     -> 
4495
4496
4497ALTER TABLE public.sessions
4498
4499
4500    OWNER TO intercon_user;
4501
4502
4503     -> 
4504
4505
4506ALTER TABLE ONLY public.sessions
4507
4508
4509    ADD CONSTRAINT sessions_pk PRIMARY KEY (id);
4510
4511
4512     -> CREATE table if not exists SI_V_ENTITIES_TAGS
4513
4514
4515(
4516
4517
4518    N_ENTITY_TAG_ID  numeric,
4519
4520
4521    N_TAG_ID         numeric,
4522
4523
4524    N_ENTITY_ID      NUMERIC,
4525
4526
4527    N_ENTITY_TYPE_ID numeric,
4528
4529
4530    N_LINE_NO        numeric,
4531
4532
4533    VC_CODE          VARCHAR(255),
4534
4535
4536    C_FL_EDITABLE    CHAR
4537
4538
4539);
4540
4541
4542     -> 
4543
4544
4545alter table SI_V_ENTITIES_TAGS
4546
4547
4548    owner to intercon_user;
4549
4550
4551     -> 
4552
4553
4554create function func_login(login character varying, pass character varying, session_id character varying) returns bool
4555
4556
4557    language plpgsql
4558
4559
4560as
4561
4562
4563$$
4564
4565
4566DECLARE
4567
4568
4569    count_session_id int;
4570
4571
4572BEGIN
4573
4574
4575    count_session_id := (SELECT count(s) FROM sessions as s WHERE s.id LIKE session_id);
4576
4577
4578    IF func_get_user_id(login, pass) THEN
4579
4580
4581        IF count_session_id > 0 THEN
4582
4583
4584            return false;
4585
4586
4587        end if;
4588
4589
4590
4591
4592        INSERT into sessions(id, user_id, curr_session)
4593
4594
4595        values (session_id, (SELECT au.N_SUBJ_SERV_ID
4596
4597
4598                             FROM AP_V_SERVICES as au
4599
4600
4601                             WHERE au.VC_LOGIN_REAL = login
4602
4603
4604                               and au.password = pass
4605
4606
4607                             LIMIT 1), true);
4608
4609
4610        return true;
4611
4612
4613    ELSE
4614
4615
4616        return false;
4617
4618
4619    end if;
4620
4621
4622end ;
4623
4624
4625$$;
4626
4627
4628     -> 
4629
4630
4631alter function func_login(VARCHAR, VARCHAR, VARCHAR) owner to intercon_user;
4632
4633
4634     -> create function func_get_user_id(login character varying, pass character varying) returns boolean
4635
4636
4637    language plpgsql
4638
4639
4640as
4641
4642
4643$$
4644
4645
4646DECLARE
4647
4648
4649    id numeric;
4650
4651
4652BEGIN
4653
4654
4655    id := (
4656
4657
4658        SELECT au.N_SUBJ_SERV_ID
4659
4660
4661        FROM AP_V_SERVICES as au
4662
4663
4664        WHERE au.VC_LOGIN_REAL = login
4665
4666
4667          and au.password = pass
4668
4669
4670        LIMIT 1);
4671
4672
4673    IF id IS NOT NULL then
4674
4675
4676        return true;
4677
4678
4679    else
4680
4681
4682        return false;
4683
4684
4685    end if;
4686
4687
4688
4689
4690end ;
4691
4692
4693$$;
4694
4695
4696     -> alter function func_get_user_id(VARCHAR, VARCHAR) owner to intercon_user;
4697
4698
4699     -> CREATE OR REPLACE FUNCTION switch_context(session_id VARCHAR(180))
4700
4701
4702    returns void
4703
4704
4705AS
4706
4707
4708$$
4709
4710
4711BEGIN
4712
4713
4714    UPDATE sessions as ses SET curr_session = false Where ses.id <> session_id;
4715
4716
4717    UPDATE sessions as ses SET curr_session = true WHERE ses.id = session_id;
4718
4719
4720end;
4721
4722
4723$$
4724
4725
4726    LANGUAGE PLpgSQL;
4727
4728
4729     -> CREATE OR REPLACE FUNCTION logoff(session_id VARCHAR(180))
4730
4731
4732    returns void
4733
4734
4735AS
4736
4737
4738$$
4739
4740
4741BEGIN
4742
4743
4744    DELETE FROM sessions WHERE id = session_id;
4745
4746
4747end;
4748
4749
4750$$
4751
4752
4753    LANGUAGE PLpgSQL;
4754
4755
4756     -> create function suspend_service(good_id numeric, instant boolean) returns varchar
4757
4758
4759    language plpgsql
4760
4761
4762as
4763
4764
4765$$
4766
4767
4768DECLARE
4769
4770
4771    flag varchar;
4772
4773
4774BEGIN
4775
4776
4777    IF instant THEN
4778
4779
4780        flag = 'Y';
4781
4782
4783        UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
4784
4785
4786        return 1;
4787
4788
4789    ELSE
4790
4791
4792        flag = 'N';
4793
4794
4795        return 2;
4796
4797
4798    end if;
4799
4800
4801
4802
4803end;
4804
4805
4806$$;
4807
4808
4809     -> alter function suspend_service(numeric, boolean) owner to intercon_user;
4810
4811
4812     -> create function stop_temp_blocking(good_id numeric, date_stop date) returns character varying
4813
4814
4815    language plpgsql
4816
4817
4818as
4819
4820
4821$$
4822
4823
4824DECLARE
4825
4826
4827    flag varchar;
4828
4829
4830BEGIN
4831
4832
4833    flag = (Select c_fl_active
4834
4835
4836            from AP_V_USER_OFFICE_ACCESS_SERVS as uoas
4837
4838
4839            where N_GOOD_ID = good_id);
4840
4841
4842    if flag = 'N' then
4843
4844
4845        return 'null';
4846
4847
4848    end if;
4849
4850
4851    UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
4852
4853
4854    return '1';
4855
4856
4857end;
4858
4859
4860$$;
4861
4862
4863     -> alter function stop_temp_blocking(numeric) owner to intercon_user;
4864
4865
4866Migration 20191009125712 failed during Execution. Error An exception occurred while executing 'alter function stop_temp_blocking(numeric) owner to intercon_user;':
4867
4868
4869
4870
4871SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(numeric) does not exist
4872
4873
4874
4875
4876In AbstractPostgreSQLDriver.php line 79:
4877
4878
4879                                                                               
4880
4881
4882  An exception occurred while executing 'alter function stop_temp_blocking(nu  
4883
4884
4885  meric) owner to intercon_user;':                                             
4886
4887
4888                                                                               
4889
4890
4891  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
4892
4893
4894  numeric) does not exist                                                      
4895
4896
4897                                                                               
4898
4899
4900
4901
4902In PDOConnection.php line 80:
4903
4904
4905                                                                               
4906
4907
4908  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
4909
4910
4911  numeric) does not exist                                                      
4912
4913
4914                                                                               
4915
4916
4917
4918
4919In PDOConnection.php line 75:
4920
4921
4922                                                                               
4923
4924
4925  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
4926
4927
4928  numeric) does not exist                                                      
4929
4930
4931                                                                               
4932
4933
4934
4935
4936doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]
4937
4938
4939
4940
4941                                                              
4942
4943
4944                    Application Migrations                    
4945
4946
4947                                                              
4948
4949
4950
4951
4952Migrating up to 20191009125712 from 0
4953
4954
4955
4956
4957  ++ migrating 20191009125712
4958
4959
4960
4961
4962     -> CREATE SEQUENCE accounts_id_seq INCREMENT BY 1 MINVALUE 1 START 1
4963
4964
4965     -> alter sequence accounts_id_seq owner to intercon_user;
4966
4967
4968     -> CREATE SEQUENCE AP_V_USER_OFFICE_GOODS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
4969
4970
4971     -> alter sequence AP_V_USER_OFFICE_GOODS_id_seq owner to intercon_user;
4972
4973
4974     -> CREATE SEQUENCE ap_v_services_id_seq INCREMENT BY 1 MINVALUE 1 START 1
4975
4976
4977     -> alter sequence ap_v_services_id_seq owner to intercon_user;
4978
4979
4980     -> CREATE SEQUENCE AP_V_PERSONS_id_seq INCREMENT BY 1 MINVALUE 1 START 1
4981
4982
4983     -> alter sequence AP_V_PERSONS_id_seq owner to intercon_user;
4984
4985
4986     -> CREATE SEQUENCE sessions_id_seq INCREMENT BY 1 MINVALUE 1 START 1
4987
4988
4989     -> alter sequence sessions_id_seq owner to intercon_user;
4990
4991
4992     -> create table if not exists AP_V_SERVICES
4993
4994
4995(
4996
4997
4998    N_SUBJ_SERV_ID         numeric,
4999
5000
5001    N_SUBJ_SERV_TYPE_ID    numeric,
5002
5003
5004    N_SERVICE_ID           numeric,
5005
5006
5007    N_OBJECT_ID            numeric,
5008
5009
5010    N_SUBJECT_ID           numeric,
5011
5012
5013    VC_SERVICE             VARCHAR(255),
5014
5015
5016    VC_OBJECT              VARCHAR(255),
5017
5018
5019    N_OBJ_GOOD_ID          numeric,
5020
5021
5022    VC_OBJ_GOOD            VARCHAR(255),
5023
5024
5025    N_OBJ_ADDRESS_ID       numeric,
5026
5027
5028    N_PORT_NO              numeric,
5029
5030
5031    VC_LOGIN               VARCHAR(255),
5032
5033
5034    VC_LOGIN_REAL          VARCHAR(255),
5035
5036
5037    C_FL_EMPTY_PASS        VARCHAR(255),
5038
5039
5040    VC_VALUE               VARCHAR(255),
5041
5042
5043    N_VALUE                numeric,
5044
5045
5046    C_CANT_CHANGE          CHAR,
5047
5048
5049    N_SERV_AUTH_TYPE       numeric,
5050
5051
5052    N_AUTH_TYPE_ID         numeric,
5053
5054
5055    C_FL_APP               VARCHAR(255),
5056
5057
5058    C_FL_PASS_TESTING      VARCHAR(255),
5059
5060
5061    C_FL_PLAINTEXT_PASS    CHAR,
5062
5063
5064    N_DEFAULT_HASH_TYPE_ID numeric,
5065
5066
5067    VC_PASSWORD_PATTERN    VARCHAR(255),
5068
5069
5070    password               varchar(180),
5071
5072
5073    roles                  json
5074
5075
5076);
5077
5078
5079     -> 
5080
5081
5082alter table AP_V_SERVICES
5083
5084
5085    owner to intercon_user;
5086
5087
5088     -> create table if not exists AP_V_PERSONAL_ACCOUNTS
5089
5090
5091(
5092
5093
5094    N_SUBJECT_ID                 numeric,
5095
5096
5097    VC_SUBJ_CODE                 varchar(255),
5098
5099
5100    VC_SUBJ_NAME                 varchar(255),
5101
5102
5103    N_ACCOUNT_ID                 numeric,
5104
5105
5106    N_CURRENCY_ID                numeric,
5107
5108
5109    VC_CURRENCY                  varchar(255),
5110
5111
5112    VC_CURRENCY_CODE             varchar(255),
5113
5114
5115    VC_CODE                      varchar(255),
5116
5117
5118    VC_NAME                      varchar(255),
5119
5120
5121    VC_ACCOUNT                   varchar(255),
5122
5123
5124    N_OVERDRAFT                  numeric,
5125
5126
5127    D_OVERDRAFT_END              DATE,
5128
5129
5130    N_PERMANENT_OVERDRAFT        numeric,
5131
5132
5133    N_TEMPORAL_OVERDRAFT         numeric,
5134
5135
5136    D_TEMP_OVERDRAFT_END         DATE,
5137
5138
5139    N_SCHED_SERV_OVERDRAFT       numeric,
5140
5141
5142    D_SCHED_SERV_OVERDRAFT_END   DATE,
5143
5144
5145    N_UNSCHED_SERV_OVERDRAFT     numeric,
5146
5147
5148    D_UNSCHED_SERV_OVERDRAFT_END DATE,
5149
5150
5151    N_SUM_BAL                    numeric,
5152
5153
5154    N_SUM_RESERVED               numeric,
5155
5156
5157    N_SUM_RESERVED_CUR           numeric,
5158
5159
5160    N_SUM_FREE                   numeric,
5161
5162
5163    D_ACCOUNTING_BEGIN           DATE,
5164
5165
5166    D_ACCOUNTING_END             DATE,
5167
5168
5169    N_LAST_PAYMENT_SUM           numeric,
5170
5171
5172    D_LAST_PAYMENT               DATE,
5173
5174
5175    VC_LAST_PAYMENT_TYPE         varchar(255),
5176
5177
5178    N_LAST_PAYMENT_BANK_ID       numeric,
5179
5180
5181    VC_LAST_PAYMENT_BANK         varchar(255),
5182
5183
5184    N_RECOMMENDED_PAY            numeric,
5185
5186
5187    C_FL_PROMISED_PAYMENT        varchar(255),
5188
5189
5190    N_CURRENT_PROMISED_PAY_SUM   numeric,
5191
5192
5193    D_CURRENT_PROMISED_PAY_DAY   DATE
5194
5195
5196);
5197
5198
5199     -> 
5200
5201
5202alter table AP_V_PERSONAL_ACCOUNTS
5203
5204
5205    owner to intercon_user;
5206
5207
5208     -> create table if not exists AP_V_USER_OFFICE_GOODS
5209
5210
5211(
5212
5213
5214    N_SUBJ_GOOD_ID            numeric,
5215
5216
5217    N_LINE_NO                 numeric,
5218
5219
5220    N_PAR_SUBJ_GOOD_ID        numeric,
5221
5222
5223    N_GOOD_ID                 numeric,
5224
5225
5226    VC_GOOD                   VARCHAR(255),
5227
5228
5229    N_ACCOUNT_ID              numeric,
5230
5231
5232    VC_ACCOUNT                VARCHAR(255),
5233
5234
5235    N_OBJECT_ID               numeric,
5236
5237
5238    VC_OBJECT                 VARCHAR(255),
5239
5240
5241    N_PAY_DAY                 numeric,
5242
5243
5244    N_SUBSCR_PERMISSION_ID    numeric,
5245
5246
5247    N_SERV_TYPE_ID            numeric,
5248
5249
5250    N_UNSUBSCR_PERMISSION_ID  numeric,
5251
5252
5253    N_SERV_END_CHARGE_TYPE_ID numeric,
5254
5255
5256    N_DOC_ID                  numeric,
5257
5258
5259    N_DOC_TYPE_ID             numeric,
5260
5261
5262    VC_DOC_NAME               VARCHAR(255),
5263
5264
5265    VC_DOC_CODE               VARCHAR(255),
5266
5267
5268    D_BEGIN                   DATE,
5269
5270
5271    D_END                     DATE,
5272
5273
5274    N_INVOICE_ID              numeric,
5275
5276
5277    VC_INVOICE_CODE           VARCHAR(255),
5278
5279
5280    VC_INVOICE_NAME           VARCHAR(255)
5281
5282
5283);
5284
5285
5286     -> 
5287
5288
5289alter table AP_V_USER_OFFICE_GOODS
5290
5291
5292    owner to intercon_user;
5293
5294
5295     -> create table if not exists AP_V_PERSONS
5296
5297
5298(
5299
5300
5301    N_PERSON_ID    numeric      not null
5302
5303
5304        constraint profile_pkey
5305
5306
5307            primary key,
5308
5309
5310    VC_FIRST_NAME  VARCHAR(255) not null,
5311
5312
5313    VC_SURNAME     VARCHAR(255) not null,
5314
5315
5316    VC_SECOND_NAME VARCHAR(255) not null,
5317
5318
5319    VC_NAME        VARCHAR(255),
5320
5321
5322    N_SEX_ID       NUMERIC,
5323
5324
5325    D_BIRTH        DATE
5326
5327
5328);
5329
5330
5331     -> 
5332
5333
5334alter table AP_V_PERSONS
5335
5336
5337    owner to intercon_user;
5338
5339
5340     -> create table if not exists AP_V_USER_OFFICE_ACCESS_SERVS
5341
5342
5343(
5344
5345
5346    N_DOC_ID                numeric,
5347
5348
5349    N_USER_ID               numeric,
5350
5351
5352    N_GOOD_ID               NUMERIC,
5353
5354
5355    N_GOOD_TYPE_ID          numeric,
5356
5357
5358    VC_NAME                 VARCHAR(255),
5359
5360
5361    VC_CODE                 VARCHAR(255),
5362
5363
5364    N_PRICE                 numeric,
5365
5366
5367    N_CURRENCY_ID           numeric,
5368
5369
5370    VC_CURRENCY             VARCHAR(255),
5371
5372
5373    N_PAR_PRICE_LINE_ID     numeric,
5374
5375
5376    N_SUBSCR_PERMISSION_ID  numeric,
5377
5378
5379    VC_SUBSCR_PERMISSION_ID VARCHAR(255),
5380
5381
5382    N_PARENT_GOOD_ID        numeric,
5383
5384
5385    VC_PARENT_GOOD          VARCHAR(255),
5386
5387
5388    VC_USER_REM             VARCHAR(255),
5389
5390
5391    N_OBJECT_ID             numeric,
5392
5393
5394    C_FL_ACTIVE             VARCHAR(255)
5395
5396
5397);
5398
5399
5400     -> 
5401
5402
5403alter table AP_V_USER_OFFICE_ACCESS_SERVS
5404
5405
5406    owner to intercon_user;
5407
5408
5409     -> create table if not exists AP_V_USER_OFFICE_DEVICES
5410
5411
5412(
5413
5414
5415    N_DEVICE_ID       numeric,
5416
5417
5418    N_USE_DEVICE_ID   numeric,
5419
5420
5421    VC_ADDR_CODE      VARCHAR(255),
5422
5423
5424    VC_DEVICE_CODE    VARCHAR(255),
5425
5426
5427    N_CATALOG_ITEM_ID numeric,
5428
5429
5430    N_DEV_STATE_ID    numeric,
5431
5432
5433    VC_DEV_STATE      VARCHAR(255),
5434
5435
5436    VC_PORT_CODE      VARCHAR(255),
5437
5438
5439    VC_IP_CODE        VARCHAR(255),
5440
5441
5442    VC_SUBNET_MASK    VARCHAR(255),
5443
5444
5445    VC_SUBNET_CODE    VARCHAR(255),
5446
5447
5448    VC_MAC_CODE       VARCHAR(255)
5449
5450
5451);
5452
5453
5454     -> 
5455
5456
5457alter table AP_V_USER_OFFICE_DEVICES
5458
5459
5460    owner to intercon_user;
5461
5462
5463     -> CREATE TABLE public.sessions
5464
5465
5466(
5467
5468
5469    id           character varying(180) NOT NULL,
5470
5471
5472    user_id      numeric                NOT NULL,
5473
5474
5475    curr_session boolean
5476
5477
5478);
5479
5480
5481     -> 
5482
5483
5484ALTER TABLE public.sessions
5485
5486
5487    OWNER TO intercon_user;
5488
5489
5490     -> 
5491
5492
5493ALTER TABLE ONLY public.sessions
5494
5495
5496    ADD CONSTRAINT sessions_pk PRIMARY KEY (id);
5497
5498
5499     -> CREATE table if not exists SI_V_ENTITIES_TAGS
5500
5501
5502(
5503
5504
5505    N_ENTITY_TAG_ID  numeric,
5506
5507
5508    N_TAG_ID         numeric,
5509
5510
5511    N_ENTITY_ID      NUMERIC,
5512
5513
5514    N_ENTITY_TYPE_ID numeric,
5515
5516
5517    N_LINE_NO        numeric,
5518
5519
5520    VC_CODE          VARCHAR(255),
5521
5522
5523    C_FL_EDITABLE    CHAR
5524
5525
5526);
5527
5528
5529     -> 
5530
5531
5532alter table SI_V_ENTITIES_TAGS
5533
5534
5535    owner to intercon_user;
5536
5537
5538     -> 
5539
5540
5541create function func_login(login character varying, pass character varying, session_id character varying) returns bool
5542
5543
5544    language plpgsql
5545
5546
5547as
5548
5549
5550$$
5551
5552
5553DECLARE
5554
5555
5556    count_session_id int;
5557
5558
5559BEGIN
5560
5561
5562    count_session_id := (SELECT count(s) FROM sessions as s WHERE s.id LIKE session_id);
5563
5564
5565    IF func_get_user_id(login, pass) THEN
5566
5567
5568        IF count_session_id > 0 THEN
5569
5570
5571            return false;
5572
5573
5574        end if;
5575
5576
5577
5578
5579        INSERT into sessions(id, user_id, curr_session)
5580
5581
5582        values (session_id, (SELECT au.N_SUBJ_SERV_ID
5583
5584
5585                             FROM AP_V_SERVICES as au
5586
5587
5588                             WHERE au.VC_LOGIN_REAL = login
5589
5590
5591                               and au.password = pass
5592
5593
5594                             LIMIT 1), true);
5595
5596
5597        return true;
5598
5599
5600    ELSE
5601
5602
5603        return false;
5604
5605
5606    end if;
5607
5608
5609end ;
5610
5611
5612$$;
5613
5614
5615     -> 
5616
5617
5618alter function func_login(VARCHAR, VARCHAR, VARCHAR) owner to intercon_user;
5619
5620
5621     -> create function func_get_user_id(login character varying, pass character varying) returns boolean
5622
5623
5624    language plpgsql
5625
5626
5627as
5628
5629
5630$$
5631
5632
5633DECLARE
5634
5635
5636    id numeric;
5637
5638
5639BEGIN
5640
5641
5642    id := (
5643
5644
5645        SELECT au.N_SUBJ_SERV_ID
5646
5647
5648        FROM AP_V_SERVICES as au
5649
5650
5651        WHERE au.VC_LOGIN_REAL = login
5652
5653
5654          and au.password = pass
5655
5656
5657        LIMIT 1);
5658
5659
5660    IF id IS NOT NULL then
5661
5662
5663        return true;
5664
5665
5666    else
5667
5668
5669        return false;
5670
5671
5672    end if;
5673
5674
5675
5676
5677end ;
5678
5679
5680$$;
5681
5682
5683     -> alter function func_get_user_id(VARCHAR, VARCHAR) owner to intercon_user;
5684
5685
5686     -> CREATE OR REPLACE FUNCTION switch_context(session_id VARCHAR(180))
5687
5688
5689    returns void
5690
5691
5692AS
5693
5694
5695$$
5696
5697
5698BEGIN
5699
5700
5701    UPDATE sessions as ses SET curr_session = false Where ses.id <> session_id;
5702
5703
5704    UPDATE sessions as ses SET curr_session = true WHERE ses.id = session_id;
5705
5706
5707end;
5708
5709
5710$$
5711
5712
5713    LANGUAGE PLpgSQL;
5714
5715
5716     -> CREATE OR REPLACE FUNCTION logoff(session_id VARCHAR(180))
5717
5718
5719    returns void
5720
5721
5722AS
5723
5724
5725$$
5726
5727
5728BEGIN
5729
5730
5731    DELETE FROM sessions WHERE id = session_id;
5732
5733
5734end;
5735
5736
5737$$
5738
5739
5740    LANGUAGE PLpgSQL;
5741
5742
5743     -> create function suspend_service(good_id numeric, instant boolean) returns varchar
5744
5745
5746    language plpgsql
5747
5748
5749as
5750
5751
5752$$
5753
5754
5755DECLARE
5756
5757
5758    flag varchar;
5759
5760
5761BEGIN
5762
5763
5764    IF instant THEN
5765
5766
5767        flag = 'Y';
5768
5769
5770        UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
5771
5772
5773        return 1;
5774
5775
5776    ELSE
5777
5778
5779        flag = 'N';
5780
5781
5782        return 2;
5783
5784
5785    end if;
5786
5787
5788
5789
5790end;
5791
5792
5793$$;
5794
5795
5796     -> alter function suspend_service(numeric, boolean) owner to intercon_user;
5797
5798
5799     -> create function stop_temp_blocking(good_id numeric, date_stop date) returns character varying
5800
5801
5802    language plpgsql
5803
5804
5805as
5806
5807
5808$$
5809
5810
5811DECLARE
5812
5813
5814    flag varchar;
5815
5816
5817BEGIN
5818
5819
5820    flag = (Select c_fl_active
5821
5822
5823            from AP_V_USER_OFFICE_ACCESS_SERVS as uoas
5824
5825
5826            where N_GOOD_ID = good_id);
5827
5828
5829    if flag = 'N' then
5830
5831
5832        return 'null';
5833
5834
5835    end if;
5836
5837
5838    UPDATE AP_V_USER_OFFICE_ACCESS_SERVS set C_FL_ACTIVE = flag where n_good_id = good_id;
5839
5840
5841    return '1';
5842
5843
5844end;
5845
5846
5847$$;
5848
5849
5850     -> alter function stop_temp_blocking(numeric) owner to intercon_user;
5851
5852
5853Migration 20191009125712 failed during Execution. Error An exception occurred while executing 'alter function stop_temp_blocking(numeric) owner to intercon_user;':
5854
5855
5856
5857
5858SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(numeric) does not exist
5859
5860
5861
5862
5863In AbstractPostgreSQLDriver.php line 79:
5864
5865
5866                                                                               
5867
5868
5869  An exception occurred while executing 'alter function stop_temp_blocking(nu  
5870
5871
5872  meric) owner to intercon_user;':                                             
5873
5874
5875                                                                               
5876
5877
5878  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
5879
5880
5881  numeric) does not exist                                                      
5882
5883
5884                                                                               
5885
5886
5887
5888
5889In PDOConnection.php line 80:
5890
5891
5892                                                                               
5893
5894
5895  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
5896
5897
5898  numeric) does not exist                                                      
5899
5900
5901                                                                               
5902
5903
5904
5905
5906In PDOConnection.php line 75:
5907
5908
5909                                                                               
5910
5911
5912  SQLSTATE[42883]: Undefined function: 7 ERROR:  function stop_temp_blocking(  
5913
5914
5915  numeric) does not exist                                                      
5916
5917
5918                                                                               
5919
5920
5921
5922
5923doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]