add instructions on how to persist registered sources

[mhogeweg]

it appears after every node restart, I have to re-register the sources. where in the configuration can I add these so they are automatically loaded upon starting the sample app?

[jgravois]

w/o a PostGIS database cache, an in memory store is used, and consequently cleared on server restarts. you can find this mentioned here.

is there somewhere else you expected to find the information?

[mhogeweg]

thanks, but I have the PostGIS cache configured. however upon inspection, the PostGIS database is completely empty, no tables whatsoever. the PostGIS configuration documents suggest this should be enough to include in the app's default.json:

    "db": {
        "conn": "postgres://localhost/koopdev"
    }

is there no need for username/password to connect? where are the tables being created? when registering a source like so:

curl --data "host=https://westsacramento.demo.socrata.com&id=westsacramento" localhost:1337/koop/socrata

what stores this in the database in which table (I'm not seeing errors in the console at all)?

[jgravois]

when things are configured correctly you should see tables similar to those in the pgAdmin screenshot below:

my ckan:services table in particular has two columns. one which contains the url of the registered service host, and another for its id

i wish i had a better idea of what you're running into. when you say 'no errors in the console' are you talking about the browser console or the terminal window associated with the running instance of koop?

[mhogeweg]

thanks. my koop database schema is empty (0 tables, sequences, triggers, views, etc). nothing gets added apparently.

the errors (or lack thereof) is in the node console. I start the koop sample app with:

node-debug --no-debug-brk server.js

the question is then how can I verify that I have configured things correctly?

[jgravois]

tbh, i don't know. obviously you could trawl the PostGIS logs, but it seems crucial to me that koop provide an indication of whether or not it is not able to read/write to the datastore configured in an application.

[mhogeweg]

Yes. For one, the docs don't mention anything about username/password for the database connection. How have you configured yours?

[jgravois]

no username or password

{
  "server": {
    "port": 1337
  },
  "data_dir": "/usr/local/koop/",
  "db": {
    "conn": "postgres://localhost/foobar"
  }
}

here's the first thing i see when i spin up the application

john6251 at frink in ~/github/koop-sample-app on test [$]
$ npm start

> koop-sample-app@1.0.3 start /Users/john6251/github/koop-sample-app
> node server.js

info: registered cache: postgis 1.3.1

when i try to register a service with socrata, it blows up (throwing an error to the console)

john6251 at frink in ~
$ curl --data "host=https://westsacramento.demo.socrata.com&id=westsacramento" localhost:1337/socrata
{"name":"error","length":107,"severity":"ERROR","code":"42P01","position":"31","file":"parse_relation.c","line":"986","routine":"parserOpenTable"}

when i try to register a service with ckan, it succeeds

john6251 at frink in ~
$ curl --data "host=https://catalog.data.gov&id=datagov" localhost:1337/ckan
{"serviceId":"datagov"}

koop-sample-app - master koop - 2.7.2 koop-socrata - 1.0.1

[mhogeweg]

mine:

D:\My Documents\GitHub\koop-sample-app>npm start

> koop-sample-app@1.0.3 start D:\My Documents\GitHub\koop-sample-app
> node server.js

No Github Token in config. This may cause problems accessing data.
No Github Token in config. This may cause problems accessing data.
Express koop server listening on port 1337

then register a source:

curl --data "host=https://westsacramento.demo.socrata.com&id=westsacramento" localhost:1337/koop/socrata

results in no messages in the node console, just in the console where I registered the source:

{"serviceId":"westsacramento"}

[chelm]

There must be an issue with your db connection @mhogeweg. The first thing koop will do when it successfully connects to postgis is create a couple tables. One of these is called koopinfo.

Would you mind sharing your config file in full?

[chelm]

Also we'll want to make sure that the cache is being registered like so: https://github.com/koopjs/koop-sample-app/blob/master/server.js#L30

[mhogeweg]

default.json:

{
    "server": {
        "port": 1337
    },
    "data_dir": "D:/data/input/koopdev",
    "db": {
        "conn": "postgres://hogeweg.esri.com:5432/koopdev"
    },
    "socrata": {
        "token": "RTttus0HeZn6WCDNLywnrirDh"
    },
    "junar": {
        "token": "8647500ea08ce418b6793365c0d3de38cdd74152"
    }
}

[mhogeweg]

and yes, that register line is there also in server.js

var express = require('express');
var cors = require('cors');
var config = require('config');
var junar = require('koop-junar');
var koop = require('koop')(config);
var koopPg = require('koop-pgcache');
var socrata = require('koop-socrata');
var ckan = require('koop-ckan');
var github = require('koop-github');
var agol = require('koop-agol');
var gist = require('koop-gist');
var path = require('path');
var gpt = require('koop-gpt');
var app = express();
var server;

...

// register koop providers
koop.register(junar);
koop.register(socrata);
koop.register(ckan);
koop.register(github);
koop.register(gist);
koop.register(agol);
koop.register(gpt);
koop.register(koopPg);

[jgravois]

i'm not much of a DBA, but i know Postgres doesn't allow outside connections without additional configuration. have you done that?

if you haven't, even if you're on the same machine, using a fully qualified domain name (hogeweg.esri.com) as opposed to localhost might be a problem.

[mhogeweg]

changed that, but no difference. I'm a little bit of a dba (20 years ago that is) and connecting to a database without providing any credentials is not good practice...

[chelm]

@mhogeweg will you try moving this line koop.register(koopPg); to before the rest of the calls to "register"? Registering the caches should be the first thing you do on the koop object.

It may also be worth sharing a gist of your entire server.js file.

[mhogeweg]

here's a gist

[chelm]

@mhogeweg any reason why you're not using the registerCache method here instead? https://gist.github.com/mhogeweg/43391f37791c2edb84b4#file-server-js-L20

[mhogeweg]

doh! totally missed that line. after switching to use that I see it tries to connect to the database, but using my windows account. my database is not setup for that:

Could not connect to the database: password authentication failed for user "..."

can I provide a database username/password somewhere?

[chelm]

@mhogeweg Cool! At least we have progress. You can send user/pw info like so

postgres://user:password@host:port/database

[mhogeweg]

ok! it connected and created 1 table. then after that the following error:

info: CREATE TABLE "koopinfo" (id varchar(255) PRIMARY KEY, info JSON)
info: CREATE TABLE "kooptimers" (id varchar(255) PRIMARY KEY, expires varchar(25))
D:\My Documents\GitHub\koop-sample-app\node_modules\koop-pgcache\index.js:754
              callback('Failed to create table ' + name)
              ^
TypeError: undefined is not a function
    at D:\My Documents\GitHub\koop-sample-app\node_modules\koop-pgcache\index.js:754:15
    at null.callback (D:\My Documents\GitHub\koop-sample-app\node_modules\koop-pgcache\index.js:724:11)
    at Query.handleError (D:\My Documents\GitHub\koop-sample-app\node_modules\koop-pgcache\node_modules\pg\lib\query.js:106:17)
    at null.<anonymous> (D:\My Documents\GitHub\koop-sample-app\node_modules\koop-pgcache\node_modules\pg\lib\client.js:171:26)
    at emit (events.js:107:17)
    at Socket.<anonymous> (D:\My Documents\GitHub\koop-sample-app\node_modules\koop-pgcache\node_modules\pg\lib\connection.js:109:12)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:538:20)

