damysql.pas

MYSQL 连接控件 MYSQL 连接控件

end; {procedure, ContainsSQLFunctionCall}

{------------------------------------------------------------------------------}
{ TdaMySQLSession.BuildFunctionList }

procedure TdaMySQLSession.BuildFunctionList;
begin
  {aggregate functions}
  FFunctions.CommaText := 'ABS(,ACOS(,ADDDATE(,ASCII(,ASIN(,ATAN(,ATAN(,AVG(,BENCHMARK(,BIN(,BIT_AND(,BIT_COUNT(,BIT_OR(,CEILING(,CHAR(,CHAR_LENGTH(,CHARACTER_LENGTH(,'+
	'COALESCE(,CONCAT(,CONV(,COS(,COT(,COUNT(,CURDATE(,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURTIME(,DATABASE(,DATE_ADD(,DATE_FORMAT(,'+
	'DATE_SUB(,DAYNAME(,DAYOFMONTH(,DAYOFWEEK(,DAYOFYEAR(,DECODE(,DEGREES(,ELT(,ENCODE(,ENCRYPT(,EXP(,EXPORT_SET(,EXTRACT(,FIELD(,'+
	'FIND_IN_SET(,FLOOR(,FORMAT(,FROM_DAYS(,FROM_UNIXTIME(,GET_LOCK(,GREATEST(,HEX(,HOUR(,IF(,IFNULL(,INSERT(,INSTR(,INTERVAL(,ISNULL(,LAST_INSERT_ID(,'+
	'LCASE(,LEAST(,LEFT(,LENGTH(,LOCATE(,LOG(,LOWER(,LPAD(,LTRIM(,MAKE_SET(,MAX(,MD(,MID(,MIN(,MINUTE(,MOD(,MONTH(,MONTHNAME(,NOW(,OCT(,OCTET_LENGTH(,ORD(,PASSWORD(,PERIOD_ADD(,'+
	'PERIOD_DIFF(,PI(,POSITION(,POW(,POWER(,QUARTER(,RADIANS(,RAND(,RELEASE_LOCK(,REPEAT(,REPLACE(,REVERSE(,RIGHT(,ROUND(,RPAD(,RTRIM(,SEC_TO_TIME(,'+
	'SECOND(,SESSION_USER(,SIGN(,SIN(,SOUNDEX(,SPACE(,SQRT(,STD(,STDDEV(,STRCMP(,SUBDATE(,SUBSTRING(,SUBSTRING_INDEX(,SUM(,SYSDATE(,SYSTEM_USER(,'+
	'TAN(,TIME_FORMAT(,TIME_TO_SEC(,TO_DAYS(,TRIM(,TRUNCATE(,UCASE(,UNIX_TIMESTAMP(,UPPER(,USER(,VERSION(,WEEK(,WEEKDAY(,YEAR(';
end; {procedure, BuildFunctionList}

{------------------------------------------------------------------------------}
{ TdaMySQLSession.GetSearchCriteriaDateFormat }

function TdaMySQLSession.GetSearchCriteriaDateFormat(aDatabaseType: TppDatabaseType; const aDatabaseName: String): String;
begin
  Result := 'YYYY-MM-DD';
end; {function, GetSearchCriteriaDateFormat}

{------------------------------------------------------------------------------}
{ TdaMySQLSession.GetSearchCriteriaTimeFormat }

function TdaMySQLSession.GetSearchCriteriaTimeFormat(aDatabaseType: TppDatabaseType; const aDatabaseName: String): String;
begin
  Result := 'HH:MM:SS';
end; {function, GetSearchCriteriaTimeFormat}

{******************************************************************************
 *
 ** I B O   D A T A S E T
 *
{******************************************************************************}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.Create }

constructor TdaMySQLDataset.Create(aOwner: TComponent);
begin

  inherited Create(aOwner);
  FQuery := nil;
end; {constructor, Create}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.Destroy }

destructor TdaMySQLDataset.Destroy;
begin

	if Assigned(FQuery) then FQuery.Free;

  inherited Destroy;

end; {destructor, Destroy}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.ClassDescription }

class function TdaMySQLDataset.ClassDescription: String;
begin
  Result := 'MySQLDataset';
end; {class function, ClassDescription}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.GetActive }

function TdaMySQLDataset.GetActive: Boolean;
begin
  Result := GetQuery.Active;
end; {function, GetActive}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.SetActive }

procedure TdaMySQLDataset.SetActive(Value: Boolean);
begin
  GetQuery.Active := Value;
end; {procedure, SetActive}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.GetQuery }

function TdaMySQLDataset.GetQuery: TMySQLDataset;
begin
  if not Assigned(FQuery) then FQuery := TMySQLDataset.Create(Self);

  //Antonie [2001/01/31]
  FQuery.Server := GetMySQLDatabaseForName(DatabaseName);
  FQuery.AllFieldValues := false;
  Result := FQuery;
end; {procedure, GetQuery}


function  TdaMySQLDataset.GetMySQLDatabaseForName(const aDatabaseName: string): TMySQLServer;
begin
	if Assigned(Owner) and (Owner is TdaMetaData) and Assigned(TdaMetaData(Owner).Session) then
  	Result := TdaMySQLSession(TdaMetaData(Owner).Session).GetMySQLDatabaseForName(aDatabaseName)
  else if (Owner is TdaSQL) and Assigned(TdaSQL(Owner).Session) then
  	Result := TdaMySQLSession(TdaSQL(Owner).Session).GetMySQLDatabaseForName(aDatabaseName)
  else
  	Result := nil;
end;

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.SetDatabaseName }

procedure TdaMySQLDataset.SetDatabaseName(const aDatabaseName: String);
begin
  inherited SetDatabaseName(aDatabaseName);
  {get database for name}
  GetQuery;
  FQuery.Server := GetMySQLDatabaseForName(aDatabaseName);
end; {procedure, SetDatabaseName}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.SetDataName }

procedure TdaMySQLDataset.SetDataName(const aDataName: String);
begin
  inherited SetDataName(aDataName);
  {construct an SQL statment that returns an empty result set,
   this is used to get the field information }
  GetQuery;
//  FQuery.SQL.Text := 'SELECT * FROM ' + FQuery.Server.FormatIdentifier(aDataName)+' WHERE ''c'' <> ''c'' ';
  FQuery.SQL.Text := 'SELECT * FROM ' + aDataName + ' WHERE ''c'' <> ''c'' ';
end; {procedure, SetDataName}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.BuildFieldList }

procedure TdaMySQLDataset.BuildFieldList;
var
  liIndex: Integer;
  lQueryMySQLField : TFieldInfo;
  lQueryField: TField;
  lField: TppField;
begin
  inherited BuildFieldList;

  {set table to active}
  if not(GetQuery.Active) then FQuery.Active := True;

  {create TppField objects for each field in the table}
  for liIndex := 0 to FQuery.FieldCount - 1 do begin
  	lQueryMySQLField := TFieldInfo(FQuery.MySQLFields.Objects[liIndex]);
  	lQueryField := FQuery.Fields[liIndex];
     
     lField := TppField.Create(nil);
     lField.Alignment := lQueryField.Alignment;
     lField.DataType  := ppConvertFieldType(lQueryField.DataType);
     lField.DisplayWidth := lQueryField.DisplayWidth;
     lField.FieldAlias := lQueryField.FieldName;
     lField.FieldName := lQueryMySQLField.FieldName;
     lField.FieldLength := lQueryField.Size;
     lField.Linkable := lQueryField.FieldKind=fkData;
     lField.Selectable := lQueryField.FieldKind=fkData;
     lField.Searchable := lQueryField.FieldKind=fkData;
     lField.Sortable := lQueryField.FieldKind=fkData;
     lField.TableAlias := lQueryMySQLField.FieldTable;
     lField.TableName := lQueryMySQLField.FieldTable;

{     lField.TableName    := DataName;
     lField.FieldName    := lQueryField.FieldName;
     lField.FieldAlias := lQueryField.DisplayLabel;
     lField.DataType     := ppConvertFieldType(lQueryField.DataType);
     lField.DisplayWidth := lQueryField.DisplayWidth; }

     AddField(lField);
  end;
end; {function, BuildFieldList}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.GetFieldNamesForSQL }

procedure TdaMySQLDataset.GetFieldNamesForSQL(aList: TStrings; aSQL: TStrings);
var
  lQuery: TMySQLDataset;
begin
  aList.Clear;

  {create a temporary IBO query}
  lQuery := TMySQLDataset.Create(Self);

  {set the database and SQL properties}
  lQuery.Server := GetMySQLDatabaseForName(DatabaseName);
  lQuery.AllFieldValues := false;
  lQuery.SQL := aSQL;

  {get the field names}
  lQuery.GetFieldNames(aList);

  lQuery.Free;
end; {procedure, GetFieldNamesForSQL}

{------------------------------------------------------------------------------}
{ TdaMySQLDataset.GetFieldsForSQL }

procedure TdaMySQLDataset.GetFieldsForSQL(aList: TList; aSQL: TStrings);
var
  lQuery: TMySQLDataset;
  lQueryField: TField;
  lQueryMySQLField: TFieldInfo;
  lField: TppField;
  liIndex: Integer;
begin
  aList.Clear;

  {create a temporary IBO query}
  lQuery := TMySQLDataset.Create(Self);
  lQuery.AllFieldValues := false;

  {assign databae and SQL properties}
  lQuery.Server := GetMySQLDatabaseForName(DatabaseName);
  lQuery.SQL := aSQL;

  {set query to active}
  lQuery.Active := True;

  {create a TppField object for each field in the query}
  for liIndex := 0 to lQuery.FieldCount - 1 do
    begin
      lQueryMySQLField := TFieldInfo(lQuery.MySQLFields.Objects[liIndex]);
      lQueryField := lQuery.Fields[liIndex];
      lField := TppField.Create(nil);
      lField.Alignment := lQueryField.Alignment;
      lField.DataType  := ppConvertFieldType(lQueryField.DataType);
      lField.DisplayWidth := lQueryField.DisplayWidth;
      lField.FieldAlias := lQueryField.FieldName;
      lField.FieldName := lQueryMySQLField.FieldName;
      lField.FieldLength := lQueryField.Size;
      lField.Linkable := lQueryField.FieldKind=fkData;
      lField.Selectable := lQueryField.FieldKind=fkData;
      lField.Searchable := lQueryField.FieldKind=fkData;
      lField.Sortable := lQueryField.FieldKind=fkData;
      lField.TableAlias := lQueryMySQLField.FieldTable;
      lField.TableName := lQueryMySQLField.FieldTable;
      aList.Add(lField);
    end;

  lQuery.Free;
