Wednesday, June 27, 2012

Virtual Columns (A feature of MariaDB)


The Virtual columns are computed columns. They will be computed based on information inserted/Updated.

In MariaDB two types of virtual columns are available.

  1. VIRTUAL virtual columns
  2.  PERSISTENT virtual columns

Virtual virtual columns as truly virtual; their value is not stored and they have no actual existence apart from the table definition. They act like regular columns in queries, but their content is always calculated on the fly and never written to disk. You might be thinking that this could lead to a performance penalty; so to help with performance, if an SQL query doesn’t reference a virtual virtual column, the value is not calculated.

Persistent virtual columns are half-way between being truly virtual and being regular columns. The main difference compared to virtual virtual columns is that the calculated data is actually stored in the database.


The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:



Create table to store data

MariaDB [virtest]>  create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0,  above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual);

Query OK, 0 rows affected (0.01 sec)



Now inserted a data row..

MariaDB [virtest]> insert into salespeople(salesperson_id, `date`, sold, day_avg) values (1,now(),300,150);

Query OK, 1 row affected (0.01 sec)




Selected data from table…

MariaDB [virtest]> select * from salespeople;

+----+----------------+---------------------+--------+---------+-----------+

| id | salesperson_id | date                | sold   | day_avg | above_avg |

+----+----------------+---------------------+--------+---------+-----------+

|  1 |              1 | 2012-06-27 20:07:22 | 300.00 |  150.00 | Y         |

+----+----------------+---------------------+--------+---------+-----------+





Now updating row with new data..



MariaDB [virtest]> update salespeople set sold = 149 where id = 1;

Query OK, 1 row affected (0.09 sec)

Rows matched: 1  Changed: 1  Warnings: 0



See the result after update



MariaDB [virtest]> select * from salespeople;

+----+----------------+---------------------+--------+---------+-----------+

| id | salesperson_id | date                | sold   | day_avg | above_avg |

+----+----------------+---------------------+--------+---------+-----------+

|  1 |              1 | 2012-06-27 20:07:22 | 149.00 |  150.00 | N         |

+----+----------------+---------------------+--------+---------+-----------+



Now you can calculate column value at runtime. 

Enjoy J

Tuesday, June 26, 2012

Find orphaned users in SQL Server

set nocount on
go
 
DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT 
name 
FROM
master.dbo.sysdatabases 
WHERE
name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW')
OPEN Curse
 
FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'USE '+ @databasename + ' 
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login auto_fix, '' + @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs'
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse


Codd's Rule (Defining a RDBMS)


Rule (0):

The system must qualify as relational, as a database, and as a management system.
For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.


Rule 1: The information rule:
All information in the database is to be represented in only one way, namely by values in column positions within rows of tables.


Rule 2: The guaranteed access rule:

All data must be accessible. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.


Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.


Rule 4: Active online catalog based on the relational model:
The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.


Rule 5: The comprehensive data sublanguage rule:
The system must support at least one relational language that

1.    Has a linear syntax

2.    Can be used both interactively and within application programs,

3.    Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval)

4.    Security and integrity constraints, and transaction management operations (begin, commit, and rollback).


Rule 6: The view updating rule:
All views that are theoretically updatable must be updatable by the system.


Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.


Rule 8: Physical data independence:
Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.


Rule 9: Logical data independence:
Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.


Rule 10: Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.


Rule 11: Distribution independence:
The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully :
when a distributed version of the DBMS is first introduced; and
when existing distributed data are redistributed around the system.


Rule 12: The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.

Useful MySQL Command to troubleshoot Database




1. Display available databases

Below command will show all database available on this instance.

> mysqlshow  -uroot –p



2. Display all tables in a database

Below command will display all the tables located under test database

> mysqlshow  -u root –p test



3. Display tables along with number of columns in a database

> mysqlshow  -v -u root -p test



4. Display total number of columns and rows of all tables in a database

Please note there are two -v in the following command.

> mysqlshow  -v -v -u root -p test



5. Display all columns of a table

In the following example, it displays the entire available column name along with additional column information for accounts table in test database.

> mysqlshow  -u root -p test test



6. Display details about a specific column from a table

In this example, it displays information about id column from test table.

> mysqlshow  -u root –p test test id



7. Show all metadata information about a table

> mysqlshow  -i  -uroot -p test test



8. Display both indexes and columns of a table

Please note that the indexes are listed at the bottom of the display after the column information.

> mysqlshow -k -u root -p test test

Database: test  Table: test



9. Display only indexes and not columns of a table

