-
Notifications
You must be signed in to change notification settings - Fork 5
/
postgeol_structure_03_1_views_opid_create
executable file
·230 lines (206 loc) · 8.79 KB
/
postgeol_structure_03_1_views_opid_create
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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
#!/usr/bin/rebol -qs
rebol [
Title: "Création des vues de bdexplo"
Name: gll_bdexplo_views_create.r
Version: 1.0.0
Date: "5-Aug-2013/17:53:46"
Author: "Pierre Chevalier"
License: {
This file is part of GeolLLibre software suite: FLOSS dedicated to Earth Sciences.
###########################################################################
## ____ ___/_ ____ __ __ __ _()____ ____ _____ ##
## / ___\/ ___// _ |/ / / / / / / _/ _ \ / __ \/ ___/ ##
## / /___/ /_ / / | / / / / / / / // /_/_/ /_/ / /_ ##
## / /_/ / /___| \/ / /__/ /__/ /___/ // /_/ / _, _/ /___ ##
## \____/_____/ \___/_____/___/_____/__/_____/_/ |_/_____/ ##
## ##
###########################################################################
Copyright (C) 2019 Pierre Chevalier <pierrechevaliergeol@free.fr>
GeolLLibre 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 program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>
or write to the Free Software Foundation, Inc., 51 Franklin Street,
Fifth Floor, Boston, MA 02110-1301, USA.
See LICENSE file.
}
]
; _________
; < bdexplo > <= the ancester of postgeol, ca. 1992 to 2018
; ---------
; \ ^__^
; \ (xx)\_______
; (__)\ )\/\
; U ||----w |
; || ||
;
; __________
; < postgeol > <= the descendant of bdexplo, since 2016
; ----------
; \ __
; \ /( )\_---------_
; { (°°) } )\
; { /()\ } | \
; / () \ |__ ____| |
; / !! |\| | |
; ! /__| /_|_\
; (gauled from man psql; double ;; when implemented here)
;SYNOPSIS
; postgeol_structure_03_1_views_opid_create [option...] [dbname [username]]
;
;OPTIONS
; -d dbname
; --dbname=dbname
; Specifies the name of the database to connect to. This is
; equivalent to specifying dbname as the first non-option argument on
; the command line.
;
; If this parameter contains an = sign or starts with a valid URI
; prefix (postgresql:// or postgres://), it is treated as a conninfo
; string. See Section 33.1.1 for more information.
;
; -h hostname
; --host=hostname
; Specifies the host name of the machine on which the server is
; running. If the value begins with a slash, it is used as the
; directory for the Unix-domain socket.
;
; -p port
; --port=port
; Specifies the TCP port or the local Unix-domain socket file
; extension on which the server is listening for connections.
; Defaults to the value of the PGPORT environment variable or, if not
; set, to the port specified at compile time, usually 5432.
;
; -U username
; --username=username
; Connect to the database as the user username instead of the
; default. (You must have permission to do so, of course.)
;do load to-file system/options/home/bin/gll_routines.r ; Récupération des routines et préférences et connexion à la base
options: system/options/args
msg_err: does [print "* Error *"]
print probe options ; DEBUG ##########
unless (none? options) [ ; => No command line options: get default values, they are already defined in .gll_preferences and/or in environment variables
; print length? options ; DEBUG ##########
switch/default (length? options) [
1 [ ; Just one word as argument: let's suppose that it is the database name
dbname: first options
]
2 [ ; two words:
; does the first one start with - ?
either (first (to-string (first options)) = "-") [
case [
(second (to-string (first options)) = "d") [ ; then the following argument is the database to connect to:
dbname: second options ]
(second (to-string (first options)) = "h") [ ; then the following argument is the host to connect to:
dbhost: second options ]
(second (to-string (first options)) = "p") [ ; then the following argument is the port to connect to:
dbport: second options ]
(second (to-string (first options)) = "U") [ ; then the following argument is the username to connect as:
user: second options ]
]
] [ ; the first word doesn't start with - : therefore, it is the database to connect to:
dbname: first options
; now what is the second word? It must be the username:
user: second options
]
]
3 [ ; three words:
; NOPE. TODO later. Find a better way to handle that, in any decent codebase...
msg_err ; pour le moment...
; ; => nothing to do
]
]
[ ;=> autre cas: erreur
msg_err
]
]
?? dbname
?? dbhost
?? dbport
?? user
connection_db
insert db "BEGIN TRANSACTION;"
;# on commence à fabriquer un gros texte en sql, qu'on fera tourner à la fin: {{{
sql_string: {
BEGIN TRANSACTION;
} ; fait doublon, mais c'est pour le moment du débogage où l'on ne fait qu'afficher le SQL résultant
;}}}
; on y met d'abord les vues homonymes des tables dans le premier schéma: {{{
;# -- 1. les vues homonymes des tables, dans le premier schéma dans l'ordre de recherche, en restreignant à l'opération active: {{{
;# La liste des tables pour lesquelles on veut faire une vue homonyme dans le schéma utilisateur:
;comment [ {{{ } } }
;tables_for_view_join_operationid = [
;'ancient_workings' ,
;'dh_collars' ,
;'dh_collars_program' ,
;'dh_density' ,
;'dh_devia' ,
;'dh_litho' ,
;'dh_mineralised_intervals',
;#'dh_sampling' ,
;'dh_sampling_grades' ,
;'dh_struct' ,
;'dh_tech' ,
;'field_observations' ,
;'field_photos' ,
;'geoch_ana' ,
;'geoch_sampling' ,
;'geoch_sampling_grades' ,
;'grade_ctrl' ,
;'lab_ana_batches' ,
;'lab_ana_results' ,
;'lab_analysis_icp' ,
;'occurrences' ,
;'qc_sampling' ,
;'qc_standards' ,
;'rock_ana' ,
;'rock_sampling' ,
;'shift_reports' ,
;'topo_points'
;]
;] ; }}}
tables_for_view_join_operationid: []
tables_public: run_query "SELECT tablename FROM pg_tables WHERE schemaname = 'public';"
; on enlève les tables qui n'ont pas à être VIEWées:
foreach t tables_public [ append tables_for_view_join_operationid to-string t ]
tables_indesirables: ["dh_nb_samples" "dh_collars_lengths" "spatial_ref_sys" "geometry_columns" "operations" "operation_active" "units" "conversions_oxydes_elements" "doc_bdexplo_table_categories" "doc_bdexplo_tables_descriptions" ]
tables_for_view_join_operationid: (exclude tables_for_view_join_operationid tables_indesirables)
; pour voir les tables concernées rapidos:
;foreach t tables_for_view_join_operationid [ print rejoin ["TABLE public." t ";"] ]
;#}}}
;# on fabrique le gros texte en sql:{{{
foreach tablename tables_for_view_join_operationid [
append sql_string rejoin ["CREATE VIEW " tablename " AS SELECT " tablename ".* FROM " tablename " JOIN operation_active ON " tablename ".opid = operation_active.opid;" newline]
]
;#}}}
;# --}}}
;# À partir de l'instruction sql ainsi commencée, on l'agrège maintenant, avec un gros tas de requêtes déjà faites:
; 2018_08_26__20_14_24 => no, rather store these views definition into a separate plain SQL file, which is played prior to this program.
;# Voilà, le sql est fabriqué.
;# ...que l'on runne ensuite:
;# (cancellé, pour le moment, par prudence: on montre juste le SQL)
;print to-string sql_string
; insert db to-string sql_string
; => marche pas, cf. Doc
close db
ligne_cmd: rejoin [{echo "} sql_string {" | psql -X -h } dbhost { -p } dbport { -U } user { -d } dbname]
call_wait_output_error ligne_cmd
;
;either ( confirm "Run the generated script?" ) [
; call/wait ligne_cmd ]
; [
; ; Prudemment, on ne runne pas, on affiche juste:
; ; print ligne_cmd
; ; Mieux, on génère un script à runner:
; script: %tt_gll_bdexplo_views_create.sh
; write script ligne_cmd
; print rejoin [newline "PostGeol views creation script generated: " to-string script newline "to run it:" newline "sh " to-string script newline]
; ]
;;###########################################################