ASSIGNMENT 2 (Draft) : Security of Inventory Transaction Process

GROUP # 6

    1) นายภุมมิฑล ไชยเชิดเกียรติ      56-010116-2131-8
    2) นายนิติธร บุญภักดี                  56-010126-2003-0
    3) นางสาวอรพรรณ เทียมสระคู      56-010126-2006-4
    4) นาวสาวปนัดดา อ่อนพลับ          56-010126-3018-3  (G#5)

สไลด์เนื้อหาการพรีเซนอยู่ที่ http://db5630183.blogspot.com/2016/05/assignment-2-security-of-inventory.html

PostgreSQL : Present 20 April 2016

Present 20 April 2016 

about PostgeSQL

สรุปจากการทดลอง

ใส่ข้อมูลตารางละ 20 ล้านrecord

Size (เรียงลำดับจากขนาดไฟล์ใหญ่สุดไปเล็กสุด)

MySQL > PostgeSQL > Sqlite

_________________________________________________________________

Time (เรียงลำดับจากใช้เวลามากสุดไปน้อยสุด)

Sqlite > MySQL > PostgeSQL 

_________________________________________________________________

https://drive.google.com/file/d/0B37DLu0T26KXLVF2dTZwRFZlTGc/view

NORMAL FORM

Find example 1NF, 2NF, 3NF

 


https://drive.google.com/file/d/0BznlD1uGWW-VSTRla3VmLUhEWW8/view

PostgreSQL : Finish inserting !!! (compare with Sqlite and MySQL)

PostgreSQL compare with Sqlite and MySQL

          เป็นการใส่ข้อมูลเกี่ยวกับคลังเก็บสินค้าที่จะส่งให้ลูกค้า โดยใส่ข้อมูลตารางละ 20 ล้านrecord เท่ากันทั้ง 3 โปรแกรม 
          Sqlite และ MySQL ทำงานบน Windows 7 และ PostgreSQL ทำงานบน Ubuntu

Size

          Sqlite (9.34 GB)

          MySQL (20.9 GB)

          PostgreSQL (17GB)

ภาพแสดงขนาดของ database ในโปรแกรม PostgreSQL

          การเช็คขนาดของ database ทำได้โดยการใช้คำสั่ง \l+ ชื่อdb  ในที่นี้คือ \l+ invtrans 

 _________________________________________________________________

select data from a database

Select ครั้งที่ 1
          เชื่อมสองตารางเข้าด้วยกัน โดยใช้ord_idเป็นเงื่อนไขให้สองตารางเชื่อมกัน คือ ตาราง inv_trans และ order_item ที่มีเลข order เท่ากันจะนำมาเชื่อมกัน เพื่อให้ทราบว่า สินค้าที่จะต้องมีการขนส่งเป็นสินค้าชนิดใด และเลือกมาแต่ข้อมูลที่มีเลข order เท่ากับ 12300000

select i.*,o.ty_group from inv_trans i inner join order_item o
on i.ord_id=o.ord_id and o.ord_id='12300000';


          Sqlite 
                      ใช้เวลา 7 min 52.2 sec
          
          MySQL 
                      ครั้งแรกใช้เวลา 3 min 33.58sec
                      ครั้งที่ 2 ใช้เวลา 39.76 sec
                      ครั้งที่ 3 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 2 ไม่มาก
                      
          PostgreSQL
                      ครั้งแรกใช้เวลา 2 min 9.74sec
                      ครั้งที่ 2 ใช้เวลา 12.2 sec
                      ครั้งที่ 3 ใช้เวลา 5.97 sec
                      ครั้งที่ 4 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 3 ไม่มาก  

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 2 min  9.74 sec

ภาพแสดงการ select ครั้งที่ 2 ใช้เวลา 12.2 sec

ภาพแสดงการ select ครั้งที่ 3 ใช้เวลา 5.97 sec

          สรุป  ถ้าเทียบในครั้งแรก PostgreSQL ใช้เวลาในการ select data น้อยที่สุด รองลงมาคือ MySQL และ Sqlite ตามลำดับ

************************************************************************

Select ครั้งที่ 2
          เชื่อมสองตารางเข้าด้วยกัน โดยใช้ trans_id เป็นเงื่อนไขให้สองตารางเชื่อมกัน คือ ตาราง inv_trans และ tr_item ที่มีเลข trans_id เท่ากันจะนำมาเชื่อมกัน เพื่อให้ทราบว่า ตำแหน่งที่ตั้งของสินค้าและ item_id ของสินค้าที่จำต้องส่งไปยังลูกค้า โดยกำหนดให้แสดงแต่ข้อมูลที่มีเลข order เป็น12300000

select i.*,t.loc_id,t.item_id from inv_trans i inner join tr_item t
on i.trans_id=t.trans_id and i.ord_id='12300000';


          Sqlite 
                      ใช้เวลา 5 min 52.79 sec
          
          MySQL 
                      ครั้งแรกใช้เวลา 6 min 55.73 sec
                      ครั้งที่ 2 ใช้เวลา 2 min 30.46 sec
                      ครั้งที่ 3 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 2 ไม่มาก
                      
          PostgreSQL
                      ครั้งแรกใช้เวลา 26.997 sec
                      ครั้งที่ 2 ใช้เวลา 25.31 sec
                      ครั้งที่ 3 ใช้เวลา 6.484 sec
                      ครั้งที่ 4 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 3 ไม่มาก 

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 26.997 sec

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 25.31 sec

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 6.484 sec

          สรุป  ถ้าเทียบในครั้งแรก PostgreSQL ใช้เวลาในการ select data น้อยที่สุด รองลงมาคือ Sqlite และ MySQL ตามลำดับ

PostgreSQL : Inserting data

Inserting data

          การใส่ข้อมูลในDatabaseที่ได้สร้างไว้ โดยการใส่ข้อมูลทีละตาราง เลือกใส่ข้อมูลที่ไม่มี FK ก่อน เพื่อให้ไม่เกิดการ error เมื่อมีการอ้างอิงไปยังตาราง

โดยลำดับการใส่ข้อมูลในตารางทั้ง 10 ตารางจะทำดังนี้

1. inv_type
2. orders
3. location
4. users
5. lab_assistant
6. item
7. storage
8. order_item
9. inv_trans
10. tr_item

ปัญหาที่พบ

          ในการใส่ข้อมูลคิดว่าสามารถใช้code ข้อความ เดียวกับ การใส่ข้อมูลใน MySQL หรือใน Sqlite ได้
นั่นคือ
    exec_str = 'insert into inv_type values ("'   +str(i) + ' ");' 
    c.execute(exec_str)


          แต่เมื่อทำการใช้ code ดังตัวอย่างข้างบน จะขึ้น error ว่า "psycopg2.ProgrammingError: column "1" does not exist"

psycopg2.ProgrammingError: column "1" does not exist

จากนั้นก็ได้แก้ไขเป็น

     c.execute("insert into inv_type values (%s);",(str(i)))

อ้างอิง : http://stackoverflow.com/questions/4113910/python-psycogp2-inserting-into-postgresql-help

          ตามที่ได้มีการแนะนำไว้ในเว็บด้านบน errorเก่าไม่ได้แจ้งเตือนแล้ว แต่ได้มีการเกิด error ใหม่ขึ้น นั่นคือ

"TypeError: not all arguments converted during string formatting python "

TypeError: not all arguments converted during string formatting python

 จึงได้ทำการศึกษาเพิ่มเติมแล้วได้ทำการเปลี่ยน code ใหม่เป็น

    c.execute("insert into inv_type values ('%s');"%(str(i)))

อ้างอิง : http://stackoverflow.com/questions/18053500/typeerror-not-all-arguments-converted-during-string-formatting-python

การเปลี่ยน password ของ user  postgres

ภาพแสดงการเปลี่ยน password

          ซึ่งได้ทำการเปลี่ยน password เป็น sam55166 เนื่องจากตอนเชื่อมต่อ db ใน user postgresจะต้องใส่ password จึงต้องทำการเปลี่ยนก่อน

อ้างอิง : http://serverfault.com/questions/110154/whats-the-default-superuser-username-password-for-postgres-after-a-new-install

Code ที่ใช้ในการใส่ข้อมูล

          ใน MySQL และ Sqlite ได้ทำการใส่ข้อมูลตารางละ 20 ล้านrecord ใน PostgreSQL จึงได้ทำการใส่ตารางละ 20 ล้านrecordเท่ากัน เพื่อใช้ในการเปรียบเทียบ

http://pastie.org/10794705

อ้างอิงการใส่ข้อมูลจาก
1) http://www.tutorialspoint.com/postgresql/postgresql_python.htm
2) http://zetcode.com/db/postgresqlpythontutorial/

