3 package require cmdline
9 set fileEncoding "utf-8"
12 set progname [::cmdline::getArgv0]
15 {U.arg "" "User name"}
16 {p.arg "" "password" }
17 {h.arg "" "host name" }
18 {e.arg "utf-8" "Output file encoding" }
24 set usage ": [::cmdline::getArgv0] \[options\] database trackerID"
26 set isError [catch {array set cmdArgs [::cmdline::getoptions argv $options $usage]} msg]
33 if { [llength $argv] <= 0 } {
34 puts stderr "$progname: Missing database name."
38 if { [llength $argv] <= 1 } {
39 puts stderr "$progname: Missing tracker id."
43 if { $cmdArgs(U) == "" } {
44 puts stderr "$progname: Missing username."
48 set dbHost $cmdArgs(h)
50 if { $cmdArgs(h) == "" } {
51 set dbHost "localhost"
54 if { $cmdArgs(e) != "" } {
55 set fileEncoding $cmdArgs(e)
59 set dbUser $cmdArgs(U)
60 set dbPassword $cmdArgs(p)
64 set trackerId [lindex $argv 1]
66 set dbName [lindex $argv 0]
70 set connStr "dbname=$dbName host=$dbHost port=5432 user=$dbUser password=$dbPassword"
73 set dbConn [pg_connect -conninfo $connStr]
77 puts "$progname: connection to database failed."
78 puts stderr "--- Details\n$msg\n---"
82 array set typeNameCache {}
84 proc getTypeName { dbConn typeoid } {
86 set query "select typname from pg_type where oid = $typeoid";
87 set result [pg_exec $dbConn $query]
88 set row [pg_result $result -getTuple 0]
89 set toReturn [lindex $row 0]
90 pg_result $result -clear
94 proc getSQLValueFormat { dbConn typeoid value } {
98 set allTypes [array get typeNameCache]
100 if { [lsearch $allTypes $typeoid] < 0 } {
101 set typeName [getTypeName $dbConn $typeoid]
102 array set typeNameCache [list $typeoid $typeName]
104 set typeName $typeNameCache($typeoid)
107 switch -glob $typeName {
110 # set tmp [string map {\' \' é e è e à a ç c ê e ô o â a} $value]
111 set tmp [string map {\' \'} $value]
112 set tmp [string map {' "\\'"} $tmp]
113 # set tmp [string map {\\\\ \\ § paragraphe } $tmp]
114 set tmp [string map {\\\\ \\} $tmp]
115 set toReturn "'$tmp'"
126 proc getColumnTypeInfo { types colName } {
128 foreach type $types {
129 if { [lindex $type 0] == $colName } {
137 proc generateInsert { sqlFile dbConn table query} {
138 set result [pg_exec $dbConn $query]
139 set colNames [pg_result $result -attributes]
140 set colTypes [pg_result $result -lAttributes]
141 pg_result $result -assign tuples
142 set nbTuples [pg_result $result -numTuples]
144 for {set idx 0} { $idx < $nbTuples} { incr idx} {
147 foreach colName $colNames {
148 set colTypeInfo [getColumnTypeInfo $colTypes $colName]
149 lappend fields $colName
150 lappend values [getSQLValueFormat $dbConn [lindex $colTypeInfo 1] $tuples($idx,$colName)]
153 puts $sqlFile "insert into $table \([join $fields ,]\) values \([join $values ,]\);\n"
156 pg_result $result -clear
159 proc getColumnAsList {dbConn query colName} {
161 set result [pg_exec $dbConn $query]
163 set nbRows [pg_result $result -numTuples]
164 for {set idx 0} { $idx < $nbRows } { incr idx } {
165 pg_result $result -tupleArray $idx row
166 lappend toReturn $row($colName)
169 pg_result $result -clear
173 set sqlFile [open "${trackerId}-export.sql" "w"]
174 set verifyFile [open "${trackerId}-validate.sql" "w"]
175 set deleteFile [open "${trackerId}-delete.sql" "w"]
177 fconfigure $sqlFile -encoding $fileEncoding
178 fconfigure $verifyFile -encoding $fileEncoding
179 fconfigure $deleteFile -encoding $fileEncoding
183 puts "Generating ${trackerId}-export.sql ..."
186 # Artifact list of this tracker
187 set query "select artifact_id from artifact where group_artifact_id = $trackerId order by artifact_id;"
188 set artifactIdList [getColumnAsList $dbConn $query artifact_id]
190 # Extra field list of this tracker
191 set query "select extra_field_id from artifact_extra_field_list where group_artifact_id = $trackerId order by extra_field_id;"
192 set extraFieldIdList [getColumnAsList $dbConn $query extra_field_id]
196 # Generating artifacts
199 set query "select * from artifact_group_list where group_artifact_id = $trackerId;"
200 generateInsert $sqlFile $dbConn artifact_group_list $query
202 set query "select * from artifact where group_artifact_id = $trackerId;"
203 generateInsert $sqlFile $dbConn artifact $query
205 set query "select * from artifact_history where artifact_id in ([join $artifactIdList ,]);"
206 generateInsert $sqlFile $dbConn artifact_history $query
209 # Generating extra fields
212 set query "select * from artifact_extra_field_list where extra_field_id in ([join $extraFieldIdList ,]);"
213 generateInsert $sqlFile $dbConn artifact_extra_field_list $query
215 set query "select * from artifact_extra_field_elements where extra_field_id in ([join $extraFieldIdList ,]);"
216 generateInsert $sqlFile $dbConn artifact_extra_field_elements $query
218 set query "select * from artifact_extra_field_data where artifact_id in ([join $artifactIdList ,]);"
219 generateInsert $sqlFile $dbConn artifact_extra_field_data $query
221 set query "select * from artifact_monitor where artifact_id in ([join $artifactIdList ,]);"
222 generateInsert $sqlFile $dbConn artifact_monitor $query
224 set query "select * from artifact_perm where group_artifact_id = $trackerId;"
225 generateInsert $sqlFile $dbConn artifact_perm $query
227 set query "select * from artifact_file where artifact_id in ([join $artifactIdList ,]);"
228 generateInsert $sqlFile $dbConn artifact_file $query
231 puts "Generating ${trackerId}-validate.sql ..."
232 puts $verifyFile "-- Validation"
234 set query "select id from artifact_history where artifact_id in ([join $artifactIdList ,]);"
235 set artifactHistoryIdList [getColumnAsList $dbConn $query id]
237 puts $verifyFile "select * from artifact_history where id in ([join $artifactHistoryIdList ,]);"
239 set query "select count(*) as extra_field_id_count from artifact_extra_field_list where group_artifact_id = $trackerId;"
240 puts $verifyFile $query
242 set query "select count(*) as extrafield_elt_count from artifact_extra_field_elements where extra_field_id in ([join $extraFieldIdList ,]);"
243 puts $verifyFile $query
246 set query "select count(*) as extrafield_data_count from artifact_extra_field_data where artifact_id in ([join $artifactIdList ,]);"
247 puts $verifyFile $query
249 set query "select count(*) as artifact_monitor_count from artifact_monitor where artifact_id in ([join $artifactIdList ,]);"
250 puts $verifyFile $query
252 set query "select count(*) as artifact_perm_count from artifact_perm where group_artifact_id = $trackerId;"
253 puts $verifyFile $query
255 set query "select count(*) as artifact_file_count from artifact_file where artifact_id in ([join $artifactIdList ,]);"
256 puts $verifyFile $query
259 puts "Generating ${trackerId}-delete.sql ..."
261 set query "delete from artifact_group_list where group_artifact_id = $trackerId;"
262 puts $deleteFile $query
264 puts $deleteFile "delete from artifact_history where id in ([join $artifactHistoryIdList ,]);"
266 puts $deleteFile "delete from artifact where group_artifact_id = $trackerId;"
268 set query "delete from artifact_extra_field_list where group_artifact_id = $trackerId;"
269 puts $deleteFile $query
271 set query "delete from artifact_extra_field_elements where extra_field_id in ([join $extraFieldIdList ,]);"
272 puts $deleteFile $query
275 set query "delete from artifact_extra_field_data where artifact_id in ([join $artifactIdList ,]);"
276 puts $deleteFile $query
278 set query "delete from artifact_monitor where artifact_id in ([join $artifactIdList ,]);"
279 puts $deleteFile $query
281 set query "delete from artifact_perm where group_artifact_id = $trackerId;"
282 puts $deleteFile $query
284 set query "delete from artifact_file where artifact_id in ([join $artifactIdList ,]);"
285 puts $deleteFile $query