fiddling around with a date parameter in an SQL query

/ bonnu18 / Leave a comment

Hey guys!

Continuing on my previous blog post which was databases oriented, this week I ended up fiddling around with an SQL query in order to fetch the data set I was after. Diving straight into it, say we have a table in the database and this table has records of different API releases. Four columns. Unique ID, name, version number and release date, and it would look something like this.

tbl_api_version

At one point I was interested in getting which versions the API was running on from a particular date onward. If we had to look at the table above, and for instance pick the first row, API version Superman was active from 01/01/2015 till 24/05/2015, one day before the release date of API version GreenLantern. Therefore, for my query, say I was interested in getting active API versions from 01/08/2017, the query should return API versions Atom, GreenArrow and Hawkgirl. The problem I was facing was that table, in my opinion, could have been designed better and include a start date column and an end date column. Having that structure querying would turn out to be easier and in fact that is what I aimed for when building my query. The idea behind it was to create two columns, start and end, on the fly and then manipulate them to get the desired result.

The query I came up with would be the following;


DECLARE @param DATETIME
SET @param = '2017-01-01 00:00:00.000'
SELECT Version, Name, StartDate FROM(
SELECT Version,
Name,
MIN(Date) StartDate,
ISNULL(( SELECT DATEADD(day, -1, MIN(c.Date)) EndDate
FROM tbl_api_version c
WHERE c.Version = b.Version + 1 GROUP BY Version, Name ), GETDATE()) EndDate
FROM tbl_api_version b GROUP BY Version, Name) a
WHERE @param BETWEEN a.StartDate AND a.EndDate OR a.EndDate > @param
GROUP BY Version, Name, StartDate
ORDER BY Version

view raw

dateparametersqlquery.sql

hosted with ❤ by GitHub

I’ve also created an SQL fiddle which has the create and insert script, and the actual select statement. Is this the best solution? Probably not. I ran the query constantly several times and the SQL Server Management Studio was producing the results quite instantaneously so for the time being I was satisfied. I’m still not sure how efficient this would be if we had to perform this query on a table that has way more rows, for instance thousands. I have my doubts it would be efficient though since we have two nested select statements. Feel free to share your thoughts in the comments section if you would have tackled it differently or perhaps if you thought of a cleaner or faster solution to his problem.

Till my next post,
Bjorn

entity framework model did not update

/ bonnu18 / 1 Comment

Recently I was working on this project and I was asked to create a new module to create a new feature. The C# solution, as most technologies, makes use of a database in order to fetch and store data. Basic CRUD operations. As you might have already guessed from the title of this post the project makes use of ADO.NET and Entity Framework to perform such processes. I will have to admit that I am not a fan of ADO.NET or Entity Framework. Personally speaking I prefer to create scripts, stored procedures, views, etc and then open connection strings and retrieve the data as data tables/data sets. Having said that, that was my opinion, and since the project was already in a stable running state I had to adapt and work with Entity Framework, which I’m also comfortable with.

The problem happened when I created a new table via SQL Server Management Studio, let’s call it tbl_weather. This new table would have historical data and store “raw” data that would be processed later on. tbl_weather would have three columns and roughly this type of data.

tbl_weather

I then switched to Visual Studio and tried to update my Entity Framework model. Right click > Update model from database > Next > Next > Finish … job done. But wait, where’s tbl_weather?? I tried this step again and again and nothing happened. The connection string was good because my new table was being detected when attempting to update my model from the database. So what is wrong with it?

After a while I tried to delete my table and re-create it but this time round I added another column, ID, and set it as unique primary key. I tried to update my model once more and this time round the table was added to my model straight away. To my understanding it seems that Entity Framework would not accept to add “basic” tables but it would require the new tables to be somewhat more structured. In this case it seemed that not having one of the most basic SQL standard, a primary key, was preventing me from adding my table to my model.

Now, I know that a primary key is a must and that a unique identifier must be present but in this case I was fetching the data from a source and bulk insert it into this table. This data would then be retrieved again, processed and insert in yet another new, better structured table. Therefore, I didn’t really worry the primary key.

Anyways, that’s my two pence that I wanted to share with you guys, until next time, adios!

Bjorn

css tricks for text selection

/ bonnu18 / 1 Comment

So recently I had to disable text selection for some web pages and after a quick Google search I realized that this could be achieved through CSS – great stuff!! 😀

