Chopping database records

I have been working on a project recently for a client which required the user to insert a record into an existing set of records and to modify those existing records to accomodate the new record.

The above diagrams shows what needed to be accomplished. If you imagine from left to right is time on the diagram, each record has a start date and and end date and the new record is needing to be inserted between those start dates and end dates.

The start and end dates can overlap the start and end dates of the new record and this is where the existing need to be chopped and modified to accomodate the new record.

So how did I go about this?

Firstly I decided to bucket the existing records into categories as follows …

And like …

This gives us four actual categories and a fifth “Ignore” category. The four actual categories are LeftRight, Middle, Left, Right. Some more examples are shown below …

The bucketing of the existing records into categories from a coding perspective is accomplished as follows …

Firstly I selected the existing records, pseudo code shown below …

dateStart = new record start date
dateEnd = new record end date
dbDateStart = existing record start date
dbDateEnd = existing record end date

Other columns could be added to this selection code if needed, in some cases a “user_id” or other foreign key could be potentially needed.

Using the above selection code will select all the existing records that exist within the range of the new record start and end dates. In other words the records that fall into the “Ignore” category will not be selected.

This is why the “Ignore” category is not a real category as the records are never selected and thus do not need to be bucketed.

Now I have selected my records from the database I put the records into a loop. Whilst in this loop I bucket the records into a set of arrays using the following conditions …

LeftRight …

Middle …

Left …

Right …

Now that the records have been bucketed into the arrays I loop through each group and process the groups seperately as follows …

new record = the new record you are wanting to insert into the DB via displacing the current records
original records = the record in the LeftRight bucket that has not been cloned

LeftRight …

I take a clone of the record and I modify the original (none cloned) records date end to be the new record date start -1 day this is then saved. I then take the clone and set the date start to be the newly inserted records date end +1 day this is then saved.

Middle …

Any middle records are deleted.

Left …

I modify the records date end to be the newly inserted records date start -1 day this is then saved.

Right …

I modify the records date start to be the newly inserted records date end +1 day this is then saved.

After all of the bucketed records have been processed there should now be room to insert the new record so I insert / save the new record.

So that concludes how I created a system that would allow a new record to be inserted whilst chopping the existing records to accomodate the new records.

Select2 library solution in Yii2 part 4 – retrieving the data

To continue on from the previous article I wrote about saving the Select2 data …

http://www.jamesbarnsley.com/site/2016/11/07/select2-library-solution-in-yii2-part-3-saving-the-data/

I will now talk about how to the retrieve the stored Select2 data so that the data can be displayed on the front-end Select2 field. Following on from the Task / Countries example, place the following “loadSelect2Items” method inside the Master model or Task model if you did not create a Master model …

The code above can be explained as follows …

  1. Line 3 – return the array of Select2 data using the “array_map” function
  2. Line 4 – store the related entity models name into a variable, in this case that would be the Country models name. This uses the Country relationship that was created in “Part 2” of this article series
  3. Line 5 – store the IDs of the related relationship entity model in the return data, in this case that would be the IDs of the Country model records
  4. Line 6 – find all the records for the model / model property in the “select2_data” table, in this case that would be the Task model and “countries” property

The next step is use the “loadSelect2Items” in the Task model to the load the Select2 data into the “countries” property, place the following inside the “afterFind” method of the Task model …

So that concludes the Select2 library solution for Yii2. I have covered the creation of the necessary database table and the read / write methods for reading / writing the data.

After the initial methods have been setup this solution will provide you with a way of easily putting a Select2 field on your forms that allows for many to many relationships between models with just a couple of line of code.

My new way and correct way of coding Controllers

I have been using frameworks for a number of years with the Yii2 framework being my framework of choice at the present time. When it comes to defining how an application should be structured in terms of Controllers and their methods i was always one to put methods wherever I felt it made sense to put one.

For example my UserController would consist of index, view, create, update, delete, login, logout, lostpassword methods. This made sense at the time. Any action methods that relate to user functionality could go in the UserController. However all the time I was doing this I was aware that some developers and articles on the internet point to the fact that Controllers should only have certain methods within them, namely the standard CRUD methods and nothing else.

So one day I decided to follow that train of thought and start writing my controllers to only have the following methods, index, view, create, update, delete. These methods serve the following purpose …

  1. Index – view all records in a grid view
  2. View – view single record in a detail view
  3. Create – create form and submission code for single record
  4. Update – update form and submission code for single record
  5. Delete – delete a single record

I am aware that I could even get rid of the index and just handle the “view all” and “view single record” through the single Controller method of “view” by checking to see whether an ID parameter has been provided to the method however I thought I would keep them separate.

I am quite pleased by how the new structure has turned out in that it makes my Controller less bulky with code and it gives a much more structured neat and clean feeling to my code and software application.

So you might be wondering what happened to my UserController and the extra methods that where described earlier i.e. login, logout, lostpassword etc. Well using the new system they can be broken down as follows …

UserController

  1. Index – view all users in the system
  2. View – view single users details
  3. Create – create a user
  4. Update – update a user
  5. Delete – delete a user

LoginController

  1. Create – log the user in
  2. Delete – log the user out

LostPasswordController

  1. Create – send the user a link to change the users password
  2. Update – process the clicked link and change the users password

So it is possible to do this, basically the Controllers within the software application now take more definition by having the Controllers named after the type of event the user is wanting to make within the software application and placing less emphasis on the Controllers methods providing the definition. I really like my new way of coding my Controllers and I intent to stick to this method for the foreseeable future.

HTML5 Web Storage Implementation

Recently I have been working on a client project which required the ability to store data from a server on the users local machine. In this case the users local machine was a mobile phone, IOS, Android, Windows etc.

It does not really matter on the platform as it will be stored using the mobile phones web browser and most of the browsers are modern enough to have the new HTML5 Web Storage mechanism.

In actual fact the data will be stored using PhoneGaps implementation of HTML5 Web Storage. PhoneGap is a framework that allows the user to write phone software in HTML5, CSS3 and Javascript.

So for all intents and purposes it is like writing a responsive website that works on mobile phone browsers but is actually compiled and runs like a real native software application. PhoneGap interfaces with HTML5 Web Storage for the purposes of using HTML5 Web Storage mechanism.

So to store the data locally I found WebSQL and HTML5 Web Storage to be my options. Although WebSQL looked perfect for storing and querying the data, it is actually being depreciated and will no longer be in use as a standard in future. So my mind was made up for me in that case.

Web Storage on the other hand seems very straightforward, but this would be the case as it does not include the more complex features of querying the data. Web Storage works by storing name / value pairs. So we can say colour = red, or age = 25, name / value.

Like I said this is straightforward but we cannot say give me all persons over the age of 20 for example there is no querying mechanism.

In actual fact there is no mechanism for even storing multiple persons with ages to begin with. The real structured data is left to the programmer to implement and like wise with querying the structured data.

So what approach did I use?

I decided to store the value of the data as a JSON string and the name would be the table name. So for my project this data needed to be fetched from the web server and stored locally using the Web Storage.

I setup a PHP web server that returned a JSON response upon request via the client. The client did this using JQuery standard Ajax request functions. When I got this JSON response from the server which was basically a dump of a particular database table on the server in JSON format.

I stored it in local storage using the particular database table name for the name value. So in the persons / ages example this would be Person = Encoded JSON string of all data in the server Person table.

So now we have all of data for a particular table from the server stored in local storage. I just repeat this process for all the tables I want to store locally. We now have our data.

But what about querying this data?

Well JQuery does not come, out of the box anyway, with functions for querying JSON data. So a developer might think, I know, I will decode the JSON string and write my own functions for querying this data, possibly by putting the data into foreach loops and returning the records when the data matches the developers expectations. Well that is one way to do it however I am not sure how efficient that would be.

Looping through lots of data just to find single records cannot be very efficient however this is JQuery (Javascript) we are talking about, it is not as if it was designed to do this kind of thing anyway, plus Javascript processing power is becoming more powerful as a whole now in modern day browsers.

It is still not a server side language though and does not query a real database like MySQL which was designed for efficiency, but this is not expected of Javascript anyway.

I do not want to talk about efficiency too much as it is easy to slate querying data this way when a lot of internal libraries might just be doing the same thing under the hood anyway. I cannot imagine there being another way to do it with standard Javascript / JQuery when it comes to the rock bottom.

Anyways enough talk of how querying the decoded JSON data might be handled at a lower level. We as developers have libraries for this kind of thing and luckily someone has written a plugin to do this task anyway called json-sql. Here is the link to the library …

www.trentrichardson.com/jsonsql/

The json-sql library allows the developer to query a JSON string using SQL like syntax. Admittedly it only includes a very small subset of the standard SQL functions but it is most certainly good enough for most purposes, selecting, ordering and where clause. So I used this library to query my JSON data.

So that is pretty much it, the project has a web server which sends JSON responses to the client, data is stored in JSON strings in the client local Web Storage and the data is queried using the json-sql library before being displayed to the user.

This is the way I implemented a server / client, DB synchronized, local storage web software application.