Class TSqlitePassDatabaseDataTypeOptions

DescriptionHierarchyFieldsMethodsProperties

Unit

Declaration

type TSqlitePassDatabaseDataTypeOptions = class(TPersistent)

Description

The TSqlitePassDatabaseDataTypeOptions is used by the TSqlitePassDatabase.DatatypeOptions property and gives you the opportunity to set custom or default behaviors for a given database. It lets you also define how fields.datatype will be retrieved and translated into pascal datatypes using TranslationRules and CustomFieldDefs.

Hierarchy

Overview

Methods

Public constructor Create(Database: TSqlitePassDatabase);
Public destructor Destroy; override;
Public function GetOwner: TPersistent; override;
Public Procedure ApplyCustomFieldDefs;
Public Procedure LoadFromDatabase(_LoadOptions: TSqlitePassDataTypeStorageOptions = []);
Public Procedure SaveToDatabase(_SaveOptions: TSqlitePassDataTypeStorageOptions = []);
Public procedure SetDefaultValues(DatabaseType: TSqlitePassDatabaseType; SetTranslationRules: Boolean=false);
Protected procedure DefineProperties(Filer: TFiler); override;

Properties

Published property BooleanFormat : String Read FBooleanFormat Write SetFBooleanFormat;
Published property BooleanStorage : TSqlitePassBooleanStorage Read FBooleanStorage Write FBooleanStorage;
Published property CustomFieldDefs : TSqlitePassCustomFieldDefs Read FCustomFieldDefs Write FCustomFieldDefs;
Published property DateFormat : String Read FDateFormat Write SetFDateFormat;
Published property DateStorage : TSqlitePassDateStorage Read FDateStorage Write FDateStorage;
Published property DateTimeFormat : String Read FDateTimeFormat Write SetFDateTimeFormat;
Published property DateTimeStorage : TSqlitePassDateTimeStorage Read FDateTimeStorage Write FDateTimeStorage;
Published property DecimalSeparator : Char Read FDecimalSeparator Write FDecimalSeparator;
Published property DefaultFieldType : TFieldType Read FDefaultFieldType Write FDefaultFieldType;
Published property DetectionMode : TSqlitePassDataTypeDetectionMode Read FDetectionMode Write FDetectionMode;
Published property LoadOptions : TSqlitePassDataTypeStorageOptions Read FLoadOptions Write FLoadOptions;
Published property SaveOptions : TSqlitePassDataTypeStorageOptions Read FSaveOptions Write FSaveOptions;
Published property TimeFormat : String Read FTimeFormat Write SetFTimeFormat;
Published property TimeStorage : TSqlitePassTimeStorage Read FTimeStorage Write FTimeStorage;
Published property TranslationRules : TSqlitePassFieldTypesTranslationRules Read FFieldTypesTranslationRules Write FFieldTypesTranslationRules;
Published property UnicodeEncoding : TSqlitePassUnicodeEncoding Read FUnicodeEncoding Write SetFUnicodeEncoding;

Description

Methods

Public constructor Create(Database: TSqlitePassDatabase);

—– Constructor / Destructor —–

Public destructor Destroy; override;
 
Public function GetOwner: TPersistent; override;
 
Public Procedure ApplyCustomFieldDefs;

Public Procedure LoadFromDatabase(_LoadOptions: TSqlitePassDataTypeStorageOptions = []);
 
Public Procedure SaveToDatabase(_SaveOptions: TSqlitePassDataTypeStorageOptions = []);
 
Public procedure SetDefaultValues(DatabaseType: TSqlitePassDatabaseType; SetTranslationRules: Boolean=false);
 
Protected procedure DefineProperties(Filer: TFiler); override;
 

Properties

Published property BooleanFormat : String Read FBooleanFormat Write SetFBooleanFormat;

a String used as a Boolean formating template. The BooleanFormat is only active when the BooleanStorage property is set to "asText". In this case it is used to read or write boolean values stored as text to the database BooleanFormat is separated into two sections : The left part represents the text statements used for True, a separator '-' The right part represents the text statements used for False

