Posts

Showing posts from March, 2013

event.preventDefault(); in javascript

this Method means prevent event from tag's basic event. I can show you sample below A href's basic function removed <!DOCTYPE html> <html> <head> <scriptsrc="http://code.jquery.com/jquery-1.9.1.js"></script> </head> <body> <ahref="http://jquery.com">default click action is prevented</a> <divid="log"></div> <script> $("a").click(function(event) { event.preventDefault(); $('<div/>') .append('default ' + event.type + ' prevented') .appendTo('#log'); }); </script> </body> </html>

make sequences in mysql

create table mysql_sequences(
seq_name varchar(10) not null,
seq_currval Bigint unsigned not null,
primary key (seq_name)
)engine=MyISAM;





DELIMITER ;;

CREATE FUNCTION nextval()
returns bigint unsigned
modifies sql data
sql security invoker
begin
insert into mysql_sequences
set seq_name='default', seq_currval=(@v_current_value:=1)
on duplicate key
update seq_currval=(@v_current_value:=seq_currval+1);

return @v_current_value;
end ;;

Create Scheduler in Mysql

Repeactable Scheduler

CREATE EVENT daily_ranking
on SCHEDULE EVERY 1 DAY STARTS '2011-05-16 01:00:00' ENDS '2013-04-01 12:59:59'
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'DONE');


every day on 01:00:00 doing insert in period

Only one time Scheduler 


CREATE EVENT daily_ranking
on SCHEDULE at CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'DONE');




blow line is test and Query



CREATE EVENT daily_ranking
on SCHEDULE EVERY 1 DAY STARTS '2011-05-16 01:00:00' ENDS '2013-04-01 12:59:59'
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'DONE');


CREATE EVENT daily_ranking_AT4
on SCHEDULE at CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
INSERT INTO SCHEDULER_TEST VALUES (NOW(), 'Test2');


SELECT * FROM INFORMATION_SCHEMA.EVENTS;

SELECT * FROM SCHEDULER_TEST;

SELECT NOW();

SELECT db, name, interval_value, interval_field, status, on_completion, time_zone, execute_at, starts, ends, last_executed, created, modified
from…

process kill in mysql

kill only query from thread
mysql>kill query 1000;

kill query with thread
mysql> kill 1000;

Phonegap file uploader in android

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <title>File Transfer Example</title>

    <script type="text/javascript" charset="utf-8" src="cordova-2.5.0.js"></script>
    <script type="text/javascript" charset="utf-8">

        // Wait for PhoneGap to load
        //
        // document.addEventListener("deviceready", onDeviceReady, false);
function imageObject(){
var options,
fileEntry,
ft,
imageURI        
        };

        imageObject.prototype = {
// 기본 셋팅
initUpload : function(imageURI){
console.log("initUpload");
image.options = new FileUploadOptions();
window.resolveLocalFileSystemURI(imageURI, image.onSuccessFile, image.onFailFile);
image.imageURI = imageURI;

image.options.fileKey="file";
image.options.fileName=image.fileEntry.name;          
image.options.mimeType="image/jpeg…

Html5 meta Tag for Device

http://www.html5rocks.com/en/mobile/mobifying/

FullText in mysql

Engine = myisam

index = fulltext key fx_article (acolumn, bcolumn)

ex)
select doc_id doc_title, doc_body
from ft_article where match(doc_title, doc_body) against('list' in boolean mode);


ft_min_word_len : minimum length of searching word
ft_stopword_file : engine consider it not word, Skip
ft_max_word_len : maximum length of searching word



in Natural Language Mode
ex) searching by word

select doc_id, doc_title, doc_body
from ft_article
where match(doc_title, doc_body) against('hash key' in natural language mode);


in Boolean Mode

+ : AND
-: Not
nothing : OR

Query Profile in mysql

show variables like 'profiling';

set profiling = 1;

show variables like 'profiling';

select * from employees where emp_no =10001;


show profiles;

show profile cpu for query [query number];


How to remove 8443 port in URL in tomcat

tomcat's Default https port is 8443

if we connect to 8443 Port

example https://www.xxxx.com:8443/web.........

as you can see 8443 exist in URL

The soulution to hide is
use Default https port : 443




server.xml


<Connector port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="443" />




<Connector port="443" protocol="HTTP/1.1" SSLEnabled="true"
               maxThreads="150" scheme="https" secure="true" keystoreFile="xxxxx" keystorePass="xxxx" keystoreType="xxxx"
               clientAuth="false" sslProtocol="TLS" />


use row_number in Mysql

select emp_no , @i:=@i +1 as result  from employees, (select @i:=0) temp order by emp_no;

ibatis get pk key after insert in mysql

<insert id="user.insertUserAndGetId" parameterClass="user">
    <![CDATA[
        INSERT INTO t
        (
            name
        )
        VALUES
        (
            #name#
        )
    ]]>
    <selectKey keyProperty="id" resultClass="Integer">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>



UserVO user = new UserVO();
user.setName("Bob");

