Indice - reindexando via table e sql |
Top Previous Next |
Reindexar tabelas via Table ***************************
{ 1) If using more than one field, separate field names with a semi-colon as
AddIndex requires it in that format (eg., 'Field1;Field2;Field3')
2) TIndexOptions are being used from DB unit. TIndexOptions are defined as follows:
TIndexOptions = set of (ixPrimary, ixUnique, ixDescending,
ixExpression, ixCaseInsensitive)
===================================================================================}
procedure IndexTbl(dbName, {Database Name to connect to} tblName, {Table Name} idxName, {Name of Index} fldName : String; {List of Fields to index on} idxOpts : TIndexOptions); {Index Options set} var tbl : TTable;
begin tbl := TTable.Create(Application); with tbl do begin Active := False; DatabaseName := dbName; TableName := tblName; AddIndex(idxName, fldName, idxOpts); Free; end; end;
Reindexar tabelas via SQL *************************
procedure SQLIndexTbl(dbName, tblName, idxName, fldName : String); var sqlIdx : TQuery; fldStr, buf, idxStr : String;
begin idxStr := ''; fldStr := fldName;
{Parse the field string. If we find a semicolon, then we know we've reached a delimiter. After we get the field name, if there's a space, tack on the standard SQL naming syntax and preceed the field name with table name (eg. "MyTable"."My Field Name")}
while Pos(';', fldStr) > 0 do begin buf := Copy(fldStr, 0, Pos(';', fldStr) - 1); if (Pos(' ', buf) > 0) then buf := '"' + tblName + '"."' + buf + '"'; fldStr := Copy(fldStr, Pos(';', fldStr) + 1, Length(fldStr) - Pos(';', fldStr) + 1); idxStr := idxStr + buf + ',' end;
{Have to repeat this step at the end because there will always be a remainder in buf}
buf := fldStr; if (Pos(' ', buf) > 0) then buf := '"' + tblName + '"."' + buf + '"';
idxStr := idxStr + buf;
{Now, create the TQuery and execute it.}
sqlIdx := TQuery.Create(Application); with sqlIdx do begin Active := False; DatabaseName := dbName; Sql.Clear; Sql.Add('CREATE INDEX ' + idxName + ' ON ":' + dbName + ':' + tblName + '" (' + idxStr + ')'); SQL.SaveToFile('creatidx.sql'); try try ExecSql; finally Free; end; except Abort; end; end; end;
|