npm ERR! Windows_NT 6.3.9600
npm ERR! argv "C:\\Program Files\\nodejs\\\\node.exe" "C:\\Program Files\\nodejs\\node_modules\\npm\\bin\\npm-cli.js" "start"
npm ERR! node v0.12.3
npm ERR! npm  v2.9.1
npm ERR! code ELIFECYCLE
npm ERR! koop-sample-app@1.0.3 start: `node server.js`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the koop-sample-app@1.0.3 start script 'node server.js'.
npm ERR! This is most likely a problem with the koop-sample-app package,
npm ERR! not with npm itself.
npm ERR! Tell the author that this fails on your system:
npm ERR!     node server.js
npm ERR! You can get their info via:
npm ERR!     npm owner ls koop-sample-app
npm ERR! There is likely additional logging output above.

npm ERR! Please include the following file with any support request:
npm ERR!     D:\My Documents\GitHub\koop-sample-app\npm-debug.log

D:\My Documents\GitHub\koop-sample-app>

[mhogeweg]

it appears the type JSON does not exist in my postgres. manually running the CREATE TABLE statement for koopinfo fails.

[chelm]

Ahh, im guessing maybe the version in older? Trying upgrading? Or in the DB maybe try: create extension json;

[mhogeweg]

installed postgres 9.4, updated config, then restarted the app. koopinfo and kooptimers have been created. then when registering the source I get:

{"name":"error","length":126,"severity":"ERROR","code":"42P01","position":"31",
"file":"src\\backend\\parser\\parse_relation.c","line":"986","routine":"parserOpenTable"}

[jgravois]

when registering the socrata provider in particular, i encountered the same thing (see my comment above).

[mhogeweg]

and I also get this when registering my Junar and Geoportal Server sources

[chelm]

@jgravois cool so the question becomes what is are socrata and others doing that is different than ckan. My guess is that ckan is doing the something very very simple inside its model's register method

[mhogeweg]

figured out what's different. in the ckan register method the lines commented out below are not there. After commenting them in my geoportal server provider I was able to register a geoportal server and the corresponding table was created in the database. Applied the same change to my Junar and Socrata providers and also successfully registered an instance for those.

  geoportalServer.register = function (id, host, callback) {
    var type = 'geoportalServer:services';
    koop.Cache.db.serviceCount(type, function (error, count) {
      //if (error) {
      //  return callback(error, null);
      //}
      id = id || count++;
      koop.Cache.db.serviceRegister(type, {'id': id, 'host': host}, function (err, success) {
        callback(err, id);
      });
    });
  };