使用 perl 自动化 sql 连接
我有一个前端 Web 应用程序，它查询 MySQL 数据库并向用户提供请求的数据。数据库包含25张表；有关联表的外键。这里的问题是用户对 SQL 完全陌生；我不能指望他们加入桌子。
我想编写一个自动生成连接的脚本。我想知道 Perl 中是否有任何模块可以使这项任务变得更容易？
我自己使用 SQL 的经历只有几个月。请让我知道您对此的看法。
I have a front end web application which queries the MySQL database and presents the user with the requested data. The database contains 25 tables; there are foreign keys relating tables. The problem here is the users are completely new to SQL; I cannot expect them to join the tables.
I want to write a script which generates the joins automatically. I want to know if there are any modules available in Perl which make this task easier?
My own experience with SQL is just a few months. Please let me know your opinion on this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不知道有任何模块可以通用地执行此操作，并且创建一个模块非常重要。问题范围从映射主键/外键匹配（它们并不总是命名相同），到如何处理 NULL 值（内连接或外连接？），再到确保整套表甚至是完全可连接的。
另外，至少在 Sybase（不确定 MySQL）中，即使您获得了正确的数据，也可以自动执行此操作，这并不是微不足道的性能优化角度。
说实话，我会做如下：
首先，强制将数据定义记录在配置中（包括表、列、主键和外键以及内部/的可连接性） 。
在“主”表和“丰富”表之间划分。
丰富表是一个表，其唯一目的是在其主键上连接到一些主表，以便向从主表检索的每一行添加更多列（我想说不应该有可以是这些表上的任何 where 子句（除了连接之外，以使生活更简单）。作为一个例子，考虑一个概念图书馆数据库。 “评论”表将是“丰富”表 - 您仅使用其中的数据来丰富图书信息，而不是进行查询。 “顾客”表也将是（对于某些业务用途）“丰富表”，仅用于将顾客姓名/地址连接到另一个表中的顾客 ID。
对于主表，忘记让用户构建查询。简单地枚举他们可能想要运行的业务场景的每个查询（大多数可能的连接没有意义/不需要完成）；然后对于每个场景，写出非常清晰、用户友好的描述，说明他们正在选择的内容，然后针对该场景的主表进行优化查询。另外，对于每个预设查询，构建在该查询中选择的外键列表，这可能有助于用户从丰富表中进行丰富。 （例如，如果在某些查询中选择了“patron_id”字段，则添加“patrons”作为可能列出的额外数据）
然后允许用户在 GUI 中选择特定的用例场景。查找与该场景匹配的预设查询。然后显示可能的丰富选项（包括顾客地址？y/n？）。将这些额外的丰富连接添加到预先固定的查询中相当容易。
I'm not aware of any module which does it generically, and creating one is very non-trivial. The issues range from mapping which primary/foreign keys match (they aren't always named the same), to how to deal with NULL values (inner or outer joins?) to ensuring that the full set of tables is even completely joinaable.
Also, at least in Sybase (not sure about MySQL), there are performance optimization angles to doing this automatically which aren't trivial, even if you get the correct data.
To be perfectly honest, what I would do instead would be the following:
First off, force the data definition to be recorded in a configuration (including, tables, columns, primary and foreign keys, and joinability as far as inner/outer joins).
Split between the "main" tables and "enrichment" tables.
An enrichment table is a table whose ONLY purpose is to be joined on its primary key to some of the main tables to add more columns to each row retrieved from main tables (I'd go as far as to say there should not be any where clauses on those tables except for joins to make life simpler). As an example, consider a notional library database. "reviews" table would be "enrichment" table - you only use the data there to enrich the book info, not to query against. "patrons" table would also be (for certain business uses) an "enrichment table", merely to join patron name/address to patron_id in another table.
For main tables, forget letting the users build a query. Simply enumerate EVERY single query they might want to run as far as business scenarios (most possible joins just don't make sense/don't need to be done); then for each scenario write up VERY clear user-friendly description of what they're selecting and then an optimized query against main tables for that scenario. Plus, for each of those canned queries, build a list of FOREIGN keys selected in that query which might be of use to the users to enrich from enrichment tables. (e.g. if "patron_id" field is selected in some query, you add "patrons" as possible extra data listed)
Then allow your users in the GUI to pick a specific use case scenario. Find the canned query matching that scenario. Then display the possible enrichment options (include patron address? y/n?). Adding those extra enrichment joins to the pre-canned query is fairly easy.
我最好的建议是通过前端应用程序解析用户输入，然后使用它来解析 Perl DBI。
My best advice would be to parse the user input via the front end app and then use that to parse queries for the Perl DBI.