MySQL Server

47K

Solutions

22K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this type of code.
1
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began appearing in 2006-7 and as of MySQL version 8.0 and MariaDB version 10.2.2. 


Common Table Expression ( the WITH RECURSIVE … clause )

Recursive SQL is achieved by using Common Table Expression or CTE which is a temporary named resultset, derived from a simple query and defined within the execution scope of SELECT, INSERT, UPDATE or DELETE statements. CTE is often used to improve readability of a SQL code by simplifying and/or prise the code into more readable sections. One “feature” of CTE is the ability to reference recursively the named resultset, hence recursive SQL. 


With recursive SQL queries you can achieve things you would not have imagined being possible with this type of SQL and at the speed it executes. You can solve many types of business problems and even rewrite some complex SQL/application logic down to a simple recursiveSQL-call to the database. 

Some example of useful usage of recursive CTE is that you can use it to find gaps in data, create organization charts and create test data.

What I like most of using recursive SQL query is the ability to produce large amount of test-data in amount of seconds or few minutes, depending on complexity of the data and the available database/systems resources. Using recursive CTE we can produce many hundreds, thousands or millions of records in very short period of time which is then only limited to available database memory and other database/system resources. Recursive query produces test data way faster compared to other test-data procedures that I have seen and experienced.
Also, it has been proven that recursive queries outperforms other queries that take days to execute on huge amount of data by running in several minutes.


The word recursive says it all. You have a query that repeatedly calls itself with some starting point and that which is EXTREMELY IMPORTANT an ending point (a fail-safe exit as I call it). If you don't have a fail-safe exit or your recursive formula goes beyond it you are in deep trouble. Your query will go into aninfinite loop resulting in very high CPU and very high LOG utilization which will lead to memory and/or storage exhaustion. If your query goes haywire you must think very fast and stop it. If you are unable to do so then alert your DBA immediately so he/she can prevent the database system of choking by killing the runnaway thread. 


Examples

Let's look at some simple examples that shows how easy and effective it can be. 


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mydates
                                        where level < 100000
                                        )
                                        SELECT nextdate from mydates
                                        );


Here we produce 100k of  random dates. The level is our counter and fail-safe exit to safely exit the recursive query. The  line ( no 2 ) is our starting point and the line (no 4-5 ) is the recursive call with the ending point in the where clause ( no 6) . Then the last lines ( no 8 - 9 ) is the call to execute the recursive query and retrieve the data. 

The above query executed in 0.84 sec and outputted 100k of rows. Note that even though you put a LIMIT 10   on the query  as shown below it will first produce the 100k rows and then limit the result to the first 10 rows in the same amount of time or 0.84 sec


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mydates
                                        where level < 100000
                                        )
                                        SELECT nextdate from mydates
                                        ) LIMIT 10;


You can also create a table and by that physically store the data by surrounding the CTE query like this. 


create table somedates as (
                                        with RECURSIVE mynumbers (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mynumbers
                                        where level < 100000
                                        )
                                        SELECT nextdate from mynumbers
                                        );

This query ran on my laptop in 2.93 seconds storing 100k rows of dates in a table called somedates.


Another simple example, a “gaps in data” solution,  which produces last 1000 dates to current date and compares it to the order table to find all dates that don't have any orders showing the newest date first.

For this example I created 1 million rows with random orderdates using recursive SQL in 32.95 seconds. Created index on (orderdate,orderid) on the the table in 9.12 seconds, deleted thousands of random rows to create a gap and ran the below query


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, ADDDATE(CURRENT_DATE,INTERVAL -1000 DAY) nextdate from DUAL
                                        union all 
                                        select level+1, ADDDATE(nextdate,INTERVAL 1 DAY) nextdate
                                        from mydates
                                        where level < 1000
                                        )
                                        SELECT md.nextdate from mydates md left join myorders od on md.nextdate = od.order_date
                                        where od.order_date is null order by md.nextdate desc;


This query ran on my test data and looked for gaps in the last 1000 days from current date and returned 68 dates in 0.05 seconds


Conclusion

Here I have talked briefly about recursive queries and showed how useful and powerful it can be to solve problems in very short and effective way. However, when writing the recursive query be VERY careful NOT to forget the fail-safe exit and make sure you double check twice that the calculations used in the fail-safe exit produces a stop signal. 

0
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
0
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
0
In this article, we’ll look at how to deploy ProxySQL.
0
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
1
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
1
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
2
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
1
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
0
Free recovery tool for Microsoft Active Directory
LVL 1
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
0
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.
0
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
0
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
3
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective solution is to partition the data into more manageable parts.
Modern databases (including MySQL and MariaDB) have and offer a feature called table partitioning which reduces query execution times and makes the data more manageable. In this article I assume you have MySQL 5.6 or newer or MariaDB 10.0.4 or newer.

What is Table Partitioning?


Table partitioning is a data organization scheme to divide large quantity of data into multiple data storage (data partitions). The whole purpose of table partitioning is to divide the table data into smaller and more manageable data units, shorten reorganization needs/ -time and speed up inserts/updates/deletes and queries execution time that on regular tables becomes slower as the data in the table grows.


Partitioning types


MySQL and MariaDB (as does most modern databases) offer several types of table partitioning. These types are


Range and range columns partitioning (the most commonly used),

create table mytable ( id int(11) NOT NULL, ….. )

partition by range ( id ) (

partition p0 values less than ( 10000),

partition p1 values less than ( 20000), ….

);

Open in new window


List and list columns partitions,

create table 

Open in new window

1
Introduction
This article is intended for those who are new to PHP error handling.  It addresses one of the most common problems that plague beginning PHP developers: effective error visualization.

PHP error handling is well-documented in the online man pages, but the documentation often eludes beginners, who are trying to learn PHP by copying examples they found on the internet.  Copying the code without understanding the code is an antipractice, and there are so many bad PHP examples out there, it can sometimes be difficult to find good ones!  This article will help you get the most, quickly and easily, out of PHP's basic error handlers.  You can get excellent error information, informative and well-targeted, by just following a few simple examples.  So stop copying those obsolete internet examples, and instead make a few "teaching examples" for your own library.  By the time you've finished this article, you will have some great insights into how to diagnose the most common errors quickly and find good solutions.

What's Covered Here
PHP has unlimited opportunities for errors, but in practice only a few things are needed to get good diagnostics.  You need to be able to see the errors in PHP and you need to …
3
 
LVL 111

Author Comment

by:Ray Paseur
Comment Utility
Thanks, Martin.

Some time ago when I published articles, there was a cascade of approvals - something like "It's OK" then "It's good" then "It's really good" and each of these approvals gave some more points as well as comments about how the article can be made better.  Does E-E still do that?
0
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logout, and Easy Access Control.
3
load balancing
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
0
Creating and Managing Databases with phpMyAdmin in cPanel.
0
Veeam and MySQL: How to Perform Backup & Recovery
LVL 1
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to do amazing, modern things with bits!
27
 
