PouchDB - The JavaScript database that Syncs

PouchDB was written to help web developers build applications that work as well offline as well as they do online, applications save data locally so the user can use all the features of an app even while offline and synchronise the data between clients so they have up to date data wherever they go.

PouchDB Example

PouchDB is Open Source, written in Javascript and inspired by Apache CouchDB, it is designed to be lightweight and easily embeddable.

Cross Browser 
PouchDB uses various backends so it can work across various browsers and in Node.js. It uses IndexedDB in Firefox and Chrome, WebSQL in Safari and Opera and LevelDB in Node.js.
Currently tested in:

• Firefox 12+
• Chrome 19+
• Opera 12+
• Safari 5+
• Node.js 0.10+
• Apache Cordova
• Internet Explorer 10+

If your application requires support for Internet Explorer below version 10, it is possible to use an online CouchDB as a fallback, however it will not work offline.

PouchDB Project Page

Ricon East 2013 Videos

Basho has made available presentation videos and slides from RICON East 2013 event. RICON East 2013 is a two day conference dedicated to Riak, developers, and the future of distributed systems in production.

RICON East 2013 Event

 Slides and videos are available at RICON East 2013 event page.

Article - Why You Should Embrace Database Virtualization

This article addresses the benefits provided from database virtualization. Before we proceed however, it is important to explain that database virtualization does NOT mean simply running a DBMS inside a virtual machine.

Contents of the article:

• Database Virtualization, More Than Running a DBMS in a Virtual Machine
• An Example of the Database Virtualization Problem
• Defining Database Virtualization
• Benefits of Database Virtualization
• Additional Resources

Continue to the article "Why You Should Embrace Database Virtualization" by Mike Hogan

MySQL Workbench 6.0.3 BETA 2 released

MySQL Workbench 6.0 is the new major update of the Development and Administration tool for MySQL. This release includes over 150 bug fixes (~40 since 6.0.2), 30 new features and a new redesigned UI. As a beta, this release is not suitable for production use.
Improvements in MySQL Workbench 6.0:

1. a new redesigned Home screen
2. the SQL Editor and Server Administration UIs were merged into a single connection specific interface, allowing for quick access to administration features while simplifying the location of specific features
3. improved model Synchronization, lets you compare and update your EER model or database with ALTER scripts and properly handle corner cases involving objects renamed externally or sync schemas with different names
4. all new Schema Inspector, gives you a detailed overview of all objects in your schemas, plus access to table maintenance operations such as ANALYZE and OPTIMIZE TABLE
5. table data search, lets you perform a text search on any number of tables and schemas for rows matching a given pattern
6. improved Server Status page, gives a quick summary of server status and configuration
7. cascaded DELETE statement generator, automatically performs the tedious task of generating DELETE statements for deleting a row referenced by foreign keys
8. new SQL Anywhere and SQLite targets for the Migration Wizard
9. and much more.

What is new in MySQL Workbench 6.0

PostgreSQL Maestro 13.7 released

SQL Maestro Group announces the release of PostgreSQL Maestro 13.7, a powerful Windows GUI solution for PostgreSQL database server administration and database development.


Top 10 new features:

1. Support for foreign data management objects.
2. Improved support for composite types.
3. Keyboard-interactive SSH authentication.
4. Enhanced SQL Dump wizard.
5. New Nullable Column Checker tool.
6. New Start Page with quick access to recently connected databases.
7. Advanced metadata filtering abilities.
8. Improved SQL Editor and Script Runner.
9. Highlighted key columns and multi-column lookup editors in the data grids.
10. Colored profiles in Database Explorer.

There are also some other useful things. Full press release is available at the SQL Maestro Group website.

How TokuMX Gets Great Compression for MongoDB

The Fractal Tree index is the data structure inside TokuMX and TokuDB. One of its strengths is the ability to get high levels of compression on the stored data. In this post author will explain why that is.

Fractal Tree index data structure

Article - How TokuMX Gets Great Compression for MongoDB

SQL Server Query Optimization Statistics

What are Query Optimization Statistics?

The term statistics refers specifically to distribution statistics. Distribution statistics describe index key values, specifically:

• Selectivity – Refers to how many rows are identified by a key value. A unique index has high selectivity. A key with many duplicates has low selectivity. 
• Distribution – Used when estimating how efficient an index will be in retrieving data associated with a key value or range. 

Example query

The query optimization statistics are simply a form of dynamic metadata that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

Full article - Understanding SQL Server Query Optimization Statistics

Impala: Open Source Real-Time SQL Queries on Hadoop

Cloudera Impala is an open source system which provides real-time SQL querying functionality on top of Hadoop.

Impala High-level Architectural View

Cloudera, which created Impala, said they had been technically inspired by Google's Dremel paper, which made them think that it would be possible to perform real-time, ad-hoc queries in Apache Hadoop.

In October 2012, when announcing Impala, Cloudera introduced it as follows:

“Real-Time Queries in Apache Hadoop, For Real”

Impala adopted Hive-SQL as an interface. As mentioned above, Hive-SQL is similar in terms of syntax to SQL, a popularly used query language. For this reason, users can access data stored in HDFS through a very familiar method.

As Hive-SQL uses Hive, you can access the same data through the same method. However, not all Hive-SQLs are supported by Impala. For this reason, you had better understand that Hive-SQLs that are used in Impala can also be used in Hive.

Continue to Meet Impala: Open Source Real-Time SQL Queries on Hadoop

Finding the most recent purchase by each customer with SQL

If you have transaction history such as purchases in a database (or really any list of items with timestamps), you frequently want to pick out recent items. SQL makes it easy to find the most recent items in the table.

The most recent items in the table

Finding the most recent purchase by each customer with SQL | Metrica

Groupdate.sql - Better SQL group by date

Groupdate.sql provides the simplest way to group by:

• day
• week
• month
• day of the week
• hour of the day
• and more

Timezone support is included and you can use Groupdate functions with any programming language. Supported SQL DBMS: MySQL and PostgreSQL.

Groupdate.sql creates SQL functions, which are stored by the database. You can use these functions with any ORM (or without one).

Example query with Groupdate functions

Groupdate.sql project page