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

Mon Apr 4 11:00:40 UTC 2016

t fails with the line:

Error - <class 'ckan.logic.NotFound'>:

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

> 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/61414e53/attachment-0003.html>