SQL Progeamming in SQLite of CRC-eDirectory

Narrowcasting Loader:

SELECT
sys_config.value,
sys_config.name,
narr_casting.message
FROM
sys_config ,
narr_casting
WHERE 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, 3
FROM 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對象。

SELECT
company.cmp_id,
company.eng_name,
company.chi_name,
company.schi_name,
company.amt_stroke,
company.samt_stroke,
company.logo,
company.descp
FROM
company

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

SELECT
renting.cmy_id,
renting.room_id,
renting.ordering,
renting.isRoute,
floor.floor_name, 
floor.floor_name || room.room_no AS room 
FROM
renting
INNER JOIN room ON renting.room_id = room.r_id
INNER JOIN floor ON room.floor_id = floor.floor_id
WHERE
renting.cmy_id = 11
ORDER BY
renting.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 routing
INNER 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'

以下棄用：

SELECT
route.points,
routing.fp_id
FROM
routing
INNER JOIN route ON routing.rot_id = route.rot_id
INNER JOIN panel_position ON routing.ppos_id = panel_position.ppos_id
WHERE
routing.room_id = 11 AND
panel_position.ppos_name = 'CRC-01'

以下棄用：

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

SELECT
room.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.points
FROM
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

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

SELECT
CAST(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.points
FROM 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