NoSQL - RavenDB

NoSQL is a form of database, which is completely different from relational databases when talking in the terms of storing and retrieving data.

There are many types of NoSQL databases being used in the market for varied requirements. I am speaking about RavenDB(open source) which is a document database.

Calling it a document database, literally means that it stores data in the form of documents. Each document can be compared to a row of data in relational DBMS(related rows of data from multiple related tables).

RavenDB is being used with .NET as of now. In general scenario, .NET developers will select data from table in the database, manipulate the data as per functionality (if required) and then inserts the data back into the table. This is the area where NoSQL is made use of.

.NET developers need not reformat the data into table format anymore. They can store the JSON format files directly in the NoSQL (RavenDB) database.

Each document will have metadata information like ETag, LastModified, Version etc. Each document will have unique ID like the primary key value in RDBMS for each row.

A group of related documents is called a Collection.

Indexing: We also have the feature of indexing. RavenDB implicitly creates indexes on the search criteria being used as and when necessary. We can also create explicit indexes.

Replication: RavenDB supports replication. We can setup replication for required documents to move the data to SQL database for reporting purposes. We need to specify Destination table and a Document key from the Source Document collection and write code to map the document fields data to SQL table columns.

Its good to see and learn a different form of database working alongside traditional RDBMSs.

Happy NoSQLing!

MySQL FULLTEXT Searches

Want to search a particular string in MYSQL database? then make use of Full Text Search techniques

There are 3 types of Full Text Search techniques available:

1.Natural language Full-Text Search

2.Boolean Full-Text Search

3.Query Expansion Search

For the above searches we make use of MATCH(col1,col2..) AGAINST(expr [IN NATURAL LANGUAGE MODE/IN BOOLEAN MODE/WITH QUERY EXPANSION])

One or more columns can be provided in the MATCH function. Search string is given as argument to AGAINST function.

• Natural language Full-Text Search(is used by default)

• Boolean Full-Text Search

• Query Expansion Search

Natural language Full-Text Search can be applied only on MYISAM tables. For performing the search we create an FULLTEXT index on the particular columns to be searched. Non indexed columns cannot be searched. FULLTEXT index can be created while creating a table or can be altered later to add the index. It is better to first create a table, upload the data and then add the index. This is because when large data sets must be loaded to table, FULLTEXT indexed table takes larger time.This search interprets the search string as a phrase in natural language. There are no special operators.

By default the search is not case sensitive. To make search case sensitive, we need to go for binary collation for the indexed columns.For example, a column that uses the latin1 character set can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.

This returns rows basing on the relevance. Relevance is calculated basing on the number of times the search string occurs in a particular row(from higher relevance to lower relevance).

Limitations:

Words that are present in 50% or more of the rows are considered common and do not match the selection.The stopword list applies.

Syntax:

select * from table_name where MATCH(indexed columns seperated by comma) AGAINST(SEARCH STRING IN '');

Ex:

select * from test where MATCH(column1,column2) AGAINST('search string');

                                                           OR

select * from test where MATCH(column1,column2) AGAINST('search string' IN NATURAL LANGUAGE MODE);

A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “some” or “then” are stopwords and do not match if present in the search string.

This does not follow any relevance order as Natural Language Search.

Boolean search can be performed even without creating an index on the columns.But this type of search would be slow.

This does not have 50% matching criteria as of Natural Language Search.

This search makes use of following operators:

+

A leading plus indicates this word must 

- 

A leading minus indicates that the rows returned must not contain the specified word.

[no operator] implies optional

Syntax:

select * from table_name where MATCH(indexed columns seperated by comma) AGAINST(SEARCH STRING IN '' IN BOOLEAN MODE);

Ex:

select * from test where MATCH(column1,column2) AGAINST('search string' IN BOOLEAN MODE);

TheBlind Query Expansion (or automatic relevance feedback)feature can be used to expand the results of the search. This often includes much more noise, and makes for a very fuzzy search.In most cases you would use this operation if the users query returned just a few results, you try it again withe query expansion and it will add words that are commonly found with the words in the query.

Syntax:

select * from table_name where MATCH(indexed columns seperated by comma) AGAINST(SEARCH STRING IN '' WITH QUERY EXPANSION);

Ex:

select * from test where MATCH(column1,column2) AGAINST('search string' WITH QUERY EXPANSION);

FEW IMPORTANT POINTS:

Use the following query to query the variables status related to FULL TEXT search

show variables like 'ft%';

• Searches are not case sensitive.

• Short words are ignored, the default minimum length is 4 characters. You  can change the min and max word length with the variables ft_min_word_len and ft_max_word_len .
• Words called stopwords are ignored, you can specify your own stopwords, but default words include the, have,some
• You can disable stopwords by setting the variable ft_stopword_file to an empty string.
• Full Text searching is only supported by the MYISAM storage engine.
• If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones(applicable only to Natural language Search).

Ordering on Varchar Datatype column

CAST function can be used like below:

SELECT col_name from table_name
ORDER BY CAST(col_name AS SIGNED INT);

We just make the ORDER BY clause work on a integer value.That is done by CAST(col_name AS SIGNED INT) in the statement. 

