研究了一下osx下dock中应用的存储,位于~/Library/Application Support/Dock/下一个比较名字比较长的db文件中,之前简单的介绍过osx launchpad图标的删除,这里对db文件进行了分析。
osx中db文件是sqlite3数据库对应的数据库文件,学过andorid或者ios开发的朋友应该比较的熟悉,查看数据库可以看到该数据库(名字比较长的db文件)中存在一下表: app_sources dbinfo image_cache widgets apps downloading_apps items categories groups widget_sources
其中比较重要的2个表要数apps与items表了,但是那个算是最基本的表呢,查看了一下表结构如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
sqlite
>.schema apps
CREATE TABLE apps
(item_id INTEGER PRIMARY KEY,title VARCHAR,bundleid VARCHAR,storeid VARCHAR,category_id INTEGER,moddate REAL,bookmark BLOB
);
sqlite
>.schema items
CREATE TABLE items
(rowid INTEGER PRIMARY KEY ASC,uuid VARCHAR,flags INTEGER,typeINTEGER,parent_id INTEGER NOT NULL,ordering INTEGER
);
CREATE TRIGGER update_items_order BEFORE UPDATE OF ordering ON items WHEN new.ordering
> old.ordering AND
0==
(SELECT value FROM dbinfo WHERE
key=
'ignore_items_update_triggers'
)
BEGIN
UPDATE dbinfo SET
value=
1WHERE
'ignore_items_update_triggers';
UPDATE items SET ordering = ordering -
1WHERE parent_id = old.parent_id AND ordering BETWEEN old.ordering and new.ordering;
UPDATE dbinfo SET
0WHERE
'ignore_items_update_triggers';
END;
CREATE TRIGGER update_items_order_backwards BEFORE UPDATE OF ordering ON items WHEN new.ordering
< old.ordering AND
'ignore_items_update_triggers'
)BEGIN UPDATE dbinfo SET
'ignore_items_update_triggers'; UPDATE items SET ordering = ordering +
1WHERE parent_id = old.parent_id AND ordering BETWEEN new.ordering and old.ordering; UPDATE dbinfo SET
'ignore_items_update_triggers'; END; CREATE TRIGGER update_item_parent AFTER UPDATE OF parent_id ON items BEGIN UPDATE dbinfo SET
'ignore_items_update_triggers'; UPDATE items SET ordering =
(SELECT ifnull
(MAX
(ordering
),0
)+
1FROM items WHERE
parent_id=new.parent_id AND ROWID
!=old.rowid
)WHERE
ROWID=old.rowid; UPDATE items SET ordering = ordering -
1WHERE parent_id = old.parent_id and ordering
> old.ordering;
UPDATE dbinfo SET
'ignore_items_update_triggers';
END;
CREATE TRIGGER insert_item AFTER INSERT on items WHEN
'ignore_items_update_triggers';
UPDATE items SET ordering =
(SELECT ifnull
(MAX
(ordering
),128)">parent_id=new.parent_id
)WHERE
ROWID=new.rowid;
UPDATE dbinfo SET
'ignore_items_update_triggers';
END;
CREATE TRIGGER app_inserted AFTER INSERT ON items WHEN new.type =
4OR new.type =
5
BEGIN
INSERT INTO image_cache VALUES
(new.rowid,160)">0,NULL,NULL
);
END;
CREATE TRIGGER widget_inserted AFTER INSERT ON items WHEN new.type =
6OR new.type =
7
BEGIN
INSERT INTO image_cache VALUES
(new.rowid,NULL
);
END;
CREATE TRIGGER app_deleted AFTER DELETE ON items WHEN old.type =
4OR old.type =
5
BEGIN
DELETE FROM image_cache WHERE
item_id=old.rowid;
END;
CREATE TRIGGER widget_deleted AFTER DELETE ON items WHEN old.type =
6OR old.type =
7
BEGIN
DELETE FROM image_cache WHERE
item_id=old.rowid;
END;
CREATE TRIGGER item_deleted AFTER DELETE ON items
BEGIN
DELETE FROM apps WHERE
rowid=old.rowid;
DELETE FROM
groupsWHERE
item_id=old.rowid;
DELETE FROM widgets WHERE
rowid=old.rowid;
DELETE FROM downloading_apps WHERE
item_id=old.rowid;
UPDATE dbinfo SET
1WHERE old.parent_id = parent_id AND ordering
> old.ordering;
UPDATE dbinfo SET
'ignore_items_update_triggers';
END;
CREATE INDEX items_uuid_index ON items
(uuid
);
CREATE INDEX items_ordering_index ON items
(parent_id,ordering
);
CREATE INDEX items_type ON items
(
type
);
sqlite
>
从上面可以看出items对应了很多的触发器,所以items应该是基表。说到了触发器,下面开始说我们的正题。
当数据库中表中的数据发生变化时,包括insert,update,delete任意操作,如果我们对该表写了对应的DML触发器,那么该触发器自动执行。DML触发器的主要作用在于强制执行业 务规则,以及扩展Sql Server约束,默认值等。因为我们知道约束只能约束同一个表中的数据,而触发器中则可以执行任意Sql命令。
下面从一个例子来介绍一下触发器的使用。数据库中有3个表,src、backup、del,分表代表原数据表,备份数据表和删除数据表,我们现在要实现的是对原数据的插入与更新以及删除要同步到backup表中,对原数据的删除,要将删除的信息写入del表中,也就是要保证src表与backup表是时刻一模一样的,del表存储删除的信息。
触发器的创建如下:
1
2
3
4
5
6
CREATE TRIGGER
<
[BEFORE
|AFTER
]
>
<
[INSERT
|UPDATE
|DELETE
]
>
ON
<tableName
>
//dbo代表该表的所有者
FOR EACH ROW
BEGIN
--dosomething
END ;
三个表都非常的简单,只有一个int类型的id属性。创建表以及触发器如下:
CREATE TABLE src
(
idint
);
CREATE TABLE backup
(
idint
);
CREATE TABLE del
(
idint
);
CREATE TRIGGER trigger1 after insert on src begin insert into backup values
(new.id
); end;
CREATE TRIGGER trigger2 after update on src begin update backup
set
id=new.id where
id=old.id ; end;
CREATE TRIGGER trigger3 after delete on src begin insert into del values
(old.id
); delete from backup where
id=old.id; end;
上面的3个触发器的意思还是很容易理解的,我们需要注意的一点是在begin与end之间的操作中,对前面作用表的新旧数据使用new和old进行指向,如在src表进行插入操作后,会处罚trigger1,此时trigger将src中id的新值(new.id)插入带backup表中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
sqlite
>insert into src values
(
1
);
//插入数据
1
sqlite
>insert into src values
(
2
);
//插入数据
2
sqlite
>
select
*from src ;
1
select
*from backup ;
select
*from del ;
sqlite
>update src
id=
3where
2;
//更新数据
sqlite
>
3
sqlite
>
select
*from del ;
sqlite
>delete from src where
1;
//删除数据
sqlite
>
select
*from del ;
1
sqlite
>
可以看出,在src表发生更删改的时候,触发器启动了,执行了相应的操作,保证了数据的统一性。
更多文章请前往小胖轩.
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!