Update Model.php

[Andrewsuares]

Added config params for sqlite.

[DaFa66]

Straight to the point sometimes isn't the best approach - this code will cause the previous version of Trongate using a MySQL database to break - check out my version on the help_bar https://trongate.io/help_bar_threads/display/FK96dUe9y55H/2

** A slightly updated version here: note the order in which the switch statement is performed; making MySQL/ MariaDB the default will shave a few thousand of a second off runtime and also allow other drivers to be added at no extra cost to execution time.

database.php

<?php

/**
 * Database settings
 * 
 * Ensure the appropriate extension is enabled in php.ini and restart your server.
 * 
 * Allowed database driver types for 'DB_TYPE':
 * 
 * 'mysql'  - MySQL and MariaDB
 *          - default port 3306
 *          - php.ini - extension=pdo_mysql
 * 
 * 'pgsql'  - PostgreSQL
 *          - default port 5432
 *          - php.ini - extension=pdo_pgsql
 * 
 * 'sqlite' - SQLite 3 and SQLite 2
 *          - php.ini - extension=pdo_sqlite
 *          - ensure to include file extension (example '{app_name}.db')
 */
define('HOST', '127.0.0.1');
define('PORT', '3306');
define('USER', 'root');
define('PASSWORD', '');
define('DATABASE', '');
define('DB_TYPE', 'mysql'); 

Model.php

<?php
class Model {

    private $host = HOST;
    private $port = '';
    private $user = USER;
    private $pass = PASSWORD;
    private $dbname = DATABASE;
    private $driver = '';

    private $dbh;
    private $stmt;
    private $error;
    private $debug = false;
    private $query_caveat = 'The query shown above is how the query would look <i>before</i> binding.';
    private $current_module;

    public function __construct($current_module = null) {

        if (DATABASE == '') {
            return;
        }

        // v1 forever - legacy support for older versions
        $this->port = (defined('PORT') ? PORT : '3306'); // default to port 3306 for mysql
        $this->driver = (defined('DB_TYPE') ? DB_TYPE : 'mysql'); // default to mysql driver

        $this->current_module = $current_module;

        switch ($this->driver) {
            case 'mysql':
            case 'pgsql':
                $dsn = $this->driver . ':host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbname;
                break;
            case 'sqlite':
                $dsn = 'sqlite:' . APPPATH . $this->dbname;
                break;
            default:
                throw new Exception('Unsupported database type: ' . $this->driver);
        }

        $options = array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        );

        try {
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            echo $this->error . ' for database type: ' . $this->driver;
            die();
        }
    }

[DaFa66]

I've also discovered that while SQLite & PostgreSQL can use the same basic CRUD calls as MySQL/ MariaDB, it doesn't support 'SHOW TABLES' or 'DESCRIBE', which is used in the API Explorer and a few other locations in the framework. I've managed to work through these issues and written fixes. I'll keep searching for other anomalies before create a complete pull request for using these database drivers. Happy to jump on a Skype call DC to discuss any concerns.

[DaFa66]

*** UPDATE The previous solution I implemented appears to be functioning well with MySQL, MariaDB, PostgreSQL, and SQLite, allowing for basic CRUD operations with these database driver types.

However, there are compatibility issues with certain SQL calls within the framework, which were originally designed for MySQL. Implementing these calls would likely cause more problems than benefits. Therefore, I strongly advise against merging this solution into Trongate. While it is technically feasible, the potential advantages may not outweigh the effort for the limited number of users who would benefit from it.

Last night, I dedicated several hours to enabling SQLite integration with the API Explorer, and it proved to be a straightforward process that required only a few modifications. However, PostgreSQL poses a different challenge altogether, as it presents numerous incompatibilities with MySQL. Consequently, incorporating PostgreSQL into the framework would be ill-advised.

[Andrewsuares]

However, there are compatibility issues with certain SQL calls within the framework, which were originally designed for MySQL. Can't we find all these calls and change them to get it to work.

I would suggest that DB_TYPE not support pgsql. Allow all the DB_TYPES that work. Why hold back a feature just because pgsql does not work. I find it cumbersome to maintain a separate version of model.php to use sqlite.

Thanks Simon!

[trongate]

Surely this would be perfect for a module.

Why not make modules for each of the other database types? Wouldn't that be better?

DC

On Tue, 23 May 2023, 04:41 Andrew Suares, @.***> wrote:

However, there are compatibility issues with certain SQL calls within the framework, which were originally designed for MySQL. Can't we find all these calls and change them to get it to work.

