Another day I needed to code up an automated stale records deletion in PostgreSQL. I suspected I could do it straight in any script as say bash using the
psql command, but I like to keep the code that affects data close to the database.
PostgreSQL User-Defined Functions to the rescue!
There are a number of ways how to write it up in SQL or PL/pgSQL and the easiest is using the Interval Data Type.
Fast forward to the implementation, caveat ahead!
One can easily express the interval as text, and it often is shown as below:
DELETE FROM my_table WHERE created_at < now() - interval '6 months';
Note: the expression will still work regardless whether you passed the period in plural or singular form.
But when you have the statement dynamically generated in a function that is based on the parameter passed in for the number of intervals it becomes quite tricky to express it.
Without further ado, luckily, there is an alternative syntax that takes the following form:
(6 || ' months')::interval
That is a much frindlier form of the expression which I leveraged in my function:
Accepts an arbitrary number of days to delete older than records
OR REPLACE FUNCTION fn_delete_from_serenity_forms_older_than
(IN num_of_days INT)
RETURNS void LANGUAGE sql AS
WHERE created_at < now() - (num_of_days || ' days')::interval;
Hopefully you will find it helpful someday.
Just a heads up folks to take advantage of the following promotion:
Till the end of the year every single eBook and video course Packt has ever created will be available on the publisher’s website for just $5!
To avail of this offer simply visit Packt’s website.
By the way, Packt has also created their very first Year in Review. It will make you aware of the biggest topics that came out in 2015. But this is not all! Since thousands of people participated in this survey it became possible to show what’s going to be defining 2016 and probably beyond – this way you can learn intelligently and strategically!
Happy New Year!
Some people say time flies, and it feels like yesterday when you realize the month of November 2015 marks the tenth year since SQL Server 2005 release to manufacturing. Quite a milestone, at least from the human prospective, it seems sure so. And this is true for so many reasons, SQL Server 2005 was definitely a landmark offering of a relation data management engine bundled with more not less significant components that saw light for the first time in it as SSAS, SSRS, SSIS and too many others to mention in a blog post.
So great in fact, I suspect those who conduct their day-to-day business with SQL Server 2005 may feel lucky they happened to have one of the best database platforms. Let me pause here and state that my post is to somewhat mar their happiness. I would not like you though leaving this page yet because I really want to come with only good intentions and make those who stay behind alerted. If I will not sound convincing, drop me a line and we can have the discussion going.
So agree or not, a 10 year time span, especially for IT is a lot! To some it may mean innovation, to others wins or losses, but I would say simply it is a lot of changes, changes everywhere: in server and user hardware, the Operating System, Internet, the peripheral software, and how we even conduct the business, but and also on the human front – we are getting more seasoned professionals. So change is inevitable, and some people say change is good.
For these very reasons the End of Support (EoS) exists for all the software vendors. It is otherwise not cheap or sometimes not even possible to change things. Speaking of the databases, the hardware became stronger, and cheaper at the same time, the amounts of data grew exponentially and the means of collecting and storing it became more affordable. And the business wants to process it all. So the workloads have changed.
So precisely, on April 12, 2016 SQL Server 2005 is retiring. While there is still time left, perhaps now is the time to start planning the upgrade.
Besides, I have always tried to understand why do IT shops stay behind on software. And I tried to study this phenomenon. Luckily, I have had access to a sheer number of clients and happened to have conversations regularly that resulted in me getting the following reply often “my existing software continues to do well what it has always used to”. Indeed, having the one time SQL Server license paid, and the development costs spread across so many years of SQL Server production support now means pure ROI. So even though SQL Server 2005 has been succeeded by three newer builds it may sound like there is no compelling reasons to upgrade. Or not to upgrade just yet. Is it so not tempting or? Let’s see…
Well, my argument to these replies was – you may never then find the time to upgrade until it is too late. And there are plenty of examples.
Another composite in the non-upgrade disaster recipe is the bound to die hardware:
How do we typically upgrade in the RDBMS world? It is by moving the data to newer and bigger machines, the so called scale up method. Stay on the old machine and only imagine what kind of nasty surprises a 10 years old hardware can present you with. I have already seen several, from failed RAID drive controllers and a motherboard chip to power supplies. Even if the instance is clustered it leaves you out with no High Availability for perhaps longer than your SLA permits or may require to migrate to a different machine all together due to lack of support or spare parts which rarely is trivial. Even if you have all the spare parts in abundance, look at the business side, did it grow? If yes, most likely the robustness, storage and the elasticity do not satisfy your users anymore.
For those who need stronger reasons, I am really worried about the security, as it happens, by the time a vendor winds down active vulnerability assessments the hackers already studied the weak points in the old software well enough, so guess what, they probably know where to hit you. For example, refer yourself to a recent security breach in the WalMart Canada Photo Center.
So, if the prospects of breaching your mission critical system and failing hardware is not enough incentive to plan the upgrade, another often overlooked factor remains – your probably as old as SQL Server ally Operating System (OS). Having an OS without support, too is a risky mix. A simple thing as a USB stick inserted by your operational staff member can wreak a lot of havoc. It is like pay me now or pay me later saying. If you choose to pay later, make sure you can afford the interest. Let’s look at what happened at Paris’ second largest airport – Orly. It operated a critical piece of their software on an outdated OS. Yes, perhaps it was working well a long time, but the human factor is always there. Are you sure your software veterans are always available?
Don’t delay the upgrade, SQL Server 2014 offers a plethora of productivity features, safety, usable components, offers extreme high availability, integration with Azure, not to mention its flexible workload data engine. SQL Server 2014 is on Service Pack 1 now which means for most companies full “certification”. Simply take advantage of these facts to easily convince the stakeholders. The time is running out.
The upgrade process (I went through a few) is a smooth sailing, perhaps my best experience upgrading to date. Besides, Microsoft has put a web page dedicated to upgrading both the SQL Server and Operating System together filled with useful resources helping conducting such a smooth upgrade process https://www.microsoft.com/en-ca/server-cloud/products/sql-server-2005/
Don’t celebrate – upgrade.
Packt celebrates International Day Against DRM, May 6th 2015
Packt Publishing firmly believes that you should be able to read and interact with your content when you want, where you want, and how you want – to that end they have been advocates of DRM-free content since their very first eBook was published back in 2004.
This year, to demonstrate their continuing support for Day Against DRM, Packt is offering all its DRM-free content at $10 for 24 hours only on May 6th – with more than 3000 eBooks and 100 Videos available across the publisher’s website www.packtpub.com, there’s plenty to discover, whatever you’re interested in.
As per the Packt CEO:
“Our top priority at Packt has always been to meet the evolving needs of developers in the most practical way possible, while at the same time protecting the hard work of our authors. DRM-free content continues to be instrumental in making that happen, providing the flexibility and freedom that is essential for an efficient and enhanced learning experience. That’s why we’ve been DRM-free from the beginning – we’ll never put limits on the innovation of our users.”
– Dave Maclean, CEO
You will not come across many robust HMVC web frameworks and especially those built on top of the excellent and now in its rebirth PHP. To not to reiterate over the things how HMVC is much more advanced than the vanilla MVC you can read a nice blog post dedicated to this topic. If you do not bother visiting it I will not be lazy to state it is the familiar MVC, but logically structured. FuelPHP is exactly such an HMVC implementation. So if you ask why HMVC, well, here I offer two choices, ether get a free chapter of the FuelPHP Application Development Blueprints book, or listen to me, well read rather: it allows what not so-web developers call “design by contract” (DbC). But this is what I say after I read this book.
I was very happy to see a new book released on the Neo4j subject, which was shortly after I read Learning Neo4j which I reviewed here. I just feel it is a very good timing for the release and that these books need to be read in this sequence, first is because Learning Neo4j is very inspirational in addition to teaching Neo4j basics, and also showing what implementations this database can have in real life.
I must say right away that Neo4j Essentials goes beyond just the essentials, and it will become immediately apparent that Sumit has an in depth knowledge of both Neo4j and enterprise architecture. So you will have a good company.
In terms of setting up your environment, it is uncomplicated, as a bonus, Neo4j comes as a free version which is all you want to learn it, and then build a prototype. Be posted Neo4j will require Java (but not its SDK) because Neo4j itself is built in Java. Oracle Java 7 or newer specifically. As a side note, Neo4j can be even embedded into your standalone application.
To be efficient with the book and technology and make the most out of the book I suggest to familiarize yourself with REST and get an IDE like Eclipse or IntelliJ to run Java code samples from the book. I found them very valuable.
So back to the book, while not exactly structured the way I expected it to be laid out (e.g. troubleshooting and maintenance related items appear early in the book) it actually allows you to navigate efficiently and the content is chained logically.
There are plenty of examples in the book covering various aspects of data processing. While most examples are not very exiting I suspect they serve a good starting point in one’s journey toward efficient processing and representation of related data. I must say the author made special emphasis on covering the newer features of the last data base release (Neo4j 2.0).
The most interesting item discussed in the book to my taste was Spring, it is just often not covered in even books dedicated to Java. Apparently Spring supports Neo4j, strongly. What it means, you can build enterprise grade, data rich web applications. Another awesome topic is clustering.
This book dedicates a lot of details with attention to deployment, maintenance, writing code for performance, aspect oriented programming and more, but just enough at the same time to build a reliable implementation of a modern enterprise grade database centric application.
I am sure this book will be of much help to many of these who embark on a wonderful journey with Neo4j!
This is a heads-up for the Canadian IT pros: for a limited time you can upgrade your skills and win a prize!
Sounds exiting? I bet! Head over to #Canitpro site to register and get the fun started!
Once done, consider checking up Azure with a free month of full trial to apply your knowledge.
If you have or planning to get a MSDN subscription, then the offer is even better with no limit in time, check it out, too.
There is nothing to lose, only gain knowledge that pays back, well… if you even do not win the prize.
Disclaimer: offer valid for Canadian residents only.
A no small print, no catches offer – totally free learning from Packt Publishing!
How it works: every day starting today there will be a new free ebook offered to claim.
The campaign has already started so hurry up and claim today
Moodle 2.0 E-Learning Course Development
This promotion ends March 5.
This book was released back in Fall 2014, but I did not had a chance to read it until recently. A big miss. As far as I can tell, it is the one of the few books covering as much ground as possible in concern to scikit-learn as free Machine Learning (ML) libraries available for Python. In general, the Machine Learning is a fascinated piece of science seeing a lot of traction these days, but it is a tad intimidating to grasp at the beginning, besides, its potential use cases given it fallen into the wrong hands (g-d forbid) can be scary. Otherwise I foresee a huge potential for it’s use in the IOT.
This book aims at easing the ML adoption hurdles providing with not less than 50 recipes which cover pretty much the whole scikit-learn landscape. I could see Trent made every effort to deliver a hight quality product. The book has a supplementary file that covers what an end user needs to install to go through all the material in the book and obtain sample data.
In terms of a general note, since this product is aiming at mostly the data scientist, engineers or research staff many topics are not going to be quite familiar to a wide non-technical or general IT audience, but please ensure you put an extra effort in understanding the concepts. Like I have said, the benefits are enormous. And prepare yourself to scratch your head a few times or more :-). Yes, this is a very advanced book. Yet, it seems that it covers all the possible scenarios and industry fields one can imagine off. Numerous graphics, detailed code samples and output examples, all are ready to copy and paste into the mighty Python REPL.
When I was reading the book I had a task at hand and I concentrated on the KMeans algorithm which is elegantly covered, and I enjoyed the most the chapter on Classifying Data. At the same time I think the cornerstone of the book is chapter 1 on pre-model workflow and the last on the post-model, I just did not see books to date going this far.
While this book is more like an ‘Academia’ publication it does have many practical applications, but for a less Data Science savvy person it desires to have more explanation on why XYZ and ABCs are necessary, or what each library function is used for and under what circumstances one would choose to use it.
Overall it is a tad dry, technical read, but at the same time no extra, volume inflating words were mixed in, so it is worth what you are paying for.
My verdict, it 4.5 our of 5.
Python has seen a rapid adoption rate recently and seems to have been proliferated many IT shops as it now boasts a myriad of helpful libraries in addition to having adapters and connectors for most of the data storage offerings and of course is very much suitable for the web development where Flask appears to me being the leader in the Python Web Frameworks space.
Would it be suffice to say it is looking like the most used language in 2015 to motivate you coding in Python?
With this popularity in sight the amount of training material started to grow significantly. Packt Publishing among the other leading technical education content providers quickly spotted the potential and released a barrage of products among them the Rapid Flask training course I happened to enjoy watching.
In short, the objective of this course is to quickly get you up and running a Python powered modern Single Page web Application (SPA), yes, not more or less, in under 50 min. well 42 in fact. Yes, no kidding. Python is so wicked!
This is of course not without mentioning the author Gareth Dwyer who put a lot of effort into making this video tutorial so effective. I like how the environment was set up on all-in-the-box plain and simple Ubuntu with all what you need to develop anything.
The video shows all the development cycles from simple URL submissions, handling JSON, to user input handling, omitting the unit tests, error handling and ends with some road-map to exploring Flask add-ons and libraries to take you into more advanced topics as database integration.
Verdict: 5 out of 5. If you are in a hurry or have a tight budget, this video is for you. As a next step to mastering Flask I would recommend a more in depth book or video.