Stored procedures in database management systems are often used to implement complex business logic. Correctness of these procedures is critical for flawless working of the system. However, testing them remains difficult due to many possible database states and constraints on data. This leads to mostly manual testing. Newer tools offer automated execution for unit testing of stored procedures but the test cases are still written manually. We propose an approach of using dynamic symbolic execution for generating automated test cases and corresponding database states for stored procedures. We model the constraints on data imposed by the schema and the SQL statements, treating values in database tables as symbolic. We use SMT solver to find values that will drive the stored procedure on a particular execution path. We instrument the internal execution plans generated by PostgreSQL to extract constraints. We use Z3 to generate test cases consisting of table data and procedure inputs. Our evaluation using stored procedures from a large business application and various GitHub repositories quantifies the evidence of effectiveness of our technique by generating test cases that lead to schema constraint violations and user-defined exceptions.

中文翻译：

---

扩展符号执行以自动测试存储过程

数据库管理系统中的存储过程通常用于实现复杂的业务逻辑。这些程序的正确性对于系统的完美运行至关重要。然而，由于许多可能的数据库状态和数据限制，测试它们仍然很困难。这导致主要是手动测试。较新的工具为存储过程的单元测试提供自动执行，但测试用例仍然是手动编写的。我们提出了一种使用动态符号执行的方法来为存储过程生成自动化测试用例和相应的数据库状态。我们对模式和 SQL 语句施加的数据约束进行建模，将数据库表中的值视为符号。我们使用 SMT 求解器来查找将在特定执行路径上驱动存储过程的值。我们检测 PostgreSQL 生成的内部执行计划来提取约束。我们使用 Z3 生成由表数据和过程输入组成的测试用例。我们使用来自大型业务应用程序和各种 GitHub 存储库的存储过程进行评估，通过生成导致架构约束违规和用户定义异常的测试用例来量化我们技术有效性的证据。