sqllogictest

Check-in [816758c2ee]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the about.wiki page to talk about the new skipif and onlyif modifiers and how to use them to work around database incompatibilities.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 816758c2ee479115e7272283451bf730183f9692
User & Date: drh 2008-12-06 02:37:26.000
Context
2008-12-08
00:54
Send summary line to stderr. check-in: 7a52c0f372 user: shaneh tags: trunk
2008-12-06
02:37
Update the about.wiki page to talk about the new skipif and onlyif modifiers and how to use them to work around database incompatibilities. check-in: 816758c2ee user: drh tags: trunk
02:02
Add the "onlyif" condition prefix. Implement hash checking of skipped by labeled queries in verify mode. check-in: 4e38bbb44f user: drh tags: trunk
Changes
Unified Diff Ignore Whitespace Patch
Changes to about.wiki.
216
217
218
219
220
221
222































































223
224
225
226
227
228
229
of recording each individual value in the full test script, an MD5 hash of
all values is computed in stored.  This makes the full test scripts much
shorter, but at the cost of obscuring the results.  If the hash-threshold
is 0, then results are never hashed.  A hash-threshold of 10 or 20 is
recommended.  During debugging, it is advantage to set the hash-threshold
to zero so that all results can be seen.
































































<h2>Suggestions For Generating Test-Scripts</h2>

When sqllogictest runs a test script, it begins with a completely
empty database.  So the first few records of any test script will
typically be CREATE statements of various kinds and expecially
CREATE TABLE statements.  In order to maximize the portability of
scripts across database engines, it is suggested that test scripts stick 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
of recording each individual value in the full test script, an MD5 hash of
all values is computed in stored.  This makes the full test scripts much
shorter, but at the cost of obscuring the results.  If the hash-threshold
is 0, then results are never hashed.  A hash-threshold of 10 or 20 is
recommended.  During debugging, it is advantage to set the hash-threshold
to zero so that all results can be seen.

<h3>Conditional Records</h3>

Statement and query records can be prefixed with zero or more conditionals
of the following form:

<blockquote>
<tt>skipif</tt>  &lt;database-name&gt;<br>
<tt>onlyif</tt>  &lt;database-name&gt;
</blockquote>

The statement or query is not evaluated if a skipif record for the
target database engine is seen in the prefix.  The statement or query
is also skipped if an onlyif record for a different database engine
is seen.

When a skipif or onlyif causes a statement or query to be skipped,
it is still transferred unchanged to standard output in completion
mode.  In validation mode, if the record is a query with &lt;label&gt;
argument, then the result found in the input script is still checked
against the results of other queries with the same label, even though
the SQL is not run.

The skipif and onlyif prefixes can be used to implement test cases where
the SQL syntax varies from one database engine to another.  For example,
PostgreSQL has the syntax quirk that the AS keyword is required prior to
alias names in the Select-list items section of a query.  On all other
database engines, and in the SQL standard, the AS keyword is optional.
The way to work around this is as follows:

<verbatim>
     query III rowsort label-xyzzy
     SELECT a AS x, b AS y, c AS z FROM t1

     skipif postgresql
     query III rowsort label-xyzzy
     SELECT a x, b y, c z FROM t1
</verbatim>

Thus, there are two queries that are identical in every way except that
the second omits the AS keywords.  The second query is omitted from
PostgreSQL runs.  We use the label-xyzzy on both queries
in order to verify that they yield identical results.

Another example is MySQL in which the "/" operator is always floating-point
division even when both operands are integers.  To get integer division in
MySQL you have to use the "DIV" operator.  To test this behavior, one
could do something like the following:

<verbatim>
     skipif mysql
     query I rowsort label-plover
     SELECT a/b FROM t1

     onlyif mysql
     query I rowsort label-plover
     SELECT a DIV b FROM t1
</verbatim>

Once completion runs are made on all database engines, the query label
will operate on subsequent validation runs to
ensure that the same results are obtained from all database engines, 
even though the query syntax is slightly different.

<h2>Suggestions For Generating Test-Scripts</h2>

When sqllogictest runs a test script, it begins with a completely
empty database.  So the first few records of any test script will
typically be CREATE statements of various kinds and expecially
CREATE TABLE statements.  In order to maximize the portability of
scripts across database engines, it is suggested that test scripts stick