Overview of CSQL MMDB Cache System
<< Before you Start - TOC - CSQL Installation >>
This chapter is intended for features, roadmap, standard compliance, information references, and advantage of CSQL.
What is MMDB and DRDB
The difference between MMDBMS (main memory database management system) and DRDBMS (disk resident database management system) is their data storage. The MMDBMS is also a kind of RDBMS where data is stored completely in main memory. So the user can access data using the standard query languages and the MMDBMS has the typical features of RDBMS such as controlling data inconsistency, concurrency controls, etc. By managing data in memory, and optimizing data structures and access algorithms accordingly, database operations execute with maximum efficiency, achieving dramatic gains in responsiveness and throughput, even compared to a fully cached DRDBMS.
Advantages of CSQL MMDB
- It keeps the data in main memory rather than disk.
- It is times faster than any disk based database system.
- No buffer manager overhead is present since all the records are stored in main memory.
- The data structure and algorithms are targeted for memory access.
- Portability via JDBC, ODBC and Gateway interface.
- A powerful proprietary, Storage engine that stores and manages the data in memory optimized for real time queries.
- A high performance proprietary SQL Engine.
CSQL - Open Source Product
CSQL is an Open Source Software at Source Forge (www.sourceforge.net), world’s largest development and download repository of open source code and applications. Source Forge is an Independent, non-profit initiative whose mission is to build user’s trust and confidence in the Software Product by keep it open to the users.
Main features of CSQL
The following list draws some of the important features of the CSQL Database.
Interoperability and Internals
- Written in C and C++
- A powerful, Storage Engine that store and manage data completely in memory.
- High performance SQL Engine optimized for real time queries.
- Works on Linux, Solaris Platform.
- APIs for C, C++, and Java are available. Refer CSQL Programmers Guide for APIs.
- Multi threaded access to database.
- Provides transactional and non-transactional storage engine.
- Tree Index for faster range look up.
- Hash Index for fast point look up.
- Very fast Joins.
- ACID Compliant (Atomicity, Consistency, Isolation and Durability Properties).
- Auto-Increment field.
- Highly Concurrent
- Uses lock free data structures and reduces contention by multifold.
- Row level locking.
- Multi Granular Locking
- Database level locking
- Table level locking
- Row level locking
- Isolation level support
- Read uncommitted
- Read committed
- Read repeatable
- Constraints
- Primary key
- Foreign Key
- Not Null
- Unique
- Multi user
- Fault tolerance
- Process clean up for resources in case of application crash
- Archive/Restore
- ODBC 3.0 Driver
- JDBC 3.0 Driver
Data Type
- All primitive
- tinyint
- smallint
- int
- integer
- long
- bigint
- real
- float
- double
- binary
- char
- varchar
- Non-Primitive
- date
- time
- timestamp
Statements
- All arithmetic and logical operator support WHERE clauses of queries.
- Support for GROUP BY clause, support for aggregate function COUNT( ), AVG ( ), MIN ( ), MAX ( ), and SUM ( ) and HAVING clause
- Support for INNER JOINS, LEFT OUTER JOINS
- Support for ORDER BY clause and DISTINCT
- Support for LIMIT and OFFSET
- SHOW statement to retrieve table information and user information.
Connectivity Mechanism
- Client can connect to the CSQL server using proprietary SQLAPI. Refer the section 3. in CSQL’s Programmers Guide
- ODBC Driver (support for C, C++ client programs that use Open Database Connectivity Connection).
- JDBC Driver (support for Java client programs that use JDBC Connection).
- PHP Driver (support for PHP client programs).
Client tools
- CSQL Interactive tool for SQL statements.
- catalog tool is used to retrieve metadata information of the database such as tables, index, mutex, etc.
- csqldump tool for backup and restore.
Standards Compliance
This section describes about Standard compliance of ANSI/ISO, ODBC, and JDBC Standards.
- SQL 92 Entry Level
- ODBC level 2
- JDBC 3.0
SQL 92 Entry Level
The SQL 92 standard was accepted in 1992. At the time of writing, SQL 92 is not fully implemented by CSQL. One of our main goals with the product is to continue to work toward compliance with the SQL standard, but without sacrificing speed or reliability.
Though it supports minimal feature set, it shall be used as stand alone main memory database system for applications. Most of the application may not require rich feature set; rather they require minimal feature set with ultra fast response.
The below list is some of the features of SQL 92 Entry Level implemented by CSQL.
- Support for dynamic SQL
- Support for Schema definition and manipulation statements
- Support for INNER JOIN, LEFT OUTER JOIN
- Support for GROUP BY, HAVING with aggregate functions.
- Support for ORDER BY
- Support for Data Types DATE, TIME, TIMESTAMP with all Primitive types
- Support for the qualifier ‘*’ in the select lists
- Case Insensitivity for Identifiers
- Table columns involved in a PRIMARY KEY constraint are no longer required to be explicitly declared as NOT NULL.
ODBC Level 2
The CSQL ODBC Driver supports ODBC version 2.5 and 3.0 Level 2 only. The Level 2 is all that is necessary to do standard operations and application might want to limit up to level 2 ODBC calls. Refer CSQL Programmers Guide to know in details about ODBC APIs, which comes under Level 2.
JDBC 3.0
The CSQL JDBC Driver supports JDBC 3.0 APIs and few higher version APIs also. Its functionality includes parameters, selects, batch updates, programmatic inserts, updates, deletes, and updates.
Feature Overview
This section provides brief overview on main features like ODBC, JDBC and Cache connection for disk-based databases.
ODBC and JDBC Interfaces
CSQL supports ODBC and JDBC. Though ODBC and JDBC are standard CSQL interfaces it operates directly with the database engine. CSQL supports these APIs that are both fully compliant with the standards and tuned for maximum performance in the CSQL environment.
Database Connectivity
Application written in C, C++ or Java can connect to the database directly through the drivers in Embedded (Server and Client running in same machine) or Client/Server mode. These connection options allow users to choose the best performance functionality for their applications. Direct driver connections are fastest for ODBC and JDBC applications.
Caching option for Disk based Databases
Caching option for MySQL, Postgres, Oracle, DB2 and Sybase is an option to the CSQL Main Memory Database that creates a real-time, updateable cache for target database.
Who can use CSQL
CSQL has been designed from scratch with a single point benefit – to provide undisruptive performance benefits in application domains where real time access to data is a core necessity. For example –
- Financial and Insurance Industry
- Information Technology
- Telecommunication Industry
<< Before you Start - TOC - CSQL Installation >>