LVL 35

Author Comment

by:gr8gonzo
Comment Utility
@philkryder - The impact is going to be different depending on the application. For example, let's say that you do have this situation where you're dealing with 20 million 15-byte strings (roughly 286 megabytes).

If you're dealing with a database, then typically you're in a client/server setup, so the client needs the data locally in order to do any viewing or processing (this can be expanded also to a scenario where you've got DB Server -> Web Server -> End User, and data has to be transferred twice - and that's just basic setups).

Typically, the data's going to be transmitted over a TCP/IP network connection, so you're also adding about 8 megs of TCP overhead for 300,000,000 bytes (versus 2 megabytes for 80,000,000 bytes). You also have overhead of the structure containing/defining the data, plus any extra identifying data, but we'll set that aside for now.

So let's say that the database server has all the resources it needs to send all the data across to the client. On a LAN, it might not take too long either way, but you'll still notice a significant difference between transferring 78-ish megs vs 294-ish megs.

If you're dealing with a situation where you transfer that over a broadband connection of some kind, the difference will be even larger.

So the majority of the time, your "extra time" is going to be found in the data transfer, since that's often the slowest point. For the sake of having some example numbers, let's say that we're transferring over a 60Mbps connection, so we're looking at anywhere from 30 seconds - 45 seconds to download a roughly-300 meg payload, and 10-15 seconds to download a roughly 80-meg payload.

We'll say that we can save about 20-30 seconds in transfer time.

Now let's say the client app finally has the data in memory. It probably needs to store it somewhere, so there's some kind of structure involved, which also means an extra % of overhead storage in memory. Let's say you have a C# app using a DataTable structure. A string column in a DataTable takes up a LOT more memory per row than a long column. I don't have hard numbers in front of me, but I did have a project once where I hadn't defined my column type and I was accidentally storing small integers as a string. When I fixed the problem with a byte type definition on the field, memory usage dropped by several hundred megabytes.

So now we've got a client app with the data in memory. What's the next step?

If we're displaying the data on-screen, then chances are that you're probably only converting a handful of records at a time - maybe 200 at most.  The time it takes to convert 200 longs into string is around 0.001 seconds each time (just a rough test on my end).

But let's say you want to convert those 20 million numbers into 20 million entries all at once to be written to a file. Let's say that looping through 20 million records in memory takes a full 3 seconds, with absolutely no processing at all, so it wouldn't matter if the numbers were already converted or not - 3 seconds is the starting baseline. Your question is then - how much ADDITIONAL time would it take to convert 20 million numbers? A rough test on my machine shows about 40 seconds, and of course, that's going to depend on processor power. Many machines might be even slower.

So now we have some example numbers.
You have about 20-30 seconds of savings on the transfer side. If you were to turn around and convert all of those to strings immediately and write them to a file, then you're probably looking at a speed LOSS of roughly 10 seconds. Of course, this also doesn't take anything else into consideration (the value of bandwidth pipes / network saturation / memory / likelihood of this scenario / scalability across multiple clients / etc).

For displaying the data on-screen, you have about 20-30 seconds of savings on the transfer side, and virtually no time spent on conversion since you're not doing them all at once. The client's not going to notice a split-second of extra time every time they move to a new page of results.

Either way, there is a LOT of extra value in more efficient storage. There will always be some scenarios where it doesn't make sense to compact everything, but that's where being a good programmer comes into the picture and understanding the business case and how it translates into data flows and the value of each resource involved, and how that multiplies with concurrent load.
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
Comment Utility
Excellent article!  The only thing I would have included would be a small section on Endianness (Big Endian vs. Little Endian).
0

Introduction

Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the principles and examples of the original article on a 1:1 basis.  As I created the examples for this article, I came across some anomalies in the PHP OOP DateTime implementation.  Here are some of my impressions and some notable gotcha's that I found.


It is possible to create an infinite loop with date/time arithmetic.  Your code will look right, but transitions at the boundaries of daylight savings time will change the values in your DateTime objects.  I hope PHP will fix this.

See: http://php.net/manual/en/datetime.sub.php#114780


When you create a new DateTime object, you can assign both a date/time string and a timezone.  If you create the DateTime object from a Unix timestamp, the timezone cannot be used in the constructor.  You must assign the timezone to the object separately, after the object is created.


The ISO-8601 format for DateTime::format() method is not 100% ISO-8601 compatible.  You can use DateTime::ATOM instead if you need perfect compatibility.


The designator for a Unix timestamp in DateTime::__construct() is the at-sign ("@") prepended to the numeric Unix timestamp.  The at-sign ("@") is also the PHP error-control operator, but it has no error-control meaning in the DateTime constructor.


There is no avoiding arithmetic - DateTime::getOffset() returns its answer in seconds, but functions that use this information may expect minutes or hours.


I found no convenient way to instantiate a DateTime object and get a formatted date/time string in a single line of code.  The marriage of PHP date() and strtotime() was a convenience that is not here any more.  I'll update this article if I find a good solution.


At PHP 7.1+ the DateTime constructor incorporates microseconds when the object is constructed from the current time.  This may increase the risk that two DateTime objects will be unequal.


Now the good news.  Most of what you learned about date formatting with the date() format parameters is still applicable.  You will write a little more code with the OOP notation, but that is common for OOP programming and may help to improve readability.  And none of the common tasks we use in procedural PHP is impeded by the OOP DateTime notation.  A list of the most useful references is included at the end of this Article.


The DATE/TIME Tower of Babel

Human beings can read dates and times in a variety of ways.  We readily understand such things as September 5th, 2010, and we know that it comes before November 11th, 2010,and after May 9th, 2010.  We have shorthand conventions that let us write things like 9/5/2010 or the military correspondence format 5 Sep 2010.  But when we try to process dates and times in a computer program, this variety of formats becomes a jumble of confusion.  In response to the confusion, the International Organization for Standards was moved to publish a standard in 1988.  The standard has been revised and extended somewhat since the original publication.  The only thing that mystifies students of history is why it took so long to promulgate a standard.


Toward a Universal DATETIME Notation

Date formats for computer processing are prescribed by the ISO-8601 standard.  The ISO-8601 standard places all the information in fixed-width data strings with leading zeros where needed.  When you read ISO-8601 standard information you notice immediately that everything lines up in columns.  The larger values are to the left and progressively smaller values are to the right, starting with Year, then Month, then Day, then Hour, etc.  Date/time information formatted according to the standard is very useful because it is both easy to read and easy to understand in comparisons and computations.  For example, the date '2010-09-05' is a valid ISO-8601 string meaning September 5th, 2010.  Imagine how difficult it would be to write programming that works with dates in the text formats, or dates that are formatted like this: 05.09.2010.  Does that mean May 9th, 2010 or September 5th, 2010?  Fortunately the ISO-8601 standard removes the ambiguity.


