B4J Library [Web] Automatic SQLite PHP REST API - Retrieving Database Schema and other things

Hi Fam

There are a couple of threads here that we looked at the PHP REST API for CRUD operations in your Database. The nice thing about this REST API is that you can use it for

• MySQL - we have explored this in detail
• MSSQL
• PostGres
• SQLite - we are exploring this today.

From a single php.api file, you can just plug it in your project and within no time, you are sorted. What I wanted to do was also be able to view the schema of the database as I need this in something else Im working on.

We will update the api.php file to use SQLite.

$config = new Config([
        'driver' => 'sqlite',
        'address' => 'bibleshow.db',
        'port' => '',
        'username' => '',
        'password' => '',
        'database' => '',
        'debug' => false,
        'tables' => 'all',
        'controllers' => 'records,columns,tables,openapi,status',
        'middlewares' => 'sslRedirect,apiKeyAuth,sanitation',
        'apiKeyAuth.keys' => 'oQNg79KwzBEBo9l74CfYjrx1yh3xCNg3033ujGlZPgfPmzMcv0zlbk38!-ndn6730UFn/Ziu',
        'apiKeyAuth.header' => 'X-API-Key',
    ]);

We are using middlewares for apiKeyAuth, sanitation and sslRedirect here. Our database as well as the api.php files are in our assets folder of our website.

Two things are important here

1. driver and
2. address (path to sqlite database)

To ensure that this works, we can test with PostNet. We ensure that the X-API-Key is specified for our connection, else we will get an authentication error message

We have activated some controllers, these being records (access actual records in the database, by default this is on), columns (access the schema of our database)

We will fire this GET with

https://localhost/sdtools/assets/api.php/columns

This will access our database and extract our schema and display it like this..

