mysql & postgresql
TRANSCRIPT
![Page 1: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/1.jpg)
MySQL & PostgreSQL
ByKanchana Welagedara
Lanka Software Foundation www.opensource.lk
![Page 2: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/2.jpg)
Agenda● Introduction● History● Licensing● Features● Commercial DB Engines vs Open Source DB
Engines● Demonstration with XAMPP ● Q&A
![Page 3: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/3.jpg)
Introduction
www.postgresql.org
WWW.mysql.org
![Page 4: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/4.jpg)
Introduction MySQL & Postgressql usage● BioPharma● Defense● eCommerce● Education● Energy● Finance● Gaming● Travel
● Government● Healthcare● Manufacturing● Media● Open Source Projects● Retail● Technology● Telecom
![Page 5: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/5.jpg)
IntroductionMySQL& Postgres users
![Page 6: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/6.jpg)
How it All Began ...
![Page 7: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/7.jpg)
HistoryPostgreSQLUniversity Of California Berkley 1986
POSTGRES project
Prof.Michael Stonebraker
![Page 8: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/8.jpg)
History MySQL● mSQL project● MySQL 1995 may● MySQL first Windows Release 1998● Version 3.23: beta from June 2000● production release January 2001● Version 4.0: beta August – 2002● Version 4.1 (rtrees, subqueries)March 2003● Version 5.0: beta 2005 (cursors,stored
procedures, triggers, views, XA transactions)
Founder MySQL ABDavid Axmark
![Page 9: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/9.jpg)
Licensing MySQL● GNU General Public License (GPL) for GPL
projects If your project is 100 % GPL in its distribution
● Commercial License for commercial applicationsIf you DON'T want to distribute the source code for your application.
![Page 10: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/10.jpg)
Licensing PostgreSQL● Berkley License It allows for any use as long
as a copy of the Berkley License is included with it.
● Much Simpler
![Page 11: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/11.jpg)
Features Storage● MySQL Multiple Storage
Engines● Native storage engines
(MyISAM, Falcon, Merge, Memory (heap), Federated, Archive, CSV, Blackhole, Cluster)
● Partnerdeveloped storage engines (InnoDB, solidDB, NitroEDB, BrightHouse)
● Communitydeveloped storage engine
● Custom storage engine
● PostgreSQLSingle data Storage mechanism
![Page 12: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/12.jpg)
Feature Data Integrity● MySQL and PostgreSQL are ACID ( Atomic,
Consistent, Isolated, Durable )Compliant.● Both Support partial rollbacks of transactions
and deadlocks.● MySQL Rowlevel locking● PostgreSQL Multi Version Concurrency
Control (MVCC) default or RLL optional
![Page 13: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/13.jpg)
FeaturesThe Advanced Features
MySQLPostgreSQLPostgreSQLviews,
triggers, stored procedures, cursors
Commercial DB
inheritance,sequences,userdefined data types
![Page 14: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/14.jpg)
Features Replication
MySQL PostgreSQL
singlemaster, multislave replication scenarios
multimaster, multislave replication from a thirdparty vendor& additional Replication methods
![Page 15: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/15.jpg)
FeaturesDatabase Interface Methods● Support ODBC and JDBC for network
connectivity● Native database access methods● Authentication for the database● Both support access via C/C++, Java, Perl,
Python, and PHP
![Page 16: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/16.jpg)
More Features● Stored Procedures and Triggers PL/pgSQL, is
very similar to Oracle's PL/SQL,PostgreSQL's procedures and triggers can be written in PL/TCL, PL/perl,PL/python
● Indexes Single column, multicolumn, unique, and primary key indexes,MySQL supports full text indexes out of the box
● Data TypesLarge Objects Data Types,GIS,PostgresSQL user define data type
![Page 17: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/17.jpg)
● GUI ToolsTools to manage DB,Natively on your operating system or Webbased tools
● Data MigrationTools to migrate data from commercial databases
● Backup come with scripts to facilitate a simple text dump of your database data and its schema,simple database recovery>soft failures,database crashes or unexpected power failures
![Page 18: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/18.jpg)
Platform Support
LinuxFreeBSDWindowsMacOS
MySQL
Oracle ,DB2
PostgreSQL
AIX, BSDiHPUX,NetBSD, Novell NetWare, OpenBSD, OS/2 Warp, QNX, SGI IRIX, Solaris, SunOS, SCO OpenServer, SCO Tru64,
![Page 19: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/19.jpg)
Commercial DB vs MySQL &PostgresSQL
Product GradeMySQL Very GoodPostgresSQL Very GoodOracle Poor
MySQL: FreeOracle8: Number of processors * number of MHz * USD 15Postgres: FreePrice of the smallest multiuser installation MySQL: FreeOracle8: 5 * USD 160Postgres: Free
Prices
![Page 20: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/20.jpg)
Commercial DB vs MySQL & PostgresCategory Problem
Importance Assessment
MySQL Oracle8 Postgres
C C B C ASQL B B C B B
B B C A A
A C D A C
C C C A A
B C B A B
A C D A ALocks A C D A A
A D C A C
B C D A A
B D A A BBackup A C C A C
C C A B A
Central database
Lab-participan
ts
Elementary features
Basic data types
Declarative constraintsProgramming abstractionsGeneration of idsNational chars
Transactions
Transactions
Multiuser access
Programming in DB
Stored procedures and triggers
Administration
Access control
Data migration
![Page 21: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/21.jpg)
Commercial DB vs MySQL & Postgres
B D D A B
B D D A D
A C C A CSize limits A B B A C
A C D A B
C D C A C
B D D B D
Performance and VLDB
Structures supporting optimizationSupport for OLAPAllocation of the disk space
VLDB implementations
Distributed databases
Access to multiple databasesHeterogeneous systems support
Category Problem
Importance Assessment
MySQL Oracle8 PostgresCentral
database
Lab-participan
ts
![Page 22: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/22.jpg)
Commercial DB vs MySQL & Postgres
C C D A B
C C D A C
C C D A B
B C B A B
A A A A A
A A B A BXML B C D A DCASE B C D A D
Reliability Recovery A B C A CPrices C A A D A
A B C B D
A C D A D
Special data types
Post-relational extensionsSupport for special data types
Application development and interfaces
Embedded SQLStandard interfacesAdditional interfacesWeb technology
Commercial issues
Technical supportPosition on the market
Category Problem
Importance Assessment
MySQL Oracle8 PostgresCentral
database
Lab-participan
ts
![Page 23: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/23.jpg)
Using open source DB in Developments .....
Prerequisites
1. XAMPP (www.apachefriends.org) installed2. jdk 1.4 / +3. Eclipse >PHPEclipse(www.eclipse.org)
![Page 24: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/24.jpg)
Q & A
?
![Page 25: MySQL & PostgreSQL](https://reader035.vdocuments.us/reader035/viewer/2022070222/613d3408736caf36b75a8aa8/html5/thumbnails/25.jpg)
Thank You!
Lanka Software Foundationwww.opensource.lk