DeveelDB  20151217
complete SQL database system, primarly developed for .NET/Mono frameworks
Static Public Member Functions | Public Attributes | Static Public Attributes | List of all members
Deveel.Data.Sql.Schemas.InformationSchema Class Reference

Static Public Member Functions

static void CreateViews (IQuery query)
 
static void GrantToPublic (IQuery query)
 

Public Attributes

const string SchemaName = "INFORMATION_SCHEMA"
 

Static Public Attributes

static readonly ObjectName Name = new ObjectName(SchemaName)
 
static readonly ObjectName Catalogs = new ObjectName(Name, "catalogs")
 
static readonly ObjectName Tables = new ObjectName(Name, "tables")
 
static readonly ObjectName TablePrivileges = new ObjectName(Name, "table_privileges")
 
static readonly ObjectName Schemata = new ObjectName(Name, "schemata")
 
static readonly ObjectName Columns = new ObjectName(Name, "columns")
 
static readonly ObjectName ColumnPrivileges = new ObjectName(Name, "column_privileges")
 
static readonly ObjectName PrimaryKeys = new ObjectName(Name, "primary_keys")
 
static readonly ObjectName ImportedKeys = new ObjectName(Name, "imported_keys")
 
static readonly ObjectName ExportedKeys = new ObjectName(Name, "exported_keys")
 
static readonly ObjectName DataTypes = new ObjectName(Name, "data_types")
 
static readonly ObjectName CrossReference = new ObjectName(Name, "cross_reference")
 
static readonly ObjectName UserPrivileges = new ObjectName(Name, "user_privileges")
 
static readonly ObjectName ThisUserSimpleGrantViewName = new ObjectName(Name, "ThisUserSimpleGrant")
 
static readonly ObjectName ThisUserGrantViewName = new ObjectName(Name, "ThisUserGrant")
 
static readonly ObjectName ThisUserSchemaInfoViewName = new ObjectName(Name, "ThisUserSchemaInfo")
 
static readonly ObjectName ThisUserTableColumnsViewName = new ObjectName(Name, "ThisUserTableColumns")
 
static readonly ObjectName ThisUserTableInfoViewName = new ObjectName(Name, "ThisUserTableInfo")
 

Detailed Description

Definition at line 23 of file InformationSchema.cs.

Member Function Documentation

static void Deveel.Data.Sql.Schemas.InformationSchema.CreateViews ( IQuery  query)
inlinestatic

Definition at line 63 of file InformationSchema.cs.