I would suggest that DB_TYPE not support pgsql. Allow all the DB_TYPES that work. Why hold back a feature just because pgsql does not work. I find it cumbersome to maintain a separate version of model.php to use sqlite.

Thanks Simon!

— Reply to this email directly, view it on GitHub https://github.com/trongate/trongate-framework/pull/148#issuecomment-1558465508, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABAUDVLSW5DBSWM5VD7OBNTXHQWWXANCNFSM6AAAAAAYJFBVXY . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[DaFa66]

DC, since our chat about doing modules for each db driver; I've had a think about it, and I'm not so sure that will solve this addition as there are so many inbuilt SQL calls throughout the framework for MySQL and doner code generated by the Desktop app. The only real solution is integrating it, as I have started. SQLite is working OK but I've yet had time to do a complete and thorough test of it. I'm pretty sure they all will work, even PostgreSQL but it would mean understanding each syntax and adding switch statements to swap in the right one.

What I might do is create a branch on my repo, test it and see what everyone thinks.

• UPDATE - compare

  Andrew, could please list the benefits 'you' see in adding SQLite as an alternative to MySQL? as this was a topic DC and I was discussing. Remember our number one priority for Trongate is STABILITY.

[Andrewsuares]

Hi Simon!

In the project I'm working on. MySQL is 'too much' of a database. Its just bits of text that are dynamic that need to be edited. No user management, no analytics, none of the usual stuff. SQLite is perfect for this scenario. Its a single file to backup, reads very fast and is overall lightweight.

I just want to be able to switch over to SQLite in the database config for new projects. If the other db drivers require changes, I think those can be implemented in future.

Thanks!

[delboy1978uk]

If you are just updating bits of dynamic text and a DB is too much, would is it not just be easier using $json = \json_encode($array) and then \file_put_contents('/path/to/some.json', $json); ?

Then you can just do the opposite when you need it, file get contents and json decode?

[Andrewsuares]

Hello,

I meant that the body is edited by a user. The content is edited. Its not a search/replace.

[DaFa66]

Not sure what you mean in your last post Andrew... But as Derek said, using a JSON-structured local file would serve the purpose.

I've been spending some time working on how we could integrate other db drivers into Trongate and I have to say it would not be worth it. The Desktop app would need to be rewritten along with many tweaks to have it function as MySQL does now. I'm not saying you can't use other databases, you just need to keep that code separate from the core CRUD of Trongate. I've found SQLite is the most compatible and I have that working about 98% with Desktop-app generated code. You are welcome to clone my repo on branch 'database-options' and take it further. I like the idea of using SQLite but as a separate module and custom-coded modules.

For reference, here are the default Trongate tables in SQLite format:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "trongate_administrators" (
    "id"    INTEGER NOT NULL,
    "username"  varchar(65) DEFAULT NULL,
    "password"  varchar(60) DEFAULT NULL,
    "trongate_user_id"  int(11) DEFAULT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "trongate_comments" (
    "id"    INTEGER NOT NULL,
    "comment"   text DEFAULT NULL,
    "date_created"  int(11) DEFAULT 0,
    "user_id"   int(11) DEFAULT NULL,
    "target_table"  varchar(125) DEFAULT NULL,
    "update_id" int(11) DEFAULT NULL,
    "code"  varchar(6) DEFAULT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "trongate_tokens" (
    "id"    INTEGER NOT NULL,
    "token" varchar(125) DEFAULT NULL,
    "user_id"   int(11) DEFAULT 0,
    "expiry_date"   int(11) DEFAULT NULL,
    "code"  varchar(3) DEFAULT '0',
    PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "trongate_user_levels" (
    "id"    INTEGER NOT NULL,
    "level_title"   varchar(125) DEFAULT '',
    PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "trongate_users" (
    "id"    INTEGER NOT NULL,
    "code"  varchar(32) DEFAULT NULL,
    "user_level_id" int(11) DEFAULT 0,
    PRIMARY KEY("id" AUTOINCREMENT)
);
INSERT INTO "trongate_administrators" ("id","username","password","trongate_user_id") VALUES (1,'admin','$2y$11$SoHZDvbfLSRHAi3WiKIBiu.tAoi/GCBBO4HRxVX1I3qQkq3wCWfXi',1);
INSERT INTO "trongate_user_levels" ("id","level_title") VALUES (1,'admin');
INSERT INTO "trongate_users" ("id","code","user_level_id") VALUES (1,'ZpTeSgXMG8gmWFm8xzP9ftbSFALhEfMt',1);
COMMIT;```

Good luck :)