SQL视图

访问数据库数据的传统方法是根据表或数据库视图配置层。从GeoServer 2.1.0开始，图层也可以定义为SQL视图。SQL视图允许对层的每个请求执行自定义SQL查询。这避免了为复杂查询创建数据库视图的需要。

更有用的是，可以通过字符串替换将SQL视图查询参数化。参数值可以在WMS和WFS请求中提供。可以为参数提供默认值，并可以通过正则表达式验证输入值，以消除SQL注入攻击的风险。

备注

SQL视图是只读的，因此不能由WFS-T事务更新。

创建SQL视图

为了创建SQL视图，管理员调用 Create new layer 第页。选择数据库存储后，将显示可供发布的表和视图的常规列表，链接 Configure new SQL view... 同时出现：

../../_images/createsqlview.png

选择 Configure new SQL view... 链接打开可在其中指定SQL视图查询的新页：

../../_images/createsql.png

备注

查询可以是任何在FROM子句（即， select * from (<the sql view>) [as] vtable ）。大多数SQL语句都是这样，但在某些数据库中，调用存储过程可能需要特殊的语法。此外，SQL语句返回的所有列都必须具有名称。在某些数据库中，函数调用需要别名。

输入有效的SQL查询后，按 Refresh 链接在 属性 表以获取从查询中确定的属性列的列表：

../../_images/sqlview-attributes.png

geoserver试图确定几何列类型和本机srid，但如果需要，应该对这些进行验证和更正。

备注

正确的SRID（空间引用ID）对于空间查询的工作至关重要。在许多空间数据库中，srid等于特定空间引用系统的epsg代码，但情况并非总是如此（例如，Oracle有许多非epsg srid代码）。

如果视图功能需要稳定的功能ID，则应在 标识符 列。始终确保这些属性生成唯一的键，否则筛选和WFS请求将无法正常工作。

定义查询和属性详细信息后，按 Save .通常 New Layer 将出现配置页面。如果需要对视图进行进一步更改，则该页底部有指向SQL视图编辑器的链接。 Data tab：

../../_images/sqlview-edit.png

创建后，SQL视图层的使用方式与传统的表支持层相同，但有一个限制是只读的。

警告

在此处保存SQL视图定义是不够的，必须保存包含该定义的层，以使更改生效。这是因为SQL视图定义实际上只是layer/featureType/coverage属性的一个组件。

参数化SQL视图

参数化SQL视图基于包含命名参数的SQL查询。参数值可以在WMS和WFS请求中动态提供，使用 viewparams 请求参数。参数可以指定默认值，以处理请求中未提供这些参数的情况。通过指定验证正则表达式，支持验证提供的参数值。仅当参数值与为其定义的正则表达式匹配时，才接受参数值。应始终使用适当的参数验证，以避免 SQL injection attacks .

警告

应谨慎使用SQL视图参数替换，因为验证不当的参数会带来SQL注入攻击的风险。在可能的情况下，考虑使用更安全的方法，如 dynamic filtering 在请求中，或 SLD中的变量替换 .

定义参数

在SQL视图查询中，参数名称由前导和尾随分隔。 % 标志。参数可以出现在查询文本中的任何位置，包括在SQL字符串常量中、代替SQL关键字或表示整个SQL子句的使用。

下面是一个用于名为 popstates 有两个参数， low 和 high ：

../../_images/sqlview-parametricsql.png

每个参数都需要定义其名称、可选的默认值和验证表达式。这个 Guess parameters from SQL 可以单击链接自动推断查询参数，也可以手动输入。结果是一个包含参数名称、默认值和验证表达式的表：

../../_images/sqlview-paramdefault.png

在这种情况下，应该指定默认值，因为没有参数值就无法执行查询（因为展开的查询 select gid, state_name, the_geom from pgstates where persons between and 是无效的SQL）。由于在SQL查询中使用参数要求其值为正整数，因此指定的验证正则表达式仅允许数字输入（即 ^[\d]+$ ）：

../../_images/sqlview-paramcustom.png

一旦定义了参数， 属性 Refresh 单击链接可分析查询并检索属性列。如果需要，可以更正计算的几何类型和列标识符详细信息。从这一点上看，工作流与非参数化查询相同。

使用参数化SQL视图

SQL视图参数是通过添加 viewparams WMS的参数 GetMap 或者WFS GetFeature 请求。这个 viewparams 参数是 key:value 对，用分号分隔：

viewparams=p1:v1;p2:v2;...

如果值包含分号或逗号，则必须用反斜杠转义这些值（例如 \, 和 \; ）

例如， popstates SQL视图层可以通过调用 层预览 . 最初不提供参数值，因此使用默认值并显示所有状态。

要显示所有居住人口超过2000万的州，以下参数将添加到 GetMap 请求： &viewparams=low:20000000

