drag different records between listgrids

[mindfox]

I am stack with a problem (which might be very simple but for some reason I just can't figure it out) regarding record drag 'n drop functionality between 2 listgrids.

I have 2 datasources for a many-to-many parent-child relation

Datasources

isc.RestDataSource.create({
	ID:"context",
	fields:{
		id:{name:"id", type:"integer", primaryKey:true, hidden:true},
		name:{name:"name", type:"text"},
		displayname:{name:"displayname", type:"text", length:50},
		type:{name:"type", type:"text", length:10},
		comments:{name:"comments", type:"text", length:100},
		canbedeleted:{name:"canbedeleted", type:"text", length:5}
	},
	dataFormat:"json",
	dataURL:"/service.php",
	sendMetaData:true
})

isc.RestDataSource.create({
	ID:"context_group",
	fields:{
		id:{name:"id", type:"integer", primaryKey:true, hidden:true},
		context_group:{name:"context_group", type:"integer", foreignKey:"context.id", foreignKey:"context.id", optionDataSource:"context", displayField:"displayname", valueField:"id"},
		context_grouped:{name:"context_grouped", type:"integer", foreignKey:"context.id", optionDataSource:"context", displayField:"displayname", valueField:"id"}
	},
	dataFormat:"json",
	dataURL:"/service.php",
	sendMetaData:true
})

Then I create 2 listgrids and bind them with those datasources (following the listgrid move example code).

    isc.ListGrid.create({
		autoDraw: false,
		autoSaveEdits:false
		ID:"myList1",
		dataSource:context_group,
		autoFetchData:true,
		canDragResize:true,
		canDragRecordsOut: true,
		canAcceptDroppedRecords: true,
		canReorderRecords: false,
		dragDataAction: "copy",
//		dropValue:{"id"},
		getDropValues: function(record, sourceDS, targetRecord, index, sourceWidget) {
			if (!this.addDropValues) return;
			isc.logWarn("Something was dropped --- " + isc.echoAll(record));
			var dstRec = {context_group: this.context_group, context_grouped: record.id};
			return dstRec;
		},
		fields:[{name:"context_grouped", align:"left"}]
	}),
	isc.VStack.create({width:32, height:74, layoutAlign:"center", membersMargin:10, members:[
		isc.Img.create({src:"icons/32/arrow_right.png", width:32, height:32,
			click:"myList2.transferSelectedData(myList1)"
		}),
		isc.Img.create({src:"icons/32/arrow_left.png", width:32, height:32,
			click:"myList1.transferSelectedData(myList2)"
		})
	]}),
    isc.ListGrid.create({
        ID:"myList2",
		dataSource:"context",
		fields:[
			{name:"displayname"},
			{name:"name"},
			{name:"type"}
		],
		autoFetchData:true,
		canDragResize:true,
		canDragRecordsOut: true,
		canAcceptDroppedRecords: true,
		dragDataAction: "copy",
		canReorderRecords: true
    })
]})

Listgrid "myList1" shows the children of a filtered context.
Listgrid "myList2" shows all the context records, so the user can drag 'n drop records in order to add (or remove) children.
In order to do that, I have to alter the incoming record (from myList2) like this:
a) keep the ID of the record and put it to the "context_group" attribute
b) insert the ID of the context whose the children are shown to myList1

I couldn't find a way to use the "dropValue" attribute so I tried to overload the getDropValues but I still see that the RPC sent to the server has the record from myList2 datasource intact...
What am I missing?

[Isomorphic]

getDropValues() strictly adds properties to the record being dropped. If you instead want to derive some subset of it's data, you could override recordDrop() and start there, however note that dataSource.sendExtraFields:false is another way to get rid of any properties that don't match a declared DataSource field, before the request is actually submitted to the server.

[chimpeenuts]

I'm facing a somewhat similar issue as the original poster.

I have a listgrid bound to datasource A.
I have a treegrid bound to datasource B.

Both datasources define a primaryKey column called 'id'.

My users need to drag a record from the listgrid and drop it onto a folder in the treegrid.

I'm using the addFolderDropHandler to catch the drop event and manipulate the dropped record's attributes/properties (which initially match datasource A schema) to make sure it will adhere to the schema of datasource B before Smart GWT automatically tries to insert the record into the treegrid and bound datasource (which is a SQL datasource to a Postgres DB).

