gestinux trunk database structure

[nomorelogic]

hi all

I downloaded sources from svn, trunk and I've a question about TGTable.FieldTypeToSql (unit gdbconnection).

This method seems not handle field type ftBlob for TMemofield.
However I can find this field type in TableQueries.SqlCode (and some other tables), thus I can't build a database from scratch.

Probably it is work in progress, I need to be able to finish to build the database from code.
I would like to have a patch or advice on how to finish the construction of db.

Thanks in advantage
nomorelogic


PS:
it_IT_utils.ini translated and committed on trunk
forgive me for having unintentionally changed fr_FR_util.ini, immediately restored

[tintinux]

Hi

I can create a database with the trunk sources and use the memo fields without problem...
I don't think there is anyting in progress.
What is your issue and when it occurs ?

Note that to create a database you should let Gestinux do the job, entering a non existing database name in the "Database" window.
Of course it is possible to create the database by script (this is done at mrit.com/cloud) but the datatypes must be the same as those created by Gestinux.

Best regards
Tintinux

[nomorelogic]

I let Gestinux create database from scratch but
during the creation of Queries table (in unit UnitDataModule, procedure DbAfterConnect, statement TableQueries.Check)

I get this error
Impossibile connettersi al server 127.0.0.1 con il nome utente nomorelogic (Unsupported field type for TableQueriesSqlCode Type=15)

I think this is due to this code: as you can see, "case aField.DataType of" doesn't handle ftBlob case (field type of field SqlCode) and the result is the execution of the raise statement with the message above

Code: Select all

//----------------------------------------------------------
// Returns the Sql keywords corresponding to a DataType
//----------------------------------------------------------
function TGTable.FieldTypeToSql(const aField: TField;
  const aProtocol: TDBMSType): string;

begin
  case aField.DataType of
    ftString: Result := 'VARCHAR(' + IntToStr(aField.Size) + ')';
    ftFixedChar: Result := 'CHAR(' + IntToStr(aField.Size) + ')';
    ftBoolean: if aProtocol in [Dbms_mysql, dbms_Oracle] then
        Result := 'TINYINT'
      else
        Result := 'BOOLEAN';
    ftAutoInc: if aProtocol = Dbms_pgsql then
        Result := 'SERIAL'
      else
        Result := 'INTEGER AUTO_INCREMENT';
    ftSmallint: Result := 'SMALLINT';
    ftInteger: Result := 'INTEGER';
    ftLargeint: Result := 'BIGINT';
    ftDateTime: if aProtocol = Dbms_pgsql then
        Result := 'TIMESTAMP'
      else
        Result := 'DATETIME';
    ftDate: Result := 'DATE';
    ftMemo: if aProtocol = Dbms_pgsql then
        Result := 'TEXT'
      else
        Result := 'LONGTEXT';
    else
      raise Exception.Create('Unsupported field type for ' + aField.Name +
        ' Type=' + IntToStr(Ord(aField.DataType)));
  end;
end;

best regards
nomorelogic

[tintinux]

Hi

On my machines, the ftMemo field type is always passed, so there is no problem...
For some reason you get ftBlob when I get ftMemo.

Could you find out for which field of which table ? It might be better or worse depending on the field where it occur.

There is certainly a difference between ours environments explaining this, and it is always interesting to find out which one.
It can be ZeosLib version (I have 7.1.3a stable) or MySql (I have 5.5.47), probably not Lazarus (I use 1.2.6 for Gestinux).
What are yours ?

Of course it should be possible to add ftBlob in the case returning the appropriate SQL keyword (and for PostgreSql too).
But before we must make sure that this is due to a newer version of some library, not due to a too old one.

Best regards
Tintinux

[nomorelogic]

I think I've found something but still confused too.

Starting from the begin, my environment is:
mysql (innodb_version) 5.6.28
zeoslib 7.1.4

Different from yours but trouble I think is in FPC, cause I use Lazarus trunk wich uses FPC 3 instead of Lazarus 1.2.6 wich uses fpc 2.6.4.
In .../fpc/packages/fcl-db/src/base/db.pas there no changes: order is not changed, ftBlob = 15 in both cases

db.pas in fpc 2.6.4

Code: Select all

  TFieldType = (ftUnknown, ftString, ftSmallint, ftInteger, ftWord,
    ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate,  ftTime, ftDateTime,
    ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo,
    ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar,
    ftWideString, ftLargeint, ftADT, ftArray, ftReference,
    ftDataSet, ftOraBlob, ftOraClob, ftVariant, ftInterface,
    ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd, ftFixedWideChar, ftWideMemo);

db.pas in fpc 3.0

Code: Select all

  TFieldType = (ftUnknown, ftString, ftSmallint, ftInteger, ftWord,
    ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate,  ftTime, ftDateTime,
    ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo,
    ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar,
    ftWideString, ftLargeint, ftADT, ftArray, ftReference,
    ftDataSet, ftOraBlob, ftOraClob, ftVariant, ftInterface,
    ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd, ftFixedWideChar, ftWideMemo);