63  {
64  // This view shows the grants that the user has (no join, only priv_bit).
65  query.ExecuteQuery("CREATE VIEW " + ThisUserSimpleGrantViewName + " AS " +
66  " SELECT \"priv_bit\", \"object\", \"name\", \"user\", " +
67  " \"grant_option\", \"granter\" " +
68  " FROM " + SystemSchema.UserGrantsTableName +
69  " WHERE ( user = user() OR user = '@PUBLIC' )");
70 
71  // This view shows the grants that the user is allowed to see
72  query.ExecuteQuery("CREATE VIEW " + ThisUserGrantViewName + " AS " +
73  " SELECT \"description\", \"object\", \"name\", \"user\", " +
74  " \"grant_option\", \"granter\" " +
75  " FROM " + SystemSchema.UserGrantsTableName + ", " + SystemSchema.PrivilegesTableName +
76  " WHERE ( user = user() OR user = '@PUBLIC' )" +
77  " AND " + SystemSchema.UserGrantsTableName + ".priv_bit = " +
78  SystemSchema.PrivilegesTableName + ".priv_bit");
79 
80  // A view that represents the list of schema this user is allowed to view
81  // the contents of.
82  query.ExecuteQuery("CREATE VIEW " + ThisUserSchemaInfoViewName + " AS " +
83  " SELECT * FROM " + SystemSchema.SchemaInfoTableName +
84  " WHERE \"name\" IN ( " +
85  " SELECT \"name\" " +
86  " FROM " + ThisUserGrantViewName + " " +
87  " WHERE \"object\" = " + ((int)DbObjectType.Schema) +
88  " AND \"description\" = '" + Privileges.List + "' )");
89 
90  // A view that exposes the table_columns table but only for the tables
91  // this user has read access to.
92  query.ExecuteQuery("CREATE VIEW " + ThisUserTableColumnsViewName + " AS " +
93  " SELECT * FROM " + SystemSchema.TableColumnsTableName +
94  " WHERE \"schema\" IN ( " +
95  " SELECT \"name\" FROM " + ThisUserSchemaInfoViewName + ")");
96 
97  // A view that exposes the 'table_info' table but only for the tables
98  // this user has read access to.
99  query.ExecuteQuery("CREATE VIEW " + ThisUserTableInfoViewName + " AS " +
100  " SELECT * FROM " + SystemSchema.TableInfoTableName +
101  " WHERE \"schema\" IN ( " +
102  " SELECT \"name\" FROM "+ThisUserSchemaInfoViewName + ")");
103 
104  query.ExecuteQuery(" CREATE VIEW " + Tables + " AS " +
105  " SELECT NULL AS \"TABLE_CATALOG\", \n" +
106  " \"schema\" AS \"TABLE_SCHEMA\", \n" +
107  " \"name\" AS \"TABLE_NAME\", \n" +
108  " \"type\" AS \"TABLE_TYPE\", \n" +
109  " \"other\" AS \"REMARKS\", \n" +
110  " NULL AS \"TYPE_CATALOG\", \n" +
111  " NULL AS \"TYPE_SCHEMA\", \n" +
112  " NULL AS \"TYPE_NAME\", \n" +
113  " NULL AS \"SELF_REFERENCING_COL_NAME\", \n" +
114  " NULL AS \"REF_GENERATION\" \n" +
115  " FROM " + ThisUserTableInfoViewName + "\n");
116 
117  query.ExecuteQuery(" CREATE VIEW " + Schemata + " AS " +
118  " SELECT \"name\" AS \"TABLE_SCHEMA\", \n" +
119  " NULL AS \"TABLE_CATALOG\" \n" +
120  " FROM " + ThisUserSchemaInfoViewName + "\n");
121 
122  query.ExecuteQuery(" CREATE VIEW " + Catalogs + " AS " +
123  " SELECT NULL AS \"TABLE_CATALOG\" \n" +
124  " FROM " + SystemSchema.SchemaInfoTableName + "\n" + // Hacky, this will generate a 0 row
125  " WHERE FALSE\n");
126 
127  query.ExecuteQuery(" CREATE VIEW " + Columns + " AS " +
128  " SELECT NULL AS \"TABLE_CATALOG\",\n" +
129  " \"schema\" AS \"TABLE_SCHEMA\",\n" +
130  " \"table\" AS \"TABLE_NAME\",\n" +
131  " \"column\" AS \"COLUMN_NAME\",\n" +
132  " \"sql_type\" AS \"DATA_TYPE\",\n" +
133  " \"type_desc\" AS \"TYPE_NAME\",\n" +
134  " IIF(\"size\" = -1, 1024, \"size\") AS \"COLUMN_SIZE\",\n" +
135  " NULL AS \"BUFFER_LENGTH\",\n" +
136  " \"scale\" AS \"DECIMAL_DIGITS\",\n" +
137  " IIF(\"sql_type\" = -7, 2, 10) AS \"NUM_PREC_RADIX\",\n" +
138  " IIF(\"not_null\", 0, 1) AS \"NULLABLE\",\n" +
139  " '' AS \"REMARKS\",\n" +
140  " \"default\" AS \"COLUMN_DEFAULT\",\n" +
141  " NULL AS \"SQL_DATA_TYPE\",\n" +
142  " NULL AS \"SQL_DATETIME_SUB\",\n" +
143  " IIF(\"size\" = -1, 1024, \"size\") AS \"CHAR_OCTET_LENGTH\",\n" +
144  " \"seq_no\" + 1 AS \"ORDINAL_POSITION\",\n" +
145  " IIF(\"not_null\", 'NO', 'YES') AS \"IS_NULLABLE\"\n" +
146  " FROM " + ThisUserTableColumnsViewName + "\n");
147 
148  query.ExecuteQuery(" CREATE VIEW " + ColumnPrivileges + " AS " +
149  " SELECT \"TABLE_CATALOG\",\n" +
150  " \"TABLE_SCHEMA\",\n" +
151  " \"TABLE_NAME\",\n" +
152  " \"COLUMN_NAME\",\n" +
153  " IIF(\"ThisUserGrant.granter\" = '@SYSTEM', \n" +
154  " NULL, \"ThisUserGrant.granter\") AS \"GRANTOR\",\n" +
155  " IIF(\"ThisUserGrant.user\" = '@PUBLIC', \n" +
156  " 'public', \"ThisUserGrant.user\") AS \"GRANTEE\",\n" +
157  " \"ThisUserGrant.description\" AS \"PRIVILEGE\",\n" +
158  " IIF(\"grant_option\" = 'true', 'YES', 'NO') AS \"IS_GRANTABLE\" \n" +
159  " FROM " + Columns + ", INFORMATION_SCHEMA.ThisUserGrant \n" +
160  " WHERE CONCAT(columns.TABLE_SCHEMA, '.', columns.TABLE_NAME) = \n" +
161  " ThisUserGrant.name \n" +
162  " AND INFORMATION_SCHEMA.ThisUserGrant.object = 1 \n" +
163  " AND INFORMATION_SCHEMA.ThisUserGrant.description IS NOT NULL \n");
164 
165  query.ExecuteQuery(" CREATE VIEW " + TablePrivileges + " AS " +
166  " SELECT \"TABLE_CATALOG\",\n" +
167  " \"TABLE_SCHEMA\",\n" +
168  " \"TABLE_NAME\",\n" +
169  " IIF(\"ThisUserGrant.granter\" = '@SYSTEM', \n" +
170  " NULL, \"ThisUserGrant.granter\") AS \"GRANTOR\",\n" +
171  " IIF(\"ThisUserGrant.user\" = '@PUBLIC', \n" +
172  " 'public', \"ThisUserGrant.user\") AS \"GRANTEE\",\n" +
173  " \"ThisUserGrant.description\" AS \"PRIVILEGE\",\n" +
174  " IIF(\"grant_option\" = 'true', 'YES', 'NO') AS \"IS_GRANTABLE\" \n" +
175  " FROM " + Tables + ", INFORMATION_SCHEMA.ThisUserGrant \n" +
176  " WHERE CONCAT(tables.TABLE_SCHEMA, '.', tables.TABLE_NAME) = \n" +
177  " ThisUserGrant.name \n" +
178  " AND INFORMATION_SCHEMA.ThisUserGrant.object = 1 \n" +
179  " AND INFORMATION_SCHEMA.ThisUserGrant.description IS NOT NULL \n");
180 
181  query.ExecuteQuery(" CREATE VIEW " + PrimaryKeys + " AS " +
182  " SELECT NULL \"TABLE_CATALOG\",\n" +
183  " \"schema\" \"TABLE_SCHEMA\",\n" +
184  " \"table\" \"TABLE_NAME\",\n" +
185  " \"column\" \"COLUMN_NAME\",\n" +
186  " \"SYSTEM.pkey_cols.seq_no\" \"KEY_SEQ\",\n" +
187  " \"name\" \"PK_NAME\"\n" +
188  " FROM " + SystemSchema.PrimaryKeyInfoTableName + ", " + SystemSchema.PrimaryKeyColumnsTableName + "\n" +
189  " WHERE pkey_info.id = pkey_cols.pk_id\n" +
190  " AND \"schema\" IN\n" +
191  " ( SELECT \"name\" FROM INFORMATION_SCHEMA.ThisUserSchemaInfo )\n");
192 
193  query.ExecuteQuery(" CREATE VIEW " + ImportedKeys + " AS " +
194  " SELECT NULL \"PKTABLE_CATALOG\",\n" +
195  " \"fkey_info.ref_schema\" \"PKTABLE_SCHEMA\",\n" +
196  " \"fkey_info.ref_table\" \"PKTABLE_NAME\",\n" +
197  " \"fkey_cols.pcolumn\" \"PKCOLUMN_NAME\",\n" +
198  " NULL \"FKTABLE_CATALOG\",\n" +
199  " \"fkey_info.schema\" \"FKTABLE_SCHEMA\",\n" +
200  " \"fkey_info.table\" \"FKTABLE_NAME\",\n" +
201  " \"fkey_cols.fcolumn\" \"FKCOLUMN_NAME\",\n" +
202  " \"fkey_cols.seq_no\" \"KEY_SEQ\",\n" +
203  " I_FRULE_CONVERT(\"fkey_info.update_rule\") \"UPDATE_RULE\",\n" +
204  " I_FRULE_CONVERT(\"fkey_info.delete_rule\") \"DELETE_RULE\",\n" +
205  " \"fkey_info.name\" \"FK_NAME\",\n" +
206  " NULL \"PK_NAME\",\n" +
207  " \"fkey_info.deferred\" \"DEFERRABILITY\"\n" +
208  " FROM " + SystemSchema.ForeignKeyInfoTableName + ", " + SystemSchema.ForeignKeyColumnsTableName + "\n" +
209  " WHERE fkey_info.id = fkey_cols.fk_id\n" +
210  " AND \"fkey_info.schema\" IN\n" +
211  " ( SELECT \"name\" FROM INFORMATION_SCHEMA.ThisUserSchemaInfo )\n");
212 
213  query.ExecuteQuery(" CREATE VIEW " + ExportedKeys + " AS " +
214  " SELECT NULL \"PKTABLE_CAT\",\n" +
215  " \"fkey_info.ref_schema\" \"PKTABLE_SCHEMA\",\n" +
216  " \"fkey_info.ref_table\" \"PKTABLE_NAME\",\n" +
217  " \"fkey_cols.pcolumn\" \"PKCOLUMN_NAME\",\n" +
218  " NULL \"FKTABLE_CATALOG\",\n" +
219  " \"fkey_info.schema\" \"FKTABLE_SCHEMA\",\n" +
220  " \"fkey_info.table\" \"FKTABLE_NAME\",\n" +
221  " \"fkey_cols.fcolumn\" \"FKCOLUMN_NAME\",\n" +
222  " \"fkey_cols.seq_no\" \"KEY_SEQ\",\n" +
223  " I_FRULE_CONVERT(\"fkey_info.update_rule\") \"UPDATE_RULE\",\n" +
224  " I_FRULE_CONVERT(\"fkey_info.delete_rule\") \"DELETE_RULE\",\n" +
225  " \"fkey_info.name\" \"FK_NAME\",\n" +
226  " NULL \"PK_NAME\",\n" +
227  " \"fkey_info.deferred\" \"DEFERRABILITY\"\n" +
228  " FROM " + SystemSchema.ForeignKeyInfoTableName + ", " + SystemSchema.ForeignKeyColumnsTableName + "\n" +
229  " WHERE fkey_info.id = fkey_cols.fk_id\n" +
230  " AND \"fkey_info.schema\" IN\n" +
231  " ( SELECT \"name\" FROM INFORMATION_SCHEMA.ThisUserSchemaInfo )\n");
232 
233  query.ExecuteQuery(" CREATE VIEW " + CrossReference + " AS " +
234  " SELECT NULL \"PKTABLE_CAT\",\n" +
235  " \"fkey_info.ref_schema\" \"PKTABLE_SCHEMA\",\n" +
236  " \"fkey_info.ref_table\" \"PKTABLE_NAME\",\n" +
237  " \"fkey_cols.pcolumn\" \"PKCOLUMN_NAME\",\n" +
238  " NULL \"FKTABLE_CAT\",\n" +
239  " \"fkey_info.schema\" \"FKTABLE_SCHEMA\",\n" +
240  " \"fkey_info.table\" \"FKTABLE_NAME\",\n" +
241  " \"fkey_cols.fcolumn\" \"FKCOLUMN_NAME\",\n" +
242  " \"fkey_cols.seq_no\" \"KEY_SEQ\",\n" +
243  " I_FRULE_CONVERT(\"fkey_info.update_rule\") \"UPDATE_RULE\",\n" +
244  " I_FRULE_CONVERT(\"fkey_info.delete_rule\") \"DELETE_RULE\",\n" +
245  " \"fkey_info.name\" \"FK_NAME\",\n" +
246  " NULL \"PK_NAME\",\n" +
247  " \"fkey_info.deferred\" \"DEFERRABILITY\"\n" +
248  " FROM " + SystemSchema.ForeignKeyInfoTableName + ", " + SystemSchema.ForeignKeyColumnsTableName + "\n" +
249  " WHERE fkey_info.id = fkey_cols.fk_id\n" +
250  " AND \"fkey_info.schema\" IN\n" +
251  " ( SELECT \"name\" FROM INFORMATION_SCHEMA.ThisUserSchemaInfo )\n");
252  }
static readonly ObjectName ThisUserSchemaInfoViewName
static readonly ObjectName TablePrivileges
static readonly ObjectName ThisUserGrantViewName
static readonly ObjectName CrossReference
static readonly ObjectName ColumnPrivileges
static readonly ObjectName ThisUserTableInfoViewName
DbObjectType
The kind of objects that can be handled by a database system and its managers
Definition: DbObjectType.cs:27
static readonly ObjectName ThisUserSimpleGrantViewName
static readonly ObjectName ThisUserTableColumnsViewName
static void Deveel.Data.Sql.Schemas.InformationSchema.GrantToPublic ( IQuery  query)
inlinestatic

