DB (DataBase Systems)

Oracle, PostgreSQL, PostgreSQL for UNIX, PostgreSQL for Linux, PostgreSQL for Windows, PostgreSQL for Mac, MySQL, JDBC, OS, Web Server, Up

Oracle

Oracle
Oracle Japan, Products, Training, OTN, Tohoku
Oracle Technology Network
Oracle Technet
Oracle8 Linux
Install 2000.1
Install Tips 2003.2
Install2001.1
Oracle 9i Database for Linux インストールメモ
Oracleシステム要件
- OS
- Linux
Terms
- OC4J (Oracle9iAS Containers for J2EE)
- BC4J (Business Components for Java)
Oracle Books
Oracle Commands
- SQL
- Variables

Comparison between Oracle and PostgreSQL

Elements	Orcale	PostgreSQL
Integer Number	number(), integer, int, long	int
Real Number	real	float
Character	charcter, char() fixed 255bytes, nchar()	char
String	varchar2() variable 4000bytes, nchar()	text
Date	date	date
Logical	boolean	bool
Order	?	Serial
File SQL	@file	\i file

PostgreSQL
New Version 8.0.1 (Jan., 2005), 7.4.7(2004), 7.3.6 (Mar. 2003), 7.3.2 (Feb. 5, 2003), 7.2.4 (Jan. 31, 2003), 6.5.2 (Sep. 14, 1999)
- PostgresSQL Org.
- Japan PostgresSQL User Group
  - Manual
  - PostgreSQL Japanese Documents
  - psql commands
  - MultiByte -E EUC_JP
  - 7.4to8.0
  - 7.2to7.3
- PostgreSQL FAQ
- PostgreSQL mailing list in Japan
- PostgreSQL 6.5.3+Apache1.3.11+Apache-JServServ1.1 (Feb. 12, 2000)
  See Apache 1.3.11
- PostgreSQL
- PHP (Hypertext Preprocessor)
- PHP Japan (Hypertext Preprocessor, Japan)
- ODBC
- PostgreSQL JDBC Driver Homepage for 6.5.2 (Sep. 14, 2000), 7.0 JDK1.2.x & 1.3.x 1.1.x
- JDBC driver for PostgreSQL Japanese Patch for 6.5.2, 6.5.3
- PostgreSQL 6MB, FTP site (U. Aizu)
- PostgreSQL Win Binary
- PostgreSQL Binary
- PostgreSQL Startup scriptsby postgresql.org
- (long)statement.getLastOID() OID (2003.11)
PostgreSQL for UNIX
- su
- gunzip postgresql-7.3.6.tar.gz
- tar xvf postgresql-7.3.6.tar
- cd postgresql-7.3.6/src
- mkdir /usr/local/pgsql
- chown postgres:postgres /usr/local/pgsql
- su postgres
- ./configure
- make all
  - (gcc, make)
  - (Makefile.global)
  - (Makefile.custom)
- make check
- (Install)
  - make install
  - ls -al /usr/local/pgsql
- (Manual)
  - cd ../doc
  - make install
- vi ~/.cshrc
```
set path = ($path /usr/local/pgsql/bin)
setenv POSTGRES_HOME /usr/local/pgsql
setenv PGLIB $POSTGRES_HOME/lib
setenv PGDATA $POSTGRES_HOME/data
setenv MANPATH "$MANPATH":$POSTGRES_HOME/man
setenv LD_LIBRARY_PATH "$PGLIB"
```
- (Start)
  - /usr/local/pgsql/bin/initdb
  - /usr/local/pgsql/bin/postmaster -S -i
  - /usr/local/pgsql/bin/psql -l
  - /usr/local/pgsql/bin/createdb testdb
  - /usr/local/pgsql/bin/psql testdb
- (Configuration)
  - vi /usr/local/pgsql/data/pg_hba.conf
- (Test)
  - cd src/test/regress
  - make all runtest
- (Error)
  - postmaster -S -i
