We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Store Object to Database: Support Mysql, Postgress, SQLite

DB's:

MySQL:

CREATE TABLE `config` (
  `collection` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'Primary Key: Config object collection.',
  `name` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'Primary Key: Config object name.',
  `data` longblob COMMENT 'A serialized configuration object data.',
  PRIMARY KEY (`collection`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='The base table for configuration data.'

Postgres:

CREATE TABLE public.config
(
  collection character varying(255) NOT NULL DEFAULT ''::character varying, -- Primary Key: Config object collection.
  name character varying(255) NOT NULL DEFAULT ''::character varying, -- Primary Key: Config object name.
  data bytea, -- A serialized configuration object data.
  CONSTRAINT config____pkey PRIMARY KEY (collection, name)
)

SQLite:

CREATE TABLE config (
    collection VARCHAR(255) NOT NULL DEFAULT '',
    name VARCHAR(255) NOT NULL DEFAULT '',
    "data" BLOB(2000000000) DEFAULT NULL,
    CONSTRAINT CONFIG_PK PRIMARY KEY (collection,name)
);

CREATE UNIQUE INDEX sqlite_autoindex_config_1 ON config (collection,name);

I want a compatible code: to save and retrieve (BLOB, BYTEA) data.

Any ideas?



79.0k
edited Sep '16

With many RDBMS out there, that's quite difficult to achieve. ORM is made for that, to abstract low level data types. Have you tried with Phalcon ORM? Do you actually need to be able to create tables from your app at first place?

With NoSQL databases it might be easier, since you can work with standard JSON as a bridge between different systems.



40.1k

@stamster very good recommendation, but I just want to keep objects 'Phalcon / Config' in the database to be able to merge and save some cache.

According to my tests:

you need 'stream_get_contents' and 'unserialize' to postgress and sqlite unserialize only for mysql.

Example:

$conn1 = new PDO('mysql:host=127.0.0.1;dbname=db_drupal_commerce', 'root', 'root');
    $conn1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = $conn1->prepare("select * from config where name = 'core.extension'");
    $sql->execute();
    $resultado = $sql->fetchAll();
    foreach ($resultado as $row) {
        d(unserialize($row["data"]));
    }

    $conn2 = new PDO('pgsql:host=127.0.0.1;dbname=db_drupal', 'xerron', 'root');
    $conn2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = $conn2->prepare("select * from config where name = 'core.extension'");
    $sql->execute();
    $resultado = $sql->fetchAll();
    foreach ($resultado as $row) {
        d(unserialize(stream_get_contents($row["data"])));
    }

    $conn3 = new PDO('sqlite:/home/xerron/Repo/php/u-w-u.com/var/dbs/drupal.sqlite');
    $conn3->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = $conn2->prepare("select * from config where name = 'core.extension'");
    $sql->execute();
    $resultado = $sql->fetchAll();
    foreach ($resultado as $row) {
        d(unserialize(stream_get_contents($row["data"])));
    }

I am now trying to Phalcon db.



40.1k
Accepted
answer

There is something strange with SQLite

using PDO needs to be used stream_get_contents and with Phalcon NOT.

    $database = [
        'adapter' => 'Postgresql',
        'host' => 'localhost',
        'username' => 'xerron',
        'password' => 'root',
        'dbname' => 'db_drupal'
    ];

    $database2 = [
        'adapter' => 'Mysql',
        'host' => 'localhost',
        'username' => 'root',
        'password' => 'root',
        'dbname' => 'db_drupal'
    ];

    $database3 = [
        'adapter' => 'Sqlite',
        'dbname' => '/home/xerron/Repo/php/u-w-u.com/var/dbs/drupal.sqlite'
    ];

    $adapter = $database['adapter'];
    unset($database['adapter']);
    $class = 'Phalcon\Db\Adapter\Pdo\\' . $adapter;
    $query = "select * from config where name = 'core.extension'";
    $connection = new $class($database);
    $result = $connection->fetchAll($query);
    $connection->close();
    d(unserialize(stream_get_contents($result[0]['data'])));

    $adapter2 = $database2['adapter'];
    unset($database2['adapter']);
    $class = 'Phalcon\Db\Adapter\Pdo\\' . $adapter2;
    $query = "select * from config where name = 'core.extension'";
    $connection = new $class($database2);
    $result = $connection->fetchAll($query);
    $connection->close();
    d(unserialize($result[0]['data']));

    $adapter3 = $database3['adapter'];
    unset($database3['adapter']);
    $class = 'Phalcon\Db\Adapter\Pdo\\' . $adapter3;
    $query = "select * from config where name = 'core.extension'";
    $connection = new $class($database3);
    $result = $connection->fetchAll($query);
    $connection->close();
    d(unserialize($result[0]['data']));