Coverage for gws-app/gws/base/database/__init__.py: 100%

1"""Database support. 

2 

3Base classes for database connections and for the objects that read and 

4write database tables: layers, models and authorization providers. Concrete 

5implementations (for PostgreSQL/PostGIS) live in ``gws.plugin.postgres`` and 

6extend the classes in this package. 

7 

8Submodules 

9---------- 

10 

11- ``manager`` - the database manager (``root.app.databaseMgr``). It creates 

12 the providers configured under ``database.providers`` and finds them by uid 

13 or type. A provider that fails to configure is logged and skipped. The 

14 manager registers itself as the ``db`` middleware. 

15- ``provider`` - the base database provider. It wraps an SQLAlchemy ``Engine``, 

16 hands out connections, reflects and caches table structures and describes 

17 tables and columns. 

18- ``connection`` - the connection object returned by ``provider.connect()``, 

19 a thin wrapper around an SQLAlchemy ``Connection`` with fetch helpers. 

20- ``model`` - the base database model, which reads features with a SELECT built 

21 by the model fields and creates, updates and deletes table rows. 

22- ``layer`` - the base vector layer for a database table, with a default model 

23 and finder for the table. The geometry type and CRS are taken from the table 

24 description. Without configured models, the layer gets a default model. If 

25 no extent is configured, it is computed from the table bounds. Unless 

26 finders are configured or search is disabled, the layer gets a default 

27 finder. 

28- ``auth_provider`` - the base authorization provider that checks users with 

29 SQL queries. 

30 

31Providers and connections 

32------------------------- 

33 

34Layers, models and other objects refer to a provider with ``dbUid``. Without 

35``dbUid`` they use the default provider passed by their parent or the first 

36provider of their type (see ``gws.config.util.configure_database_provider_for``). 

37 

38A provider keeps one SQLAlchemy connection per thread. ``connect()`` calls can 

39be nested; inner calls reuse the open connection, and only the outermost one 

40closes it:: 

41 

42 with db.connect() as conn: 

43 rows = conn.fetch_all('SELECT * FROM my_table WHERE id = :id', id=1) 

44 with db.connect() as conn2: 

45 # the same connection 

46 n = conn2.fetch_int('SELECT count(*) FROM my_table') 

47 

48Because the connection is shared, a ``commit()`` or ``rollback()`` on any level 

49ends the transaction for all levels. The ``fetch_*`` helpers roll back after 

50reading. 

51 

52The engine is created on activation and is not pickled. Connection pooling is 

53off unless ``withPool`` is set; the ``pool`` options ``disabled``, 

54``pre_ping``, ``size``, ``recycle`` and ``timeout`` are passed to the engine. 

55 

56Table structures are reflected per schema and cached for 

57``schemaCacheLifeTime`` seconds. ``describe`` and ``describe_column`` turn 

58the reflected structure into ``gws.DataSetDescription`` and 

59``gws.ColumnDescription`` objects, which models use to derive their fields. 

60 

61Models 

62------ 

63 

64A database model builds its SELECT from the contributions of its fields 

65(``mc.dbSelect``), the search query (uids, keyword, shape, sort, limit, extra 

66conditions and columns) and the configured ``sqlFilter``. If the search asks 

67for uids, a keyword or a shape, but the table has no primary key or no field 

68contributes a matching condition, nothing is selected. Writes call the field 

69hooks (``before_create``, ``after_create`` and so on) around a single INSERT, 

70UPDATE or DELETE and commit the connection. Reads and writes check the user's 

71permissions on the model and raise ``gws.ForbiddenError`` if they are 

72missing. Composite primary keys are not supported. See ``gws.base.model`` for 

73the data flow between records, features and props. 

74 

75SQL authorization 

76----------------- 

77 

78The authorization provider runs two configured SELECT queries: 

79 

80- ``authorizationSql`` receives the placeholders ``{username}``, ``{password}`` 

81 and ``{token}`` from an authentication method. If it returns no rows, the 

82 provider does not know the user and the next provider is tried. More than one 

83 row is an error. Otherwise the row must contain the columns ``validuser`` 

84 and ``validpassword`` (booleans, both must be true to log in) and ``uid`` 

85 (the user id). The column ``roles`` is a comma-separated list of roles. 

86- ``getUserSql`` receives the placeholder ``{uid}`` and returns the record of 

87 this user, for example to restore a user from a session. 

88 

89The column names ``validuser``, ``validpassword`` and ``uid`` are 

90case-insensitive. Other columns are passed to ``gws.base.auth.user.from_record``. 

91In the config file the braces of the placeholders are doubled. 

92 

93Example:: 

94 

95 database.providers+ { 

96 uid "main_db" 

97 type "postgres" 

98 serviceName "LOCAL" 

99 } 

100 

101 map.layers+ { 

102 type "postgres" 

103 dbUid "main_db" 

104 tableName "edit.poi" 

105 models+ { 

106 type "postgres" 

107 isEditable true 

108 } 

109 } 

110 

111 auth.providers+ { 

112 type "postgres" 

113 dbUid "main_db" 

114 

115 authorizationSql ''' 

116 SELECT 

117 user.id 

118 AS uid, 

119 user.first_name || ' ' || user.last_name 

120 AS displayname, 

121 user.login 

122 AS login, 

123 user.is_enabled 

124 AS validuser, 

125 ( passwd = crypt({{password}}, passwd) ) 

126 AS validpassword 

127 FROM 

128 public.user 

129 WHERE 

130 user.login = {{username}} 

131 ''' 

132 

133 getUserSql ''' 

134 SELECT 

135 user.id 

136 AS uid, 

137 user.first_name || ' ' || user.last_name 

138 AS displayname, 

139 user.login 

140 AS login 

141 FROM 

142 public.user 

143 WHERE 

144 user.id = {{uid}} 

145 ''' 

146 } 

147 

148The ``authorizationSql`` example assumes Postgres with the ``pgcrypto`` extension. 

149""" 

150 

151from . import provider, manager, model