Handling DatabaseTransactions

Transaction is a unit of work, when completed, takes the database from one stage to another.

This post assumes the database to be MySQL and it is almost same in other databases too. 

You will have 3 steps to do:

You need to begin a transaction, code required statements and end the transaction.

Step 1: Start the transaction: Initiate a transaction by using BEGIN WORK command. When you issue this command, MySQL assumes that everything following it is part of a transaction

BEGIN WORK;

You can also use just BEGIN, or use the equivalent START TRANSACTION command.

Step 2: Perform the transaction

Once the transaction is start, use SQL commands as necessary. The dirty data visibility to other processes depends on the isolation level set for the database. 

Step 3: End the transaction

Once done with the SQL commands, you can either cancel the transaction or save the transaction to database.

To cancel the transaction, use ROLLBACK command. It means when we issue ROLLBACK command, the database will go to the state before the BEGIN WORK command has been issued.

To save the transaction, use COMMIT command. This statement will save all the changes to database done with the previous SQL commands.

We also have a AUTOCOMMIT feature, which implicitly saves the changes. But do a research before setting it to ON/OFF.

Source: http://www.weberdev.com/ViewArticle/Using-Transactions-In-MySQL-Part-1

This article copyright Melonfire, 2005. All rights reserved.

Exception Handling in MySQL

Exception Handling

Exception Handling is a technique which helps smooth execution of code in case of any known/unknown issues

Declare for Conditions

Certain conditions may require specific handling. These conditions can relate to errors or warnings, as well as to general flow control inside a stored program.

General: Here we declare a specific name to a specific error number. These error numbers are pre-defined mysql errors. When a particular error number exception occurs, the condition associated is fired and relating actions are done.

Syntax

DECLARE condition_name CONDITION FOR condition_value;

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | mysql_error_code

The DECLARE ... CONDITION statement defines a named error condition. It specifies a condition that needs specific handling and associates a name with that condition. The name can be referred to in a subsequent DECLARE ... HANDLER statement.

A condition_value for DECLARE ... CONDITION can be an SQLSTATE value (a 5-character string literal) or a MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate success rather than an error condition.

Below is an Example:

We have a table s1 as follows with ‘s’ as primary key

s	sname
1	Name1
2	Name2

Now try to insert a row with values (1,’name3’). We interpret it with the procedure

CREATE PROCEDURE p1 (IN p_s int (2), IN p_sname varchar (5), OUT p_status varchar (30))

BEGIN

DECLARE duplicates CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

SET p_status ='Out in middle';

END;

Insert into s1 values (p_s, p_sname);

END;

Execute

Call p1 (1,'name3', @s);

Select @s;

OUTPUT:

Out in middle

In the above procedure we declared a condition for duplicates.

i.e. for returning a message when we try to insert duplicate values

DECLARE for Handlers

DECLARE handler_type HANDLER

    FOR condition_value [, condition_value] ...

    statement

handler_type:

    CONTINUE

  | EXIT

  | UNDO

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | condition_name

  | SQLWARNING

  | NOT FOUND

  | SQLEXCEPTION

  | mysql_error_code

The DECLARE ... HANDLER statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed. Statement can be a simple statement (for example, SET var_name = value), or it can be a compound statement written using BEGIN and END

For a CONTINUE handler, execution of the current program continues after execution of the handler statement. For an EXIT handler, execution terminates for the BEGIN ... END compound statement in which the handler is declared. (This is true even if the condition occurs in an inner block.) The UNDO handler type statement is not supported.

If a condition occurs for which no handler has been declared, the default action is EXIT.

A condition_value for DECLARE ... HANDLER can be any of the following values:

·         An SQLSTATE value (a 5-character string literal) or a MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate success rather than an error condition. 

Create procedure p1 (in p_s int (2), in p_sname varchar (5), out p_status varchar (30))

Begin

Declare Continue handler for SQLSTATE '23000'

Begin

Set p_status = 'In SQLSTATE 23000';

End;

Insert into s1 Values (p_s, p_sname);

desc s1;

End;

//

Call P1 (2,'name3',@s);

Select @S//

Output

In SQLSTATE 23000

       Field                Type                       Null        Key         Default   Extra

s               int(2)                        NO        PRI         0

sname            varchar(5)               YES        NULL

The example associates a handler with SQLSTATE value '23000', which occurs for a duplicate-key error. Notice that p_status is In SQLSTATE 23000 after the procedure executes, and the describe command shows the structure of the table, which shows that execution continued to the end of the procedure. If the DECLARE ... HANDLER statement had not been present, MySQL would have taken the default path (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint.

·         A condition name previously specified with DECLARE ... CONDITION.

See Declare for Conditions Example Above

·         SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.

·         NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition).

·         This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

·         SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

If you want to ignore a condition, you can declare a CONTINUE handler for it and associate it with an empty block. For example:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

Source:

http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Open Source Database

What is Open Source

Open Source software is available for free, and the source code is also available. This means that you (or others) can see how the software was actually programmed and change it, if you wish. That is, the user is allowed to implement, share and further develop the database software to suit various needs.