../../_images/sqlview-20millions.png

要显示居住人口在200万至500万之间的所有州，视图参数为： &viewparams=low:2000000;high:5000000

../../_images/sqlview-2m-5m.png

通过用逗号分隔每个参数映射，可以为多个图层提供参数：

&viewparams=l1p1:v1;l1p2:v2,l2p1:v1;l2p2:v2,...

参数映射的数量必须与请求中包含的层数（FeatureTypes）匹配。

使用XML视图参数格式

除了默认的SQL视图参数格式之外，还可以通过使用请求参数/值来使用XML格式：

&viewParamsFormat=XML

XML替代格式示例：

&viewParams=<VP><PS><P n="m1">8302,802,8505</P><P n="m2">22,44</P></PS><PS/><PS><P n="csvInput">acv,rrp;1,0;0,7;22,1</P></PS></VP>

viewParamsFormat 新的可选参数定义：

  • 选择视图参数格式，有效的实现值为 CharSeparated (默认)和 XML 。

  • 这是一个可选参数，如果不设置，将使用支持向后兼容的默认字符分隔格式。

XML标记/属性定义：

  • VP ：查看参数的根XML元素标记。这确保了XML的有效性(一个XML文档必须有一个根元素)。

  • PS: contains the parameters for a given layer (by position). If there are no parameters for the current layer this must be set as an empty element, e.g. <PS/>

  • P ：参数定义XML元素，包括参数名称作为 n 属性，并将值作为其文本内容。

  • n ：中的参数名称属性 P 元素。

如果某个图层没有要设置的参数，只需提供一个空 PS 元素： <PS/>

注：XML视图参数只能在GET请求中使用。

参数和验证

SQL视图参数的值可以是任意文本字符串。唯一的约束是视图查询返回的属性名和类型不能更改。这使得创建包含表示复杂SQL片段的参数的视图成为可能。例如，使用视图查询 select * from pgstates %where% 允许动态指定查询的WHERE子句。但是，这可能需要一个空的验证表达式。这是一个严重的风险 SQL injection attacks .只有当对服务器的访问被限制为受信任的客户端时，才应该使用此技术。

通常，必须小心使用SQL参数。它们应该始终包括只接受预期参数值的验证正则表达式。请注意，虽然应该构造验证表达式以防止非法值，但它们不必确保这些值在语法上是正确的，因为数据库SQL解析器将检查这些值。例如：

  • ^[\d\.\+-eE]+$ 检查参数值是否包含浮点数字（包括科学记数法）的有效字符，但不检查该值是否实际为有效数字。

  • [^;']+ 检查参数值是否不包含引号或分号。这可以防止常见的SQL注入攻击，但对实际值没有太大的限制。

验证正则表达式的资源

定义有效的验证正则表达式对于安全性很重要。正则表达式是一个复杂的主题，在这里不能完全解决。以下是构造正则表达式的一些资源：

SQL WHERE子句的占位符

SQL WHERE geoserver使用上下文过滤器（例如WMS查询的边界框过滤器）生成的子句将添加到SQL视图定义周围。当我们有额外的操作可以在用geoserver生成的过滤器过滤的行的顶部完成时，这就变得很方便（性能更好）。

此功能的典型用例是在过滤结果的基础上执行分析函数：

SELECT STATION_NAME,
       MEASUREMENT,
       MEASUREMENT_TYPE,
       LOCATION
FROM
  (SELECT STATION_NAME,
          MEASUREMENT,
          MEASUREMENT_TYPE,
          LOCATION,
          ROW_NUMBER() OVER(PARTITION BY STATION_ID, MEASUREMENT_TYPE
                            ORDER BY TIME DESC) AS RANK
   FROM
     (SELECT st.id AS STATION_ID,
             st.common_name AS STATION_NAME,
             ob.value AS MEASUREMENT,
             pr.param_name AS MEASUREMENT_TYPE,
             ob.time AS TIME,
             st.position AS LOCATION
      FROM meteo.meteo_stations st
      LEFT JOIN meteo.meteo_observations ob ON st.id = ob.station_id
      LEFT JOIN meteo.meteo_parameters pr ON ob.parameter_id = pr.id

      -- SQL WHERE clause place holder for GeoServer
      WHERE 1 = 1 :where_clause:) AS stations_filtered) AS stations

WHERE RANK = 1;

在使用显式 :where_clause: 持证人：

  • 它需要添加到一个位置，其中geoserver已知的所有属性都已存在。

  • 这个 :where_clause: 只能出现一次

当A WHERE 子句占位符存在，geoserver将始终添加一个显式 AND 在生产开始时 WHERE 条款。这样就可以注入 WHERE 在复杂表达式的中间。

Previous: JNDI
Next: 控制空间数据库中特征ID的生成