My app works like this, I have a local database that is filled with data that comes from my API, and when I have a new data inserted in my API the app checks for the last modified item and synchronize it, and in order to achieve that I'm using INSERT OR REPLACE statement, but it is messing up with my "faturamento_id", it is deleting the ids and replacing with new ones, I want it to continue auto increment(if it is possible) when there is new data to synchronize. How can I do that?
angular.forEach(item.faturamentos, function (fat) {
db.transaction(
function (tx) {
tx.executeSql('INSERT OR REPLACE INTO faturamento_pedidos (valor_a_faturar, ' +
'nota_fiscal, ' +
'_criado,' +
'_modificado , ' +
'_status, ' +
'id_rm, ' +
'cod_id, ' +
'id_rm_pedido, ' +
'id_rm_empresa, ' +
'data, ' +
'informacoes_adicionais ) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
[
fat.valor_a_faturar,
fat.nota_fiscal,
fat.criado,
fat.modificado,
fat.status,
fat.id,
fat.cod_id,
fat.id_rm_pedido,
fat.id_rm_empresa,
fat.data,
fat.informacoes_adicionais
]);
},
txErrorHandler,
function () {
log('Record inserted successfully');
}
);
});
TABLE:
tx.executeSql("CREATE TABLE IF NOT EXISTS faturamento_pedidos (" +
"faturamento_id Integer PRIMARY KEY AUTOINCREMENT, " +
"_criado Text, " +
"_modificado Text, " +
"_status Text, " +
"id_rm Integer, " +
"id_rm_pedido Integer, " +
"id_rm_empresa Integer, " +
"cod_id Text, " +
"valor_a_faturar Text, " +
"nota_fiscal Text, " +
"data Text, " +
"informacoes_adicionais Text," +
"CONSTRAINT unique_id_rm UNIQUE ('id_rm'))");
tx.executeSql('CREATE INDEX IF NOT EXISTS "faturamento_pedidos.index_faturamento_id" ON "faturamento_pedidos"("faturamento_id");');
INSERT OR REPLACE always removes the old row, if it exists.
However, there is no reason to use a single SQL statement. Just try to update the old row, and if it was not found, you know you have to insert a new one:
tx.executeSql("UPDATE ...",
[...],
function(tx, result) {
if (result.rowsAffected == 0)
tx.executeSql("INSERT ...", [...]);
});