dynamobi/conn-couchdb — Issue #1: Connector does not work without a reduce operator on view
Author: nagoodman
Created: 2011-12-16 18:15:58+00:00
State: open

Without reduce, couchdb emits id, key, values but the connector only matches columns in foreign table to keys/values. You get a mismatch in column numbers. If you try and add the id field (to match the numbers, you get a class cass exception. We have a test case (fpds/all) that can be used to repro/resolve.

```
create or replace server fpds_couch
. . . . . . . . > foreign data wrapper couchdb_wrapper
. . . . . . . . > options( username 'dynamobi', password 'removed', url 'http://dynamobi.cloudant.com/fpds_test');

0: jdbc:luciddb:> create or replace foreign table couchdb.all_contracts
. . . . . . . . > (AMOUNT_OBLIGATED DECIMAL(19,2)
. . . . . . . . > , COMPETE_CAT VARCHAR(255)
. . . . . . . . > , SIGNED_DATE VARCHAR(100)
. . . . . . . . > , CURRENT_COMPLETION_DATE VARCHAR(100)
. . . . . . . . > , ULTIMATE_COMPLETION_DATE VARCHAR(100)
. . . . . . . . > , CONT_ANNUAL_REV DECIMAL(19,2)
. . . . . . . . > , CONT_NUM_EMPLOYEES INTEGER
. . . . . . . . > , CONT_SMALL_BUS VARCHAR(10)
. . . . . . . . > , CONT_WOMENOWNED_BUS VARCHAR(10)
. . . . . . . . > , CONT_CITY VARCHAR(255)
. . . . . . . . > , CONT_NAME VARCHAR(255)
. . . . . . . . > , CONT_COUNTRY VARCHAR(100)
. . . . . . . . > , CONT_STATE VARCHAR(50)
. . . . . . . . > , PROD_OR_SERVICE_CODE VARCHAR(255)
. . . . . . . . > , AGENCY_FUNDINGID VARCHAR(255)
. . . . . . . . > , AGENCY_CAT VARCHAR(100)
. . . . . . . . > , AGENCY_MOD VARCHAR(100)
. . . . . . . . > , FISCAL_YEAR INTEGER
. . . . . . . . > , AGENCY_ID VARCHAR(255)
. . . . . . . . > )
. . . . . . . . > server fpds_couch
. . . . . . . . > options (view '_design/all/_view/all');
No rows affected (0.425 seconds)
0: jdbc:luciddb:> select * from couchdb.all_contracts;
Error: Read 20 params and 19 columns, which need to match. Did you add column(s) for both the key and value? (state=,code=0)
```

Adding the ID yields a different error

```
0: jdbc:luciddb:> select * from couchdb.all_contracts;
Error: Read 20 params and 19 columns, which need to match. Did you add column(s) for both the key and value? (state=,code=0)
0:

jdbc:luciddb:> create or replace foreign table couchdb.all_contracts
. . . . . . . . > ("ID" VARCHAR(100)
. . . . . . . . > , AMOUNT_OBLIGATED DECIMAL(19,2)
. . . . . . . . > , COMPETE_CAT VARCHAR(255)
. . . . . . . . > , SIGNED_DATE VARCHAR(100)
. . . . . . . . > , CURRENT_COMPLETION_DATE VARCHAR(100)
. . . . . . . . > , ULTIMATE_COMPLETION_DATE VARCHAR(100)
. . . . . . . . > , CONT_ANNUAL_REV DECIMAL(19,2)
. . . . . . . . > , CONT_NUM_EMPLOYEES INTEGER
. . . . . . . . > , CONT_SMALL_BUS VARCHAR(10)
. . . . . . . . > , CONT_WOMENOWNED_BUS VARCHAR(10)
. . . . . . . . > , CONT_CITY VARCHAR(255)
. . . . . . . . > , CONT_NAME VARCHAR(255)
. . . . . . . . > , CONT_COUNTRY VARCHAR(100)
. . . . . . . . > , CONT_STATE VARCHAR(50)
. . . . . . . . > , PROD_OR_SERVICE_CODE VARCHAR(255)
. . . . . . . . > , AGENCY_FUNDINGID VARCHAR(255)
. . . . . . . . > , AGENCY_CAT VARCHAR(100)
. . . . . . . . > , AGENCY_MOD VARCHAR(100)
. . . . . . . . > , FISCAL_YEAR INTEGER
. . . . . . . . > , AGENCY_ID VARCHAR(255)
. . . . . . . . > )
. . . . . . . . > server fpds_couch
. . . . . . . . > options (view '_design/all/_view/all');
No rows affected (0.393 seconds)
0: jdbc:luciddb:> select * from couchdb.all_contracts;
Error: java.lang.ClassCastException: java.lang.String cannot be cast to java.util.List (state=,code=0)
```

Raw output from view without reduce/etc. at URL `http://dynamobi.cloudant.com/fpds_test/_design/all/_view/all`

```
{"total_rows":498,"offset":0,"rows":[
{"id":"00e2f6dc7fe914d3efa9adf4c0c9b9dd","key":"00e2f6dc7fe914d3efa9adf4c0c9b9dd","value":{"AMOUNT_OBLIGATED":"23360.00","COMPETE_CAT":"Not competed","SIGNED_DATE":"05/24/2007","CURRENT_COMPLETION_DATE":"06/07/2007","ULTIMATE_COMPLETION_DATE":"06/07/2007","CONT_ANNUAL_REV":"0","CONT_NUM_EMPLOYEES":"0","CONT_SMALL_BUS":"Yes","CONT_WOMENOWNED_BUS":"No","CONT_CITY":"RIVIERA BEACH","CONT_NAME":"ADVANCED AIR OF WEST PALM BCH","CONT_COUNTRY":"USA","CONT_STATE":"FL: Florida","PROD_OR_SERVICE_CODE":"3120: Bearings, Plain, Unmounted","AGENCY_FUNDINGID":"AIR FORCE, Department of the (Headquarters, USAF)","AGENCY_CAT":"Dept. of Defense","AGENCY_MOD":"Dept. of Defense","FISCAL_YEAR":"2007","AGENCY_ID":"9700: DEFENSE, Department of"}},
{"id":"014f75a43ed9ffe4001ec7dd36fe6422","key":"014f75a43ed9ffe4001ec7dd36fe6422","value":{"AMOUNT_OBLIGATED":"72708.00","COMPETE_CAT":"Not competed","SIGNED_DATE":"09/04/2007","CURRENT_COMPLETION_DATE":"12/31/2007","ULTIMATE_COMPLETION_DATE":"12/31/2007","CONT_ANNUAL_REV":"9766831","CONT_NUM_EMPLOYEES":"25","CONT_SMALL_BUS":"No","CONT_WOMENOWNED_BUS":"No","CONT_CITY":"WASHINGTON","CONT_NAME":"NOVA INTERNATIONAL, INC.","CONT_COUNTRY":"USA","CONT_STATE":"DC: District of Columbia","PROD_OR_SERVICE_CODE":"7105: Household Furniture","AGENCY_FUNDINGID":"STATE, Department of","AGENCY_CAT":"Dept. of State","AGENCY_MOD":"Dept. of State","FISCAL_YEAR":"2007","AGENCY_ID":"1900: STATE, Department of"}}
```