This can be done by tricking the mysqlshow, by giving an invalid column name. Since invalid_col_name doesn’t exist on accounts table, the following command will display only the indexes of test table.

> mysqlshow -k -u root -p test test invalid_col_name

  Database: test  Table: test Wildcard: invalid_col_name

Thursday, June 21, 2012

Data Type Comparison (Oracle and MySQL)


Whenever you work Database design, Database Migration, You should be aware about data type, Storage required by data type.   Whenever you work on migration, you should know the equivalent data type between existing definitions.
Below is the quick comparison of Oracle Data type with MySQL Data type.
Default Data Type Mappings
MySQL Data Type
Oracle Data Type
BIGINT
NUMBER(19, 0)
BIT
RAW
BLOB
BLOB, RAW
CHAR
CHAR
DATE
DATE
DATETIME
DATE
DECIMAL
FLOAT (24)
DOUBLE
FLOAT (24)
DOUBLE PRECISION
FLOAT (24)
ENUM
VARCHAR2
FLOAT
FLOAT
INT
NUMBER(10, 0)
INTEGER
NUMBER(10, 0)
LONGBLOB
BLOB, RAW
LONGTEXT
CLOB, RAW
MEDIUMBLOB
BLOB, RAW
MEDIUMINT
NUMBER(7, 0)
MEDIUMTEXT
CLOB, RAW
NUMERIC
NUMBER
REAL
FLOAT (24)
SET
VARCHAR2
SMALLINT
NUMBER(5, 0)
TEXT
VARCHAR2, CLOB
TIME
DATE
TIMESTAMP
DATE
TINYBLOB
RAW
TINYINT
NUMBER(3, 0)
TINYTEXT
VARCHAR2
VARCHAR
VARCHAR2, CLOB
YEAR
NUMBER

Numeric Types
MySQL
Size
Oracle
BIGINT
8 Bytes
NUMBER (19,0)
BIT
approximately (M+7)/8 Bytes
RAW
DECIMAL(M,D)
M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
FLOAT(24), BINARY_FLOAT
DOUBLE
8 Bytes
FLOAT(24), BINARY_FLOAT, BINARY_DOUBLE
DOUBLE PRECION
8 Bytes
FLOAT(24), BINARY_DOUBLE
FLOAT(25<=X <=53)
8 Bytes
FLOAT(24), BINARY_FLOAT
FLOAT(X<=24)
4 Bytes
FLOAT, BINARY_FLOAT
INT
4 Bytes
NUMBER (10,0)
INTEGER
4 Bytes
NUMBER (10,0)
MEDIUMINT
3 Bytes
NUMBER (7,0)
NUMERIC
M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
NUMBER
REAL
8 Bytes
FLOAT(24), BINARY_FLOAT
SMALLINT
2 Bytes
NUMBER(5,0)
TINYINT
1 Byte
NUMBER(3,0)

Date and Time Types
MySQL
Size
Oracle
DATE
3 Bytes
DATE
DATETIME
8 Bytes
DATE
TIMESTAMP
4 Bytes
DATE
TIME
3 Bytes
DATE
YEAR
1 Byte
NUMBER


String Types
MySQL
Size
Oracle
BLOB
L + 2 Bytes whereas L<2^16
RAW, BLOB
CHAR(m)
M Bytes, 0<=M<=255
CHAR
ENUM (VALUE1, VALUE2, ...)
1 or 2 Bytes depending on the number of enum. values (65535 values max)

LONGBLOB
L + 4 Bytes whereas L < 2 ^ 32
RAW, BLOB
LONGTEXT
L + 4 Bytes whereas L < 2 ^ 32
RAW, CLOB
MEDIUMBLOB
L + 3 Bytes whereas L < 2^ 24
RAW, BLOB
MEDIUMTEXT
L + 3 Bytes whereas L < 2^ 24
RAW, CLOB
SET (VALUE1, VALUE2, ...)
1, 2, 3, 4 or 8 Bytes depending on the number of set members (64 members maximum)

TEXT
L + 2 Bytes whereas L<2^16
VARCHAR2, CLOB
TINYBLOB
L + 1 Bytes whereas L<2 ^8
RAW, BLOB
TINYTEXT
L + 1 Bytes whereas L<2 ^8
VARCHAR2
VARCHAR(m)
L+1 Bytes whereas L<=M and0<=M<=255 before MySQL 5.0.3 (0 <= M <= 65535 in MySQL 5.0.3 and later; effective maximum length is 65,532 bytes)
VARCHAR2, CLOB