-
Notifications
You must be signed in to change notification settings - Fork 5
Database ERD
NOTE: this page is work-in-progress and is subject to change frequently
This page contains details about the database tables that would be a part of the CRUDE-NET project.
In this page:
- True Show of Strength
- crude.Configuration
- crude.DataSource
- crude.DataView
- crude.DataViewPanel
- crude.DataViewField
- crude.DataViewQueryGroup
- crude.DataViewQuery
- crude.DataViewAction
- crude.DataViewActionParameter
- crude.DataViewChart
- crude.Comment
- crude.User
- crude.Role
- crude.PermissionSegment
- crude.UserRoles
- crude.RolePermissions
- crude.AuthProvider
- crude.CustomPage
- crude.NavSection
- crude.Navigation
- crude.FilterOperator
- crude.FieldType
The CRUDE-NET platform is very extensive and contains many database tables needed for its implementation and management. However, a true show of strength of the CRUDE platform, would be to implement the management of these tables using the very platform they're used for implementing.
In other words: Use the CRUD generator platform to manage the CRUD generator platform.
If all functionality needed for the management of these tables can be implemented using the CRUDE platform itself, that would, indeed, be a True Show of Strength of the CRUDE platform, right off the shelf.
(only exception, possibly, would be the management of the navigation menu items, which would be managed using an editable treeview control)
Also, leveraging the crud generator abilities for the administrative sections should tremendously reduce development time and effort, since we won't need to hard-code these sections ourselves.
Controls various site settings.
Each setting would have its own column in this table. Please see Site Settings for list of settings.
Exact structure and implementation of this table is TBD
The data source would be the equivalent of a connection string, defining the source server, authentication and default database. However, this table won't contain the connection string itself. This table could be managed dynamically, but the actual use of it would be by correlating the data from it with a secured configuration file (i.e appSettings.json or web.config or something equivalent). This is so that we won't save secure connection strings inside the database.
In other words, this table would contain identifiers that the controller layer would know to look for in a secure configuration file, to find the actual connection string.
Column Name | Data Type | Description |
---|---|---|
DataSourceId |
Auto Increment | Primary Key |
Label |
String | Textual title or friendly name for the data source |
ConnStringName |
String | The connection string identifier as it is written in the secured configuration file. |
SourceType |
Enum | Determines what kind of data source this is. Would be used for determining the data layer class library to be used. Possible values: |
- Microsoft SQL Server | ||
- Oracle database | ||
- MySQL | ||
- PostgreSQL | ||
- Access database | ||
- More TBD |
More fields are TBD.
Equivalent of Evolutility's form
element.
Each record in this table represents a single data model, with a single underlying database source (either a table/view or a stored procedure).
Column Name | Data Type | Description |
---|---|---|
ViewId |
Auto Increment | Primary Key |
Published |
Boolean | Determines whether this dataview is accessible to end users (otherwise it's only accessible to admins) |
PermissionSegmentId |
Foreign Key | References crude.PermissionSegment |
SlugName |
String(100) | A unique name to be used as a route (slug) to this module. Must be small case in English, without spaces or special characters. Will be used in routing, simplifying URLs. For example: "https://myportaladdress/customers" would be a URL for a data view with "customers" as slug name. |
Title |
String(100) | Used as page title |
Description |
String(max) | Rich free-text displayed on the main component page |
Entity |
String(100) | User's object name for the database object. For example: "contact". Default value: "item" |
Entities |
String(100) | Plural for entity. Default value: "items" |
DataSourceId |
Foreign key | References crude.DataSource. Would be used for getting the connection string identifier name as configured in appSettings.json or web.config's list of connection strings |
DBTable |
String(300) | Name of driving table for the component |
DBPKColumn |
String(255) | Name of the primary key column used as record identifier |
DBUserColumn |
String(255) | In multiple-users environment, specify the column name of the “author column” designating the specific user author of the record. Default is NULL which means the feature is disabled for this component |
DBOrderBy |
String(1000) | List of column names to include in the default "order by" SQL clause |
DBWhere |
String(4000) | SQL where clause to limit the dataset manipulated, must use the alias "T" for the driving table. Example: "T.Deleted <> 1 AND T.DateAdded > GETDATE()-360" |
SPSource |
String(300) | Name of the stored procedure for paging search results. Replaces DBTable as source. Expected parameters: @SQLdeclare, @SQLwhere, @SQLorderby, @PageSize, @PageNumber, @UserId |
SPGetOne |
String(300) | Name of stored procedure for retrieving details of a single record. Expected parameters: @ItemID, @UserID. |
SPDelete |
String(300) | Name of the stored procedure for deleting records. Expected parameters: @ItemID, @UserId. |
SPModify |
String(300) | Name of the stored procedure for updating or inserting records. Expected parameters: @ItemID (null if inserting), @UserID, @Field1, @Field2, etc... (one parameter per each editable field, ordered). |
LOVValueField |
String | If this field is set, it enables this dataview to be used as a list of values. This determines the database column name that should be used as the value. |
LOVTitleField |
String | Determines the database column that should be used as the label in a list of values. If not set, the value field will be used instead. |
LOVGroupField |
String | Determines the database column that should be used as the group label in a list of values. If not set, items won't be grouped. |
LOVGlyphField |
String | Determines the database column that should be used for the glyph icon in a list of values (where supported). If not set, glyph icons won't be used. |
LOVIsDefaultField |
String | Determines the database column (must be of a Boolean type), which would be used in a list of values to determine which item(s) should be selected by default. If not set, no default items would be set. |
LOVTooltipField |
String | Determines the database column to be used as a tooltip in a list of values. If not specified, no tooltips would be used. |
LOVDefaultListType |
Enum | Determines the type of list that would be used, if not overridden explicitly. Possible values: |
- Dropdown list (default, normal Dropdown combo box) | ||
- Radio buttons | ||
- Checkboxes | ||
- Buttons | ||
- Select2 control | ||
- Switches | ||
DTDefaultPageSize |
Int | Sets the default number of records per page. Ignored if pagination is disabled. |
DTPagingStyle |
Enum | Sets how pagination buttons would look like. Ignored if pagination is disabled (more info). Possible values: |
- First, Previous, Next and Last buttons, plus page numbers | ||
- Previous and Next buttons, plus page numbers | ||
- Previous and Next buttons only | ||
- Page number buttons only | ||
- First, Previous, Next and Last buttons | ||
- First and Last buttons, plus page numbers | ||
DTActionButtonStyle |
Enum | Sets how the action buttons of the component would look like. Possible values: |
- Icon and Text | ||
- Icon Only | ||
- Text Only | ||
Flags |
Int | A bitwise multi-value field for various component toggles. Possible values: |
- Allow Edit | ||
- Allow Add | ||
- Allow Delete | ||
- Allow Clone | ||
- Enable Items List (datatable) | ||
DTFlags |
Int | A bitwise multi-value field for various toggles for the datatable. Possible values: |
- Enable Column Search (advanced search) | ||
- Enable Quick-Search (toggles whether to allow quick free-text search on the table) | ||
- Show Info (toggles an info bar indicating how many rows out of what total are currently being showed) (more info) | ||
- Column Footers (toggles whether to show column headers at the bottom of the table as well) | ||
- Sortable Columns (toggles whether to allow users to dynamically sort columns) (more info) | ||
- Pagination (toggles whether to split records into pages based on page size) (more info). If this toggle is turned off, datatable scroller will be used instead (more info). | ||
- Page Size Selection (more info) | ||
- Client-Side State Save (more info) | ||
- Show "Copy to Clipboard" button (more info) | ||
- Show "Export to CSV" button (more info) | ||
- Show "Export to Excel" button (more info) | ||
- Show "Export to PDF" button (more info) | ||
- Show "Print" button (more info) | ||
- Show "Columns Toggle" button (more info) | ||
- Enable Row Details (more info) | ||
- Enable Row Selection (allows bulk actions on selected rows) (more info) | ||
- Enable Fixed Headers (more info) | ||
ViewTemplate |
String | This will determine the HTML template to be used for rendering this dataview. TBD whether this would be a foreign key to another table, or a string path to a template file. |
DBDTRowClassColumn |
String (255) | Optional database column (SQL name) containing the CSS class name to be used as DT_RowClass which automatically sets the class of the TR element. Example value: "table-success". |
Equivalent of Evolutility's panel
, tab
and panel-details
elements.
Panels are used to visually group fields on the screen (in edit and view modes).
Panels can also contain sub-groups of panels (for example, a panel can contain tabs, which in turn can contain more panels etc.).
Alternatively, panels can be configured as "Grid Panels" (equivalent of Evolutility's panel-details
element).
Grid Panels allow for nested sub-entities within the driving entities. In other words, it allows for hierarchical data and adds the feature of master-details. The sub-entity (or detail) can be edited within the page as a grid, or by linking to another page using the sub-entity as its driving entity.
The screenshot below shows an order. The master is composed of 3 panels, one panel-details and another panel for the total.
Grid Panels cannot contain fields or sub-panels.
Column Name | Data Type | Description |
---|---|---|
PanelID |
Auto Increment | Primary Key |
ParentPanelID |
Foreign Key | Optional recursive parent. References crude.DataViewPanel |
PanelGroupContainerType |
Enum | Sets the display type of this panel's sub-panels, determining how they would look like (more info about Bootstrap Navs). Possible values: |
- Panels (i.e. Cards) | ||
- Tabs | ||
- Pills | ||
- Nav Links | ||
- Vertical Tabs | ||
- Vertical Pills | ||
- Vertical Nav Links | ||
- Accordion | ||
- Wizard Steps | ||
PermissionSegmentID |
Foreign Key | References crude.PermissionSegment. Default: NULL (inherit from Panel Group). |
Label |
String(100) | The panel's title to be displayed to users |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-address-card". |
Logo |
String(255) | An optional image file to be used as the panel's "logo" (similar to Glyph). |
Description |
String(max) | A rich-text description to be displayed at the top of the panel |
PanelOrder |
Int | A number representing the order in which the panel should be displayed |
Width |
Int | A number between 1 and 100, representing in percents how much space the panel would take horizontally |
GridViewID |
Foreign Key | If panel is configured as a grid panel: References crude.DataView. Used for configuring the DB table source and fields. |
DBFKColumn |
String(255) | If panel is configured as a grid panel: Sets the database column name in the child data view which would serve as a foreign key to the master data view's primary key column. This column would be automatically filtered while displaying the grid, and it would automatically be filled when adding new records in the grid. |
Flags |
Int | A bitwise multi-value field for various toggles. Possible options: |
- Optional (whether to skip the panel from displaying, if every field contained within it is empty and optional. In View mode only). | ||
- Collapsible (whether to allow users to collapse/expand this panel) | ||
- Closed By Default (if this panel is collapsible, enabling this will make it collapsed by default) | ||
- Grid Panel (whether this panel is to be used as a grid displaying data from another dataview (utilizing the GridViewID and DBFKColumn fields). Note: Grid panels cannot contain fields within them. |
||
CSSPanel |
String | CSS class for the panel element. Default is '' (using base element styling) |
Equivalent of Evolutility's field
element.
The data view fields represent fields on the screen, and database columns at once. It is the most used element and the element with the most attributes. Database columns hidden to the user (like the primary key of the driving table) are not declared.
Column Name | Data Type | Description |
---|---|---|
FieldID |
Auto Increment | Primary Key |
PanelID |
Foreign Key | References crude.DataViewPanel |
FieldLabel |
String(100) | The field title visible to users |
FieldIdentifier |
String (100) | Short text to use as a unique identifier for this field within the dataview. This identifier will be used when replacing placeholders in various expressions. For example: If "MyField" is specified as the identifier, then you can use {{row[MyField]}} as a placeholder for it in other fields. If not specified, it would default to "Field_x" where x would be the FieldId. For example: "Field_123", and then {{row[Field_123]}} would be the placeholder. more info about expression placeholders
|
Glyph |
String(100) | CSS class name to be used as a glyph icon to prepend to the field (optional). Example: "fas fa-at". |
Help |
String(4000) | Help text to be displayed under the field while editing. Can be formatted rich text. |
Tooltip |
String(1000) | Tooltip text to be displayed when hovering over the field while viewing |
Placeholder |
String(1000) | Expression to be used as placeholder while editing this field |
FieldOrder |
Int | Number representing the sort order in which this field will be displayed. |
FieldType |
Enum | The type of the field as described in more details in field types. Possible values: |
- boolean (yes/no) | ||
- integer | ||
- decimal | ||
- text | ||
- textarea | ||
- date | ||
- datetime | ||
- time | ||
- phone | ||
- password | ||
- formula (computed SQL formula, must be read-only) | ||
- html (Rich Text Format) | ||
- image (Path to file) | ||
- document (Path to file) | ||
- lookup (single value from a list of values) | ||
- list of values (multi value comma separated) | ||
- url | ||
DefaultValue |
String(max) | Default value for the field displayed while creating a new record |
Format |
String(100) | Format pattern regular expression. |
MaxLength |
Int | Maximum number of characters allowed for the field (relevant to most field types) |
Height |
Int | Height property for certain field types: textarea , html , image and multi-selection dropdown |
Width |
Int | Width of the field in percentage of the Panel it belongs to. |
Flags |
Int | A bitwise multi-value field used for setting various toggles. Possible options: |
- Required | ||
- Read Only | ||
- Show in Form | ||
- Show in Items List | ||
- Show in View | ||
- Show in Data Table Details Row (if enabled) | ||
- Allow in Quick Search | ||
- Allow in Advanced Search | ||
- Sortable | ||
URLPath |
String(4000) | Expression to be used as URL path. Turns the field text into a clickable link. The expression can contain field placeholders. For example: {{row[Field_3]}} or {{this}} (the placeholder {{this}} is a special placeholder which will be replaced by the data value of the current field, i.e DBColumn ). |
CSSViewLabel |
String | CSS class for this field's label element while viewing a single item. Default: "" (base styling of text) |
CSSEditLabel |
String | CSS class for this field's label element while editing or adding a single item. Default: "" (base styling of label element) |
CSSCell |
String | CSS class for how the field would look like in the data table (TBD: using either a span or div tag). Default value: "" which means base text styling, and in case of url field types default is: "btn btn-link". |
CSSColumnHeader |
String | CSS class for this field's column header element in the data table. Default: "" (base styling of th element) |
DBColumn |
String(255) | Database source column (SQL name) for the field |
DBColumnImage |
String(255) | Optional database column (SQL name) containing the filename of the image to display for this field. |
DBColumnGlyph |
String(255) | Optional database column (SQL name) containing the CSS class name to use as a glyph for this field. |
DBDTCellClassColumn |
String (255) | Optional database column (SQL name) containing the CSS class name which sets the class of the cell in the data table. Example values: "alert alert-danger", "text-success", "text-warning". |
LOVViewId |
Foreign key | References crude.DataView. To be used for displaying values for list of values field types (lookup and multi value) |
LOVType |
Enum | The display type of the list of values. Possible options: |
- Default (based on dataview default list of values type) | ||
- Dropdown combo box | ||
- Radio buttons | ||
- Checkboxes | ||
- Buttons | ||
- Switches | ||
LOVIsMultiple |
Boolean | Determines whether single value or multi value |
Equivalent of Evolutility's queries
also know as "Selections"
It is possible to add canned queries as a "Selections" button on the toolbar.
Queries are also grouped into Query Groups.
Column Name | Data Type | Description |
---|---|---|
QueryGroupID |
Auto Increment | Primary Key |
ViewID |
Foreign Key | References crude.DataView |
Label |
String(255) | Group title |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-archive". |
Description |
String(max) | Introduction rich-text displayed above the list of queries. |
GroupOrder |
Int | Number determining the order in which the query group will be displayed within the list of groups (if there's more than one). |
Equivalent of Evolutility's query
used within "Selections".
Column Name | Data Type | Description |
---|---|---|
QueryID |
Auto Increment | Primary Key |
QueryGroupID |
Foreign Key | References crude.DataViewQueryGroup |
Label |
String(255) | Query title |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-exclamation-circle". |
QueryOrder |
Int | Number determining the order in which the query will be displayed within the list. |
QueryIdentifier |
String(50) | Key used to identity each query within the list. Each key can be used in links within the page to force the control to display the corresponding query. Example: Assuming "sfo" is a QueryIdentifier you entered for one of the queries, a link to it would look like this: "<a href`"javascript:__doPostBack('evo1','q:sfo')">Restaurants in San Francisco" |
DBWhere |
String(4000) | SQL Where clause for the query (will be appended to the regular query of the parent data view) |
Each dataview can have, in addition to its data displayed, also a set of customizable "action buttons".
Action buttons could be placed either at the top of the page, and even be formatted as a hierarchical menu, or as contextual buttons per each row in the datatable (right next to the Edit/Clone/Delete buttons).
After activating an action, a modal will be displayed with the text string returned as response (except for http links which would simply open the URL).
Column Name | Data Type | Description |
---|---|---|
ActionID |
Auto Increment | Primary Key |
ViewID |
Foreign Key | References crude.DataView |
PermissionSegmentID |
Foreign Key | References crude.PermissionSegment. Default: NULL (inherit from DataView). |
IsPerRow |
Boolean | Determines whether this action button is per each row, or otherwise as a button on the toolbar. |
ParentActionID |
Foreign Key | References crude.DataViewAction (recursive) |
ActionType |
Enum | Determines the type of action. Possible values: |
- HTTP link | ||
- DB SQL Command | ||
- DB SQL Procedure | ||
- DataTable Javascript Function | ||
Label |
String(100) | Text to be displayed as the button label |
Glyph |
String | CSS class name for this button's glyph icon (optional). Example: "fas fa-globe" |
Tooltip |
String(200) | Tooltip to be displayed when hovering over this action button |
Description |
String(max) | This is a rich-text to be displayed in the pop-up modal (relevant to parameterized) |
CSSButton |
String(100) | A CSS class name to be used as the a element's class attribute. Default: "btn btn-primary btn-sm" |
ActionOrder |
Int | Number determining the order in which the action button will be displayed. |
Flags |
Int | A bitwise multi-value field used for setting various toggles. Possible values: |
- Require Confirmation (determines whether to display confirmation modal before executing the action) | ||
- Open In New Window (relevant to HTTP links, determines whether to open in a new window) | ||
ActionExpression |
String(4000) | Determines the behavior of this action. |
- If ActionType is HTTP Link, this expression will be used as URL path. | ||
- If ActionType is DB SQL Command, this expression will be used as the command to be executed. | ||
- If ActionType is DB SQL Procedure, this expression will be used as the stored procedure name to be executed. | ||
- If ActionType is DataTable Javascript Function, this expression will be used as the Javascript function body (more info) | ||
CSSBtn |
String | CSS class name for the button element. Default: "btn btn-primary". |
Action parameters can be configured for all action types (HTTP Link, DB SQL Command, DB SQL Procedure, JavaScript function). A modal dialog will be displayed for setting parameter values before execution, and/or use pre-configured "default" values.
Parameters for http links would be appended to the URL querystring. DB Commands and procedures would receive parameters as SQL parameters, and JavaScript functions would also receive these parameters accordingly.
Column Name | Data Type | Description |
---|---|---|
ActionParameterID |
Auto Increment | Primary Key |
ActionID |
Foreign Key | References crude.DataViewAction |
ParamName |
String(255) | System name of the parameter. |
Label |
String(100) | The parameter label visible to users |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-credit-card". |
Help |
String(4000) | Help text to be displayed under the parameter. Can be formatted rich text. |
Tooltip |
String(1000) | Tooltip text to be displayed when hovering over the parameter |
Placeholder |
String(1000) | Expression to be used as placeholder |
ParamOrder |
Int | Number representing the order in which this parameter will be displayed. |
ParamType |
Enum | The type of the parameter as described in more details in field types. Possible values: |
- boolean (yes/no) | ||
- integer | ||
- decimal | ||
- text | ||
- textarea | ||
- date | ||
- datetime | ||
- time | ||
- phone | ||
- password | ||
- html (Rich Text Format) | ||
- image (Path to file) | ||
- document (Path to file) | ||
- lookup (single value from a list of values) | ||
- list of values (multi value comma separated) | ||
DefaultValue |
String(max) | Default value for the parameter |
Format |
String(100) | Format for parameters of type boolean , date , datetime , time , decimal , or integer . Examples: "$'#,##0.00", "YYYY-MM-DD"
|
MaxLength |
Int | Maximum number of characters allowed for the parameter |
Height |
Int | Height property for certain parameter types: textarea , html , image and multi-selection dropdown
|
Width |
Int | Width of the parameter, in percent (between 1 and 100) |
Flags |
Int | A bitwise multi-value field used for setting various toggles. Possible values: |
- Required | ||
- Read Only | ||
- Hidden | ||
CSSLabel |
String | CSS class name for this parameter's label. Default: "" (base styling of label element) |
LOVViewId |
Foreign key | References crude.DataView. To be used for displaying values for list of values field types (lookup and multi value) |
LOVType |
Enum | The display type of the list of values. Possible options: |
- Default (based on dataview default list of values type) | ||
- Dropdown combo box | ||
- Radio buttons | ||
- Checkboxes | ||
- Buttons | ||
- Select2 control | ||
- Switches | ||
LOVIsMultiple |
Boolean | Determines whether single value or multi value |
Configures the set of charts that would be available for a data view.
Defines the type of chart (line, area, pie, bubble, bars, etc.) and the various dimensions as needed (x, y, z) mapped to database columns or expressions.
TBA
Equivalent of Evolutility's EVOL_Comment
.
TBA
Used for authentication, registration and profiles.
Equivalent of evolutility's EVOL_User
. Also comparable to aspnet_Users.
Passwords are saved as one-way hash values, with a salt randomly generated per each user/password.
Column Name | Data Type | Description |
---|---|---|
UserID |
Auto Increment | Primary Key |
Active |
Boolean | Whether the user account is enabled or not |
IsAdmin |
Boolean | Whether the user has master admin permissions |
Login |
String(50) | The username used during authentication |
HashSalt |
String(25) | A random character string uniquely generated per each user password. Used for adding complexity to the password hash |
PasswordHash |
Binary | A one-way hash result of the user's password, together with the HashSalt . i.e. HASH(Clear Text Password + HashSalt) ` PasswordHash |
Email |
String(100) | The user's email address, used for registration and password reset |
Phone |
String(20) | The user's phone number, might be used for 2-factor authentication (if implementation allows) |
Flags |
Int | A bitwise collection of various toggles for this user. Possible values TBD |
ProfileFields |
Json/XML | A dynamic collection of user profile fields, either in JSON or XML format. e.g. Address, City, Country, First and Last Name, Notes, etc. |
I'm not sure yet what's the best way to go about the extendable user's profile fields. Whether to save them in a single document column (JSON or XML), or as regular relational table columns. Also, there may be more columns TBA.
Various roles such as user, moderator, admin, and any other custom role.
Comparable to aspnet_Roles.
Column Name | Data Type | Description |
---|---|---|
RoleID |
Auto Increment | Primary Key |
Title |
String | Name for the role |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-user-cog". |
Description |
String | Description for the role |
Enabled |
Boolean | Sets whether this role is enabled or not. Can be used for toggling access of entire groups of users, based on their role. |
A logical group of site objects and sections (i.e. data views, navigation menus, etc.) to be used for setting permissions.
Column Name | Data Type | Description |
---|---|---|
PermissionSegmentID |
Auto Increment | Primary Key |
Title |
String | Name for the segment |
Description |
String | Description for the segment |
Enabled |
Boolean | Sets whether this segment is enabled or not. Can be used for shutting off entire sections of the portal. |
Mapping between users and roles. A user must be mapped to at least one role in order to have any sort of permissions.
Column Name | Data Type | Description |
---|---|---|
UserRoleID |
Auto Increment | Primary Key |
UserID |
Foreign Key | References crude.User |
RoleID |
Foreign Key | References crude.Role |
Determines the permissions configured per each role, for each permission segment.
Column Name | Data Type | Description |
---|---|---|
PermissionID |
Auto increment | Primary key |
RoleID |
Foreign key | References crude.Role |
SegmentID |
Foreign key | References crude.PermissionSegment |
PublicGrantSet |
Int | A bitwise multi value used for toggling for which operations the role is granted permission on all records . Possible values: |
- View | ||
- Create | ||
- Read | ||
- Update | ||
- Delete | ||
- Execute | ||
- Charts | ||
- Queries | ||
- More (TBD) | ||
PublicDenySet |
Int | A bitwise multi value used for toggling for which operations the role is denied permission on all records . Possible values: |
- View | ||
- Create | ||
- Read | ||
- Update | ||
- Delete | ||
- Execute | ||
- Charts | ||
- Queries | ||
- More (TBD) | ||
PrivateGrantSet |
Int | A bitwise multi value used for toggling for which operations the role is granted permission for records created by current user . Possible values: |
- View | ||
- Create | ||
- Read | ||
- Update | ||
- Delete | ||
- Execute | ||
- Charts | ||
- Queries | ||
- More (TBD) | ||
PrivateDenySet |
Int | A bitwise multi value used for toggling for which operations the role is denied permission on records created by current user . Possible values: |
- View | ||
- Create | ||
- Read | ||
- Update | ||
- Delete | ||
- Execute | ||
- Charts | ||
- Queries | ||
- More (TBD) |
A table holding settings of various SSO authentication service providers (i.e. OAuth and OAuth2), such as: Facebook, Google, Twitter, Office365, LinkedIn, Yammer, etc.
Column Name | Data Type | Description |
---|---|---|
ProviderID |
Auto Increment | Primary Key |
ProviderName |
String | Name of the service provider |
Enabled |
Boolean | Sets whether to allow users to login using this service provider |
LogoImage |
String | Path to service provider's logo image |
IconImage |
String | Path to service provider's icon image |
ClientID |
String | Site/Authentication Key or Client ID |
ClientSecret |
String | Secret Key or Client Secret |
RedirectURLSuccess |
String | Redirect URL to return page after successful login (optional, defaults to current page) |
RedirectURLFail |
String | Redirect URL to return page after failed login (optional) |
ResponseType |
Enum | Sets the "response_type" OAuth2 parameter. Possible values: |
- None (if using OAuth instead of OAuth2, for example) | ||
- Code | ||
- Token | ||
Scope |
String | Sets the "scope" OAuth2 parameter. One or more scope values indicating which parts of the user's account we wish to access. |
Flags |
Int | Bitwise multi-value field for various toggles (possible options TBD, for future use) |
I'm not actually experienced with OAuth implementation so I don't know what other fields may be needed here.
Custom pages are rich text format pages used as non data bound web pages.
Theoretically, this entity may not be needed, if one was to create a data view without any data binding, instead only using the rich text format description of the data view.
Column Name | Data Type | Description |
---|---|---|
PageId |
Auto Increment | Primary Key |
PermissionSegmentID |
Foreign Key | References crude.PermissionSegment |
Slug |
String | Unique string name to be used for routing. Must be unique across all pages as well as across all dataview controller names. |
Title |
String | Main heading to display at top of page |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-question-circle". |
Content |
String(max) | HTML Rich-text format contents for the page |
Published |
Boolean | Sets whether this page is visible to visitors, or is hidden as a draft. |
SEOTitle |
String | SEO meta title |
SEODescription |
String | SEO meta description |
SEOTags |
String | SEO meta tags |
SEOAuthor |
String | SEO meta author |
A logical "super-group" of navigation items.
Each Navigation Section would be displayed as a top-navbar menu-item.
Clicking on a top-navbar menu item would open a page with the side-navbar menu changing its contents to fit the Navigation items belonging to the selected navigation section.
Column Name | Data Type | Description |
---|---|---|
SectionId |
Auto Increment | Primary Key |
PermissionSegmentId |
Foreign Key | References crude.PermissionSegment |
Label |
String(100) | Label for the menu item to be displayed to visitors |
Glyph |
String(100) | CSS class name to be used as a glyph icon (optional). Example: "fas fa-question-circle". |
SectionOrder |
Int | Number used for determining this section's sort order in the top-navbar, if there's more than one section. |
DefaultPage |
String(200) | A URL to be used for this section, when clicking on the related top-navbar menu item. |
Equivalent of CRUDE-ASP's Navigation
.
Each navigation item would belong to a specific NavSection.
Column Name | Data Type | Description |
---|---|---|
NavID |
Auto Increment | Primary Key |
SectionID |
Foreign Key | References crude.NavSection |
ParentNavId |
Foreign Key | References crude.Navigation (recursive) |
Label |
String(100) | Item text to be displayed to users |
NavOrder |
Int | Number determining the sorting order of the menu items |
Glyph |
String | CSS class name used as a glyph icon for this menu item. Example: "fa fa-folder" |
Tooltip |
String(100) | Text to be shown as tooltip when hovering over this menu item |
NavUri |
String(1000) | A specific URL to be opened by clicking this menu item. If points to a local page in the portal, that would be automatically detected and the menu item would be highlighted when its corresponding page is opened. |
ViewID |
Foreign Key | References crude.DataView. If set, will open the specific data view. |
PageID |
Foreign Key | References crude.CustomPage. If set, will open the specific custom page. |
OpenUriInIFRAME |
Boolean | Determines whether to open this menu item's target inside an IFRAME element. |
The NavUri, ViewID and PageID fields are all optional, and are mutually exclusive.
If more than one is set anyway, the one chosen will be based on the following priority order: NavUri, ViewID, PageID.
In either case, if this menu item is also the parent of another menu item, no link will be created for this item. Instead, it will serve exclusively as a parent tree node, clicking which would show or hide its sub-nodes.
Based on the Dynamic Filters solution, to be used for advanced search.
Defines a set of filter operators (equals, not equals, larger than, smaller than, in, contains, starts with...), whether they're a multi value operator or not, and their respective database expression template.
For example, the template for "starts with" is:
{Column} LIKE {Parameter} + '%'
The template for "in" is:
{Column} IN (SELECT [value] FROM {Parameter})
Column Name | Data Type | Description |
---|---|---|
OperatorID |
Auto Increment | Primary Key |
Identifier |
String | Unique identifier for this operator to be used inside the code. |
Label |
String | Text to show users when selecting operators |
Glyph |
String | CSS class name used as a glyph icon for this operator. Example: "fa fa-not-equal" |
MultiValue |
Boolean | Sets whether parameters affected by this operator should be treated as multi-valued (i.e. table variable with a single value column). |
DBTemplate |
String | Defines the database expression template for this operator. {Column} and {Parameter} can be used as palceholders within the template. For example, the template for "starts with" is: "{Column} LIKE {Parameter} + '%'" and the template for "in" (which is a multi-value operator), is: "{Column} IN (SELECT [value] FROM {Parameter})". |
Shared lookup table between field types and parameter types.
Will also define which filter operators are valid for which data types, and which types can be used by action parameters.
Depending on whether this is possible in the programming language, this table may also contain information about how the data type may be implemented (various templates for the view layer, validations, etc.). If this is possible, it would allow us to very easily extend the list of data types. An alternative would be to use a standardized file naming convention for loading partial files implementing the functionality of each type.
More info about custom field types.
Column Name | Data Type | Description |
---|---|---|
TypeID |
Auto Increment | Primary Key |
Identifier |
String | Unique identifier for this type to be used inside the code. |
Label |
String | Text to show users when selecting types |
AllowedOperators |
String | A multi-value field (comma-separated) of filter operators supported by this type |
More fields may be added as needed, based on the programming language and implementation.
Copyright © 2019 Eitan Blumin. All Rights Reserved.