Usualy BooleanFormat takes one of the following value : 'Yes,True,On,1,-No,False,Off,0'

When BooleanFormat is set to "asText" the first value of the "True" section ('Yes' in the example) and the first value of the "False" sections ('No' in the example) are used as text representation to write boolean value to database

Published property BooleanStorage : TSqlitePassBooleanStorage Read FBooleanStorage Write FBooleanStorage;

BooleanStorage determines how the Boolean values are stored in the database. It can take one of the following values : asInteger : the field value is stored as an integer. asText : the field value is stored as formated text, using the BooleanFormat property setting

Published property CustomFieldDefs : TSqlitePassCustomFieldDefs Read FCustomFieldDefs Write FCustomFieldDefs;

CustomFieldDefs : A collection of custom fielddefs.

A custom fielddef can be used to overwrite the fielddefs translation already done by the DetectionMode and the TranslationRules properties.

A custom fielddef is made of : A TableName and a FieldName to identify the field A FieldType: TfieldType (ftinteger, ftstring...etc) A FieldSize A FieldPrecision

This property can be very usefull when a fielddef cannot be built on the datatype. For example, you created a table with two BLOB fields declared with the same datatype (CLOB) in the table create statement. If you want to store MemoText in the first blob and store graphic data in the second, you just need to create two custom fielddefs using the designtime editor or the sqliteToolbox program and store them in your database using LoadOptions and SaveOptions.

At design time, a specific editor lets you define the translation rules.

Published property DateFormat : String Read FDateFormat Write SetFDateFormat;

a String used as a Date formating template. The DateFormat is only active when the DateStorage property is set to asText. In this case it is used to read or write date values stored as text to the database DateFormat accepts only Uppercases to match Day (D), Month (M), Year (Y) Any other character is treated as literal and is included is the formated String

usualy DateFormat takes one of the following value : DD-MM-YYYY YYYY-MM-DD DD/MM/YYYY YYYY/MM/DD

Published property DateStorage : TSqlitePassDateStorage Read FDateStorage Write FDateStorage;

DateStorage determines how the Date values are stored in the database. It can take one of the following values : asInteger : the field value is stored as an integer. asText : the field value is stored as formated text, using the DateFormat property setting

Published property DateTimeFormat : String Read FDateTimeFormat Write SetFDateTimeFormat;

a String used as a DateTime formating template. The DateTimeFormat is only active when the DateTimeStorage property is set to "asText". In this case it is used to read or write datetime value stored as text to the database DateFormat accepts only Uppercases for the Date part and only lowercases for the Time part to match Days (D), Months (M), Years (Y), Hours (h), Minutes (m), Secondes (s) and Msecs (z) Any other character is treated as literal and is included is the formated String

usualy DateFormat takes one of the following value : DD-MM-YYYY hh:mm:ss DD/MM/YYYY hh:mm:ss DD-MM-YYYY hh:mm:ss:zzz DD/MM/YYYY hh:mm:ss:zzz Note : Kexi databases format DateTime as DD-MM-YYYYThh:mm:ss )

Published property DateTimeStorage : TSqlitePassDateTimeStorage Read FDateTimeStorage Write FDateTimeStorage;

DateTimeStorage determines how the DateTime values are stored in the database. It can take one of the following values : dtsDateTime : the field value is stored as a double value. dtsJulian : the field value is stored as a double value converted to a julian date. dtsText : the field value is stored as formated text, using the DateTimeFormat property setting dtsUnix : the field value is stored as a int64 value converted to an Unix date. dtsMac : the field value is stored as a int64 value converted to an Apple Mac date.

Published property DecimalSeparator : Char Read FDecimalSeparator Write FDecimalSeparator;

Decimal separator : not used

Published property DefaultFieldType : TFieldType Read FDefaultFieldType Write FDefaultFieldType;

