opis / database Goto Github PK

Hello,

I'm gathering informations on available DB tools for PHP. I know Doctrine is like a go-to for most programmers. What are the advantages of using this package ?

Thank you ?

$connection = new Connection(
		    'sqlsrv:server=myserver;database=mydatabase,
		    'user,
		    'password'
		);

		$db = new Database($connection);
		$result = $db->from('mytable')
             ->select()
             ->all();

SQLSTATE[IMSSP]: The given attribute is only supported on the PDOStatement object.

$MSSQL = new \PDO('sqlsrv:Server=myserver;Database=mydatabase,'user','mytable');
$MSSQL->exec("SET NAMES 'UTF-8'");
$rs = $MSSQL->prepare("SELECT * FROM mytable");

	$rs->execute();
	$results =  $rs->fetchAll(\PDO::FETCH_ASSOC);

This connection successful

Opis\Database\Schema\Compiler\SQLite::currentDatabase() is declared to return an array but returns a string, causing breakages.

This can be demonstrated with the following code:

use Opis\Database\Connection;
use Opis\Database\Database;

$database = new Database(new Connection('sqlite:/tmp/test.db'));
$database->schema()->hasTable('foo');

Relevant error:

PHP Fatal error:  Uncaught TypeError: Return value of Opis\Database\Schema\Compiler\SQLite::currentDatabase() must be of the type array, string returned in /project-path/vendor/opis/database/src/Schema/Compiler/SQLite.php:117

The Oracle, Postrge and SQL Server implementations all return an array so are not impacted, this is only an issue for SQLite. Relevant source:

• Compiler::currentDatabase()
• SQLite::currentDatabase()

On line 218 the method getConnection is declared both abstract and static. This seams to be not valid php, when I run the code it raises a error.

Hope you can find time to fix this issue, and write some tests. Alot of the code looks pretty good.

There are no tests for the ORM.

Hi,
I was writing an API and loved your library, the struggle is when I select data from DB like this:

Jobs::where('title')->like('%'.$args['job'].'%')->first();

The result is returned as an object and couldn't find any way to convert it into an array.

I've something like this:

$group->orWhere('tbl.FIELD')->like($value);

and i need something like

$group->orWhere('LOWER(tbl.FIELD)')->like($value);

Is this possible with this library? An if so, how?

Please note: I'm not able to change the collation of the schema.

I have been getting this crash with opis installed and I have not been able to figure out what causes it. http://pastebin.com/bKJ1Es3v

Hi there,
it will be very useful to have fulltext search
is it possible?

Cheers

Hello,
How to handle composite primary keys for a table (multiple columns) in models? It seems there’s no way to specify an array as primary key.

How to reproduce?
0. Use postgresql

1. Create table

CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    boolfield Boolean not null,
);

2. Execute queries

$con = Connection::fromPDO($this->PDO);
$db = new Database($con);
$db->insert(['boolfield' => true])->into('tree2'); // ok
$db->insert(['boolfield' => false])->into('tree2'); // type err, because PDO::PARAM_STRING used in the $stmt->execute() pdo method by default.

3. Get the following errors

Database error.SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: \"\" [ INSERT INTO \"test\" (\"boolfield\") VALUES ('') ] 

The same bug as in Fluentpdo.

How fix it?
Work correctly with PHP types through PDO methods bindValue or bindParam like this:

$stmt->bindValues(1, $value, PDO::PARAM_BOOL);

hi
can we use raw query $this->db->query("select * from user");
how to achve that.
thanks alot

I have been playing around witht the code base trying not to use getConnection since its one of the few ugly parts of the API.

It seams as if almost all stages except the Model has a connection to use instead of using $model::getConnection. If you add a 'Connection' member and constructor argument to the Model then every thing falls in place, except for Model::find(x) and all other static methods that needs a connection.

However all these static methods are not needed, since the Query object is doing all the work any way, and creating it is supper simple. Could maybe getConnection only be used when you use the static function syntax?

I'm willing to make a PR.

It would be possible to implement the PSR-3 Logger Interface, to save log of queries, this opens other possibilities such as monolog.

thanks for the great package btw

Dear,
I am try to use an if into function expression, but they dont work.

            $result = $db->from($table)
                            ->where($kpiDetail->column)->gte($dataConsultaStart)
                            ->andWhere($kpiDetail->column)->lte($dataConsultaEnd)
                            ->select(
                            function($include){
                                if ($kpiDetail->type == "count")
                                {
                                    $include->count('data', 'dado');
                                }
                            })
                            ->all();

if i remove the if, working the count.

    if ($kpiDetail->type == "count")
    {
        $include->count('data', 'dado');
    }

I can use if into the function()?
Thanks
Mauro

With a previous database library, I would do the following in order to log exception messages:

$qudb->beginTransaction();
try {
    $prepare = $qudb->prepare(
        "UPDATE {$qudb->site_prefix}post SET post_relative_url = REPLACE(post_relative_url,'?','?')",
        [$old_slug, $new_slug]
    );
    $qudb->query($prepare);

    $qudb->commit();
} catch (\PDOException $ex) {
    $qudb->rollback();
    Cascade::getLogger('error')->error(
        sprintf(
            'SQLSTATE[%s]: %s',
            $ex->getCode(),
            $ex->getMessage()
        ),
        [
            'Db Function' => 'update_post_relative_url_posttype'
        ]
    );
}

