Last week our focus was on databases.

Databases hold a special place in my heart. When I was on active duty, one of my jobs was to create a Lotus Approach database to replace a mainframe database. Long story short, I ended up entering my current profession primarily because of the power I saw in databases (at least, that’s how I would have described it at the time, now I’d describe it more as ‘the power of big data’).

Data! Data! Data! I can’t make bricks without clay!

Background

Before I go on, I’d like to apologize to my readers who have patiently waited for last week’s update. I normally start drafting right after class on Saturday afternoon, have a completed draft before going to sleep Saturday, and then will polish it on Sunday or Monday, with the goal to have it officially posted before the start of Tuesday’s class. This weekend, I started tackling a new project and was so focused on that, that I completely forgot to do an entry about last week. But, the project itself involved some related information, so I think this post will be a bit better for the wait.

The job I mentioned earlier involved learning about databases pretty much by myself, without the internet, and without any books [1] (it was the mid-90’s and I was on a Marine Corps base in Okinawa, Japan; we thought we were the bomb because we had email addresses!). My dad had a little bit of database knowledge, so he was able to explain relational databases, and in particular, why they were important, but other than that my sole resource was the very, very, very sparse help documentation within Lotus Approach. Since my time in the Marine Corps, I’ve done one other massive database project (that project was in Microsoft Access, but it pushed the limits of Microsoft Access [2]). I have to admit, knowing what I know about SQL[3], I was really curious about how DevMountain was going to tackle both SQL and noSQL databases in a week!

Coming into last week’s lessons, I was extremely rusty with SQL and had never worked with a noSQL database. We ended up spending Tuesday and Wednesday evenings with MySQL and Thursday and Saturday with MongoDB. I have to admit, for four days we covered a fair bit of ground.

MySQL

Ian Platter was our SQL instructor, and I have to admit that I was really impressed with how much he was able to cover in six hours! In my opinion, the sole failing was the complete lack of coverage of the ‘relational’ aspects of SQL databases. It’s something that I know I struggled with a lot when I was learning Lotus Approach, and it’s something that I think is absolutely critical to really understanding SQL.

Because of my previous experiences and understanding of relational databases, when I first heard about noSQL databases I wondered how in the world they could compete with the performance of SQL databases. But to truly grasp the benefits of SQL, one really has to understand the ‘relational’ aspect of SQL databases.

At the end of the week, we were asked which we felt more comfortable with - MySQL or MongoDB. If you had asked me before the week started what I thought the response would be, I would have bet on noSQL. In fact, Ian did such a phenomenal job covering MySQL that it won the student vote by a very large majority!

MongoDB

Britton was our MongoDB guide, and he started off pretty solid, but it’s clear that HTML5 and CSS3 are his domain and passion. This is not to say he did a bad job. In fact, he covered MongoDB enough that I was able to start hacking on a new project using it this weekend. (So it’s his fault this post is late!)

Results

Like the past few weeks, a couple of the projects this week require a running server to view, the others are more of a task-command listing, so I’m linking directly to GitHub source code instead of a working demonstration of the page. For the task-command listings, I’m also including the tasks and my commands.

MySql

• sql-practice
  Use a MySQL Client Query window to complete the following tasks:

  1. Add a column to represent when this post was created
     ALTER TABLE posts ADD createdAt DATE;
  2. Add a column to represent what the current status is of the post (draft, publish, archive)
     ALTER TABLE posts ADD status VARCHAR(10);
  3. Add a column to represent the number of views this post has received
     ALTER TABLE posts ADD views FLOAT;
  4. Add a column to represent the subtitle for this post ALTER TABLE posts
     ADD subtitle VARCHAR(50);
  5. Insert a published post created yesterday with a title and body of your choosing
     INSERT INTO posts (title, body, createdAt, status) VALUES ("Hello world", "Welcome to SQL", '2014-10-27', 'published');
  6. Insert a published post created last Tuesday with 100 views and a title/body of your choosing
     INSERT INTO posts (title, body, createdAt, status, views) VALUES ("Hello galaxy", "See Sean rock SQL", 'published', '2014-10-21', 100);
  7. Insert a post created today (now) that is a draft and a title/body of your choosing
     INSERT INTO posts (title, body, createdAt, status) VALUES ("Now", "seize the now!", NOW(), 'draft');
  8. Select all posts that are published
     SELECT * from posts WHERE status='published';
  9. Select the post with id = 2
     SELECT * from posts WHERE id='2';
  10. Select all posts that have more than 1 view
      SELECT * from posts WHERE views>1;
  11. (Black Diamond) Select all posts that were made in the last two days
      SELECT * from posts WHERE createdAt>DATE_SUB(NOW(), INTERVAL 2 DAY);
