Native database

[jimgwhit]

@daveismyname and @LuckyCyborg have you seen the pull request on the fluentpdo github site?
If we are not using doctrine dbal, could we not use these packages either? If everything is written by dave or @LuckyCyborg or @tomvlk then updating the framework will be easier. As of now I have to look for updates in nova and fluentpdo to stay up to date. I know you want to move forward and finalize, but give some thought to it please.

[LuckyCyborg]

@jimgwhit about what pull-request you talk, Jim?

[jimgwhit]

The ones on their site.

[jimgwhit]

@LuckyCyborg ok I am now trying nova again without doctrine dbal, but using the native db. I am getting a table showing, but no data, here is model, how to change to work?

<?php

namespace App\Models;

use Nova\Net\Session;

class PetModel extends \App\Core\BaseModel
{

    public function __construct()
    {
        parent::__construct();
    }

    public function petCount($petsearch = "")
    {
        $petsearch = $petsearch . "%";
        //echo "you are here".Session::get('owner');
        //$ownerid = '1';
        //echo $this->_db->kselect('SELECT count(petid) as total FROM ' . PREFIX . 'pets WHERE petname LIKE :search',
        //array('search' => $petsearch));
        if (Session::get('owner') == '1') {
            echo "you are here" . Session::get('owner');
            $result = $this->db->select('SELECT COUNT(petid) as total FROM ' . DB_PREFIX . 'pets WHERE petname LIKE :search', array('search' => $petsearch));
            return $result[0]->total;

            //return $this->_db->kselect('SELECT count(petid) as total FROM ' . PREFIX . 'pets WHERE petname LIKE :search', 
            //array('search' => $petsearch));   
        } else {
            $result = $this->db->select('SELECT count(petid) as total FROM ' . DB_PREFIX . 'pets WHERE petname LIKE :search AND ownerid =
        :ownerid', array('search' => $petsearch, 'ownerid' => Session::get('owner')));
        }
        return $result[0]->total;

        //good return $this->_db->kselect('SELECT count(petid) as total FROM ' . PREFIX . 'pets WHERE petname LIKE :search',
        // array('search' => $petsearch));  
    }

    // good public function get_pets($limit="", $petsearch = ""){
    public function getPets($offset = "", $rowsperpage = "", $petsearch = "")
    {
        //$pagingQuery = "LIMIT " . $offset . ", " .  $rowsperpage;
        $pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
        //echo 'pg===',$pagingQuery;
        //echo "===".$limit."====";
        //$petsearch = "b";
        $petsearch = $petsearch . "%";
        //$pagingQuery = " LIMIT $offset, $rowsperpage";
        //$ownerid = '1';
        if (Session::get('owner') == '1') {
            return $this->db->select("SELECT * FROM " . DB_PREFIX . "pets WHERE petname like :search ORDER BY petname " . $pagingQuery, array('search' => $petsearch));
        } else {
            return $this->db->select("SELECT * FROM " . DB_PREFIX . "pets WHERE petname like :search AND ownerid =
        :ownerid ORDER BY petname " . $pagingQuery, array('search' => $petsearch, 'ownerid' => Session::get('owner')));
        }

        ///good
        /* if(Session::get('owner') == '1')
          {
          return $this->_db->select("SELECT * FROM ".PREFIX."pets WHERE petname like :search ORDER BY petname $limit",
          array('search' => $petsearch));
          }
          else
          {
          return $this->_db->select("SELECT * FROM ".PREFIX."pets WHERE petname like :search AND ownerid =
          :ownerid ORDER BY petname $limit",
          array('search' => $petsearch, 'ownerid' => Session::get('owner')));
          } */
        //good
    }

    public function petCount_bak($petsearch = "")
    {
        $prefix = 'dc_';
        $petsearch = $petsearch . "%";
        if (Session::get('owner') == '1') {
            echo "you are here" . Session::get('owner');
            $stmt = $this->db->prepare('SELECT COUNT(petid) as total FROM ' . DB_PREFIX . 'pets WHERE petname LIKE :search');
            $stmt->bindValue("search", $petsearch);
            $stmt->execute();
            while ($row = $stmt->fetch()) {
                //echo $row['kount'];
                $kount = $row['total'];
            }

            return $kount;
        } else {
            $stmt = $this->db->prepare('SELECT count(petid) as total FROM ' . DB_PREFIX . 'pets WHERE petname LIKE :search AND ownerid = :ownerid');
            $stmt->bindValue('search', $petsearch);
            $stmt->bindValue('ownerid', Session::get('owner'));
            $stmt->execute();
            while ($row = $stmt->fetch()) {
                //echo $row['kount'];
                $kount = $row['total'];
            }

            return $kount;
        }
        //$result[0]['total'];

        //good return $this->_db->kselect('SELECT count(petid) as total FROM ' . DB_PREFIX . 'pets WHERE petname LIKE :search',
        // array('search' => $petsearch));  
    }

    // good public function get_pets($limit="", $petsearch = ""){
    public function getPets_bak($offset = "", $rowsperpage = "", $petsearch = "")
    {
        //$pagingQuery = "LIMIT " . $offset . ", " .  $rowsperpage;
        $prefix = 'dc_';
        $pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
        $petsearch = $petsearch . "%";
        if (Session::get('owner') == '1') {
            return $this->db->executeQuery("SELECT * FROM " . DB_PREFIX . "pets WHERE petname like :search ORDER BY petname " . $pagingQuery, array('search' => $petsearch));
        } else {
            return $this->db->executeQuery("SELECT * FROM " . DB_PREFIX . "pets WHERE petname like :search AND ownerid =
        :ownerid ORDER BY petname " . $pagingQuery, array('search' => $petsearch, 'ownerid' => Session::get('owner')));
        }

    }

    public function getPet($petid)
    {
        echo 'petid ====' . $petid;
        return $this->db->select("SELECT * FROM " . DB_PREFIX . "pets WHERE petid =:petid", array('petid' => $petid));
    }

    public function insert($data)
    {
        $this->db->insert(DB_PREFIX . "pets", $data);
    }

    public function update($data, $where)
    {
        $this->db->update(DB_PREFIX . "pets", $data, $where);
    }

    public function delete($id)
    {
        $this->db->delete(DB_PREFIX . "contacts", array('id' => $id));
    }

    public function fix($varvalue)
    {
        return $this->db->fixvalue($varvalue);
    }

}

[LuckyCyborg]

@jimgwhit Sorry, Jim, but I'm not sure what you mean about the FluentPDO. You can be more explicit?

In other hand, you do not need a BaseModel if you do not want advanced things into. I suggest you to use just Nova\Core\Model for a complete custom implementation.

Also, into our \Nova\Database\Connection there is no executeQuery.

You should use, similar with Dave's previous Database Helper, the method select. For examples, look, like I said, there:

http://smvc3.giulianaeassociati.com/demos/dbal

There are around 15 examples about using the \Nova\Database.

Note that literally are shown the command and their result.

[jimgwhit]

@LuckyCyborg got this error in apache error log

[Mon Jan 11 14:11:03.981296 2016] [:error] [pid 2508:tid 1192] [client ::1:52622] PHP Fatal error:  Call to undefined function Nova\\Net\\finfo_open() in C:\\BitNami\\wampstack-5.6.2-0\\apache2\\htdocs\\nova\\system\\Net\\Response.php on line 165, referer: http://localhost/nova/pet

[LuckyCyborg]

@jimgwhit Okay, Jim! I will take a look right now!

[LuckyCyborg]

@jim BTW, what version are your PHP?

[jimgwhit]

@LuckyCyborg 5.6, same as 2 days ago when doctrine dbal worked, why would you even ask that?
Is this query wrong

return $this->db->select("SELECT * FROM " . DB_PREFIX . "pets WHERE petname like :search ORDER BY petname " . $pagingQuery, array('search' => $petsearch));

[LuckyCyborg]

@jimgwhit I do not see something wrong. maybe I will nitpicking into LIKE instead of like

But, in theory that query should work. BTW, what contains the $pagingQuery ?

[LuckyCyborg]

@jimgwhit Please update your framework, I made a change to improve the things into \Nova\Net\Response, in your case.

[jimgwhit]

@LuckyCyborg

$pagingQuery = "LIMIT {$offset}, {$rowsperpage}";

The framework is fresh install.

[LuckyCyborg]

@jimgwhit Please, update it again. You seen my message about \Nova\Net\Response ?

[jimgwhit]

@LuckyCyborg I can just copy that file over, correct?

[LuckyCyborg]

@jimgwhit Yep!

[LuckyCyborg]

@jimgwhit Look there:

http://smvc3.giulianaeassociati.com/demos/dbal

I introduced an example about search, like I understand to do this thing.

[LuckyCyborg]

@jimgwhit I've used

$this->db->selectAll("SELECT * FROM " .DB_PREFIX ."members WHERE username LIKE :search", array('search' => '%micha%'));

But the selectAll is a convenience one, you can use also something similar with:

$this->db->select("SELECT * FROM " .DB_PREFIX ."members WHERE username LIKE :search", array('search' => '%micha%'), array(), 'array', true);

OR

$this->db->select("SELECT * FROM " .DB_PREFIX ."members WHERE username LIKE :search", array('search' => '%micha%'), array(), 'object', true);

[jimgwhit]

@LuckyCyborg now getting

[Mon Jan 11 14:51:22.078935 2016] [:error] [pid 5680:tid 1192] [client ::1:50140] PHP Fatal error:  Call to undefined function finfo_open() in C:\\BitNami\\wampstack-5.6.2-0\\apache2\\htdocs\\nova\\system\\Net\\Response.php on line 147, referer: http://localhost/nova/pet

[LuckyCyborg]

@jimgwhit Amazing! Your PHP do not have finfo_open() ?!?

How that? See that you have some PHP module non-activated.

[LuckyCyborg]

@jimgwhit There you should have something like

extension=fileinfo.so

into your php.ini

[jimgwhit]

@LuckyCyborg nothing has changed since it worked in doctrine dbal a couble days ago, except this is a framework install from today. I replaced the response file

[LuckyCyborg]

@jimgwhit Today being when?

[jimgwhit]

@LuckyCyborg 2 or 3 hours ago

[LuckyCyborg]

@jimgwhit Hmm, there was some modifications, in the last 2 hours, yet, nothing supposed to touch your code.

Why you do not keep you the .git or rather, use GIT, into your testing tree?

GIT will ignore your custom files when you do pulls...

[jimgwhit]

@LuckyCyborg extension=fileinfo.so is not in my php.ini, I guess now a windows development machine won't work with nova?
Is everyone supposed to use linux only?

[LuckyCyborg]

@jimgwhit Look there:

http://smvc3.giulianaeassociati.com/demos/dbal

I'm introduced even the LIMIT, like in your search:

$this->db->selectAll("SELECT * FROM " .DB_PREFIX ."members WHERE username LIKE :search LIMIT 0, 2", array('search' => '%micha%'));

In other hand, talking about fileinfo, this is a usual PHP extension and a native one. Look in the documentation of your WAMP how to enable it. Nothing special there, should be by default.

Probably, talking about Windows, there should be something like:

extension=php_fileinfo.dll

To be sure, search for, into WAMP documentation about fileinfo.

[LuckyCyborg]

@jimgwhit Look there what say:

http://php.net/manual/en/fileinfo.installation.php

Windows users must include the bundled php_fileinfo.dll DLL file in php.ini to enable this extension.

[jimgwhit]

@daveismyname and @LuckyCyborg I enabled extension=php_fileinfo.dll changed select to selectAll still no results, and why the extension=php_fileinfo.dll now, doctrine dbal worked without it. and now

[Mon Jan 11 15:21:24.925976 2016] [ssl:warn] [pid 3932:tid 492] AH01909: localhost:443:0 server certificate does NOT include an ID which matches the server name
[Mon Jan 11 15:21:24.988543 2016] [mpm_winnt:notice] [pid 3932:tid 492] AH00354: Child: Starting 64 worker threads.

I don't think extension=php_fileinfo.dll should be needed

[LuckyCyborg]

@jimgwhit That fileinfo issue has practically no effect for CSS and JS files where I use another way.

We are debugged the error message on logs, but probably you are not affected by it. :smirk:

Talking about the real problem, let's debug it. I tested searching and looks like a charm. You agree that the following command is similar with your command?

$this->db->selectAll("SELECT * FROM " .DB_PREFIX ."members WHERE username LIKE :search LIMIT 0, 2", array('search' => '%micha%'));

In other hand, what you are supposed to return your search ? Objects or Arrays?

[LuckyCyborg]

@jimgwhit It is important to know what is supposed to return your call. Objects or Arrays?

There is a general configuration option into Nova\Database Configuration, into app/Config/config.php

'return_type' => 'array'

It instruct the \Nova\Database to return Assoc Arrays by default.

If you want to return by default Objects you should change it into:

'return_type' => 'object'

[jimgwhit]

@LuckyCyborg I commentd out

extension=php_fileinfo.dll

since it was commented out anyway at first.
I changed select to selectAll, and changed
'return_type' => 'array', to 'return_type' => 'object', now it's working.
Why doesn't select alone work? And in the count total I have

return $result[0]->total;

If I was using array would this be correct

return $result[0]['total'];

[LuckyCyborg]

@jimgwhit Because by default, the select return one Assoc or one Object. It have a option to ask for fetching all, default being false.

This is the complete definition of the method:

public function select($sql, array $params = array(), array $paramTypes = array(), $returnType = null, $fetchAll = false)

At the second question, response is yes.

BTW, I suggest you to leave active that fileinfo thing, is a nice to have and no make loads into your memory consumption.

[jimgwhit]

@LuckyCyborg will hosting companies have FileInfo ext by default? Remember dave was testing framework on various situations, dedicated servvers, hosting servers windows and Linux. Please don't call Nova good to go until thoroughly tested in various situations. I think all would agree that the framework should be set up to accommodate what the majority of hosting companies have set up. Remember on some hosts you can't change settings and some can.

[LuckyCyborg]

@jimgwhit All the Hosting Companies have the fileinfo enabled by default. Sure, I can't guarantee for sure on obscure super-cheapo cheapo ones. :smirk:

But I worked with all major hosting companies and they have it. It is usual under CentOS 6.x which is used by them.

BTW, you know that 99% of the Hosting Companies use that CentOS and no one use PHP with version bigger than 5.6 ?

[jimgwhit]

@LuckyCyborg would you know right off if GoDaddy had it enable on their shared Linux hosting? Also now that I have tried the native dbal I am ready to retry the doctrine Dbal package, please refresh my memory In pulling that in and setting it up.
As time goes by will the doctrine DBAL continue to be available as a package?

[ghost]

@LuckyCyborg Not true, the host I am registered with uses up to PHP 7, so there is at least one host.

[jimgwhit]

@LuckyCyborg I forgot to ask a question, the native DBAL has nothing to do with fluentpdo, is that correct?

[LuckyCyborg]

@jimgwhit The Doctrine based DBAL will be supported by us, primary for our own use, then go figure! :smirk:

@Kingorgg Your hosting is adventurous. Usually they are very very conservative.

[jimgwhit]

@LuckyCyborg its a dog gone shame that @daveismyname didn't go along with doctrine DBAL as the default, I think it would have been a wonderful choice. Hey let's sneak it in behind his back.

[LuckyCyborg]

@jimgwhit

I forgot to ask a question, the native DBAL has nothing to do with fluentpdo, is that correct?

For the native DBAL, the QueryBuilder based on FluentPDO is an option, loaded on request and offered as instance.

This QueryBuilder is not used internally by DBAL, the single one using it being \App\Core\BaseModel, primary written for our own use.

Also will be used by the native \Nova\ORM, under our Design.

@LuckyCyborg its a dog gone shame that @daveismyname didn't go along with doctrine DBAL as the default, I think it would have been a wonderful choice. Hey let's sneak it in behind his back.

It is better this way, to have it as optional Package, trust me! :smirk:

[jimgwhit]

@LuckyCyborg if I pull in doctrine dbal with composer like I did in version 2.2, and set it up like we did in issue #364 would it still work? Because you really have me hooked on that doctrine DBAL now you rascal you.

[LuckyCyborg]

@jimgwhit It is a shame to see an "Illuminated" being sooo "indoctrinated"! Shameful! :smirk:

BTW, you know from where you can get that DBAL Package, right?

https://github.com/simple-mvc-framework/nova-doctrine-dbal

[jimgwhit]

@LuckyCyborg I pulled in doctrine dbal just like I did in ver 2.2, made a database file and put

<?php
namespace Nova\Helpers;

use PDO;

/*
 * database Helper - extending PDO to use custom methods
 *
 * @author David Carr - dave@simplemvcframework.com
 * @version 2.1
 * @date June 27, 2014
 * @date May 18 2015
 */
class Database extends PDO
{
    /**
     * @var array Array of saved databases for reusing
     */
    protected static $conn;

    /**
     * Static method get
     *
     * @param  array $group
     * @return \helpers\database
     */
    public static function getconnection()
    {
        $config = new \Doctrine\DBAL\Configuration();
        $connectionParams = array(
            'dbname' => 'v2',
            'user' => 'root',
            'password' => 'pw',
            'host' => 'localhost',
            'driver' => 'pdo_mysql',
        );
        //$conn = $dvm::getConnection($connectionParams, $config);
        //$conn = $dvm::getConnection($connectionParams, $config); 
        $conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);
        return $conn;

    }

}

and used my app\core\Dbl.php file

<?php

namespace App\Core;

use Nova\Helpers\Database;

/*
 * model - the base model
 *
 * @author David Carr - dave@simplemvcframework.com
 * @version 2.2
 * @date June 27, 2014
 * @date updated May 18 2015
 */

abstract class Dbl
{
    /**
     * hold the database connection
     * @var object
     */
    protected $db;

    /**
     * create a new instance of the database helper
     */
    public function __construct()
    {
        // Get a Connection from DBAL here.
        $this->db = Database::getConnection();
    }
}

And presto it worked just like ver 2.2 did. This was in latest build of nova beta.

[LuckyCyborg]

@jimgwhit Of course that it will work. :smirk:

BTW, what you use as parent for your Controllers? \App\Core\BaseController , right?

[jimgwhit]

@LuckyCyborg right. Nice to know it is still easy to set up alternative DBAL 's.

[LuckyCyborg]

@jimgwhit Of course that is easily to work with...

BTW, what is your rendering style of choice? You managed to find a style at your likeness?

[jimgwhit]

@LuckyCyborg compare http://tjhs.taylorjoneshumane.com/tjhs/dog And http://tjhs.taylorjoneshumane.com/tjhs/cat So far I am putting the header right in the HTML page. Later I will probably use custom header files.

[LuckyCyborg]

@jimgwhit To implement that customization you need only two Layouts.

BTW, you don't responded. What rendering style you use now?

[jimgwhit]

@LuckyCyborg what do you mean by rendering style?

[LuckyCyborg]

@jimgwhit Nova have different methods and styles of Rendering.

For example, with View instances, auto-Rendering in the CakePHP style, with View::renderPage , etc...

[jimgwhit]

On mobile now, give me a few minutes and I wll toss up example.