通過觀察ditto的表發(fā)現(xiàn)， 可以添加觸發(fā)器進行統(tǒng)計粘貼次數(shù)的功能，不需要用源碼

使用navicat連接數(shù)據(jù)庫

主要使用的表

Types

MainDeletes

Main

Data

CopyBuffers

通過觀察Main表發(fā)現(xiàn)數(shù)據(jù)庫結(jié)構(gòu)

/*
 Navicat Premium Data Transfer

 Source Server         : ditto
 Source Server Type    : SQLite
 Source Server Version : 3035005 (3.35.5)
 Source Schema         : main

 Target Server Type    : SQLite
 Target Server Version : 3035005 (3.35.5)
 File Encoding         : 65001

 Date: 17/04/2023 10:44:23
*/

PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for Main
-- ----------------------------
DROP TABLE IF EXISTS "Main";
CREATE TABLE "Main" (
  "lID" INTEGER PRIMARY KEY AUTOINCREMENT,
  "lDate" INTEGER,
  "mText" TEXT,
  "lShortCut" INTEGER,
  "lDontAutoDelete" INTEGER,
  "CRC" INTEGER,
  "bIsGroup" INTEGER,
  "lParentID" INTEGER,
  "QuickPasteText" TEXT,
  "clipOrder" REAL,
  "clipGroupOrder" REAL,
  "globalShortCut" INTEGER,
  "lastPasteDate" INTEGER,
  "stickyClipOrder" REAL,
  "stickyClipGroupOrder" REAL,
  "MoveToGroupShortCut" INTEGER,
  "GlobalMoveToGroupShortCut" INTEGER
);

-- ----------------------------
-- Auto increment value for Main
-- ----------------------------
UPDATE "sqlite_sequence" SET seq = 50355 WHERE name = 'Main';

-- ----------------------------
-- Indexes structure for table Main
-- ----------------------------
CREATE INDEX "Main_CRC"
ON "Main" (
  "CRC" ASC
);
CREATE INDEX "Main_ClipGroupOrder"
ON "Main" (
  "clipGroupOrder" DESC
);
CREATE INDEX "Main_ClipOrder"
ON "Main" (
  "clipOrder" DESC
);
CREATE UNIQUE INDEX "Main_ID"
ON "Main" (
  "lID" ASC
);
CREATE INDEX "Main_InGroup2"
ON "Main" (
  "lParentId" ASC,
  "stickyClipGroupOrder" DESC,
  "bIsGroup" ASC,
  "clipGroupOrder" DESC
);
CREATE INDEX "Main_IsGroup"
ON "Main" (
  "bIsGroup" DESC
);
CREATE INDEX "Main_MoveToGroup"
ON "Main" (
  "MoveToGroupShortCut" DESC,
  "GlobalMoveToGroupShortCut" DESC
);
CREATE INDEX "Main_ParentId"
ON "Main" (
  "lParentID" DESC
);
CREATE INDEX "Main_ShortCut2"
ON "Main" (
  "lShortCut" DESC,
  "globalShortCut" DESC
);
CREATE INDEX "Main_TopLevel"
ON "Main" (
  "stickyClipOrder" DESC,
  "bIsGroup" ASC,
  "clipOrder" DESC
);
CREATE INDEX "Main_TopLevelParentID"
ON "Main" (
  "lParentId" ASC,
  "stickyClipOrder" DESC,
  "bIsGroup" ASC,
  "clipOrder" DESC
);

-- ----------------------------
-- Triggers structure for table Main
-- ----------------------------
CREATE TRIGGER "delete_data_trigger"
BEFORE DELETE
ON "Main"
FOR EACH ROW
BEGIN
INSERT INTO MainDeletes VALUES(old.lID, datetime('now'));
END;

PRAGMA foreign_keys = true;

通過表結(jié)構(gòu)發(fā)現(xiàn)里面是有一個觸發(fā)器進行記錄刪除時間的

同理可以增加一個觸發(fā)器，記錄更新時間，就可以記錄下粘貼的使用id和時間

添加觸發(fā)器

-- 添加 ditto 觸發(fā)器
DROP TRIGGER "main"."update_date_trigger"


CREATE TRIGGER "main"."update_date_trigger"
BEFORE UPDATE
ON "Main"
BEGIN
  -- 這里是因為粘貼一次，會進行兩次update， 所以這里刪除掉已經(jīng)記錄的時間，避免生成兩個一樣的記錄
  DELETE FROM MainPaste WHERE lastPasteDate=old.lastPasteDate;
	-- 記錄一次粘貼記錄
  INSERT INTO MainPaste VALUES(old.lID, old.lastPasteDate);
END

COMMIT;

統(tǒng)計粘貼次數(shù)

測試結(jié)論，通過粘貼四次 “好的”， 可以查詢出復(fù)制了四次

-- 統(tǒng)計粘貼次數(shù)
select t1.*, t2.mText from 
( select clipID, count(clipID) cnt from MainPaste group by clipID order by count(clipID)  desc) t1,
Main t2 
where t1.clipID = t2.lID;

統(tǒng)計的作用

1. 通過一段時間的使用，可以記錄使用次數(shù)最高的記錄
2. 使用了如果一年多，則 ditto.db 的文件會特別的大。 則根據(jù)使用記錄次數(shù)， 可以刪除一些不常用的數(shù)據(jù)， 避免空間浪費