Definition at line 254 of file InformationSchema.cs.

254  {
255  query.GrantToUserOn(DbObjectType.View, ThisUserSimpleGrantViewName, User.PublicName, Privileges.TableRead);
256  query.GrantToUserOn(DbObjectType.View, ThisUserGrantViewName, User.PublicName, Privileges.TableRead);
257  query.GrantToUserOn(DbObjectType.View, ThisUserSchemaInfoViewName, User.PublicName, Privileges.TableRead);
258  query.GrantToUserOn(DbObjectType.View, ThisUserTableInfoViewName, User.PublicName, Privileges.TableRead);
259  query.GrantToUserOn(DbObjectType.View, ThisUserTableColumnsViewName, User.PublicName, Privileges.TableRead);
260 
261  query.GrantToUserOn(DbObjectType.View, Catalogs, User.PublicName, Privileges.TableRead);
262  query.GrantToUserOn(DbObjectType.View, Schemata, User.PublicName, Privileges.TableRead);
263  query.GrantToUserOn(DbObjectType.View, Tables, User.PublicName, Privileges.TableRead);
264  query.GrantToUserOn(DbObjectType.View, TablePrivileges, User.PublicName, Privileges.TableRead);
265  query.GrantToUserOn(DbObjectType.View, Columns, User.PublicName, Privileges.TableRead);
266  query.GrantToUserOn(DbObjectType.View, ColumnPrivileges, User.PublicName, Privileges.TableRead);
267  query.GrantToUserOn(DbObjectType.View, PrimaryKeys, User.PublicName, Privileges.TableRead);
268  query.GrantToUserOn(DbObjectType.View, ImportedKeys, User.PublicName, Privileges.TableRead);
269  query.GrantToUserOn(DbObjectType.View, ExportedKeys, User.PublicName, Privileges.TableRead);
270  query.GrantToUserOn(DbObjectType.View, CrossReference, User.PublicName, Privileges.TableRead);
271  }
static readonly ObjectName ThisUserSchemaInfoViewName
const string PublicName
The name of the PUBLIC special user.
Definition: User.cs:47
static readonly ObjectName TablePrivileges
static readonly ObjectName ThisUserGrantViewName
static readonly ObjectName CrossReference
static readonly ObjectName ColumnPrivileges
static readonly ObjectName ThisUserTableInfoViewName
DbObjectType
The kind of objects that can be handled by a database system and its managers
Definition: DbObjectType.cs:27
static readonly ObjectName ThisUserSimpleGrantViewName
Provides the information for a user in a database system
Definition: User.cs:27
static readonly ObjectName ThisUserTableColumnsViewName

