[ckan-dev] Use view instead of table for datastore resource

Mon Apr 4 10:59:14 UTC 2016

By creating another view between the actual table and the view with same
name as resource_id things work WHEN I'm logged in on the CKAN web GUI.
When logging out, things fails again. Wow, seems very complicated to use a
view as resource for the datastore instead of a table.

On Mon, Apr 4, 2016 at 12:34 PM, Michael Nielsen <mic.niel84 at gmail.com>
wrote:

> Investigating further, it seems as if my view gets assigned an alias
> "test", which all the other real tables doesn't get assigned.
> It's coming from a view called "_table_metadata" which gets the alias from
> a table called pg_class (and it's column "relname".
>
> On Mon, Apr 4, 2016 at 12:21 PM, Michael Nielsen <mic.niel84 at gmail.com>
> wrote:
>
>> Just to clarify, when using DROP TABLE resource_id and then manually
>> creating a table it still works.
>> So it's the fact that it's a view and not a table which seems to cast an
>> error.
>> But somehow I guess the datastore shouldn't are if it's a table or a view
>> as they have somewhat same behaviour?
>>
>> On Mon, Apr 4, 2016 at 12:05 PM, Michael Nielsen <mic.niel84 at gmail.com>
>> wrote:
>>
>>> I would like to use a view instead of a table as a DataStore resource.
>>>
>>> I'm creating my DataStore only resource via the API datastore_create:
>>>
>>>     {
>>>     "force": true,
>>>     "resource": {
>>>         "package_id": "3d2101e5-0166-46ba-b112-8b9da5882a42",
>>>         "name": "Test"
>>>         }
>>>     }
>>>
>>> The DataStore table is created successfully with 2 empty columns "_id"
>>> and "_full_text".
>>>
>>> I'm then trying to use:
>>>
>>>     DROP TABLE "resource_id";
>>>
>>> And create a similar view:
>>>
>>>     CREATE VIEW "resource_id" AS (
>>>         SELECT row_number() OVER (ORDER BY id) AS _id,
>>>         to_tsvector('') AS _full_text,
>>>         column1, column2
>>>         FROM schema-test.my-table
>>>     );
>>>
>>> When querying the resource in PSQL everything seems to match other
>>> normal DataStore resources. But CKAN fails to show the resource. I'm
>>> getting an internal error. The log shows:
>>>
>>>     ...SELECT * FROM "test" LIMIT 1'], 'orig': ['relation "test" does
>>> not exist\\nLINE 1: SELECT * FROM "test" LIMIT 1\\n
>>>  ^\\n']}, 'query': ['Invalid query']}...
>>>
>>> So it seems as if there is created some sort of alias to the table,
>>> which gets deleted when I'm running DROP TABLE.
>>> Can I recreate that correctly or is the error some where else?
>>>
>>>
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20160404/e2130b95/attachment-0003.html>