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