```
IpcMemoryCreate: shmget failed (Invalid argument) key=5432001, size=1115136, permission=600
This type of error is usually caused by an improper
shared memory or System V IPC semaphore configuration.
For more information, see the FAQ and platform-specific
FAQ's in the source directory pgsql/doc or on our
web site at http://www.postgresql.org.
FATAL 1:  ShmemCreate: cannot create region
```
  - vi doc/FAQ_Solaris
  - Sun shared memory

PostgreSQL for RedHat
Please read the following directory structure before instration.

Directory Structure

OS	Orignail RedHat RPM	Manual Install
path	export PATH=/usr/bin:$PATH	export PATH=/usr/local/pgsql/bin:$PATH
HOME	export POSTGRES_HOME=/var/lib/pgsql	export POSTGRES_HOME=/usr/local/pgsql
PGDATA	export PGDATA=/var/lib/pgsql/data	export PGDATA=/usr/local/pgsql/data export PGDATA=/var/lib/pgsql/data
PGLIB	export PGLIB=/var/lib/pgsql/lib	export PGLIB=/usr/local/pgsql/lib
MANPATH	export MANPATH="$MANPATH"	export MANPATH="$MANPATH":$POSTGRES_HOME/man
LIB	export LD_LIBRARY_PATH="$PGLIB"	export LD_LIBRARY_PATH="$PGLIB"
start script	/etc/init.d/postgresql	cp $Install/contrib/startup-scripts/linux /etc/init.d/postgresql /etc/init.d/postgresql
CONF	/var/lib/pgsql/data/*.conf	/usr/local/pgsql/data/.conf /var/lib/pgsql/data/.conf
LOG	/var/lib/pgsql/data/*.log	/usr/local/pgsql/data/.log /var/lib/pgsql/data/.log

PostgreSQL Make and Install
- su
- mkdir /usr/local/pgsql
- chown postgres:postgres /usr/local/pgsql
- su postgres
- cd /usr/local/src
- gunzip postgresql-7.3.6.tar.gz
- tar xvf postgresql-7.3.6.tar
- cd /usr/local/src/postgresql-7.3.6
- ./configure
- make all
- make check
- make install
- ls -alF /usr/local/pgsql
Delete old PostgreSQL RPM
- #su
- #rpm -qa | grep postgresql
- #rpm -e postgresql-server-7.2.2-1
- #rpm -e postgresql-7.2.2-1
- #rpm -e postgresql-libs-7.2.2-1
  Error: Failed dependencies: libpq.so.2 is needed by (installed) perl-DBD-Pg-1.13-5
Readd postgres user
- #su
- grep postgres /etc/passwd
- grep postgres /etc/group
- #adduser -u 26 -g 26 -d /var/lib/pgsql -c "PostgreSQL Server" postgres
Startup Script
- su
- cp /usr/local/src/postgresql-7.3.6/contrib/start-scripts/linux /etc/init.d/postgresql
- chmod 755 /etc/init.d/postgresql
- vi /etc/init.d/postgresql
  
  35: #PGDATA='/usr/local/pgsql/data'
  36: PGDATA='/var/lib/pgsql/data'
- diff /etc/init.d/postgresql /usr/local/src/postgresql-7.3.6/contrib/start-scripts/linux
- # = comment line
- #rm -f /etc/rc.d/rc0.d/K15postgresql
- #rm -f /etc/rc.d/rc1.d/K15postgresql
- #rm -f /etc/rc.d/rc2.d/K15postgresql
- #rm -f /etc/rc.d/rc3.d/K15postgresql
- #rm -f /etc/rc.d/rc4.d/K15postgresql
- #rm -f /etc/rc.d/rc5.d/K15postgresql
- #rm -f /etc/rc.d/rc6.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc0.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc1.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc2.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc3.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc4.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc5.d/K15postgresql
- #ln -s /etc/init.d/postgresql /etc/rc.d/rc6.d/K15postgresql
- /sbin/chkconfig --list postgresql
- #/sbin/chkconfig --add postgresql
- #/sbin/chkconfig postgresql on
initdb
- su postgres
- cd /var/lib/pgsql
- ls -al
- \rm -r data
- export PGDATA=/var/lib/pgsql/data
- env | grep PGDATA
- /usr/local/pgsql/bin/initdb
- cd /var/lib/pgsql/data
- touch serverlog
- ls -alF
Start and Stop by Manual
- su
- sh /etc/init.d/postgresql start
- sh /etc/init.d/postgresql restart
- su postgres
- /usr/local/pgsql/bin/psql -l
- /usr/local/pgsql/bin/createuser user1
  Shall the new user be allowed to create databases? (y/n) y
  Shall the new user be allowed to create more new users? (y/n) n
  CREATE USER
- su user1
- /usr/local/pgsql/bin/psql -l
- /usr/local/pgsql/bin/createdb -E UTF-8 mydb
- /usr/local/pgsql/bin/createdb -E EUC_JP mydb2
- /usr/local/pgsql/bin/psql -l
```
-----------+----------+------------------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 mydb      | user1    | UNICODE
 mydb2     | user1    | EUC_JP
```
- su
- sh /etc/init.d/postgresql stop

FAQ of PostgresSQL for Linux

Q: How to setup .bashrc for PostgreSQL
A: vi ~/.bashrc

export PATH=/usr/local/pgsql/bin:$PATH
export POSTGRES_HOME=/usr/local/pgsql
export PGLIB=$POSTGRES_HOME/lib
export PGDATA=/var/lib/pgsql/data
#export MANPATH=$MANPATH:$POSTGRES_HOME/man
export LD_LIBRARY_PATH=$PGLIB

Q: version
A: psql db
select version();
PostgreSQL 7.3.6
\q
Q: createuserコマンドで登録したユーザ名を表示するには？
A: psql template1
\du (Display User)コマンドで表示できます。
データベースユーザ一覧
ユーザ名: | ユーザ ID | 属性
------------+-----------+----------------------------------
postgres | 1 | スーパーユーザ, データベース生成
user1 | 100 | データベース生成
(2 行)
\q
Access from Other Hosts
- /var/lib/pgsql/data/pg_hba.conf
  
  local all
  
  trust
  
  #host all 127.0.0.1 255.255.255.255 md5 user
  
  #host dbname 123.234.123.0 255.255.255.128 trust
- /var/lib/pgsql/data/postgresql.conf
  tcpip_socket = true
Backup
- su postgres
- vi /var/lib/pgsql/data/pg_hba.conf
  local all 　　　　　　　　trust
- pg_dump mydb -f mydb.out
  後でcreatedb必要、別の名前にできる。
- pg_dumpall > mydb.out
  後でcreatedb不要、別の名前にできない。
  (すべての情報含む、内部でpg_dumpを呼び出している。)
- Synopsis
  pg_dump [-a | -s] [-b] [-c] [-C] [-d | -D] [-f file] [-F format] [-i] [-n | -N] [-o] [-O] [-R] [-S] [-t table] [-v] [-x] [-X keyword] [-Z 0...9] [-h host] [-p port] [-U username] [-W] dbname
Restore
- su postgres
- psql -l
- createdb newmydb
- psql -e -d newmydb -f mydb.out
  -e = Echo on
- pg_restore -d newmydb mydb.out
  pg_restore [ -a ] [ -c ] [ -C ] [ -d dbname ] [ -f output-file ] [ -F format ] [ -i index ] [ -l ] [ -L contents-file ] [ -N | -o | -r ] [ -O ] [ -P function-name ] [ -R ] [ -s ] [ -S ] [ -t table ] [ -T trigger ] [ -v ] [ -x ] [ -X keyword] [ -h host ] [ -p port ] [ -U username ] [ -W ] [ archive-file ]

PostgreSQL for Windows
- Add postgres user
  - Task bar
  - Start
  - Setup
  - Control Panel
  - User & Passwd
  - Add user
    - User name
    - Passwd
    - User group
  - grep postgres /etc/passwd
    postgres:......
  - /usr/bin/mkpasswd | grep posrgres/usr/bin/mkpasswd -l > /etc/passwd
  - /usr/bin/mkgroup -l > /etc/group
- bash
  - ftp cygwin
    - setup
  - setup for postgreSQL
    - vi ~/.bashrc
      
      export CYGWIN=server
      export PATH=$PATH:/usr/sbin
      export PGLIB=/usr/local/pgsql/lib
      export PGDATA=/usr/local/pgsql/data
      export PGHOST=127.0.0.1
    - source ~/.bashrc
- PostgreSQL install from binary file
  - download binary file from 日本PostgreSQLユーザ会
  - unzip postgresql-8.2.4-1-ja.zip
  - postgresql-8.2-ja.msi
    Install
    1. C:\Program Files\PostgreSQL\8.2\
    2. Service configuration
    3. Initialize database cluster
    4. PostgreSQL is now ready to be installed
    5. Installation complete!
    6. postmaster.exe
      postgres.exe
      pg_ctl.exe
- PostgreSQL install from source file
  - ftp postgres source file
  - Install
    - ./configure
      ./configure --enable-multibyte=EUC_JP
    - cat configure.log
    - make > make.log 2>&1
    - make install > install.log 2>&1
  - Setup for postgres
    1. /usr/bin/whoami
      OK: postgres
    2. /usr/bin/uname -a
    3. /bin/cygcheck -cd cygwin postgresql
    4. /usr/sbin/cygserver --version
    5. echo $CYGWIN
      OK: server
      NG: Windows: Control Panel - System - Details - Environment Variables - Append - CYGWIN = server
    6. /bin/cygserver-config
      yes
      yes
    7. ls -al /etc/cygserver.conf
    8. /usr/sbin/cygserver &
      &: Background Job
    9. jobs
    10. ps aux | grep server
      cygserver
    11. /bin/ipcs
      - NG:
        Bad system call
        Please check above setting values.
      - OK:
        Message Queues: T ID KEY MODE OWNER GROUP Shared Memory: T ID KEY MODE OWNER GROUP Semaphores: T ID KEY MODE OWNER GROUP
    12. echo $PGDATA
      OK: /usr/local/pgsql/data or /var/lib/pgsql/data
    13. /usr/bin/initdb
      
      NG: initdb: removing data directory "/var/lib/pgsql/data"
      Please check above setting values. NG: /usr/bin/postgres could not locate postgres execution.
      
      ls -l /usr/bin/postgres
      chmod 755 /usr/bin/postgres
    14. /usr/sbin/postmaster -i &
    15. jobs
    16. /usr/bin/psql -l
```
-----------+----------+------------------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
```
    17. /usr/bin/createuser administrator
      Yes
      Yes
    18. /usr/bin/psql template1
    19. select version();
      PostfreSQL 7.4.5
    20. \du
```
User name     | User ID  |  Attrubutes
--------------+----------+------------------
administrator |     100  |  superuser, create database
postgres      |       1  |  superuser, create database
```
    21. \?
    22. \q
    23. :
    24. /usr/bin/pg_ctl stop
    25. exit
  - Setup for user(administrator)
    1. /usr/bin/createdb -E EUC_JP newjdb
    2. /usr/bin/createdb -E UTF-8 newgdb
    3. /usr/bin/psql -l
    4. /usr/bin/psql newdb
    5. \dt
    6. \set
    7. \encoding
      EUC_JP
    8. \encoding SJIS
    9. set client_encoding to 'SJIS';
    10. show client_encoding;
      SJIS
      after 7.4
    11. select * from post;
    12. :
    13. \q
    14. jobs
    15. exit
- FAQ of PostgresSQL for Windows
  - Q: psqlでselect文の実行結果の文字化けを回避できますか？
    A: 上記の例のように、\encoding SJISを使用してください。
  - Q: destroydbはコマンドは無いのですか？
    A: dropdbコマンドに変更になりました。
  - Q: psqlの起動時の表示設定を教えてください。
    A: ~/.psqlrcの設定例
    
    select version();
  - Q: ' 文字の記述方法を教えてください。
    A: 以下の例の用の様に、''とします。
    select * from tbl where message = 'It''s mine.';
  - Q: _, % 文字の検索方法を教えてください。
    A: select * from tbl where name like '%\\_%';
    select * from tbl where name like '%\\%%';
    select * from tbl where name like '%=_%' escape '=';
    select * from tbl where name like '%=%%' escape '=';
- FAQ of PostgresSQL for Windows2000
  - Q: Windows2000でPostgresSQLは使用できますか？
    A: はいできます。ただし、postgresユーザを作成し、上記の手順に従って設定してください。
  - Q: FATAL 1: SetUserId: user "administrator" is not in "pg_shadow"
    A: postgresユーザで"createuser administrator"を実行してください。
  - Q: コマンドに、-h localhostを入れない方法はありませんか？
    A: .bashrcにPGHOSTの設定をしてください。
PostgreSQL for Mac OS
1. Apple Developer Connection (ADC) Join (Free)
2. Mac Dev Center login, download and install
  - "Xcode 4.3.2 for Lion" and "Command Line Tools" (for 10.7, Apr., 2012).
  - "Xcode 3.2.6 and IOS SDK 4.3 for Snow Leopard" (for 10.6, Apr., 2012).
3. Download and Install "Mac Ports" from http://www.macports.org
4. export PATH=$PATH:/opt/local/bin
5. which port
  /opt/local/bin
6. sudo /opt/local/bin/port list postgresql*
  postgresql91-server @9.1.3 databases/postgresql91-server
7. sudo /opt/local/bin/port install postgresql91-server (Get the latest version, Apr. 2012)
8. sudo cat /etc/passwd | grep postgres
  _posrgres
9. sudo finger postgres
  Login: postgres Name: PostgreSQL-91 Server
  Directory: /opt/local/var/db/postgresql91 Shell: /bin/sh
10. sudo cat /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/org.macports.postgresql91-server.plist
11. sudo cat /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper | grep postgresql
  /opt/local/lib/postgresql91/bin/
  /opt/local/var/db/postgresql91/defaultdb
12. sudo /bin/launchctl load -w /Library/LaunchDaemons/org.macports.postgresql91-server.plist
13. ~~sudo /bin/launchctl unload -w /Library/LaunchDaemons/org.macports.postgresql91-server.plist~~
14. sudo /bin/launchctl list | grep postgresql
  org.macports.postgresql91-server
15. sudo ls -al /opt/local/lib/postgresql91/bin
16. sudo mkdir -p /opt/local/var/db/postgresql91/defaultdb
17. sudo chown postgres:postgres /opt/local/var/db/postgresql91/defaultdb
18. sudo chown postgres:postgres /opt/local/var/db/postgresql91
19. sudo touch /opt/local/var/db/postgresql91/postgres.log
20. sudo chown postgres:postgres /opt/local/var/db/postgresql91/postgres.log
21. sudo ls -al /opt/local/var/db/postgresql91
22. sudo su postgres -c '/opt/local/lib/postgresql91/bin/initdb -D /opt/local/var/db/postgresql91/defaultdb'
  Success. You can now start the database server
23. sudo ls -al /opt/local/var/db/postgresql91/defaultdb
  pg_hba.conf
  postgresql.conf
  postmaster.pid
24. sudo su postgres -c '/opt/local/lib/postgresql91/bin/pg_ctl -D /opt/local/var/db/postgresql91/defaultdb -l /opt/local/var/db/postgresql91/postgres.log start'
  server starting
25. sudo su postgres -c '/opt/local/lib/postgresql91/bin/pg_ctl -D /opt/local/var/db/postgresql91/defaultdb status'
  pg_ctl: server is running
26. ps aux | grep postgres
27. sudo su postgres -c '/opt/local/lib/postgresql91/bin/createuser -P myname'
  password setting for "myname"
28. sudo su postgres -c '/opt/local/lib/postgresql91/bin/createdb -U myname -E UTF-8 mydb'
29. sudo su postgres -c '/opt/local/lib/postgresql91/bin/psql -l'
```
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
```
30. sudo su postgres -c '/opt/local/lib/postgresql91/bin/psql -h localhost -l'
```
   Name    |  Owner   | Encoding |
-----------+----------+----------+
 mydb      | myname   | UTF8     |
 postgres  | postgres | UTF8     |
 template0 | postgres | UTF8     |
 template1 | postgres | UTF8     |
```
31. sudo su postgres -c '/opt/local/lib/postgresql91/bin/pg_ctl -D /opt/local/var/db/postgresql91/defaultdb stop'
  server stopped
32. reboot
33. sudo su postgres -c '/opt/local/lib/postgresql91/bin/pg_ctl -D /opt/local/var/db/postgresql91/defaultdb status'
  pg_ctl: server is running
34. sudo su postgres -c '/opt/local/lib/postgresql91/bin/psql -h localhost -l'
MySQL
1. MySQL
2. MySQL Group
3. MySQL Manual
4. MySQL 5.1
5. JDBC Connector/J 5.1
6. Connector/J for J2EE, Tomcat and JBoss
7. MySQL Quick Manual
8. Manual Start & Stop
  /sbin/service mysqld start
  /sbin/service mysqld stop
9. Automatic Start & Stop
  /sbin/chkconfig --list mysqld
  mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
10. /etc/my.cnf
```
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8
:
[client]
default-character-set=utf8
```
11. Root Password
  - Windows
    - install wizard
12. mysql [--default-character-set=utf8] -u root -p
  password: yourpasswd
  1. help; (\h)
  2. show character set;
    utf8: UTF-8
    ujis: EUC-JP
    sjis: Shift-JIS
  3. status; (\s)
```
Server version:         5.0.67
Server characterset:    latin1(utf8 by /etc/my.cnf)
Db     characterset:    latin1(utf8 by /etc/my.cnf)
Client characterset:    latin1(utf8 by /etc/my.cnf)
Conn.  characterset:    latin1(utf8 by /etc/my.cnf)
```
  4. show status;
  5. use mysql;
  6. select host,user,password from user;
  7. create database mydb;
  8. grant all on mydb.* to mydb@localhost identified by 'mypass';
  9. use mydb;
  10. show tables;
  11. desc[ribe] mytbl;
  12. create table mytbl (id int, name varchar(10));
  13. insert into mytbl values (1, 'user');
  14. insert into mytbl values (2, 'my');
  15. select * from mytbl;
  16. delete from mytbl where id=1;
  17. select * from mytbl;
  18. source myoption.sql
  19. --drop table mytbl;
  20. --drop database mydb;
  21. exit; (\q)
13. mysqladmin shutdown
JDBC
- PostgreSQL JDBC Drivers
- Download JDBC Drivers
  - JDBC1 for PostgreSQL7.3 and JDK 1.1
  - JDBC2 for PostgreSQL7.3 and JDK 1.2, 1.3
  - JDBC3 for PostgreSQL7.3 and JDK 1.4
- cp pg73jdbc3.jar /usr/java/j2sdk1.4.1_03/lib/postgresql.jar

Interface

DB	Oracle	PostgreSQL	MySQL
shell command	sqlplus	psql	mysql
all tables	?	\dt	show tables;
table	desc tbl	\d tbl	desc tbl;
Password			SET PASSWORD=PASSWORD('new_password')
file	@file	\i file	source file.sql
sequence	id number(15) create seaquence	id serial not null	id integer NOT NULL AUTO_INCREMENT PRIMARY KEY
table	?	\copy tbl_name from '/path/data.csv' using delimiters ',';	load data local infile "/path/data.csv" into table tbl_name fields terminated by '\t'; NULL='\N'
csv			select * from mytbl into outfile "/tmp/dump.csv" fields terminated by '\t';
end	exit quit	\q	exit

dump		pg_dump mydb -f mydb.out	mysqldump file.sql

SQL
- Java SQL
- SQL connection
Web Server and Servlet
Apache and Tomcat
UNIX データベース入門稚内北星学園短期大学丸山先生
UNIX PostgreSQL
RDB SQL
Sequrity
- SQL Injection

local	all			trust
#host	all	127.0.0.1	255.255.255.255	md5	user
#host	dbname	123.234.123.0	255.255.255.128	trust