The advantage of freely available software is obvious, but why is the source code availability important? Open Source software products are most often the collective effort of many volunteer programmers. Anyone can submit bug fixes, security patches, and the like. This means that bugs get fixed faster, programs are more secure, and, in general, as a program matures, it is of higher quality.

One of the primary disadvantages of open source software is that it can be user-unfriendly, and it may be difficult to find someone with expertise in the software to set it up for an organization.

What are Server-Based Databases

Server-based databases differ from database management systems such as FileMaker Pro and Microsoft Access, which are primarily designed as stand-alone desktop databases for a single user. In contrast, server-based databases, such as MySQL, PostgreSQL, Microsoft SQL Server, and Oracle, are designed to be used on servers instead of on a desktop, and they are meant to be shared by many users. Technically, multiple users can share FileMaker Pro and Access, but as the number of users increases (along with the number of requests for information) performance suffers greatly. That is why a server-based database is a common choice for organizations where many people need to share data.

MySQL and PostgreSQL

MySQL and PostgreSQL are two primary Open Source server-based database management systems in use. Both of these database systems run well on UNIX-based operating systems, such as Linux, as well as Windows. In general, however, UNIX is the better OS for multi-user database applications because of its stability and scalability.

Primarily cost, stability, and security are the reasons to choose an Open Source server-based database management package, rather than, for example, Microsoft's SQL server. Both MySQL and PostgreSQL are freely available, fairly easy to set up, and more secure than Microsoft's SQL Server.

The most common use of server-based DBMS in the nonprofit sector is for Web-based databases. Many nonprofits use server-based DBMS for their interactive Web sites, providing data and content to their constituencies. MySQL is probably the most common of the Open Source database systems used for Web sites because almost all virtual hosting companies provide it.

Another common use of server-based DBMS is to underlie large shared databases, such as client management databases and financial packages.
What if you are already using Access at your organization? You can set up a Linux-based database with either MySQL or PostgreSQL, and use an ODBC (Open Data Base Connectivity) connection between the desktop Access databases and the server database. This means that on the front-end, your users can still use Access, but the back-end is more robust and secure, because it is server-based.

Should you use MySQL or PostgreSQL?

MySQL is simpler and smaller, and therefore generally faster than PostgreSQL. It is also the better choice for Web-based use, as MySQL is most often the only DBMS available in virtual hosting accounts. In addition, there are more users of MySQL, so it would be easier to find someone to help you set it up and work with it.

However, PostgreSQL is much more robust, scalable, and standards compliant and it can handle multiple transactions easily.

In conclusion, both PostgreSQL and MySQL are freely available, stable, robust, and secure database management systems, provide a clear, inexpensive way to design and implement server-based databases. Through a variety of methods, such as Web-based interfaces, Access front ends, or custom designed GUIs, these DBMS can be used easily, in a multi-platform environment.

Database Storage Engines

Database Storage Engines:

Database Storage Engines are programs that are integrated into MySQL database management system to manage data tables. Database Storage Engines are also called Table Types.

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

For example, if you work with a large amount of temporary data, you may want to make use of the MEMORY storage engine, which stores all of the table data in memory. Alternatively, you may want a database that supports transactions (to ensure data resilience).

Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type). By default, MySQL comes with a number of different storage engines pre-configured and enabled in the MySQL server. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.

Benefits of Database Storage Engines Availability

Reliability and concurrency are enhanced with new algorithms for physical file interaction. These algorithms eliminate the need to run database console commands (DBCCs) as part of regular maintenance. However, DBCC is still available, and the new DBCC CHECK commands can be run without inhibiting online processing.

Scalability

The storage subsystem, which consists of the physical database files and their layout on disk, supports scaling from very small to very large databases. SQL Server can now support up to 64 GB of physical memory (RAM) and up to 32 processors.

Ease of use

Enhanced administration capabilities help the Database Administrator (DBA) to automate and centralize server management. This also allows easy maintenance of remote servers and applications without the necessity of having a DBA visit every site. Server configuration, managed by a sophisticated algorithm, is dynamically responsive to server usage patterns, freeing the DBA to concentrate on database management and optimization tasks.

Here are listed a few types of Database Storage Engines

MyISAM Storage Engine – MySQL default storage engine. Based on ISAM database concept. MyISAM is not transaction safe.

Innodb Storage Engine – A transaction safe storage engine developed by Innobase Oy (an Oracle company).

BDB (BerkeleyDB) Storage Engine – A transaction safe storage engine originally developed at U.C. Berkeley. Sleepycat Software, Inc. was created in 1996 to continue BDB development. Sleepycat is an Oracle company now.

CSV Storage Engine – A simple storage engine storing data as text files using comma-separated values format.

MEMORY (HEAP) Storage Engine – A storage engine storing data in computer main memory.

BLACKHOLE – A /dev/null storage engine (anything you write to it disappears)
We also have many other types of Database Storage Engines available for use.

To see the MySQL storage engines available on your server, use the show engines statement:

show engines;

However, in the output, only if DEFAULT or YES appears in the “Support” column is an engine available for use.