PostgreSQL 9.0.4 (source code) lanjutan

E. Login menggunakan Password

1. Membuat password di PostgreSQL :
   template1=# ALTER USER septu23 with password 'septu';
   ALTER ROLE
   
   
2. Merubah configurasi :
   septu23@arseptu:~$ vim.tiny /home/septu23/datapg/pg_hba.conf
   # TYPE DATABASE USER CIDR-ADDRESS METHOD
   # "local" is for Unix domain socket connections only
   local all all trust
   # IPv4 local connections:
   host all all 127.0.0.1/32 trust
   # IPv6 local connections:
   host all all ::1/128 trust
   - ubah method trust menjadi password 
   
   
   # TYPE DATABASE USER CIDR-ADDRESS METHOD
   # "local" is for Unix domain socket connections only
   local all all password
   # IPv4 local connections:
   host all all 127.0.0.1/32 password
   # IPv6 local connections:
   host all all ::1/128 password
   
   
3. Mendaftarkan direktori bin ke PATH secara temporary :
   septu23@arseptu:~$ PATH=$PATH:/home/septu23/pg90/bin/
   - direktori bin berisikan file shell scrpit seperti : startpg.sh, restartpg.sh, stoppg.sh dan statuspg.sh yang guna nya untuk menjalankan, mematikan, dan melihat status pada database PostgreSQL
   
   
4. Mengexport PATH yang telah kita daftarkan :
   septu23@arseptu:~$ export PATH 
   
   
5. Restart daemon PostgreSQL :
   septu23@arseptu:~$ restartpg.sh
   - perintah ini harus selalu dilakukan setiap kali merubah configurasi pada database PostgreSQL

F. Membuat database

1. Membuat user baru pada database :
   septu23@arseptu:~$ createuser rahman -P -U septu23 
   
   
2. Membuat database dengan user :
   septu23@arseptu:~$ createdb siventik -U rahman  
3. Masuk ke database :
   septu23@arseptu:~$ psql siventik -U rahman
   
   
   Password:
   psql (9.0.4)
   Type "help" for help.
   siventik=>
   
   
4. Melihat list data base yang terdaftar di PostgreSQL :
   siventik=> \l;
   List of databases
   Name | Owner | Encoding | Collation | Ctype | Access privileges
   -----------+---------+----------+-------------+------------+---------------------
   postgres | septu23 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
   template0 | septu23 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |=c/septu23 +
   | | | | |septu23=CTc/septu23
   template1 | septu23 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |=c/septu23 +
   | | | | |septu23=CTc/septu23
   siventik | septu23 | UTF8 | en_US.UTF-8 | en.US.UTF-8 |=c/septu23 +
   septu23=CTc/septu23
   (4 rows) 
    
5. Membuat table role :
   siventik=> create table role(
   idrole serial primary key,
   jenis varchar(15) not null); 
   
   
6. Melihat deskripsi table role :
   siventik=> \d role;
    
   Table "public.role"
   Column | Type | Modifiers
   ----------+--------------------+-------------------------------------------------------
   idrole | integer | not null default nextval('role_idrole_seq'::regclass)
   jenis | character varying(15) | not null
   Indexes:
   "role_pkey" PRIMARY KEY, btree (idrole)
   Referenced by:
   TABLE "member" CONSTRAINT "member_role_fkey" FOREIGN KEY (role) REFERENCES role(idrole)
   
   
7. Memasukan data ke table role :
   siventik=> insert into role (jenis) values ('admin'),('operator');

8. Melihat data dari table role :
   siventik=> select * from role;
   idrole | jenis
   --------+----------
   1 | admin
   2 | operator
   (2 rows)

9. Membuat table member :
   siventik=> create table member(
   idmember serial primary key,
   fullname varchar(30) not null,
   email varchar(30) not null,
   pass varchar(32) not null,
   role int references role,
   join_date date);

10. Melihat deskripsi dari table member :
    siventik=> \d member;
    
    
    Table "public.member"
    Column | Type | Modifiers
    -----------+-----------------------+-----------------------------------------------------------
    idmember | integer | not null default nextval('member_idmember_seq'::regclass)
    fullname | character varying(30) | not null
    email | character varying(30) | not null
    pass | character varying(32) | not null
    role | integer |
    join_date | date |
    Indexes:
    "member_pkey" PRIMARY KEY, btree (idmember)
    Foreign-key constraints:
    "member_role_fkey" FOREIGN KEY (role) REFERENCES role(idrole)
    
    
11. Memasukan data ke table member :
    siventik=> insert into member (fullname,email,pass,role) values
    ('septu23','abd.rahmanseptu@yahoo.com',md5('septu'),2); 
    
    
12. Melihat data dari table member :
    siventik=> select * from member;
    
    
    idmember | fullname | email | pass | role | join_date
    ----------+----------+-----------------------------+----------------------------------+------+------------
    1 | septu23 | abd.rahmanseptu@yahoo.com | d9bc8966b92549c8e470ae96860a14f5 | 2 |
     
13. Mengupdate field :
    siventik=> update member set join_date = '2011-10-06' where idmember=1;
    idmember | fullname | email | pass | role | join_date
    ---------+----------+-----------------------------+----------------------------------+------+------------
    1 | septu23 | abd.rahmanseptu@yahoo.com | d9bc8966b92549c8e470ae96860a14f5 | 2 | 2011-10-06
    
    
    siventik=> update member set role = '1' where idmember=1;
    idmember | fullname | email | pass | role | join_date
    ---------+----------+-----------------------------+----------------------------------+------+------------
    1 | septu23 | abd.rahmanseptu@yahoo.com | d9bc8966b92549c8e470ae96860a14f5 | 1 | 2011-10-06