SQL Progeamming in SQLite of CRC-eDirectory

Narrowcasting Loader:

SELECTsys_config.value,sys_config.name,narr_casting.messageFROMsys_config ,narr_castingWHERE sys_config.name LIKE 'narrcast_speed' AND narr_casting.nc_id = 1

Panel03->Zone01:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT       room.r_id,       3,     1,      3,     1 FROM room  WHERE room.floor_id > 6 AND room.floor_id < 20 

Panel03->Zone02:

Panel03->Zone03:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT routing.room_id, 3, 4, 1, 3FROM routing INNER JOIN room ON routing.room_id = room.r_id WHERE room.floor_id > 32 AND room.floor_id < 41 

Panel03->Zone04:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT       room.r_id,       3,     5,      3,     4 FROM room  WHERE room.floor_id > 40 AND room.floor_id < 50 

Panel04->Zone01:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT       room.r_id,       4,     6,      3,     1 FROM room  WHERE room.floor_id > 40 AND room.floor_id < 50 

Panel04->Zone02:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT room.r_id, 4, 7, 1, 2 FROM room  WHERE room.floor_id > 19 AND room.floor_id < 33  

Panel04->Zone03:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT room.r_id, 4, 8, 1, 3 FROM room  WHERE room.floor_id > 32 AND room.floor_id < 41 

Panel04->Zone04:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT       room.r_id,       4,     6,      3,     1 FROM room  WHERE room.floor_id > 40 AND room.floor_id < 50 

Panel01->Zone04:

UPDATE routing SET rot_id=13, fp_id=2, zone_id=4 WHERE ppos_id=1 AND  room_id IN (  SELECT  room.r_id FROM room WHERE room.floor_id > 40 AND room.floor_id < 50 )

Panel01->Zone01:

UPDATE routing SET rot_id=10, fp_id=2, zone_id=1 WHERE ppos_id=1 AND  room_id IN (  SELECT  room.r_id FROM room WHERE room.floor_id > 6 AND room.floor_id < 20 )

Panel02->Zone04:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT       room.r_id,       2,     17,      2,     4 FROM room  WHERE room.floor_id > 40 AND room.floor_id < 50 

Panel02->Zone01:

INSERT INTO routing (room_id, ppos_id, rot_id, fp_id, zone_id)SELECT       room.r_id,       2,     14,      2,     1 FROM room  WHERE room.floor_id > 6 AND room.floor_id < 20 

UPDATE routing SET zone_id = 1 WHERE room_id IN (SELECT routing.room_id FROM routing INNER JOIN room ON routing.room_id = room.r_id WHERE room.floor_id > 6 AND room.floor_id < 20 )

首先載入所有company表中的數據，為每一行數據分別創建一個Company對象。

SELECTcompany.cmp_id,company.eng_name,company.chi_name,company.schi_name,company.amt_stroke,company.samt_stroke,company.logo,company.descpFROMcompany

然後，再遍歷Company對象數組，對每一個company，找出屬於它的房間以及樓層信息。

SELECTrenting.cmy_id,renting.room_id,renting.ordering,renting.isRoute,floor.floor_name, floor.floor_name || room.room_no AS room FROMrentingINNER JOIN room ON renting.room_id = room.r_idINNER JOIN floor ON room.floor_id = floor.floor_idWHERErenting.cmy_id = 11ORDER BYrenting.ordering ASC

查詢結果得到后，更新該Company實例的floor，roomId，roomNo屬性。

然後再根據房間ID尋找相關的floorPlan和route信息。

最新調整，因為數據表routing中將用0作為外鍵id值來表示route或者floor plan或者lift zone不存在，爲了不至於返回一個空結果，將INNER JOIN 變成LEFT JOIN：

SELECT route.points, routing.fp_id, lift_zone.zone_name FROM routingINNER JOIN panel_position ON routing.ppos_id = panel_position.ppos_id LEFT JOIN route ON routing.rot_id = route.rot_id LEFT JOIN lift_zone ON routing.zone_id = lift_zone.zone_id WHERE routing.room_id = 299 AND panel_position.ppos_name = 'd536772b24277636e56da046ccc76d61'

以下棄用：

SELECTroute.points,routing.fp_idFROMroutingINNER JOIN route ON routing.rot_id = route.rot_idINNER JOIN panel_position ON routing.ppos_id = panel_position.ppos_idWHERErouting.room_id = 11 ANDpanel_position.ppos_name = 'CRC-01'

以下棄用：

BusinessNature将被抛弃使用。Floor_plan表内的图片将被分开载入，这样每张图片就只需要载入一个备份在内存中。而在查询某间公司时，可以根据相关联的FloorPlan的id寻找到相应的FP图片。

SELECTroom.floor,room.room_no,renting.ordering,company.eng_name,company.chi_name,company.amt_stroke,company.biz_ntr_id,company.logo,company.descp,routing.fp_id,route.pointsFROMroomINNER JOIN renting ON room.r_id = renting.room_idINNER JOIN company ON renting.cmy_id = company.cmp_idINNER JOIN routing ON room.r_id = routing.room_idINNER JOIN route ON routing.rot_id = route.rot_idINNER JOIN panel_position ON routing.ppos_id = panel_position.ppos_idWHEREpanel_position.ppos_name = "CRC-01"GROUP BY company.cmp_id

加入針對floor進行數字排序的查詢將會是：

SELECTCAST(room.floor AS INTEGER) AS floor,room.room_no,renting.ordering,company.eng_name,company.chi_name,company.amt_stroke,company.schi_name,?company.samt_stroke,company.biz_ntr_id,company.logo,company.descp,routing.fp_id,route.pointsFROM room INNER JOIN renting ON room.r_id = renting.room_id INNER JOIN company ON renting.cmy_id = company.cmp_id INNER JOIN routing ON room.r_id = routing.room_id INNER JOIN route ON routing.rot_id = route.rot_id INNER JOIN panel_position ON routing.ppos_id = panel_position.ppos_id WHERE panel_position.ppos_name = "CRC-01" GROUP BY company.cmp_id ORDER BY floor

字符串替換功能：

UPDATE company SET logo = replace(logo, 'images', 'assets')

在SQLite中得到偽隨機數，連接字符串，以及轉換類型：

UPDATE company SET logo = 'assets/logo/logo_' || CAST(((abs(random()) + 1) % 11) AS TEXT) || '.png' 

在表中增加一个field：

ALTER TABLE "company" ADD COLUMN "is_def_font" VARCHAR(1) NOT NULL DEFAULT 0;

REFs:

http://www.sqlite.org/lang_corefunc.html

http://www.sqlite.org/lang_expr.html