System.out.println("Index of user " + user.getName() + " is " + user.getId() + " (before insert)");
int id = ((Integer) sqlMap.insert("user.insertUserAndGetId")).intValue();
System.out.println("Index of user " + user.getName() + " is " + user.getId() + " (after insert)");

Anti join in mysql

create table tab_tset1 (id INT, PRIMARY KEY(id));
create table tab_test2(id INT);
insert into tab_test1 values (1), (2), (3), (4);
insert into tab_test2 values (1), (2);

select t1.id from tab_test1 t1
where t1.id not in (select t2.id from tab_test2 t2);

select t1.id from tab_Test1 t1
where not exists
(select 1 from tab_tetst2 t2 where t2.id=t1.id);

select t1.id
from tab_test1 t1
left outer join tab_test2 t2 on t1.id=t2.id
where t2.id is null;


choose one thing that you want

if there are a lot of datas i prefer to use third way

DATE_FORMAT, STR_TO_DATE

-- datetime, date to str

SELECT DATE_FORMAT(REG_DATE, '%Y-%m-%d %H:%i:%s') FROM TB_FILE;

SELECT DATE_FORMAT(REG_DATE, '%Y-%m-%d %H:%i:%s') FROM TB_FILE;

-- str to datetime

select STR_TO_DATE('2011-04-30 15:13:25', '%Y-%m-%d %H:%i:%s');

--

SELECT NOW();

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);

linux ip setting

vi /etc/sysconfig/network-script/ifcfg-eth0
 DEVICE=eth0  BOOTPROTO=none  HWADDR=08:00:27:02:c5:aa  ONBOOT=yes  NETMASK=255.255.255.0  IPADDR=192.168.0.200  GATEWAY=192.168.0.1  TYPE=Ethernet
and 
service network restart

tomcat 7 auth

http://www.avajava.com/tutorials/lessons/how-do-i-use-basic-authentication-with-tomcat.html?page=1

string to datetime in mysql

http://www.geeksengine.com/database/single-row-functions/conversion-functions.php

Spring Properties get in class

test.properties application.file.home = fileHomeString spring.xmlclasspath:/properties/test.propertiesBean class public PropertyTest class{ @Value("${" + "application.file.home" + "}") private String propertiValue; }

Spring Mvc jsp Exception Page

Web.xmljava.lang.Exception/WEB-INF/pages/error.jsperror.jsp <%@ page language="java" contentType="text/html; charset=EUC-KR" isErrorPage="true" pageEncoding="EUC-KR"%> <%@taglib prefix = "c" uri = "http://java.sun.com/jsp/jstl/core" %> Insert title here ${hello} This is an error page <%= exception %> HandlerExceptionResolver class public class SnsHandlerExceptionResolver implements HandlerExceptionResolver @Override public ModelAndView resolveException(HttpServletRequest request, HttpServletResponse response, Object handler, Exception error) { if (request.getRequestURI().contains(".htm")) { request.setAttribute("javax.servlet.error.exception", error); request.setAttribute("hello", "hello"); final ModelAndView view = new ModelAndView(); return view; } } If servlet Exception Occured this error.jsp page will called

Index list depending on Engine in mysql

MyISAM : B-Tree, R-tree, fulltext-index
InnoDB :  B-Tree
Memory : B-Tree, Hash
TokuDB : Fractal-Tree

'cascade on delete' in mysql

what mean cascade on delete ??

this means if parent record deleted, the child records referencing parent pk also deleted


create table tb_parent (
 id int not null,
fd varchar(100) not null,
primary key (id)
)ENGINE = INNODB;

create table tb_child(
id int not null,
pid int default null,
fd varchar(100) default null,
primary key (id),
key ix_parentid(pid),
constraint child_ibfk_1 foreign key (pid) references tb_parent (id) on delete cascade
) ENGINE = INNODB;

insert into tb_parent values (1, 'parent-1'), (2, 'parent-2');

insert into tb_child values(100, 1, 'childe-100');


match against in mysql

CREATE TABLE `articles` (
  `id` int(11) NOT NULL DEFAULT '0',
  `title` varchar(65) DEFAULT NULL,
  `topic` varchar(25) NOT NULL DEFAULT '',
  `author` varchar(25) NOT NULL DEFAULT '',
  `ondate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `body` text NOT NULL,
  KEY `index_id` (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$



select id,title FROM articles WHERE
    MATCH(title) AGAINST ('+cvs' IN BOOLEAN MODE) limit 1000;

what you have to consier is depending on DB Engine

MYSQL EXPLAIN QUERY

Explain 정보보는법
인덱스가 적절히 사용되고 있는지 검토
나열된 순서는 MYSQL 이 쿼리처리에 사용하는 순서대로 출력 EXPLAIN 의 각 행 설명 1. id : SELECT 번호, 쿼리내의 SELECT 의 구분번호 2. select_type : SELECT 의 타입 SIMPLE: 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)PRIMARY: 가장 외곽의 SELECTUNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECTDEPENDENT UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적UNION RESULT: UNION 의 결과물SUBQUERY: 서브쿼리의 첫번째 SELECTDEPENDENT SUBQUERY: 서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적DERIVED: SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)3. table : table명 4. type : 조인타입, 우수한 순서대로… 뒤로갈수록 나쁜 조인형태 system
테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우const
많아야 하나의 매치되는 행만 존재할 때
PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때
각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름eq_ref
조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우
조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
인덱스된 컬럼이 = 연산에 사용되는 경우ref
이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때
leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때
(즉 키값으로 단일행을 추출할수 없을때)
사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입
ref 는 인덱스된 컬럼과 = 연산에서 사용됨ref_or_null
ref 와 같지만 NULL 값을 포함하는 행…

'show processlist' in mysql

you can see process list

LOG setting in mysql

vi /etc/my.cnf

#general Query
log=mysql-general.log


#slow Query
slow-query-log = 1
long_query_time = 1
log_slow_queries = mysql-slow.log


restart mysql





show variables , show global variables

which my.cnf is read in mysql

mysql --help

count(*) over(order by no range between 2 preceding and 4 following)

select no, name, count(*)
over(order by no range between 2 preceding and 4 following) as sim_cnt
from pt_test

before -2 after 4 from your no


NO,NAME,SIM_CNT
-21,hello,1
1,hello2,4
4,hello2,3
4,hello,3
5,hello,3
10,hello,1

sum over() in oracle >> really good

select * from pt_test

NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello


select no, name, sum(no) over (partition by name ) value ,
sum(no) over (partition by name order by no range unbounded preceding) preceding from pt_test


NO,NAME,VALUE,PRECEDING
-21,hello,-2,-21
4,hello,-2,-17
5,hello,-2,-12
10,hello,-2,-2
1,hello2,5,1
4,hello2,5,5


rank, dense_rank, row_number difference

select * from pt_test



NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello



select no, name, rank() over(order by no desc) all_rank,
                 row_number() over(order by no desc) row_number,
                 dense_rank() over(order by no desc) dense_rank
from pt_test


NO,NAME,ALL_RANK,ROW_NUMBER,DENSE_RANK
10,hello,1,1,1
5,hello,2,2,2
4,hello,3,3,3
4,hello2,3,4,3
1,hello2,5,5,4
-21,hello,6,6,5




window function < rank() over >in oracle

select * from pt_test


NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello


select no, name, rank() over(order by no desc) all_rank,  rank() over(partition by name  order by no desc) no_rank  from pt_Test

NO,NAME,ALL_RANK,ALL_RANK_1 10,hello,1,1 5,hello,2,2 4,hello,3,3 4,hello2,3,1 1,hello2,5,2 -21,hello,6,4

How to use Connect by oracle

first create table


create table node(
em varchar(100),
ma varchar(100)
)


insert into node (em, ma) values(upper('a'), null)
insert into node (em, ma) values(upper('b'), upper('a'))
insert into node (em, ma) values(upper('c'), upper('a'))
insert into node (em, ma) values(upper('d'), upper('c'))
insert into node (em, ma) values(upper('e'), upper('c'))


commit;

select * from node




select level, lpad(' ', 4 * (level-1)) || em 사원, ma 관리자, connect_by_isleaf isleaf
from node
start with ma is null
connect by prior em = ma

select connect_by_root em 루트사원, sys_connect_by_path(em, '/') 경로, em, ma from node start with ma is null connect by prior em = ma



Oracle Range Partition

// create partiton table

create table pt_test(
NO NUMBER NOT NULL,
NAME VARCHAR2(10) NULL
)
PARTITION BY RANGE(no)
(
PARTITION PT_DUMMY VALUES LESS THAN (-1)
);


//add partition
alter table PT_TEST ADD PARTITION PT_1 VALUES LESS THAN (5);

alter table PT_TEST ADD PARTITION PT_2 VALUES LESS THAN (11);

//TEST DATA INSERT
INSERT INTO PT_TEST VALUES (1 , 'A');
INSERT INTO PT_TEST VALUES (2 , 'B');
INSERT INTO PT_TEST VALUES (3 , 'C');
INSERT INTO PT_TEST VALUES (4 , 'D');
INSERT INTO PT_TEST VALUES (5 , 'E');
INSERT INTO PT_TEST VALUES (6 , 'F');
INSERT INTO PT_TEST VALUES (7 , 'G');
INSERT INTO PT_TEST VALUES (8 , 'H');
INSERT INTO PT_TEST VALUES (9 , 'I');
INSERT INTO PT_TEST VALUES (10 , 'J');
INSERT INTO PT_TEST VALUES (11 , 'K');



//Tset

SELECT * FROM PT_TEST
SELECT * FROM PT_TEST PARTITION (PT_1);
SELECT * FROM PT_TEST PARTITION (PT_2);



//drop partition

ALTER TABLE pt_test DROP PARTITION pt_1;



//…