forked from Giswater/giswater_dbmodel
-
Notifications
You must be signed in to change notification settings - Fork 0
/
readme.txt
138 lines (117 loc) · 4.83 KB
/
readme.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
/*
This file is part of Giswater 3
The program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This version of Giswater is provided by Giswater Association
*/
This file explains the sql's structure of this folder
-- Folders structure
--------------------------------
api
corporate
dev
example
i18n
tools
ud
updates
utils
ws
subfolder structure of ud/ws/utils folder
- ddl
- ddlrule
- ddlview
- dml
- fct
- ftrg
- tablect
- trg
--- Instructions to update sql's
--------------------------------
1) FUNCTION AND TRIGGER FUNCTION:
It's mandatory to modify the original definition. They must be unique and must be located on (fct) and (ftrg) folders.
In case of different code for different versions 'IF' on code will be used
Use always one file for each function/trigger
2) VIEWS:
It's forbidden to modify the original definition. Keep the original defition but put a comment there like 'definiton updated on 3.x.xxxx'
Use ddlview.sql file located on ws/ud/utils update folders.
DROP IS NOT FORBIDDEN but use it only if it's needed.
DROP CASCADE IS FORBIDDEN. If it's needed, take time and wait for next major release.
Identify any change on any view on the chapter views of changelog.txt file
3) TABLES, RULES, TRIGGERS, CONSTRAINTS:
It's forbidden to modify the original definition. Keep the original defition but put a comment there like 'definiton updated on 3.x.xxxx'
Use below files located on ws/ud/utils update folders
ddl.sql
dml.sql
ddlrule.sql
tablect.sql
trg.sql
DROP IS FORBIDDEN for all. If same function / trigger / table / view or sequence becomes deprecated we must use:
UPDATE audit_cat_table / audit_cat_function / audit_cat_sequence SET isdeprectaded=TRUE
4) DML
It's forbidden to modify the original definition. Keep the original defition but put a comment there like 'definiton updated on 3.x.xxxx'
Use below files located on ws/ud/utils update folders
ddl.sql
dml.sql
ddlrule.sql
tablect.sql
trg.sql
5) I18N
It's forbidden to modify the original definition. Keep the original defition but put a comment there 'definiton updated on 3.x.xxxx'
It's specific case of dml
Use below files located on EN/ES/CA/PT folders
ud.sql
ws.sql
utils.sql
6) API:
Same as ud/ws projects but without ws/ud folders
No ws/ud folders means that no specific ws/ud sql file will be located on API folder
In case of specific ws/ud API table/view/function use update files of ws/ud to work with
7) OTHER PROJECT TYPES
If you are looking to use Giswater sql project creation and update structure of specific project
- Define list of other project types on config.file
- Create folder with same name defined on config.file and use this subfolder structure:
example
i18n
updates
ddl
ddlrule
ddlview
dml
fct
ftrg
tablect
trg
- Same behaviour of WS/UD will be done with the unique difference of the location of folders
8) MANDATORY FOLDERS
- Some times reader of SQL crash. To prevent some bugs is mandatory to define al folders in spite of no information needs. Put sql file with 'SET SEARCH_PATH' as unique row
-- Changelog file
-----------------------------
- Use it to register any change on sqls.
- Issue is mandatory. If not exits, create new one on Github.
- If we need to drop whitout cascade some view, changelog has two parts in order to identify as best as possible that special AND UNIQUE CASE OF DROPS
-- Rules to prevent conflits on update files
--------------------------------------------
- For ct and trg use DROP...IF EXISTS......CASCADE
- For tables and sequences use CREATE TABLE/SEQUENCE IF NOT EXISTS ....
- For views use CREATE OR REPLACE VIEW
- For insert on system tables use ON CONFLICT (pk_field) DO NOTHING
- For new fields, USE THE FUNCTION gw_fct_admin_manage_fields (see below an example of new field)
SELECT gw_fct_admin_manage_fields($${"data":{"action":"ADD","table":"config_web_fields", "column":"table_type", "dataType":"text"}}$$)
-- Workflows
----------------------------
1) CREATE EMPTY PROJECT
2) CREATE EMPTY PROJECT WITHOUT CONSTRAINTS AND TRIGGERS
3) CREATE PROJECT WITH SAMPLE DATA
4) CREATE PROJECT WITH SAMPLE FOR DEV
5) CREATE PROJECT USING INP FILE
6) PROJECT UPDATE
7) CREATE API
8) API UPDATE
9) LAST PROCESS FUNCTION
- Grant permissions to all relations using audit_cat_* tables
- Enable foreing keys with utils schema if exists
- Drop deprecated table/views/functions/sequences ONLY for new projects;
-- List of ui views desacoplated from code (fields to filter must exists) but users can adapt by ownself this views
-------------------------------------------------------------------------------------------------------------------
v_ui_hydrometer
v_ui_anl_mincut_result_cat