Unique keys on hosted Local SQL Server

Schema & Data
1

Hi!
So I am amazed by DreamFactory. This is exactly what I needed and I am really thankful for people involved.
Yet there is one thing that bugs me. I can’t create unique keys in my database. I am using Free Edition hosted dsp, with Local SQL Databse named ThornDb.
When I create a table with unique property via schema ( accordingly to the wiki ) it updates with response 200. But when I then use API Docs to get back the schema it differs and doesn’t have “is_unique” property. I also tried creating new table in “Schema” view of DF, then added some normal fields and one with checkbox “unique” checked. I updated everything every step and got green notifications saying every action was successful. Yet my field isn’t unique and I can’t make relationship to other table (I know that I can do it with primary key but I would prefer to make it with unique one).
Here is what I post (createTable()):

{
              "name": "Test20152170052",
              "field": [
              {
                      "name": "Id",
                      "type": "id",
                      "default_value": "aaa",
                      "required": true,
                      "allow_null": false,
                      "fixed_length": false,
                      "supports_multibyte": false,
                      "auto_increment": true,
                      "is_primary_key": true,
                      "validation": [
                          ""
                      ],
                      "value": [
                          ""
                      ]
                  },
                  {
                      "name": "unique",
                      "type": "string",
                      "default_value": "aaa",
                      "required": true,
                      "allow_null": false,
                      "fixed_length": false,
                      "supports_multibyte": false,
                      "auto_increment": false,
                      "is_primary_key": false,
                      "is_foreign_key": false,
                      "is_index": false,
                      "is_unique": true,
                      "ref_table": "",
                      "ref_fields": "",
                      "validation": [
                          ""
                      ],
                      "value": [
                          ""
                      ]
                  }
  ]
  }

Here is what i get (describeTable()):

{
      "name": "Test20152170052",
      "label": "Test20152170052",
      "plural": "Test20152170052s",
      "primary_key": "Id",
      "name_field": null,
      "field": [
        {
          "name": "Id",
          "label": "Id",
          "type": "id",
          "db_type": "int(11)",
          "length": 11,
          "precision": 11,
          "scale": 0,
          "default": null,
          "required": false,
          "allow_null": false,
          "fixed_length": false,
          "supports_multibyte": false,
          "auto_increment": true,
          "is_primary_key": true,
          "is_foreign_key": false,
          "ref_table": "",
          "ref_fields": "",
          "validation": [
            ""
          ],
          "value": []
        },
        {
          "name": "unique",
          "label": "Unique",
          "type": "string",
          "db_type": "varchar(255)",
          "length": 255,
          "precision": 255,
          "scale": 0,
          "default": null,
          "required": true,
          "allow_null": false,
          "fixed_length": false,
          "supports_multibyte": true,
          "auto_increment": false,
          "is_primary_key": false,
          "is_foreign_key": false,
          "ref_table": "",
          "ref_fields": "",
          "validation": [
            ""
          ],
          "value": []
        }
      ],
      "related": [],
      "access": [
        "GET",
        "POST",
        "PUT",
        "PATCH",
        "MERGE",
        "DELETE"
      ]
    }

Could You help me? I am fighting with this for hours. Thanks in advice.

2

The is_unique (and I believe supports_multibyte) flag is sent to the DB but is not retrieved back to the DSP at this time. I recommend confirming from the DB side itself that these properties are being set properly.

This non-ideality of the Schema Manager is planned to be resolved for 2.0. At least for now the options are settable although not retrievable.

3

So if it is sent to the DB why am I unable to post this schema?

{"table" : [{
  "name": "UniqueBsse",
  "label": "UniqueBsse",
  "plural": "UniqueBsses",
  "primary_key": "Id",
  "field": [
    {
      "name": "Id",
      "label": "ID",
      "plural": "Ids",
      "type": "id",
      "db_type": "int(11)",
      "is_primary_key": true
    },
    {
      "name": "UniqueString",
      "label": "Unique String",
      "type": "string",
      "length": 10,
      "required": true,
      "allow_null": false,
      "is_foreign_key": false,
      "is_unique": true
    }
  ],
  "access": [
    "GET",
    "POST",
    "PUT",
    "PATCH",
    "MERGE",
    "DELETE"
  ]
},
{
  "name": "UniqueTest2",
  "label": "UniqueTests2",
  "plural": "UniqueTests2",
  "primary_key": "Id",
  "field": [
    {
      "name": "Id",
      "label": "ID",
      "plural": "Ids",
      "type": "id",
      "db_type": "int(11)",
      "is_primary_key": true
    },
    {
      "name": "Unique",
      "label": "Unique Reference",
      "type": "reference",
      "required": true,
      "allow_null": false,
      "is_foreign_key": true,
      "ref_table": "UniqueBsse",
      "ref_fields": "UniqueString"
    }
  ],
  "access": [
    "GET",
    "POST",
    "PUT",
    "PATCH",
    "MERGE",
    "DELETE"
  ]
}]}

