虚表

title: 虚表
date: 2019-12-04 11:07:52
categories:
- SQL
tags:
- SQLITE

-- ----------------------------
-- Table structure for contact
-- ----------------------------
DROP TABLE IF EXISTS "contact";
CREATE TABLE "contact" (
  "contactKey" TEXT,
  "contactName" TEXT,
  "photo" BLOB,
  "isBlack" INTEGER,
  "isMark" INTEGER,
  "sortKey" TEXT,
  "spellFirstChar" TEXT,
  "spell" TEXT,
  "briefSpell" TEXT,
  "prefix" TEXT,
  "imsi" TEXT NOT NULL ON CONFLICT FAIL,
  "systemContactId" TEXT,
  PRIMARY KEY ("contactKey")
);


-- ----------------------------
-- Table structure for phone
-- ----------------------------
DROP TABLE IF EXISTS "phone";
CREATE TABLE "phone" (
  "rowid" TEXT,
  "countryCode" TEXT NOT NULL ON CONFLICT FAIL,
  "number" TEXT NOT NULL ON CONFLICT FAIL,
  "normalizedNumber" TEXT,
  "contactKey" TEXT,
  "type" TEXT,
  PRIMARY KEY ("rowid")
);


-- ----------------------------
-- View structure for PhoneContactModelView
-- ----------------------------
DROP VIEW IF EXISTS "PhoneContactModelView";
CREATE VIEW "PhoneContactModelView" AS SELECT `B`.`contactKey`,`B`.`contactName`,`B`.`photo`,`B`.`imsi`,`B`.`prefix`,`B`.`spell`,`B`.`briefSpell`,`A`.`number`,`A`.`normalizedNumber`,`A`.`countryCode` FROM `phone` AS `A` LEFT OUTER JOIN `contact` AS `B` USING (`contactKey`);