Freezing querying table without PR.

[f3l1x]

Is there any way how to define view entity?

[Mikulas]

What level of support from orm would you need? Views should work same as table would, but you have to define custom rules to resolve inserts and updates from the query orm generates.

[f3l1x]

Hmm, at this moment ORM stuck in idle process (according to select * from pg_stat_activity). I have postgresql database and simple view.

[hrach]

I still have no idea what does it mean to stuck in idle process.

[f3l1x]

Hmmm. Maybe I found a problem.

https://github.com/nextras/orm/blob/7a0872cb91054b9cfa85ed0e780cb816b5693380/src/Mapper/Dbal/StorageReflection/StorageReflection.php#L72-L89

    public function getStoragePrimaryKey()
    {
        if (!$this->storagePrimaryKey) {
            $primaryKeys = [];
            foreach ($this->getColumns() as $column => $meta) {
                if ($meta['is_primary']) {
                    $primaryKeys[] = $column;
                }
            }
            if (count($primaryKeys) === 0) {
                $this->invalidateCache();
                throw new InvalidArgumentException("Storage '$this->storageName' has not defined any primary key.");
            }
            $this->storagePrimaryKey = $primaryKeys;
        }
        return $this->storagePrimaryKey;
    }

View does not have any primary key, right? It throws InvalidArgumentException, but in different context (in Nette\Cache $fallback), so any exception is propagated.

It creates cache file with 0 lenght. And it will frozen.

If I hardcode ID as primary key, it works.

            if (count($primaryKeys) === 0) {
                $primaryKeys = ['id'];
            }

Any idea? :smiley:

[hrach]

Yeah, I have exactly the same problem with the cache. I never had an oportunity to debug it, though, I think, it's fixed by nette/caching#36.

[f3l1x]

And what about view? Should I create my own ViewStorageReflection with custom primary key? Or any other solution?

[hrach]

Yeah, something like that would be probably the best.

[Mikulas]

@f3l1x was it enough to override createStorageReflection in mapper of the view-ed entity and specify custom primary key as third parameter to StorageReflection, or were there other issues?

[f3l1x]

@Mikulas I've override StorageReflection::getStoragePrimaryKey() and it works for now. We'll see. :first_quarter_moon:

[hrach]

Do you have any suggestions how should be the view support provided?

[f3l1x]

I know there are no connections to original tables such as foreign keys and indexes.

I see two options in this case:

1) Automatic - disable comparing primary keys if is it view

            if (count($this->entityPrimaryKey) !== count($primaryKey)) {
                throw new InvalidStateException(
                    'Mismatch count of entity primary key (' . implode(', ', $this->entityPrimaryKey)
                    . ') with storage primary key (' . implode(', ', $primaryKey) . ').'
                );
            }

2) Manual - new phpdoc macro {view} - it also disable comparing keys by metadata

What do you think?

[hrach]

I did some changes in StorageReflection, this comment should show the current workadound: https://gist.github.com/milo/dc61a7e347bb1632d0f0#gistcomment-1666793

[f3l1x]

@hrach

I found same cache problem at 2.0.0-r1 with Postgres.

I had normal entity with defined primary key, but at storage (psql) I had bigint id column which didn't be marked as primary. ORM / nette/cache has created 0 lenght file and frozen down.

I have changed id to primary key and it works, but my point is. There could be some error / exception. Dont you think?

[hrach]

Which version of nette caching package do you have?

[hrach]

@f3l1x You're right, I had nette/cache:~2.3.0. It's fixed at 2.4?

[hrach]

Its this commit, so it should be fixed since nette caching 2.3.4.

[f3l1x]

@hrach It happend again.

DB: MySQL ORM: ~2.0.0 nette\cache: ~2.4.0

If table has no primary key, connection frozen up. There's a file with 0 size in orm_mapper folder.

There could be some exception. What do you think?

[hrach]

Please, again, write the spefific nette/caching vetsion and check it :-) 2.4.0 was not ok.

[f3l1x]

So, ok.

Here are the tables.

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `download`;
CREATE TABLE `download` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `file_id` int(10) unsigned NOT NULL,
  KEY `file_id` (`file_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `download_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `download_ibfk_2` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `file`;
CREATE TABLE `file` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

nette\caching: 2.3.4 -> frozen nette\caching: 2.4.4 -> frozen

Like I said, table with NO PRIMARY KEY is a problem. No error message, no exception, just idle / frozen up / sleeping or what ever you want to call it. :-)

[hrach]

Ok, thanks a lot, I will look into it :)