First thing I did was create a new HTML page, wrote some dummy text and applied some CSS to test it out. Loaded the HTML in the browser and tried to select the dummy text. The text could not be select – awesome! The CSS I tried out was the following;


-webkit-user-select: none;
-moz-user-select: none;
-ms-user-select: none;
user-select: none;

view raw

csstricks.css

hosted with ❤ by GitHub

The different styling is added to ensure that the styling is rendered properly on different web browsers. I then dug a bit deeper and tried out different CSS implementations related to text selection. I create a working JSFiddle with the said CSS and it can be accessed via this link. The JSFiddle has three different styles

  • No text selection
  • One click, all text selection
  • Different colors shown when selecting text

Until next time,
Bjorn

loading a video at client-side level

/ bonnu18 / Leave a comment

Imagine a scenario where you want to load media, or any other form of content, from the user’s computer rather than retrieving the content from the web server and consuming bandwidth every time the pages is loaded or refreshed – and this can be quite pricey if the content you’re loading is a video. That being said, having a web application that can access the user’s file directory poses a serious security threat as this makes the user’s computer vulnerable to threats with not so friendly intentions. So, whilst keeping this security issue in mind, let’s create a scenario where we retrieve the HTML, and other web content such as CSS and Javascript, from the web server but load the interactive content from the file directory.

Let’s start by creating a simple HTML file and add a HTML5 video tag linked to a video.


<html>
<head></head>
<body>
<h1>Hello World</h1>
<video width="640" height="360" controls="controls">
<source src="./PlayStationStartup.mp4" type="video/mp4">
</video>
</body>
</html>

view raw

videoclientsideone.html

hosted with ❤ by GitHub

Run this on your browser and it should look something like this.

1

At this point let’s try and mimic the behavior of a web server by loading this HTML file on the Internet Information Services(IIS). I created a simple web site with a specific port(3001) and set it’s directory to where the HelloWorld.html is. Typing http://127.0.0.1:3001/helloworld.html in the browser’s address bar loaded the page as intended and the video still running just like it was before. So far so good. At this point the video is still in the same directory as the HTML file. I re-modeled the file system and created the following structure:

ROOT
– Server One
– – HelloWorld.html
– Server Two
– – PlaystationStartup.mp4

Server One is the web server where the HTML file is hosted and Server Two is the location of the video on the user’s computer. When I tried to edit the source of the video and changed it to the direct path, the video did not load and the following error showed up in the browser’s console.

2

One way to work around this is to host the video on the IIS at client-side and then make a reference to it, specifically by its port and file name, from the HTML file. I created another site on the IIS and mapped it on the new Server Two folder. I then edited the HTML file and the value of the source attribute was changed as below.


<html>
<head></head>
<body>
<h1>Hello World</h1>
<video width="640" height="360" controls="controls">
<source src="http://127.0.0.1:3002/PlayStationStartup.mp4&quot; type="video/mp4">
</video>
</body>
</html>

view raw

videoclientsidetwo.html

hosted with ❤ by GitHub

The web page was refreshed and the video loaded as intended. Even though the above is one way to load content from the user’s computer I still think that ideally content should be retrieved from the web server. However, in case of a closed environment and with certain strict conditions, it’s good to know that there are alternatives.

Adios,
Bjorn

a new blog

/ bonnu18 / Leave a comment

I’ve been wanting to make my presence felt on the web for months, just a small application but enough to have my own space where I can share my thoughts. Today, whilst driving back home I decided that a blog would be ideal to share my software developer ideas and ideally help others who might be stuck on an issue.

The idea behind this blog is to casually write posts about coding, a mixture of languages, with the intention of helping others out. It could be a student who just started using HTML and is finding it hard to finish their assignment or a more experienced professional who is stuck on an SQL query but maybe a different perspective is all they need to overcome that hurdle.

If in any of my posts you happen to notice any technical errors, or even grammatical errors, do contact me and I’ll be sure to update it. At the end of the day no one is perfect and I’m no where close to perfection.

I opted for WordPress, one of the most popular (if not the most) CMS which is free to use, with lots of support and an endless list of plugins and themes. You’re probably thinking that as a software developer I should have developed my own application and whilst I fully agree with you, at the same time I wanted an application which is low maintenance so that I can focus more on the posts and its content. Unfortunately I live quite a busy life so I went for efficiency with minimum time wasting.

That’s a wrap for today so stay tuned for future posts and see y’all soon.

Peace out,
Bjorn