search

AlaSQL / alasql

AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.
http://alasql.org
MIT License
7k stars 650 forks source link

Inserting pre-defined serial type gets overwritten #895

Open shivavelingker opened 7 years ago

shivavelingker commented 7 years ago

I have a table that has an ID key set to be a serial datatype. When I insert data, this works as expected, setting the ID to the auto-incremented counter.

However, I occasionally is flush the entire database and inserting new data objects with their own ID keys. AlaSQL overwrites these keys with its own counter, which means if an object has ID = 4 but keys 1-3 have been deleted from the data set, it will be reassigned to 1.

Is this expected behavior? Is there a way to accomplish what I'm looking for? I have a back-end storage file which can be accessed from multiple devices, so for each instance of the app, I flush the localStorage database and reinsert the new data. I could theoretically just update already-existing data and insert the new data (i.e., data created on a different device that is being loaded onto this instance for the first time), but if the ID is overwritten, my app won't work.

mathiasrw commented 7 years ago

Hmmmmmmm. Can you remove the auto-update property, insert and then put it on again?

shivavelingker commented 7 years ago

In the past, I tried something similar where I said alasql.tables.NAME.data = dataSet. This kept my IDs intact, but when inserting a new event, the ID counter starts off at 1 again, creating two duplicate "unique keys" if another event with ID = 1 was in dataSet.

I've currently just changed ID to datatype number and do the auto-increment on my own through selecting the MAX(ID) through alasql. I'm just not sure if the obstacles I faced were expected behavior.

mathiasrw commented 7 years ago

Hmm. shall an autoupdate be overruled if defined in the input....

I think that would be a nice feature.