Monday, May 24, 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

Sunday, May 16, 2010

[Level 1] vim settings.

set nu
set ic
set nolist
set tabstop=4


Wish this helps.
regards,
Stanley Huang

Friday, May 14, 2010

[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.
but how to verify?
use a, dump a schema and data,
use b, dump b schema and data,
compare two results.

[useful skill]
mysql> set foreign_key_checks=0;
mysql> set unique_checks=0;

Wish this helps.
regards,
Stanley Huang

Monday, May 3, 2010

[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
end
)


Wish this helps. regards, Stanley Huang