Author Topic: (Transformation) CONVERT_TYPE(  (Read 946 times)

son-of-sargasso

  • EA User
  • **
  • Posts: 122
  • Karma: +0/-0
    • View Profile
(Transformation) CONVERT_TYPE(
« on: November 08, 2009, 02:21:55 pm »
Here on Venus we are having a little bit of bother deciphering the EA help for the CONVERT_TYPE macro in transformations. We (the Royal one), are simply trying to convert an imported M$Access  table  to its much preferred PostgreSQL equivalent, such that we may, should we desire, generate some almost perfect DDL code.

We just don't quite get the gist, so to speak, of what the heck to put in the parameters of our call to this most fortunate Sparxian  macro.  

Would one or two of you who have managed to grok this piece of magick kindly pop us back a word or two on the above.

yours in continued bewilderment
bruce
« Last Edit: November 08, 2009, 02:23:39 pm by barrydrive »

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7290
  • Karma: +84/-12
    • View Profile
Re: (Transformation) CONVERT_TYPE(
« Reply #1 on: November 09, 2009, 08:50:52 am »
From what you are trying to do, I think you'll get better results going into the table and changing the database.

In fact, right click on a package in the tree, select Code Engineering | Reset DBMS options.  That makes at least a semi intelligent guess about the datatypes.
Eve

support@sparxsystems.com

son-of-sargasso

  • EA User
  • **
  • Posts: 122
  • Karma: +0/-0
    • View Profile
Re: (Transformation) CONVERT_TYPE(
« Reply #2 on: November 09, 2009, 12:29:06 pm »
Simon,

Your right, the results are better! and it is semi intelligent!  ;D

I've still got to complete the transform to change all names to lc, create proper key names, index names etc and to fix all the defaults (e.g. m$access booleans from "Yes" to True).

I also would like to rationalise the string lengths.  PostgreSQL doesn't care if the string is one character or a gazillion.  However, there is a reason for using length limited strings - it will truncate them when we specifically want short names - even if the coder doesn't set the length limit in the UI.  Probably not the way its done int the best salons I guess, but hey it looks like a suitale hammer to me!  Which brings me to:

In the transform, I would like to do the following pseudocode:
Code: [Select]
if atttype in ("char", "varchar") then{
    select case attlength
        case 0
           change_type_to_text
        case 1
           change_type_to_char
        case 2-10
            change_length_to_12
            change_type_to_varchar
        case 11-50
            change_type_to_varchar*
        case >50
             change_type_to_text
    }
}
 

Any pointers on how to do this.  (BTW there are over 120 tables and around 3000 columns!)


bruce

p.s. any thoughts on this: http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1257566905
« Last Edit: November 09, 2009, 12:37:44 pm by barrydrive »

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7290
  • Karma: +84/-12
    • View Profile
Re: (Transformation) CONVERT_TYPE(
« Reply #3 on: November 09, 2009, 02:38:41 pm »
Well, the transform templates don't do numerical comparisons well (at all).  So, I would actually recommend just calling EXEC_ADD_IN.

You'll want it to return something like

type="newtype"
length="newlength"

Oh, and regarding the other thread... I don't know a great deal about databases, less about postgreSQL, and the database engineering is one area of EA I haven't had a lot to do with.  Basically, I'm not the person you want answering that question.
« Last Edit: November 09, 2009, 02:40:27 pm by simonm »
Eve

support@sparxsystems.com