The ISO-8601 standard removes the ambiguity about the time of day, as well.  All of the hours are represented on a 24-hour clock, so there is no question about what "two o'clock" might mean.  The string 0200 or 02:00:00 refers to 2:00 a.m.  If you're thinking of 2:00 p.m. your ISO-8601 standard will use 14:00:00.


This link gives a good discussion and examples of permissible variations on the ISO-8601 format.

See http://en.wikipedia.org/wiki/ISO_8601


The Value of PHP time() is Always the Same

No matter where in the world you stand, the value of time() is always the same.  The PHP function time() returns the number of seconds since the Unix Epoch.  While local times may differ, time() ticks on second-by-second.  Run this script to see the effect.  Knowing this, we can do arithmetic with seconds, and then return human-readable date and time values that are sensible for different locations around the world.

<?php
                                        /**
                                         * Different Timezones share the same Unix Timestamp
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        // TIMEZONES AROUND THE GLOBE
                                        $locations
                                        = array
                                        ( 'Pacific/Auckland'
                                        , 'Australia/Sydney'
                                        , 'Australia/Perth'
                                        , 'Asia/Tokyo'
                                        , 'Asia/Calcutta'
                                        , 'Asia/Tel_Aviv'
                                        , 'Africa/Cairo'
                                        , 'Europe/Rome'
                                        , 'Europe/London'
                                        , 'Atlantic/Bermuda'
                                        , 'America/Chicago'
                                        , 'America/Anchorage'
                                        , 'Pacific/Honolulu'
                                        , 'UTC'
                                        )
                                        ;
                                        // ITERATE OVER THE TIMEZONES
                                        foreach ($locations as $location)
                                        {
                                            // SET OUR ZONE AND LOCAL TIME
                                            $zoneobj = new DateTimeZone($location);
                                            $dateobj = new DateTime(NULL, $zoneobj);
                                        
                                            // SHOW THE LOCATION AND THE CURRENT DATE / TIME
                                            echo PHP_EOL;
                                            echo str_pad($location, 24, ' ');
                                            echo $dateobj->format(DateTime::RSS);
                                        
                                            // SHOW THE NUMBER OF SECONDS SINCE THE EPOCH, RECOMPUTED IN EACH TIMEZONE
                                            echo ' ';
                                            echo $dateobj->format('U');
                                        }


Handling External Input

Whenever you accept a date/time string from an external data source, what is the first thing you should do?  Turn it (immediately) into an instance of the DateTime object.  The DateTime constructor can turn almost any English language human-readable date/time text into an object for use in DateTime computations and displays. Like the PHP function date(), the DateTime::format() method can turn DateTime information into a formatted date. 

<?php 
                                        /**
                                         * The DateTime constructor may throw an Exception
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $external = 'Yesterday';
                                        try
                                        {
                                            $dateobj = new DateTime($external);
                                        }
                                        catch (Exception $e)
                                        {
                                            // ON FAILURE, SHOW THE EXCEPTION
                                            var_dump($e);
                                        }
                                        echo PHP_EOL . $dateobj->format(Datetime::ATOM);
                                        echo PHP_EOL;

Once you have done that internal date format conversion your programming will handle internal date/time computations easily.


When you are storing date/time values, you may want to store them in the ISO-8601 format strings or in the form of Unix timestamps.  I prefer to use the formatted strings - they are easier to understand when you're debugging.


Handling External Input that is Invalid

The PHP DateTime constructor can turn virtually any human-readable date / time text into a DateTime object - but it won't work on everything.  When it fails to make the conversion, it throws Exception.  You can and should test for this.


<?php
                                        /**
                                         * Invalid date/time strings can be detected when you catch the Exception
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $external = 'Nonsense';
                                        $dateobj  = FALSE;
                                        try
                                        {
                                            $dateobj = new DateTime($external);
                                        }
                                        catch (Exception $e)
                                        {
                                            // ON FAILURE, SHOW THE EXCEPTION
                                            var_dump($e);
                                        }
                                        if ($dateobj)
                                        {
                                            $iso_datetime = $dateobj->format(Datetime::ATOM);
                                            echo "SUCCESS: $external EQUALS ISO-8601 $iso_datetime";
                                        }
                                        else
                                        {
                                            echo "ERROR: I DO NOT UNDERSTAND $external";
                                        }


Handling External Input that is Formatted (contrary to the disambiguation rules)

The PHP DateTime Constructor works much the same way as strtotime(), making some assumptions and obeying certain rules about the format of the date / time input string.  But what if you have dates that are not formatted according to the rules?  One of the common issues arises when we have dates that are in European order d-m-y, but have been written with slashes, implying American order m/d/y.  This may result in incorrect output when the values for m and d  are both in the range from 1 - 12, and will cause an exception when m exceeds 12.  Fortunately PHP has a way around this issue, in the static constructor createFromFormat().  We can tell PHP what format to use as it interprets the date.  This example shows how.


<?php
                                        /**
                                         * Date values separated by slash are assumed to be in American order: m/d/y
                                         * Date values separated by dash are assumed to be in European order: d-m-y
                                         * Exact formats for date/time strings can be injected with createFromFormat()
                                         *
                                         * http://php.net/manual/en/datetime.createfromformat.php
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        // THIS WOULD IMPLY MONTH == 19, OBVIOUSLY INVALID
                                        $external = "19/10/2016 14:48:21";
                                        
                                        // HOWEVER WE CAN INJECT THE FORMATTING WHEN WE DECODE THE DATE
                                        $format = "d/m/Y H:i:s";
                                        $dateobj = DateTime::createFromFormat($format, $external);
                                        
                                        $iso_datetime = $dateobj->format(Datetime::ATOM);
                                        echo "SUCCESS: $external EQUALS ISO-8601 $iso_datetime";


Interesting Forms of External Input

All of these external inputs work correctly with the DateTime constructor and this gives you and your clients powerful ways of talking about dates and computing with dates.

- 3 hours

tomorrow

tomorrow midnight

tomorrow 1:35pm

March 15, 1986

yesterday

yesterday + 1 week

next year

now

now + 627 hours 15 minutes

last Tuesday

third Wednesday

3 minutes, 15 seconds


Producing "Pretty" Dates From DateTime Objects ISO-8601 Dates

When you are ready to present the dates to people and you want them to see nicely formatted dates, you can use the DateTime::format() method to do the reformatting.  Let's say you have this timestamp value: 1,284,593,400 (which equals the ISO-8601 date: '2010-09-15T18:30:00-0500'. )  Maybe you got it out of your data base or computed it in your script.  It's meaningful, but not what you expect when you open the envelope.  "Please join us for cocktails on Unix Timestamp 1,284,593,400."  How can we get something that would be appropriate on an invitation?  Here is how you might do the conversion from a timestamp or ISO-8601 date to the pretty date.

<?php 
                                        /**
                                         * The DateTime::format() can change the rendering of date values and produce pretty dates
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $iso_date = '2010-09-15T18:30:00';
                                        $formats  = 'l, F jS, Y \a\t g:ia';
                                        $dateobj  = new DateTime($iso_date);
                                        echo PHP_EOL . $dateobj->format($formats);

Outputs "Wednesday, September 15th, 2010 at 6:30pm" -- very civilized!


Breaking down the date formatting, we have the following (all documented on PHP.net)...

$pattern
                                        = 'l, '    // LOWERCASE L - text name of the day of the week
                                        . 'F '     // UPPERCASE F - text name of the month
                                        . 'j'      // LOWERCASE J - day of the month
                                        . 'S, '    // UPPERCASE S - ordinal like the letters in 1st, 2nd, etc 
                                        . 'Y '     // UPPERCASE Y - 4-digit year
                                        . '\a\t '  // ESCAPED LETTERS... More to follow
                                        . 'g:'     // LOWERCASE G - hours on a 12-hour clock
                                        . 'i'      // LOWERCASE I - minutes 
                                        . 'a'      // LOWERCASE A - designator of "am" or "pm"
                                        ;


You might envision several patterns that you need in your application.  Most PHP sites and services are written with a central "common.php" file that gets included into all the specialized scripts.  You could prepare the formatting patterns in the common script and refer to them by a variable name, or define the patterns as constants.

define('MILITARY_CORRESPONDENCE', 'j M y');
                                        define('SHORT_8601', 'Y-m-d');
                                        define('COCKTAILS', 'l, F jS, Y \a\t g:ia');


What about the escaped letters?  Like the date() function, the DateTime::format() uses letter patterns as signals to describe the conversion into human-readable character strings, so some letters have special relevance and some do not.  Any character that is not used as a signal in the date pattern string is returned from the function unaltered.  Punctuation is one example - commas and hyphens go right through.  But if we want to get one of the signal letters back, we must tell PHP to ignore the signal and just return the letter.  We do that with the back-slash character, as shown here.  Try running these two lines of code to see how the escape can be useful:

echo PHP_EOL . date('The year is: Y');
                                        echo PHP_EOL . date('\T\h\e \y\e\a\r\ \i\s\: Y');


Setting Your Own Clock Values

Since the values contained in the DateTime object are usable in many different time zones, you want to have control over the way PHP interprets the time zone.  You control this time with the DateTimeZone object.  You can set the DateTimeZone in the DateTime constructor, or you can inject a new DateTimeZone into a DateTime object via the setTimeZone() method.  Here is an example that I use, because my server is located in the midwest at ChiHost.com.


<?php
                                        /**
                                         * DateTime objects are usually aware of time zones via the DateTimeZone object
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $string  = 'Now';
                                        $zoneobj = new DateTimeZone("America/Chicago");
                                        $formats = 'l, F jS, Y \a\t g:ia (e)';
                                        $dateobj = new DateTime($string, $zoneobj);
                                        echo PHP_EOL . $dateobj->format($formats);
                                        
                                        // WHAT TIMEZONE SHOULD I USE?
                                        $tz_ids = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, 'US');
                                        foreach($tz_ids as $zone)
                                        {
                                            echo PHP_EOL . $zone;
                                        }


Special Meanings for NOW and TODAY

We know how to convert external date/time strings into DateTime objects, and we know how to return them to human-preferred formats with DateTime::format().  Now we can return to some of the special meanings of date/time strings.  NOW and TODAY are particularly useful.

<?php 
                                        /**
                                         * NOW and TODAY have special meanings in the DateTime object; NOW includes time of day
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $date = new DateTime('Now');
                                        echo PHP_EOL . $date->format(DateTime::RSS);
                                        
                                        $date = new DateTime('Today');
                                        echo PHP_EOL . $date->format(DateTime::RSS);
                                        
                                        $date = new DateTime('Now', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $date->format(DateTime::RSS);
                                        
                                        $date = new DateTime('Today', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $date->format(DateTime::RSS);

The principal difference between NOW and TODAY is the time of day.  TODAY is always today's local date at midnight, with hour, minute and second equal to zero.  NOW is today's date including the current time of day.  So in our September example, NOW and TODAY have UNIX timestamp values equal to 1,284,593,400 and 1,284,526,800 respectively.  The difference is 66,600.  This is the number of seconds from midnight to 6:30pm (18 hours plus 30 minutes).  


This script shows that the Unix timestamp is consistent, no matter what timezone you have specified.

<?php 
                                        /**
                                         * The Unix Timestamp values for NOW and TODAY are the same, no matter what timezone
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $dateobj = new DateTime('Now');
                                        echo PHP_EOL . $dateobj->format('U');
                                        
                                        $dateobj = new DateTime('Today');
                                        echo PHP_EOL . $dateobj->format('U');
                                        
                                        $dateobj = new DateTime('Now', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $dateobj->format('U');
                                        
                                        $dateobj = new DateTime('Today', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $dateobj->format('U');


Computing the Difference Between DateTime Objects

This script shows how to compute the difference between two DateTime objects, using the diff() method.


<?php 
                                        /**
                                         * Computing the difference between NOW and TODAY in different timezones
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        // TESTING UTC
                                        $zoneobj    = new DateTimeZone('UTC');
                                        $date_now   = new DateTime('Now',   $zoneobj);
                                        $date_today = new DateTime('Today', $zoneobj);
                                        $date_diff  = $date_now->diff($date_today);
                                        $print_diff = $date_diff->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . $zoneobj->getName();
                                        echo PHP_EOL . $print_diff;
                                        echo PHP_EOL;
                                        
                                        // TESTING ANOTHER ZONE
                                        $zoneobj    = new DateTimeZone('America/New_York');
                                        $date_now   = new DateTime('Now',   $zoneobj);
                                        $date_today = new DateTime('Today', $zoneobj);
                                        $date_diff  = $date_now->diff($date_today);
                                        $print_diff = $date_diff->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . $zoneobj->getName();
                                        echo PHP_EOL . $print_diff;
                                        echo PHP_EOL;
                                        
                                        // USING METHOD CHAINING FOR TIGHTER CODE
                                        $print_diff  = $date_now->diff($date_today)->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . $zoneobj->getName();
                                        echo PHP_EOL . $print_diff;
                                        echo PHP_EOL;


As you can see, it is important to be consistent about your timezone.  You can deal with this phenomenon in one of two ways.  You can set your timezone to UTC.  Or you can set your timezone to a reasonable setting for the location of your server.  I prefer the latter.  See http://php.net/manual/en/timezones.php and http://php.net/manual/en/timezones.others.php for more.


Time Without Date

Let's say we want to know the elapsed time between two events, and we want to express it in notation like HH:MM:SS. DateTime::diff() and DateTime::format() can help with this.

<?php 
                                        /**
                                         * Computing the difference between two times
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $alpha = "2:30:47pm";
                                        $omega = "3:43:16pm";
                                        $date_alpha = new DateTime($alpha);
                                        $date_omega = new DateTime($omega);
                                        $elapsed    = $date_omega->format('U') - $date_alpha->format('U');
                                        $date_diff  = $date_omega->diff($date_alpha);;
                                        $print_diff = $date_diff->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . "TWIXT $alpha AND $omega THERE ARE " . number_format($elapsed) . " SECONDS";
                                        echo PHP_EOL . "RIGHT ELAPSED TIME IS $print_diff";


Leap Year and Daylight Savings Time

These are amazingly easy in PHP.  But beware of a bug in DateTime::add(), illustrated in this code.


3

Why snapshot as backup-strategy?

When working with large to huge databases it is critical to take a backup of the database for disaster recovery and data failures that application either cannot recover or regenerate corrected data. For a database of such size it is vital that backup time is as short as possible to minimize application downtime/disruption due to backups. Regular backups can take several hours or more to complete for very large to huge databases.
The fastest way today to take a database backup is using the snapshot technology (sometimes called FlashCopy) either on a filesystem level or if the database has such technology implemented.


ZFS filesystem and snapshots

ZFS uses copy-on-write transactional object model allowing old data to be retained as new data is written and allowing snapshot versions of the filesystem to be maintained.
Here I will demonstrate how you can use the Solaris zfs filesystem (ZFS on FUSE) to backup MySQL/MariaDB database in as short time as possible.


Preparing the VM

For this demonstration I'm using Centos 6.5 64 bit with zfs-fuse in a VirtualBox VM with 1 Core (2.3 MHz AMD A10 ) and 1GB RAM, even though recommended minimum for zfs-fuse is 2 GB.
The VM disks (VDI format) are as follows
  • 20 GB disk with the Centos 6.5 system installed.
  • Three empty 5 GB disks that will be under the zfs filesystem.  
First thing is to make sure the SELINUX is disabled and reboot the VM after editing the config file as root :

Open in new window

0

Introduction: When a new description on subject like subqueries is read, with various types and each with unique features, often readers try to make a mental picture of the process. Such a mental picture as a point of reference becomes very helpful when one attempts to apply the techniques to solve a problem. This is why the old saying "One Picture is Worth a Thousand Words" holds true. 


I have prepared the content in this article with visual presentation followed by descriptions and samples to make the path much easier for those interested in having a good understanding of the world of subqueries and retention of techniques to apply them later. Your comment below and/or indicating if this article was helpful is very much appreciated and could be used to improve my future work.


You may download Northwind.bak to restore in your local SQL Server from https://northwinddatabase.codeplex.com to practice or test most of the examples included in this article.  Also, consider printing figure 1 below to keep it handy for reference as you are reading the article.


0x.pngIn T-SQL we can use the results returned from one query in another. The embedded or referencedsubquery (inner query) returns required data to enable the outer query to perform more complex operations compared to a simple select clause. Below is the list of different subqueries discussed later in detail:


1) Self-contained subqueries (embedded).

2) Correlated subqueries (embedded).

3) Derived Table Expressions (embedded).

4) Common Table Expression, CTE (referenced).

5) Views and Inline Table-Valued Functions (referenced).


To reference a Table Expression (or an encapsulated named query ) that is executed prior to its use at referenced points in an outer query and/ or in an intermediate table expression. The major advantage of using a named table expression is to facilitate recursive operation and to be referenced more than once in a query operation.


 

Figure01.png Figure 1: Subqueries at a glance.



* Inline table-valued FUNCTIONS return a table data type, the other types of functions not related to the discussion here are scalar FUNCTIONS and Built-in functions. Scalar functions return a single data value and Built-in functions come with SQL Server to facilitate various operations.

01x.pngA self-contained subquery embedded in an outer query is a stand-alone query with no dependencies to its outer query. You can highlight such subquery to test run it independently for QC purpose.

02x.pngA scalar subquery, by definition, returns a single value to be used in its outer query either as predicate in the outer query's WHERE clause or included directly in its outer query's SELECT clause as an aliased column. 


-- Problem statement: Show product names for ContactName ='Peter Wilson'.
                                        
                                        SELECT ProductID
                                             , ProductName 
                                        FROM dbo.Products
                                        WHERE SupplierID = (SELECT SupplierID                -- scalar subquery in WHERE clause, example 1.1
                                                            FROM dbo.Suppliers
                                        	            Where ContactName ='Peter Wilson');
                                        
                                        -- Problem statement: Display all products with first contact name from Suppliers table. Not very
                                        -- practicle, but it shows how to include a self-containe independent subquery in a SELECT clause.
                                        
                                        SELECT ProductID
                                             , ProductName
                                             , (SELECT Top(1) ContactName                    -- scalar subquery in SELECT clause, example 1.2
                                                FROM dbo.Suppliers) AS FirstContactName 
                                        FROM dbo.Products;


If a self-contained scalar subquery returns an empty set, its outer query sees it as a null mark. And, if such a subquery by mistake coded returning multiple values will produce the following error upon running the entire query (Outer + Subquery): 


Msg 512, Level 16, State 1, Line 23

Subquery returned more than 1 value...


Note: The new and acceptable terminolgy is null or null mark. The term null value, often used, is not correct because by definition null is neither a value nor can have a value.

03x.pngThe multiple value self-contained query is used where a multi-valued result is expected, for example when using the IN predicate. As an example, the following query uses a multi-valued subquery to return products supplied by suppliers from UK. The inner query returns supplierid for suppliers from UK. You can use ... WHERE supplierid NOT IN (SELECT supplierid... 0 to return all others but UK if required.


-- Problem statement: Show product names not associated with ContactName ='Peter Wilson'.
                                        
                                        SELECT ProductID
                                             , ProductName
                                        FROM dbo.Products 
                                        WHERE SupplierID IN (SELECT SupplierID               -- Multi-value subquery in WHERE clause, example 1.3
                                                             FROM dbo.Suppliers
                                                             WHERE CompanyName <> 'Peter Wilson') 
                                        
                                        -- There is no practical application for multi-value subquery in SELECT clause.

04x.pngCorrelated subqueries, unlike self-contained subqueries discussed above, this subquery references a column in the outer query. Thus we cannot select a correlated subquery to test run it. Both outer and inner query must run at once.

05x.pngSame table correlation: Both outer and inner query refer to different instances of the same table.


-- Problem statement:  Write a query to return products with the maximum unit price in each category.
                                        
                                        SELECT ProductName
                                             , UnitPrice
                                        	 , CategoryID
                                        FROM dbo.Products AS t1                              -- Same table correlation subquery, example 2.1
                                        WHERE unitprice =
                                        (SELECT MAX(unitprice)
                                        FROM dbo.Products AS t2
                                        WHERE t2.categoryid = t1.categoryid);

To produce the expected results, the same table (dbo.Products) is aliased as t1 and t2 to produce two instance of the same table to be used independent of each other in the outer and inner query .

06x.pngDifferent table correlation: The outer query (Customers) and the inner query (Orders) refer to instances of two different tables.


-- Problem statement: Write a query to return CompanyName for OrderDate '1997-01-16'.
                                        
                                        SELECT CompanyName                                   
                                        FROM dbo.Customers AS C                              -- Different tables correlation subquery, example 2.2
                                        WHERE EXISTS    -- exist here returns true or false 
                                        (SELECT * FROM dbo.Orders AS O
                                         WHERE O.CustomerID = C.CustomerID 
                                               AND O.OrderDate = '1997-01-16');
                                        
                                        -- ... WHERE NOT EXISTS(SELECT * FROM …    <-- use this when required

08x.pngTable Expressions are named queries. You a) write an inner query that returns a relational result set, b) name it, and c) query it from an outer query. T-SQL supports four forms of table expressions:

a) Derived tables and b) Common table expressions (CTEs) are created and used in code (non-db object).

c) Views and d) Inline table-valued functions are created in code or in SSMS stored as db objects.

09x.pngNon-database-objects are created and used in code, visible only in the scope of the statement that defines them. When out of scope, the table expression is gone and is not usable. See the first example under section 3 below.

10x.pngThe derived table is a subquery in a FROM clause. The FROM clause must have an aliased name. It is called a derived table because the fields in the outer SELECT clause query come from this named derived table. There are Inline and External derived table subqueries:

11x.pngIn an inline single derived table, it is mandatory to give an alias to the table in the FROM clause.

In an external single derived table, it is mandatory to list the field name inside () after the FROM table alias name. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html


Product  ProdType                                    -- Inline single derived table subquery, example 3.1
                                        A	      x
                                        B	      x
                                        B	      xx
                                        C	      x
                                        D	      x
                                        D	      xx
                                        E	      x 
                                        -- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
                                        ProdType  ProdType
                                        B	      x
                                        B	      xx
                                        D	      x
                                        D	      xx
                                        
                                        Select Product, ProdType
                                        from (
                                          select *, count(*) over (partition by Product) cnt from #t
                                        ) d where cnt > 1;
                                        
                                        -- Note: This example will be used later in discussing CTEs.

07ax.pngThe self imposed limitation with derived tables is that we don't use multiple table expressions in a FROM clause whereas with CTEs discussed later we can. Of course, technically speaking, we could nest multiple derived tables as shown below but nesting them in this manner complicates the coding and makes it prone to syntax errors thus rarely used. 


-- Multiple derived tables syntax 
                                        -- (for reasons stated this is possible but not used):
                                        
                                        SELECT f1, f2, ...                       
                                        FROM (SELECT ...  
                                              FROM (SELECT ...  
                                                    FROM Ta 
                                                    WHERE ...) AS Tb
                                              WHERE ...) AS Tc
                                        WHERE ...) As Td (f1, f2, ...);

12x.pngCTE (common table expression):

CTE is like derived table expressions. It's a named table expression that is visible only to the statement that follows it. Like a query against a derived table, CTE involves three main parts:

a) Name the inner query,

b) Name the columns of the inner query (right outside the FROM clause),

c) Name the outer query.


As opposed to derived tables, CTEs are not nested. CTE_1 and CTE_2 etc. are separated by a comma. Now, CTE_2 can reference CTE_1 because it leads VTE_2. And the outer query can reference both CTE_1 and CTE_2.

;WITH <CTE_a>                    -- Single CTE syntax
                                        AS
                                        (
                                        <inner query>
                                        )
                                        <outer query>
                                        ;                 -- Semicolon at the end terminates CTE_a operation.                 
                                        
                                        ;WITH CTE_1 AS (                 -- Multiple CTEs syntax
                                        SELECT ...
                                        FROM Table1),     -- Comma here allows a second CTE start next.
                                        CTE_2 AS (
                                        SELECT ...
                                        FROM CTE_1)
                                        <outer query with references to CTE_1 and/or CTE_2>
                                        ;                 -- Semicolon at the end terminates CTEs operation. 

13x.pngRecall the example 3.1 (from inline single derived table section) above where it was returning Products with two maximum UnitPrice in each category? Here we essentially producing the same result but using with a CTE. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html


Product  ProdType                                    -- Inline single derived table subquery, example 4.1
                                        A	      x
                                        B	      x
                                        B	      xx
                                        C	      x
                                        D	      x
                                        D	      xx
                                        E	      x 
                                        -- Problem statement: Starting with above data, produce the following (ProdType =x):
                                           (we can do this easily with a simple WHERE clause but the following is to demo CTE use) 
                                        ProdType  ProdType
                                        B	      xx
                                        D	      xx
                                        
                                        ;With d
                                        as
                                        (
                                        Select row_number()  over(partition by Product order by ProdType) As rn
                                        , Product
                                        , ProdType
                                        From #t)
                                        Select d.Product, d.ProdType
                                        From d
                                        Where d.rn =2;

14x.pngTo have multiple CTEs, end the first with a comma and start the next CET. There is no need to repeat another WITH keyword because the first one will be is not terminated yet. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html


Product  ProdType                                    -- Inline single derived table subquery, example 4.2
                                        A	      x
                                        B	      x
                                        B	      xx
                                        C	      x
                                        D	      x
                                        D	      xx
                                        E	      x 
                                        -- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
                                        ProdType  ProdType
                                        B	      x
                                        B	      xx
                                        D	      x
                                        D	      xx
                                        
                                        ;With d
                                        as
                                        (
                                        Select row_number()  over(partition by Product order by ProdType) As rn
                                        , Product
                                        From #t),
                                        e 
                                        as
                                        (
                                        Select #t.Product, ProdType, d.rn 
                                        From #t inner join d on d.Product = #t.Product
                                        Where d.rn >=2)
                                        Select e.Product, e.ProdType
                                        From e;

15x.pngRecursive CTE: “A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.” This description is from https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx

 

In earlier versions prior to SQL Server 2005, a recursive query use of temp tables, cursors, and additional coding was required. Here, in the example below an anchor subquery (starting subquery which fires only once) is followed by CTE body subquery with a UNION ALL operator before it. It is possible to have multiple UNION ALL + body subquery combination following the first one all separated by a comma. At least one of the body subquery is required to reference the CTE name defined in the anchor query.

 

In this example, employee 7 is returned by the anchor query before executing the body subquery repeatedly joining previous rows.

-- Problem statement: Write a query to return the management chain from EmploeeID = 7. 
                                        
                                        ;WITH BossCTE                                        -- Recursive CTE, example 4.3
                                        AS             
                                        (
                                        SELECT EmployeeID
                                             , ReportsTo
                                             , firstname
                                             , lastname
                                             , 0 AS distance
                                        FROM dbo.Employees 
                                        WHERE EmployeeID=7
                                        UNION ALL
                                        SELECT M.EmployeeID
                                             , M.ReportsTo
                                             , M.firstname
                                             , M.lastname
                                             , S.distance + 1 AS distance
                                        FROM BossCTE AS S JOIN dbo.Employees AS M
                                        ON S.ReportsTo = M.EmployeeID
                                        ) 
                                        SELECT EmployeeID
                                             , ReportsTo
                                             , firstname
                                             , lastname
                                             , distance
                                        FROM BossCTE
                                        Order By distance DESC;

CTEs and derived tables are created and used in code, visible only in the scope of the statement that defines them. After that statement terminates, the table expression is gone thus is not, as we discussed in the previous sections, derived tables and CTEs are table expressions that are visible only in the scope of the statement that defines them. Hence, derived tables and CTEs are not reusable.

What we have discussed so far is for use in the code being executed after which when it runs out of scope, it is of no use. In the following section we will see how they could be saved as database object for reusability. 

19x.pngDatabase-object(Views and Inline Table-Valued Functions): For reusability, you need to store the definition of the table expression as an object in the database, and for this you can use either views or inline table-valued functions. Because these are objects in the database, you can control access by using permissions. The main difference between views and inline table-valued functions is that the former doesn't accept input parameters and the latter does. As an example, suppose you need to persist the definition of the query with the row number computation from the examples in the previous sections. To achieve this, you create the following view.


17x.pngA view is some saved TSQL to create a virtual table based on the saved definition. The input column names used in views must exist in the other tables in the database. The view, saved object, in the database is reusable whenever needed provided the users have necessary permission to use them. The result set return by views are relational*  (see below on use of Order By clause with relational subqueries). Additionally, unlike functions, views do not accept parameters.

 

--Problem statement: Write a view to show products with the same ListPrice in  more than three products:
                                        
                                        create VIEW dbo.ProductsSameListPriceOverTwoCounts
                                        AS                                                   -- View, example 5.1
                                        select ProductID
                                             , ProductName
                                             , UnitPrice
                                        from (
                                          select *, cnt = count(*) over (partition by unitprice)
                                          from dbo.Products
                                        ) d where cnt > 3;
                                         
                                        -- Test:
                                        select * from ProductsSameListPriceOverTwoCounts
                                        --Returns:
                                        ProductID  ProductName                   UnitPrice
                                        
                                        67     Laughing Lumberjack Lager          14.00
                                        42     Singaporean Hokkien Fried Mee      14.00
                                        34     Sasquatch Ale                      14.00
                                        25     NuNuCa Nuß-Nougat-Creme            14.00
                                        1      Chai                               18.00
                                        35     Steeleye Stout                     18.00
                                        39     Chartreuse verte                   18.00
                                        76     Lakkalikööri                       18.00

* The term relation often is mixed up with term relationship which has to do with joining primary keys and foreign keys. However in standard SQL “… a relation, as originally defined by E. F. Codd, is a set of tuples (d1, d2, ..., dn), where each element dj is a member of Dj, a data domain”. SQL Server, Oracle, MySQL etc. are different flavors of the standard SQL These languages use terms rows and columns in place tuples and domains. As part of a relation defined by standard SQL, relations cannot have Order By clause to sort rows. The exception to use Order By applies in a subquery, the use of ORDER BY with a TOP or OFFSET-FETCH to produce certain results not ordering the data it returns. 


 Inline table-valued functions are like views but accept parameters and you may include Order By clause in its definition. Unlike the previous example where constant 3 was hard coded, with inline table-valued function we can include an input parameters like @RepeadedListPriceCoun to enable user to use different values.


-- Problem statement: Write an Inline table-valued function to show products with the same ListPrice 
                                        -- if equal or over RepeadedListPriceCoun provided:
                                        
                                        Create Function dbo.fnProductsSameListPriceOver(
                                        @RepeadedListPriceCount As Int) Returns Table
                                        AS                                                   -- Inline table-valued function , example 5.2
                                        Return
                                        select ProductID
                                             , ProductName
                                             , UnitPrice
                                        From (
                                          Select *, cnt = Count(*) over (Partition Yy unitprice) 
                                          From dbo.Products
                                        ) d Where cnt >= @RepeadedListPriceCount;
                                        
                                        -- Test:
                                        Select * From fnProductsSameListPriceOver(4);
                                        
                                        -- Returns 25 records:
                                        ProductID  ProductName             UnitPrice
                                        47	Zaanse koeken	                9.50
                                        45	Rogede sild	                9.50
                                        21	Sir Rodney's Scones	       10.00
                                        3	Aniseed Syrup	               10.00
                                        74	Longlife Tofu	               10.00
                                        .
                                        .
                                        56	Gnocchi di nonna Alice	       38.00
                                        12	Queso Manchego La Pastora      38.00
                                        27	Schoggi Schokolade	       43.90
                                        63	Vegie-spread	               43.90

 



Summary: The readers of this article fall in three categories of pro, intermediate, or beginner. If you are a pro, any correction or improvement from will be greately appreciated. For intermediate-level professionals, I hope this presentation is helping to form a complete mental picture and is making easier your work with subqueries by recognizing 5 distinct mode of there applications 1) Self-contained, 2) Correlated, 3) Derived table, 4) CTEs, and 5) View & inline table-valued Functions. My recommendation for the beginner SQL enthusiasts is to read the SQL related questions present at EE (answered or not, without going through solution provided by others, if already answered) to come up with your own solution. This is a good mental gym for your SQL brain muscles. In a matter of a few months you will see how easily you are able to suggest a good solution for a questions which presently maybe a bit mind boggling.


Thank you,


Mike Eghtebas, BSME, PE

 





piechartY.pngSkill Highlights:

 

  • Extensive data analysis experience, dynamic reporting and developing automation tools to streamline activities and boost productivity.
  • Expertise designing user friendly interfaces, experienced in T-SQL, ASP.Net, C#, VB.Net, ADO.Net, VBA, Excel Macros, and VBA coding.
  • Others: Git, MySQL, Java, PHP
     






5
 

Expert Comment

by:Rayne
Comment Utility
Thank you Mike, this really clears a lot of key concepts :)
0
Dependencies in Software Design
In software development, the idea of dependencies is an issue of some importance. This article seeks to explain what dependencies are and where they can create a problem for growth or an opportunity for excellence.

In a nutshell, if program "A" calls upon program "B" for some data or service, program "A" is said to have a dependency on program "B." If the interface that program "A" uses to call program "B" changes, then program "A" must be changed, too. This sort of situation may be easy to fix if it is an isolated case (perhaps all calls to program "B" are routed through a particular class method in program "A"). However if the coupling is not an isolated interface, the dependency on program "B" can be a real risk to the continued health of program "A."

To try to illustrate this in practical terms, let's consider an issue that many PHP programmers are facing right now. They developed web sites or web applications that are dependent on the familiar but obsolete MySQL extension. Throughout their scripts, we find numerous instances of mysql_connect(), mysql_query(), mysql_fetch_assoc(), etc. There are red warning labels on all of those main pages! It has been a couple of years since PHP deprecated the MySQL extension
1
 

Expert Comment

by:calslim
Comment Utility
Another great article! I have started using DI more and more in my code. I have not as yet done much with phpunit and automated testing, largely because there does not seem to much in the way of real-world, yet easily approachable "getting started" material. For example, many tutorials start with the obligatory test add function. assert that a call to add(1,2) equals 3. This is all well and good, but then they fall off quickly and do a bit of handing waving over mocks, stubs, automation, etc. But I digress.

One question I have about DI, is how to decouple use of global functions (particularly in a framework). After all, PHP is essentially a language and a framework. I total get DI with objects. For example, instantiate an object, then pass that instance into a function or method of another object.

But what about decoupling functions?

A bit of a contrived example:

class Gatekeeper
{
    public $passphrase;
    private $secret;

    function __construct($phrase) {
        $this->passphrase = $phrase;
    }

   private function validate(){
       return $this->secret == md5($this->passphrase);
   }

  public response(){
     if ($this->validate()){
         return 'Welcome. May I take your coat?';
     }
     else{
         return 'Get lost!';
    }
  }
}

Open in new window


// The above code is dependent on MD5. MD5 is a function, not an instantiable class; what is best practice to inject this dependency analogous to:

$JoeTheBouncer =  Gatekeeper('Shazaam', md5);
$JoeTheBouncer->response();

Open in new window

0
Assuming MySQL Tables called "invoices" and "latefees" with the following columns:
 
CREATE TABLE IF NOT EXISTS `invoices`
(
  id BIGINT (255) NOT NULL AUTO-INCREAMENT,
  customer BIGINT (255),
  invoiceno VARCHAR (255),
  startdate DATE,
  isPaidStatus ENUM ("YES,NO") DEFAULT 'NO',
  payment_terms INT (5),
  latefee INT (10),
  subtotal DECIMAL ("6,2"),
  vat DECIMAL ("6,2"),
  total DECIMAL ("6,2"),
  total_addtional DECIMAL ("6,2"),
  PRIMARY KEY ( id)
) ENGINE = InnoDB ;

Open in new window

 

CREATE TABLE IF NOT EXISTS `latefees`
(
  id BIGINT (255) NOT NULL AUTO-INCREAMENT,
  type ENUM("%,Flat Fee") DEFAULT '%',
  frequency INT (5),
  amount DECIMAL ("6,2"),
  PRIMARY KEY (`id`)
) ENGINE = InnoDB ;

Open in new window

respectively.

1. This is the MySQL Code that produces the results:
 
SELECT 
	`A`.customer,`A`.id,`A`.invoiceno,`A`.total,`A`._total,`A`.`latefee`,`A`.startdate,`A`.payment_terms,
	`B`.name,`B`.type,`B`.frequency,`B`.amount,
	((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) - `A`.payment_terms) AS daysElasped,
	MOD(((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) - `A`.payment_terms), `B`.frequency) AS `isBillableDay`,
	IF(`B`.type=1,(`A`.total + `A`.total_addtional)*(`B`.amount / 100),`B`.amount) AS `new_amount`
FROM 
	`invoices` AS `A`
LEFT JOIN 
	`latefees` AS `B` ON `B`.id=`A`.`latefee`
WHERE 
	`latefee` > 0 AND 
	`isPaidStatus` = 'NO' AND
	((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) > `A`.payment_terms) AND 
	(MOD(((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) -`A`.payment_terms),`B`.frequency)=0)

Open in new window

This query basically does the following:
 
    a) Firstly checks if the invoice status is on "not paid yet"

    b) Calculates the number days ellapsed "daysElapsed" by checking invoice start date against the current date and then,

    c) Checks if the resultant "daysElapsed" exceeds the selected invoice terms and these could be "7 => Net 7 Days, 15 => Net 15 Days, 25 => Net 25 Days, 45 => Net 45 Days, 60 => Net 60 Days", and then 

    d) Checks if the remainder of our dividend "daysElapsed" minus invoice terms against our divisor latefee frequency is 0. This tells us that, today is the suitable day for us to pernalize this particular invoice by adding they appropriate latefee amount.

    e) If the invoice late fee selected is set to increament in percentages (%), then our new amount will be the  x % of the invoice's current amount.

    f) Else if the invoice late fee selectedd is set to be a Flate Rate then, our new amount will just be the latefee amount

2. Update the invoice with the new amount just calculated. Lets assume that we have saved our resultant new amount from the query above

i.e. say you are using PHP as your scripting language FIELDS[]"new_amount"]; ?>

 

Open in new window

0
 
LVL 2

Author Comment

by:Eric Mulovhedzi
Comment Utility
With all due respect that's not what i had in mind and that's not what i would like to do in the future. What you are advising me to do is to find a business model first before i contribute an article on your network.

I am a GENIUS but only on Coding and Software Development, i do lots of research and development stuff for Enterprises.

I also have my own business network portal where people can use ERP and CRM for free ( http://1stbn.co.za )

Two, i recently developed an Open Source server configuration utility tool "OVH Server Basement" (http://ovhserverbasement.co.za/) and whole lots of other genius commercial stuff.

The moral of the story is i have lots and lots of knowledge to share more especially on Programming and Development, i just do not have time to think of business models etc. Most of these articles are solutions i derived for problems that i experienced when i develop my own stuff or even clients stuff and i believe that there might be at-least one person out there who is still yet to go through what i went through and perhaps this article might just help them.

ALSO, i selected a tag called MySQL so automatically this article is targeted to someone who is already trying to write an SQL query for what ever reason that prompted them to do so.

Those are the people i was honestly having in mind when i was writing the article,  people who already have a problem or business model and are just searching for solution
0
 
LVL 2

Author Comment

by:Eric Mulovhedzi
Comment Utility
This is also one of the articles i contributed recently:

http://www.experts-exchange.com/Networking/Linux_Networking/A_13635-How-to-setup-a-local-Area-Network-between-two-Ubuntu-servers.html

It was published but to me i do not think has a business model to it because i just went straight to the point
0

MySQL Server

47K

Solutions

22K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.