Keona Health Q&A

Keona Health Q&A

EXERCISE #1: SQL

	I made up a DB Fiddle demonstrating this answer: https://www.db-fiddle.com/f/bM61e774csDyG7F4ogoy7E/0 

– Persons and Orders Tables
CREATE TABLE Persons (
 PersonId int(8) AUTO_INCREMENT PRIMARY KEY,
 NameFirst VARCHAR(48),
 NameLast VARCHAR(48)
);

CREATE TABLE Orders (
 OrderId int(8) AUTO_INCREMENT PRIMARY KEY,
 PersonId int(8),
 OrderDateTime TIMESTAMP,
 FOREIGN KEY (PersonId) REFERENCES Persons(PersonId)
);

-- Mock Person data
INSERT INTO Persons(PersonId, NameFirst, NameLast) VALUES(1, "Jean Luc", "Picard");
INSERT INTO Persons(PersonId, NameFirst, NameLast) VALUES(2, "William", "Riker");
INSERT INTO Persons(PersonId, NameFirst, NameLast) VALUES(3, "Lt. Cmdr.", "Data");
INSERT INTO Persons(PersonId, NameFirst, NameLast) VALUES(4, "William", "Riker Jr.");

-- Mock Orders Data
INSERT INTO Orders (OrderId, PersonId, OrderDateTime) VALUES (1, 1, NOW());
INSERT INTO Orders (OrderId, PersonId, OrderDateTime) VALUES (2, 1, DATE_ADD(NOW(), INTERVAL 1 HOUR));
INSERT INTO Orders (OrderId, PersonId, OrderDateTime) VALUES (3, 3, DATE_ADD(NOW(), INTERVAL 1 HOUR));
INSERT INTO Orders (OrderId, PersonId, OrderDateTime) VALUES (4, 3, DATE_ADD(NOW(), INTERVAL 2 HOUR));

– QUERY: Retrieve Every Person and their order; include all persons even those without matching orders
SELECT
 p.PersonId,
 p.NameFirst,
 p.NameLast,
 o.OrderId as "LastOrderId",
 o.OrderDateTime as "LastOrderDateTime"
FROM Persons as p
LEFT JOIN Orders as o ON p.PersonId = o.personId
ORDER BY p.PersonId, o.OrderId;



EXERCISE #2: High Availability Upgrades

	I haven’t done a lot of professional work in availability upgrades and clusters, but I think I know how I’d approach this for SQL Server instances.

	SQL Server has a good clustering tool called “Windows Server Failover Clustering”. My understanding is it spins up multiple nodes for the same instance of SQL Server, and “fails over” to another node when a hardware or software failure occurs (it uses logs and a polling technique to determine if unresponsiveness is a failure or not). 

	I don’t think WSFC automatically handles transactions, though, so I would need to sync the transaction logs between nodes so that if one failed there wouldn’t be data loss associated with current transactions and the next node could compare the transactions and the database state. I’d also assign a static IP to the SQL Server instance so that a DHCP lease renewal would not cause unnecessary downtime for the nodes.
	
	

EXERCISE #3: JavaScript
	Demonstration JS-Fiddle: http://jsfiddle.net/bamalone/yp3c8qd7/40/

	As a note, one of the “gotchas” I think applies here is the date comparisons should be between the start of the day both days, given that new Date() returns the current date and time. But the question specifically wants only the difference in days exactly. Also, I opted for using the MomentJS library which I find very handy for JavaScript DateTime handling.

HTML			
<div class="container">
  <h4>Exericse #3: JS Dates</h4>
  <hr>
  <p class="days-difference">
    <b>1. # of days between 1/1/2017 and 11/28/2018, start of day</b>
    <br>
  </p>
  <br>
  <p class="today-formatted">
    <b>2. Today datetime formatted AS MM/D/YYYY xx:xx:xx am/pm
  </b>
  <br>
  </p>
</div>


JavaScript
var $daysDiff = document.querySelector(".days-difference");
var $today    = document.querySelector(".today-formatted");