{
    "tables": [
        {
            "name": "Analysis",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "analysis",
                    "type": "varchar",
                    "length": 254,
                    "nullable": true
                },
                {
                    "name": "title",
                    "type": "varchar",
                    "length": 254,
                    "nullable": true
                },
                {
                    "name": "scripture",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Bible",
            "type": "table",
            "columns": [
                {
                    "name": "Book",
                    "type": "integer",
                    "nullable": true
                },
                {
                    "name": "Chapter",
                    "type": "integer",
                    "nullable": true
                },
                {
                    "name": "Verse",
                    "type": "integer",
                    "nullable": true
                },
                {
                    "name": "Scripture",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Books",
            "type": "table",
            "columns": [
                {
                    "name": "BookID",
                    "type": "clob",
                    "pk": true
                },
                {
                    "name": "BookName",
                    "type": "varchar",
                    "length": 2147483647,
                    "nullable": true
                },
                {
                    "name": "Chapters",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Chapters",
            "type": "table",
            "columns": [
                {
                    "name": "Book",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Chapter",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verse",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "GreatChapters",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "Title",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Scriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "GreatStories",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "Title",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Scriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "GreatVerses",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "Title",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Scriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Intro",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "title",
                    "type": "varchar",
                    "length": 254,
                    "nullable": true
                },
                {
                    "name": "image",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "subtitle",
                    "type": "varchar",
                    "length": 254,
                    "nullable": true
                }
            ]
        },
        {
            "name": "Lessons",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "LessonTitle",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "LessonScriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Life",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "LessonTitle",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "LessonScriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Miracles",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "LessonTitle",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "LessonScriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Parables",
            "type": "table",
            "columns": [
                {
                    "name": "id",
                    "type": "integer",
                    "pk": true
                },
                {
                    "name": "LessonTitle",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "LessonScriptures",
                    "type": "clob",
                    "nullable": true
                },
                {
                    "name": "Verses",
                    "type": "clob",
                    "nullable": true
                }
            ]
        },
        {
            "name": "Pictures",
            "type": "table",
            "columns": [
                {
                    "name": "Key",
                    "type": "varchar",
                    "length": 5,
                    "pk": true
                },
                {
                    "name": "Text",
                    "type": "varchar",
                    "length": 255,
                    "nullable": true
                },
                {
                    "name": "Tag",
                    "type": "clob",
                    "nullable": true
                }
            ]
        }
    ]
}

 

[Mashiane]

CRUD METHODS

Let's get all records from the Analysis Table

https://localhost/sdtools/assets/api.php/records/Analysis

Here is the result.

{
    "records": [
        {
            "id": 1,
            "analysis": "Positive Mental Qualities",
            "title": "Agreeableness",
            "scripture": "1 Corinthians\\10:32-33",
            "verses": "{I}{B}WEB\\1 Corinthians\\10:32-33{/I}{/B}{BR}{BR}32  Give no occasions for stumbling, either to Jews, or to Greeks, or to the assembly of God;\r {BR}33  even as I also please all men in all things, not seeking my own profit, but the profit of the many, that they may be saved.\r {BR}{BR}"
        },
        {
            "id": 2,
            "analysis": "Positive Mental Qualities",
            "title": "Ambition",
            "scripture": "Philippians\\3:13-14",
            "verses": "{I}{B}WEB\\Philippians\\3:13-14{/I}{/B}{BR}{BR}13  Brothers, I don't regard myself as yet having taken hold, but one thing I do. Forgetting the things which are behind, and stretching forward to the things which are before,\r {BR}14  I press on toward the goal for the prize of the high calling of God in Christ Jesus.\r {BR}{BR}"
        },
        {
            "id": 3,
            "analysis": "Positive Mental Qualities",
            "title": "Analysis",
            "scripture": "2 Timothy\\2:15",
            "verses": "{I}{B}WEB\\2 Timothy\\2:15{/I}{/B}{BR}{BR}15  Give diligence to present yourself approved by God, a workman who doesn't need to be ashamed, properly handling the Word of Truth.\r {BR}{BR}"
        },
        {
            "id": 4,
            "analysis": "Positive Mental Qualities",
            "title": "Beauty ",
            "scripture": "Psalms\\27:4",
            "verses": "{I}{B}WEB\\Psalms\\27:4{/I}{/B}{BR}{BR}4  One thing I have asked of Yahweh, that I will seek after, that I may dwell in the house of Yahweh all the days of my life, to see Yahweh's beauty, and to inquire in his temple.\r {BR}{BR}"
        },
        {
            "id": 5,
            "analysis": "Positive Mental Qualities",
            "title": "Charity",
            "scripture": "1 Corinthians\\13:1",
            "verses": "{I}{B}WEB\\1 Corinthians\\13:1{/I}{/B}{BR}{BR}1  If I speak with the languages of men and of angels, but don't have love, I have become sounding brass, or a clanging cymbal.\r {BR}{BR}"
        },
        {
            "id": 6,
            "analysis": "Positive Mental Qualities",
            "title": "Cheerfulness",
            "scripture": "Proverbs\\15:13",
            "verses": "{I}{B}WEB\\Proverbs\\15:13{/I}{/B}{BR}{BR}13  A glad heart makes a cheerful face; but an aching heart breaks the spirit.\r {BR}{BR}"
        },
        {
            "id": 7,
            "analysis": "Positive Mental Qualities",
            "title": "Concentration",
            "scripture": "Philippians\\3:13",
            "verses": "{I}{B}WEB\\Philippians\\3:13{/I}{/B}{BR}{BR}13  Brothers, I don't regard myself as yet having taken hold, but one thing I do. Forgetting the things which are behind, and stretching forward to the things which are before,\r {BR}{BR}"
        },
        {
            "id": 8,
            "analysis": "Positive Mental Qualities",
            "title": "Confidence",
            "scripture": "Hebrews\\10:35",
            "verses": "{I}{B}WEB\\Hebrews\\10:35{/I}{/B}{BR}{BR}35  Therefore don't throw away your boldness, which has a great reward.\r {BR}{BR}"
        },
        {
            "id": 9,
            "analysis": "Positive Mental Qualities",
            "title": "Contentment",
            "scripture": "1 Timothy\\6:6",
            "verses": "{I}{B}WEB\\1 Timothy\\6:6{/I}{/B}{BR}{BR}6  But godliness with contentment is great gain.\r {BR}{BR}"
        },
        {
            "id": 10,
            "analysis": "Positive Mental Qualities",
            "title": "Courage",
            "scripture": "Joshua\\1:7",
            "verses": "{I}{B}WEB\\Joshua\\1:7{/I}{/B}{BR}{BR}7  Only be strong and very courageous, to observe to do according to all the law, which Moses my servant commanded you. Don't turn from it to the right hand or to the left, that you may have good success wherever you go.\r {BR}{BR}"
        },
        {
            "id": 11,
            "analysis": "Positive Mental Qualities",
            "title": "Courtesy",
            "scripture": "1 Corinthians\\13:5",
            "verses": "{I}{B}WEB\\1 Corinthians\\13:5{/I}{/B}{BR}{BR}5  doesn't behave itself inappropriately, doesn't seek its own way, is not provoked, takes no account of evil;\r {BR}{BR}"
        },
        {
            "id": 12,
            "analysis": "Positive Mental Qualities",
            "title": "Decision",
            "scripture": "Joshua\\24:15",
            "verses": "{I}{B}WEB\\Joshua\\24:15{/I}{/B}{BR}{BR}15  If it seems evil to you to serve Yahweh, choose this day whom you will serve; whether the gods which your fathers served that were beyond the River, or the gods of the Amorites, in whose land you dwell: but as for me and my house, we will serve Yahweh.\r {BR}{BR}"
        },
        {
            "id": 13,
            "analysis": "Positive Mental Qualities",
            "title": "Diplomacy",
            "scripture": "2 Corinthians\\12:16",
            "verses": "{I}{B}WEB\\2 Corinthians\\12:16{/I}{/B}{BR}{BR}16  But be it so, I did not myself burden you. But, being crafty, I caught you with deception.\r {BR}{BR}"
        },
        {
            "id": 14,
            "analysis": "Positive Mental Qualities",
            "title": "Discernment",
            "scripture": "1 Kings\\3:9",
            "verses": "{I}{B}WEB\\1 Kings\\3:9{/I}{/B}{BR}{BR}9  Give your servant therefore an understanding heart to judge your people, that I may discern between good and evil; for who is able to judge this your great people?\r {BR}{BR}"
        },
        {
            "id": 15,
            "analysis": "Positive Mental Qualities",
            "title": "Discretion",
            "scripture": "Proverbs\\2:11",
            "verses": "{I}{B}WEB\\Proverbs\\2:11{/I}{/B}{BR}{BR}11  Discretion will watch over you. Understanding will keep you,\r {BR}{BR}"
        },
        {
            "id": 16,
            "analysis": "Positive Mental Qualities",
            "title": "Encouragement",
            "scripture": "1 Samuel\\30:6",
            "verses": "{I}{B}WEB\\1 Samuel\\30:6{/I}{/B}{BR}{BR}6  David was greatly distressed; for the people spoke of stoning him, because the soul of all the people was grieved, every man for his sons and for his daughters: but David strengthened himself in Yahweh his God.\r {BR}{BR}"
        },
        {
            "id": 17,
            "analysis": "Positive Mental Qualities",
            "title": "Enlightenment",
            "scripture": "Psalms\\18:28",
            "verses": "{I}{B}WEB\\Psalms\\18:28{/I}{/B}{BR}{BR}28  For you will light my lamp, Yahweh. My God will light up my darkness.\r {BR}{BR}"
        },
        {
            "id": 18,
            "analysis": "Positive Mental Qualities",
            "title": "Enthusiasm",
            "scripture": "1 Corinthians\\15:10",
            "verses": "{I}{B}WEB\\1 Corinthians\\15:10{/I}{/B}{BR}{BR}10  But by the grace of God I am what I am. His grace which was bestowed on me was not futile, but I worked more than all of them; yet not I, but the grace of God which was with me.\r {BR}{BR}"
        },

There are other methods to perform other CRUD functions as indicated here.

GitHub - mevdschee/php-crud-api: Single file PHP script that adds a REST API to a SQL database

Single file PHP script that adds a REST API to a SQL database - mevdschee/php-crud-api

github.com

 

[byz]

Feedback a problem, PHP8.2 should remove these redundant parts, otherwise it will report an error.
-----------------------------------------------------
'port' => '',
'username' => '',
'password' => '',
'database' => '',
--------------------------------------------------------

$config = new Config([
        'driver' => 'sqlite',
        'address' => 'bibleshow.db',
        'debug' => false,
        'tables' => 'all',
        'controllers' => 'records,columns,tables,openapi,status',
        'middlewares' => 'sslRedirect,apiKeyAuth,sanitation',
        'apiKeyAuth.keys' => 'oQNg79KwzBEBo9l74CfYjrx1yh3xCNg3033ujGlZPgfPmzMcv0zlbk38!-ndn6730UFn/Ziu',
        'apiKeyAuth.header' => 'X-API-Key',
    ]);