end; {procedure, GetFieldsForSQL}


{******************************************************************************
 *
 ** Q U E R Y   D A T A V I E W
 *
{******************************************************************************}

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.Create }

constructor TdaMySQLQueryDataView.Create(aOwner: TComponent);
begin
  inherited Create(aOwner);

  {notes: 1. must use ChildQuery, ChildDataSource, ChildPipeline etc.
          2. use Self as owner for Query, DataSource etc.
          3. do NOT assign a Name }

  FQuery := TdaChildMySQLQuery.Create(Self);
  //Antonie 2001/02/01 line below
  FQuery.Server := GetMySQLDatabaseForName(SQL.DatabaseName);
  FQuery.AllFieldValues := false;

  FDataSource := TppChildDataSource.Create(Self);
  FDataSource.DataSet := FQuery;
end; {constructor, Create}

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.Destroy }

destructor TdaMySQLQueryDataView.Destroy;
begin
  FDataSource.Free;
  FQuery.Free;
  inherited Destroy;
end; {destructor, Destroy}

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.SessionClass }

class function TdaMySQLQueryDataView.SessionClass: TClass;
begin
  Result := TdaMySQLSession;
end; {class function, SessionClass}

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.PreviewFormClass }

class function TdaMySQLQueryDataView.PreviewFormClass: TFormClass;
begin
  Result := TFormClass(GetClass('TdaPreviewDataDialog'));
end; {class function, PreviewFormClass}

{class function TdaMySQLQueryDataView.SQLClass: TdaSQLClass;
begin
  Result := TdaMySQL;
end;}

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.Loaded }

procedure TdaMySQLQueryDataView.Loaded;
begin
  inherited Loaded;
//  UpdateQueryObject;
end; {procedure, Loaded}

function  TdaMySQLQueryDataView.GetMySQLDatabaseForName(const aDatabaseName: string): TMySQLServer;
begin
	if Assigned(Session) then
  	Result := TdaMySQLSession(Session).GetMySQLDatabaseForName(aDatabaseName)
  else
  	Result := nil;
end;

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.ConnectPipelinesToData }

procedure TdaMySQLQueryDataView.ConnectPipelinesToData;
begin
  if DataPipelineCount = 0 then Exit;

  {need to reconnect here}
  TppDBPipeline(DataPipelines[0]).DataSource := FDataSource;
//  UpdateQueryObject;
end; {procedure, ConnectPipelinesToData}

{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.Init }

procedure TdaMySQLQueryDataView.Init;
var
  lDataPipeline: TppChildDBPipeline;
begin
  inherited Init;

  if DataPipelineCount > 0 then Exit;

  {note: DataView's owner must own the DataPipeline }
  lDataPipeline := TppChildDBPipeline(ppComponentCreate(Self, TppChildDBPipeline));
  lDataPipeline.DataSource := FDataSource;

  lDataPipeline.AutoCreateFields := False;

  {add DataPipeline to the dataview }
  lDataPipeline.DataView := Self;
end; {procedure, Init}


{------------------------------------------------------------------------------}
{ TdaMySQLQueryDataView.SQLChanged }

procedure TdaMySQLQueryDataView.SQLChanged;
begin

  if FQuery.Active then FQuery.Close;
  FQuery.Server := nil;
  FQuery.Server := GetMySQLDatabaseForName(SQL.DatabaseName);
  FQuery.SQL := SQL.MagicSQLText;
end; {procedure, WizardCompleted}

{******************************************************************************
 *
 ** P R O C E D U R E S   A N D   F U N C T I O N S
 *
{******************************************************************************}

initialization
  {register the MySQL descendant classes}
  RegisterClasses([TdaChildMySQLQuery, TdaChildMySQLTable]);
  RegisterClasses([TdaChildMySQLQuery, TdaChildMySQLTable]);

  {register DADE descendant session, dataset, dataview}
  daRegisterSession(TdaMySQLSession);
  daRegisterDataSet(TdaMySQLDataset);
  daRegisterDataView(TdaMySQLQueryDataView);

finalization
  {unregister the IBO descendant classes}
  UnRegisterClasses([TdaChildMySQLQuery, TdaChildMySQLTable]);
  UnRegisterClasses([TppChildMySQLPipeline]);

  {unregister DADE descendant the session, dataset, dataview}
  daUnRegisterSession(TdaMySQLSession);
  daUnRegisterDataSet(TdaMySQLDataset);
  daUnRegisterDataView(TdaMySQLQueryDataView);
end.