unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, DBAccess, MyAccess, MemDS;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;
    DBGrid1: TDBGrid;
    MyConnection1: TMyConnection;
    MyQuery1: TMyQuery;
    MyDataSource1: TMyDataSource;
    procedure Button1Click(Sender: TObject);
    procedure preparedata;
  private
    { Private declarations }
  public
  end;

var
  Form1: TForm1;
  b: array of integer; //bad
  g: array of integer; //good

implementation

{$R *.dfm}


procedure saveheader(fn: string);
var
 f: Text;
begin
 AssignFile(f,fn);
 rewrite(f);
 writeln(f,'-- MySQL dump 10.11');
 writeln(f,'--');
 writeln(f,'-- Host: localhost    Database: library');
 writeln(f,'-- ------------------------------------------------------');
 writeln(f,'-- Server version	5.0.87-log');
 writeln(f,'');
 writeln(f,'/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;');
 writeln(f,'/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;');
 writeln(f,'/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;');
 writeln(f,'/*!40101 SET NAMES UTF8 */;');
 writeln(f,'/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;');
 writeln(f,'/*!40103 SET TIME_ZONE=''+00:00'' */;');
 writeln(f,'/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;');
 writeln(f,'/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;');
 writeln(f,'/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */;');
 writeln(f,'/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;');
 writeln(f,'');
 writeln(f,'--');
 writeln(f,'-- Table structure for table `libavtoraliase`');
 writeln(f,'--');
 writeln(f,'');
 writeln(f,'DROP TABLE IF EXISTS `libavtoraliase`;');
 writeln(f,'/*!40101 SET @saved_cs_client     = @@character_set_client */;');
 writeln(f,'/*!40101 SET character_set_client = utf8 */;');
 writeln(f,'CREATE TABLE `libavtoraliase` (');
 writeln(f,'  `AliaseId` int(11) NOT NULL auto_increment,');
 writeln(f,'  `BadId` int(11) NOT NULL default ''0'',');
 writeln(f,'  `GoodId` int(11) NOT NULL default ''0'',');
 writeln(f,'  PRIMARY KEY  (`AliaseId`),');
 writeln(f,'  KEY `u` (`BadId`,`GoodId`),');
 writeln(f,'  KEY `BadId` (`BadId`),');
 writeln(f,'  KEY `GoodId` (`GoodId`)');
 writeln(f,') ENGINE=MyISAM AUTO_INCREMENT=13189 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=''Таблица замены авторов'';');
 writeln(f,'/*!40101 SET character_set_client = @saved_cs_client */;');
 writeln(f,'');
 writeln(f,'--');
 writeln(f,'-- Dumping data for table `libavtoraliase`');
 writeln(f,'--');
 writeln(f,'LOCK TABLES `libavtoraliase` WRITE;');
 writeln(f,'/*!40000 ALTER TABLE `libavtoraliase` DISABLE KEYS */;');
 closefile(f);
end;

procedure savefootter(fn: string);
var
 f: Text;
begin
 AssignFile(f,fn);
 append(f);
 writeln(f,'/*!40000 ALTER TABLE `libavtoraliase` ENABLE KEYS */;');
 writeln(f,'UNLOCK TABLES;');
 writeln(f,'/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;');
 writeln(f,'');
 writeln(f,'/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;');
 writeln(f,'/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;');
 writeln(f,'/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;');
 writeln(f,'/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;');
 writeln(f,'/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;');
 writeln(f,'/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;');
 writeln(f,'/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;');
 writeln(f,'');
 writeln(f,'-- Dump completed on 2010-08-31 19:50:02');
 closefile(f);
end;

procedure savedata(fn: string);
var
 f: Text;
  I: Integer;
begin
 AssignFile(f,fn);
 append(f);
 write(f,'INSERT INTO `libavtoraliase` VALUES ');
 for I := 0 to high(g) do
  begin
   if g[i]<0
    then continue;
   write(f,'('+inttostr(i+1)+','+inttostr(b[i])+','+inttostr(g[i])+')');
   if i<>high(g) then write(f,',') else write(f,';');
  end;
 writeln(f,'');
 closefile(f);
end;

procedure TForm1.preparedata;
var
 i,j,m,n,k: integer;
 bb: boolean;
 q: TMyQuery;
begin
 k:=0;
 m:=0;
 n:=0;
 setlength(b,0);
 setlength(g,0);
 MyConnection1.Open;
 q:=TMyQuery.Create(nil);
 q.Connection:=MyConnection1;
 q.SQL.Text:='select * from libavtoraliase';
 q.Open;
 q.First;
 while not q.Eof do
  begin
   setlength(b,length(b)+1);
   setlength(g,length(g)+1);
   b[high(b)]:=q.FieldByName('badid').AsInteger;
   g[high(g)]:=q.FieldByName('goodid').AsInteger;
   q.Next;
  end;
 repeat
  inc(n);
  bb:=true;
  for j := 0 to high(b) do
   for i := 0 to high(b) do
    if g[j]=b[i]
     then
      begin
       k:=k-2;
       if i=j then
        begin
         g[j]:=k;
         b[j]:=k+1;
        end;

       bb:=false;
       inc(m);
       g[j]:=g[i];
      end;
 until bb;
 q.Close;
 q.Free;
 saveheader('111.sql');
 savedata('111.sql');
 savefootter('111.sql');
 setlength(b,0);
 setlength(g,0);
 memo1.Lines.Add('Проходов: '+inttostr(n)+' - Замен:'+inttostr(m))
end;

procedure TForm1.Button1Click(Sender: TObject);
var t1: tdatetime;
begin
 t1:=now;
 preparedata;
 t1:=now-t1;
 memo1.Lines.Add(FormatDateTime('SS:ZZZ',t1));
end;

end.
