Grep我如何检查一个令牌是否出现在另一个文件之前?

Question

我试图在一堆持有SQL语句的文件中找到我们是否曾经从表中SELECT，然后我们INSERT进入它。看起来应该是用Grep编写的一行代码。

我已经想出了grep -zl "FROM (S*).*INSERT INTO

-z将输入视为一行，然后反向引用完成其余部分。

然而，用echo "SELECT a FROM x INSERT INTO x VALUES(1);" | grep -zl "FROM (S*).*INSERT INTO "没有结果

事实上，即使echo "aa aa" | grep "(S*) "也不返回任何值。

我错过了什么?

Answer 1

首先，让我们解决x:

echo "SELECT a FROM x INSERT INTO x VALUES(1);" | grep -E "FROM (S)*x.*INSERT INTO (S)*x"

然而，你可能有很多表格，你对它们都感兴趣。下面是列出表名的方法:

select TABLE_NAME
from information_schema.tables;

现在，让我们为每个表生成grep:

select CONCAT('sudo bash foo.sh "your script" ', TABLE_NAME)
from information_schema.tables;

和实现foo.sh如下:

echo "$1" | grep -E "FROM (S)*$2.*INSERT INTO (S)*$2"

查询为每个表生成grep。当然，您可以将查询筛选为选定的表，您可能还需要处理像

这样的情况

select ... from yourschema.yourtable

或

select ... from `yourtable`

但是从我给出的概念证明开始，看看这对你是否足够。

Answer 2

grep解决方案:

使用-P选项使用Perl符号的RegExp

grep -zPl "FROM ([[:alnum:]]+) INSERT INTO 1 VALUES"

在INSERT之前匹配SELECT语句，解:

报告的问题比描述的更复杂。

假设SELECT语句和相应的INSERT语句不顺序

例如:

SELECT a FROM x1 
INSERT INTO x1 VALUES(1);
SELECT a FROM x2 
SELECT a FROM x3 
SELECT a FROM y1
SELECT a FROM x2 
INSERT INTO x3 VALUES(1);
INSERT INTO x2 VALUES(1);
INSERT INTO y1 VALUES(1);
INSERT INTO x3 VALUES(2);
SELECT a FROM y2
INSERT INTO y1 VALUES(1);

只有x3和y1不匹配。并且有嵌套和重复。

我们不知道所有的表名。

我们需要stack数据结构。推入selectselect语句中的所有表名(不重复)，拉出insert中的所有表名

使用gawk(大多数Linux机器的标准awk)关联数组实现。筛选一次输入的SQL文件

gawkscript:script.awk

/SELECT .* FROM / { # for each line matching RegExp "SELECT .* FROM"
# read table name from current line
tableName = gensub(/.*FROM[[:space:]]+([[:alnum:]]+).*/,"\1",1);
# push-in tableName into associative array (used as stack)
tableNamesStack[tableName] = 1; 
}
/INSERT INTO / { # for each line matching RegExp "INSERT INTO "
# read table name from current line
tableName = gensub(/.*INTO[[:space:]]+([[:alnum:]]+).*/,"\1",1);
# if current tableName is in stack
if (tableName in tableNamesStack) {
# pull-out current tableName from stack
delete tableNamesStack[tableName];
} else {
# current tableName is missing from stack, report and continue.
printf ("Unmatched INSERT statement in line %d, for table %sn", NR, tableName);
}
}

runningscript.awk

gawk -f script.awk input.sql 
Unmatched INSERT statement in line 9, for table x3
Unmatched INSERT statement in line 11, for table y1