TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
K03, ORM and SQLite via PDO
  • Hi,

    User of SQLite in PDO mode, I am stuck with 2 troubles:
    1> As of today, if I am not wrong, ORM does not work because of the lack of 'list_tables' and 'list_columns'...
    2> 'set_charset' often cause me trouble if I forget to set it empty, because it has already been set in the default config.

    I just built a new set of classes PDOSQLite with a 'set_charset' function empty, and 'list_tables', 'list_columns' similar to mysql ones.... And it seems to be working.

    As I am a rookie here, am I going the right way? Should I share, even if anyone could do it?
    Thanks for your advice(s)
  • How are you loading it? My personal preference would be to go with a seperate driver and not use PDO.
  • Separate driver, and rewrite what has been done? I am not sure, but here is what I have done for now


    modules\database\classes\database\pdosqlite.php

    <?php defined('SYSPATH') or die('No direct script access.');

    class Database_PDOSQLite extends Kohana_Database_PDOSQLite {}

    modules\database\classes\kohana\database\pdosqlite.php

    <?php defined('SYSPATH') or die('No direct script access.');
    /**
    * PDOSQLite database connection.
    *
    * @package Kohana
    * @author Kohana Team
    * @copyright (c) 2008-2009 Kohana Team
    * @license http://kohanaphp.com/license.html
    */
    class Kohana_Database_PDOSQLite extends Kohana_Database_PDO {

    public function set_charset($charset)
    {
    }

    public function list_tables($like = NULL)
    {
    if (is_string($like))
    {
    // Search for table names
    $result = $this->query(Database::SELECT, 'SELECT name FROM SQLITE_MASTER WHERE type="table" AND name LIKE '.$this->quote($like).' ORDER BY name', FALSE);
    }
    else
    {
    // Find all table names
    $result = $this->query(Database::SELECT, 'SELECT name FROM SQLITE_MASTER WHERE type="table" ORDER BY name', FALSE);
    }

    $tables = array();
    foreach ($result as $row)
    {
    // Get the table name from the results
    $tables[] = current($row);
    }

    return $tables;
    }

    public function list_columns($table, $like = NULL)
    {
    if (is_string($like))
    {
    throw new Kohana_Exception('Database method :method is not supported by :class',
    array(':method' => __FUNCTION__, ':class' => __CLASS__));
    }

    // Find all column names
    $result = $this->query(Database::SELECT, 'PRAGMA table_info('.$table.')', FALSE);

    $columns = array();
    foreach ($result as $row)
    {
    // Get the column name from the results
    $columns[] = $row['name'];
    }

    return $columns;
    }

    } // End Database_PDOSQLite

    I would love to know if I could do it respecting the "best practices" around.....

  • My reason for creating a seperate driver is because it allows more control over how everything works together, like the result class. I am primarily working with MSSQL so it helps to customize every part for its differences. PDO, in my opinion, allows you to connect to different databases with a single driver but does not do a good job at handling the differences between databases. It lulls you into believe that you can change the database and connection string but not single sql query in the application. Unfortuately it does not help with things like getting column or table names. You run into these problems because each database's sql statements are different.

    A few suggestions I would look at for your code:


    1) I would create your own major namespace (TigerWay_Database_PDO) and not use the Kohana one. Good job though on allowing it to be easily extended.
    2) I would name your class Database_PDO_SQLite to show it is part of PDO and not completely seperate. This should allow you to load the driver using pdo_sqlite but I have not tested that piece.
    3)You may look at overriding the Database_PDO class for your base class not the Kohana_Database_PDO. This would allow any customization to PDO that the user does is automatically added UNLESS you overrode it in your driver.

    Have you tested it with the Query builder? That may cause a problem if the syntax is different then mysql's.
  • Hi TW, do you manage to make PRAGMA table_info work with PDO? I tried this but it did not work

    $adapter = new PDO("sqlite:$path");
    $statement = $adapter->query("PRAGMA table_info($table)");

    $statement always return null

    Thanks
  • @bstin9
    It does work, happily :-)
    Have a look here, if you use Kohana 3.x.
  • @Tiger's Way

    Thanks for sharing the above code. Finally managed to use SQLite with ORM. :-)

    I made some minor changes

    $columns[] = $row['name'];
    

    changed to

    $columns[$row['name']] = $row['name'];
    

    Without this Formo was taking numbers as field labels.

    also as suggested by @dcunited08, I have created in directory

    modules/database/classes/database/pdo/sqlite.php
    

    and changed the class declaration to

    class Database_PDO_SQLite extends Kohana_Database_PDO_SQLite {}
    

    and in directory

    modules/database/classes/kohana/database/pdo/sqlite.php
    

    and changed the class declaration to

    class Kohana_Database_PDO_SQLite extends Kohana_Database_PDO {
    

    Also make sure that in your database config file

    'type' = 'pdo_sqlite',
    

    Hope the above changes will be useful to someone.

  • i take this code and add ko 3.3 support, and transparent class extension i leave it here for future reference: http://brainoverflow.co.nf/?p=63

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion