RedAlan. Los Blogs

Friday, September 2, 2022

MySQL - Procedures and Functions with optional parameters

If you use database procedures and functions in MySql or Sql, that receive parameters and that are called from back code (for example, PHP), or from any other language, you will have noticed that, on more than one occasion, if you have decided to add a parameter, you have either created an alternative procedure or function (so that the code already written is not screwed up) or you have had to revise all the back code to add, even if it is empty or null, the parameter in question.

And you may have thought... "SQL could already have, in the style of javascript, php and other languages, the possibility of creating a parameter with a default value in case it is not supplied", that is... an optional parameter. 

For example, we are going to use a very simple procedure that receives two string parameters, returning their values.

  CREATE PROCEDURE localdb.parameters_test(p1 varchar(10),p2 varchar(30) )
      select p1,p2;

If in this case we do...

	call parameters_test("hello","bye");
If we now change the procedure to... CREATE PROCEDURE localdb.parameters_test(p1 varchar(10),p2 varchar(30),p3 varchar(10) ) BEGIN select p1,p2,p3; END But if we do not change the call, we will get an error. How can we then use database procedures and functions, where, within their code, we get optional parameters, even of undefined type values? Yes, I know that now the trend is to use data typing at all times, just as from 1998 to 2004 approximately, it was recommended to use Foreign Keys to preserve the integrity of the tables, while later it became fashionable to maintain the integrity of the code and not use the related keys, or Foreign Keys. And now many already use No-SQL to avoid defining a rigid database structure. All these types of practices are up to the analyst, who has to take into account whether robust integrity, more agile code development, and a thousand other variables are desired. As an anecdote, I will tell you that many people understood that "now it is not fashionable to create related keys/foreign keys" and ended up not using key indices. You can imagine how many databases I have had to index and reindex because, at a certain volume of data, the query was taking forever. In SQL, please use keys! That said... to the point.

Optional Parameters in SQL/MySQL

To "simulate" and control parameters in database functions and procedures, we make use of the EXTRACTVALUE command, which is like a "reader" or "interpreter" of XML values. In the following basic example, you will understand it clearly. CREATE PROCEDURE localdb.parameters_test(pAll varchar(300)) BEGIN set @p1 = (select ExtractValue(pAll,"p1")); set @p2 = (select ExtractValue(pAll,"p2")); select @p1,@p2; END Call parameters_test("<p1>Hello</p1><p2>Bye</p2>");
And if we now change the procedure to accept a new parameter, it won't be necessary to change anything in the back (or source or whatever you want to call it) code. CREATE PROCEDURE localdb.parameters_test(pAll varchar(300)) BEGIN set @p1 = (select ExtractValue(pAll,"p1")); set @p2 = (select ExtractValue(pAll,"p2")); set @p3 = (select ExtractValue(pAll,"p3")); select @p1,@p2,@p3; END Call parameters_test("<p1>Hello</p1><p2>Bye</p2>");
Of course, within the procedure or function, you should check both the type, the value and whether or not the passed parameter exists to make your code understandable and useful. But that is up to you and your team (if any).

Tuesday, August 23, 2022

Scrum, Sprints, Estimates and Braggarts

And what a topic it is!

Every year, in a strange inheritance of miracle diets, magic formulas that promise to be a revolution appear in the business world, especially in the technology sector. In addition to this, there are those who, by attending a conference, chance discovery or coffee conversation (with or without mystery) with a colleague in a similar position in a larger company, "soak it up" or look it over or create a concept in their head, arrive at the office and say: Eureka!
You have probably heard of, or suffered from, the Scrum method or similar. They are working methods, really applied to any sector, oriented to groups, in principle, with a common project whose tasks are dependent.

In many cases you will attend, or you will have attended, meetings where each professional is really in charge of a sprint (the estimated time for that group of tasks), although for some reason the "specialist" on duty has mixed non-dependent tasks in a sprint, hence it is said that each professional is in charge of a sprint. The meeting goes on forever, as it is really a meeting with those present who are not all involved in the same project or who do not have dependent tasks. 

The company has just fallen victim to a braggart, and you have just suffered the consequences. For that word which the "specialist" heard and which led him to say "Eureka!", has become an absurd method of control and stressful examination of skills.

If you have understood anything at this point, you have suffered. Welcome to the age of technological fanfare.

If an agile methodology such as Scrum is applied correctly, we are in a quagmire if we are talking about services, especially if we are talking about IT development.

One of the bases of this type of methodology is time estimation. And that, in IT development, is a problem.

Let's see what time estimation would be like for a carpenter.

A carpenter is asked to make a chair. With a specific wood, varnish and shape. The carpenter has never made that specific chair. The carpenter is asked the question: when will it be ready? The carpenter has that wood. If he didn't have it, he would have to look for it and talk to suppliers, which would take an indeterminate amount of time. But fortunately for the person involved, he has the wood that has been requested. 

How long will it take? He has worked with that wood, and it takes him 3 days to make a chair. But he does not know the design, so he answers: "6 days". 

The carpenter finds that certain aspects of the design, with that particular wood, are not feasible. He has had to make some tests that have delayed him for a day. In the end he finds that for some elements he needs to change the wood. He writes an email (he is a modern craftsman) to the client who replies a day later, accepting the change of wood in those elements. 

The carpenter, when varnishing with the specified liquid, discovers that the liquid, which he also had, behaves differently on each type of wood. So he needs to change the type of varnish, writing back to the customer. The customer responds within two days, agreeing to the change. 

The carpenter delivers the job after 8 days. In this case he is lucky and the client (or project manager) understands the circumstance. 

The following month, another customer arrives asking for a design very similar to the previous one, with the same wood and the same varnish. The carpenter informs him of the changes that will be necessary, and when asked "when will it be ready?", the carpenter answers 5 days, delivering the work in 3 days. 

Is that understood? Yes, it is. Normally a computer developer does not repeat a task. Moreover, a good computer programmer/analyst will reuse threads of code (classes, traits, etc.), the initial preparation of which will take an indeterminate amount of time for the sake of saving time later on. And with this in mind, what do you think the answer to the question of when it will be there should be?

This is the big problem that a programmer, analyst or any position that needs to make a previous study of an unknown task before the time estimation. Any developer knows that, except for maintenance tasks with localised errors, something you estimate in five days can be done in two hours or a month.

Something that, no doubt, will never be understood by the agile methodologies braggart who uses it as a personnel control and who makes him feel useful with the conviction that, if it were not for him, the company would not be able to move forward. 

There are many types of workers in all companies and sectors. Some bring speed, others excellence, others creativity. A project manager who does not know how to make the most of his team is not doing his job properly. No matter how up to date he or she may be on miracle diets.