Android sqlite约束-视图-检查

1.创建数据库辅助类，用于创建数据库

public final class DvbDatabaseHelper extends SQLiteOpenHelper {    /** 数据库版本号。每当修改数据库结构后，均需要对版本号递增1。 */    public static final int DB_VERSION = 68; //Database version!!!private Context mContext = null;public DvbDatabaseHelper(Context context) {        super(context, "dvb"/*location*/, null, DB_VERSION);        mContext = context;        sLog.LOGI("DvbDatabaseHelper(): create DvbDatabaseHelper! "                + "ProjectName=" + Config.getInstance().getProjectName()                + ", DBLocation=" + Config.getInstance().getDBLocation()                + ", Verion=" + DB_VERSION);    }//onCreate中执行创建sqldb.execSQL(DvbDataContent.DATA_TABLE_PROGRAMS);//onUpgrade中再次执行创建的sql

// 开启外键表约束

public static final String FOREIGN_KEYS_ON                      = "pragma foreign_keys = on;";db.execSQL(DvbDataContent.FOREIGN_KEYS_ON);//所有创建更新sql最好进行异常会滚，放入try catchtry {            initData();            db.beginTransaction();             db.execSQL("....");            db.setTransactionSuccessful();            db.endTransaction();        } catch (Exception e) {            sLog.LOGE("delete dvb database table error!");        }

//创建一个具有外键约束的表

   /** 创建数据表： Data_Table_Crc32 */    private void createTableCrc32() {        DvbDataContent.DATA_TABLE_CRC32 = "create table "                + DvbDataContent.TABLE_CRC32 + "(";        {/* 列定义 */            DvbDataContent.DATA_TABLE_CRC32 += (""                    + Program.TableCrc32Columns.ID           + " integer PRIMARY KEY autoincrement, "                    + Program.TableCrc32Columns.TpKey        + " integer NOT NULL, "                    + Program.TableCrc32Columns.TableId      + " integer NOT NULL, "                    + Program.TableCrc32Columns.SectionNum   + " integer NOT NULL, "                    + Program.TableCrc32Columns.Crc32        + " integer NOT NULL, "                    + Program.TableProgramsColumns.SERVICE_NAME             + " varchar DEFAULT 'n/a', "                    );        }        {/* 外键约束 */            DvbDataContent.DATA_TABLE_CRC32 += ""                    + " constraint fk_TableCrc32_tp  foreign key("                    + Program.TableCrc32Columns.TpKey                    + ") references " + DvbDataContent.TABLE_TRANSPONDERS                    + "(_id) on delete cascade on update cascade";      // crc32表的外键tpkey依赖与tranponder表id字段，随之更新和删除还可以+ on insert cascade        }        DvbDataContent.DATA_TABLE_CRC32 += " )";       {/* 值合法性检查检查 */            DvbDataContent.DATA_TABLE_ELEMENT_STREAM += (""            /* 基本流的pid值有效范围 */            + "constraint ck_StreamPid CHECK(" + Program.TableElementStreamCloumns.STREAM_PID + " >= 0 " +                    " AND " + Program.TableElementStreamCloumns.STREAM_PID +" <= 8191), ");  //ck_StreamPid为自定义检查名称        }    }

/** 创建视图: View_Transponders */

  private void createViewTransponders() {        DvbDataContent.DATA_VIEW_TRANSPONDERS = "create view "                + DvbDataContent.VIEW_TRANSPONDERS + " as select "                + DvbDataContent.TABLE_TRANSPONDERS + ".*" + ", "                + DvbDataContent.TABLE_NETWORKS + ".*" + " from "                + DvbDataContent.TABLE_TRANSPONDERS + ", "                + DvbDataContent.TABLE_NETWORKS + " where "                + DvbDataContent.TABLE_TRANSPONDERS + "."                + Program.TableTranspondersColumns.NETWORKS_ID + " = "                + DvbDataContent.TABLE_NETWORKS + "._id";    }

// 频道-对应nvod信息级联删除触发器

       DvbDataContent.DATA_TRIGGER_PROGRAM_NVOD_DEL = "CREATE TRIGGER "                + DvbDataContent.TRIGGER_PROGRAM_NVOD_DEL                + " BEFORE DELETE ON " + DvbDataContent.TABLE_PROGRAMS                + " FOR EACH ROW BEGIN " + "delete from "                + DvbDataContent.TABLE_NVOD + "  where "                + Program.TableNvodColumns.REFERENCE_IDENT + "=old."                + Program.TableProgramsColumns.ID + " or "                + Program.TableNvodColumns.TIMESHIFTED_IDENT + "=old."                + Program.TableProgramsColumns.ID + "; END;";

// 插入节目分类信息元素，关键字段数据重复检测触发器

      DvbDataContent.DATA_TRIGGER_BOUQUET_ADD = "CREATE TRIGGER "                + DvbDataContent.TRIGGER_BOUQUET_ADD + " BEFORE INSERT ON "                + DvbDataContent.TABLE_BOUQUETS + " FOR EACH ROW BEGIN "                + "SELECT RAISE(FAIL,'insert failed') WHERE EXISTS(select "                + Program.TableBouquetsColumns.BOUQUET_ID + " from "                + DvbDataContent.TABLE_BOUQUETS + " where "                + Program.TableBouquetsColumns.BOUQUET_ID + " = new."                + Program.TableBouquetsColumns.BOUQUET_ID + "); END;";

SAX解析器使用：

   SAXParserFactory spf = SAXParserFactory.newInstance(); // 初始化sax解析器        SAXParser sp = spf.newSAXParser(); // 创建sax解析器        DvbXmlContentHandler handler = new DvbXmlContentHandler(db);        sp.parse(inputStream, handler);

write somethings into Setting :

1.add permission<uses-permission android:name="android.permission.WRITE_SETTINGS"></uses-permission>2.addSettings.System.putString(mContext.getContentResolver(), "key" , (String)oneValues.getValue());//annouce provider package&class path and authorities<provider android:name="android.ccdt.dvb.provider.ProgramProvider"            android:authorities="android.ccdt.dvb.provider.ProgramProvider"            android:multiprocess="true" />

//使用工作Hnadler,默认handler挂在UI线程

private HandlerThread mHandlerThread = null; private WorkHandler mHandler = null;        mHandlerThread = new HandlerThread("SvcLiveSearchThread");        assert(mHandlerThread != null);        mHandlerThread.start();    //必须先启动线程，否则获取不到looper      mHandler = new WorkHandler(mHandlerThread.getLooper());  /** 实时节目搜索服务的独立工作线程（Thread）所使用的处理器（Handler）。 */    private final class WorkHandler extends Handler {        public WorkHandler(Looper looper) {            super(looper);        }        @Override        public void handleMessage(Message msg) {       ......       }

更多相关文章

随机推荐