reflects on firebird INTEGER as BIGINT, BIGINT as NUMERIC

[Former user]

when reflecting from existing firebird table sqlalchemy reflects INTEGER fields as being BIGINT and BIGINT fields as NUMERIC

[Mike Bayer]

current translations:

 'LONG': BIGINT,
 'INT64': NUMERIC,

'INT64' should be BIGINT. not clear what LONG is. Have searched the FB site and cannot find any comprehensive documentation on types except for http://www.firebirdsql.org/manual/migration-mssql-data-types.html which is not complete.

[Former user]

(original author: werner) Some of the Interbase 6 documentation is still the base for FB.

http://www.firebirdsql.org/en/reference-manuals/

On the above page: InterBase 6.0 Manuals the Data Definition Guide

And then there are the Language Reference Updates.

BigInt is in 1.5: http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd15.html

And there is a FAQ about limits: http://www.firebirdfaq.org/faq61/

[Mike Bayer]

can you propose changes to the dictionary please ?

ischema_names = {
      'SHORT': SMALLINT,
       'LONG': BIGINT,
       'QUAD': FLOAT,
      'FLOAT': FLOAT,
       'DATE': DATE,
       'TIME': TIME,
       'TEXT': TEXT,
      'INT64': NUMERIC,
     'DOUBLE': FLOAT,
  'TIMESTAMP': TIMESTAMP,
    'VARYING': VARCHAR,
    'CSTRING': CHAR,
       'BLOB': BLOB,
    }

does any of it need to change based on the version of FB in use ? ( I hope not? )

[Former user]

Extract from Helen Borrie Firebird book :

BIGINT, INTEGER and SMALLINT [BR] • SMALLINT is a signed short integer with a range from –32,768 to 32,767 [BR] • INTEGER is a signed long integer with a range from –2,147,483,648 to 2,147,483,647[BR] • BIGINT is a signed 64-bit integer with a range from 2-63 to 263 -1. Not available in Dialect 1.

NUMERIC and DECIMAL[BR] NUMERIC and DECIMAL types, as implemented in Firebird, are identical except when precision is less than 5. Both types effectively conform with the standard DECIMAL type.[BR] NUMERIC is thus not compliant with SQL-92. precision is in 1,18

FLOAT and DOUBLE PRECISION[BR] Firebird provides two floating-point or approximate numeric data types, FLOAT and DOUBLE PRECISION, differing only in the limit of precision. FLOAT is a 32-bit floating-point data type with a limit of approximately 7 digits of precision—assume 6 digits for reliability. A 10-digit number 25.33333312 inserted into a FLOAT column is stored as 25.33333. Range from -3.402 x 1038 to 3.402 x 1038. The smallest positive number it can store is 1.175 x 10 –38.[BR] DOUBLE PRECISION is a 64-bit floating-point data type with a limit of approximately 15 digits of precision. Range from –1.797 x 10308 to 1.797 x 10308. The smallest positive number it can store is 2.225 x 10–308.

DATE, TIME, and TIMESTAMP[BR] DATE stores the date alone, with no time portion – “date-only”. Storage is a 32-bit signed longword. Storable dates range from January 1, 0001 to December 31,9999.[BR] There is no “date-only” type in dialect 1[BR] The TIMESTAMP data type is made of two 32-bit longword portions, storing a date and a time. It is stored as two 32-bit longwords, equivalent to the DATE type in dialect 1.[BR] A column or variable of type TIME stores the time of day, with no date portion – “time- only”. Storage is a 32-bit unsigned longword. Storable times range 00:00 to 23:59:59.9999.

CHARACTER, VARYING CHARACTER, and NATIONAL CHARACTER[BR] CHAR(n), alias CHARACTER (n) is the base fixed-length character type. n represents the exact number of characters stored. CHAR and CHARACTER are completelysynonymous. It will store strings in any of the supported character sets.[BR]

NCHAR(n), alias NATIONAL CHAR(n) is a specialized implementation of CHAR(n) that is pre-defined with ISO8859_1 as its character set attribute. Of course, it is not valid to define a character set attribute for an NCHAR column, although a collation sequence —the sequence in which the sorting of characters is arranged for searches and ordered output— can be defined for a column or domain that uses this type.

I think you can forget NCHAR(n)

VARCHAR(n), alias CHARACTER VARYING(n), is the base variable-length character type. n represents the maximum number of characters that can be stored in the column. It will store strings in any of the supported character sets. If no character set is defined, the character set attribute defaults to the one defined during CREATE DATABASE as DEFAULT CHARACTER SET. If there is no default character set, the column default is CHARACTER SET NONE.

NCHAR VARYING(n), alias NATIONAL CHAR VARYING(n), alias NATIONAL CHARACTER VARYING(n), is a specialized implementation of VARCHAR(n) that is pre-defined with ISO8859_1 as its character set attribute. It is not valid to define a character set attribute for an NVARCHAR column, although a collation sequence —the sequence in which the sorting of characters is arranged for searches and ordered output— can be defined for a column or domain that uses this type.

I think you can forget NCHAR VARYING(n)

BLOB, typed and untyped

BLOB SUB_TYPE 0, alias BINARY[BR] Generic BLOB type for storing any sort of data, including text. Commonly referred to as “an untyped binary BLOB”. Firebird is unaware of the contents. This is the default subtype.

BLOB SUB_TYPE 1, alias TEXT[BR] More specialized sub-type for storing plain text. Equivalent to CLOB and MEMO types implemented by some other DBMSs.

ARRAY[BR] An array can contain elements of any Firebird data type except BLOB. Arrays of ARRAY are not supported. All of the elements of a particular array are of the same data type.

[Mike Bayer]

see #2757 which has a patch