PostgreSQL : create table

Create Database

         ก่อนอื่นต้องทำการ สร้าง database เพื่อจัดเก็บตารางที่มีความสัมพันธ์กันก่อน โดยใช้คำสั่ง createdb ชื่อdb ในที่นี้ตารางเราใช้เก็บข้อมูลเกี่ยวกับคลังเก็บสินค้าที่จะส่งให้ลูกค้า จึงใช้ชื่อdatabase ว่า "inv_trans"

ภาพแสดงคำสั่งที่ใช้สร้าง db

         วิธีเช็ค ว่ามี Database ใดบ้าง จะใช้คำสั่ง  
postgres=# \l         <-------เริ่มPostgres prompt โดยการพิมพ์ psql ก่อน
          จากรูปด้านล่างจะพบว่ามี database ชื่อ "inv_trans" ที่ได้ทำการสร้างไว้แล้ว หากต้องการจะออกจาก window ดังกล่าวแล้ว จะใช้คำสั่ง \q

ภาพแสดงlist ของ db

         ก่อนจะทำการสร้างตาราง จะต้องเลือก database ที่ต้องการจะสร้างตารางด้านในก่อน โดยใช้คำสั่ง \c ชื่อdb

ภาพแสดง การเลือก db ที่ชื่อว่า inv_trans