With Opis transactions, how can I achieve something similar or would I need to use the PDO object instead? Thanks.

SQL in Fiberdird with error order by

count does not need order by it could be removed during SQL assembly

$size = $result->count();

<?php
 $result = $db->from('vw_escrituracao_fiscal');

 $itens = $result->limit($params->limit)
            ->offset($params->offset)
            ->orderBy('id', $params->order_by ?? 'asc')
            ->select()
            ->fetchClass(ListNFSE::class)
            ->all();
$size = $result->count();

For methods like create the argument that is typed to Closure will be more useful if it is typed as callable.

Example:

<?php

use Opis\Database\Database;
use Opis\Database\Schema\CreateTable;

class Mapper
{
  public function __construct(Database $db)
  {
    $this->db = $db;
  }

  public function definition(CreateTable $table)
  {
    $table->integer('id')->primary();
  }

  public function createTable()
  {
    $this->db->schema()->create('table_name', [$this, 'definition']);
  }
}

I'll make a PR if you agree that this is a good idea.

WhereInterface is never used.

I found this class while documenting the methods on the Where and WhereCondition classes.

Should it be deleted or used?

Dear,
I have two tables that i make a inner join. But in the both tables have a column with field named 'title'.
My question is, i will use ->select(), without use alias name, like the select below

result = $db->from([getenv('DB_PREFIX').'kpi' => 'kpi'])
                        ->join([getenv('DB_PREFIX').'kpi_unit' => 'unit'], function($join){
                               $join->on('kpi.id_kpi_unit', 'unit.id_kpi_unit');
                            })
                        ->orderBy('id_kpi', 'ASC')
                        ->select()
                        ->all();

In this situation the result show only one 'title' field.
how i can solve this?
Thanks

In ColumnExpression::round variable $foramt is used but never assigned. Seams to be a typo from a copy of some other method.

php 5.6.12

im use transaction with callback:

