/ Check-in [283385d2]
Login

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

Overview
Comment:http://www.sqlite.org/cvstrac/tktview?tn=2219

When creating fts tables in an attached database, the backing tables are created in database 'main'. This change propagates the appropriate database name to the routines which build sql statements.

Note that I propagate the database name and table name separately. I briefly considered just making the table name be "db.table", but it didn't fit so well in the model used to store the table name and other information, and having the db name passed separately seemed a bit more transparent. (CVS 3631)

Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 283385d20724f0144f38de89bd179715ee5e738b
User & Date: shess 2007-02-07 01:01:17
Context
2007-02-07
01:06
Change the coding of PRAGMA count_changes so that it uses memory cells of the VM rather than the stack, to avoid problems with leftovers on the stack interfering with other operations. Ticket #2217. (CVS 3632) check-in: 2bd4b62a user: drh tags: trunk
01:01
http://www.sqlite.org/cvstrac/tktview?tn=2219

When creating fts tables in an attached database, the backing tables are created in database 'main'. This change propagates the appropriate database name to the routines which build sql statements.

Note that I propagate the database name and table name separately. I briefly considered just making the table name be "db.table", but it didn't fit so well in the model used to store the table name and other information, and having the db name passed separately seemed a bit more transparent. (CVS 3631) check-in: 283385d2 user: shess tags: trunk