var today = moment(new Date());
var todayStartOfDay = moment(new Date()).startOf('day');
var januaryDateStartOfDay = moment([2017, 0, 1]).startOf('day');
var days_difference = todayStartOfDay.diff(januaryDateStartOfDay, 'days');

$daysDiff.append(days_difference);
$today.append(today.format("MM/D/YYYY hh:mm:ss A"));



EXERCISE #4: C#

	First, I’ll assume I have some local host SQL server instance. I’ll also add a namespace since I might want to group Order or person related code. Then an Orders class for interfacing with Orders DB data so I don’t throw spaghetti inside main(). To execute the stored procedure and get back results, I’d use a command object, and then loop the returned results from the sproc and place them in a List of new person objects. I’m assuming I can set Person fields after instantiation, but I could also pass them into a Person constructor.

	A C# program doing this might look something like (PersonOrders.cs):

using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace PersonOrders {
	
/**
* @summary
*	Class for interfacing to the Orders DB.
*/
public class Orders {
		
	/**
	* @summary
	*	Returns list of Persons that have orders for the parameter datetime.
	*/
	public List<Person> ordersByDate(DateTime orderDT) {
	List<Person> list = null;
			
	try {
		// TRY opening DB connection
		using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=Orders")) {
					
			conn.Open();
					
			// create a command object for stored procedure
			SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);
			cmd.Parameters.Add(new SqlParameter("@OrderDateTime", orderDT));
					
			// READ values from Sproc, and add new Person for each result
			using (SqlDataReader reader = cmd.ExecuteReader()) {
						
				while (reader.Read())
				{
					Person p = new Person();
						
					Person.PersonId = sqlReader.GetString(“PersonId”);
					Person.NameFirst = sqlReader.GetString(“NameFirst”);
					Person.NameLast = sqlReader.GetString(“NameLast”);
							
					list.Add(Person);
				}
			} // END Reader-Loop
					
			// Done. Close the connection.
			conn.Close();
		}
	}
	catch(Exception e) {
		// Error Handling! Something useful.
	}
			
	return list.ToArray();
}
		
	public static void Main(string[] args) {
		Orders orders = new Orders();
		List<Person> personsOrdersToday = orders.ordersByDate(DateTime.Now);
	}
		
}
}


EXERCISE #5: New .NET App

	Technologies – A list of technologies involved would be something like Docker, GIT, Visual Studio Code, ReactJS, Cypress/Web Driver and probably N unit.

	Unless there was a set of existing requirements or the like, I’d probably roll a docker project. All (enterprise) applications often need to not just be developed locally but deployed to various environments – staging, testing, production, etc. Docker is the best tool I’m familiar with and like for deploying across various environments. 

	For example, I could write a Dockerfile similar to the below that would run on both Windows and Linux machines:


FROM microsoft/aspnetcore:2.0 AS base
WORKDIR /app
EXPOSE 80
# Copy project files
…
# Copy and add packages, if needed, here too
...
# Copy “root” directory to root in Docker Container
COPY . . 

# Run dotnet build and publish
RUN dotnet build … -c Release -o /app
RUN dotnet publish … -c Release -o /app

# Set Entry point / Docker runs this to start the application 
ENTRYPOINT [“dotnet”, params]


	Ideally, I’d also version control the project under GIT, just because I prefer GIT to SVN. I’d make a master branch and probably separate branches for changes to the Docker / docker compose files and branches for project features. Maybe even some git hooks to deploy or run tests on commits (unit tests in Nunit). I’d want to roll end-to-end tests, as well, and while I’ve mostly used Selenium derivatives for end-to-end testing I’ve been using Cypress lately and I think I’d go with that – built in dashboard, atuomagic logging, and a reasonably simple API for testing where I don’t find reasons to need to manually grab a web driver to interact with pages during tests.
	I've also had experience with various front end frameworks (Angular, Backbone, jQuery, React), but so far I think React is the most clean and it lends itself well to being very testable. It's also super fast and pushes developers to write smaller (testable!) components and smarter state management. For example, AngularJS got crazy with $scope soup and binding way too many things and it was not very performant with large amounts of data syncing to the UI (Angular5+ seems to be fixing some of these issues, though).