PROGRAM SchemaNameDeleter;
{-- ***************************************************************************
 -- ** Descr. : This script will remove hardcoded schema owner prefixes from all
 -- **          sourcecode(trigger/progunits/menuitems/recordgroups/etc)
 -- **          and block-tablenames.
 -- **
 -- ** MINVERS: requires FormsAPI Master 1.0 Build 241 and up !
 -- ***************************************************************************
 -- ** 09/05/02 1.001.00 Initial Creation, sm@orcl-toolbox.com
 -- ** 13/05/02 1.002.00 Code beautifying for build241, sm@orcl-toolbox.com
 -- ***************************************************************************}

//Global Variable Declaration
VAR
  s            : varchar2;
  i            : number;
  j            : number;
  frm          : number;
  ps           : TParamScreen;
  x            : tparamboard;
  files        : tstringlist;
  sources      : tstringlist;
  blocks       : tstringlist;
  savepath     : varchar2;
  schemaname   : varchar2;
  poscounts    : number;


//function that will do the actual schema name delete
function replaceschema(src : varchar2) : varchar2;
var posx       : number;
    uppersrc   : varchar2;
    newsrc     : varchar2;
    prefix     : char;

begin
  //initialize our running variables
  newsrc := src;
  uppersrc := upper(src);
  posx := 1;

  //check if we found anything
  posx := instr(uppersrc,schemaname);

  //loop through the source as long as we can find something
  while posx > 0 do
  begin

    //get the character directly in front of where we found our schemaname
    if posx=1
      then prefix := ' '
      else prefix := uppersrc[posx-1];

    //check that it really is just a schemaname and has nothing in front of its name!
    if      (prefix = ' ') //space
        or  (prefix = #9)  //tab
        or  (prefix = #10) //linefeed
        or  (prefix = #13) //carriage return
        or  (prefix = ',')
        or  (prefix = ';') then
    begin
      newsrc := removestring(newsrc,posx,posx+length(schemaname)); //remove the "schema."
      uppersrc := removestring(uppersrc,posx,posx+length(schemaname));
      poscounts := poscounts +1; //increase our statistic counter
    end;
    posx := instr(uppersrc,schemaname,posx+1); //search again
  end;

  //set the functions returnvalue
  result := newsrc;
end;

//Begin Main
BEGIN
      //build a nice parameterscreen
      ps := TParamScreen.create;
      x := ps.AddBoard('File selection',picOptions);
      x.addparam(parLabel,'MYLABEL','Please select the <B>files</B> to remove the POS. schema owner','','');
      x.addparam(parFiles,'MYFILES','Source','','Forms (*.fmb)|*.fmb|Menu (*.mmb)|*.mmb|All Files *.*|*.*');
      x.addparam(parPathname,'MYPATH','Savepath','','');
      x := ps.AddBoard('Search options',picOptions);
      x.addparam(parString,'SCHEMA','Schema Name','SYS','');
      x := ps.AddBoard('Database Connection',picOptions);
      x.addparam(parDatabaseLogon,'MYDB','Forms Connection','scott/tiger','');

      //show parameterscreen
      if ps.ShowParamScreen then
      begin
        //get the mandantory new path we are saving the modules to ..
        savepath := ps.paramvalue('MYPATH');
        if savepath='' then raiseException('Please specify a path to save the new modules to!');

        //get the schemaname to delete
        schemaname := upper( ps.paramvalue('SCHEMA')) +'.';

        //make a connection to the DB, that will speed up loading of formsmodules!
        api_connect(ps.paramvalue('MYDB'));

        //get the selected files
        files := StringToList(ps.paramvalue('MYFILES'));

        //loop through all files
        for i := 0 to files.count-1 do
        begin
          try

            //reset statistic counter for new module
            poscounts :=0 ;

            //load the formsfile
            frm := api_loadmodule( files.strings[i] );

            //loop through all sourcecode objects and delete the schema name
            sources := api_GetAllSourceObjects(frm);
            for j := 0 to sources.count-1 do
            begin
              s := api_GetPLSQL( sources.objects[j] );
              s := replaceschema(s);
              api_SetPLSQL( sources.objects[j], s);
            end;
            sources.free;

            //loop through blocks and delete the schema name from block-tablenames
            blocks := api_GetSubObjects(frm, D2FP_BLOCK);
            for j := 0 to blocks.count-1 do
            begin
              s := generic_GetTextProp( blocks.objects[j], D2FP_DML_DAT_NAM );
              s := replaceschema(s);
              generic_SetTextProp( blocks.objects[j], D2FP_DML_DAT_NAM, s );
              s := generic_GetTextProp( blocks.objects[j], D2FP_QRY_DAT_SRC_NAM );
              s := replaceschema(s);
              generic_SetTextProp( blocks.objects[j], D2FP_QRY_DAT_SRC_NAM, s );
            end;
            blocks.free;

            //save the module under the new path
            api_savemodule(frm,savepath+extractfilename(files.strings[i]));

            //output a nice progress message to the log
            logadd(to_char(i+1)+'/'+to_char(files.count)+' - '+files.strings[i]+', replaced '+to_char(poscounts)+' occurences' );

            //unload the module from memory
            api_destroymodule(frm);

          except
            logadd(' => ' +GetError,logerror);
          end;
        end;

        //disconnect and free all allocated stuff
        api_disconnect;
        files.free;

      end else
      begin
        logadd('pressed cancel button on parameterscreen!');
      end;
      ps.free;


END.
//End Main