Advanced Coding Concepts

MySQL - The right way to get a week range from a time column or from the current date

2012-10-07T18:10:00.001-07:00

I was trying to save 5 minutes of time today by Googling for what I thought would be a fairly standard query to get a date range representing the week of a time column, but to my surprise I found nothing but crap online -- check out this forum, for example, where a poster suggests a complex case switch that is external to the SQL query, and surprisingly has other users backing him up. His query also will not extend to a time column, only to the current date, so it can not be used in reports where the intent is to group by week.

I would have posted this as a reply on that forum, but the forum is locked (the question's been resolved?) I think I'll start posting more now, about the inadequacies of online "help", in order to drive developers towards thinking for themselves. Further, to have an accurate answer available for searchers.

Here's my sql statement (field portion) to get the week start/end date in a range - replace 'timecol' with your time column, or "now()" for the current week.

select
concat(
date_format(date_sub(timecol, interval date_format(timecol, '%w') day), '%b %d %Y'),
' - ',
date_format(date_add(timecol, interval (6-date_format(timecol, '%w')) day), '%b %d %Y')
) as timecol_weekrange

.... from
... where
... group by date_format(timecol, '%X%V')
.. order by timecol

This can be extended to make it a bit more pretty - if it's the current year, why display the year?

I if the year is the same on the upper and lower half of the range, why display it twice? Same question, for the month.

The next query (while more complex) will shorten Oct 7 2012-Oct 13 2012 to To Oct 7 - 13
Also note, if it was currently 2013, then it would instead be shortened to Oct 7 - 13, 2012

It also recognizes "This week" and "Last week" - it's always great to put data in human form; the exception to this is if you anticipate your data being printed, without the context of the time frame it was generated from.

select

if (date_format(timecol, '%X%V')=date_format(now(), '%X%V'),'This Week',
if(date_format(timecol, '%X%V')=date_format(date_sub(now(), interval 1 week), '%X%V'), 'Last Week',
concat(
date_format(date_sub(timecol, interval date_format(time, '%w') day), '%b %D'),

' - ',

if(month(date_sub(timecol, interval date_format(time, '%w') day)) <> month(date_add(timecol, interval (6-date_format(timecol, '%w')) day)),
date_format(date_add(timecol, interval (6-date_format(timecol, '%w')) day), '%b '),
''
),

date_format(date_add(timecol, interval (6-date_format(timecol, '%w')) day), '%D '),

if (YEAR(now())<>YEAR(date_add(timecol, interval (6-date_format(timecol, '%w')) day)),YEAR(timecol),'')
)
)
) as timecol_weekrange

.... from
... where
... group by date_format(timecol, '%X%V')
.. order by timecol

There's a few things I should note - at first glance, many people might think this looks inefficient as a single column in a query, which it may be if you're expecting 100000 rows to be returned,

However the likely use of this is to generate a report that is grouped by week, in which case you'll want to take the following advice on as best practice:

Make sure you use the "as timecol_weekrange" (or any other name) after the field, to give this section a separate name - that's not too important for optimization, but you don't want to give it the same name as your source column (ie don't call it timecol)
When you group by week, don't group by timecol_weekrange - that would force mysql to run this code on every row in the database. Use a where statement showing your upper and lower limit, ie where timecol>date_sub(now(), interval (7*5)+ date_format(now(), '%w') day)) ) - this will limit the results to the past 5 days, and make sure that it starts on a Sunday - and stopping a full scan
For the grouping, group by date_format(timecol, '%X%V') - it's easier for the database to execute, your grouping will be done more quickly. If you have a where statement that you know would never span more than 11 months, group by weekofyear(timecol) should be slightly faster
After grouping, order by timecol, not by timecol_weekrange- if you order by timecol_weekrange, it will order alphabetically - January will come after August. If you group by the suggestion above, then that's not an issue as the result will still be numeric (201201 will be before 201208)

If you take the advice above, then the running your query across a full year will only run the column formatting a maximum of 52 times - once per each grouping, rather than once for each row if not grouped correctly.

If this query or post helped you, let me know via a comment, share, trackback, tweet or +1

Database Optimization: Indexing, and Overcoming the Incompetence of Online Information

2012-01-25T12:05:00.000-08:00

As techs, we've all thought that we knew something, and had it turn out incorrect. Realizing our blind spots when making a decision, and acknowledging that our decisions may be correct or incorrect, where full information isn't available, is a right of passage in to being a professional.

I've been seen many blog entries about MySQL optimization that have significant flaws. Most recently, an entry comparing the load time of a thousand PNG files whether from a blob entry in a database, or from the file system.

The fault in the entry is that the database loads were being performed through lookups in the database based on a random varchar column that was not indexed. This on it's own, on a 4GB database, is enough to make it crawl down to the 1000% speed differential that that experiment experienced.

Key optimization is an essential skill to any database administrators - and incredibly simple when done correctly. At the most basic form, just ask yourself if the column you've added to your table will be searched through - ie a phone number, in a phone number search tool, or a user name column if you're going to have users signing in. If you need to go beyond this, chances are you'll find out in the MySQL slow query log file (disabled by default in my.cnf). Usually you will only need the first column referenced in any where statement to be indexed; Order the logic inside of your where statement based on which statement is most likely to reduce the table the most, so that the remaining statements won't have as much work to do.

If you have a web application that can not easily scale to 10,000 - 50,000 daily users or 100,000-500,000 daily page loads, I recommend reading directly from the source - the MySQL documentation.

Importance of a strong roadmap - overcoming Writer's Block and Welcoming Brain Fog

2011-06-10T21:44:00.000-07:00

I've been working on a large project for 7 or 8 months, that has been self-motivated and self-established to date.

In the past two months, I've acquired an interested buyer who's funded my development for 45 hours per week, each week, without interest in IP acquisition. Am I one of the lucky few, or is the outcome of inhumane dedication?

In the past I've worked on projects for a month, three months, or sometimes longer and then achieved a point of brain fog where I couldn't motivate myself to work further. Today, after twice this week working so intently that I've become unable to operate - or even speak clearly, I've completely changed my internal definition of brain fog.

Why? The results on Google reference brain fog for programmers as a sort of writer's block, but this time around I've developed such a strong roadmap that I can direct my development efforts non-stop with continual precision. I've never kept any aspect of this project on paper, other than my last entry describing my desires for it's plugin application system, but through an equal amount of planning in terms of what will be necessary in the future, categorization, and strong focus on not taking the "easy" way and producing reusable code (esp. API calls in a large, custom API wherever a set of logic may need to be reproduced in the future) I've created a beautiful chaos of electronic alchemy. Large tasks on my todo list are now taking days whereas months ago they would have taken a week to two weeks. However, the end effect is still the same amount of thought.

Have any other programmers out there "thought themselves" to a cucumber state? Are there any exercises that you can recommend to strengthen the brain's ability to operate at an intellectual level for long periods of time?

Application Plugin Interface design

2011-03-06T22:35:00.000-08:00

A large part of enterprise grade solution releases includes methods for distributing, purchasing, monetizing or for free allowing extensions to their products. Apple with the iTunes store for iPad and iPhone, Drupal with their simple base-line product for setting up webpages can be extended easily with readily available plugins. Winamp, Firefox, Google Chrome. The list goes on.

If you build it, they will come

The question is how to create an application from scratch with the immediate intension of opening it up to developers to extend, as well as for corporate clients to customize for their purposes. As Teddy Roosevelt said, "If You Build It, They Will Come". Microsoft's Steve Balmer famously threw a chair in 2005 at the thought of Google, but also throws the words "Developers Developers Developers" on stage at conferences, and has for years. He knows that to have the leading platform, you need to motivate developers to extend the functionality to your platform. Google has a great list of APIs accessible with a Google API Key, but other than Google Adsense and Google Adwords, makes it difficult to monetize the products you can create with their services as a developer. The allure of a centralized platform for publishing your applications is a one that is difficult to miss as an application designer, and can even allow some leeway for not having the best interface, if you have the right amount of users.

How much power
Should you restrict your application to a specific language? What if your language becomes outdated in three years? How do you handle third party add-ons to your application without allowing them to interfere? Do you require the use of your own function calls to pass information, or provide complete control to the developer?

The method I've chosen in my APEX Platform that I am creating, is to allow a plugin to be written in any language with almost no integration in to the interface other than by interfacing with other, user-written applications. APEX has essentially become a website template that allows you to drop in code to display a main page - whichever application is running controls the interface. It sounds simple, but is there anything else necessary? To date the only communication that is done between APEX and the application is that an application can list a variable as being in it's global variables list - which allows other applications to read and write to it. The settings application can see the functions you expose in your application for configuring your applications extensions - the settings application is not a part of APEX, and can be readily extended. The Dashboard application reads from the icon_list global, and allows you to customize the dashboard and quicklists. A "contact" application can be created that "accepts" a "data structure" and an application controlling that data structure can search for an application that "accepts" it. The possibilities become endless. Want to write a virus that runs on my platform? I say go ahead, because I gain the creativity of the world that prefers unlimited barriers.

In consideration of writing such a mechanism, remember the age old "KISS" - Keep it simple, stupid! You want to be universal. You want to provoke developers of all types. I will post more on the structure of my simple application plugin interface and allow you to benefit from it's internal structure.

Implementation of security measures
With great power comes great responsibility, and "app stores" like those that already exist, that allow you to publish an application and have it moderated, allow for a great resource of moderated products. I see a great business model in allowing developers to write and monetize their applications, apply updates and have the rest of the world benefit. Write free applications, expand the open source movement. Build communities - the kind that are viral. Who wrote Napster? What if it was open source, would it have been unstoppable? Didn't Facebook suck before Applications were added? Why did Facebook suffer privacy issues?

If your application exposes customer records to applications, I hope you know who's writing them. Facebook encountered this issue very quickly and has been a subject of many legal disputes centered around privacy. Who's responsibility is it to secure an application and ensure it does not release vital information?

I've chosen to provide a hosted interface that allows you to integrate any level of functionality that you desire, with preselected applications. My desire is to create a one size fits all business platform - Lotus Notes and Sales Force have been diligent in mucking this up in the past. I will control a small amount of internal database structures and allow applications to extend the rest. Each "client" would own their own database instance, with pointers to the applications involved; function calls to each application's initialize, install, view functions. Allow applications to integrate cron job entries. I allow an application to create database tables, and to lock out access to these tables from other applications - in theory this should be enough. The "users" table can not be modified by a rogue application, unless an administrator approves the rogue application. We essentially move the risk to an intelligent controller.

What are your thoughts? Has anybody out there in this blogosphere built such a plugin interface before? In the end were you too restrictive, not restrictive enough, did you consider this interface early enough in your development process or too was it too early? If you are interested, follow my blog - I will be posting my modelling diagrams of this process in a few weeks.

Blog finds of the week - March 6, 2011

Web Development Blog - Why Should you Cache your PHP Website? A good starting point for increasing the maximum capacity of your web application - very little of it is PHP centric, you can apply this concept to all other languages.
Coding Horror - How to Write Without Writing Describing the benefits of improving your communication skills when it relates to software development. If you want to write code, you should first learn how to write, and how to communicate.

Asterisk Manager Interface C++ Interpretor for Linux

2011-02-22T11:46:00.000-08:00

Through the past number of months in my full-time developer position for an online company and it's sales office, we've migrated to a VOIP telephony platform based on Asterisk, Linux and our custom call centre management application.

In order to integrate an autodialer in to the application, I had originally written a script in PHP to read from two asterisk servers (local and offshore) to post information about whether an agent is on a call or not, and also inbound calls to the call centre application.

The PHP script took a matter of hours to set up initially, but lacked proper structure - specifically, it could not track information when we switched to call queues, and was not easily portable between asterisk versions (1.8 locally, 1.2 in offshore)

For the past week I have worked on the design and initial coding of an app in C++ that acts as a client to both servers, interprets the messages and creates internal structures. I'm releasing that code here for review, and to help other's save some time, as I haven't noticed any other software out there that could accomplish this elegantly. Currently, I am calling this application AMIflex based on the flexibility it provides to manage the Asterisk AMI protocol.

This source code and it's derivatives can not be sold, licensed or packaged with any commercial software without my explicit permission.

Download the source code here (.tar.bz2, 6kb) [[Link fixed March 3, 2013]]

Installation instructions:

unpack the file via "tar -xvjf" command
create /etc/amiflex directory, and add a "servers.conf" file in the following format

Server: server name (for display purposes only)

Host: ip address or host name of ami server

Username: AMI manager username

Secret: AMI manager password/secret

Optional fields:

RetryPeriod: number of seconds to wait between connection retries - ie if asterisk is rebooted (default: 300)

Port: port number (default 5038)

You can insert more than one server (connections will be maintained together) by entering the first server's details, placing a second new line after the last configration line for that server, and then entering the details for the second server (and so on)

How to make this useful

When a server connection is established, there's a AMI::RegisterAllEvents() call after authentication. Add in your own events, and manipulate the structures or log output

Example:

AMI::RegisterEventCB("Dial", &AMI::MyDialCallback)

void AMI::MyDialCallback(MSG *msg)

{

if (msg->Attr("SubEvent").value=="Begin")

cout << msg->Attr("Channel").value + " is dialing " +msg->Attr("Dialstring").value<<endl;

}

Current limitations

I have some well known limitations with this software - I'll be resolving them myself for internal use, but my next step is to integrate proprietary MySQL links in to the source code, so I made sure to release this first. Currently my task list with my employer is too long to not take some shortcuts :)

The channel list linked list is a static member of the channel class - this means that you run AMIflex with multiple servers in the configuration file, all of the channels for all servers will be parallel. If you have two servers and each server has the same extension, then there will be one device entry created and two channels open on that device, when each extension is busy.
The bridge event currently only registered the first channel's Bridge member to the second member, and vice versa - If you are monitoring channels that will be bridged to more than one other channel, this will have to be expanded to a proper many to many-style linked list.

Blog Guidelines

2011-02-16T16:50:00.000-08:00

As a professional systems architect, I believe in strong guidelines for all work to ensure that a system produces stable and consistent results. As a blogger, I aim to continue that style through in my posts. This entry is to enumerate the posts you will find within this personal space.

I will post thoughts on proper code and system design, when possible keeping the work independent of specific computer language, providing code examples (both concept and fully functional, with an open license when plausible)

My motive is to help other's with the logic I've learned and begun to take for granted, and to learn better ways through open collaboration within the blog comments.

Please feel free to leave your constructive comments and questions, as well as open personal communication with me.

Thank you, and welcome to Advanced Coding Concepts on Blogspot!