Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot insert a record with an id of 0 (zero) if the id field is a primary key #204

Open
JayNaire opened this issue Jul 16, 2020 · 0 comments

Comments

@JayNaire
Copy link

JayNaire commented Jul 16, 2020

Which version are you using?
@nano-sql/[email protected]

Describe the undocumented(?) featurette
Cannot insert a record with an id of 0 (zero) if the (int) id field has a primary key constraint; upsert succeeds but the record returned will have an id of 1 not 0 (in a previously empty table).
Upsert works fine if the id field is not a pk. However, if you subsequently specify the id field as a pk and query with ["id", "=", 0] the record will not be found until the pk constraint is lifted.
Edit: Further investigation shows related behaviour if the id field is not a pk but has a unique index constraint. Then it is possible to insert a record with id=0, but it will not be found without the predicate code shown below in Workaround.

Expected behavior
Would expect an id of 0 to be acceptable in a pk field?

Workaround
Do not specify id field as pk
Add unique index instead
Change predicates:
Use .where(["id", "<=", id], "AND", ["id", ">=", id]) which will find id == 0
instead of .where(["id", "=", id]) which won't

Example: refers to main problem not the workaround Link to CodePen

const { resolvePath } = require("@nano-sql/core/lib/utilities")
const nSQL = require("@nano-sql/core").nSQL //@nano-sql/[email protected]
const moment = require("moment")

nSQL().createDatabase({
  mode: "TEMP",
  id: "id0",
  tables: [
    {
      name: "notes",
      model: {
        // pk == primary key, ai == auto increment,
        //"id:int": { min: 0, pk: true, ai: true }, //can't insert a record with id = 0
        "id:int": { min: 0, pk: false, ai: true }, //works ok - can insert id = 0
        "parentId:int": { default: 0 }, // parent note or top level if 0
        "special:bool": { default: false },
        "title:string": { default: "New Note" },
        "contents:string": { default: "" },
        "selected:bool": { default: false },
        "created:date": { default: () => moment().toJSON() },
        "lastModified:date": { default: () => moment().toJSON() }
      }
    }
  ]
})
  .then(() => {
    //insert note with id = 0
    nSQL("notes").query("upsert", { id: 0 })
      .exec()
      .then((rows) => {
        console.log(`Line 31: rows: ${JSON.stringify(rows)}`) // works BUT - id is not 0 : [{"id":1,"parentId":0, ... }]
      })
  })

please provide a link to a CodePen
Link to CodePen

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant