lonelycat's cellar

Audio, Video 예제

Audio
- MediaPlayer 클래스를 사용.
- create한 개체를 release 안하면 런타임 에러 발생
- 실제론 하나의 소리에 대한 객체를 미리 모두 생성해놓고, 플레이하고, 어플 종료 시 릴리즈하는 식으로 사용하게 될 듯

// 이전 MediaPlayer의 모든 리소스를 릴리스한다.         // 아래의 릴리즈를 주석처리하면 사운드가 겹쳐서 들리게도 할 수 있으나, 릴리즈 안하고 계속 생성만 하다보면 런타임 오류 발생         if(mp != null)             mp.release();         // 이 사운드를 재생하기 위해 새로운 MediaPlayer를 만든다.         mp = MediaPlayer.create(this, resId);         mp.start();

Video
- VideoView에 video파일 로드 후, start.
- 재생가능한 파일에 제약이 좀 있는 듯.

윈도우즈-이클립스에서 LogCat에 UTF-8로 로그를 찍으면 글자가 깨짐.
(우분투 등 OS자체가 UTF-8이면 문제가 없는 듯?)

해결방법은 없는 듯하고 임시방편으로 다음과 같이 처리

1. 이클립스 메뉴의 Run -> Externel Tools -> Externel Tools Configurations... 클릭
2. Program 선택하고 New Launch Configuration 클릭
3. Name에 LogCat(아무거나), Main탭의 Location에 안드로이드 툴의 adb.exe 선택, Arguments에 logcat 입력, Common탭의 Console Encoding에서 UTF-8 선택하고 적용
4. Run하면 이클립스 콘솔창에 LogCat이 실행됨. 보기에는 별로지만 한글 지원됨.

http://code.google.com/p/android/issues/detail?id=1590

px : 화면 위의 점. 디스플레이의 dpi(dots per inch)가 높아지면 그림이 작아짐.
dp : density-independent pixels. 밀도에 독립적인 화소. dpi가 높아지면 같이 커진다.
dip : 구글 예제에서 자주 쓰이는 dp와 동의어
sp : scale-independent pixels. 스케일에 독립적인 화소. 사용자의 글꼴 크기 설정에 따라서 확대/축소된다.

==> 인터페이스가 현재와 미래의 모든 디스플레이에 사용될 수 있으려면
텍스트 크기로는 sp 단위를, 그 외의 경우에는 dip 단위를 사용하고, 비트맵 보다는 벡터 그래픽을 사용하도록 한다.

익명의 내부 클래스가 만들어질 때마다 1KB의 메모리가 소요된다고 한다...

주로 클릭 처리 등의 리스너에 많이 익명의 내부 클래스로 처리하는데, 가능한한 줄이는 것도 퍼포먼스를 위한 하나의 방법일 듯

AnyEdit Tools   http://andrei.gmxhome.de/eclipse/   공백문자 표시 등 에디팅 편의 기능 -> 이건 쓸만한 듯
Checkstyle Plug-in   http://eclipse-cs.sf.net/update/   Source Code Analyzer -> 이건 모르겠음 ㅡ.ㅡ;;

html 에디터만 추가하기
Go to "Help" > "Install New Software" Choose to work with the site "http://download.eclipse.org/releases/galileo", Expand "Web, XML and Java EE development", Check "Web Page Editor" and click Next to continue with the install

UPDATE /*+ bypass_ujvc */ (            SELECT A.CATEGORY_ID            FROM DEPLOY_PRODUCT_CATEGORY A, PRODUCT_INFO B            WHERE A.CATEGORY_ID LIKE '%DP070%'                      AND A.PRODUCT_ID = B.PRODUCT_ID                      AND B.STATUS LIKE '6%' ) SET CATEGORY_ID = 'XXXXXXXX';

/*+ bypass_ujvc */ 이 부분 필수임.

다음과 같이 SELECT에서 2개의 컬럼을 가져다가 넣는 것도 가능

update /*+ bypass_ujvc */ (    select e.PRODUCT_ID, e.PROD_DESC o_PROD_DESC,f.PROD_DESC N_PROD_DESC    from (      select b.CHANNEL_ID,c.PRODUCT_ID,max(d.PROD_DESC) PROD_DESC      from PRODUCT_INFO a, PRODUCT_CATEGORY b, PRODUCT_INFO c, TBL_DP_PROD d      where a.CONTENT_TYPE='09'        and a.PRODUCT_ID = b.CHANNEL_ID        and b.PRODUCT_ID = c.PRODUCT_ID        and b.PRODUCT_ID <> b.CHANNEL_ID        and b.CHANNEL_ID = d.PROD_ID     group by b.CHANNEL_ID,c.PRODUCT_ID   ) e, TBL_DP_PROD f   where e.PRODUCT_ID = f.PROD_ID     and f.PROD_DESC is null ) set N_PROD_DESC = o_PROD_DESC

[7.2 뷰가 있는 웹]에서 WebView에 setWebViewClient() 를 실행하지 않으면 WebView에서 웹페이지가 열리지 않고,
그냥 브라우저가 실행된다.(Intent로 여는 것처럼)

안드로이드OS가 버젼업되면서 바뀐 것 같지는 않고, 저자의 미스인 듯?

소스는 다음과 같다.

private void openBrowser() {         webView.getSettings().setJavaScriptEnabled(true);         webView.loadUrl(urlText.getText().toString());         webView.setWebViewClient(new HelloWebViewClient());  // WebViewClient 지정     }             private class HelloWebViewClient extends WebViewClient {                 @Override         public boolean shouldOverrideUrlLoading(WebView view, String url) {             view.loadUrl(url);             return true;         }     }

/**      * byte 단위로 substring      * @param str      * @param beginIndex 0부터 시작      * @param endIndex substr할 바이트 수. 2-byte문자의 경우 바이트가 부족하면 그 앞 글자까지만 자름.      * @param bytesForDB 2-byte문자(한글 등)의 DB에서의 바이트 수. 예를들어 오라클/UTF-8이면 3바이트임      * @return      */     public static String substrb(String str, int beginIndex, int endIndex, int bytesForDB) {                 if(str == null)             return "";                 String tmp = str;         int slen = 0, blen = 0;         char c;                 if(tmp.getBytes().length > endIndex - 1) { // 0부터 카운트 되므로 endIndex - 1                         while(blen + 1 < endIndex - 1) {                                 c = tmp.charAt(slen);                 blen++;                 slen++;                                 if(c > 127)                     blen = blen + (bytesForDB - 1); //2-byte character..             }                         tmp = tmp.substring(beginIndex, slen);         }                 return tmp;     }

public void testStringUtil() {                 String str = "한글테스트";         int bytesForDB = 3;                 System.out.println(StringUtil.substrb(str, 0, 9, bytesForDB));         System.out.println(StringUtil.substrb(str, 0, 8, bytesForDB));     }

<s:if test="%{contentType < 11}"><%--VOD면--%>     <option value="2">2 일</option>     <option value="7">7 일</option>     <option value="30">30 일</option> </s:if> <s:else>     <option value="7">7 일</option>     <option value="30">30 일</option>     <option value="60">60 일</option>     <option value="90">90 일</option>     <option value="120">120 일</option>     <option value="365">365 일</option> </s:else>

contentType은 String 인데도, %{} 안에서 숫자와의 비교가 가능
숫자로 변환해서 비교하는 건지, 문자열로 비교하는 건지...

ImageIO, BufferedImage 사용

private static BufferedImage createResizedCopy(Image originalImage, int scaledWidth, int scaledHeight, boolean preserveAlpha) {                 int imageType = preserveAlpha ? BufferedImage.TYPE_INT_RGB : BufferedImage.TYPE_INT_ARGB;                 BufferedImage scaledBI = new BufferedImage(scaledWidth, scaledHeight, imageType);         Graphics2D g = scaledBI.createGraphics();         if (preserveAlpha) {                 g.setComposite(AlphaComposite.Src);         }         g.drawImage(originalImage, 0, 0, scaledWidth, scaledHeight, null);         g.dispose();                 return scaledBI;     }         /**      * 이미지 리사이즈      * @param srcFile 원본 이미지      * @param destFile 리사이즈 이미지      * @param width 리사이즈될 가로 길이      * @param height 리사이즈될 세로 길이      * @param imageType 리사이즈될 이미지 타입(PNG/JPG 등)      * @param preserveAlpha 투명화 여부      * @throws IOException      */     public static void resizeImage(File srcFile, File destFile, int width, int height, String imageType, boolean preserveAlpha) throws IOException   {                 BufferedImage originalImage = ImageIO.read(srcFile);         BufferedImage resizedImage = createResizedCopy(originalImage, width, height, preserveAlpha);         ImageIO.write(resizedImage, imageType, destFile);         //        System.out.println(ImageIO.getReaderFormatNames().toString()); //        System.out.println(ImageIO.getWriterFormatNames().toString());     }

- 프로젝트 만들고, 빌드 패스 오류라고 컴파일 되지 않을 때 -> Project > Clean 실행
- 시스템 환경설정에 android-sdk/tools 디렉토리를 path에 넣어둘 것

http://developer.android.com/reference/android/app/Activity.html

- Activity가 화면 전면에 있으면(activity stack의 최상위), active/running.
- Activity가 포커스를 잃었으나 여전히 보이면(풀사이즈가 아닌 창이 뜨거나, 반투명 창이 뜨면), paused.
   모든 상태값, 멤버정보는 유지됨.
   Paused Activity는 살아있으나 메모리가 극단적으로 부족하면 시스템이 프로세스를 죽일 수 있음.
- Activity가 다른 activity에 의해 완전히 가려지면 stopped.
   모든 상태값, 멤버정보는 유지되나, 더이상 사용자에게 보이지 않으며, 메모리가 필요하면 시스템이 종종 프로세스를 죽일 수 있음.
- Activity가 paused 또는 stopped 일 경우, 시스템의 종료 요청 또는 그냥 프로세스를 죽이는 방식으로 메모리에서 삭제될 수 있음.
   이 경우 사용자에게 다시 보여지려면 완전히 다시 시작해서 기존의 상태를 복원해야 함.

- entire lifetime은 onCreate(Bundle) ~ onDestroy()
   global을 onCreate()에서 설정하고, onDestroy()에서 리소스를 해제.
   ex) 쓰레드를 onCreate()에서 만들고, onDestroy()에서 정지
- visible lifetime은 onStart() ~ onStop()
   리소스를 유지하고 사용자에게 activity를 보여줘야함.
   ex) UI에 영향을 주는 변화를 모니터하기 위해 BroadcastReceiver를 onStart()에서 등록하고 onStop()에서 해제.
   onStart()와 onStop()은 여러번 불려질 수 있음.
- foreground lifetime은 onResume() ~ onPause()
   이 사이에 activity는 다른 모든 activity보다 상위에 있고 사용자와 상호작용함.
   activity는 빈번하게 resume/pause됨.
   ex) device가 sleep모드로 되는 때, activity 결과가 전달될 때, 새 intent가 전달될 때 등. 따라서 이 메소드의 코드는 가벼워야 함.

http://mwultong.blogspot.com/2006/05/html-tooltip-tag.html

HTML에서는 title 속성으로 말풍선을 만듭니다.

http://www.sqlite.org/sqlite.html

The SQLite library includes a simple command-line utility named sqlite3 (or sqlite3.exe on windows) that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use the sqlite3 program.

Getting Started

To start the sqlite3 program, just type "sqlite3" followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The sqlite3 program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.

For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:

$ sqlite3 ex1
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

You can terminate the sqlite3 program by typing your systems End-Of-File character (usually a Control-D). Use the interrupt character (usually a Control-C) to stop a long-running SQL statement.

Make sure you type a semicolon at the end of each SQL command! The sqlite3 program looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite3 will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:

sqlite> CREATE TABLE tbl2 (
   ...>   f1 varchar(30) primary key,
   ...>   f2 text,
   ...>   f3 real
   ...> );
sqlite>

Aside: Querying the SQLITE_MASTER table

The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:

$ sqlite3 ex1
SQLite vresion 3.6.11
Enter ".help" for instructions
sqlite> select * from sqlite_master;
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
sqlite>

But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table.

The schema for TEMPORARY tables is not stored in the "sqlite_master" table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named "sqlite_temp_master". The "sqlite_temp_master" table is temporary itself.

Special commands to sqlite3

Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.

For a listing of the available dot commands, you can enter ".help" at any time. For example:

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.genfkey ?OPTIONS?     Options are:
                         --no-drop: Do not drop old fkey triggers.
                         --ignore-errors: Ignore tables with fkey errors
                         --exec: Execute generated SQL immediately
                       See file tool/genfkey.README in the source 
                       distribution for further information.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.iotrace FILE          Enable I/O diagnostic logging to FILE
.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.timer ON|OFF          Turn the CPU timer measurement on or off
.width NUM NUM ...     Set column widths for "column" mode
sqlite>

Changing Output Formats

The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

sqlite> .mode list
sqlite> select * from tbl1;
hello|10
goodbye|20
sqlite>

You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this:

sqlite> .separator ", "
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>

In "line" mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign and the column data. Successive records are separated by a blank line. Here is an example of line mode output:

sqlite> .mode line
sqlite> select * from tbl1;
one = hello
two = 10

one = goodbye
two = 20
sqlite>

In column mode, each record is shown on a separate line with the data aligned in columns. For example:

sqlite> .mode column
sqlite> select * from tbl1;
one         two       
----------  ----------
hello       10        
goodbye     20        
sqlite>

By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:

sqlite> .width 12 6
sqlite> select * from tbl1;
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>

The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:

sqlite> .header off
sqlite> select * from tbl1;
hello         10    
goodbye       20    
sqlite>

Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.

When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:

sqlite> .mode insert new_table
sqlite> select * from tbl1;
INSERT INTO 'new_table' VALUES('hello',10);
INSERT INTO 'new_table' VALUES('goodbye',20);
sqlite>

The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.

Writing results to a file

By default, sqlite3 sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to begin writing to standard output again. For example:

sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$

Querying the database schema

The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.

For example, to see a list of the tables in the database, you can enter ".tables".

sqlite> .tables
tbl1
tbl2
sqlite>

The ".tables" command is similar to setting list mode then executing the following query:

SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL 
SELECT name FROM sqlite_temp_master 
WHERE type IN ('table','view') 
ORDER BY 1

In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you'll find exactly the above query.

The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices. We have:

sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite> .schema tbl2
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>

The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:

SELECT sql FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name

Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name

You can supply an argument to the .schema command. If you do, the query looks like this:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name

The "%s" in the query is replace by your argument. This allows you to view the schema for some subset of the database.

sqlite> .schema %abc%

Along these same lines, the ".table" command also accepts a pattern as its first argument. If you give an argument to the .table command, a "%" is both appended and prepended and a LIKE clause is added to the query. This allows you to list only those tables that match a particular pattern.

The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.

sqlite> .databases

Converting An Entire Database To An ASCII Text File

Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

A good way to make an archival copy of a database is this:

$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:

$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2

Other Dot Commands

The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:

sqlite> .explain
sqlite> explain delete from tbl1 where two<20;
addr  opcode        p1     p2     p3          
----  ------------  -----  -----  -------------------------------------   
0     ListOpen      0      0                  
1     Open          0      1      tbl1        
2     Next          0      9                  
3     Field         0      1                  
4     Integer       20     0                  
5     Ge            0      2                  
6     Key           0      0                  
7     ListWrite     0      0                  
8     Goto          0      2                  
9     Noop          0      0                  
10    ListRewind    0      0                  
11    ListRead      0      14                 
12    Delete        0      0                  
13    Goto          0      11                 
14    ListClose     0      0

The ".timeout" command sets the amount of time that the sqlite3 program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.

And finally, we mention the ".exit" command which causes the sqlite3 program to exit.

Using sqlite3 in a shell script

One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite3 program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits. This mechanism is designed to make sqlite3 easy to use in conjunction with programs like "awk". For example:

$ sqlite3 ex1 'select * from tbl1' |
>  awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$

Ending shell commands

SQLite commands are normally terminated by a semicolon. In the shell you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won't work in sqlite3_exec(), because the shell translates these into a semicolon before passing them to that function.

Compiling the sqlite3 program from sources

The source code to the sqlite3 command line interface is in a single file named "shell.c" which you can download from the SQLite website. Compile this file (together with the sqlite3 library source code to generate the executable. For example:

gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread

http://www.sqlite.org/quickstart.html

Here is what you do to start experimenting with SQLite without having to do a lot of tedious reading and configuration:

Download The Code
Get a copy of the prebuilt binaries for your machine, or get a copy of the sources and compile them yourself. Visit the download page for more information.

Create A New Database
At a shell or DOS prompt, enter: "sqlite3 test.db". This will create a new database named "test.db". (You can use a different name if you like.)
Enter SQL commands at the prompt to create and populate the new database.
Additional documentation is available here

Write Programs That Use SQLite
Below is a simple TCL program that demonstrates how to use the TCL interface to SQLite. The program executes the SQL statements given as the second argument on the database defined by the first argument. The commands to watch for are the sqlite3 command on line 7 which opens an SQLite database and creates a new TCL command named "db" to access that database, the invocation of the db command on line 8 to execute SQL commands against the database, and the closing of the database connection on the last line of the script.

#!/usr/bin/tclsh if {$argc!=2} {   puts stderr "Usage: %s DATABASE SQL-STATEMENT"   exit 1 } load /usr/lib/tclsqlite3.so Sqlite3 sqlite3 db [lindex $argv 0] db eval [lindex $argv 1] x {   foreach v $x(*) {     puts "$v = $x($v)"   }   puts "" } db close

Below is a simple C program that demonstrates how to use the C/C++ interface to SQLite. The name of a database is given by the first argument and the second argument is one or more SQL statements to execute against the database. The function calls to pay attention to here are the call to sqlite3_open() on line 22 which opens the database, sqlite3_exec() on line 27 that executes SQL commands against the database, and sqlite3_close() on line 31 that closes the database connection.

See also the Introduction To The SQLite C/C++ Interface for an introductory overview and roadmap to the dozens of SQLite interface functions.

#include <stdio.h> #include <sqlite3.h> static int callback(void *NotUsed, int argc, char **argv, char **azColName){   int i;   for(i=0; i<argc; i++){     printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");   }   printf("\n");   return 0; } int main(int argc, char **argv){   sqlite3 *db;   char *zErrMsg = 0;   int rc;   if( argc!=3 ){     fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);     exit(1);   }   rc = sqlite3_open(argv[1], &db);   if( rc ){     fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));     sqlite3_close(db);     exit(1);   }   rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);   if( rc!=SQLITE_OK ){     fprintf(stderr, "SQL error: %s\n", zErrMsg);     sqlite3_free(zErrMsg);   }   sqlite3_close(db);   return 0; }

http://kio.zc.bz/Lecture/regexp.html#chap03


1. 정규식이란?

String의 검색,치환,추출을 위한 패턴.
언어별 사용법은 대동소이함.
패턴예>전화번호 형식, 이메일 형식 등.
2. 정규식 만들기

Javascript
var regexp = /pattern/[flags] ;
var test = regexp.test(to be checked)
var regexp = new RegExp("pattern"[, "flags"]);
var test = regexp.test(to be checked)
flags for javascript
g : global match, 일반적으로 패턴이 1번만 발견되면 찾기를 종료하지만, g flag가 있으면, 문자열 내에서 모든 패턴을 찾는다.
i : ignore case, 대소문자를 고려하지 않고 체크한다.[a-z]와 [A-Z]는 같은 표현이 된다.
m : match over multiple lines, 여러 줄에 걸쳐 체크를 한다.
Java
java.util.regex package
Pattern p = Pattern.compile("pattern");
Matcher m = p.matcher("string to be checked");
boolean b = m.matches();
boolean b = Pattern.matches("pattern", "string to be checked");
3. 정규식 표현법

*ⓥ는 valid, ⓘ는 invalid
*두꺼운 글씨체는 매칭되는 부분.
*예제는 javascript 기준이며, 언어에 따라 다소 차이가 발생할 수 있다.
문자    용도    예제
\   
특수문자를 의미
특수문자의 사용을 제외(특수문자 앞에서)
b는 b라는 글자를 의미 하지만 \b는 단어 경계를 의미
*은 0번이상 반복이라는 의미이지만, \*는 *이라는 글자를 의미.
^    문자열의 시작. []안에서는 not의 의미
* ^A는 "A로 시작"이라기 보다는 "시작 직후에 A가 나온다"는 의미로 해석하는 것이 좋다. 즉, 시작과 끝과 같은 빈 공간을 하나의 문자로 간주하는 것이 좋다.    /^A/g
A stringⓥ
an Aⓘ
/[^A]/g
A stringⓥ
an Aⓘ
$    문자열의 마지막
/t$/
eatⓥ
GREATⓘ
*    0번 이상 반복    /ab*d/g
adⓥ
abdⓥ
abdcdeabbbbdedbⓥ
abⓘ
axdⓘ
+    1번 이상 반복 ( = {1,} )    /ab+d/g
adⓘ
abdⓥ
abdcdeabbbbdedbⓥ
abⓘ
axdⓘ
?    0번 이나 1번    /e?le?/g
angelⓥ
angleⓥ
elementⓥ
/abc\-?d/g
abc-dⓥ
abcdⓥ
.    new line 을 제외한 모든 글자    /.n/g
nay, an apple is on the treeⓥ
nayⓘ
(x)    x를 체크하고 체크한 값을 변수로 저장    /(f..) (b..)/
foo barⓥ
1th :foo
2th :bar
(?:x)    x를 체크하고 체크한 값을 변수로 저장하지 않음    /(?:f..) (b..)/
foo barⓥ
1th :bar
bar fooⓘ
x|y    x 또는 y    /green|red/
green appleⓥ
red appleⓥ
yellow appleⓘ
x(?=y)    x후에 y가 나오고, x부분만 매칭되는 부분으로 간주    /blah(?=soft|hard)/
blahsoftⓥ
blahhardⓥ
blah softⓘ
/blah(?=soft).*/
blahsoftⓥ
blahhardⓘ
blah softⓘ
x(?!y)    x가 나오고 그 뒤에 y가 있으면 안 됨    /blah(?!hard)/
blahsoftⓥ
blahhardⓘ
blah softⓥ
{n}    앞에 지정한 것이 n개    /.{3}/
abⓘ
abcⓥ
abcdⓥ
홍길동ⓥ
{n,}    앞에 지정한 것이 n개 이상    /.{3,}/
abⓘ
abcⓥ
abcdⓥ
{n,m}    앞에 지정한 것이 n~m개    /.{3,5}/
abⓘ
abcⓥ
abcdⓥ
홍길동ⓥ
[xyz]    x나 y나 z. []안에는 얼마든지 쓸 수 있다.    /[abc]{2}/
abⓥ
abcⓥ
adbdⓘ
[x-z]    x에서 z까지    /[a-z]{4,}/g
She sells sea shells by the sea shore는 Very 어렵다!ⓥ
[^xyz]    x,y,z를 제외한 나머지 모든 것    /[^a-z]{2,}/g
I'm a good manⓥ
I am A good Manⓥ
[\b]    백스페이스. \b와 혼동하지 말것.    /[\b]/g
abcdⓥ
일반적인 String에서는 \b가 백스페이스를 의미한다.
\b    단어의 경계.[\b]와 혼동하지 말것.    /\bn[a-z]/g
I am not a boyⓥ
onlineⓘ
nopeⓥ
\B    \b 를 제외한 전부    /\Bn[a-z]/g
noondayⓥ
onlineⓥ
nopeⓘ
\cX    컨트롤X와 매칭. \cM은 컨트롤M과 매칭   
\d    숫자.[0-9]와 같음    /\d/g
7 eight 9ⓥ
123ⓘ
/^0[0-9]{2}/g
0120ⓥ
12011ⓘ
\D    \d 를 제외한 전부    /\D/g
7 eight 9ⓥ
12?3ⓘ
\f    form-feed   
\n    new line   
\r    carriage return   
\s    white space
ex>탭, 띄어쓰기, \n, \r    /k\s/g
koreaⓘ
blank isⓥ
blankⓘ
\S    \s 를 제외한 전부    /k\S/g
koreaⓥ
blank isⓘ
\t    탭   
\v    vertical tab   
\w    알파벳+숫자+_. [A-Za-z0-9_]와 동일    /\w/g
!@#$%^&*()+_-[]{}\|"':;,.<>?/ⓥ
        _가 <b>를 먹여도 별로 티가 안 난다.
\W    \w 빼고 전부    /\W/g
!@#$%^&*()+_-[]{}\|"':;,.<>?/ⓥ
\n    \n이 자연수일때, ()로 지정한 n번째 정규식    /(.{2})e tru\1 is out \1ere/
the truth is out there ...ⓥ
1th :th
(th)가 \1로 지정된다.
\xhh    hh는 hexacode,    /[\x21-\x40]/g
!@#$%^&*()poⓥ
Code table 보기
\uhhhh    hhhh는 hexacode,    /[\u3131-\u3163\uac00-\ud7a3]/g
나는 blah 직원입니다.ㅋㅋⓥ
코드 번호> 3131:ㄱ 3163:ㅣ ac00:가 d7a3:힣 (javascript, java)

4. 정규식 사용 예제

/^[0-9]/
09없다ⓥ
100점ⓥ
집이 10평ⓘ
/^\w+$/
blahsoftⓥ
blah(co)ⓘ
blah softⓘ
/^[a-zA-Z][\w\-]{4,11}$/
blah2010ⓥ
blah-2010!ⓘ
2010blahⓘ
ILikegoooooooooooooooooogleⓘ
/^[0-9]{2,3}-[0-9]{3,4}-[0-9]{4}/
02-6288-2114ⓥ
031-779-7114ⓥ
12-1234-5678ⓥ
02-6288-2114545ⓥ
02-0288-2114ⓥ
/^0\d{1,2}-[1-9]\d{2,3}-\d{4}$/
02-6288-2114ⓥ
031-779-7114ⓥ
12-1234-5678ⓘ
02-2123-12314545ⓘ
02-0288-2114ⓘ
/^[\.a-zA-Z0-9\-]+\.[a-zA-Z]{2,}/
r-d.blah.co.krⓥ
r-d.blah.co.kr입니다.ⓥ
blah..co.krⓥ
a.comⓥ
/^(?:[\w\-]{2,}\.)+[a-zA-Z]{2,}$/
r-d.blah.co.krⓥ
r-d.blah.co.kr입니다.ⓘ
blah..co.krⓘ
a.comⓘ
/^[_a-zA-Z0-9\-]+@[\._a-zA-Z0-9\-]+\.[a-zA-Z]{2,}/
abc@haha.co.krⓥ
abc@haha..co.krⓥ
hwang@a.comⓥ
/^[\w\-]+@(?:(?:[\w\-]{2,}\.)+[a-zA-Z]{2,})$/
abc@haha.co.krⓥ
abc@haha..co.krⓘ
hwang@a.comⓘ
/^([a-z]+):\/\/((?:[a-z\d\-]{2,}\.)+[a-z]{2,})(:\d{1,5})?(\/[^\?]*)?(\?.+)?$/i
http://www.blah.co.kr/main/index.jsp?var=valueⓥ
1th :http
2th :www.blah.co.kr
3th :undefined
4th :/main/index.jsp
5th :?var=value
http://www.blah.co.kr/main/index.jspⓥ
1th :http
2th :www.blah.co.kr
3th :undefined
4th :/main/index.jsp
5th :undefined
http://blah.co.kr/ⓥ
1th :http
2th :blah.co.kr
3th :undefined
4th :/
5th :undefined
http://blah.co.krⓥ
1th :http
2th :blah.co.kr
3th :undefined
4th :undefined
5th :undefined
http://blah.co.kr:8088/main/ⓥ
1th :http
2th :blah.co.kr
3th ::8088
4th :/main/
5th :undefined
/^[ㄱ-ㅣ가-힣]+$/
티맥스소프트ⓥ
ㅜㅜⓥ
ㅎㅎⓥ

5. Javascript 정규식 함수

함수    코드예제    코드설명
Array RegExp.exec (to be checked)   

var myRe=/d(b+)(d)/ig;
var myArray = myRe.exec("cdbBdbsbz");
/d(b+)(d)/gi

cdbBdbsbzⓥ
myArray.index =1 ; (처음으로 매칭되는 위치, 컴터가 늘 그렇듯 위치는 0번째부터 센다.)
myArray.input = cdbBdbsbz; (체크할 대상)
myArray[0] = dbBd;(검사에 통과한 부분)
myArray[1] = bB;(1번째 괄호에서 체크된 부분)
myArray[2] = d;(2번째 괄호에서 체크된 부분)

myRe.lastIndex =5 ; (다음번 체크를 하기위한 위치.)
myRe.ignoreCase = true; (/i 플래그 체크)
myRe.global = true; (/g 플래그 체크)
myRe.multiline = false; (/m 플래그 체크)

RegExp.$_ = cdbBdbsbz;(입력한 스트링)
RegExp.$1 = bB;(1번째 괄호에서 체크된 부분 )
boolean RegExp.test(to be checked)   

var myRe=/d(b+)(d)/ig;
var checked = myRe.test("cdbBdbsbz");
document.write("checked = " + checked +";<br>");
/d(b+)(d)/gi

cdbBdbsbzⓥ
실행결과: checked = true;
String RegExp.toString()   

var myRe=/d(b+)(d)/ig;
var str = myRe.toString();
document.write(str);
실행 결과: /d(b+)(d)/gi
String String.replace(pattern or string, to be replaced)   

var str = "abcdefe";
document.write(str.replace("e" , "f"));
실행 결과: abcdffe

e가 2번 있지만, 첫번째 인자가 정규식이 아니라 문자열일 경우는 첫번째 것만 바꾼다.

var str = "aba";
document.write(str.replace(/^a/ , "c"));
실행 결과: cba

var re = /(\w+)\s(\w+)/;
var str = "John Smith";
newstr = str.replace(re, "$2, $1");
document.write(newstr)
실행 결과: Smith, John

re에 의해서 찾아진 문자열 들은 re에서 ()로 표현된 순서대로 $1, $2와 같이 변수로 저장된다.

var re = /\s(?:http|https):\/\/\S*(?:\s|$)/g;
var str = "url is http://iilii.egloos.com/ !!\n";
str += "blah home: http://www.blah.co.kr";
newstr = str.replace(re, function (str,p1,offset,s) {
     return "<a href='" + str + "'>" + str + "</a>";
  }
).replace(/\n/, "<br>");
document.write(newstr);
url is http://iilii.egloos.com/ !!
blah home: http://www.blah.co.kr

str: 찾은 문자열
p1: ()에서 검색된 1번째 문자열. 마찬가지로 p2,p3 등도 가능
offset: str을 찾은 위치
s : 원본 문자열.
Array String.match(regular expression   

var str = "ABCdEFgHiJKL";
var myResult = str.match(/[a-z]/g );
for(var cnt = 0 ; cnt < myResult.length; cnt++){
    document.write(cnt +":" + myResult[cnt] +"<br>");
}

document.write("비교<br>");

var str = "ABCdEFgHiJKL";
var myResult = /[a-z]/g.exec(str);
for(var cnt = 0 ; cnt < myResult.length; cnt++){
    document.write(cnt +":" + myResult[cnt] +"<br>");
}
실행 결과:
0:d
1:g
2:i
비교
0:d

String.match(RegExp) =>g flag가 있어도 다 찾아낸다.
RegExp.exec(String) =>g flag가 있으면, 한 개만 찾고 끝낸다.
Array String.split([separator[, limit]])   

var str = "ABCdEFgHiJKL";
var myResult = str.split(/[a-z]/g , 3);
for(var cnt = 0 ; cnt < myResult.length; cnt++){
    document.write(cnt +":" + myResult[cnt] +"<br>");
}
실행 결과:
0:ABC
1:EF
2:H

주어진 문자열을 separator를 기준으로 limit 만큼 자른다.
6. 정규식으로 만든 유용한 Javascript 함수

String removeTags(input)

HTML tag부분을 없애준다

function removeTags(input) {
    return input.replace(/<[^>]+>/g, "");
};
example>

var str = "<b>blah</b> <i>soft</i>";
document.write(str +"<br>");
document.write(removeTags(str));
result>
blah soft
blah soft
String String.trim()

문자열의 앞뒤 공백을 없애준다.

String.prototype.trim = function() {
    return this.replace(/^\s+|\s+$/g, '');
};
example>

var str = "         untrimed string            ";
document.write("========" + str+ "==============<br>");
document.write("========" + str.trim() + "==============");
result>
======== untrimed string ==============
========untrimed string==============
String String.capitalize()

단어의 첫 글자를 대문자로 바꿔준다.

String.prototype.capitalize = function() {
    return this.replace(/\b([a-z])/g, function($1){
        return $1.toUpperCase();
    }) ; 
};
example>

var str = "korea first world best";
document.write(str.capitalize());
result>
Korea First World Best
String number_format(input)

입력된 숫자를 ,를 찍은 형태로 돌려준다

function number_format(input){
    var input = String(input);
    var reg = /(\-?\d+)(\d{3})($|\.\d+)/;
    if(reg.test(input)){
        return input.replace(reg, function(str, p1,p2,p3){
                return number_format(p1) + "," + p2 + "" + p3;
            }   
        );
    }else{
        return input;
    }
}
example>

document.write(number_format(1234562.12) + "<br>");
document.write(number_format("-9876543.21987")+ "<br>");
document.write(number_format("-123456789.12")+ "<br>");
result>
1,234,562.12
-9,876,543.21987
-123,456,789.12
7. Java 정규식 함수

 Pattern p = Pattern.compile("(a*)(b)");
Matcher m = p.matcher("aaaaab");
if (m.matches()) {
    for (int i = 0; i < m.groupCount() + 1; i++) {
        System.out.println(i + ":" + m.group(i));
    }
} else {
    System.out.println("not match!");
}

result>
0:aaaaab
1:aaaaa
2:b
0번째는 매칭된 부분.
String a = "I love her";
System.out.println(a.replaceAll("([A-Z])", "\"$1\""));

result>
"I" love her
자바도 $1을 쓸 수 있다.
Pattern p = Pattern.compile("cat");
Matcher m = p.matcher("one cat two cats in the yard");
StringBuffer sb = new StringBuffer();
while (m.find()) {
    m.appendReplacement(sb, "dog");
    System.out.println(sb.toString());
}
m.appendTail(sb);
System.out.println(sb.toString());

result>
one dog
one dog two dog
one dog two dogs in the yard

http://zmzm.tistory.com/302


sqlplus 에서 아래와 같은 방식으로 처리한다.

set echo off
set null @
set pages 0
set colsep ','
set trimspool on
set linesize 30000
set termout off

spool 파일명.csv;

select * from tablename ;

spool off;

ps. 근데 컬럼사이즈만큼 공백 생기는 건 어떻게 처리 안되나???

http://www.w3schools.com/jsref/jsref_tofixed.asp

var num = new Number(13.3714); document.write(num.toFixed()+"<br />"); document.write(num.toFixed(1)+"<br />"); document.write(num.toFixed(3)+"<br />"); document.write(num.toFixed(10)); >> 13 13.4 13.371 13.3714000000

소수점 자리수
천단위 콤마

double s = files[i].length(); s = s / 1024 / 1024; DecimalFormat df = new DecimalFormat("###.##"); String size = df.format(s) + "MB"; // 사이즈

###.## 에서 정수부분 #의 개수는 출력에 영향을 미치지 않음.
3개만 있어도 4자리 이상도 출력됨.

public static void main(String[] args) {     NumberFormat nf = NumberFormat.getNumberInstance();     System.out.println(nf.format(2500000));     System.out.println(nf.format(2500000.23));          NumberFormat pf = NumberFormat.getPercentInstance();     System.out.println(pf.format(1));     System.out.println(pf.format(0.25));     System.out.println(pf.format(0.2535));  } >>> 2,500,000 2,500,000.23 100% 25% 25%

var m_browser; var m_version; function detectBrowser() { // jQuery로 브라우저 타입/버젼 확인     if($.browser.msie) {         m_browser = "msie";     }     else if($.browser.safari) {         m_browser = "safari";     }     else if($.browser.opera) {         m_browser = "opera";     }     else if($.browser.mozilla) {         m_browser = "mozilla";     }     m_version = $.browser.version;     //alert(m_browser + " " + m_version); } function setElAttribute(el, attrName, attr) {     if(attrName == "style") {         // this.setAttribute("style", "background-color:yellow; font-weight:bolder;");         // IE6 DOESN'T WORK! Use this.style.setAttribute("cssText", "STYLE");         if(m_browser.indexOf("msie") == 0 && (m_version.indexOf("6.0") == 0 || m_version.indexOf("7.0") == 0)) {             el.style.setAttribute("cssText", attr);         }         else {             el.setAttribute("style", attr);         }     }     else {         el.setAttribute(attrName, attr);     } }

6.0만 style.setAttribute 하면 될 줄 알았는데, 7.0도 해야하더라....