Create Tables

        ในการสร้างตาราง ในขั้นตอนแรกจะต้องสร้างตารางที่ไม่มี FK เพื่อให้ไม่เกิดปัญหาการอ้างอิง
        คำสั่งที่ใช้ในการสร้าง เช่นเดียว คำสั่ง ในMySQL หรือ Sqlite

        ในขั้นตอนต่อมา ต้องเลือกลำดับการสร้างตาราง เนื่องจากมี FK ที่จะต้องอ้างอิงไปยังตารางอื่น หากตารางที่ถูกอ้างอิงยังไม่ได้ถูกสร้าง จะเกิดการ error ได้

 

        เมื่อทำการสร้างตารางทั้ง 10 ตารางเสร็จหมดแล้ว หากต้องการตรวจสอบว่ามีตารางใดบ้างใน db นี้ จะใช้คำสั่ง \d

ภาพแสดงตารางต่างๆใน database

   
        แต่หากต้องการทราบรายละเอียดในแต่ละตาราง จะใช้คำสั่ง \d+ ชื่อตาราง

DESCRIBE TABLE ใช้คำสั่ง \d+ inv_type

        ซึ่งจะเห็นได้ว่าในตาราง inv_type มีattribute เดียวคือ ty_group แล้วก็ยังมีการแสดงข้อมูลอื่นๆเกี่ยวกับ attributeในตารางนี้


อ้างอิงข้อมูลจาก
1) http://stackoverflow.com/questions/109325/postgresql-describe-table
2) https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

PostgreSQL

PostgreSQL 

          เป็นระบบการจัดการฐานข้อมูลเชิงวัตถุ-สัมพันธ์ (object-relational) แบบ ORDBMS โดยสามารถใช้รูปแบบคำสั่งของภาษา SQL ได้เกือบทั้งหมด ซึ่งเราได้ทำการศึกษาภาษา SQL มาแล้วในตอนต้น ทั้งในสัปดาห์ที่ 2 และ สัปดาห์ที่ 3
สัปดาห์ที่ 2: http://db5620064.blogspot.com/search/label/W2
สัปดาห์ที่ 3: http://db5620064.blogspot.com/search/label/W3

          นอกจากนี้ PostgreSQL ยังเป็น OpenSource ที่สามารถทำงานได้บนระบบปฏิบัติการหลายตัว ทั้ง  Linux, UNIX และ Windows ซึ่งทางเราได้ทำการศึกษาบนระบบ Ubuntu

อ้างอิงข้อมูลจาก : http://www.mdsoft.co.th/%E0%B8%84%E0%B8%A7%E0%B8%B2%E0%B8%A1%E0%B8%A3%E0%B8%B9%E0%B9%89/92-what-is-postgresql.html

 

HOW TO INSTALL

ใช้ทั้งสองคำสั่งนี้ใน terminal บน ubuntu 

~$ sudo apt-get update
~$ sudo apt-get install postgresql postgresql-contrib
 จากที่ได้ทำการทดลองลงโปรแกรม ไม่พบข้อผิดพลาดใดๆ

***_______________________________________________________________***

Using PostgreSQL Roles and Databases

ในขั้นตอนการติดตั้งจะสร้าง user account ที่ชื่อว่า postgres
หากจะใช้ postgres เราจะต้อง log in เข้า account ก่อน นั่นคือ

คำสั่ง
~$ sudo -i -u postgres


เมื่อต้องการเริ่มใช้ PostgreSQL prompt จะใช้คำสั่ง
~$ psql 
.
.
.
.
.
เมื่อต้องการออกจาก PostgreSQL prompt จะใช้คำสั่ง 
postgres=#      \q


แต่หากต้องการlog out ออกจาก account postgres จะใช้คำสั่ง
~$ exit


 ศึกษาเพิ่มเติมจาก : https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04