• lawyer-scoreboard
  : Angular front-end pulling in MySQL data

noSQL

• mongodb-practice
  You’re creating a database for a small-time CRM (Customer Relationship Management) system.

  1. You’re creating a database for a small-time CRM (Customer Relationship Management) system.
     Create a collection of customers.
     1. Add a new customer with a business_name of Acme, num_employees of 1200, account_value of 50000
        db.customers.insert({business_name: "Acme", num_employees: 1200, account_value: 50000})
     2. Add a new customer with a business_name of Apple, num_employees of 35100, account_value of 23000000
        db.customers.insert({business_name: "Apple", num_employees: 35100, account_value: 23000000})
     3. Add a new customer with a business_name of Ma&Pa, num_employees of 15, account_value of 1200
        db.customers.insert({business_name: "Ma&Pa", num_employees: 15, account_value: 1200})
  2. Now, practice querying for results:
     1. Select all customers with an account value of greater than 10000
        db.customers.find({account_value: {$gt: 10000}})
     2. Select only names of businesses that have less than 2000 employees
        db.customers.find({num_employees: {$lt: 2000}}, {business_name:1, _id:0})
        3.Select all customers sorted by number of employees (ascending, or lowest to highest)
        db.customers.find().sort({num_employees: 1})
     3. Select the id of customers whose business name’s begin with ‘A’ (hint: http://docs.mongodb.org/manual/reference/operator/query/where/#op._S_where)
        db.customers.find({$where: "this.business_name[0]=='A'"}, {_id:1})
  3. Let’s modify some of our records:
     1. Add a rep for each record in the collection:
        • For Acme, rep name: ‘Wile E. Coyote’, employee #: 4311
          db.customers.update({business_name: "Acme"}, {$set: {rep_name: "Wile E. Coyote", employee_num: 4311 }})
        • For Apple, rep name: ‘Fan Boi’, employee #: 1216
          db.customers.update({business_name: "Apple"}, {$set: {rep_name: "Fan Boi", employee_num: 1216 }})
        • For Ma&Pa, rep name: ‘Jedediah’, employee #: 5918
          db.customers.update({business_name: "Ma&Pa"}, {$set: {rep_name: "Jedediah", employee_num: 5918 }})
     2. Update every customer to have an active status of true
        db.customers.update({},{$set: {active: true}}, {multi: true})
     3. Update Ma&Pa to have 20 employees and an account_value of 2500
        db.customers.update({business_name: "Ma&Pa"},{$set: {num_employees: 20, account_value: 2500}})
     4. Select the rep names of every customer
        db.customers.find({},{rep_name:1, _id:0})
  4. Kelsey challenge: Delete a record/document (I deleted my Acme entry)
     db.customers.findAndModify({query: {business_name: "Acme"}, remove:true})

• extinct-mammals
  : Node back-end exposing the noSQL data

Post-class project

So the reason this post is late, is that I got uber-focused on a project. I don’t want to introduce the actual project yet, but the stack is a basic MEAN stack. I had run into a hiccup with our noSQL project, which I was using as a template for this new project. So I had the backend mostly up within an hour or so, but then ran into this bug