Automate database design changes! Post: 302924175

Sponsored Content

Top Forums Shell Programming and Scripting Automate database design changes! Post 302924175 by Chubler_XL on Thursday 6th of November 2014 06:19:35 PM

11-06-2014

Moderator

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 ;

This User Gave Thanks to Chubler_XL For This Post:

Chubler_XL

View Public Profile for Chubler_XL

Find all posts by Chubler_XL

7 More Discussions You Might Find Interesting

1. Programming

C++ class design

Can anybody tell me what is the best website or books to read for getting good knowledge in doing C++ class design. Please leave cplusplus.com or bjorne stroustrup. Other than these is there any website or book. Please do tell me

2. Shell Programming and Scripting

I want to design a program

i want to make a shell program. This program i give a current day and the result is to appear the celebrity and birthday(birthday and celebration is 2 txt files). In addition this procedure must do for a space day in future or past depend on user choice. Finally the program can run and as...

3. Shell Programming and Scripting

shell design

i want to design the shell for some basic commands such as cp mkdir rm mv etc... so please let me know how to start and wich all books to refer and if any body is having the model just mail me at, devskamat018@gmail.com:) i will be waiting please reply soon

4. Solaris

redirect solaris database from linux database..

hi.. i have a need .. my php runs on my linux redhat box with mysql.. i want my php code to refer another mysql database which is in solaris 10 x86... can u tell me the procedure .. how it can be done through php .. sorry am new to php... is it possible to redirect from linux mysql to...

5. Solaris

Can't create database after Oracle Database installation

I installed Oracle 10 software on Solaris 11 Express, everything was fine execpt I can't create database using dbca.rsp file. I populated file with following options. OPERATION_TYPE = "createDatabase" GDBNAME = "solaris_user.domain.com" SID = "solaris_user" TEMPLATENAME = "General...

6. Shell Programming and Scripting

CRON Job to copy database and replace existing database

I have a reseller account with hostgator, which means i have WHM and Cpanel. I have set up a staging environment for one of my wordpress installations (client website), which is essentially sitting at staging.domain.com (live site is at domain.com). The staging website is a complete copy of the...

7. UNIX for Advanced & Expert Users

SFTP Design

Hi all, I'm seeking an efficient and secure means of providing multiple named users access to files by their functional areas. For security, I've chosen SFTP using key pair authentication. The general principle is we have multiple users as follows: We have two type of files for Function...

LEARN ABOUT SUSE

drop_table

DROP 
TABLE(7)							   SQL Commands 						     DROP TABLE(7)

NAME

       DROP TABLE - remove a table

SYNOPSIS

       DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DESCRIPTION

       DROP  TABLE  removes tables from the database.  Only its owner can drop a table. To empty a table of rows without destroying the table, use
       DELETE [delete(7)] or TRUNCATE [truncate(7)].

       DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table.  However, to drop a table that	is
       referenced  by  a  view	or  a  foreign-key  constraint	of another table, CASCADE must be specified. (CASCADE will remove a dependent view
       entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

PARAMETERS

       IF EXISTS
	      Do not throw an error if the table does not exist. A notice is issued in this case.

       name   The name (optionally schema-qualified) of the table to drop.

       CASCADE
	      Automatically drop objects that depend on the table (such as views).

       RESTRICT
	      Refuse to drop the table if any objects depend on it. This is the default.

EXAMPLES

       To destroy two tables, films and distributors:

       DROP TABLE films, distributors;

COMPATIBILITY

       This command conforms to the SQL standard, except that the standard only allows one table to be dropped per command, and apart from the	IF
       EXISTS option, which is a PostgreSQL extension.

SEE ALSO

       ALTER TABLE [alter_table(7)], CREATE TABLE [create_table(7)]

SQL - Language Statements					    2010-05-14							     DROP TABLE(7)