2007-02-06
23:41
Additional tests to give full coverage testing to ticket #2211. (CVS 3630) check-in: ecb1f2fd user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts1/fts1.c.

   843    843    * (We don't use strdup() since it's not part of the standard C library and
   844    844    * may not be available everywhere.) */
   845    845   static char *string_dup(const char *s){
   846    846     return string_dup_n(s, strlen(s));
   847    847   }
   848    848   
   849    849   /* Format a string, replacing each occurrence of the % character with
   850         - * zName.  This may be more convenient than sqlite_mprintf()
          850  + * zDb.zName.  This may be more convenient than sqlite_mprintf()
   851    851    * when one string is used repeatedly in a format string.
   852    852    * The caller must free() the returned string. */
   853         -static char *string_format(const char *zFormat, const char *zName){
          853  +static char *string_format(const char *zFormat,
          854  +                           const char *zDb, const char *zName){
   854    855     const char *p;
   855    856     size_t len = 0;
          857  +  size_t nDb = strlen(zDb);
   856    858     size_t nName = strlen(zName);
          859  +  size_t nFullTableName = nDb+1+nName;
   857    860     char *result;
   858    861     char *r;
   859    862   
   860    863     /* first compute length needed */
   861    864     for(p = zFormat ; *p ; ++p){
   862         -    len += (*p=='%' ? nName : 1);
          865  +    len += (*p=='%' ? nFullTableName : 1);
   863    866     }
   864    867     len += 1;  /* for null terminator */
   865    868   
   866    869     r = result = malloc(len);
   867    870     for(p = zFormat; *p; ++p){
   868    871       if( *p=='%' ){
          872  +      memcpy(r, zDb, nDb);
          873  +      r += nDb;
          874  +      *r++ = '.';
   869    875         memcpy(r, zName, nName);
   870    876         r += nName;
   871    877       } else {
   872    878         *r++ = *p;
   873    879       }
   874    880     }
   875    881     *r++ = '\0';
   876    882     assert( r == result + len );
   877    883     return result;
   878    884   }
   879    885   
   880         -static int sql_exec(sqlite3 *db, const char *zName, const char *zFormat){
   881         -  char *zCommand = string_format(zFormat, zName);
          886  +static int sql_exec(sqlite3 *db, const char *zDb, const char *zName,
          887  +                    const char *zFormat){
          888  +  char *zCommand = string_format(zFormat, zDb, zName);
   882    889     int rc;
   883    890     TRACE(("FTS1 sql: %s\n", zCommand));
   884    891     rc = sqlite3_exec(db, zCommand, NULL, 0, NULL);
   885    892     free(zCommand);
   886    893     return rc;
   887    894   }
   888    895   
   889         -static int sql_prepare(sqlite3 *db, const char *zName, sqlite3_stmt **ppStmt,
   890         -                const char *zFormat){
   891         -  char *zCommand = string_format(zFormat, zName);
          896  +static int sql_prepare(sqlite3 *db, const char *zDb, const char *zName,
          897  +                       sqlite3_stmt **ppStmt, const char *zFormat){
          898  +  char *zCommand = string_format(zFormat, zDb, zName);
   892    899     int rc;
   893    900     TRACE(("FTS1 prepare: %s\n", zCommand));
   894    901     rc = sqlite3_prepare(db, zCommand, -1, ppStmt, NULL);
   895    902     free(zCommand);
   896    903     return rc;
   897    904   }
   898    905   
................................................................................
  1036   1043   ** of this structure and xDestroy and xDisconnect free that instance.
  1037   1044   ** All other methods receive a pointer to the structure as one of their
  1038   1045   ** arguments.
  1039   1046   */
  1040   1047   struct fulltext_vtab {
  1041   1048     sqlite3_vtab base;               /* Base class used by SQLite core */
  1042   1049     sqlite3 *db;                     /* The database connection */
         1050  +  const char *zDb;                 /* logical database name */
  1043   1051     const char *zName;               /* virtual table name */
  1044   1052     int nColumn;                     /* number of columns in virtual table */
  1045   1053     char **azColumn;                 /* column names.  malloced */
  1046   1054     char **azContentColumn;          /* column names in content table; malloced */
  1047   1055     sqlite3_tokenizer *pTokenizer;   /* tokenizer for inserts and queries */
  1048   1056   
  1049   1057     /* Precompiled statements which we keep as long as the table is
................................................................................
  1135   1143         case CONTENT_INSERT_STMT:
  1136   1144           zStmt = contentInsertStatement(v); break;
  1137   1145         case CONTENT_UPDATE_STMT:
  1138   1146           zStmt = contentUpdateStatement(v); break;
  1139   1147         default:
  1140   1148           zStmt = fulltext_zStatement[iStmt];
  1141   1149       }
  1142         -    rc = sql_prepare(v->db, v->zName, &v->pFulltextStatements[iStmt],
         1150  +    rc = sql_prepare(v->db, v->zDb, v->zName, &v->pFulltextStatements[iStmt],
  1143   1151                            zStmt);
  1144   1152       if( zStmt != fulltext_zStatement[iStmt]) free((void *) zStmt);
  1145   1153       if( rc!=SQLITE_OK ) return rc;
  1146   1154     } else {
  1147   1155       int rc = sqlite3_reset(v->pFulltextStatements[iStmt]);
  1148   1156       if( rc!=SQLITE_OK ) return rc;
  1149   1157     }
................................................................................
  1738   1746   
  1739   1747   /*
  1740   1748   ** An instance of this structure defines the "spec" of a
  1741   1749   ** full text index.  This structure is populated by parseSpec
  1742   1750   ** and use by fulltextConnect and fulltextCreate.
  1743   1751   */
  1744   1752   typedef struct TableSpec {
         1753  +  const char *zDb;         /* Logical database name */
  1745   1754     const char *zName;       /* Name of the full-text index */
  1746   1755     int nColumn;             /* Number of columns to be indexed */
  1747   1756     char **azColumn;         /* Original names of columns to be indexed */
  1748   1757     char **azContentColumn;  /* Column names for %_content */
  1749   1758     char **azTokenizer;      /* Name of tokenizer and its arguments */
  1750   1759   } TableSpec;
  1751   1760   
................................................................................
  1800   1809       strcpy(z, argv[i]);
  1801   1810       z += strlen(z)+1;
  1802   1811     }
  1803   1812   
  1804   1813     /* Identify the column names and the tokenizer and delimiter arguments
  1805   1814     ** in the argv[][] array.
  1806   1815     */
         1816  +  pSpec->zDb = azArg[1];
  1807   1817     pSpec->zName = azArg[2];
  1808   1818     pSpec->nColumn = 0;
  1809   1819     pSpec->azColumn = azArg;
  1810   1820     zTokenizer = "tokenize simple";
  1811   1821     for(i=3; i<argc; ++i){
  1812   1822       if( startsWith(azArg[i],"tokenize") ){
  1813   1823         zTokenizer = azArg[i];
................................................................................
  1900   1910     char *schema;
  1901   1911   
  1902   1912     v = (fulltext_vtab *) malloc(sizeof(fulltext_vtab));
  1903   1913     if( v==0 ) return SQLITE_NOMEM;
  1904   1914     memset(v, 0, sizeof(*v));
  1905   1915     /* sqlite will initialize v->base */
  1906   1916     v->db = db;
         1917  +  v->zDb = spec->zDb;       /* Freed when azColumn is freed */
  1907   1918     v->zName = spec->zName;   /* Freed when azColumn is freed */
  1908   1919     v->nColumn = spec->nColumn;
  1909   1920     v->azContentColumn = spec->azContentColumn;
  1910   1921     spec->azContentColumn = 0;
  1911   1922     v->azColumn = spec->azColumn;
  1912   1923     spec->azColumn = 0;
  1913   1924   
................................................................................
  2016   2027     rc = parseSpec(&spec, argc, argv, pzErr);
  2017   2028     if( rc!=SQLITE_OK ) return rc;
  2018   2029   
  2019   2030     initStringBuffer(&schema);
  2020   2031     append(&schema, "CREATE TABLE %_content(");
  2021   2032     appendList(&schema, spec.nColumn, spec.azContentColumn);
  2022   2033     append(&schema, ")");
  2023         -  rc = sql_exec(db, spec.zName, schema.s);
         2034  +  rc = sql_exec(db, spec.zDb, spec.zName, schema.s);
  2024   2035     free(schema.s);
  2025   2036     if( rc!=SQLITE_OK ) goto out;
  2026   2037   
  2027         -  rc = sql_exec(db, spec.zName,
         2038  +  rc = sql_exec(db, spec.zDb, spec.zName,
  2028   2039       "create table %_term(term text, segment integer, doclist blob, "
  2029   2040                           "primary key(term, segment));");
  2030   2041     if( rc!=SQLITE_OK ) goto out;
  2031   2042   
  2032   2043     rc = constructVtab(db, &spec, ppVTab, pzErr);
  2033   2044   
  2034   2045   out:
................................................................................
  2078   2089   }
  2079   2090   
  2080   2091   static int fulltextDestroy(sqlite3_vtab *pVTab){
  2081   2092     fulltext_vtab *v = (fulltext_vtab *)pVTab;
  2082   2093     int rc;
  2083   2094   
  2084   2095     TRACE(("FTS1 Destroy %p\n", pVTab));
  2085         -  rc = sql_exec(v->db, v->zName,
         2096  +  rc = sql_exec(v->db, v->zDb, v->zName,
  2086   2097                   "drop table if exists %_content;"
  2087   2098                   "drop table if exists %_term;"
  2088   2099                   );
  2089   2100     if( rc!=SQLITE_OK ) return rc;
  2090   2101   
  2091   2102     fulltext_vtab_destroy((fulltext_vtab *)pVTab);
  2092   2103     return SQLITE_OK;
................................................................................
  2835   2846     char *zSql;
  2836   2847   
  2837   2848     TRACE(("FTS1 Filter %p\n",pCursor));
  2838   2849   
  2839   2850     zSql = sqlite3_mprintf("select rowid, * from %%_content %s",
  2840   2851                             idxNum==QUERY_GENERIC ? "" : "where rowid=?");
  2841   2852     sqlite3_finalize(c->pStmt);
  2842         -  rc = sql_prepare(v->db, v->zName, &c->pStmt, zSql);
         2853  +  rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql);
  2843   2854     sqlite3_free(zSql);
  2844   2855     if( rc!=SQLITE_OK ) return rc;
  2845   2856   
  2846   2857     c->iCursorType = idxNum;
  2847   2858     switch( idxNum ){
  2848   2859       case QUERY_GENERIC:
  2849   2860         break;

Changes to ext/fts2/fts2.c.

  1349   1349    * (We don't use strdup() since it's not part of the standard C library and
  1350   1350    * may not be available everywhere.) */
  1351   1351   static char *string_dup(const char *s){
  1352   1352     return string_dup_n(s, strlen(s));
  1353   1353   }
  1354   1354   
  1355   1355   /* Format a string, replacing each occurrence of the % character with
  1356         - * zName.  This may be more convenient than sqlite_mprintf()
         1356  + * zDb.zName.  This may be more convenient than sqlite_mprintf()
  1357   1357    * when one string is used repeatedly in a format string.
  1358   1358    * The caller must free() the returned string. */
  1359         -static char *string_format(const char *zFormat, const char *zName){
         1359  +static char *string_format(const char *zFormat,
         1360  +                           const char *zDb, const char *zName){
  1360   1361     const char *p;
  1361   1362     size_t len = 0;
         1363  +  size_t nDb = strlen(zDb);
  1362   1364     size_t nName = strlen(zName);
         1365  +  size_t nFullTableName = nDb+1+nName;
  1363   1366     char *result;
  1364   1367     char *r;
  1365   1368   
  1366   1369     /* first compute length needed */
  1367   1370     for(p = zFormat ; *p ; ++p){
  1368         -    len += (*p=='%' ? nName : 1);
         1371  +    len += (*p=='%' ? nFullTableName : 1);
  1369   1372     }
  1370   1373     len += 1;  /* for null terminator */
  1371   1374   
  1372   1375     r = result = malloc(len);
  1373   1376     for(p = zFormat; *p; ++p){
  1374   1377       if( *p=='%' ){
         1378  +      memcpy(r, zDb, nDb);
         1379  +      r += nDb;
         1380  +      *r++ = '.';
  1375   1381         memcpy(r, zName, nName);
  1376   1382         r += nName;
  1377   1383       } else {
  1378   1384         *r++ = *p;
  1379   1385       }
  1380   1386     }
  1381   1387     *r++ = '\0';
  1382   1388     assert( r == result + len );
  1383   1389     return result;
  1384   1390   }
  1385   1391   
  1386         -static int sql_exec(sqlite3 *db, const char *zName, const char *zFormat){
  1387         -  char *zCommand = string_format(zFormat, zName);
         1392  +static int sql_exec(sqlite3 *db, const char *zDb, const char *zName,
         1393  +                    const char *zFormat){
         1394  +  char *zCommand = string_format(zFormat, zDb, zName);
  1388   1395     int rc;
  1389   1396     TRACE(("FTS2 sql: %s\n", zCommand));
  1390   1397     rc = sqlite3_exec(db, zCommand, NULL, 0, NULL);
  1391   1398     free(zCommand);
  1392   1399     return rc;
  1393   1400   }
  1394   1401   
  1395         -static int sql_prepare(sqlite3 *db, const char *zName, sqlite3_stmt **ppStmt,
  1396         -                const char *zFormat){
  1397         -  char *zCommand = string_format(zFormat, zName);
         1402  +static int sql_prepare(sqlite3 *db, const char *zDb, const char *zName,
         1403  +                       sqlite3_stmt **ppStmt, const char *zFormat){
         1404  +  char *zCommand = string_format(zFormat, zDb, zName);
  1398   1405     int rc;
  1399   1406     TRACE(("FTS2 prepare: %s\n", zCommand));
  1400   1407     rc = sqlite3_prepare(db, zCommand, -1, ppStmt, NULL);
  1401   1408     free(zCommand);
  1402   1409     return rc;
  1403   1410   }
  1404   1411   
................................................................................
  1539   1546   ** of this structure and xDestroy and xDisconnect free that instance.
  1540   1547   ** All other methods receive a pointer to the structure as one of their
  1541   1548   ** arguments.
  1542   1549   */
  1543   1550   struct fulltext_vtab {
  1544   1551     sqlite3_vtab base;               /* Base class used by SQLite core */
  1545   1552     sqlite3 *db;                     /* The database connection */
         1553  +  const char *zDb;                 /* logical database name */
  1546   1554     const char *zName;               /* virtual table name */
  1547   1555     int nColumn;                     /* number of columns in virtual table */
  1548   1556     char **azColumn;                 /* column names.  malloced */
  1549   1557     char **azContentColumn;          /* column names in content table; malloced */
  1550   1558     sqlite3_tokenizer *pTokenizer;   /* tokenizer for inserts and queries */
  1551   1559   
  1552   1560     /* Precompiled statements which we keep as long as the table is
................................................................................
  1638   1646         case CONTENT_INSERT_STMT:
  1639   1647           zStmt = contentInsertStatement(v); break;
  1640   1648         case CONTENT_UPDATE_STMT:
  1641   1649           zStmt = contentUpdateStatement(v); break;
  1642   1650         default:
  1643   1651           zStmt = fulltext_zStatement[iStmt];
  1644   1652       }
  1645         -    rc = sql_prepare(v->db, v->zName, &v->pFulltextStatements[iStmt],
         1653  +    rc = sql_prepare(v->db, v->zDb, v->zName, &v->pFulltextStatements[iStmt],
  1646   1654                            zStmt);
  1647   1655       if( zStmt != fulltext_zStatement[iStmt]) free((void *) zStmt);
  1648   1656       if( rc!=SQLITE_OK ) return rc;
  1649   1657     } else {
  1650   1658       int rc = sqlite3_reset(v->pFulltextStatements[iStmt]);
  1651   1659       if( rc!=SQLITE_OK ) return rc;
  1652   1660     }
................................................................................
  1712   1720   /* TODO(shess) Write version for generic statements and then share
  1713   1721   ** that between the cached-statement functions.
  1714   1722   */
  1715   1723   static int sql_get_leaf_statement(fulltext_vtab *v, int idx,
  1716   1724                                     sqlite3_stmt **ppStmt){
  1717   1725     assert( idx>=0 && idx<MERGE_COUNT );
  1718   1726     if( v->pLeafSelectStmts[idx]==NULL ){
  1719         -    int rc = sql_prepare(v->db, v->zName, &v->pLeafSelectStmts[idx],
         1727  +    int rc = sql_prepare(v->db, v->zDb, v->zName, &v->pLeafSelectStmts[idx],
  1720   1728                            LEAF_SELECT);
  1721   1729       if( rc!=SQLITE_OK ) return rc;
  1722   1730     }else{
  1723   1731       int rc = sqlite3_reset(v->pLeafSelectStmts[idx]);
  1724   1732       if( rc!=SQLITE_OK ) return rc;
  1725   1733     }
  1726   1734   
................................................................................
  2330   2338   
  2331   2339   /*
  2332   2340   ** An instance of this structure defines the "spec" of a
  2333   2341   ** full text index.  This structure is populated by parseSpec
  2334   2342   ** and use by fulltextConnect and fulltextCreate.
  2335   2343   */
  2336   2344   typedef struct TableSpec {
         2345  +  const char *zDb;         /* Logical database name */
  2337   2346     const char *zName;       /* Name of the full-text index */
  2338   2347     int nColumn;             /* Number of columns to be indexed */
  2339   2348     char **azColumn;         /* Original names of columns to be indexed */
  2340   2349     char **azContentColumn;  /* Column names for %_content */
  2341   2350     char **azTokenizer;      /* Name of tokenizer and its arguments */
  2342   2351   } TableSpec;
  2343   2352   
................................................................................
  2392   2401       strcpy(z, argv[i]);
  2393   2402       z += strlen(z)+1;
  2394   2403     }
  2395   2404   
  2396   2405     /* Identify the column names and the tokenizer and delimiter arguments
  2397   2406     ** in the argv[][] array.
  2398   2407     */
         2408  +  pSpec->zDb = azArg[1];
  2399   2409     pSpec->zName = azArg[2];
  2400   2410     pSpec->nColumn = 0;
  2401   2411     pSpec->azColumn = azArg;
  2402   2412     zTokenizer = "tokenize simple";
  2403   2413     for(i=3; i<argc; ++i){
  2404   2414       if( startsWith(azArg[i],"tokenize") ){
  2405   2415         zTokenizer = azArg[i];
................................................................................
  2492   2502     char *schema;
  2493   2503   
  2494   2504     v = (fulltext_vtab *) malloc(sizeof(fulltext_vtab));
  2495   2505     if( v==0 ) return SQLITE_NOMEM;
  2496   2506     CLEAR(v);
  2497   2507     /* sqlite will initialize v->base */
  2498   2508     v->db = db;
         2509  +  v->zDb = spec->zDb;       /* Freed when azColumn is freed */
  2499   2510     v->zName = spec->zName;   /* Freed when azColumn is freed */
  2500   2511     v->nColumn = spec->nColumn;
  2501   2512     v->azContentColumn = spec->azContentColumn;
  2502   2513     spec->azContentColumn = 0;
  2503   2514     v->azColumn = spec->azColumn;
  2504   2515     spec->azColumn = 0;
  2505   2516   
................................................................................
  2579   2590     rc = parseSpec(&spec, argc, argv, pzErr);
  2580   2591     if( rc!=SQLITE_OK ) return rc;
  2581   2592   
  2582   2593     initStringBuffer(&schema);
  2583   2594     append(&schema, "CREATE TABLE %_content(");
  2584   2595     appendList(&schema, spec.nColumn, spec.azContentColumn);
  2585   2596     append(&schema, ")");
  2586         -  rc = sql_exec(db, spec.zName, stringBufferData(&schema));
         2597  +  rc = sql_exec(db, spec.zDb, spec.zName, stringBufferData(&schema));
  2587   2598     stringBufferDestroy(&schema);
  2588   2599     if( rc!=SQLITE_OK ) goto out;
  2589   2600   
  2590         -  rc = sql_exec(db, spec.zName, "create table %_segments(block blob);");
         2601  +  rc = sql_exec(db, spec.zDb, spec.zName,
         2602  +                "create table %_segments(block blob);");
  2591   2603     if( rc!=SQLITE_OK ) goto out;
  2592   2604   
  2593         -  rc = sql_exec(db, spec.zName,
         2605  +  rc = sql_exec(db, spec.zDb, spec.zName,
  2594   2606                   "create table %_segdir("
  2595   2607                   "  level integer,"
  2596   2608                   "  idx integer,"
  2597   2609                   "  start_block integer,"
  2598   2610                   "  leaves_end_block integer,"
  2599   2611                   "  end_block integer,"
  2600   2612                   "  root blob,"
................................................................................
  2651   2663   }
  2652   2664   
  2653   2665   static int fulltextDestroy(sqlite3_vtab *pVTab){
  2654   2666     fulltext_vtab *v = (fulltext_vtab *)pVTab;
  2655   2667     int rc;
  2656   2668   
  2657   2669     TRACE(("FTS2 Destroy %p\n", pVTab));
  2658         -  rc = sql_exec(v->db, v->zName,
         2670  +  rc = sql_exec(v->db, v->zDb, v->zName,
  2659   2671                   "drop table if exists %_content;"
  2660   2672                   "drop table if exists %_segments;"
  2661   2673                   "drop table if exists %_segdir;"
  2662   2674                   );
  2663   2675     if( rc!=SQLITE_OK ) return rc;
  2664   2676   
  2665   2677     fulltext_vtab_destroy((fulltext_vtab *)pVTab);
................................................................................
  3415   3427     char *zSql;
  3416   3428   
  3417   3429     TRACE(("FTS2 Filter %p\n",pCursor));
  3418   3430   
  3419   3431     zSql = sqlite3_mprintf("select rowid, * from %%_content %s",
  3420   3432                             idxNum==QUERY_GENERIC ? "" : "where rowid=?");
  3421   3433     sqlite3_finalize(c->pStmt);
  3422         -  rc = sql_prepare(v->db, v->zName, &c->pStmt, zSql);
         3434  +  rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql);
  3423   3435     sqlite3_free(zSql);
  3424   3436     if( rc!=SQLITE_OK ) return rc;
  3425   3437   
  3426   3438     c->iCursorType = idxNum;
  3427   3439     switch( idxNum ){
  3428   3440       case QUERY_GENERIC:
  3429   3441         break;

Added test/fts1j.test.

            1  +# 2007 February 6
            2  +#
            3  +# The author disclaims copyright to this source code.
            4  +#
            5  +#*************************************************************************
            6  +# This file implements regression tests for SQLite library.  This
            7  +# tests creating fts1 tables in an attached database.
            8  +#
            9  +# $Id: fts1j.test,v 1.1 2007/02/07 01:01:18 shess Exp $
           10  +#
           11  +
           12  +set testdir [file dirname $argv0]
           13  +source $testdir/tester.tcl
           14  +
           15  +# If SQLITE_ENABLE_FTS1 is defined, omit this file.
           16  +ifcapable !fts1 {
           17  +  finish_test
           18  +  return
           19  +}
           20  +
           21  +# Clean up anything left over from a previous pass.
           22  +file delete -force test2.db
           23  +file delete -force test2.db-journal
           24  +sqlite3 db2 test2.db
           25  +
           26  +db eval {
           27  +  CREATE VIRTUAL TABLE t3 USING fts1(content);
           28  +  INSERT INTO t3 (rowid, content) VALUES(1, "hello world");
           29  +}
           30  +
           31  +db2 eval {
           32  +  CREATE VIRTUAL TABLE t1 USING fts1(content);
           33  +  INSERT INTO t1 (rowid, content) VALUES(1, "hello world");
           34  +  INSERT INTO t1 (rowid, content) VALUES(2, "hello there");
           35  +  INSERT INTO t1 (rowid, content) VALUES(3, "cruel world");
           36  +}
           37  +
           38  +# This has always worked because the t1_* tables used by fts1 will be
           39  +# the defaults.
           40  +do_test fts1j-1.1 {
           41  +  execsql {
           42  +    ATTACH DATABASE 'test2.db' AS two;
           43  +    SELECT rowid FROM t1 WHERE t1 MATCH 'hello';
           44  +    DETACH DATABASE two;
           45  +  }
           46  +} {1 2}
           47  +# Make certain we're detached if there was an error.
           48  +catch {db eval {DETACH DATABASE two}}
           49  +
           50  +# In older code, this appears to work fine, but the t2_* tables used
           51  +# by fts1 will be created in database 'main' instead of database
           52  +# 'two'.  It appears to work fine because the tables end up being the
           53  +# defaults, but obviously is badly broken if you hope to use things
           54  +# other than in the exact same ATTACH setup.
           55  +do_test fts1j-1.2 {
           56  +  execsql {
           57  +    ATTACH DATABASE 'test2.db' AS two;
           58  +    CREATE VIRTUAL TABLE two.t2 USING fts1(content);
           59  +    INSERT INTO t2 (rowid, content) VALUES(1, "hello world");
           60  +    INSERT INTO t2 (rowid, content) VALUES(2, "hello there");
           61  +    INSERT INTO t2 (rowid, content) VALUES(3, "cruel world");
           62  +    SELECT rowid FROM t2 WHERE t2 MATCH 'hello';
           63  +    DETACH DATABASE two;
           64  +  }
           65  +} {1 2}
           66  +catch {db eval {DETACH DATABASE two}}
           67  +
           68  +# In older code, this broke because the fts1 code attempted to create
           69  +# t3_* tables in database 'main', but they already existed.  Normally
           70  +# this wouldn't happen without t3 itself existing, in which case the
           71  +# fts1 code would never be called in the first place.
           72  +do_test fts1j-1.3 {
           73  +  execsql {
           74  +    ATTACH DATABASE 'test2.db' AS two;
           75  +
           76  +    CREATE VIRTUAL TABLE two.t3 USING fts1(content);
           77  +    INSERT INTO two.t3 (rowid, content) VALUES(2, "hello there");
           78  +    INSERT INTO two.t3 (rowid, content) VALUES(3, "cruel world");
           79  +    SELECT rowid FROM two.t3 WHERE t3 MATCH 'hello';
           80  +
           81  +    DETACH DATABASE two;
           82  +  } db2
           83  +} {2}
           84  +catch {db eval {DETACH DATABASE two}}
           85  +
           86  +catch {db2 close}
           87  +file delete -force test2.db
           88  +
           89  +finish_test

Added test/fts2j.test.

            1  +# 2007 February 6
            2  +#
            3  +# The author disclaims copyright to this source code.
            4  +#
            5  +#*************************************************************************
            6  +# This file implements regression tests for SQLite library.  This
            7  +# tests creating fts2 tables in an attached database.
            8  +#
            9  +# $Id: fts2j.test,v 1.1 2007/02/07 01:01:18 shess Exp $
           10  +#
           11  +
           12  +set testdir [file dirname $argv0]
           13  +source $testdir/tester.tcl
           14  +
           15  +# If SQLITE_ENABLE_FTS2 is defined, omit this file.
           16  +ifcapable !fts2 {
           17  +  finish_test
           18  +  return
           19  +}
           20  +
           21  +# Clean up anything left over from a previous pass.
           22  +file delete -force test2.db
           23  +file delete -force test2.db-journal
           24  +sqlite3 db2 test2.db
           25  +
           26  +db eval {
           27  +  CREATE VIRTUAL TABLE t3 USING fts2(content);
           28  +  INSERT INTO t3 (rowid, content) VALUES(1, "hello world");
           29  +}
           30  +
           31  +db2 eval {
           32  +  CREATE VIRTUAL TABLE t1 USING fts2(content);
           33  +  INSERT INTO t1 (rowid, content) VALUES(1, "hello world");
           34  +  INSERT INTO t1 (rowid, content) VALUES(2, "hello there");
           35  +  INSERT INTO t1 (rowid, content) VALUES(3, "cruel world");
           36  +}
           37  +
           38  +# This has always worked because the t1_* tables used by fts2 will be
           39  +# the defaults.
           40  +do_test fts2j-1.1 {
           41  +  execsql {
           42  +    ATTACH DATABASE 'test2.db' AS two;
           43  +    SELECT rowid FROM t1 WHERE t1 MATCH 'hello';
           44  +    DETACH DATABASE two;
           45  +  }
           46  +} {1 2}
           47  +# Make certain we're detached if there was an error.
           48  +catch {db eval {DETACH DATABASE two}}
           49  +
           50  +# In older code, this appears to work fine, but the t2_* tables used
           51  +# by fts2 will be created in database 'main' instead of database
           52  +# 'two'.  It appears to work fine because the tables end up being the
           53  +# defaults, but obviously is badly broken if you hope to use things
           54  +# other than in the exact same ATTACH setup.
           55  +do_test fts2j-1.2 {
           56  +  execsql {
           57  +    ATTACH DATABASE 'test2.db' AS two;
           58  +    CREATE VIRTUAL TABLE two.t2 USING fts2(content);
           59  +    INSERT INTO t2 (rowid, content) VALUES(1, "hello world");
           60  +    INSERT INTO t2 (rowid, content) VALUES(2, "hello there");
           61  +    INSERT INTO t2 (rowid, content) VALUES(3, "cruel world");
           62  +    SELECT rowid FROM t2 WHERE t2 MATCH 'hello';
           63  +    DETACH DATABASE two;
           64  +  }
           65  +} {1 2}
           66  +catch {db eval {DETACH DATABASE two}}
           67  +
           68  +# In older code, this broke because the fts2 code attempted to create
           69  +# t3_* tables in database 'main', but they already existed.  Normally
           70  +# this wouldn't happen without t3 itself existing, in which case the
           71  +# fts2 code would never be called in the first place.
           72  +do_test fts2j-1.3 {
           73  +  execsql {
           74  +    ATTACH DATABASE 'test2.db' AS two;
           75  +
           76  +    CREATE VIRTUAL TABLE two.t3 USING fts2(content);
           77  +    INSERT INTO two.t3 (rowid, content) VALUES(2, "hello there");
           78  +    INSERT INTO two.t3 (rowid, content) VALUES(3, "cruel world");
           79  +    SELECT rowid FROM two.t3 WHERE t3 MATCH 'hello';
           80  +
           81  +    DETACH DATABASE two;
           82  +  } db2
           83  +} {2}
           84  +catch {db eval {DETACH DATABASE two}}
           85  +
           86  +catch {db2 close}
           87  +file delete -force test2.db
           88  +
           89  +finish_test