4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
14 #include "sqlite3intck.h"
19 struct sqlite3_intck
{
21 const char *zDb
; /* Copy of zDb parameter to _open() */
22 char *zObj
; /* Current object. Or NULL. */
23 char *zKey
; /* Key saved by _intck_suspend() call. */
24 sqlite3_stmt
*pCheck
; /* Current check statement */
25 int rc
; /* Error code */
26 char *zErr
; /* Error message */
27 char *zTestSql
; /* Returned by sqlite3_intck_test_sql() */
32 ** Some error has occurred while using database p->db. Save the error message
33 ** and error code currently held by the database handle in p->rc and p->zErr.
35 static void intckSaveErrmsg(sqlite3_intck
*p
){
36 const char *zDberr
= sqlite3_errmsg(p
->db
);
37 p
->rc
= sqlite3_errcode(p
->db
);
39 sqlite3_free(p
->zErr
);
40 p
->zErr
= sqlite3_mprintf("%s", zDberr
);
44 static sqlite3_stmt
*intckPrepare(sqlite3_intck
*p
, const char *zFmt
, ...){
45 sqlite3_stmt
*pRet
= 0;
49 zSql
= sqlite3_vmprintf(zFmt
, ap
);
50 if( p
->rc
==SQLITE_OK
){
54 p
->rc
= sqlite3_prepare_v2(p
->db
, zSql
, -1, &pRet
, 0);
56 if( p
->rc
!=SQLITE_OK
){
58 printf("ERROR: %s\n", zSql
);
59 printf("MSG: %s\n", sqlite3_errmsg(p
->db
));
61 if( sqlite3_error_offset(p
->db
)>=0 ){
63 int iOff
= sqlite3_error_offset(p
->db
);
64 printf("AT: %.40s\n", &zSql
[iOff
]);
78 static void intckFinalize(sqlite3_intck
*p
, sqlite3_stmt
*pStmt
){
79 int rc
= sqlite3_finalize(pStmt
);
80 if( p
->rc
==SQLITE_OK
&& rc
!=SQLITE_OK
){
85 static char *intckStrdup(sqlite3_intck
*p
, const char *zIn
){
87 if( p
->rc
==SQLITE_OK
){
88 int nIn
= strlen(zIn
);
89 zOut
= sqlite3_malloc(nIn
+1);
93 memcpy(zOut
, zIn
, nIn
+1);
99 static void intckFindObject(sqlite3_intck
*p
){
100 sqlite3_stmt
*pStmt
= 0;
101 char *zPrev
= p
->zObj
;
104 assert( p
->rc
==SQLITE_OK
);
105 pStmt
= intckPrepare(p
,
106 "WITH tables(table_name) AS ("
108 " FROM %Q.sqlite_schema WHERE (type='table' OR type='index') AND rootpage"
110 " SELECT 'sqlite_schema'"
112 "SELECT table_name FROM tables "
113 "WHERE ?1 IS NULL OR table_name%s?1 "
115 , p
->zDb
, (p
->zKey
? ">=" : ">")
118 if( p
->rc
==SQLITE_OK
){
119 sqlite3_bind_text(pStmt
, 1, zPrev
, -1, SQLITE_TRANSIENT
);
120 if( sqlite3_step(pStmt
)==SQLITE_ROW
){
121 p
->zObj
= intckStrdup(p
, (const char*)sqlite3_column_text(pStmt
, 0));
124 intckFinalize(p
, pStmt
);
126 /* If this is a new object, ensure the previous key value is cleared. */
127 if( sqlite3_stricmp(p
->zObj
, zPrev
) ){
128 sqlite3_free(p
->zKey
);
136 ** Return the size in bytes of the first token in nul-terminated buffer z.
137 ** For the purposes of this call, a token is either:
139 ** * a quoted SQL string,
140 * * a contiguous series of ascii alphabet characters, or
141 * * any other single byte.
143 static int intckGetToken(const char *z
){
146 if( c
=='\'' || c
=='"' || c
=='`' ){
150 if( z
[iRet
+1]!=c
) break;
156 while( z
[iRet
++]!=']' && z
[iRet
] );
158 else if( (c
>='A' && c
<='Z') || (c
>='a' && c
<='z') ){
159 while( (z
[iRet
]>='A' && z
[iRet
]<='Z') || (z
[iRet
]>='a' && z
[iRet
]<='z') ){
167 static int intckIsSpace(char c
){
168 return (c
==' ' || c
=='\t' || c
=='\n' || c
=='\r');
171 static int intckTokenMatch(
178 (strlen(z1
)==nToken
&& 0==sqlite3_strnicmp(zToken
, z1
, nToken
))
179 || (z2
&& strlen(z2
)==nToken
&& 0==sqlite3_strnicmp(zToken
, z2
, nToken
))
184 ** Argument z points to the text of a CREATE INDEX statement. This function
185 ** identifies the part of the text that contains either the index WHERE
186 ** clause (if iCol<0) or the iCol'th column of the index.
188 ** If (iCol<0), the identified fragment does not include the "WHERE" keyword,
189 ** only the expression that follows it. If (iCol>=0) then the identified
190 ** fragment does not include any trailing sort-order keywords - "ASC" or
193 ** If the CREATE INDEX statement does not contain the requested field or
194 ** clause, NULL is returned and (*pnByte) is set to 0. Otherwise, a pointer to
195 ** the identified fragment is returned and output parameter (*pnByte) set
196 ** to its size in bytes.
198 static const char *intckParseCreateIndex(const char *z
, int iCol
, int *pnByte
){
204 const char *zRet
= 0;
209 /* Skip forward until the first "(" token */
210 while( z
[iOff
]!='(' ){
211 iOff
+= intckGetToken(&z
[iOff
]);
212 if( z
[iOff
]=='\0' ) return 0;
214 assert( z
[iOff
]=='(' );
220 const char *zToken
= &z
[iOff
];
223 /* Check if this is the end of the current column - either a "," or ")"
226 if( z
[iOff
]==',' || z
[iOff
]==')' ){
227 if( iCol
==iThisCol
){
228 int iEnd
= iEndOfCol
? iEndOfCol
: iOff
;
229 nRet
= (iEnd
- iStart
);
234 while( intckIsSpace(z
[iStart
]) ) iStart
++;
237 if( z
[iOff
]==')' ) break;
239 if( z
[iOff
]=='(' ) nOpen
++;
240 if( z
[iOff
]==')' ) nOpen
--;
241 nToken
= intckGetToken(zToken
);
243 if( intckTokenMatch(zToken
, nToken
, "ASC", "DESC") ){
245 }else if( 0==intckIsSpace(zToken
[0]) ){
252 /* iStart is now the byte offset of 1 byte passed the final ')' in the
253 ** CREATE INDEX statement. Try to find a WHERE clause to return. */
254 while( zRet
==0 && z
[iOff
] ){
255 int n
= intckGetToken(&z
[iOff
]);
256 if( n
==5 && 0==sqlite3_strnicmp(&z
[iOff
], "where", 5) ){
263 /* Trim any whitespace from the start and end of the returned string. */
265 while( intckIsSpace(zRet
[0]) ){
269 while( nRet
>0 && intckIsSpace(zRet
[nRet
-1]) ) nRet
--;
276 static void parseCreateIndexFunc(
277 sqlite3_context
*pCtx
,
279 sqlite3_value
**apVal
281 const char *zSql
= (const char*)sqlite3_value_text(apVal
[0]);
282 int idx
= sqlite3_value_int(apVal
[1]);
283 const char *zRes
= 0;
288 zRes
= intckParseCreateIndex(zSql
, idx
, &nRes
);
290 sqlite3_result_text(pCtx
, zRes
, nRes
, SQLITE_TRANSIENT
);
294 ** Return true if sqlite3_intck.db has automatic indexes enabled, false
297 static int intckGetAutoIndex(sqlite3_intck
*p
){
299 sqlite3_stmt
*pStmt
= 0;
300 pStmt
= intckPrepare(p
, "PRAGMA automatic_index");
301 if( p
->rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pStmt
) ){
302 bRet
= sqlite3_column_int(pStmt
, 0);
304 intckFinalize(p
, pStmt
);
309 ** Return true if zObj is an index, or false otherwise.
311 static int intckIsIndex(sqlite3_intck
*p
, const char *zObj
){
313 sqlite3_stmt
*pStmt
= 0;
314 pStmt
= intckPrepare(p
,
315 "SELECT 1 FROM %Q.sqlite_schema WHERE name=%Q AND type='index'",
318 if( p
->rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pStmt
) ){
321 intckFinalize(p
, pStmt
);
325 static void intckExec(sqlite3_intck
*p
, const char *zSql
){
326 sqlite3_stmt
*pStmt
= 0;
327 pStmt
= intckPrepare(p
, "%s", zSql
);
328 while( p
->rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pStmt
) );
329 intckFinalize(p
, pStmt
);
332 static char *intckCheckObjectSql(
338 sqlite3_stmt
*pStmt
= 0;
342 const char *zCommon
=
343 /* Relation without_rowid also contains just one row. Column "b" is
344 ** set to true if the table being examined is a WITHOUT ROWID table,
345 ** or false otherwise. */
346 ", without_rowid(b) AS ("
348 " SELECT 1 FROM tabname, pragma_index_list(tab, db) AS l"
349 " WHERE origin='pk' "
350 " AND NOT EXISTS (SELECT 1 FROM sqlite_schema WHERE name=l.name)"
354 /* Table idx_cols contains 1 row for each column in each index on the
355 ** table being checked. Columns are:
357 ** idx_name: Name of the index.
358 ** idx_ispk: True if this index is the PK of a WITHOUT ROWID table.
359 ** col_name: Name of indexed column, or NULL for index on expression.
360 ** col_expr: Indexed expression, including COLLATE clause.
361 ** col_alias: Alias used for column in 'intck_wrapper' table.
363 ", idx_cols(idx_name, idx_ispk, col_name, col_expr, col_alias) AS ("
364 " SELECT l.name, (l.origin=='pk' AND w.b), i.name, COALESCE(("
365 " SELECT parse_create_index(sql, i.seqno) FROM "
366 " sqlite_schema WHERE name = l.name"
367 " ), format('\"%w\"', i.name) || ' COLLATE ' || quote(i.coll)),"
368 " 'c' || row_number() OVER ()"
372 " pragma_index_list(t.tab, t.db) l,"
373 " pragma_index_xinfo(l.name) i"
376 " SELECT '', 1, '_rowid_', '_rowid_', 'r1' FROM without_rowid WHERE b=0"
380 ", tabpk(db, tab, idx, o_pk, i_pk, q_pk, eq_pk, ps_pk, pk_pk) AS ("
381 " WITH pkfields(f, a) AS ("
382 " SELECT i.col_name, i.col_alias FROM idx_cols i WHERE i.idx_ispk"
384 " SELECT t.db, t.tab, t.idx, "
385 " group_concat('o.'||a, ', '), "
386 " group_concat('i.'||quote(f), ', '), "
387 " group_concat('quote(o.'||a||')', ' || '','' || '), "
388 " format('(%s)==(%s)',"
389 " group_concat('o.'||a, ', '), "
390 " group_concat(format('\"%w\"', f), ', ')"
392 " group_concat('%s', ','),"
393 " group_concat('quote('||a||')', ', ') "
394 " FROM tabname t, pkfields"
397 ", idx(name, match_expr, partial, partial_alias, idx_ps, idx_idx) AS ("
399 " format('(%s,%s) IS (%s,%s)', "
400 " group_concat(i.col_expr, ', '), i_pk,"
401 " group_concat('o.'||i.col_alias, ', '), o_pk"
403 " parse_create_index("
404 " (SELECT sql FROM sqlite_schema WHERE name=idx_name), -1"
406 " 'cond' || row_number() OVER ()"
407 " , group_concat('%s', ',')"
408 " , group_concat('quote('||i.col_alias||')', ', ')"
412 " WHERE i.idx_ispk==0 "
416 ", wrapper_with(s) AS ("
417 " SELECT 'intck_wrapper AS (\n SELECT\n ' || ("
419 " SELECT col_expr, col_alias FROM idx_cols"
421 " SELECT partial, partial_alias FROM idx WHERE partial IS NOT NULL"
423 " SELECT group_concat(format('%s AS %s', a, b), ',\n ') FROM f"
425 " || format('\n FROM %Q.%Q ', t.db, t.tab)"
426 /* If the object being checked is a table, append "NOT INDEXED".
427 ** Otherwise, append "INDEXED BY <index>", and then, if the index
428 ** is a partial index " WHERE <condition>". */
429 " || CASE WHEN t.idx IS NULL THEN "
432 " format('INDEXED BY %Q%s', t.idx, ' WHERE '||i.partial)"
435 " FROM tabname t LEFT JOIN idx i ON (i.name=t.idx)"
440 bAutoIndex
= intckGetAutoIndex(p
);
441 if( bAutoIndex
) intckExec(p
, "PRAGMA automatic_index = 0");
443 bIsIndex
= intckIsIndex(p
, zObj
);
445 pStmt
= intckPrepare(p
,
446 /* Table idxname contains a single row. The first column, "db", contains
447 ** the name of the db containing the table (e.g. "main") and the second,
448 ** "tab", the name of the table itself. */
449 "WITH tabname(db, tab, idx, prev) AS ("
450 " SELECT %Q, (SELECT tbl_name FROM %Q.sqlite_schema WHERE name=%Q), "
455 ", case_statement(c) AS ("
457 " 'CASE WHEN (' || group_concat(col_alias, ', ') || ') IS (\n ' "
458 " || 'SELECT ' || group_concat(col_expr, ', ') || ' FROM '"
459 " || format('%%Q.%%Q NOT INDEXED WHERE %%s\n', t.db, t.tab, p.eq_pk)"
460 " || ' )\n THEN NULL\n '"
461 " || 'ELSE format(''surplus entry ('"
462 " || group_concat('%%s', ',') || ',' || p.ps_pk"
463 " || ') in index ' || t.idx || ''', ' "
464 " || group_concat('quote('||i.col_alias||')', ', ') || ', ' || p.pk_pk"
466 " || '\nEND AS error_message'"
467 " FROM tabname t, tabpk p, idx_cols i WHERE i.idx_name=t.idx"
471 " SELECT format('format(''(%%s,%%s)'', %%s, %%s) AS thiskey', "
472 " group_concat('%%s', ','), p.ps_pk, "
473 " group_concat('quote('||i.col_alias||')',', '), p.pk_pk"
474 " ) FROM tabpk p, idx_cols i WHERE i.idx_name=p.idx"
477 ", whereclause(w_c) AS ("
478 " SELECT CASE WHEN prev!='' THEN "
479 " '\nWHERE (' || group_concat(i.col_alias, ',') || ',' "
480 " || o_pk || ') > ' || prev"
483 " FROM tabpk, tabname, idx_cols i WHERE i.idx_name=tabpk.idx"
486 ", main_select(m) AS ("
488 " 'WITH %%s\nSELECT %%s,\n%%s\nFROM intck_wrapper AS o%%s',"
489 " ww.s, c, t.k, whereclause.w_c"
491 " FROM case_statement, wrapper_with ww, thiskey t, whereclause"
494 "SELECT m FROM main_select"
495 , p
->zDb
, p
->zDb
, zObj
, zObj
499 pStmt
= intckPrepare(p
,
500 /* Table tabname contains a single row. The first column, "db", contains
501 ** the name of the db containing the table (e.g. "main") and the second,
502 ** "tab", the name of the table itself. */
503 "WITH tabname(db, tab, idx, prev) AS (SELECT %Q, %Q, NULL, %Q)"
507 /* expr(e) contains one row for each index on table zObj. Value e
508 ** is set to an expression that evaluates to NULL if the required
509 ** entry is present in the index, or an error message otherwise. */
511 " SELECT format('CASE WHEN EXISTS \n"
512 " (SELECT 1 FROM %%Q.%%Q AS i INDEXED BY %%Q WHERE %%s%%s)\n"
514 " ELSE format(''entry (%%s,%%s) missing from index %%s'', %%s, %%s)\n"
516 " , t.db, t.tab, i.name, i.match_expr, ' AND (' || partial || ')',"
517 " i.idx_ps, t.ps_pk, i.name, i.idx_idx, t.pk_pk),"
518 " CASE WHEN partial IS NULL THEN NULL ELSE i.partial_alias END"
519 " FROM tabpk t, idx i"
522 ", numbered(ii, cond, e) AS ("
523 " SELECT 0, 'n.ii=0', 'NULL'"
525 " SELECT row_number() OVER (),"
526 " '(n.ii='||row_number() OVER ()||COALESCE(' AND '||p||')', ')'), e"
530 ", counter_with(w) AS ("
531 " SELECT 'WITH intck_counter(ii) AS (\n ' || "
532 " group_concat('SELECT '||ii, ' UNION ALL\n ') "
533 " || '\n)' FROM numbered"
536 ", case_statement(c) AS ("
537 " SELECT 'CASE ' || "
538 " group_concat(format('\n WHEN %%s THEN (%%s)', cond, e), '') ||"
539 " '\nEND AS error_message'"
544 /* This table contains a single row consisting of a single value -
545 ** the text of an SQL expression that may be used by the main SQL
546 ** statement to output an SQL literal that can be used to resume
547 ** the scan if it is suspended. e.g. for a rowid table, an expression
550 ** format('(%d,%d)', _rowid_, n.ii)
553 " SELECT 'format(''(' || ps_pk || ',%%d)'', ' || pk_pk || ', n.ii)'"
557 ", whereclause(w_c) AS ("
558 " SELECT CASE WHEN prev!='' THEN "
559 " '\nWHERE (' || o_pk ||', n.ii) > ' || prev"
562 " FROM tabpk, tabname"
565 ", main_select(m) AS ("
567 " '%%s, %%s\nSELECT %%s,\n%%s AS thiskey\nFROM intck_wrapper AS o"
568 ", intck_counter AS n%%s\nORDER BY %%s', "
569 " w, ww.s, c, thiskey.k, whereclause.w_c, t.o_pk"
571 " FROM case_statement, tabpk t, counter_with, "
572 " wrapper_with ww, thiskey, whereclause"
575 "SELECT m FROM main_select",
576 p
->zDb
, zObj
, zPrev
, zCommon
580 while( p
->rc
==SQLITE_OK
&& SQLITE_ROW
==sqlite3_step(pStmt
) ){
582 int nField
= sqlite3_column_count(pStmt
);
584 for(ii
=0; ii
<nField
; ii
++){
585 const char *zName
= sqlite3_column_name(pStmt
, ii
);
586 const char *zVal
= (const char*)sqlite3_column_text(pStmt
, ii
);
587 printf("FIELD %s = %s\n", zName
, zVal
? zVal
: "(null)");
592 zRet
= intckStrdup(p
, (const char*)sqlite3_column_text(pStmt
, 0));
595 intckFinalize(p
, pStmt
);
597 if( bAutoIndex
) intckExec(p
, "PRAGMA automatic_index = 1");
601 static void intckCheckObject(sqlite3_intck
*p
){
602 char *zSql
= intckCheckObjectSql(p
, p
->zObj
, p
->zKey
);
603 p
->pCheck
= intckPrepare(p
, "%s", zSql
);
605 sqlite3_free(p
->zKey
);
609 int sqlite3_intck_open(
610 sqlite3
*db
, /* Database handle to operate on */
611 const char *zDbArg
, /* "main", "temp" etc. */
612 const char *zFile
, /* Path to save-state db on disk (or NULL) */
613 sqlite3_intck
**ppOut
/* OUT: New integrity-check handle */
615 sqlite3_intck
*pNew
= 0;
617 const char *zDb
= zDbArg
? zDbArg
: "main";
618 int nDb
= strlen(zDb
);
620 pNew
= (sqlite3_intck
*)sqlite3_malloc(sizeof(*pNew
) + nDb
+ 1);
624 sqlite3_create_function(db
, "parse_create_index",
625 2, SQLITE_UTF8
, 0, parseCreateIndexFunc
, 0, 0
627 memset(pNew
, 0, sizeof(*pNew
));
629 pNew
->zDb
= (const char*)&pNew
[1];
630 memcpy(&pNew
[1], zDb
, nDb
+1);
637 int sqlite3_intck_close(sqlite3_intck
*p
){
640 rc
= (p
->rc
==SQLITE_DONE
? SQLITE_OK
: p
->rc
);
642 sqlite3_create_function(
643 p
->db
, "parse_create_index", 1, SQLITE_UTF8
, 0, 0, 0, 0
646 sqlite3_free(p
->zObj
);
647 sqlite3_free(p
->zKey
);
648 sqlite3_free(p
->zTestSql
);
649 sqlite3_free(p
->zErr
);
655 int sqlite3_intck_step(sqlite3_intck
*p
){
656 if( p
->rc
==SQLITE_OK
){
659 if( p
->rc
==SQLITE_OK
){
668 if( p
->rc
==SQLITE_OK
){
670 if( sqlite3_step(p
->pCheck
)==SQLITE_ROW
){
671 /* Fine, whatever... */
673 if( sqlite3_finalize(p
->pCheck
)!=SQLITE_OK
){
684 const char *sqlite3_intck_message(sqlite3_intck
*p
){
686 return (const char*)sqlite3_column_text(p
->pCheck
, 0);
691 int sqlite3_intck_error(sqlite3_intck
*p
, const char **pzErr
){
693 return (p
->rc
==SQLITE_DONE
? SQLITE_OK
: p
->rc
);
696 int sqlite3_intck_suspend(sqlite3_intck
*p
){
697 if( p
->pCheck
&& p
->rc
==SQLITE_OK
){
698 assert( p
->zKey
==0 );
699 p
->zKey
= intckStrdup(p
, (const char*)sqlite3_column_text(p
->pCheck
, 1));
700 intckFinalize(p
, p
->pCheck
);
706 const char *sqlite3_intck_test_sql(sqlite3_intck
*p
, const char *zObj
){
707 sqlite3_free(p
->zTestSql
);
709 p
->zTestSql
= intckCheckObjectSql(p
, zObj
, 0);
712 p
->zTestSql
= intckCheckObjectSql(p
, p
->zObj
, p
->zKey
);
714 sqlite3_free(p
->zTestSql
);