Here is the code in the addFolderDropHandler's onFolderDrop event:

String deviceId = node.getAttribute("id");

// Trying to set ID to null or remove the attribute
// so SmartGWT/Postgres will auto-generate it upon insertion of new record
node.setAttribute("id", "");
node.setAttribute("device_id", deviceId);

node.setAttribute("isFolder", false);
node.setAttribute("isLocked", false);
node.setAttribute("isOpen", false);
node.setAttribute("type", CategoryType.DEVICE.ordinal());
node.setAttribute("userId", getCurrentUserId());

This **almost** works. Unfortunately, I get the following error after dropping a record:

ERROR: null value in column "id" violates not-null constraint

My Datasource A ds.xml file:

<DataSource ID="host" serverType="sql" tableName="device"
  dropExtraFields="true">
  <fields>
    <field name="id" type="sequence" hidden="true" primaryKey="true" />
    <field name="hostName" type="text" title="Host(s)" />
  </fields>

My Datasource B ds.xml file:

<DataSource ID="categoryDevice" tableName="categorydevice" serverType="sql"
  dropExtraFields="true">
  <fields>
    <field name="id" type="integer" hidden="true" primaryKey="true" />
    <field name="title" type="text" title="Categories" />
    <field name="device_id" type="integer" />
    <field name="name" type="text" tableName="device" nativeName="hostName"/>
    <field name="userId" type="integer" />
    <field name="type" type="number" hidden="true" />
    <field name="parentId" type="number" hidden="true" foreignKey="id"/>
    <field name="isFolder" type="boolean" hidden="true" nativeName="category"/>
    <field name="isOpen" type="boolean" hidden="true" nativeName="open"/>
    <field name="isLocked" type="boolean" hidden="true" nativeName="locked"/>
  </fields>

By looking at the POST transaction, I can see that Smart GWT is passing a NULL or nil ID to postgres. Postgres has the id column defined as a 'bigserial' and as a primary key. I thought Postgres would auto-generate an ID if it receives a null value for it...but not so.

Here is the Smart GWT RPC POST transaction:

<transaction xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance" xsi:type="xsd:Object"><transactionNum xsi:type="xsd:long">7</transactionNum><operations xsi:type="xsd:List"><elem xsi:type="xsd:Object"><values xsi:type="xsd:Object"><id xsi:nil="true"/>...

So I thought a viable solution would be to remove the "id" attribute from the dropped record. But how can I remove the "id" attribute from the node/record? I'm assuming if I can remove the "id" attribute then Smart GWT and/or Postgres will auto-generate the next serial or sequence number for the new record's id column.

I've tried setting it to "", setting it to a null object, etc. I don't want to specifically define an id value because I want it to be automatically generated/inserted when the dragged record is dropped on the tree node.

I've tried setting 'id' to a sequence type in Datasource B but then the treegrid complains it can't find that column for the foreignKey relationship defined for the parentId field.

How can I get SmartGWT to recognize that the id attribute for this datasource-bound treegrid is auto-generated so that dropped records that do contain an id property (but from a different datasource) will be ignored?

NOTE: This appeared to work fine when using MySQL. Upon switching to Postgres this problem emerged.

UPDATE: Here's the stack trace from the console. Is it possible that the error is because Smart GWT is trying to perform an 'update' operation when really it should be performing a 'create' or 'insert' operation and by doing that Postgres wouldn't care that id = NULL or NIL?

=== 2012-06-01 00:00:57,255 [l0-2] WARN  RequestContext - dsRequest.execute() failed: 
org.postgresql.util.PSQLException: ERROR: null value in column "category" violates not-null constraint
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:530)
	at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:482)
	at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:604)
	at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:403)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1388)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:293)
	at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:245)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1291)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1948)

[chimpeenuts]

As a quick follow-up, this worked fine with a MySQL backend. But fails with a Postgres backend.

That leads me to two thoughts:

1) MySQL accepts null or nil values for it's auto-sequence primary key fields and will happily auto-generate one AND/OR MySQL ignores any values passed to it for auto-sequence primary key fields because it knows it will be generating the values. Perhaps Postgres does not behave the same way.

2) The SmartGWT internal logic when inserting records into Postgres databases does not correctly handle Postgres' expectations for auto-sequence primary key fields either by using an IDENTIY function to get the next auto-sequence number and adding it to the SQL statement or by omitting such fields for an insert operation.