27 Open Source Databases – I

Miss Bhumika Shah

epgp books
  1. Introduction
  • The era of databases has seen a revolution in the past few years. So many things have changed , the data models were challenged , new data models came in the market and many new changes came in the area of database.
  • Around 1950-60s all the software’s were developed by academics or corporate researchers and they distributed this software’s under principles of openness and cooperation.
  • During this period, source code was given with the software, as frequent modifications aroused in the software.
  • A2, developed by UNIVAC was the first example of open source software.
  • Various groups were formed to share information and one of such user group was IBM
  • Initially , free software was declined
  • In early 1970s, At & T distributed Unix at No cost to government and researchers
  • In 1983, Richard Stallman launched the GNU project which followed launch of open source and rest is history.
  • Within a decade, Linus Torvalds (1991) came with Linux and changed the market altogether.
  1. What are Open Source Databases
  • A Database management system is computer software that interacts with user, other applications and other databases. It is a software that handles Data management (storage, retreival and updation of data).
  • Open source refers to the software the allows the users to use, modify and distribute the software. The source code is made available and this type of software’s are registered under Open-Source licensing like GNU, BSD etc.2.1 Why Open Source Database
  • The No cost-Label makes them more popular and acceptable
  • Exist since more than two decades
  • The Databases which are registered under this open source models are the open source databases
  • Majority of Open source databases (MySQL, PostgreSQL, Firebird) are simpler to use then their enterprise counterparts

Open Source Vs Commercial (Proprietary)

  1. List of Open Source Databases
  • MySQL/MariaDB
  • SQLite
  • PostGres
  • Ms-SQL
  • MongoDB
  • BigTable
  • Cassandra
  • Hbase
  • CouchDB

In the above list , you can observe that some databases are of SQL category and some are of NoSQL category. So let us have a quick look at the comparison between SQL and NoSQL category

  1. SQL Vs NoSQL

Hence, First we will cover the category of NoSQL Databases Following Databases come under SQL category

  • SQLite
  • PostgreSQL
  • Firebird
  • Microsoft SQL Server Express (SQL Server on Linux)
  • MySQL / MariaDB

6.1 SQLite:

  • SQLite is not a client-server database engine, but it is embedded into the end program.
  • It is the most widely deployed database engine
  • It does not have separate server process and it reads and writes directly to ordinary disk files and that is the reason it is also known as embedded relational database management system

Features of SQLite

  • Serverless : It does not required a separate server process or system to operate
  • Large collection of APIs
  • Written in Ansi-C
  • Cross-Platform DBMS
  • No configuration required
  • Length of columns is not fixed : Variable length of columns as it allocates space as and when required
  • Included as part of Android software stack

6.2 PostgreSQL

 

During the period of 1977-85 , a project called Ingres was developed. It followed all the concepts of a relational database management system.

  • Established the company Ingres in 1980
  • The main focus of this development was on object orientation
  • A Query language named Quel was developed
  • The code base of Ingres was not used as basis for PostGres
  • The team leader at Berkeley left the company to launch the proprietary version of Ingres
  • Returned to Berkeley in 1985 and started Post-Ingres project
  • 1994 – 95 : Launched Postgres95
  • SQL support added in 94
  • Released Postgres in 95 , re-released in 95

    PostgreSQL(Postgres) is an ORDBMS used to store data securely and make data retrieval easy.

 

It is fully acid complaint and has support for Updateable and materialized views and objects

 

Features:

  • It has support for lot of SQL features like triggers, procedures , functions and foreign keys.
  • It also has support for complex sql queries and Multiversion concurrency control(MVCC) :(MVCC means each transaction get snapshot of database allowing changes to be made without being visible to other transactions)
  • Various extensions are available for download to add additional functionality to PostgreS
  • Here , table can be set to inherit characteristics from Parent table
  • It can run on almost all well known operating systems like Linux , unix, windows etc.

6.3 Firebird

 

Firebird is an open source SQL relational database management system that runs on Linux , Windows, mac and Unix. Firebird technology has been in use since 20 years now which makes it a mature and stable product.

 

Features:

  • Fully ACID complaint transactions
  • Multi-Generational architecture
  • Good and vast community support
  • Full support for Stored procedures and triggers
  • Very Small footprint
  • API support : There is an API named TraceAPI which lets you know what happens on your server
  • No major configuration changes required

On Windows, Firebird can be run as a service or in application mode

Fig 1: Firebird GUI

6.4 Microsoft SQL Server Express

  • A free version of Microsoft’s popular SQL Server, SQL Server Express is targeted at smaller-scale applications. Many of the core-functionality features are added to this product.

Fig 2 : Microsoft SQL Server Express GUI

SQL Server on Linux

 

In the end of 2016, microsoft announced to come with a version for linux and was made available for preview on Microsoft’s website.

 

SQL Server on Linux: World-class RDBMS, Open source enterprise environment

 

Features:

  • Dynamic Data Masking : It limits sensitive data exposure by masking it to non-privileged users. It is used to simplify design and coding of security.
  • Row level Security : It enables customers to access table rows based on the characteristics of user executing a query. It enables to implement restriction on data row access.
  • Transparent Data Encryption : Data Encryption performs real time input/output encryption and decryption of data and log files. Encryption uses DEK(Data Encryption Key)
  • Most Secure Platform: With features like DDM and Row level security it becomes one of the most secure platform.

  6.5 MariaDB

 

MariaDB is a community developed fork of MySQL RDBMS. MariaDB maintains the compatibilty with MySQL ensuring “drop-in” replacement for MySQL Databases.

 

Features:

  • Scalable
  • Robust
  • Fast
  • Rich plugins
  • Efficient storage engine
  • Latest versions include GIS and JSON features
  • XtraDB replaces Innodb and new storage engine ARIA
  • (Aria intends to be both transactional and non-transactional engine, might be included in future versions of MySQL)

     6.6 MySQL

 

MySQL is one of the most popular open source database. It is the key part of LAMP – the fastest growing open source stack.

 

Features:

  • Full ACID Support
  • Cross platform support
  • Unicode support
  • Security
  • Scalability
  • Reliability
  • Good community support
  • Powerful GUI tool
  • Powerful MySQL Workbench

    MySQL Download

 

MySQL is available in variety of flavors

  • MySQL Enterprise Edition(commercial)
  • Oracle MySQL Cloud Service(commercial)
  • MySQL Cluster(commercial)
  • MySQL Community Edition(GPL)
  • GPL downloads again have different variants like :
  • Community Server
  • Utilities
  • Workbench and much more

    Summary

  • Open Source Databases have changed the Database industry altogether.
  • Open Source Databases are divided into two categories :SQL and NoSQL
  • Open Source Databases are available under GPL licencing
you can view video on Open Source Databases – I

References:

 

1) https://db-engines.com/en/ranking_osvsc

2) http://www.firebirdnews.org/docs/fb2min.html

3) https://www.microsoft.com/en-us/sql-server/sql-server-2017

4) https://www.microsoft.com/en-us/sql-server/developer-tools

5) http://info.microsoft.com/rs/157-GQE-382/images/EN-CNTNTSQL_Server_on_Linux_Public_Preview_Technical_Whitepaper-en-us.pdf

6) https://dev.mysql.com/downloads/windows/installer/5.7.html

7) https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/

8) https://mariadb.org/about/

9) https://www.mongodbmanager.com/features

10) https://www.mongodb.com/products/compass