->onError(function(PDOException $e, $transaction){ //code write log throw new \PDOException($e->getMessage()); }) ->onSuccess(function($transaction){ //code write log })

both methods i have fatal error, for example:
Fatal error: Call to undefined method Opis\Database\Transaction::successCallback()

that will be corrected in \opis\database\lib\Transaction.php, method execute, we should replace
if ($this->successCallback !== null) { $this->successCallback($this); }
on
if ($this->successCallback !== null) { call_user_func($this->successCallback, $this); }
got error callback the same...

Second argument for Connection::column is now mandatory.

I would also request a method ResultSet::column with result of PDOStatement::fetchColumn.

Idea: using the logic of the opis/database for generate the sql text files.
Current code only allow makes it while the database is connected.
Create tables documentation: https://docs.opis.io/database/4.x/schema-creating-tables.html

hello, I would like to share a new ORDERBY method for RAND () to update your repository.

version 4.1

I needed to use the ORDERBY rand () method but I see that it was not possible because it is attributed to the query columns.

so I created a new "orderByRaw" method so that it is possible to write as needed for each query.

//--------------------------------------------------------------------------------------------------------------------------------------------
file: Query.php
namespace: Opis \ Database \ SQL
line 132
new method
/**
* @param string
* @param string $order (optional)
* @return Select|SelectStatement
*/
public function orderByRaw($order=null)
{
return $this->buildSelect()->orderByRaw($order);
}

//--------------------------------------------------------------------------------------------------------------------------------------------

file: SelectStatement.php
namespace: Opis\Database\SQL
line: 137
new method
/**
* @param |string|optional
* @param string $order
* @return SelectStatement
*/
public function orderByRaw($order=null): self
{
$this->sql->addOrderRaw($order);
return $this;
}

//--------------------------------------------------------------------------------------------------------------------------------------------

file: Compiler.php
namespace: Opis\Database\SQL;

###line: 51
$sql .= $this->handleOrderingRaw($select->getOrderRaw());

#line: 492

/**
* Handle ORDER BY RAW
* @param string $orderingRaw
* @return string
*/
protected function handleOrderingRaw($orderingRaw)
{
if (empty($orderingRaw)) {
return '';
}

	return ' ORDER BY '.$orderingRaw;		
}

//--------------------------------------------------------------------------------------------------------------------------------------------

file: SQLStatement.php
namespace: Opis\Database\SQL;

###line: 30

protected $orderRaw;

###line: 348
/**
* @param string $orderraw
*/
public function addOrderRaw(string $orderRaw)
{
return $this->orderRaw=$orderRaw;
}

###line: 486
/**
* @return string
*/
public function getOrderRaw()
{
return $this->orderRaw;
}

//--------------------------------------------------------------------------------------------------------------------------------------------

how to use

$result = $db->from('users')
->orderByRaw('rand()')
->select()
->all();

it is possible for me to do a union all, or enter a sql manually?

Apparently I can't perform any kind of these operations:

$db->from('table_name')
	->orderBy('UNIX_TIMESTAMP(date_time)', 'desc')
	->select('row')->first();

There is error while using Firebird 1.5 because of compiler wrapper.

I fixed it by adding this wrapper:
protected $wrapper = '%s';
to vendor/opis/database/lib/Compiler/Firebird.php

In method SelectStatement::addHavingClause call to new AggregateExpression is given to few arguments, missing HavingClause $clause.

Hi,
I dont find how i can do a DISTINCT('p.id') from one column.
using ->distinct(), they filter all columns, but i don't will.
Have a method to solve this?

Is there any plan to support Firebird for Schema?

Is it correct, that Implementing \Opis\Database\Schema\Compiler\Firebird should resolve this?

If there are no current plans for this. Is there anything you can tell me to watch for when implementing it by my own?

Please add the psr/log ~1.0 interface support

like Psr\Log\LoggerAwareInterface
OR
like Psr\Log\LoggerAwareTrait

package https://packagist.org/packages/psr/log

composer require psr/log ~1.0

hello.
how can add prefix into the first of all tables in opis?

This issue is shown only when I have tried to execute two queries at the same time, using axios JavaScript HTTP library, I have made a call to two endpoints of my API, then this exception is thrown, but when I call one endpoint at once everything goes smoothly, tested on PHP 7.0.10 & 5.6.25. Does Opis use existing connections instead of creating new one?

How create table with opis database? Or how to run sample Query?

When paging results, when I use count (), it fires sql for all queries I make during paging, it would be possible to implement a cache so that it returns count () from cache, we can mount a combination sql with the parameters, when this value is equal the cache can be used.

How can I make a SUBQUERY and ALIAS

ex:

SELECT u.*, (SELECT img1 FROM user_imgs WHERE userid=u.user_id LIMIT 0,1) as imgPerfil (SELECT img2 FROM user_imgs WHERE userid=u.user_id LIMIT 1,1 as imgCapa FROM users u WHERE u.activated=1

Right now, I did this:

      try {  // insert
        $db->insert($item)->into($table);
      } catch (Exception $e) {
        if ($e->getCode() == 23000) {
          $db->update($table)->where('_id')->is($item['_id'])->set($item);
        }
      }

and that works, but I'm curious about two things:

• if this is the best way and
• if I've actually overlooked a predefined function for this?

Hi,
I am have this insert

    $result = $db->insert($fieldsPrep)->into('pedidos_detalhes');

But i dont need that they run, only prepare the sql and then i get in varible and use after.
I dont find this options in opis, or how i can do this.
Thanks
Mauro

I have a table with 545 records in a MySQL database.
I run this code:

$st = time();
$rec = $db->from('data_table')->select()->first();
$et = time();

print "Elapsed: " . ($et - $st) . " seconds\n";

When I run this code, it takes 2.5+ seconds to return. Every time.

Interestingly, when I put that code into a loop and run it over and over, the first pull takes 2+ seconds and subsequent ones take ~0.6 seconds. To me, that implies there's a caching method involved.

But why does the initial pull of the first record take 2.5+ seconds? That seems ridiculous for pulling one record.

When I run (what I would think is the underlying SQL) SELECT * FROM data_table LIMIT 1 directly, it takes ~100 ms. So why does Opis take 2.5+ seconds to run that query?

I'm having a similar issue when writing data. Writing/updating those 545 records (inside a transaction) takes ~3.5 minutes.

Is your library capable of such a thing? If it does, I couldn't find it in the docs.

I am trying to use the in filter with a dynamic values, and for some reason it is not working for me. Below is the dynamic array values that I am trying to input into the the 'in filter:

$users = [
  0 => '\'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\', \'2\'',
]; // I am assuming this array gets sanitized at some point.

$_users = array_unique_compact($users);
$_users = "'" . implode("', '", $_users) . "'";

$list_users = $qudb->from($qudb->base_prefix . 'user')
    ->where('user_id')->in([$_users])
    ->select()
    ->fetchAssoc()
    ->all();

error_log(var_export($list_users, true)); // An empty array is returned.

Is there something with my code above that I am not seeing?

I'm having trouble using firebird, the generated sql puts the table name and fields in double quotation marks

SELECT * FROM "table"

In continuation of #65 i was thinking something like this:

$result = $db->from('users')
             ->where('age')->is(18)
             ->append(' AND WHERE match (username) against (?, ?) ', [ 'param1', 'param2'])
             ->select()
             ->all();

what do you think?

https://github.com/opis/database/blob/master/lib/Transaction.php#L113

In this line is used $pdo->commit() but it should be $this->commit().

get($data = []) { $query = $db->from('table'); if ($data["search"]) { $query->where('column')->like($data["search"].'%'); } $query->select(); return $query->all(); }

Or ;

function get($data = []) { $db->from('table'); if ($data["search"]) { $db->where('column')->like($data["search"].'%'); } $db->select(); return $db->all(); }

How do I make them work?

Show tables command doesn't exist, can any development be done for this?

For Oracle I can write:

$db->from('users')
   ->orderBy('age', 'NULLS LAST')
   ->select()
   ->all()

But this doesn't work in MySQL for instance.