Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3410137
  • 博文数量: 534
  • 博客积分: 11595
  • 博客等级: 上将
  • 技术积分: 5785
  • 用 户 组: 普通用户
  • 注册时间: 2006-12-22 17:00
文章分类

全部博文(534)

文章存档

2015年(4)

2014年(27)

2013年(15)

2012年(38)

2011年(36)

2010年(85)

2009年(63)

2008年(142)

2007年(124)

分类: LINUX

2008-07-31 12:47:59

bash shell代码如下:
#!/bin/bash

#Find all tablename in VIEWS files
views=$(cat $1 | awk -F " " '
{
        print $3;
}
');

for table_name in `echo $views`
do
        echo "-----------------$table_name";
        table_columns_sql=$(sed -e :a -e 'N;s/\r\n//;ta' $2 | sed -e 's/go/\n/ig' | grep -i "create table $table_name " | awk -F "\\\)lock allpages" '{print $1}' | awk -F ",\t\tCONSTRAINT" '{print $1}' | sed -e "s/create\ table\ $table_name\ (//ig");
        table_pk=$(echo $table_columns_sql | awk -F " " '{print $1}');

        #===== View ======
        table_columns_type=$(echo $table_columns_sql | sed -e 's/ , /,\n/ig' | sed -e 's/null//ig' | sed -e 's/not//ig' | sed -e '/^$/d');
        echo "CREATE OR REPLACE view $table_name as
select * from dblink('dbname=xxxx user=xxxx password=xxxx',
'select * from $table_name')
  as t1(
        $table_columns_type
);";


        #===== View insert ======
        table_columns=$(echo $table_columns_type | sed -e 's/ , /,\n/ig' | awk -F " " '{
                if(NR>1){
                        printf("|| \047,\047 || case when NEW.%s is not null then \047\047\047\047 || NEW.%s || \047\047\047\047 else \047\047\047\047\047\047 end\n\t",$1,$1)
                };
}');
        echo "CREATE OR REPLACE RULE "$table_name"_i AS
    ON INSERT TO $table_name DO INSTEAD
SELECT dblink_exec('dbname=xxxx user=xxxx password=xxxx'::text,
'INSERT INTO $table_name VALUES ('
        || case when NEW.$table_pk is not null then '''' || NEW.$table_pk || '''' else '''''' end
        $table_columns|| ');'
);
";

        #===== View update ======
        table_columns=$(echo $table_columns_type | sed -e 's/ , /,\n/ig' | awk -F " " '{
                if(NR>1){
                        printf("|| \047chrcssm=\047 || case when NEW.%s is not null then \047\047\047\047 || NEW.%s || \047\047\047,\047 ELSE \047%s,\047  end\n\t",$1,$1,$1)
                };
}');
        echo "CREATE OR REPLACE RULE "$table_name"_u AS
    ON UPDATE TO $table_name DO INSTEAD
SELECT dblink_exec('dbname=xxxx user=xxxx password=xxxx'::text,
'UPDATE $table_name SET '
        $table_columns|| ' WHERE $table_pk=''' || OLD.$table_pk || ''';'
);
";

        #===== View delete ======
        echo "CREATE OR REPLACE RULE "$table_name"_d AS
    ON DELETE TO $table_name DO INSTEAD
SELECT dblink_exec('dbname=xxxx user=xxxx password=xxxx'::text,
'DELETE FROM $table_name WHERE $table_pk=''' || OLD.$table_pk || ''';'
);
";
done
阅读(1462) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~