98
99
struct auxdbf_fld *last;
102
struct resultset_values
103
/* a column value in a duplicated row */
104
/* a struct wrapping values from a resultset */
107
106
sqlite3_int64 int_value;
108
107
double dbl_value;
109
const char *txt_value;
108
unsigned char *txt_blob_value;
112
struct resultset_comparator
114
/* object for comparing two rows of the same resultset */
115
struct resultset_values *previous;
116
struct resultset_values *current;
118
sqlite3_int64 previous_rowid;
119
sqlite3_int64 current_rowid;
122
static struct resultset_comparator *
123
create_resultset_comparator (int columns)
125
/* creating an empty resultset comparator object */
127
struct resultset_comparator *p =
128
malloc (sizeof (struct resultset_comparator));
129
p->num_columns = columns;
130
p->previous_rowid = -1;
131
p->current_rowid = -1;
132
p->previous = malloc (sizeof (struct resultset_values) * columns);
133
p->current = malloc (sizeof (struct resultset_values) * columns);
134
for (i = 0; i < columns; i++)
136
struct resultset_values *value = p->previous + i;
137
value->type = SQLITE_NULL;
138
value->txt_blob_value = NULL;
139
value = p->current + i;
140
value->type = SQLITE_NULL;
141
value->txt_blob_value = NULL;
147
destroy_resultset_comparator (struct resultset_comparator *ptr)
149
/* memory cleanup - destroying a resultset comparator object */
153
for (i = 0; i < ptr->num_columns; i++)
155
struct resultset_values *value = ptr->previous + i;
156
if (value->txt_blob_value != NULL)
157
free (value->txt_blob_value);
158
value = ptr->current + i;
159
if (value->txt_blob_value != NULL)
160
free (value->txt_blob_value);
162
if (ptr->previous != NULL)
163
free (ptr->previous);
164
if (ptr->current != NULL)
170
save_row_from_resultset (struct resultset_comparator *ptr, sqlite3_stmt * stmt)
172
/* saving the current row values */
113
struct dupl_column *next;
118
/* a duplicated row with column values */
120
struct dupl_column *first;
121
struct dupl_column *last;
126
clean_dupl_row (struct dupl_row *str)
128
/* destroying a duplicated row struct */
129
struct dupl_column *p;
130
struct dupl_column *pn;
142
add_to_dupl_row (struct dupl_row *str, const char *name)
144
/* adding a column to the duplicated row struct */
146
struct dupl_column *p = malloc (sizeof (struct dupl_column));
149
p->name = malloc (len + 1);
150
strcpy (p->name, name);
152
p->type = SQLITE_NULL;
154
if (str->first == NULL)
162
set_int_value (struct dupl_row *str, int pos, sqlite3_int64 value)
164
/* setting up an integer value */
165
struct dupl_column *p = str->first;
170
p->type = SQLITE_INTEGER;
171
p->int_value = value;
179
set_double_value (struct dupl_row *str, int pos, double value)
181
/* setting up a double value */
182
struct dupl_column *p = str->first;
187
p->type = SQLITE_FLOAT;
188
p->dbl_value = value;
196
set_text_value (struct dupl_row *str, int pos, const char *value)
198
/* setting up a text value */
199
struct dupl_column *p = str->first;
204
p->type = SQLITE_TEXT;
205
p->txt_value = value;
213
set_blob_value (struct dupl_row *str, int pos, const void *blob, int size)
215
/* setting up a blob value */
216
struct dupl_column *p = str->first;
221
p->type = SQLITE_BLOB;
231
set_null_value (struct dupl_row *str, int pos)
233
/* setting up a NULL value */
234
struct dupl_column *p = str->first;
239
p->type = SQLITE_NULL;
247
reset_query_pos (struct dupl_row *str)
249
/* resetting QueryPos for BLOBs */
250
struct dupl_column *p = str->first;
175
const unsigned char *p;
178
ptr->current_rowid = sqlite3_column_int64 (stmt, 0);
179
for (i = 0; i < ptr->num_columns; i++)
181
struct resultset_values *value = ptr->current + i;
182
value->type = sqlite3_column_type (stmt, i + 1);
186
value->int_value = sqlite3_column_int64 (stmt, i + 1);
189
value->dbl_value = sqlite3_column_double (stmt, i + 1);
192
p = sqlite3_column_text (stmt, i + 1);
193
size = strlen ((const char *) p);
194
value->txt_blob_value = malloc (size + 1);
195
strcpy ((char *) (value->txt_blob_value), (const char *) p);
198
p = sqlite3_column_blob (stmt, i + 1);
199
size = sqlite3_column_bytes (stmt, i + 1);
200
value->txt_blob_value = malloc (size);
201
memcpy (value->txt_blob_value, p, size);
202
value->txt_blob_size = size;
259
check_dupl_blob2 (struct dupl_column *ptr, const void *blob, int size)
209
resultset_rows_equals (struct resultset_comparator *ptr)
261
/* checking a BLOB value */
262
if (ptr->type != SQLITE_BLOB)
264
if (ptr->size != size)
266
if (memcmp (ptr->blob, blob, size) != 0)
211
/* comparing the current and previous row from the resultset */
215
for (i = 0; i < ptr->num_columns; i++)
217
struct resultset_values *val_prev = ptr->previous + i;
218
struct resultset_values *val_curr = ptr->current + i;
219
if (val_prev->type != val_curr->type)
221
switch (val_prev->type)
224
if (val_prev->int_value != val_curr->int_value)
228
if (val_prev->dbl_value != val_curr->dbl_value)
233
((const char *) (val_prev->txt_blob_value),
234
(const char *) (val_curr->txt_blob_value)) != 0)
238
if (val_prev->txt_blob_size != val_curr->txt_blob_size)
241
(val_prev->txt_blob_value, val_curr->txt_blob_value,
242
val_curr->txt_blob_size) != 0)
272
check_dupl_blob (struct dupl_row *str, int pos, const void *blob, int size)
274
/* checking a BLOB value */
275
struct dupl_column *p = str->first;
278
if (p->query_pos == pos)
280
return check_dupl_blob2 (p, blob, size);
251
get_current_resultset_rowid (struct resultset_comparator *ptr)
253
/* returns the current ROWID */
256
return ptr->current_rowid;
260
reset_resultset_current_row (struct resultset_comparator *ptr)
262
/* resetting the resultset current row values */
266
ptr->current_rowid = -1;
267
for (i = 0; i < ptr->num_columns; i++)
269
struct resultset_values *value = ptr->current + i;
270
value->type = SQLITE_NULL;
271
if (value->txt_blob_value != NULL)
272
free (value->txt_blob_value);
273
value->txt_blob_value = NULL;
278
swap_resultset_rows (struct resultset_comparator *ptr)
280
/* resetting the resultset comparator */
284
ptr->previous_rowid = ptr->current_rowid;
285
ptr->current_rowid = -1;
286
for (i = 0; i < ptr->num_columns; i++)
288
struct resultset_values *val_prev = ptr->previous + i;
289
struct resultset_values *val_curr = ptr->current + i;
290
if (val_prev->txt_blob_value != NULL)
291
free (val_prev->txt_blob_value);
292
val_prev->type = val_curr->type;
293
val_prev->int_value = val_curr->int_value;
294
val_prev->dbl_value = val_curr->dbl_value;
295
val_prev->txt_blob_value = val_curr->txt_blob_value;
296
val_prev->txt_blob_size = val_curr->txt_blob_size;
297
val_curr->type = SQLITE_NULL;
298
val_curr->txt_blob_value = NULL;
287
302
static struct auxdbf_list *
372
387
SPATIALITE_DECLARE int
373
load_shapefile_ex (sqlite3 * sqlite, char *shp_path, char *table, char *charset,
374
int srid, char *g_column, char *gtype, char *pk_column,
375
int coerce2d, int compressed, int verbose, int spatial_index,
376
int *rows, char *err_msg)
388
load_shapefile_ex (sqlite3 * sqlite, char *shp_path, char *table,
389
char *charset, int srid, char *g_column, char *gtype,
390
char *pk_column, int coerce2d, int compressed, int verbose,
391
int spatial_index, int *rows, char *err_msg)
393
return load_shapefile_ex2 (sqlite, shp_path, table, charset, srid, g_column,
394
gtype, pk_column, coerce2d, compressed, verbose,
395
spatial_index, 0, rows, err_msg);
398
SPATIALITE_DECLARE int
399
load_shapefile_ex2 (sqlite3 * sqlite, char *shp_path, char *table,
400
char *charset, int srid, char *g_column, char *gtype,
401
char *pk_column, int coerce2d, int compressed,
402
int verbose, int spatial_index, int text_dates, int *rows,
378
405
sqlite3_stmt *stmt = NULL;
4314
do_delete_duplicates2 (sqlite3 * sqlite, sqlite3_stmt * stmt1,
4315
struct dupl_row *value_list, int *count)
4402
do_delete_duplicates2 (sqlite3 * sqlite, sqlite3_int64 rowid,
4403
sqlite3_stmt * stmt1)
4317
4405
/* deleting duplicate rows [actual delete] */
4323
sqlite3_stmt *stmt2 = NULL;
4324
struct dupl_column *col;
4331
gaiaOutBuffer sql_statement;
4332
gaiaOutBuffer where;
4333
gaiaOutBuffer condition;
4336
reset_query_pos (value_list);
4337
gaiaOutBufferInitialize (&sql_statement);
4338
gaiaOutBufferInitialize (&where);
4339
gaiaOutBufferInitialize (&condition);
4341
/* preparing the query statement */
4342
gaiaAppendToOutBuffer (&sql_statement, "SELECT ROWID");
4343
gaiaAppendToOutBuffer (&where, "\nWHERE ");
4344
col = value_list->first;
4347
if (col->type == SQLITE_BLOB)
4349
sql = sqlite3_mprintf (", %s", col->name);
4350
gaiaAppendToOutBuffer (&sql_statement, sql);
4352
col->query_pos = qcnt++;
4354
else if (col->type == SQLITE_NULL)
4359
sql = sqlite3_mprintf ("%s", col->name);
4360
gaiaAppendToOutBuffer (&condition, sql);
4365
sql = sqlite3_mprintf (" AND %s", col->name);
4366
gaiaAppendToOutBuffer (&condition, sql);
4369
gaiaAppendToOutBuffer (&condition, " IS NULL");
4370
gaiaAppendToOutBuffer (&where, condition.Buffer);
4371
gaiaOutBufferReset (&condition);
4378
sql = sqlite3_mprintf ("%s", col->name);
4379
gaiaAppendToOutBuffer (&condition, sql);
4384
sql = sqlite3_mprintf (" AND %s", col->name);
4385
gaiaAppendToOutBuffer (&condition, sql);
4388
gaiaAppendToOutBuffer (&condition, " = ?");
4389
gaiaAppendToOutBuffer (&where, condition.Buffer);
4390
gaiaOutBufferReset (&condition);
4391
col->query_pos = param++;
4395
xname = gaiaDoubleQuotedSql (value_list->table);
4396
sql = sqlite3_mprintf ("\nFROM \"%s\"", xname);
4398
gaiaAppendToOutBuffer (&sql_statement, sql);
4400
gaiaAppendToOutBuffer (&sql_statement, where.Buffer);
4401
gaiaOutBufferReset (&where);
4403
if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
4405
sqlite3_prepare_v2 (sqlite, sql_statement.Buffer,
4406
strlen (sql_statement.Buffer), &stmt2, NULL);
4408
sqlite3_reset (stmt1);
4409
sqlite3_clear_bindings (stmt1);
4410
sqlite3_bind_int64 (stmt1, 1, rowid);
4411
ret = sqlite3_step (stmt1);
4412
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
4409
gaiaOutBufferReset (&sql_statement);
4410
if (ret != SQLITE_OK)
4412
4416
spatialite_e ("SQL error: %s\n", sqlite3_errmsg (sqlite));
4416
sqlite3_reset (stmt2);
4417
sqlite3_clear_bindings (stmt2);
4418
col = value_list->first;
4421
/* binding query params */
4422
if (col->type == SQLITE_INTEGER)
4423
sqlite3_bind_int64 (stmt2, col->query_pos, col->int_value);
4424
if (col->type == SQLITE_FLOAT)
4425
sqlite3_bind_double (stmt2, col->query_pos, col->dbl_value);
4426
if (col->type == SQLITE_TEXT)
4427
sqlite3_bind_text (stmt2, col->query_pos, col->txt_value,
4428
strlen (col->txt_value), SQLITE_STATIC);
4434
/* fetching the result set rows */
4435
ret = sqlite3_step (stmt2);
4436
if (ret == SQLITE_DONE)
4437
break; /* end of result set */
4438
if (ret == SQLITE_ROW)
4440
/* fetching a row */
4442
n_cols = sqlite3_column_count (stmt2);
4443
for (col_no = 1; col_no < n_cols; col_no++)
4445
/* checking blob columns */
4446
if (sqlite3_column_type (stmt2, col_no) == SQLITE_BLOB)
4449
sqlite3_column_blob (stmt2, col_no);
4451
sqlite3_column_bytes (stmt2, col_no);
4453
(value_list, col_no - 1, blob, blob_size) == 0)
4466
/* deleting any duplicated row except the first one */
4467
sqlite3_reset (stmt1);
4468
sqlite3_clear_bindings (stmt1);
4469
sqlite3_bind_int64 (stmt1, 1,
4470
sqlite3_column_int64 (stmt2, 0));
4471
ret = sqlite3_step (stmt1);
4472
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
4476
spatialite_e ("SQL error: %s\n",
4477
sqlite3_errmsg (sqlite));
4484
spatialite_e ("SQL error: %s\n", sqlite3_errmsg (sqlite));
4489
sqlite3_finalize (stmt2);
4495
sqlite3_finalize (stmt2);
4502
4423
do_delete_duplicates (sqlite3 * sqlite, const char *sql1, const char *sql2,
4503
struct dupl_row *value_list, int *count)
4424
int *count, int transaction)
4505
4426
/* deleting duplicate rows */
4427
struct resultset_comparator *rs_obj = NULL;
4506
4428
sqlite3_stmt *stmt1 = NULL;
4507
4429
sqlite3_stmt *stmt2 = NULL;
4513
4432
char *sql_err = NULL;
4517
/* the complete operation is handled as an unique SQL Transaction */
4518
ret = sqlite3_exec (sqlite, "BEGIN", NULL, NULL, &sql_err);
4519
if (ret != SQLITE_OK)
4521
spatialite_e ("BEGIN TRANSACTION error: %s\n", sql_err);
4522
sqlite3_free (sql_err);
4438
/* the complete operation is handled as an unique SQL Transaction */
4439
ret = sqlite3_exec (sqlite, "BEGIN", NULL, NULL, &sql_err);
4440
if (ret != SQLITE_OK)
4442
spatialite_e ("BEGIN TRANSACTION error: %s\n", sql_err);
4443
sqlite3_free (sql_err);
4525
4448
/* preparing the main SELECT statement */
4526
4449
ret = sqlite3_prepare_v2 (sqlite, sql1, strlen (sql1), &stmt1, NULL);
4527
4450
if (ret != SQLITE_OK)
4546
4470
if (ret == SQLITE_ROW)
4548
4472
/* fetching a row */
4549
sqlite3_reset (stmt2);
4550
sqlite3_clear_bindings (stmt2);
4551
n_cols = sqlite3_column_count (stmt1);
4552
for (col_no = 1; col_no < n_cols; col_no++)
4473
save_row_from_resultset (rs_obj, stmt1);
4474
if (resultset_rows_equals (rs_obj))
4554
/* saving column values */
4555
if (sqlite3_column_type (stmt1, col_no) == SQLITE_INTEGER)
4556
set_int_value (value_list, col_no - 1,
4557
sqlite3_column_int64 (stmt1, col_no));
4558
if (sqlite3_column_type (stmt1, col_no) == SQLITE_FLOAT)
4559
set_double_value (value_list, col_no - 1,
4560
sqlite3_column_double (stmt1,
4562
if (sqlite3_column_type (stmt1, col_no) == SQLITE_TEXT)
4565
(const char *) sqlite3_column_text (stmt1,
4567
set_text_value (value_list, col_no - 1, xtext);
4569
if (sqlite3_column_type (stmt1, col_no) == SQLITE_BLOB)
4572
sqlite3_column_blob (stmt1, col_no);
4574
sqlite3_column_bytes (stmt1, col_no);
4575
set_blob_value (value_list, col_no - 1, blob,
4578
if (sqlite3_column_type (stmt1, col_no) == SQLITE_NULL)
4579
set_null_value (value_list, col_no - 1);
4476
if (do_delete_duplicates2
4477
(sqlite, get_current_resultset_rowid (rs_obj), stmt2))
4480
reset_resultset_current_row (rs_obj);
4581
if (do_delete_duplicates2 (sqlite, stmt2, value_list, &xcnt))
4588
4489
spatialite_e ("SQL error: %s\n", sqlite3_errmsg (sqlite));
4492
swap_resultset_rows (rs_obj);
4593
4495
sqlite3_finalize (stmt1);
4594
4496
sqlite3_finalize (stmt2);
4497
destroy_resultset_comparator (rs_obj);
4596
/* confirm the still pending Transaction */
4597
ret = sqlite3_exec (sqlite, "COMMIT", NULL, NULL, &sql_err);
4598
if (ret != SQLITE_OK)
4600
spatialite_e ("COMMIT TRANSACTION error: %s\n", sql_err);
4601
sqlite3_free (sql_err);
4501
/* confirm the still pending Transaction */
4502
ret = sqlite3_exec (sqlite, "COMMIT", NULL, NULL, &sql_err);
4503
if (ret != SQLITE_OK)
4505
spatialite_e ("COMMIT TRANSACTION error: %s\n", sql_err);
4506
sqlite3_free (sql_err);
4693
4601
sqlite3_free_table (results);
4694
4602
/* preparing the SQL statement (identifying duplicated rows) */
4695
4603
gaiaOutBufferInitialize (&sql_statement);
4696
gaiaAppendToOutBuffer (&sql_statement,
4697
"SELECT Count(*) AS \"[dupl-count]\", ");
4604
gaiaAppendToOutBuffer (&sql_statement, "SELECT ROWID, ");
4698
4605
if (col_list.Error == 0 && col_list.Buffer != NULL)
4699
4606
gaiaAppendToOutBuffer (&sql_statement, col_list.Buffer);
4700
4607
xname = gaiaDoubleQuotedSql (table);
4701
sql = sqlite3_mprintf ("\nFROM \"%s\"\nGROUP BY ", xname);
4608
sql = sqlite3_mprintf ("\nFROM \"%s\"\nORDER BY ", xname);
4703
4610
gaiaAppendToOutBuffer (&sql_statement, sql);
4704
4611
sqlite3_free (sql);
4705
4612
if (col_list.Error == 0 && col_list.Buffer != NULL)
4706
4613
gaiaAppendToOutBuffer (&sql_statement, col_list.Buffer);
4707
4614
gaiaOutBufferReset (&col_list);
4708
gaiaAppendToOutBuffer (&sql_statement, "\nHAVING \"[dupl-count]\" > 1");
4615
gaiaAppendToOutBuffer (&sql_statement, ", ROWID");
4709
4616
/* preparing the SQL statement [delete] */
4710
4617
xname = gaiaDoubleQuotedSql (table);
4711
4618
sql2 = sqlite3_mprintf ("DELETE FROM \"%s\" WHERE ROWID = ?", xname);