Using application-generated row IDs with Editor causes an exception

ahanssens@cps247.com

I'm using Editor 1.5.0 with the .NET libraries.

My database is using row IDs which are generated by my application. Based upon the documentation at http://editor.datatables.net/manual/net/conditions, I added this field definition:

  field_list.Add(new Field("RowID")
    .Set(Field.SetType.Create)
    .SetValue(() => AllocateRowID()));

However, this ends up causing an exception on line 877 of editor.cs (which is part of Editor._Insert()).

        // Insert the new row
        var id = _InsertOrUpdate(null, values);

        // Was the primary key sent? Unusual, but it is possible
        var pkeyField = _FindField(_pkey, "name");
        if (pkeyField != null && pkeyField.Apply("create", values))
        {
            id = values[_pkey];
        }

The call to _InsertOrUpdate finishes without an error and returns the correct row ID. However, the attempt to read the ID from "values[_pkey]" fails because there is no "RowID" key in the dictionary.

There is a second bug involving the "values[_pkey]" line.

If Editor._Insert() is being called as part of an attempt to work with a single table, "_pkey" contains a column name and "values" is a dictionary which maps a column name to a column value.

However, if it is being called as part of an attempt to do a left join, "_pkey" contains a value like "MyTable.RowID" and "values" is a dictionary which maps a table name to a dictionary of column names and values. So in the case of a left join, the code would need to be something like this:

            string[] split_string = _pkey.Split('.');
            id = values[split_string[0]][split_string[1]];

though this still would not work since the "RowID" key is not in the second-level dictionary.

I did not check to see what the variables contain during a multi-join.

allan

Hi,

Thanks for letting me know about this - I must confess I hadn't considered such a use, but it does make perfect sense!

The fix is to use the following:

                id = pkeyField.Val("get", values);

Rather than the id = values[_pkey]; line. Similarly in the _Update method the code should be updated to also use Field.Val() rather than accessing the property from the data:

            var getId = pkeyField != null && pkeyField.Apply("edit", values) ?
                pkeyField.Val("get", values) :
                id;

If you could let me know how you get on with that, that would be great.

Allan

ahanssens@cps247.com

I applied the code to the _Insert function, and it results in the id variable being set to null. (The direction is "get", so the code inside Field.Val tries to look up the value of RowID in the dictionary, and it's not there.) The id variable is used in several places below that point, so it looks like that will cause problems.

If you were trying to reach the code at the bottom of Field.Val (which calls _GetAssignedValue(_setValue)), the problem with that is that making another call to AllocateRowID would return a new and different value than the previous call. (The function basically behaves the same as it does in most ORM systems -- it increments the value in a "next row ID" table and returns the pre-incremented value.) The SetValue delegate isn't given any information (like a context object) by the Editor code, so I don't think there is any way for it to know that it needs to return an old value as opposed to a new value.

The only other way I can think of to solve this problem is to have the action handler make a copy of the form variables in Request.Form, add a row ID to the copy (as if it had come from the JavaScript), and pass the copy to Editor.Process(). This is what I was doing when I was using Editor 1.4.2. However, the changes to allow multi-row editing in Editor 1.5.0 broke this code (because the key names have a different format), so I'd prefer not to do this. I'd rather solve the problem in a way that's forward-compatible if possible.

allan

Agreed. Let me have a bit of a think about this (properly thinking it through this time!) and get back to you.

Allan

allan

Hi,

The "get" in the above code should be "set" to have it trigger the SetValue function or read the data from the POST if required.

However, while that addresses the basic utility of that code, it doesn't resolve the issue of multiple calls to AllocateRowID (or whatever function is used).

I think that at the moment, the only way to address that will be to run that function once, yourself, storing the result in a variable and then returning that variable in the closure function, rather than running AllocateRowID in the function. Its a little messy, but that is probably the only way at the moment.

Regards,
Allan

allan

Actually, I might have a nicer way to implement that that a custom variable - which is to use the server-side events in 1.5+.

You could use the PreCreate event to set the value that should be used for the primary key:

            editor.PreCreate += (sender, e) =>
                editor.Field("myPkey").SetValue( AllocateRowID() );

Allan

ahanssens@cps247.com

I see what you're saying. The Field objects get recreated every time the JavaScript makes a call to the table interface function in the C# code. This would cause a new closure to be created for the SetValue() function. The closure could have a nullable variable in it, which is given a value the first time the closure is called, and the contents of that variable are returned in subsequent calls.

However, this seems unsafe to me. The Field object is part of the definition of the entire table, not of a particular row. If it is possible for the JavaScript to ask for more than one row to be created at a time, all of the rows would have the same row ID. I'm not sure if this is possible right now, but it might become possible in a future version of the library.

Unfortunately, I found that to support some other requirements of my application (things not directly related to the Editor), I had to know the ID of the new row before I called Editor.Process(). So I changed my code back to the way it was before, where it creates a fake form variable with the row ID in it.

It would be helpful if, in a future version of the .NET libraries, there is a formally supported way to add or change entries in the form variables. But when I think of how such an API might be structured, it seems to me that the function signatures would still be strongly related to the internal structure of the form variables, so I don't know if it would help that much.

In case anyone else has this problem and runs across this thread, I've attached the code I'm using to create the fake form variable. The "Row ID" field should be defined with .Set(Field.SetType.Create). This code works for single tables and left joins, but has never been tested with multi-joins.

DtResponse ajax_datatables_table_interface_lowlevel(Editor editor_object, string primary_table_name) {
     List<KeyValuePair<string,string>> kvp_list = new List<KeyValuePair<string,string>>();
  DtRequest dt_request;
  NameValueCollection form_variables_copy;
  Dictionary<string, object> http_data;
  long primary_object_id;
  string table_substring;
  string form_variable_key;
  Dictionary<string,object> first_level_of_dictionary;
  Dictionary<string,object> second_level_of_dictionary;
  object third_level_of_dictionary;

  kvp_list.AddRange(Request.Form.AllKeys.Select(x => new KeyValuePair<string,string>(x, Request.Form[x])));

  http_data = DtRequest.HttpData(kvp_list);

  dt_request = new DtRequest(kvp_list);

  form_variables_copy = new NameValueCollection(Request.Form);

  if (dt_request.RequestType == DtRequest.RequestTypes.EditorCreate) {
    primary_object_id = AllocateRowID();

/* If this is a left join operation, the form variable will include the table name, which will cause the dictionary in http_data
 * to have an extra level. */
    first_level_of_dictionary = (Dictionary<string,object>) http_data["data"];
    second_level_of_dictionary = (Dictionary<string,object>) first_level_of_dictionary.Values.First();
    third_level_of_dictionary = second_level_of_dictionary.Values.First();

    if (third_level_of_dictionary is Dictionary<string,object>)
      table_substring = string.Format("[{0}]", primary_table_name);
    else
      table_substring = "";

    form_variable_key = string.Format("data[0]{0}[RowID]", table_substring);

    form_variables_copy.Add(form_variable_key, primary_object_id.ToString());
  }

  editor_object.Process(form_variables_copy);

  return editor_object.Data();
}