It then gives me the 150 error:
[This image is no longer available]

It doesn’t matter if I first Create UniqueBase and then reference table or simultaneously in single post I still get 150 error indicating there is something wrong with my foreign key and it shouldn’t if unique flag was posted correctly. So maybe the problem lays somewhere else? Should I open a ticket on bugtracker?

4

Because the DreamFactory simplified type id translates to INT NOT NULL AUTO_INCREMENT PRIMARY KEY (reference) and then you also specify "is_primary_key": true, you are asking your DSP to set the field to a primary key twice, therefore generating this error from your DB.

5

If you find a bug, then please do open an Issue on the dsp-core repository on GitHub.

However, please do not open GitHub Issues for support requests, like this one. I’d ask that you be reasonably certain that you’ve identified a bug and at least able to provide steps to reproduce and/or the specific line(s) where the bug is found in our source code before opening an Issue.

Support issues may be posted to this forum (as you have) for community responses, or submitted by contacting DreamFactory support directly.

6

It is not the issue. As far as I can tell DSP doesn’t bother if I set “is_primary_key” along with “id” type or not. I created all my tables this way and they all are working. Just to be sure I simplified the call. So:

{"table" : [{
  "name": "UniqueBsse",
  "label": "UniqueBsse",
  "plural": "UniqueBsses",
  "primary_key": "Id",
  "field": [
    {
      "name": "Id",
      "label": "ID",
      "plural": "Ids",
      "type": "id"
    },
    {
      "name": "UniqueString",
      "label": "Unique String",
      "type": "string",
      "length": 10,
      "is_foreign_key": false,
      "is_unique": true
    }
  ],
  "access": [
    "GET",
    "POST",
    "PUT",
    "PATCH",
    "MERGE",
    "DELETE"
  ]
},
{
  "name": "UniqueTest2",
  "label": "UniqueTests2",
  "plural": "UniqueTests2",
  "primary_key": "Id",
  "field": [
    {
      "name": "Id",
      "label": "ID",
      "plural": "Ids",
      "type": "id"
    },
    {
      "name": "Unique",
      "label": "Unique Reference",
      "type": "string",
      "length": 10,
      "required": true,
      "allow_null": false,
      "is_foreign_key": true,
      "ref_table": "UniqueBsse",
      "ref_fields": "UniqueString"
    }
  ],
  "access": [
    "GET",
    "POST",
    "PUT",
    "PATCH",
    "MERGE",
    "DELETE"
  ]
}]}

It still fails (same error) because I try to set reference to an unique field which apparently isn’t unique even if I set “is_unique” flag. As counter example, to prove that every field is set up correctly this code executes without any errors and create relations:

{"table" : [{
  "name": "UniqueBsse",
  "label": "UniqueBsse",
  "plural": "UniqueBsses",
  "primary_key": "UniqueString",
  "field": [
    {
      "name": "Id",
      "label": "ID",
      "plural": "Ids",
      "type": "integer"
    },
    {
      "name": "UniqueString",
      "label": "Unique String",
      "type": "string",
      "is_primary_key":true,
      "length": 10
    }
  ],
  "access": [
    "GET",
    "POST",
    "PUT",
    "PATCH",
    "MERGE",
    "DELETE"
  ]
},
{
  "name": "UniqueTest2",
  "label": "UniqueTests2",
  "plural": "UniqueTests2",
  "primary_key": "Id",
  "field": [
    {
      "name": "Id",
      "label": "ID",
      "plural": "Ids",
      "type": "id"
    },
    {
      "name": "Unique",
      "label": "Unique Reference",
      "type": "string",
      "length": 10,
      "required": true,
      "allow_null": false,
      "is_foreign_key": true,
      "ref_table": "UniqueBsse",
      "ref_fields": "UniqueString"
    }
  ],
  "access": [
    "GET",
    "POST",
    "PUT",
    "PATCH",
    "MERGE",
    "DELETE"
  ]
}]}

So in both cases UniqueTest2 looks the same, and everything works if UniqueString from UniqueBase is a primary key and fails if it is unique but not primary key.
As for me it seems like a bug and uploading those schemas are steps to reproduce the problem. Of course dropping tables is needed between uploads. Unless I am doing something wrong?