Posts

Showing posts from May, 2010

[Level 3] Using vim to replace ^M with newline

I searched it on net Under unix, inside vim, ^V + gives me a ^M which is the \r character. Wish this helps. regards, Stanley Huang

[Level 1] vim settings.

set nu set ic set nolist set tabstop=4 Wish this helps. regards, Stanley Huang

[Level 3] Schema management tips for MySQL

{Database management tips} [in source save] 4 files in source save. 1. schema: => by release version. => only create table/procedure, no alter, no insert, no drop ...etc. => use mysqldump -d. 2. schema change log: => by date. => split into two parts. one is schema(except foreign key), and put it on the top. Another is foreign key, and put it on the tail. => must add drop tabele before create, and must be sequencial by date. => modify manually. 3. data: => by release version. => only insert, no update, no delete...etc. => use mysqldump. 4. data change log: => by date. => could use insert/update/delete, but delete(truncate) must before update than insert, and must be sequencial by date. => modify manually. [use case] how to create patch: a: use schema comparision. => takes time, but more efficacy. b: use changelog comparision. => esay way, but suffer performance. so, use a to build patch, but use b to verify result.

[Level 3] Rownum/auto_increment, substring, reverse index, case and mutli-table update for MySQL

-- [rownum] -- set initial equipment_id select max(equipment_id) from device into @rownum;   -- insert into equipment insert into device select @rownum:=ifnull(@rownum,-1)+1 from device; -- [multi-table update] update event inner join device using (deviceid) inner join equipment on device.macaddress = equipment.mac_address set event.equipment_id = equipment.equipment_id where event.eventtypeid < 400 ; -- [reverse index] select instr(c,','), substring(c,1,instr(c,',')-1), reverse(substring(c,1,instr(c,',')-1)), reverse(substring(reverse(substring(c,1,instr(c,',')-1)),1,instr(reverse(substring(c,1,instr(c,',')-1)),'@')-1)) from ( select 'a@1:2:3:4,x,y,z' as c union all select '1:2:3:4,x,y,z' as c union all select 'a@b@1:2:3:4,x,y,z' as c ) as t -- [case] update types set id =   ( select case types.id when 1 then 10001 when 2 then 10002 when 3 then 10003 when 4 then 10004 else types.id