Member Data Documentation

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.Catalogs = new ObjectName(Name, "catalogs")
static

Definition at line 28 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ColumnPrivileges = new ObjectName(Name, "column_privileges")
static

Definition at line 38 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.Columns = new ObjectName(Name, "columns")
static

Definition at line 36 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.CrossReference = new ObjectName(Name, "cross_reference")
static

Definition at line 48 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.DataTypes = new ObjectName(Name, "data_types")
static

Definition at line 46 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ExportedKeys = new ObjectName(Name, "exported_keys")
static

Definition at line 44 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ImportedKeys = new ObjectName(Name, "imported_keys")
static

Definition at line 42 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.Name = new ObjectName(SchemaName)
static

Definition at line 26 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.PrimaryKeys = new ObjectName(Name, "primary_keys")
static

Definition at line 40 of file InformationSchema.cs.

const string Deveel.Data.Sql.Schemas.InformationSchema.SchemaName = "INFORMATION_SCHEMA"

Definition at line 24 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.Schemata = new ObjectName(Name, "schemata")
static

Definition at line 34 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.TablePrivileges = new ObjectName(Name, "table_privileges")
static

Definition at line 32 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.Tables = new ObjectName(Name, "tables")
static

Definition at line 30 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ThisUserGrantViewName = new ObjectName(Name, "ThisUserGrant")
static

Definition at line 54 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ThisUserSchemaInfoViewName = new ObjectName(Name, "ThisUserSchemaInfo")
static

Definition at line 56 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ThisUserSimpleGrantViewName = new ObjectName(Name, "ThisUserSimpleGrant")
static

Definition at line 52 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ThisUserTableColumnsViewName = new ObjectName(Name, "ThisUserTableColumns")
static

Definition at line 58 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.ThisUserTableInfoViewName = new ObjectName(Name, "ThisUserTableInfo")
static

Definition at line 60 of file InformationSchema.cs.

readonly ObjectName Deveel.Data.Sql.Schemas.InformationSchema.UserPrivileges = new ObjectName(Name, "user_privileges")
static

Definition at line 50 of file InformationSchema.cs.


The documentation for this class was generated from the following file: