我怎样才能建立正常的表和FTS3表之间的触发器？ [英] How can I create a trigger between normal table and fts3 table?

问题描述

我一直有问题FTS3表和普通的数据库表之间切换。我的应用程序非常简单，并允许用户将联系人添加到数据库，他们可以搜索匹配的搜索查询的联系人（为什么我使用FTS表），结果然后用onItemclicklistener列表中显示。但是，当我点击的项目，我得到一个错误。我已经追查此错误回数据库（如果我用它工作正常的数据库，但如果我使用FTS事实并非如此）。所以，我决定使用这两种类型的数据库，并想知道如果有人可以告诉我怎么触发创建同步的数据库。

我的第一个数据库是联系方式（数据库名）和表称为联系。第二个数据库表是CONTACTS_FTS。所有我正在寻找的是COL_NAME，所以是所有我需要在我的CONTACTS_FTS表？我想知道，如果有人可以检查，看看我的触发器是有效的？

 公共静态最后弦乐DATABASE_NAME =联系;
公共静态最后弦乐DATABASE_TABLE =联系;
私有静态最后弦乐DATABASE_TABLE_FTS =CONTACTS_FTS;
私有静态最终诠释DATABASE_VERSION = 20;
私人语境ourContext;
私人DbHelper DBHelper;
私有静态SQLiteDatabase分贝;
私有静态最后弦乐DATABASE_CREATE =
    CREATE TABLE+ DATABASE_TABLE +（+
     COL_ID +INTEGER PRIMARY KEY AUTOINCREMENT，+
     COL_NAME +TEXT NOT NULL，+
     COL_EMAIL +TEXT NOT NULL，+
     COL_CELL +TEXT NOT NULL，+
     COL_ARRIVAL +TEXT NOT NULL，+
     COL_DEPARTURE +TEXT NOT NULL，+
     COL_FLIGHT_NUMBER +TEXT NOT NULL，+
     COL_HOTEL_ROOM_NUMBER +TEXT NOT NULL，+
     COL_EVENT1 +TEXT NOT NULL，+
     COL_EVENT2 +TEXT NOT NULL，+
     COL_EVENT1_ROOM +TEXT NOT NULL，+
     COL_EVENT2_ROOM +TEXT NOT NULL）;;

私有静态最后弦乐DATABASE_CREATE_FTS =
    创建虚拟TABLE+ DATABASE_TABLE_FTS +使用FTS3（+
    CONTENT =+\联系\，+
    COL_NAME +）;;

私有静态最后弦乐触发=
    CREATE TRIGGER contacts_Trigger+
    之​​前加上+ON+ DATABASE_TABLE +
    BEGIN+
    插入+ DATABASE_TABLE_FTS +SET+ COL_NAME += new.COL_NAME WHERE+ COL_ID += old.COL_ID; +
     结束;;
 

解决方案

我觉得选项外部内容表仅适用于FTS4而不是FTS3。

外部内容FTS4表

尝试使用，而不是FTS3 FTS4表。

I have been having issues switching between FTS3 tables and normal database tables. My application is very simple and allows the user to add contacts to a database and they can search for contacts that match the search query (why i used a fts table) and the result is then displayed on a list with an onItemclicklistener. But when I click the item, i get an error. I have traced this error back to the database (if I use normal database it works, but if I use fts it doesn't). So I have decided to use both types of databases and was wondering if someone could show me how a trigger is created to sync databases.

My first database is CONTACT (database name) and the table is called CONTACTS. The second database table is CONTACTS_FTS. All I am searching for is the COL_NAME, so is that all I need in my CONTACTS_FTS table? I was wondering if someone could check to see if my trigger is valid?

public static final String DATABASE_NAME = "CONTACT";
public static final String DATABASE_TABLE = "CONTACTS";
private static final String DATABASE_TABLE_FTS = "CONTACTS_FTS";
private static final int DATABASE_VERSION = 20;
private Context ourContext;
private DbHelper DBHelper;
private static SQLiteDatabase db;
private static final String DATABASE_CREATE = 
    "CREATE TABLE " + DATABASE_TABLE + " (" +
     COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
     COL_NAME + " TEXT NOT NULL, " + 
     COL_EMAIL + " TEXT NOT NULL, " +
     COL_CELL + " TEXT NOT NULL, " + 
     COL_ARRIVAL + " TEXT NOT NULL, " +
     COL_DEPARTURE + " TEXT NOT NULL, " + 
     COL_FLIGHT_NUMBER + " TEXT NOT NULL, " +
     COL_HOTEL_ROOM_NUMBER + " TEXT NOT NULL, " + 
     COL_EVENT1 + " TEXT NOT NULL, " +
     COL_EVENT2 + " TEXT NOT NULL, " + 
     COL_EVENT1_ROOM + " TEXT NOT NULL, " +
     COL_EVENT2_ROOM + " TEXT NOT NULL);";

private static final String DATABASE_CREATE_FTS = 
    "CREATE VIRTUAL TABLE " + DATABASE_TABLE_FTS + " USING fts3(" +
    "content=" + "\"CONTACTS\", " + 
    COL_NAME + ");";

private static final String Trigger = 
    "CREATE TRIGGER contacts_Trigger " + 
    "AFTER INSERT "+ "ON " + DATABASE_TABLE +
    " BEGIN " +
    "INSERT " + DATABASE_TABLE_FTS + " SET " + COL_NAME + " = new.COL_NAME WHERE " + COL_ID + " = old.COL_ID;" +
    " END;";

解决方案

I think option for "external content" table is only available for FTS4 and not for FTS3.

External Content FTS4 Tables

Try using FTS4 table instead of FTS3.

这篇关于我怎样才能建立正常的表和FTS3表之间的触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！