yiisoft / db-pgsql Goto Github PK
View Code? Open in Web Editor NEWPostgreSQL driver for Yii Database
Home Page: http://www.yiiframework.com
License: BSD 3-Clause "New" or "Revised" License
PostgreSQL driver for Yii Database
Home Page: http://www.yiiframework.com
License: BSD 3-Clause "New" or "Revised" License
After upgrading to 2.0.14, application always show errors like this:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "tbl_user_session_pkey"
DETAIL: Key (id)=(s9ud40t3uorkfo22t9bvd76am3 )
The SQL being executed was: INSERT INTO "tbl_user_session" ("data", "id", "expire") VALUES ('__flash|a:0:{}', 's9ud40t3uorkfo22t9bvd76am3', 1519149822);
For security reasons, we designed the database to have a schema "protected" that have all tables, and a schema "public", that have no tables, only filtered updatable views.
The database component have this schemaMap config:
'schemaMap' => [
'pgsql'=> [
'class'=>'yii\db\pgsql\Schema',
'defaultSchema' => 'public',
],
],
The upsert command introduced in Yii2 2.0.14 didn't recognize the primary keys or unique keys metadata from Updatable Views, so it always build a INSERT statement instead.
Q | A |
---|---|
Yii version | 2.0.14 |
PHP version | 5.6, 7.1 |
Operating system | any |
PostgreSQL version | 9.5.5 |
Side note: My ActiveRecord models works on past Yii2 versions because I fix the primary keys manually. They extends the class below:
<?php
namespace app\common\models;
use Yii;
class UpdatableViewActiveRecord extends \yii\db\ActiveRecord
{
public static function primaryKey()
{
throw new \Exception("primary key must be overrided!");
}
public static function getTableSchema()
{
$tabelSchema = parent::getTableSchema();
if (static::primaryKey()) {
$tabelSchema->fixPrimaryKey(static::primaryKey());
}
return $tabelSchema;
}
}
1) Suggest make (#297 (comment))ArrayParser
as internal. Users don't must use this class directly, it'sright?
ArrayParser
.Lines 107 to 112 in 56df5ac
It would be good prevent this behavior and create instance of ArrayParser
once or make ArrayParser
methods static.
Similar case: #303 (comment)
Dependabot can't resolve your PHP dependency files.
As a result, Dependabot couldn't update your dependencies.
The error Dependabot encountered was:
Your requirements could not be resolved to an installable set of packages.
Problem 1
- Installation request for yiisoft/db-pgsql No version set (parsed as 1.0.0) -> satisfiable by yiisoft/db-pgsql[No version set (parsed as 1.0.0)].
- yiisoft/db 3.0.x-dev requires yiisoft/db-pgsql ^3.0@dev -> satisfiable by yiisoft/db-pgsql[3.0.x-dev].
- Can only install one of: yiisoft/db-pgsql[3.0.x-dev, No version set (parsed as 1.0.0)].
- Installation request for yiisoft/db ^3.0@dev -> satisfiable by yiisoft/db[3.0.x-dev].
If you think the above is an error on Dependabot's side please don't hesitate to get in touch - we'll do whatever we can to fix it.
Тип array postgresql возвращается object, сейчас еще есть возможность сказать дай array https://github.com/yiisoft/yii2/blob/1fe3d61a3bac1fb8e59b9d026d9f37b5c5eadd4a/framework/db/pgsql/ColumnSchema.php#L99
Теперь после обновления
Line 66 in 3edf67d
Нельзя ли оставить возможность выбора, потому что не удобно получать данные. Когда из базы после populate летит object приходится в модели делать getер, задача которого вызывать getValue() и все, хотя лучше просто из базы получить нужный массив с данными... Но может я не прав... В общем как будем жить дальше?
Q | A |
---|---|
Yii version | 2.0.16 |
PHP version | 7.2 |
Operating system | CentOS 7 |
$dsn = (new Dsn('pgsql', '127.0.0.1'))->asString();
$pdoDriver = new Driver($dsn, 'postgres', '', [\PDO::ATTR_STRINGIFY_FETCHES => true]);
$schemaCache = new SchemaCache(new FileCache(__DIR__ . '/runtime/cache'));
$db = new Connection($pdoDriver, $schemaCache);
$schema = $db->getSchema();
$tableSchema = $schema->getTableSchema('table_name', true);
The result is
TypeError: Yiisoft\Db\Schema\AbstractColumnSchema::allowNull(): Argument #1 ($value) must be of type bool, string given, called in ...\db-pgsql\src\Schema.php on line 775
See -
Line 81 in 3d47b9a
if ($this->dimension > 0) {
if (!is_array($value)) {
$value = $this->getArrayParser()->parse($value);
}
...
After change DB from pgsql to mysql - we can't will use class ArrayParser (and other pgsql specified types from this package) in our application
if ($value instanceof ArrayParser) - will be throws Exception
Dependabot can't resolve your PHP dependency files.
As a result, Dependabot couldn't update your dependencies.
The error Dependabot encountered was:
Your requirements could not be resolved to an installable set of packages.
Problem 1
- Installation request for yiisoft/yii-web ^3.0@dev -> satisfiable by yiisoft/yii-web[3.0.x-dev].
- yiisoft/yii-web 3.0.x-dev requires psr/http-factory-implementation 1.0 -> no matching package found.
Problem 2
- Installation request for yiisoft/view ^3.0@dev -> satisfiable by yiisoft/view[3.0.x-dev].
- yiisoft/view 3.0.x-dev requires psr/event-dispatcher-implementation 1.0.0 -> no matching package found.
Potential causes:
- A typo in the package name
- The package is not available in a stable-enough version according to your minimum-stability setting
see <https://getcomposer.org/doc/04-schema.md#minimum-stability> for more details.
- It's a private package and you forgot to add a custom repository to find it
Read <https://getcomposer.org/doc/articles/troubleshooting.md> for further common problems.
If you think the above is an error on Dependabot's side please don't hesitate to get in touch - we'll do whatever we can to fix it.
Postgres alter column often require explicit typecast with USING clause
Propose to add the ability for support
$db->alterColumn('my_table', 'text_field', 'VARCHAR(255) USING text_field::varchar')
https://github.com/yiisoft/db-pgsql/blob/master/src/QueryBuilder.php#L319
http=>https
Good day. After last update of yiisoft/db pgsql migration set wrong type (varchar) for serial type. It doesn't matter how set that type - using MigrstionBuilger::primaryKey
method or set it manually using ColumnSchemaBuilder
instance. And i not tested with other db/native db types. Thanks
PostgreSQL has composite types.
https://www.postgresql.org/docs/current/rowtypes.html
They are useful to store complex data, for example money with currency
CREATE TYPE currency_money AS
(
value numeric(19,4),
currency_code char(3)
);
CREATE TABLE product (
id serial NOT NULL,
price currency_money DEFAULT '(0,USD)',
CONSTRAINT product_pkey PRIMARY KEY (id)
);
In PHP we can use it as array
$command->insert('product', ['price' => ['value' => 10, 'currency_code' => 'USD']])->execute(); // or [10, 'USD']
$price = (new Query($db))->select(['price'])->from('product')->scalar();
$pricePhpType = $tableSchema->getColumn('price')->phpTypecast($price);
$pricePhpType === ['value' => 10, 'currency_code' => 'USD'];
Examples of composite types
CREATE TYPE file_with_name AS
(
path text,
name text
);
CREATE TYPE url_with_name AS
(
url text,
name text
);
CREATE TYPE phone_composite AS
(
country_code char(2),
number bigint,
extension smallint,
leading_zeros smallint,
type text
);
CREATE TYPE permission_composite AS
(
create bool,
view bool,
update bool,
delete bool
);
But it is not possible to transfer data to another DBMS without changing PHP code.
I can realize if you agree.
if I write a query that changes column type only, query builder add queries that will drop not null and default value
https://github.com/yiisoft/db-pgsql/blob/master/src/QueryBuilder.php#L333
https://github.com/yiisoft/db-pgsql/blob/master/src/QueryBuilder.php#L345
If I want to change type only, default value and null-state should be kept as is
https://github.com/yiisoft/db-pgsql/blob/master/src/Schema.php#L340
This line provides invalid index column order
See #295
When attempting to upsert()
duplicate values the autoincrement primary key will be incremented even if no duplicates are inserted.
$columns = ['unique_field' => 'unique value', 'field' => 'value'];
$command->upsert('table_name', $columns, false)->execute();
Generated query:
INSERT INTO table_name (unique_field, field) VALUES ('unique value', 'value') ON CONFLICT DO NOTHING
This can be solved by generating query without ON CONFLICT DO NOTHING
INSERT INTO table_name (unique_field, field)
SELECT 'unique value', 'value'
WHERE NOT EXISTS(
SELECT 1
FROM table_name
WHERE unique_field = 'unique value'
)
Q | A |
---|---|
Version | 1.1.0 |
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.