unit uDataBaseUnitServer;

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait, Data.DB,
  FireDAC.Comp.Client, FireDAC.Phys.MySQLDef, FireDAC.Phys.MySQL,Vcl.Dialogs, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Comp.DataSet,System.IOUtils, System.Types,uDataBaseInitialTableFilling,

  Vcl.Forms,

  //myUnits

  uFileTypeServer
  ;


type
  TDataBaseUnitServer = class(TDataModule)
    FDConnection: TFDConnection;
    FDPhysMySQLDriverLink: TFDPhysMySQLDriverLink;
    qSelectFilesFromFolder: TFDQuery;
    qInsertFolderOrFile: TFDQuery;
    qDelete: TFDQuery;
    qUpdateIdParent: TFDQuery;
    qSelectWhereParent: TFDQuery;
    qUpdateUsers_id: TFDQuery;
    qGetLastInsertedID: TFDQuery;
    qInsertFolderOrFileNullUsers_id: TFDQuery;
    qSelectFileType: TFDQuery;
    qInsertFileType: TFDQuery;
    qSelectFileTypeWhere: TFDQuery;
    qSelectChildren: TFDQuery;
    qGetAbsAdressByID: TFDQuery;
    qIsFolder: TFDQuery;
    qSelectFromFoldersAndFilesWhereID: TFDQuery;
    qSelectFromFoldersAndFilesWhereRelPath: TFDQuery;
    qSelectFileExtension: TFDQuery;
    qInsertFileExtension: TFDQuery;
    qSelectFileExtensions: TFDQuery;
    qSelectFileExtensionsByType: TFDQuery;

  private
    { Private declarations }

    FDataBase:string;
    FPassword:string;
    FUser_Name:string;
    FPort:string;
    FServer:string;
    FCharacterSet:string;
    FFileTypeServer:TFileTypeServer;
    FDirsForDB:TStringList;
    FDataBaseInitialTableFilling:TDataBaseInitialTableFillling;

    
    
    function IsFileOrFolderAlreadyInDB(idParent: Integer; nameToCompare: string; var idParentNext:integer): boolean; overload;
    function IsFileOrFolderAlreadyInDB(idParent:Integer; nameToCompare:string):boolean; overload;



    procedure ConnectQueries;


    function GetLastInsertedID: integer;

    procedure AddValueToSDA(var SDA: TStringDynArray; Value: string);
    




  public
    { Public declarations }

    constructor Create(AOwner: TComponent); override;
    destructor  Destroy; override;

    function Connect:Boolean;

    function GetFileSize(AFilePath: String): Int64;
    
    procedure SynchronizeDirWithDataBase(dir: string;

    var RelDir:string;
    var SDAResult: TStringDynArray;
    idParent: integer;
    idUser:integer

    );


    function GetAbsAdressByID(AID: integer): string;
    {< will get AbsAdress from ID}


    function IsFolder(Aid: integer): boolean;
    {< checks if Current Record Folder or not}



   
    procedure DeleteRecord(AID:integer);

    property DataBase:string read FDataBase write FDataBase;
    property Password:string read FPassword write FPassword;
    property User_Name:string read FUser_Name write FUser_Name;
    property Port:string read FPort write FPort;
    property Server:string read FServer write FServer;
    property CharacterSet:string read FCharacterSet write FCharacterSet;


    property FileTypeServer:TFileTypeServer read FFileTypeServer write FFileTypeServer;
    
  end;

//var
//  DataBaseUnitServer: TDataBaseUnitServer;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}


uses uHTTPServer_Main;

var HTTPServer_Main_UnitVar:TfHTTPServer;


{ TDataBaseUnitServer }

function TDataBaseUnitServer.Connect: Boolean;
var oParams:TStrings;  ErrorInfo:string;

begin



 oParams:=TStringList.Create;


 try

  oParams.Add('DataBase='+FDataBase);
  oParams.Add('Password='+FPassword);
  oParams.Add('User_Name='+FUser_Name);
  oParams.Add('Port='+FPort);
  oParams.Add('Server='+FServer);
  oParams.Add('CharacterSet='+FCharacterSet);

  FDConnection.Params.Assign(oParams);
  FDConnection.DriverName:='MySQL';


   //Пробуем подключиться

   try

   FDConnection.Connected:=true;

   if FDConnection.Connected then

     begin
     Result:=true;

//     showmessage('Connected');
     end else Result:=false;



   except



   on E: EFDDBEngineException do

    case E.Kind of
    ekUserPwdInvalid:


       // user name or password are incorrect
       raise Exception.Create('DBConnection Error. User name or password are incorrect'+
       #13#10+#13#10+E.ClassName+' поднята ошибка, с сообщением : '+E.Message);



    ekUserPwdExpired:


      raise Exception.Create('DBConnection Error. User password is expired'
    +#13#10+#13#10+E.ClassName+' поднята ошибка, с сообщением : '+E.Message);



    ekServerGone:



      raise Exception.Create('DBConnection Error. DBMS is not accessible due to some reason'
      +#13#10+#13#10+E.ClassName+' поднята ошибка, с сообщением : '+E.Message);




    else                // other issues



      raise Exception.Create('DBConnection Error. UnknownMistake'
      +#13#10+#13#10+E.ClassName+' поднята ошибка, с сообщением : '+E.Message);





    end;

    on E : Exception do

      raise Exception.Create(
      E.ClassName+' поднята ошибка, с сообщением : '+#13#10+#13#10+E.Message
      );





   end;




 finally

  FreeAndNil(oParams);

 end;


end;

constructor TDataBaseUnitServer.Create(AOwner: TComponent);
begin
  inherited;

  // values by default - they can be overriden from outside
    FDataBase:='coffeetest_db';
    FPassword:='masterkey';
    FUser_Name:='root';
    FPort:='3306';
    FServer:='localhost';
    FCharacterSet:='utf8';

    Connect;
    ConnectQueries;


    FFileTypeServer:=TFileTypeServer.Create(Self);
    FDirsForDB:=TStringList.Create;


    FDataBaseInitialTableFilling:=TDataBaseInitialTableFillling.Create(Self);
    FDataBaseInitialTableFilling.InitialTablesFilling;
    FreeAndNil(FDataBaseInitialTableFilling);

    if (AOwner is TfHTTPServer) then HTTPServer_Main_UnitVar:=(AOwner as TfHTTPServer);
    
    

end;


procedure TDataBaseUnitServer.DeleteRecord(AID:integer);
begin

with qDelete do begin

Params.ParamValues['id']:=AID;
ExecSQL;

end;


end;



destructor TDataBaseUnitServer.Destroy;
begin

 FDirsForDB.Free;

  inherited;
end;















procedure TDataBaseUnitServer.ConnectQueries;
var
  I: Integer;
begin

  //Connecting FDTable
  for I := 0 to Self.ComponentCount - 1 do
    if (Self.Components[i] is TFDQuery) then
      (Self.Components[i] as TFDQuery).Connection :=FDConnection;



end;



function TDataBaseUnitServer.GetLastInsertedID:integer;
begin

      Result:=-1;

      with qGetLastInsertedID do begin

      Open();

      Result:=FieldByName('lastInsertedID').AsInteger;

      Close();

      end;



end;


function TDataBaseUnitServer.
IsFileOrFolderAlreadyInDB(idParent:Integer; nameToCompare:string; var idParentNext:integer):boolean;

begin
  //

  Result:=false;
  idParentNext:=-2;


  with qSelectWhereParent do begin

  Params.ParamValues['idParent']:=idParent;

  Open();

   while not EOF do begin

    if nameToCompare=FieldByName('name').AsString then

     begin
     idParentNext:=FieldByName('id').AsInteger;
     Result:=true;
     Break;
     end;



   Next();
   end;

  Close();
  end;


end;



function TDataBaseUnitServer.
IsFileOrFolderAlreadyInDB(idParent:Integer; nameToCompare:string):boolean;

begin
  //

  Result:=false;



  with qSelectWhereParent do begin

  Params.ParamValues['idParent']:=idParent;

  Open();

   while not EOF do begin

    if nameToCompare=FieldByName('name').AsString then

     begin
     Result:=true;
     Break;
     end;



   Next();
   end;

  Close();
  end;


end;














procedure TDataBaseUnitServer.

           SynchronizeDirWithDataBase(

               dir: string;
               var RelDir:string;
               var SDAResult:TStringDynArray;
               idParent:integer;
               idUser:integer

               );

var SDAFiles:TStringDynArray;
    SDADirs:TStringDynArray;
    SDAIDParentNext:TStringDynArray;


    i: Integer;
    SearchOption: TSearchOption;
    SplittedString: TArray<String>;
    idNextParent: integer;
    idParentNext: integer;
    idUserLocal:integer;
    RelFilePath: string;
    absPath:string;
  StartIndex: integer;

  NameToCompare:String;

begin

idUserLocal:=idUser;

//giving to this function following root dir
//abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public
// as a dir param
{
 abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public
 rel public

 abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public\files\
 rel public\files

 abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public\files\UserID\
 rel public\files\UserID\

 abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public\files\UserID\2016\
 rel public\files\UserID\2016\

 abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public\files\UserID\2016\28\
 rel public\files\UserID\2016\28\

 abs C:\DELPHI\MyStudyProjects\373_PS_HTTPClient_Dev\HTTPServer\Win32\Debug\public\files\UserID\2016\28\10
 rel public\files\UserID\2016\28\10

}


//check
if not TDirectory.Exists(dir) then Exit;


  // Splitting dir
   SplittedString:=dir.Split(['\']);


if SplittedString[High(SplittedString)]<>'temp' then
begin

     StartIndex:=Pos('\public',dir);
     if StartIndex<>0 then RelDir:=dir.Substring(StartIndex);

     RelDir:=StringReplace(RelDir,'\','/',[rfReplaceAll]);
     RelDir:='/'+RelDir;

    // RelDir:=RelDir+'/'+SplittedString[High(SplittedString)];

     SDADirs:=TDirectory.GetDirectories(dir);
     SDAFiles:=TDirectory.GetFiles(dir);



           //------ do something with subdirs
           for i := Low(SDADirs) to High(SDADirs) do

           begin

                    // Writing to database

                    if not IsFileOrFolderAlreadyInDB(idParent, SplittedString[High(SplittedString)],idParentNext ) then


                              begin

                               // writing to database following dirs with users_id=null
                               if ( SplittedString[High(SplittedString)]='public' ) or // =public
                                  ( SplittedString[High(SplittedString)]='files' ) // =files


                               then
//                                     if SplittedString[High(SplittedString)]<>'temp' then
                                          HTTPServer_Main_UnitVar.
                                          AddFoldersAndFilesToDB.InsertFolderOrFileToDB
                                          (

                                          true, // isFolder
                                          idParent,   // idParent
                                          SplittedString[High(SplittedString)], // name
                                          -1, // filesize
                                          RelDir,
                                          dir
                                          ) else

                                              // writing all other dirs with users_id index

 //                                             if SplittedString[High(SplittedString)]<>'temp' then
                                              HTTPServer_Main_UnitVar.
                                              AddFoldersAndFilesToDB.InsertFolderOrFileToDB
                                              (

                                              true, // isFolder
                                              idParent,   // idParent
                                              SplittedString[High(SplittedString)], // name
                                              idUser, // users_id
                                              -1, // filesize
                                               RelDir,
                                               dir
                                              );


                              AddValueToSDA(SDAIDParentNext,GetLastInsertedID.ToString);

                              end else

                              AddValueToSDA(SDAIDParentNext,idParentNext.ToString);


                   AddValueToSDA(SDAResult,SDADirs[i]);


           end;




           //----------- do something with files

           for i := Low(SDAFiles) to High(SDAFiles) do

           begin

            if Length(SDAIDParentNext)>0 then

            begin

             RelFilePath:=RelDir+'/'+ExtractFileName(SDAFiles[i]);
              absPath:=dir+'\'+ExtractFileName(SDAFiles[i]);
             // Writing to database

             NameToCompare:=ExtractFileName(SDAFiles[i]);

            if not IsFileOrFolderAlreadyInDB(SDAIDParentNext[0].ToInteger, NameToCompare,idParentNext ) then


              begin
              HTTPServer_Main_UnitVar.
              AddFoldersAndFilesToDB.InsertFolderOrFileToDB
              (

              false, // File
              SDAIDParentNext[0].ToInteger,   // idParent
              NameToCompare,   //SplittedString[High(SplittedString)], // name
              idUser, // users_id
              GetFileSize(SDAFiles[i]), // filesize
              RelFilePath,
              absPath

              );

              AddValueToSDA(SDAIDParentNext,GetLastInsertedID.ToString);

              end else

              AddValueToSDA(SDAIDParentNext,idParentNext.ToString);


             AddValueToSDA(SDAResult,SDAFiles[i]);
            end;
           end;




     // Recursievely going on all subdirs...
      for i := Low(SDADirs) to High(SDADirs) do
      SynchronizeDirWithDataBase( SDADirs[i],RelDir, SDAResult,SDAIDParentNext[i].ToInteger, idUserLocal);



end;

end;














procedure TDataBaseUnitServer.AddValueToSDA(var SDA: TStringDynArray;  Value: string);
begin

  SetLength(SDA, Length(SDA)+1);
  SDA[Length(SDA)-1]:=Value;

end;


function TDataBaseUnitServer.GetFileSize(AFilePath: String): Int64;
var
  FS: TFileStream;


begin


  try

     Result:=-1;

     FS := TFileStream.Create(AFilePath, fmOpenRead);

      try
        Result:=FS.Size;
      finally
        FS.Free;
      end;

  except

         on E:EStreamError do

         begin

         Result := -1;

          raise Exception.Create('This is EStreamError EClassName'+E.ClassName+' '
          +'EMessage '+E.Message);

         end;


  on E:Exception do

        begin
           Result := -1;




           raise Exception.Create(E.ClassName+' Exception Raised : '
         +#13#10+#13#10+E.Message);

        end;

  end;

end;




function TDataBaseUnitServer.GetAbsAdressByID(AID: integer): string;
begin

Result:='';


    with qGetAbsAdressByID do begin

     Params.ParamValues['id']:=AID;

     Open();

      if not IsEmpty then Result:=FieldByName('absPath').AsString;

     Close();

    end;


end;




function TDataBaseUnitServer.IsFolder(Aid:integer):boolean;
begin

Result:=False;

with qIsFolder do begin

  Params.ParamValues['id']:=Aid;
  Open();
  Result:=FieldByName('isFolder').AsBoolean;
  Close();

end;

end;




end.
