database - 如何在 Informix 中创建影子表

Question

我正在开发一个审计跟踪模块，该模块要求我从原始表创建一个巨大的影子表列表。我正在考虑编写一个可以从另一个表生成影子表的存储过程。这个表可以是任何表，可以是包含任意数据类型的3个字段的table_A，也可以是包含任意数据类型的10个字段的table_B，并且可以基于传递给存储过程的参数。

我知道我可以像这样从 syscolumns 和 systables 中检索特定 table_A 可用的字段列表:

select * from syscolumns where tabid = (select tabid from systables where tabname='table_A')

这个语句会返回一个字段列表，比方说field_A，然后我会重命名这个field_A来创建2个新字段pre_field_A和post_field_A，然后使用这2个新字段创建shadow_table_A的影子表.将此概念应用于其余字段。无需担心字段的数据类型，因为此信息已存在于 syscolumns 中，我可以将其复制到影子表中。

我目前对如何存储从上述语句返回的值列表感到困惑，因为通常任何表都包含多个字段。可以使用数组来完成吗？或为审计跟踪目的创建影子表的任何替代解决方案？

Answer 1

您的查询将有效，但使用连接可能更符合习惯:

SELECT *
  FROM "informix".systables AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabname = 'table_a';

另外，请注意，如果您在环境中设置了 DELIMIDENT 时创建了表，并且您创建的表的名称用双引号引起来，则系统目录将仅包含大小写混合的 table_A .通常，表名在系统目录中将全部为小写字母；列名也类似。

但是，这与您的问题无关。处理所有荣耀的用户定义类型是痛苦的。但是，如果您处理的是普通数据库，您当然可以那样工作，尽管使用 DB-Schema (dbschema) 生成表的模式，然后捕获它可能更容易.您实际上可以使用 SYSTEM 语句通过存储过程来完成此操作，但我可能会从存储过程外部进行此操作。这确实取决于您还需要做什么。每个字段的前像和后像可能成本适中。

如果您有 IBM Informix Dynamic Server 11.70，则可以动态创建 CREATE {audit} TABLE 语句，然后执行该语句。因此，您将在存储过程中使用 FOREACH 循环构建查询以依次添加每一列，然后执行创建审计表的语句。您还必须解码类型。您也可以/应该为此使用一个程序。我假设 tabname 是传递给存储过程的变量，c_colno、c_colname 和 c_typename是局部变量(如 cts，'create table statement' 的缩写，和 pad):

LET cts = 'CREATE TABLE ' || tabname || '(';
LET pad = '';
FOREACH SELECT c.colno, c.colname, type_name(c.coltype, c.collength)
          INTO c_colno, c_colname, c_typename
          FROM "informix".systables AS t
          JOIN "informix".syscolumns AS c
            ON t.tabid = c.tabid
         WHERE t.tabname = tabname
         ORDER BY c.colno
    LET cts = cts || pad || 'pre_'  || c_colname || ' ' || c_coltype;
    LET cts = cts || ',' || 'post_' || c_colname || ' ' || c_coltype;
    LET pad = ',';
END FOREACH;
LET cts = cts || ');';

您可能想要处理 NOT NULL 和主键约束以及各种其他事情，但这为您提供了继续进行的基础知识。