File: postgresql.tcl

package info (click to toggle)
ferret 0.7-2
  • links: PTS
  • area: main
  • in suites: bullseye, buster, sid
  • size: 2,820 kB
  • ctags: 1,494
  • sloc: tcl: 22,364; makefile: 112
file content (266 lines) | stat: -rw-r--r-- 5,481 bytes parent folder | download
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
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
;# postgresql.tcl -- Gerwin
;#
;# PostgreSQL SQL generation routine
;#
;# Copyright (C) Jose E. Marchesi
;#
;# Time-stamp: "08/11/16 19:22:06 jemarch"

;# This 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 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/>.

;# Register the output format
lappend gerwin_output_formats postgresql

;# Set the color scheme for this output format
set output_colors(postgresql) {

    {CREATE blue}
    {TABLE blue}
    {ALTER blue}
    {ADD blue}
    {KEY blue}
    {FOREIGN blue}
    {REFERENCES blue}
    {PRIMARY blue}
    {{NOT NULL} darkgreen}
    {{\(} blue}
    {{\)} blue}
    {{,} orange}
    {{;} orange}
    {{--.*$} red}
    {{\".*?\"} orange}
}

;# Set the type scheme for this output format

set gerwin_output_format_types(postgresql) {

    {{DYNAMIC_STRING(%d)}  {VARCHAR(%d)}}
    {{FIXED_STRING(%d)} {CHAR(%d)}}
    {INTEGER INTEGER}
    {FLOAT FLOAT}
    {NATURAL INTEGER}
    {CHARACTER {CHAR(1)}}
}


;# The generation routine

proc gen_TD_to_format_postgresql {} {

    global gerwin_cproject_name
    global gerwin_cproject_author
    global gerwin_cproject_file
    global gerwin_output_active_format
    global gerwin_tables
    global gerwin_table

    ;# Generate some comments
    omit_line "-- Automatically generated by Gerwin"
    omit_line "--"
    omit_line "-- Project $gerwin_cproject_name"
    omit_line "-- From file: $gerwin_cproject_file"
    omit_line "-- By: $gerwin_cproject_author"
    omit_line "--"
    omit_line "-- Using SQL dialect: $gerwin_output_active_format"

    ;# Ok, generate the SQL

    ;# Iterate on the tables
    foreach t $gerwin_tables {

	set table_name $t

	;# Some blank space before each table
	omit_line ""
	omit_line ""
	omit_line ""

	omit_line "CREATE TABLE $t ("
	
	;# Iterate on the table attributes
	set nattributes [gob_table_get_nattributes $t]
	set i 0
	foreach a [gob_table_get_attributes $t] {
	    incr i

	    ;# See if the name contain any #
	    ;# If it is the case, then this is a repeated foreign key
	    ;# and the name in gob_table_get_foreign_keys is without the #!

	    if {[regexp \% $a]} then {

		;# Convert the # to an underscore
		set nname [string map {% _} $a]

	    } else {
		set nname $a
	    }

	    ;# Write the attribute name and the attribute domain
	    append line  "      $nname     [gob_table_get_attribute_domain $t $a]"

	    ;# Write NULL or NOT NULL
	    if {! [gob_table_get_attribute_null $t $a]} then {

		;# The attribute  is not anullable
		append line " NOT NULL"
	    }


	    if {$i != $nattributes} then {
		;# End of the column line
		append line ","
	    }
	    
	    ;# Ok, omit the line
	    omit_line $line
	    set line {}
	}

	;# End of the table
	omit_line ");"

	omit_line ""
	omit_line ""
	
	;# Emit the primary key for $t
	set pkey "ALTER TABLE $t ADD PRIMARY KEY ("
	set nattributes [llength [gob_table_get_key $t]]
	set i 0
	foreach a [gob_table_get_key $t] {

	    incr i

	    ;# See if the name contain any %
	    ;# If it is the case, then this is a repeated foreign key
	    ;# and the name in gob_table_get_foreign_keys is without the #!

	    if {[regexp \% $a]} then {

		;# Convert the % to an underscore
		set nname [string map {% _} $a]

	    } else {
		set nname $a
	    }


	    if {$i == $nattributes} then {
		append pkey "${nname}\);"
	    } else {
		append pkey "${nname},"
	    }
	}


	omit_line $pkey

    }


    ;# Generate foreign keys on the tables
    foreach t $gerwin_tables {

	omit_line ""
	omit_line ""


	;# Emit foreign keys for $t
	set rtables {}
	foreach fkd [gob_table_get_foreign_keys $t] {

	    set rtable [lindex $fkd 1]

	    if {[lsearch $rtables $rtable] == -1} then {
		lappend rtables $rtable
	    }
	}
	;# Ok, now we have all the rtables that $t references into $rtables

	foreach rtable $rtables {


	    set lattrs {}
	    set rattrs {}

	    set fkline "ALTER TABLE $t ADD FOREIGN KEY"


	    ;# Emit the foreign keys for this rtable
	    foreach a [gob_table_get_attributes $t] {




		;# Get the remote name for the attribute
		regsub {.+%([^%]+)} $a "\\1" nname
		set nname [string map "% _" $nname]


		if {[gob_table_is_foreign_key $t $a]} then {



		    ;# Get the foreign key for the attribute
		    set art [gob_table_get_foreign_key_rtable $t $a]

		    if {$art == $rtable} then {

			set a [string map "% _" $a]
			;# Begin foreign_key_attribute
			lappend lattrs $a
			lappend rattrs $nname
		    }
		}
	    }

	    append fkline " \("
	    set i 0
	    foreach la $lattrs {
		incr i
		if {$i == [llength $lattrs]} then {
		    append fkline $la
		} else {
		    append fkline "$la,"
		}

	    }

	    append fkline "\) REFERENCES $rtable \("

	    set i 0
	    foreach ra $rattrs {
		incr i
		if {$i == [llength $rattrs]} then {
		    append fkline $ra
		} else {
		    append fkline "$ra,"
		}
		
	    }

	    append fkline "\);"

	    ;# Ok, emit the line
	    omit_line $fkline
	    omit_line ""
	}

    }


}