Determines the default field type used when field type translations fails. It is usually set to ftUnknow or ftString

Published property DetectionMode : TSqlitePassDataTypeDetectionMode Read FDetectionMode Write FDetectionMode;

The DetectionMode controls how the TsqlitePassDatabase component will behave when it tries to retrieve the database fields definition. It can have on of the following values :

dmTypeName
FieldTypes are retrieved from the initial SQL CREATE statement of the table for example : 'CREATE TABLE cars_names (id INTEGER PRIMARY KEY, companycode Integer, name Text(200))' Then the database uses the TranslationRules (a collection of translation Rule) to match the datatypes names found in the database table, for example 'integer' with a pascal datatype (ftInteger in this example).

dmDbSpecific
Field.datatypes are set directly by sqlitepass, depending on the Database.Databasetype value. Note : This can only be used with the 'dbtKexi' Databasetype.

dmCustom
Field.datatypes are first preset directly by sqlitepass, depending on the Database.Databasetype value. If the first match fails, it uses the TranslationRules if you defined some Finally, it fires the OnDataTypeConversion event letting you modified directly the Field.Datatype Of course, the OnDataTypeConversion Event must be assigned...

dmForceStr
Converts any Datatype to ftString;

Published property LoadOptions : TSqlitePassDataTypeStorageOptions Read FLoadOptions Write FLoadOptions;

if SoManual is not selected (default behavior) your optional settings are retrieved directly from a special table named "SQLitePass__DbSettings" every time you open a database

The LoadOptions property determines which parts of the database DataTypeOptions are retrieved from the table It can take one or more of the following values : soProperties : It retrieves your settings for BooleanExtension; BooleanStorage; DateFormat; DateStorage; DateTimeFormat; DateTimeStorage; DecimalSeparator; DefaultFieldType; DetectionMode; TimeFormat; TimeStorage;

soTranslationRules : It retrieves and fills the TranslationRules collection. soCustomFieldDefs : It retrieves and fills the CustomFieldDefs collection.

soManual : The selected parts are not retrieved automatically. You will then have to call LoadFromDatabase to load your database settings.

Published property SaveOptions : TSqlitePassDataTypeStorageOptions Read FSaveOptions Write FSaveOptions;

if SoManual is not selected (default behavior) your optional settings are stored directly to a special table named "SQLitePass__DbSettings" every time you close a database.

The SaveOptions property determines which parts of the database DataTypeOptions are saved from the table It can take one or more of the following values : soProperties : It saves your settings for BooleanExtension; BooleanStorage; DateFormat; DateStorage; DateTimeFormat; DateTimeStorage; DecimalSeparator; DefaultFieldType; DetectionMode; TimeFormat; TimeStorage;

soTranslationRules : It saves the the TranslationRules collection to the database. soCustomFieldDefs : It saves the CustomFieldDefs collection to the database.

soManual : The selected parts are not saved automatically. You will then have to call SaveToDatabase to save your database settings.

Published property TimeFormat : String Read FTimeFormat Write SetFTimeFormat;

a String used as a Time formating template. The TimeFormat is only active when the DateStorage property is set to "asText". In this case it is used to read or write Time values stored as text to the database DateFormat accepts only lowercases to match Hours (h), Minutes (m), Secondes (s) and Msecs (z) Any other character is treated as literal and is included is the formated String usualy TimeFormat takes one of the following value : hh:mm:ss hh:mm:ss:zzz hh-mm-ss hh-mm-ss-zzz

Published property TimeStorage : TSqlitePassTimeStorage Read FTimeStorage Write FTimeStorage;

TimeStorage dtermines how the Time values are stored in the database. It can take one of the following values : asInteger : the field value is stored as an integer. asText : the field value is stored as formated text, using the TimeFormat property setting

Published property TranslationRules : TSqlitePassFieldTypesTranslationRules Read FFieldTypesTranslationRules Write FFieldTypesTranslationRules;

