Menu
▾
▴
[Instantobjects-cvscommit] SF.net SVN: instantobjects: [775] trunk/Source/Catalogs
|
From: <fas...@us...> - 2007-05-13 21:20:18
|
Revision: 775
http://svn.sourceforge.net/instantobjects/revision/?rev=775&view=rev
Author: fastbike2
Date: 2007-05-13 14:20:15 -0700 (Sun, 13 May 2007)
Log Message:
-----------
Added new MySQL Catalog
Added Paths:
-----------
trunk/Source/Catalogs/MySQL/
trunk/Source/Catalogs/MySQL/D2007/
trunk/Source/Catalogs/MySQL/D2007/IOMySQLCatalog.dpk
trunk/Source/Catalogs/MySQL/D7/
trunk/Source/Catalogs/MySQL/D7/IOMySQLCatalog.dpk
trunk/Source/Catalogs/MySQL/InstantMySQLCatalog.pas
Added: trunk/Source/Catalogs/MySQL/D2007/IOMySQLCatalog.dpk
===================================================================
--- trunk/Source/Catalogs/MySQL/D2007/IOMySQLCatalog.dpk (rev 0)
+++ trunk/Source/Catalogs/MySQL/D2007/IOMySQLCatalog.dpk 2007-05-13 21:20:15 UTC (rev 775)
@@ -0,0 +1,37 @@
+package IOMySQLCatalog;
+
+{$R *.res}
+{$ALIGN 8}
+{$ASSERTIONS ON}
+{$BOOLEVAL OFF}
+{$DEBUGINFO ON}
+{$EXTENDEDSYNTAX ON}
+{$IMPORTEDDATA ON}
+{$IOCHECKS ON}
+{$LOCALSYMBOLS ON}
+{$LONGSTRINGS ON}
+{$OPENSTRINGS ON}
+{$OPTIMIZATION ON}
+{$OVERFLOWCHECKS OFF}
+{$RANGECHECKS OFF}
+{$REFERENCEINFO ON}
+{$SAFEDIVIDE OFF}
+{$STACKFRAMES OFF}
+{$TYPEDADDRESS ON}
+{$VARSTRINGCHECKS ON}
+{$WRITEABLECONST OFF}
+{$MINENUMSIZE 1}
+{$IMAGEBASE $400000}
+{$DESCRIPTION 'InstantObjects Catalog for MySQL (Delphi 2007)'}
+{$LIBSUFFIX '_D11'}
+{$RUNONLY}
+{$IMPLICITBUILD OFF}
+
+requires
+ rtl,
+ IOCore;
+
+contains
+ InstantMySQLCatalog in '..\InstantMySQLCatalog.pas';
+
+end.
Added: trunk/Source/Catalogs/MySQL/D7/IOMySQLCatalog.dpk
===================================================================
--- trunk/Source/Catalogs/MySQL/D7/IOMySQLCatalog.dpk (rev 0)
+++ trunk/Source/Catalogs/MySQL/D7/IOMySQLCatalog.dpk 2007-05-13 21:20:15 UTC (rev 775)
@@ -0,0 +1,37 @@
+package IOMySQLCatalog;
+
+{$R *.res}
+{$ALIGN 8}
+{$ASSERTIONS ON}
+{$BOOLEVAL OFF}
+{$DEBUGINFO ON}
+{$EXTENDEDSYNTAX ON}
+{$IMPORTEDDATA ON}
+{$IOCHECKS ON}
+{$LOCALSYMBOLS ON}
+{$LONGSTRINGS ON}
+{$OPENSTRINGS ON}
+{$OPTIMIZATION ON}
+{$OVERFLOWCHECKS OFF}
+{$RANGECHECKS OFF}
+{$REFERENCEINFO ON}
+{$SAFEDIVIDE OFF}
+{$STACKFRAMES OFF}
+{$TYPEDADDRESS ON}
+{$VARSTRINGCHECKS ON}
+{$WRITEABLECONST OFF}
+{$MINENUMSIZE 1}
+{$IMAGEBASE $400000}
+{$DESCRIPTION 'InstantObjects Catalog for MySQL'}
+{$LIBSUFFIX '_D7'}
+{$RUNONLY}
+{$IMPLICITBUILD OFF}
+
+requires
+ rtl,
+ IOCore;
+
+contains
+ InstantMySQLCatalog in '..\InstantMySQLCatalog.pas';
+
+end.
Added: trunk/Source/Catalogs/MySQL/InstantMySQLCatalog.pas
===================================================================
--- trunk/Source/Catalogs/MySQL/InstantMySQLCatalog.pas (rev 0)
+++ trunk/Source/Catalogs/MySQL/InstantMySQLCatalog.pas 2007-05-13 21:20:15 UTC (rev 775)
@@ -0,0 +1,342 @@
+(*
+ * InstantObjects DBEvolver Support
+ * MySQL Catalog
+ *)
+
+(* ***** BEGIN LICENSE BLOCK *****
+ * Version: MPL 1.1
+ *
+ * The contents of this file are subject to the Mozilla Public License Version
+ * 1.1 (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ * http://www.mozilla.org/MPL/
+ *
+ * Software distributed under the License is distributed on an "AS IS" basis,
+ * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
+ * for the specific language governing rights and limitations under the
+ * License.
+ *
+ * The Original Code is: InstantObjects DBEvolver Support
+ *
+ * The Initial Developer of the Original Code is: David Moorhouse
+ *
+ * Portions created by the Initial Developer are Copyright (C) 2007
+ * the Initial Developer. All Rights Reserved.
+ *
+ * Contributor(s):
+ *
+ *
+ * ***** END LICENSE BLOCK ***** *)
+
+ // TODO: separate out the MysQL4.x and MYSQl 5.x
+ // TODO: not sure if different features need supporting for different db engine options e.g. MyISAM, InnoDB, etc
+
+unit InstantMySQLCatalog;
+
+{$IFDEF LINUX}
+{$I '../../InstantDefines.inc'}
+{$ELSE}
+{$I '..\..\InstantDefines.inc'}
+{$ENDIF}
+
+interface
+
+uses
+ InstantPersistence, InstantBrokers, InstantMetadata, InstantTypes;
+
+type
+ // A TInstantCatalog that reads catalog information from a MySQL
+ // database. Can be used with a SQL broker that accesses MySQL databases.
+ TInstantMySQLCatalog = class(TInstantSQLBrokerCatalog)
+ private
+ procedure AddFieldMetadatas(TableMetadata: TInstantTableMetadata);
+ procedure AddIndexMetadatas(TableMetadata: TInstantTableMetadata);
+ procedure AddTableMetadatas(TableMetadatas: TInstantTableMetadatas);
+ // Returns True if the TInstantDataType value that matches the supplied
+ // ColumnType. If more than one datatypes apply, alternate data types are
+ // returned in AlternateDataTypes, otherwise AlternateDataTypes is [] on exit.
+ function ColumnTypeToDataType(const ColumnType: string; out DataType:
+ TInstantDataType; out AlternateDataTypes: TInstantDataTypes): Boolean;
+ function GetSelectFieldsSQL(const ATableName: string): string;
+ function GetSelectIndexesSQL(const ATableName: string): string;
+ function GetSelectTablesSQL: string;
+ public
+ procedure InitTableMetadatas(ATableMetadatas: TInstantTableMetadatas);
+ override;
+ end;
+
+implementation
+
+uses
+ SysUtils, Classes, DB, InstantConsts, StrUtils;
+
+{ TInstantMySQLCatalog }
+
+procedure TInstantMySQLCatalog.AddIndexMetadatas(TableMetadata: TInstantTableMetadata);
+var
+ Indexes: TDataSet;
+ IndexMetadata: TInstantIndexMetadata;
+ IndexFieldList: TStrings;
+ KeyName: string;
+begin
+ IndexFieldList := TStringList.Create;
+ Indexes := Broker.AcquireDataSet(GetSelectIndexesSQL(TableMetadata.Name));
+ try
+ Indexes.Open;
+ try
+ while not Indexes.Eof do
+ begin
+ IndexMetadata := TableMetadata.IndexMetadatas.Add;
+ IndexMetadata.Name := Trim(Indexes.FieldByName('Key_name').AsString);
+ IndexMetadata.Options := [];
+ if Pos('PRIMARY', Indexes.FieldByName('Key_name').AsString) = 1 then
+ IndexMetadata.Options := IndexMetadata.Options + [ixPrimary, ixUnique]
+ else if Indexes.FieldByName('Non_unique').AsInteger = 0 then
+ IndexMetadata.Options := IndexMetadata.Options + [ixUnique];
+ { TODO : support other Options? e.g partial indexes, descending}
+ IndexFieldList.Clear;
+ KeyName := Indexes.FieldByName('Key_name').AsString;
+ while (not Indexes.Eof) and (KeyName = Indexes.FieldByName('Key_name').AsString) do
+ begin
+ IndexFieldList.Add(Trim(Indexes.FieldByName('Column_name').AsString));
+ Indexes.Next;
+ end;
+ IndexMetadata.Fields := StringReplace(IndexFieldList.CommaText, ',', ';', [rfReplaceAll]);
+ end;
+ finally
+ Indexes.Close;
+ end;
+ finally
+ IndexFieldList.Free;
+ Broker.ReleaseDataSet(Indexes);
+ end;
+end;
+
+procedure TInstantMySQLCatalog.AddFieldMetadatas(TableMetadata: TInstantTableMetadata);
+ function GetFieldSize(AFieldType: string): Integer;
+ var
+ I, J: Integer;
+ begin
+ Result := 0;
+ I := Pos('(', AFieldType);
+ if I > 0 then begin
+ J := PosEx(',', AFieldType, I);
+ if J = 0 then
+ J := Pos(')', AFieldType);
+ Result := StrToIntDef(Copy(AFieldType, I + 1, J -I - 1), 0);
+ end;
+ end;
+
+var
+ Fields: TDataSet;
+ FieldMetadata: TInstantFieldMetadata;
+ AlternateDataTypes: TInstantDataTypes;
+ FieldMetaDataType: TInstantDataType;
+ FieldTypeName: string;
+begin
+ Fields := Broker.AcquireDataSet(GetSelectFieldsSQL(TableMetadata.Name));
+ try
+ Fields.Open;
+ try
+ while not Fields.Eof do
+ begin
+ FieldMetadata := TableMetadata.FieldMetadatas.Add;
+ FieldMetadata.Name := Trim(Fields.FieldByName('Field').AsString);
+ if ColumnTypeToDataType(
+ Fields.FieldByName('Type').AsString,
+ FieldMetaDataType, AlternateDataTypes) then
+ begin
+ FieldMetadata.DataType := FieldMetaDataType;
+ FieldMetadata.AlternateDataTypes := AlternateDataTypes;
+ FieldMetadata.Options := [];
+ if Fields.FieldByName('Null').AsString <> 'YES' then
+ FieldMetadata.Options := FieldMetadata.Options + [foRequired];
+ if TableMetadata.IndexMetadatas.IsFieldIndexed(FieldMetadata) then
+ FieldMetadata.Options := FieldMetadata.Options + [foIndexed];
+ if FieldMetadata.DataType = dtString then
+ FieldMetadata.Size := GetFieldSize(Fields.FieldByName('Type').AsString);
+ end
+ else
+ DoWarning(Format(SUnsupportedColumnSkipped, [
+ TableMetadata.Name, Fields.FieldByName('Field').AsString,
+ Fields.FieldByName('Type').AsString]));
+ Fields.Next;
+ end;
+ finally
+ Fields.Close;
+ end;
+ finally
+ Broker.ReleaseDataSet(Fields);
+ end;
+end;
+
+procedure TInstantMySQLCatalog.AddTableMetadatas(TableMetadatas: TInstantTableMetadatas);
+// NB: unresolved error
+// MySQL and dbx raise a "Commands out of sync" error when using 2 simultaneous datasets
+// store table names in stringlist to work around this
+var
+ Tables: TDataSet;
+ TableMetadata: TInstantTableMetadata;
+ TableList: TStringList;
+ i: Integer;
+begin
+ TableList := TStringList.Create;
+ Tables := Broker.AcquireDataSet(GetSelectTablesSQL());
+ try
+ Tables.Open;
+ try
+ while not Tables.Eof do
+ begin
+ TableList.Add(Trim(Tables.FieldByName('tables_in_' + Broker.Connector.DatabaseName).AsString));
+ Tables.Next;
+ end;
+ finally
+ Tables.Close;
+ end;
+ for i := 0 to TableList.Count - 1 do
+ begin
+ TableMetadata := TableMetadatas.Add;
+ TableMetadata.Name := TableList[i];
+ // Call AddIndexMetadatas first, so that AddFieldMetadatas can see what
+ // indexes are defined to correctly set the foIndexed option.
+ AddIndexMetadatas(TableMetadata);
+ AddFieldMetadatas(TableMetadata);
+ end;
+ finally
+ Broker.ReleaseDataSet(Tables);
+ TableList.Free;
+ end;
+end;
+
+function TInstantMySQLCatalog.ColumnTypeToDataType(const ColumnType: string;
+ out DataType: TInstantDataType; out AlternateDataTypes: TInstantDataTypes):
+ Boolean;
+begin
+// NB: datatypes at http://dev.mysql.com/doc/refman/4.1/en/data-types.html
+ AlternateDataTypes := [];
+ Result := True;
+
+ if Pos('bool', ColumnType) = 1 then
+ DataType := dtBoolean
+ else if Pos('tinyint', ColumnType) = 1 then begin
+ DataType := dtBoolean;
+ Include(AlternateDataTypes, dtInteger);
+ end
+ else if Pos('bigint', ColumnType) = 1 then // prevent "bigint" from matching "int" i.e Int64
+ Result := False
+ else if Pos('int', ColumnType) > 0 then // covers SMALLINT, MEDINT, INT and INTEGER
+ DataType := dtInteger
+ else if Pos('float', ColumnType) = 1 then
+ DataType := dtFloat
+ else if Pos('decimal', ColumnType) = 1 then
+ DataType := dtCurrency
+ else if Pos('char', ColumnType) = 1 then // MySQL stores varchar(3) or smaller as char
+ DataType := dtString
+ else if Pos('varchar', ColumnType) = 1 then
+ DataType := dtString
+ else if Pos('text', ColumnType) > 0 then begin // i.e. text, tinytext, mediumtext or longtext
+ DataType := dtMemo;
+ Include(AlternateDataTypes, dtString); // IO strings longer than 255 stored as MySQL TEXT
+ end
+ else if Pos('blob', ColumnType) > 0 then // blob, tinyblob, mediumblob, longblob
+ DataType := dtBlob
+ else if Pos('datetime', ColumnType) = 1 then
+ DataType := dtDateTime
+ else if Pos('date', ColumnType) = 1 then
+ DataType := dtDate
+ // need the following to prevent "timestamp" from matching with "time"
+ else if Pos('timestamp', ColumnType) = 1 then // seconds since UNIX epoch - not valid for IO
+ Result := False
+ else if Pos('time', ColumnType) = 1 then
+ DataType := dtTime
+ else
+ Result := False;
+end;
+
+function TInstantMySQLCatalog.GetSelectFieldsSQL(
+ const ATableName: string): string;
+begin
+ Result := 'DESCRIBE ' + ATableName; // synonym for MySQL "SHOW COLUMNS FROM"
+end;
+
+function TInstantMySQLCatalog.GetSelectIndexesSQL(const ATableName: string): string;
+begin
+ Result := 'SHOW INDEX FROM ' + ATableName;
+end;
+
+function TInstantMySQLCatalog.GetSelectTablesSQL: string;
+begin
+ Result := 'SHOW TABLES';
+end;
+
+procedure TInstantMySQLCatalog.InitTableMetadatas(ATableMetadatas: TInstantTableMetadatas);
+begin
+ ATableMetadatas.Clear;
+ AddTableMetadatas(ATableMetadatas);
+end;
+
+
+{ A. test table definition
+
+
+CREATE TABLE Test
+(
+ Class VARCHAR(32) NOT NULL,
+ Id VARCHAR(32) NOT NULL,
+ UpdateCount INTEGER,
+ StringAttr VARCHAR(255),
+ StringAttr2 VARCHAR(3), // MySQL stores this as CHAR(3) !!
+ StringAttr3 VARCHAR(256), // MySQL stores this as TEXT
+ BlobAttr BLOB,
+ BooleanAttr SMALLINT,
+ CurrencyAttr DECIMAL(14,4),
+ DateTimeAttr TIMESTAMP,
+ FloatAttr FLOAT,
+ GraphicAttr BLOB,
+ IntegerAttr INTEGER,
+ MemoAttr TEXT,
+ ReferenceAttributeClass VARCHAR(32),
+ ReferenceAttributeId VARCHAR(32),
+ EmbeddedPartsAtttribute BLOB,
+ EmbeddedPartAttribute BLOB,
+ ExternalPartAttributeClass VARCHAR(32),
+ ExternalPartAttributeId VARCHAR(32),
+ EmbeddedReferencesAtttribute BLOB,
+ DateAttr DATE,
+ TimeAttr TIME,
+ PRIMARY KEY (Class, Id)
+);
+
+
+B. Result of "GetSelectFieldsSQL" method - run against Win32 MySQL 4.1.14 with InnoDB engine
+
+Field Type Null Key Default Extra
+===== ==== ==== === ======= =====
+Class varchar(32) PRI
+Id varchar(32) PRI
+UpdateCount int(11) YES
+StringAttr varchar(255) YES
+StringAttr2 char(3) YES
+StringAttr3 text YES
+BlobAttr blob YES
+BooleanAttr smallint(6) YES
+CurrencyAttr decimal(14,4) YES
+DateTimeAttr datetime YES
+FloatAttr double YES
+GraphicAttr blob YES
+IntegerAttr int(11) YES
+MemoAttr text YES
+ReferenceAttributeClass varchar(32) YES
+ReferenceAttributeId varchar(32) YES
+EmbeddedPartsAtttribute blob YES
+EmbeddedPartAttribute blob YES
+ExternalPartAttributeClass varchar(32) YES
+ExternalPartAttributeId varchar(32) YES
+EmbeddedReferencesAtttribute blob YES
+DateAttr date YES
+TimeAttr time YES
+
+}
+
+
+end.
|
