process/one-off/pgsql-gen.p

   1 &ANALYZE-SUSPEND _VERSION-NUMBER UIB_v8r12
   2 &ANALYZE-RESUME
   3 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure
   4 /*--------------------------------------------------------------------------
   5     File        : pgsql-gen.p
   6     Purpose     : Dump database suitable for loading into PostgreSQL
   7
   8     Syntax      : Just run it :-)
   9
  10     Description :
  11
  12     Author(s)   : Andrew McMillan
  13     Created     :
  14     Notes       : (c) 1999 Andrew McMillan. Licensed under the GNU General
  15                   Public License version 2.
  16   ------------------------------------------------------------------------*/
  17
  18 DEF VAR dump-dir AS CHAR NO-UNDO INITIAL "D:/dump/sql".
  19 DEF VAR file-name AS CHAR NO-UNDO.
  20 DEF VAR bad-table AS LOGI NO-UNDO.
  21
  22 DEF VAR keywords AS CHAR NO-UNDO INITIAL "order".
  23
  24 DEFINE TEMP-TABLE MyIndex NO-UNDO
  25     FIELD Index-name AS CHAR
  26     FIELD seq AS INT
  27     FIELD actual-name AS CHAR
  28     INDEX xpk-index-name IS UNIQUE index-name seq.
  29
  30 /* _UIB-CODE-BLOCK-END */
  31 &ANALYZE-RESUME
  32
  33
  34 &ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK
  35
  36 /* ********************  Preprocessor Definitions  ******************** */
  37
  38 &Scoped-define PROCEDURE-TYPE Procedure
  39
  40
  41
  42 /* _UIB-PREPROCESSOR-BLOCK-END */
  43 &ANALYZE-RESUME
  44
  45
  46 /* ************************  Function Prototypes ********************** */
  47
  48 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD create-myindex Procedure
  49 FUNCTION create-myindex RETURNS CHARACTER
  50   ( INPUT new-index AS CHAR )  FORWARD.
  51
  52 /* _UIB-CODE-BLOCK-END */
  53 &ANALYZE-RESUME
  54
  55 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD munged Procedure
  56 FUNCTION munged RETURNS CHARACTER
  57   ( INPUT from-name AS CHAR, INPUT bad_suffix AS CHAR)  FORWARD.
  58
  59 /* _UIB-CODE-BLOCK-END */
  60 &ANALYZE-RESUME
  61
  62
  63 /* *********************** Procedure Settings ************************ */
  64
  65 &ANALYZE-SUSPEND _PROCEDURE-SETTINGS
  66 /* Settings for THIS-PROCEDURE
  67    Type: Procedure
  68    Allow:
  69    Frames: 0
  70    Add Fields to: Neither
  71    Other Settings: CODE-ONLY COMPILE
  72  */
  73 &ANALYZE-RESUME _END-PROCEDURE-SETTINGS
  74
  75 /* *************************  Create Window  ************************** */
  76
  77 &ANALYZE-SUSPEND _CREATE-WINDOW
  78 /* DESIGN Window definition (used by the UIB)
  79   CREATE WINDOW Procedure ASSIGN
  80          HEIGHT             = .35
  81          WIDTH              = 37.
  82 /* END WINDOW DEFINITION */
  83                                                                         */
  84 &ANALYZE-RESUME
  85
  86
  87
  88
  89 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure
  90
  91
  92 /* ***************************  Main Block  *************************** */
  93
  94 FOR EACH _File /* WHERE _File._File-name BEGINS "Var" */:
  95   IF _File._File-name BEGINS "Repl" THEN NEXT.
  96   IF _File._File-name BEGINS "_" THEN NEXT.
  97
  98   bad-table = No.
  99   file-name = dump-dir + "/" + _File._File-Name.
 100   OUTPUT TO VALUE( file-name + ".sql" ) PAGE-SIZE 0.
 101   RUN dump-tabledef( _File._File-Name ).
 102   OUTPUT CLOSE.
 103
 104   RUN dump-tabledat( _File._File-Name ).
 105
 106   OUTPUT TO VALUE( file-name + ".sql" ) PAGE-SIZE 0 APPEND.
 107   RUN dump-indexdef( _File._File-Name ).
 108   OUTPUT CLOSE.
 109
 110   IF bad-table THEN
 111     OS-RENAME VALUE( file-name + ".sql" ) VALUE( file-name + ".sqx" ).
 112 END.
 113
 114 /* _UIB-CODE-BLOCK-END */
 115 &ANALYZE-RESUME
 116
 117
 118 /* **********************  Internal Procedures  *********************** */
 119
 120 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE dump-fielddef Procedure
 121 PROCEDURE dump-fielddef :
 122 /*------------------------------------------------------------------------------
 123   Purpose:
 124 ------------------------------------------------------------------------------*/
 125 DEF INPUT PARAMETER file-name AS CHAR NO-UNDO.
 126 DEF INPUT PARAMETER field-name AS CHAR NO-UNDO.
 127
 128   PUT UNFORMATTED "  " + munged(field-name, "fld") + " ".
 129   CASE SUBSTRING( _Field._Data-Type, 1, 3):
 130     WHEN "CHA" THEN PUT "TEXT".
 131     WHEN "DEC" THEN PUT "FLOAT8".
 132     WHEN "LOG" THEN PUT "BOOL".
 133     WHEN "INT" THEN PUT "INT4".
 134     WHEN "DAT" THEN PUT "DATETIME".
 135     WHEN "RAW" THEN PUT "xxxRAW".
 136     WHEN "ROW" THEN PUT "xxxROWID".
 137     OTHERWISE PUT "xxx" + _Field._Data-Type .
 138   END CASE.
 139
 140   IF _Field._Extent > 1 THEN PUT "[]".
 141   IF _Field._Mandatory THEN PUT " NOT NULL".
 142
 143 END PROCEDURE.
 144
 145 /* _UIB-CODE-BLOCK-END */
 146 &ANALYZE-RESUME
 147
 148
 149 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE dump-indexdef Procedure
 150 PROCEDURE dump-indexdef :
 151 /*------------------------------------------------------------------------------
 152   Purpose:
 153 ------------------------------------------------------------------------------*/
 154 DEF INPUT PARAMETER fname AS CHAR NO-UNDO.
 155
 156 DEF VAR idx-name AS CHAR NO-UNDO.
 157 DEF VAR trailing AS CHAR NO-UNDO.
 158   FOR EACH _Index OF _File:
 159     PUT UNFORMATTED "CREATE ".
 160     IF _Index._Unique THEN PUT "UNIQUE ".
 161     idx-name = create-myindex( _Index._Index-Name ).
 162     PUT UNFORMATTED "INDEX " + idx-name.
 163     IF idx-name = fname THEN PUT UNFORMATTED "_key".
 164     PUT UNFORMATTED " ON " + munged(fname, "tbl") + " (".
 165     trailing = "".
 166     FOR EACH _Index-Field OF _Index, FIRST _Field OF _Index-Field:
 167       PUT UNFORMATTED trailing + " " + munged(_Field._Field-Name, "fld").
 168       IF _Field._Data-Type BEGINS "LOG" THEN PUT UNFORMATTED " int4_ops".
 169       trailing = ", ".
 170     END.
 171     PUT UNFORMATTED " ) ;" SKIP.
 172   END.
 173
 174 END PROCEDURE.
 175
 176 /* _UIB-CODE-BLOCK-END */
 177 &ANALYZE-RESUME
 178
 179
 180 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE dump-tabledat Procedure
 181 PROCEDURE dump-tabledat :
 182 /*------------------------------------------------------------------------------
 183   Purpose:
 184 ------------------------------------------------------------------------------*/
 185 DEF INPUT PARAMETER fname AS CHAR NO-UNDO.
 186
 187 DEF VAR trailing AS CHAR NO-UNDO.
 188 DEF VAR fld-type AS CHAR NO-UNDO.
 189 DEF VAR i AS INT NO-UNDO.
 190 DEF VAR line AS CHAR NO-UNDO.
 191   OUTPUT TO VALUE( file-name + ".p" ).
 192   PUT UNFORMATTED
 193         "~{inc/tabledump.i~}" SKIP(1)
 194         "PROCEDURE DumpTableData:" SKIP(1)
 195         "  OUTPUT TO " + file-name + ".sql KEEP-MESSAGES APPEND." SKIP
 196         "  FOR EACH " + fname + " NO-LOCK:" SKIP
 197         "    PUT UNFORMATTED ~"INSERT INTO " + munged(fname, "tbl") + " VALUES( ~"." SKIP .
 198   trailing = "".
 199   FOR EACH _Field OF _File:
 200     fld-type = SUBSTRING( _Field._Data-type, 1, 3).
 201     IF _Field._Extent > 1 THEN DO:
 202       line = '    PUT UNFORMATTED "' + trailing + ' ~~~{".'.
 203       PUT UNFORMATTED line SKIP.
 204       trailing = "".
 205       DO i = 1 TO _Field._Extent:
 206         PUT UNFORMATTED "    PUT UNFORMATTED ".
 207         IF trailing <> "" THEN PUT UNFORMATTED '"' + trailing + '" +'.
 208         PUT UNFORMATTED " convert-" + fld-type + "( " + _Field._Field-name + "[" + STRING(i) + "] )." SKIP.
 209         trailing = ", ".
 210       END.
 211       PUT UNFORMATTED '    PUT UNFORMATTED "' + '~~~}".' SKIP.
 212     END.
 213     ELSE
 214       PUT UNFORMATTED '    PUT UNFORMATTED "' + trailing + '" + convert-' + fld-type + "( " + _Field._Field-name + " )." SKIP.
 215
 216     trailing = ", ".
 217   END.
 218   PUT UNFORMATTED "    PUT UNFORMATTED ~" ); ~" SKIP." SKIP.
 219   PUT UNFORMATTED "  END." SKIP "  OUTPUT CLOSE." SKIP(1) "END PROCEDURE.".
 220   OUTPUT CLOSE.
 221
 222   RUN VALUE( file-name + ".p" ).
 223
 224 END PROCEDURE.
 225
 226 /* _UIB-CODE-BLOCK-END */
 227 &ANALYZE-RESUME
 228
 229
 230 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE dump-tabledef Procedure
 231 PROCEDURE dump-tabledef :
 232 /*------------------------------------------------------------------------------
 233   Purpose:
 234 ------------------------------------------------------------------------------*/
 235 DEF INPUT PARAMETER fname AS CHAR NO-UNDO.
 236
 237 DEF VAR trailing AS CHAR NO-UNDO INITIAL " (".
 238   PUT UNFORMATTED "CREATE TABLE " + munged(fname, "tbl").
 239   FOR EACH _Field OF _File:
 240     PUT UNFORMATTED trailing SKIP.
 241     RUN dump-fielddef( fname, _Field._Field-Name ).
 242     trailing = ",".
 243   END.
 244   PUT UNFORMATTED SKIP ") ;" SKIP.
 245
 246 END PROCEDURE.
 247
 248 /* _UIB-CODE-BLOCK-END */
 249 &ANALYZE-RESUME
 250
 251
 252 /* ************************  Function Implementations ***************** */
 253
 254 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION create-myindex Procedure
 255 FUNCTION create-myindex RETURNS CHARACTER
 256   ( INPUT new-index AS CHAR ) :
 257 /*------------------------------------------------------------------------------
 258   Purpose:
 259     Notes:
 260 ------------------------------------------------------------------------------*/
 261 DEF BUFFER LastIndex FOR MyIndex.
 262
 263   FIND LAST LastIndex WHERE LastIndex.Index-name = new-index NO-LOCK NO-ERROR.
 264   CREATE MyIndex.
 265   MyIndex.index-name = new-index.
 266   MyIndex.seq = (IF AVAILABLE(LastIndex) THEN LastIndex.seq + 1 ELSE 0).
 267   MyIndex.actual-name = munged( new-index
 268                      + (IF AVAILABLE(LastIndex) THEN "_" + STRING(MyIndex.seq) ELSE "")
 269                      , "idx").
 270
 271   RETURN MyIndex.actual-name.
 272
 273 END FUNCTION.
 274
 275 /* _UIB-CODE-BLOCK-END */
 276 &ANALYZE-RESUME
 277
 278
 279 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION munged Procedure
 280 FUNCTION munged RETURNS CHARACTER
 281   ( INPUT from-name AS CHAR, INPUT bad_suffix AS CHAR) :
 282 /*------------------------------------------------------------------------------
 283   Purpose:  Munge a name into an SQL friendlier version
 284     Notes:  Not strictly necessary, but a good idea.
 285 ------------------------------------------------------------------------------*/
 286 DEF VAR result AS CHAR NO-UNDO.
 287
 288   result = REPLACE( from-name, "-", "_").
 289   result = LOWER(result).
 290
 291   IF CAN-DO( keywords, result ) THEN result = result + "_" + bad_suffix.
 292
 293   RETURN result.
 294
 295 END FUNCTION.
 296
 297 /* _UIB-CODE-BLOCK-END */
 298 &ANALYZE-RESUME
 299
 300