there's something new in fpc 3, but this shoulnd't break

Code: Select all

{ TBlobField }
  TBlobStreamMode = (bmRead, bmWrite, bmReadWrite);
  // This type is needed for compatibility. While it should contain only blob
  // types, it actually does not.
  // Instead of this, please use ftBlobTypes
  TBlobType = ftBlob..ftWideMemo deprecated 'Warning: Does not contain BLOB types. Please use ftBlobTypes.';

  TBlobField = class(TField)

strange to me is that in unitdatamodule.lfm, SqlCode is defined as ftBlob and not as ftMemo!
I thought about RTTI troubles and I checked in sourceforce, code is following: BlobType = ftBlob.
So, I can't figure out why I get thi error while you don't.

Code: Select all

    object TableQueriesSqlCode: TMemoField
      DisplayWidth = 10
      FieldKind = fkData
      FieldName = 'SqlCode'
      Index = 3
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = True
      BlobType = ftBlob
      Transliterate = False
    end

Anyway I fixed that way, it works, but I have not figured out why
how is your lfm?

Code: Select all

    ftMemo {$IFDEF VER3_0}, ftBlob{$ENDIF}: if aProtocol = Dbms_pgsql then
        Result := 'TEXT'
      else
        Result := 'LONGTEXT';

[tintinux]

You are right, SqlCode should have a BlobType to ftMemo and not ftBlob since it contains only text.
I will correct this, and you should no more have a ftBlob for this field.

I suppose that the FPC (or ZeosLib) version I have returns somewhere ftMemo when ftBlob is found, while yours don't. That is why I never had the problem.

In table Products, for example, there is a field Picture having a BlobType to ftBlob.
This sounds logical for an image, but it works like this (for me) creating MySQL fields of type TEXT, because Gestinux always save images to database as XPM.
XPM is text and this allow to export image to a SQL script in plain text. With other image types, we could have binary content, leading to trouble.
Anyway I'm afraid you'll get the same error on this field (if you don't fix as proposed).

My solution is to correct also the type of this field to ftMemo, and in any other place where ftBlob is used : it should work on your environment without other changes.

You proposed fix make MySQL field of type TEXT when BlobType is ftBlob.
I don't know if it works. Did you tried storing and retrieving data ?
If it works, I don't find it very consistant.
I would suggest to return BLOB when BlobType is ftBlob, but we have to check if the upgrade from 1.2 to trunk works fine, in recent(s) versions of MySql and PostGreSQL.

IMHO, my solution is simpler and have less unknown consequences.

What do you think ?

Best regards

[nomorelogic]

...
You proposed fix make MySQL field of type TEXT when BlobType is ftBlob.
I don't know if it works. Did you tried storing and retrieving data ?
If it works, I don't find it very consistant.
I would suggest to return BLOB when BlobType is ftBlob, but we have to check if the upgrade from 1.2 to trunk works fine, in recent(s) versions of MySql and PostGreSQL.
IMHO, my solution is simpler and have less unknown consequences.
What do you think ?

My fix was an attempt to bypass this bug to see if database generation process came to an end.
Now that we understand we need a real fix.

My solution is to correct also the type of this field to ftMemo, and in any other place where ftBlob is used : it should work on your environment without other changes.

Store images as text is a nice idea.
About upgrading, on RDBMS side, fields have already been generated with the right DDL both in MySql and Postgres.
What we need is that the trunk continues to do things in the same way.

You're right: Replace ftBlob with ftMemo everywhere in .flm I think is the best fix (and we have ftBlob type free to use natively the future).

best regards
nomorelogic

[Hakala]

Hi

On my machines, the ftMemo field type is always passed, so there is no problem...
For some reason you get ftBlob when I get ftMemo.

Could you give more information on these lightweight wheelchairs and find out for which field of which table ? It might be better or worse depending on the field where it occur.

There is certainly a difference between ours environments explaining this, and it is always interesting to find out which one.
It can be ZeosLib version (I have 7.1.3a stable) or MySql (I have 5.5.47), probably not Lazarus (I use 1.2.6 for Gestinux).
What are yours ?

Of course it should be possible to add ftBlob in the case returning the appropriate SQL keyword (and for PostgreSql too).
But before we must make sure that this is due to a newer version of some library, not due to a too old one.

Best regards
Tintinux

Are there any drawbacks about storing images as text btw? Or is that the best solution to this problem?

[tintinux]

Hi

Storing images as texts is not a solution to "this problem", it is the opposite. The problem submitted in the thread was caused by wrong datatypes for some MySql fields, when storing images as text. This has been solved for 3 years. It is better to avoid reopening so old threads...

The choice to store images as texts in Gestinux allows a backup of the database containing only text, independent of the DBMS used, and that we can edit, save and exchange with tools working on texts.

As far as I know it is working fine and no important drawback was reported. It increases the size of the database storage, but this don't look like an issue.

Best regards