On Thursday 20 December 2007 01:44:34 am Wolfgang Keller wrote:
> Hello,
>
> sorry it took me so long to reply.
>
> > As Ed suggested our current Postgres interface does not support
> > special data types. But I would like it too. Could you describe
> > the data type you are creating. Maybe there is a way.
>
> I am basically defining custom types for everything, so that I don't
> have to use "native" datatypes in the table definitions:
>
> CREATE DOMAIN unsigned_smallint AS SMALLINT
> CHECK(VALUE >= 0);
>
> CREATE DOMAIN unsigned_integer AS INTEGER
> CHECK(VALUE >= 0);
>
> CREATE DOMAIN cris_range_0_100_type AS SMALLINT
> CHECK(VALUE >= 0 AND VALUE <=100);
>
> CREATE DOMAIN cris_range_0_1_type AS DOUBLE
> CHECK(VALUE >=0 AND VALUE <=1);
>
> CREATE DOMAIN cris_segment_or_asset_type AS CHARACTER(1)
> CHECK(VALUE = 'A' OR VALUE = 'S');
>
> CREATE DOMAIN cris_no_or_yes_type AS character(1)
> CHECK(VALUE = 'N' OR VALUE = 'Y');
>
> CREATE DOMAIN cris_motion_direction_type AS character(1)
> CHECK(VALUE = 'N' OR VALUE = 'R');
>
> CREATE DOMAIN cris_freq_or_order_type AS character(1)
> CHECK(VALUE = 'F' OR VALUE = 'O');
>
> CREATE DOMAIN cris_pct_or_oct_type AS character(1)
> CHECK(VALUE = 'O' OR VALUE = 'P');
>
> CREATE DOMAIN cris_secs_or_revs_type AS character(1)
> CHECK(VALUE = 'R' OR VALUE = 'S');
>
> CREATE TYPE cris_string16_type AS CHARACTER(16);
> CREATE TYPE cris_string254_type AS CHARACTER VARYING(254);
> CREATE TYPE cris_string4000_type AS CHARACTER VARYING(4000);
> CREATE TYPE cris_base64binary_type AS BYTEA;
> CREATE TYPE cris_ushort_type AS unsigned_smallint;
> CREATE TYPE cris_short_type AS SMALLINT;
> CREATE TYPE cris_uint_type AS unsigned_integer;
> CREATE TYPE cris_double_type AS DOUBLE PRECISION;
>
> CREATE TYPE cris_datetime_type AS (
> datetime TIMESTAMP(0) WITHOUT TIME ZONE
> nanoseconds NUMERIC(9, 9)
> );
>
> TIA,
>
> Sincerely,
>
> Wolfgang Keller
Using
CREATE DOMAIN unsigned_smallint AS SMALLINT CHECK(VALUE >= 0);
dbPostgres.py getFields() would find "unsigned_smallint" as the data type and
by chance would be correct in applying it to the correct Dabo data type. Of
course if the user defined data type that had not contained the "int" in the
name or anything of the other standard data types names dbPostgres would have
returned 'fldType = "?"" for a field data type.
It maybe possible to check the pg_type table and determine the actual data
type by checking the "typbasetype" field. If the "typbasetype "is not" 0
then it is a pointer (oid) into the pg_type which returns the basic postgres
data type. This also appears to allow recursive assignments. IOW the first
user defined data type can point to a user data ... etc.
Something like below.
fldType = findRealDataType(r["typname"])
if "int" in fldType:
fldType = "I"
...
else:
fldType = "?"
But in the case of
CREATE TYPE cris_uint_type AS unsigned_integer;
I don't see how our current way of Dabo data type assignments would work.
The first form of CREATE TYPE creates a composite type. The composite type is
specified by a list of attribute names and data types. Sort of like
the "array" data type we are not supporting currently.
And in the second form of "CREATE TYPE" the use of input and output functions
is required. But the data type is defined and should resolve to one of
Postgres basic data types for the output function. However, the data type of
the input function could be any of the allowed Postgres data types (including
user defined types). Although, it (input function) normally accepts a
string, it means I will not know what the input function requires as a data
type (without some sort of hard coding or reading the input function).
I think it would require some major coding of the framework to allow data
types created with "CREATE TYPE" to work with DataFields and other parts of
the framework.
Anybody have any different thoughts?
--
John Fabiani
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
