Comments of DB Objects in Zeos

[Wild_Pointer]

Hello,

in ZDbcPostgreSqlMetadata.pas I see the table of DB comments (pg_description) is used when getting column information :

Code: Select all

function TZPostgreSQLDatabaseMetadata.UncachedGetColumns(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const ColumnNamePattern: string): IZResultSet;
{const
  VARHDRSZ = 4;
}var
  TypeOid, AttTypMod: Integer;
  SQL, PgType: string;
begin
    Result:=inherited UncachedGetColumns(Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern);

    if (GetDatabaseInfo as IZPostgreDBInfo).HasMinimumServerVersion(7, 3) then
    begin
      SQL := 'SELECT n.nspname,' {1}
        + 'c.relname,' {2}
        + 'a.attname,' {3}
        + 'a.atttypid,' {4}
        + 'a.attnotnull,' {5}
        + 'a.atttypmod,' {6}
        + 'a.attlen,' {7}
        + 'a.attnum,' {8}
        + 'pg_get_expr(def.adbin, def.adrelid) as adsrc,' {9}
        + 'dsc.description ' {10}
        + ' FROM pg_catalog.pg_namespace n '
        + ' JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) '
        + ' JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) '
        + ' LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid'
        + ' AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc'
        + ' ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) '
        + ' LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid'
        + ' AND dc.relname=''pg_class'') LEFT JOIN pg_catalog.pg_namespace dn'
        + ' ON (dc.relnamespace=dn.oid AND dn.nspname=''pg_catalog'') '
        + ' WHERE a.attnum > 0 AND NOT a.attisdropped';
      if SchemaPattern <> '' then
      begin
        SQL := SQL + ' AND n.nspname LIKE '
          + EscapeString(SchemaPattern);
      end else begin
         SQL := SQL + ' AND pg_table_is_visible (c.oid) ';
      end; 
    end
    else
    begin
      SQL := 'SELECT NULL::text AS nspname,' {1}
        + 'c.relname,' {2}
        + 'a.attname,' {3}
        + 'a.atttypid,' {4}
        + 'a.attnotnull,' {5}
        + 'a.atttypmod,' {6}
        + 'a.attlen,' {7}
        + 'a.attnum,' {8}
        + 'NULL AS adsrc,' {9}
        + 'NULL AS description' {10}
        + 'FROM pg_class c, pg_attribute a '
        + ' WHERE a.attrelid=c.oid AND a.attnum > 0 ';
    end;

    SQL := SQL + ' AND c.relname LIKE ' + EscapeString(ToLikeString(TableNamePattern))
      + ' AND a.attname LIKE ' + EscapeString(ToLikeString(ColumnNamePattern))
      + ' ORDER BY nspname,relname,attnum';

    with GetConnection.CreateStatement.ExecuteQuery(SQL) do
    begin
      while Next do
      begin
        AttTypMod := GetInt(6 {atttypmod});

        TypeOid := GetInt(4 {atttypid});
        PgType := GetPostgreSQLType(TypeOid);

        Result.MoveToInsertRow;
        Result.UpdateNull(1);
        Result.UpdateString(2, GetString(1 {nspname}));
        Result.UpdateString(3, GetString(2 {relname}));
        Result.UpdateString(4, GetString(3 {attname})); 
        Result.UpdateInt(5, Ord(GetSQLTypeByOid(TypeOid)));
        Result.UpdateString(6, PgType);
        Result.UpdateInt(8, 0);

        if (PgType = 'bpchar') or (PgType = 'varchar') or (PgType = 'enum') then
        begin
          if AttTypMod <> -1 then
            Result.UpdateInt(7, AttTypMod - 4)
          else Result.UpdateInt(7, 0);
        end
        else if (PgType = 'numeric') or (PgType = 'decimal') then
        begin
          Result.UpdateInt(7, ((AttTypMod - 4) div 65536)); //precision
          Result.UpdateInt(9, ((AttTypMod -4) mod 65536)); //scale
          Result.UpdateInt(10, 10); //base? ten as default
        end
        else if (PgType = 'bit') or (PgType = 'varbit') then
        begin
          Result.UpdateInt(7, AttTypMod);
          Result.UpdateInt(10, 2);
        end
        else
        begin
          Result.UpdateInt(7, GetInt(7 {attlen}));
          Result.UpdateInt(10, 2);
        end;

        Result.UpdateNull(8);
        if GetBoolean(5 {attnotnull}) then
        begin
          Result.UpdateString(18, 'NO');
          Result.UpdateInt(11, Ord(ntNoNulls));
        end
        else
        begin
          Result.UpdateString(18, 'YES');
          Result.UpdateInt(11, Ord(ntNullable));
        end;

        Result.UpdateString(12, GetString(10 {description}));
        Result.UpdateString(13, GetString(9 {adsrc}));
        Result.UpdateNull(14);
        Result.UpdateNull(15);
        Result.UpdateInt(16, Result.GetInt(7));
        Result.UpdateInt(17, GetInt(8 {attnum}));

        Result.UpdateNullByName('AUTO_INCREMENT');
        Result.UpdateBooleanByName('CASE_SENSITIVE',
          GetIdentifierConvertor.IsCaseSensitive(GetString(3 {attname})));
        Result.UpdateBooleanByName('SEARCHABLE', True);
        Result.UpdateBooleanByName('WRITABLE', True);
        Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
        Result.UpdateBooleanByName('READONLY', False);

        Result.InsertRow;
      end;
      Close;
    end;
end;

The problem I face is when DB is of encoding UTF8 and a client uses client_encoding that is not UTF8 (Win1251 (Russian) for example). I can make sure the results of my queries can be translated to Win1251, but now I must also take comments into consideration. They are written in Lithuanian (encoding Win1257) and is not compatible with Win1251.
The work around is either:
1)using only English comments
2)using only English letters for comments

The other option is not to query comment data in ZEOS. Why do we need it in the first place? If we don't need it - I would vote for loosing pg_description from the queries. (I can make a PATCH)

[EgonHugeist]

Wild_Pointer,

before patching the codebase: Do i understand you right?: ProstgreSQL doesn't convert the characters to your current client encoding? Or can't you display the characters with your compiler. I know the byte-sequences between 1251 and 1257 won't be compatible. What i do not understand is what happens exactly? Do you get some exceptions?

I think omiting the field will raise some new bugreports for people who are using the TZMetadata-Component f.e..

[Wild_Pointer]

EgonHugeist,

ProstgreSQL doesn't convert the characters to your current client encoding?

Well, it tries but because the byte-sequences are incompatible it gives an exception "SQL Error: ERROR: character with byte sequence 0xc5 0xa1 in encoding "UTF8" has no equivalent in encoding "WIN1251"". The table data has no special chars, just English letters, but I have Lithuanian letters in the comment of one collumn.

I think omiting the field will raise some new bugreports for people who are using the TZMetadata-Component f.e..

So that's what the comments are used for... I've never used the component before, so it is hard for me to decide, but why have the comment info where it is not needed (in TZQuery)?

My colleague argues that comments should contain just Latin letters, but it is my conviction that no matter what language you use, you should use it right (no matter Russian, Lithuanian or Delphi )

[EgonHugeist]

Wild_Pointer,

i propose a workaround. Did you upgrade to Zeos7? I'm starting now from the premisse you're a Delphi user. Follow my suggestion:

Code: Select all

TZConnection.ClientCodepage := 'utf8';
TZConnection.ControlsCodepage := cGet_ACP; //if you're a Ansi-Delphi user (<=D2007)
TZConnection.AutoEncodeStrings := True;

The implementations i made for Zeos7 does convert all incoming utf8-bytesequences to your current codepage. All unsupported byte-sequences where displayed as '?'. But this should suppress the postgre-exception you got and you don't need to change any codeline or your custom to work with Zeos. So we don't need to change the current codebase and you have a workaround for your 1251/1257 encoding issue in the comment field. What do you think?

Edit: Do you try to update a db with current encoding of CP1257?

[mdaems]

Did this solve the problem? (Or more precise : is that a usable workaround?)

Actually I would classify this under the category 'database problems' or 'unfortunate encoding incompatibility'. But of course, if we can provide a usable workaround, that would be nice.