At the core of the PnPBase stays the understanding that for most database driven applications the structure of the application's database acts like some sort of a DNA for the application itself.
In order to illustrate the capabilities of PnPBase will take as an example an application intended to manage users, users groups (teams) and their assets. At the minimum level of detail the following database structure should be created.
A database structure typically consists of various tables, views, stored procedures. In our case we will define 3 tables and 1 view. Further customization like triggers and user defined functions are part of the database design but PnPBase role is not to expose or use them directly.
Of course, various method calls of PnPBase, like POST, PATCH, DELETE might trigger certain triggers, and certain user defined functions might be called for example in a VIEW "SELECT" definition, but PnPBase is agnostic in regard with these database features.
Used to store information about users, like: first name, last name, username, which team they belong to.
| Field name | Type | Null | Xtra | Foreign Key |
|---|---|---|---|---|
| id | INT | NO | PRIMARY KEY | - |
| username | TEXT | NO | UNIQUE | - |
| password | TEXT | NO | - | - |
| first_name | INT | NO | - | - |
| last_name | INT | NO | - | - |
| team | INT | YES | - | teams . id |
Used to store information about team, like: team name, team leader id
| Field name | Type | Null | Xtra | Foreign Key |
|---|---|---|---|---|
| id | INT | NO | PRIMARY KEY | - |
| name | TEXT | NO | UNIQUE | - |
| teamleader | INT | YES | - | users . id |
Used to store information about assets, like: type (laptop, phone, badge etc), make/brand and model, serial number , owner id.
| Field name | Type | Null | Xtra | Foreign Key |
|---|---|---|---|---|
| id | INT | NO | PRIMARY KEY | - |
| make | TEXT | NO | UNIQUE | - |
| model | TEXT | NO | - | - |
| type | INT | NO | - | - |
| owner | INT | NO | - | users . id |
Because we want to check how many members each team has, we will create a VIEW using the following SQL:
CREATE VIEW teams_count AS
SELECT team,count(*) AS cnt FROM users
GROUP BY team;
| Field name | Type | Null | Xtra | Foreign Key |
|---|---|---|---|---|
| team | INT | NO | - | - |
| cnt | INT | NO | - | - |
Using the setup script, we will generate a configuration file which enables the following API endpoints:
- /users
- /users/$user_id
- /users/$user_id/assets
- /users/$user_id/assets/$asset_id
- /users/$user_id/teams
- /teams/
- /teams/$team_id
- /teams/$team_id/teamleader
- /teams/$team_id/users
- /teams/$team_id/users/$user_id
- /assets
- /assets/$asset_id
- /assets/$asset_id/owner
- /teams_count/
As one might notice there is a pattern in the generated endpoints names. First segment takes the name of the table or view being exposed. Second follows the ID of the record, while the third segment identifies the relation by using either the foreign key field name as relation identifier for 1:1 relations, or the linked table name for 1:n relationships.
This might come handy for most of the cases, but sometimes one might wish, or it might be needed, to change the relation name. For such situations the relation name can be changed by editing accordingly the generated PnPBase DB configuration.
There are certain situations when the setup script is not able to detect various features of the data source. Such situations include unique ID fields in the case of view or relationships between views and table.
As it is the case in our example, the VIEW "teams_count" contains the field "team" which we can see from the VIEW definition that it is unique and can be used as an ID field. Therefore we will modify the generated PnPBase DB configuration file to reflect the fact that key field for VIEW "teams_count" is "team".
In the second place, the field "team" can be considered a foreign key pointing to field ID of table "teams". Again, by modifying the generated PnPBase DB configuration file one can declare the 1:1 relationship between VIEW "teams_count" and TABLE "teams"
Endpoint for table users to create and retrieve records
Methods:
Retrieve records from table users
- query parameters:
- filter: comma separated list of filtering criteria. Provided criteria are combined together with a logical AND
- sort[users]: comma separated list of fields to be used as sort criteria the records
- fields[users]: comma separated list of fields to include in the returned result. By default it will include all fields
- include: comma separated list of related data to include. Possible values: assets, teams
- where: alternate to "filter" parameter allowing to define more complex filtering conditions
- responses:
- 200 OK
- 400
Create new users
- body data:
- description: JSON:API document object containing one or more
- syntax:
- example:
- responses:
Methods:
Retrieve user by ID
- path parameters
- $id - user ID as it is stored in field id of table users which is marked as PRIMARY KEY
- query parameters
- include
- fields
Update user identified by ID
- path parameters
- $id - user ID as it is stored in field id of table users which is marked as PRIMARY KEY
Endpoint for table assets to create and retrieve records related to a record from table users identified by ID
Methods:
Methods:
Endpoint for table assets to edit and retrieve records related to a record from table users identified by ID
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods:
Methods: