Shell Programming and Scripting

I need to create a script to automate creating a deployment for me by taking into consideration two input files.
1. design file of entire database, it has the entire database create statements.
2. logs which point to database objects that need to be fixed like below:


LOG_design_failure.txt

Code:

Syntax {TABLENAME}{singelspace}{DATABASE}.{TABLEINDEXNAME}

Code:

  ###LOGS OF INSUFFICIENT DESIGN###
  ###SOME NONSENSE LINES HERE###SOME NONSENSE LINES HERE###
  ###SOME NONSENSE LINES HERE###SOME NONSENSE LINES HERE###
  ###SOME NONSENSE LINES HERE###SOME NONSENSE LINES HERE###
   
 
TABLE NAME  DATABASE.TABLE_INDEX_NAME  has insufficient design
  TABLE NAME  test123.stagingdata_5151_Design_1a  has insufficient design
  TABLE NAME  test123.stagingdata5  has insufficient design
  TABLE NAME  test123.RTMASLstagingdata13_13151_Design_1a  has insufficient design
  TABLENAME  DATABASERTM.BusinessTable  has insufficient design
  TABLE NAME  PROJECTRTM.BusinessData  has insufficient design

so heres what I need to do I need to create a script to grab from the logs above the table index name from this line:

TABLENAME test123.stagingdata_5151_Design_1a has insufficient design

isolate it like so: test123.stagingdata_5151_Design_1a to save to a variable to temporarily hold it.

Then take that line at and take it and look at the same "index name" inside another text file I provide from there and look at its create statement and find it:
{CREATE INDEX DATABASE.TABLE_INDEX_NAME …} --- below


*** Always the same format
*** the below is extracted from a huge file don’t worry I have 150gb+ ram so I can do this sort of processing

Grab from log file:

Code:

CREATE INDEX DATABASE.TABLE_INDEX_NAME /* nonsense label here */
  (
   a
  )
  AS
   SELECT stagingdata_5151_Design_1a.a
   FROM DATABASE.TABLE_NAME
   ORDER BY testint.a
  SEGMENTED BY algorithmHASH(testint.a) ALL NODES ;

1a ***important thing is I want the code to grab and find the CREATE INDEX statemetnt then go to that statement in the design file above (it will have 3000+ designs) I provided.

2a I want to assign the variable $DATABASESOURCE by grabbing the where it says "FROM DATABASE.TABLE_NAME" cut of the "FROM"

Go find the line CREATE INDEX DATABASE.TABLE_INDEX_NAME go down to read until the ; semicolon where it should take that and make the next changes.

By dragging the create index statement to another smaller file to make the changes inside as I specify below:



The following is to create a replacement design to fix the design issue:

1b. CREATE INDEX STATEMENT. adjusted like so grab the statement from log above, take the create statement and adjust like SO (ADD _CHANGES to the index name) AND (at the ; (semicolon) change design parameters by replacing the words: ALL NODES ; say for example ALL NODES to NODE ONE; ( OR whatever I specify the changes in the design end to be in another variable like this)

**** I want the above to be a sort of Ctl+F and replace the portion I specify in a variable I’m searching for and replace it with alternative to fix the design issue. (grep/replace)

Code:

 
  $FIND_THIS="ALL NODES ;"
  $REPLACE_WITH_THIS="NODE_ONE_ONLy ;"

1b. EXAMPLE

Code:

  CREATE INDEX DATABASE.TABLE_INDEX_NAME_CHANGES /* nonsense label here */
  (
   a
  )
  AS
   SELECT stagingdata_5151_Design_1a.a
   FROM DATABASE.TABLE_NAME
   ORDER BY testint.a
  SEGMENTED BY algorithmHASH(testint.a) NODE_ONE_ONLY ;

This part is to create aditional statemetns I need using the variables from above:
####Create these statements using variables by grabbing from the above:####

3. alter rename DATABASE.TABLE_INDEX_NAME_CHANGES RENAME TO DATABASE.TABLE_INDEX_NAME;

4. DROP OLD INDEX LIKE SO :

DROP OLD INDEX DATABASE.TABLE_INDEX_NAME;

It is very important that this be done and saved to a file as I need to generate the design fix deployment and use it.





So in summary.
Look in log , take an issue with a table index and assign it to a variable isolating the INDEX name only

Then go ahead search the database design file I provide find the start of the CREATE INDEX DATABASE.TABLE_INDEX_NAME ….
…
…
As well and go to the end of the create index statement to the “;”

Grab and Isolate this into another file make changes like add _CHANGES to CREATE INDEX DATABASE.TABLE_INDEX_NAME_CHANGES

Find the FROM LINE and isolate the root database table name separate from the index label. And assign to a variable and generate the other database deployment statements to fix this “design” issue.


3. alter rename DATABASE.TABLE_INDEX_NAME_CHANGES RENAME TO DATABASE.TABLE_INDEX_NAME;

4. DROP OLD INDEX LIKE SO :
DROP OLD INDEX DATABASE.TABLE_INDEX_NAME;



The following diagram illustrates my logic in the process
Pasteboard — Uploaded Image


---------- Post updated at 04:59 PM ---------- Previous update was at 04:49 PM ----------

Code:

for file in $FILE_LOCATION/file_history_*
do
    FDATE=${file#*file_history_}
    FDATE=${FDATE%%_*}
    if [[ "$FDATE" < "$LDATE" ]]
    then
        echo "$file REMOVED!"
                rm -rf ${file}
    fi
done

Something like this code block can be used to run through and grab/assign varaibles initally.
I used the above before to look through a list of logs files with same name take them remove those that match a DATE parameter.
i'm sure it can be used for this...

How about this:

Code:

awk \
    -v F="ALL NODES ;" \
    -v T="NODE_ONE_ONLY ;" '
FNR==NR {
   TABLE=$0
   sub(/^.*SELECT /,x,TABLE)
   sub(/(\.a)*\n.*/,x,TABLE)
   if (TABLE !~ " " && length(TABLE))
       C[TABLE]=$0";"
   next
}
$1 == "TABLE" && $2 =="NAME" {
   TABLE=$3
   sub(/.*[\.]/,x,TABLE);

   if (TABLE in C) {
      N=C[TABLE]
      gsub(F,T,N)
      printf "DROP OLD INDEX %s;\n", TABLE
      print N "\n"
   }
}' RS=\; database_design.txt RS='\n' LOG_design_failure.txt

output:

Code:

DROP OLD INDEX stagingdata_5151_Design_1a;
  CREATE INDEX DATABASE.TABLE_INDEX_NAME_CHANGES /* nonsense label here */
  (
   a
  )
  AS
   SELECT stagingdata_5151_Design_1a.a
   FROM DATABASE.TABLE_NAME
   ORDER BY testint.a
  SEGMENTED BY algorithmHASH(testint.a) NODE_ONE_ONLY ;

Code:

   TABLE=$0
   sub(/^.*SELECT /,x,TABLE)
   sub(/(\.a)*\n.*/,x,TABLE)
   if (TABLE !~ " " && length(TABLE))
       C[TABLE]=$0";"
   next

I am trying to point to this line instead:

Code:

FROM DATABASE.TABLE_NAME

im trying to edit but will remove this message if successful

I am trying to change to

Code:

  DATABASE.TABLE_NAME

Thanks a lot chubler!