TranslationRules : A collection of translation rules. TranslationRules are stored in *.DFM or *.LFM file but can also be stored directly in the database using the LoadOptions and SaveOptions properties.

A translation rule is made of : A datatype name (whatever you want depending on how fields datatypes are named inside the database or the table create statement) A matching rule (mmExact, mmExactNoCase, mmPartial, mmPartialNoCase, mmAll); A resulting datatype (ft... pascal fieldtype)

The matching rule can be set for each translation rule and can be one of the following values:

mmExact the datatype found in the database table definition must match exactly the rule datatype name. It is case sensitive.

For example if the 'Int' name is found in the database table definition then it will not match the ('Integer',mmExact,ftInteger) rule thus ftUnknown will be returned

mmExactNoCase Same as mmExact but not case sensitive

mmPartial the datatype found in the database table definition can partially match the rule datatype name. It is case sensitive. For example if the 'Int' name is found in the database table definition then it will match the ('Integer',mmExact,ftInteger) rule and will return ftInteger

mmPartialNoCase Same as mmPartial but not case sensitive

mmAll (Default) Tries to determine the datatype using mmExact. If it fails then another try is done using mmPartial If the matching rule failed, the DefaultFieldType is returned (ftUnknown as default).

At design time, a specific editor lets you define the translation rules.

Published property UnicodeEncoding : TSqlitePassUnicodeEncoding Read FUnicodeEncoding Write SetFUnicodeEncoding;

Unicode UTF8 and UTF16 support : The components are now 'unicode friendly' with UTF8 and UTF16 support for SQL statements, Table names, field names, field Data...etc.

The property UnicodeEncoding sets how text, strings or memo are retrieved from the database, encoded, displayed and eventually put back to the database.
This property is independant of the Database.Options.Encoding property which is readonly : you can read or write strings as UTF16 from an UTF8 Database and vice-versa. // TODO : To be tested...
UnicodeEncoding can take one of the following value (ueAuto, ueUTF8, ueUTF16, ueRawText). UnicodeEncoding doesn't really change the way text is encoded. It simply overwrite the field definition for a given table column, changing the original ftString to ftWideString, ftMemo to ftWideMemo or vice-versa.
Whatever delphi or fpc version you use (unless you set UnicodeEncoding to ueRawText), SqlitePassDatabase uses the following rules :

  • A ftString field is ALWAYS encoded as UTF8,

  • A ftMemo field is ALWAYS encoded as UTF8,

  • A ftWideString field is ALWAYS encoded as UTF16,

  • A ftWideMemo field is ALWAYS encoded as UTF16.


The fields conversions applies only for dynamically created fields (not the ones already set at design time). If a custom conversion rule is already set for a given table field, it will always have the priority for the final field definition. .
The UnicodeEncoding default value is ueAuto.

  • ueAuto: For Delphi prior 2009 and fpc = ueUTF8 ; ueUTF16 for Delphi 2009 and up

  • ueUtf8: ftWideString fields are converted to ftString, ftWideMemo are converted to ftMemo,

  • ueUtf16: ftString fields are converted to ftWideString, ftMemo are converted to ftWideMemo,

  • ueRawText: no change is done to the fieldDefs, no encoding is done when retrieving data.



Note : Delphi, prior Delphi 2009, doesn't handle Unicode in classic vcl. In order to write Unicode applications you have to use third part libraries like tnt unicode controls or utf8-vcl (freeware). The delphi 4 demo program uses utf8-vcl which is not very reliable (memory leaks and pointers crashes) but good enougth to test the database components.

Delphi 2009 offers native Unicode support as UTF16. Lazarus-fpc offers native Unicode support as UTF8 : it is a nice alternative to Delphi prior Delphi 2009 to build unicode applications.

See also
TSqlitePassDatabaseDataTypeOptions.CustomFieldDefs
CustomFieldDefs : A collection of custom fielddefs.

Generated by PasDoc 0.11.0 on 2010-03-22 23:38:11