Coding Blocks - All Your Database Are Belong to Us

Episode Date: July 14, 2014

Part one of our two part database podcast starts with choosing the RDBMS (Relational Database Management System) and what to do when you run into deficiencies in that particular database system. First and foremost, what’s with the title?!  Are these guys grammatically challenged?  If that was your first thought, then you should check out this link: […]

Who downvotes a joke on Reddit? Who does that? In the programming humor? Humorless people. Poor Michael. Poor Michael. You're listening to Coding Blocks, episode 13. Subscribe to us and leave us a review on iTunes, Stitcher, and more using your favorite podcasting app.

And visit us at codingblocks.net where you can find show notes examples discussions and more and send your feedback questions and rants to comments at codingblocks.net follow us on twitter at codingblocks and on facebook at facebook.com slash codingblocks and if you can't remember those just go to www.codingblocks.net and we have all the social links at the top of the site what what yeah all right and with that welcome to Coding Blocks. I'm Alan Underwood. I'm Joe Zack. And I'm Michael Outlaw. A real quick note before we get into this episode. We had initially intended for this to go just one episode but it ended up being a little over two hours in length. So we decided to split this up so that you guys could

actually get grasp of what we're saying without being inundated with just a ton of information. So without further ado, here we go. Today, let's get started with some podcast news. The first one is, and this is kind of relevant to this particular show we're doing, is I had an article I put together about database schemas thought on product catalogs and attributes so we'll have a link in the show notes for that so that you can go over there and take a look at it this was the blog article that was about uh like how how to define like multiple products how to create a database schema for multiple product types yeah and and list their different attributes

so you know maybe a motherboard has a certain chipsets and you know memory has whatever so just ways of thinking about the schema for that it's like do you do it one to many and do you go do you go deep you know in terms of like row counts or do you go wide in terms of column count or do you go deep or do you go large in the number of tables and i didn't get into that into the into the article but we might talk about that a little bit more in a little while. Yep. And last week we talked a little bit, we talked a lot a bit about JavaScript and what it's like to work on JavaScript in large applications.

So Eric Lippert actually just put out a really nice, it's not really an article, but we'll have a link to it in the show notes. And next week, Monday specifically, we'll be going to the Atlanta Build Guild. We're looking forward to that. That sounds exciting. Yeah, we'll also have a link in the show notes for that.

Also, we got a question from somebody on Twitter regarding MVC versus web forms, and we all kind of have something to say about that. First I want to reference, though, in our OWASP episode, we did bring that out there just in regards to security. And the MVC already out of the box for all its controls, encodes, HTML for you when it comes back out and you're putting it on the page. And you don't have to do anything explicitly for that. So that's already one thing.

Let's backtrack a moment, though, because specifically the question was something along the lines of i can't remember exactly does does um does anybody does does it still have it does webform still have its place or should you just pursue mvc there's something along that in that vein yeah and personally i think that if you are going starting from scratch mvc is the way to go for a number of reasons uh the first one being though is your separation like pretty much web forms almost forces you to have somewhat of spaghetti code because you have your things in your code behind then you have your things on the page and they all get mixed up whereas mvc you could literally have a designer working on the look of your site you have somebody working on the the business programming portion of it and then you have your controller actions that kind of links them all up.

It's also built with testing in mind, which is really nice. Yeah, I would say there's plenty of web forms to work around. There's plenty of open source projects that are built around web forms. There's no shortage. It's certainly not dead. But I think if I was starting a new project,

definitely MVC. And if there are a few things that web forms does better, I would just deal with it. Yeah, and so that was when I saw this question where I was thinking I was just like if it's if it's greenfield absolutely MVC all day long you know and uh you know but unless unless you're coming in to deal with something legacy and it's web forms then you might be stuck now if it's if it's a small enough project and you can do your client the favor and go ahead and do the upgrade without any major ramifications,

then maybe that's something you want to consider. But if it's a large-scale application, then that might not even be something you can put on the table. The only thing that I will put in Webforms camp, I'll say, is if you had a group of Windows programmers that needed to port something over to the web so their skill sets are more linked to that type of programming, which is what web forms grew out of anyways, right? It was Windows programmers. They were trying to convert to the web.

So if you have a bunch of resources like that and you don't have actual.NET web developer type people, then maybe you go web form simply because you have a skill set that transfers over easily. But if you actually have a team of people that know HTML, know JavaScript, CSS, all that kind of stuff, there's no question you should go MVC. Now, here's the trick question. What about combining them? If you've got a legacy web forms app and you want to do something new, absolutely.

But putting web forms into MVC, I would just say just suffer. Don't do the inverse. But if you wanted to add MVC to web forms to take advantage of that, now you will run into some problems. You got your routing issues and all that. So I've actually done this. And it was a time where you're bringing in new code and you're trying to migrate things to go the new way.

But it was a little bit tricky. So one of the things that you're going to run into is like anything existing is going to be scrapped. You're not going to be able to reuse any of your web form controls inside of that. So that's something to consider. You know, it would have to be limited purpose that, you know, if you were going to like bring this into a monster uh existing legacy app but uh you know and then the other trick to make that work though and i don't know the goo it off the top of my head but you can google for it is that in your existing web forms project you would have to add in an additional gooid into it so you'd have to manually edit it in a notepad or something like that and add that guid in there so that

visual studio would recognize that this could have controllers and things like that in it in order for it to work yeah it can be done but going back to this question like if you were going to start off oh yeah all three of us would say go mvc greenfield possible yeah and i would say even you know if you've got developers who aren't familiar with the web and they're kind of trying to go the web form route because of the view state and because it makes things easier, I would say it's a good time to start learning web development. It's quite popular these days. Can you imagine really trying to talk somebody into bringing in view state?

Oh, man. View state drives me crazy. I know it does a lot of good and i get it but man as someone who kind of um you know like learned html before dot net it really drove me crazy they kind of abstracted away all that stuff that you know that works all right without it yeah that's that's the one very frustrating thing is is they try and get you to use all their control so you have very little control yourself over what's happening and by the way the person who brought that up was brock signori maybe i'm not sure if i said that right but yeah it was at brock beat though i could say that okay there we go at brock beat b-r-o-c-b-e-a-t the one name we

get to say that i could actually pronounce and you guys take it from me yeah so um thanks for the question brock uh hopefully that answers it for you. And if not, you know, ask us some more. We'll respond. Yeah, and actually we mentioned a few links already, like Build Guild and whatnot and the dynamic languages. But I wanted to mention, Raj So Tweet mentioned on Twitter that we have decent show notes and wanted to let you guys know, in case you haven't heard,

that we do put a lot of links and show notes and everything for every episode so rather than trying to write this stuff down you can just go to the website www.codingbox.net slash episode number or just go to the home page you can find it there's tons of links and other stuff yeah and and we we weren't trying to laugh at his expense but it is tweet he actually said man I've been rewinding and taking notes and rewinding and taking notes. We were like, oh, yeah, sorry, dude. You should have just gone up because we literally, everything in here, this isn't like other podcasts where people just talk and then it disappears. You can go up and pretty much everything we mentioned on this show

is going to make its way into the show notes. So we should also mention in that vein, though, we have actually talked about possibly doing transcriptions of the shows, too. So maybe if somebody knows of a good service or something like that, they could hit us up on Twitter or on the site or something. Hit us up at comments at codingblocks.net and let us know if you know of a good service for that. Yep. Or if you just want to come to our contact form,

we never mention that, but you come to www.codingblocks.net and just hit contact us and type in the information there so either which way if you want to open up your email client or you just want to go to the site either way yep and while we're talking about twitter i wanted to mention a couple things i've been tweeting like crazy about the steam summer sale just happened so if you're a gamer, then I feel for you because it was brutal this year. I spent a ton of money. I bought a bunch of games I haven't even installed yet.

You in trouble? No, the wife doesn't listen to the show, so our little secret. But my favorite one I got... No one will hear it. ...was Divinity Original Sin. Now, if you're an old-school RPG fan, stuff like Fallout or Baldur's Gate

whatever, if you're that kind of nerd then this game is right up your alley. You're going to love it. It just came out. You're going to love it. I was just thinking about this because I finally tried Steam streaming which is the thing where you can stream games from another computer to your laptop or whatever.

So I am playing this game on my laptop while the game's actually running upstairs, and I'm doing it over wireless, and it's perfect, especially with a turn-based game like this. It's been fantastic. Okay, not like an FPS. You wouldn't be able to do that more than likely. That would be rough, but it works surprisingly much better than I ever thought it would.

Very cool. That might be a possibility. Maybe not soon. Maybe if I had a plugged-in Ethernet cable, it might be better. But I won't be playing Payday 2 anytime there. But anytime soon. All right. Well, we also went to a Xamarin meetup here recently.

We had a lot of great information that we got from that. It was very interesting hearing what other developers were doing with Xamarin and how they were using it. If you're not already familiar with Xamarin, Xamarin is a tool that allows you to write your C-sharp code, but then cross-compile that into an Android application or an iOS application. So you could have the ultimate goal here, right? You write it one time and run it everywhere, and that's the hope and goal with Xamarin. Wait, best part, you write it in C-sharp

and run it anywhere. Yeah, yeah. That is cool. And you can use Visual Studio. Yes. So a couple of interesting things. We're not going to go in this fully in-depth because this show is not about Xamarin today, but one of the things that is cool is you write it in C sharp but typically what they said is you write maybe 70 to 75 percent of your code that gets reused everywhere so like your business classes and that kind of stuff and then the interface the actual UI type stuff you end up rewriting for most each platform so they said that 70 to 75 percent of your code is shared across the platforms, and then your UI tier is pretty much done specifically for iOS or Android or Windows or whatever. So it's a really cool concept. Met a lot of great people.

Roger Peters was somebody who presented there. Fantastic stuff. You should check him out. Hit him up on Twitter. He's written some games that he actually originally wrote in java then he brought over to xamarin for windows and ported that over to ios so if you go into the apple store and you look for word search i believe it was the name of the game right no it was oh great it was uh like my big word book we'll put it in the show notes yeah

we'll add that to the show notes. But he's done something on there. I've actually run it on my Android phone, and it looks good. And so, you know, check it out. This is what people are actually doing with Xamarin. So really cool stuff. And along those, go ahead. I was going to say, you know, when I first heard of Xamarin, I was like, okay, that's kind of cool. But, you know, I feel a little weird about starting a new project on a platform I'm not familiar with.

And, you know, in the language that I'm familiar with and having this weird layer of extraction. But, man, let me tell you, if you have a big code base and you don't want to port all those objects over, if you don't want to redo that logic in another language, if you don't want to maintain that, then this is a perfect option. And really, from what I've seen from Xamarin, it looks like a great option for Greenfield-type stuff, too. You know, why mess around with the stuff you're unfamiliar with when you can focus on the actual application itself it's just beautiful this doesn't come from you trying to screw with uh xcode at all does it or uh or objective c oh man absolutely does absolutely all right so uh with that though we also we met a bunch of great people

there and the the zamarin meetup was actually hosted by a company called blue tube and they were the atlanta users group and again fantastic time we learned a lot and got a lot of useful information they did a pretty deep dive on some of it and go ahead no i was just gonna i felt like we would be doing ourselves a disservice if we didn't correct it so it was the word search little books was the uh Search Little Books was the app. But he did have a new one out there. It was called Match & Go. And you can find him at smartypantscoding.com or on Twitter at Smarty P.

At Smarty P, yeah. And so Bluetooth did the Xamarin meetup. They hosted it. It met a lot of great people there. Paul Pisano was one. And they've got another meetup coming up in Cincinnati, Ohio. I forget the date. We'll get it in the show notes.

But definitely, if you guys get a chance. I mean, they didn't bring a bunch of fluff. Like, there was a lot of content to this. So, if you're interested in Xamarin going to be up in Cincinnati, Ohio, definitely go check them out. You know, it's funny, last episode we talked about, you know, like leveling up as a coder and career paths and yada yada. We didn't really mention meetups or going to user groups. It's a great way to learn without having to invest that much time. So you get a kind of high level overview, you get to see a nice demo and just get to see what's out there without having to invest a lot of your own time. Yeah. And really the cool thing is even if you don't think that

it's probably what's going to be right up your alley, you should probably check out some of these anyways, because I've been to some that I didn't think I'd be that excited about. You get there and you find some really cool stuff and you meet a lot of great people too. So definitely something to check out. Yeah. I always thought it was kind of fun idea to like go to a meetup that you know nothing about just like kind of search one for the week and say all right what do i what absolutely sounds foreign to me and just go and see what happens it's just like a water cooler talk right basically you go from one thing and so many more topics come up that you didn't anticipate or expect so you get a wide breadth of knowledge real quick that you know if you want to dive deeper on

you can um and so the last thing we have under the podcast news for today is uh reviews in itunes so currently we have 14 five-star reviews in itunes and we'd love you to be the 15th yes yes so please if you would um you know i know you guys listen in the cars and all that but if you remember please when you get home or on your iphone you know when you're taking a break at work, drop in, click the five-star. Four would be rough, but if you go ahead and do that five-star and leave us some feedback, that would be awesome. If you're the 16th five-star, we won't take it away from you, or if you're the 17th or the 18th. Right. 17th or the 18th right oh but we will we do want to point out that uh pgr underscore atl left us a great review in itunes as did a3 electronics with a ks at the end we appreciate both of those so

thank you very much for taking the time to go up there and leave us some feedback you're awesome yep all right so moving right along yep here we go so oh yeah that's me so this week we are talking about SQL and I'm surprised that no one jumped on my throat there that's often a point of contention there I actually call it sequel and everyone I know is called it sequel but I've heard rumors of people actually saying sq out that's just too much so sequelsql is much better yeah but uh speaking of that and kind of ignorance around sql how much should devs really know i mean we've got orms now right do i even need to know how to write queries anymore man this is this is a really tough topic

as far as that's concerned because depends on what you do right like if you're working on websites you probably need to know a lot about it and depending on the type of stuff you're doing on the website, if you're writing reports, you better know a lot about it. I don't even think it necessarily like you have, you don't have to be the guy working on the website, but you could still be impacting the guy working on the website. Absolutely. Yeah. So, I mean, to Joe's point, you know, I'm thinking specifically in like an entity framework kind of world. Yeah. You still kind of want to poke around and see like what gibberish got spit out for you.

And, uh, did it make sense? Well, even just while you're working, there's going to be a point when you're like, Hey, the data I expected to come back,

didn't come back. And if you can't just go to the database and write a little quick query to see what's in there, then you're going to be spending a lot of time debugging. Yeah. So I would say that starting out obviously as with anything programming wise you're not going to know everything but definitely you should know all the types of joins i would say as a developer you need to know all the types of

joins indexing we're going to go over in this so you'll know a little bit about it but you at least need to know what it does partitioning that might be a dba's job you're probably not going to go over in this so you'll know a little bit about it but you at least need to know what it does partitioning that might be a dba's job you're probably not going to be partitioning tables yourself as a developer uh it depends on how many hats you wear in the company but there's definitely a set of things that you need to be aware of and we're going to try and go over all those or at least a ton of those in this just things that we we feel that you use on fairly a daily if not weekly basis yep and you know i'm sure everyone out there knows somebody or maybe you know does this themselves where basically it's like you get kind of lazy and you don't want to join so you kind of select from one table and you copy paste the id and you select from another table and you paste

the id and that's something i try to stay away from because eventually i end up needing that query and it's just so much easier if you just write it up front. Yeah, and save it off in like a miscellaneous folder where you can just go back and get that stuff. So let's start on one topic. How do you choose your database server? And I did a ton of reading on the various different opinions on this because as everybody or as most people on the web know my sequel is pretty much the most popular open source database on the planet all

right does that mean you should use it maybe maybe not michael's got a huge small i can't wait for you to finish this oh yeah i have no idea where he's getting ready to go you're about to get pounced on yeah so my sequel is huge uh it pretty, like I said, open source-wise, it kills it. Now, you start getting into other things like Postgres. Depending on where you look it up, I think it's actually pronounced PostgresQL. So this one is open source like MySQL, except it's more along the lines of an oracle database so if you need true transactional stuff and acid then postgres sql is the one open source free one that you can go for that has more of a real grown-up type approach to things uh then you get into the big boys like microsoft sql server and oracle

and there they they have their express editions but You didn't mention DB2. Like, wow. Okay, DB2 still exists. That hurts. Well, you didn't mention DB2. You might as well mention Access since that's probably a little more popular than DB2. And FoxPro. Okay.

So here's the thing. There was an article I read that was pretty interesting. So somebody was asking, should I go with Microsoft SQL Server Express or Oracle Express? And here was an interesting thing. If you think it's never going to grow past a certain point, choose either one of them. It doesn't matter, right? But if you think it's going to outgrow because both of them have space limitations that if your database grows past a certain point, you can no longer use it. And you have to upgrade to either the big boy SQL Server or the big boy Oracle.

And then you're starting to run into bugs. Yeah, big boy bugs. Okay, this is where I was going with this, though. Because we all know the real answer. How do you pick your database? How much money do you want to spend? That's question number one. How How much money you want to spend? Yeah. Yeah. Like that, that's, that's question number one. How much money you want to spend?

Because if you're not willing to come out of pocket, then right out of the gate, you're looking at something like Postgres or MySQL or NoSQL, you know, you're looking at an open source kind of alternative right out, right before you even start. Absolutely. So even Microsoft SQL Server Express or Oracle Express, the only reason I could possibly think to use those if I was working on a little side project that I was messing with. If I was really planning on putting anything in a production environment,

if I didn't have funding for it, I'm probably not using either of those unless I've got like a shared hosting space or an Azure where I can pay a certain amount a month to use those kind of features without really blowing it out. Because paying for these licenses out of pocket, you're going to kill yourself. It's horrible. You mean you don't have an Oracle rack install at home? Right.

Come on. I got one of those in my car. It costs more than your house in most cases, right? So that's one interesting thing. Now, going back to the open source ones, because I think this is where a lot of people will be interested in just hearing about databases. So let's start with MySQL. One of the reasons why it's so incredibly popular and you guys jump in whenever you want is because it's fast. It's really fast and it's free, right? Some of the downsides to MySQL are they don't use standard ANSI SQL language. They have their own thing. So

they don't have these things like common table expressions, which other languages have adopted. They don't have, um, like their sub query language is not standard and it's not fast. It doesn't perform all that well. If you're doing straight SQL statements out of it, it's good. They're ACID. They are not truly ACID compliant. So they do have, they do have transactional, um, implementations in place so that if you have foreign key constraints and all that stuff, it does do ACID on that. However, triggers don't enforce that. So they aren't fully ACID compliant all the way through. And ACID basically means that if you have a transaction or if you have something that fails in a transaction,

it rolls it back or it commits it all. That's to basically make sure that your data's integrity stays good. MySQL doesn't comply all the way across the board, whereas something like PostgreSQL does. So there are things like that. But there's little differences between all of them, though. I mean, it doesn't... There are.

I mean, even between, like even even if we look at you know the upper end where you're going to come out of pocket right and you're looking at like a db2 or a sql server or an oracle and there's differences even between like something like with uh i ran into one recently i want to say it was something like oracle the string values you know wouldn't take in a null a true null for it or something like that. Like, I forget what it was. And it went, and it went back to like the early days of Oracle and they had made a design decision early on and they just decided, okay, well, we're going to carry this forward, you know, all these years later. But I will say though,

when you get into most of the other big boy databases, they really are fully compliant across the board. Like if you're worried about data integrity, they're going to work. Right. Oracle, SQL Server, DB2, those have. Well, yeah, but what I meant, though, is like, you know, you were you were I felt like you were beating up on MySQL in terms of like query, but you know, in the differences of how, if it's query structure, um, and like how you might write your, yeah, the actual syntax of how you might write the query from my SQL versus something else. And I felt like, well, come on, I've, I've had problems where like, you know, especially if you're, if you do any kind of function calls inside your query, you know, the function call for Oracle might be a little bit different than the one in DB two might be a little bit

different than the one in DB2, might be a little bit different than the one in SQL Server. So, I mean, they all have their trade-offs in that regard. Well, one of the big selling points for MySQL other than just speed is the fact that it's very easy for people to use. And that is, if nothing else, a huge boon for them, right? I mean, if you can pick something up and relatively easily get started using it and that's what my sequel brings to the table and there there was there was another thing also so this is one thing that i found interesting and frustrating here's the exact one i found i'm sorry to interrupt no good it was it was that uh a varchar column in oracle wait wait is it varchar or varchar? I say car. Varchar. Thank you. Okay. All right, go ahead. I stand corrected.

Varchar. Yes. A column in Oracle was that the empty strings were null. So in SQL Server, for example, you could have a column, varchar column, that you could insert an empty string in, and it was an empty string. It was different than null. Whereas in Oracle, that same query you would write, it's treated. It is null in Oracle.

So when you retrieve the data back, you're getting back null. Interesting. So there's little differences like that that can bite you. Yeah, they will be. and they'll be frustrating so again going with my sequel though fast easy uh it's not perfect like you can't one of the things that i found frustrating when i first started trying to mess around with my sequel was you cannot create defaults of non-deterministic values so like in sql server you can do a get date or you can do some sort of formula you can call a function or something you can't do it in my sql they have one situation where you can because they changed it i think where you can call the now function but they

they definitely they have some limitations but people find ways to work around them there was another one for getting uh row counts and stuff there's just a lot of oddities now moving over to post grace sequel the the drawback to it has always been it doesn't perform quite as well as my sequel however it is more of a standard it's almost like a baby version of oracle apparently is what it boils down to and so they have things like real store products, functions. You can have defaults that are non-deterministic. You can do a lot of things, and their sub-query performance is good. And it also has a more grown-up tool set

as far as being able to do standard database-type stuff, things that you would expect for an enterprise enterprise level db to handle so again not knocking on either one of them those are just a couple of things those are some things to keep in mind and apparently its performance is calling php my admin not a mature tool now i will say that feels wrong i will say if you guys have messed with my sql workbench it's actually a very nice tool when did that happen have you not done it no MySQL Workbench, it's actually a very nice tool. When did that happen? Have you not done it?

No, I have. I mean, it's been years, but I always bought Navicat, which is a nice pay-for program, but I couldn't stand Workbench, and I couldn't stand doing it through console either, which I've done. Oh, yeah. Console can be a little bit frustrating. Show tables. MySQL Workbench is actually pretty decent, so if you get a chance to check that out, you should. But all of us here, we're pretty much SQLl server guys simply because in the jobs we've worked being a dot net

shop it almost always goes hand in hand with sql server right yeah i was surprised when you said there was a there was a choice there because to me it's like you know when you're talking about sql server or oracle unless there's a hard reason you know that you need one or the other that your dva you know insist on one the other then uh enterprise manager is so much nicer than whatever oracle's tool is called i've blown it out of my mind i don't want to think about it anymore but the enterprise manager is so nice it is good yeah i honestly can't stand behind that statement that you said though what which one well when you're talking about like you know us being pretty much sequel server guys like no way i i've i have probably spent an equal amount of time in my career on each of the big three.

Oh, really? Between Oracle, SQL Server, and DB2. Interesting. I've been locked into SQL Server. In various capacities. DB what? Yeah. DB also.

Me too, guys. Me too. Yeah. I mean, it's – that's been from uh but that wasn't working on dot net though right no that is it is i've done i've done i've done plenty of um you know well even going back even prior to dot net um but even you know specific and specifically to dot net i've done plenty of development in db2 Oracle as well as SQL Server. And that's why I say it's really depending on there's so much more.

But a big part of it was the client had the money and they thought, you know what? I already have the engineering behind Oracle and I know what Oracle can do for me and I really like Oracle. So we're going to go Oracle. Or there've been other times where it's like, well, we don't really have a preference, but there's this other tool set over here that already has some nice integration into DB2. So let's just use DB2 since we already have it. And they'll piggyback on that license for some, you know, so yeah i i've definitely spent a mix of my career across the three cool yeah i've mostly been in sql server you too yep same yeah so my sql it's it seems that like just from a perspective of the big boys it seems like sql server is the easiest to set up um just out of the gate and from what i understand and i i'm not that well versed with

oracle but apparently it's very much a la carte like every little piece that you want added in addition you're paying a decent amount of salaries for so whereas sql server you kind of buy the addition you want and you get all the functionality out of it and and here's the thing you can set up sql server like you can basically run the install and you're up and running. If you wanted to run a finely tuned machine like a Porsche or a Ferrari or something, you really need people who understand how to set things up properly, like partitioning your disks out and all that kind of stuff. That's going to be true of any database.

And part of that, even before you start with the database, starts at the operating system especially if you're going to be in like any kind of unix uh you know variant yeah so it's again all these bring something to the table but we just wanted to give you some ideas of how things are chosen um sometimes it boils down to the resources they have in house like i mentioned about the windows developers earlier right like if you have a bunch of Oracle DBAs, you're not installing SQL Server. You're putting Oracle on your system. So the big boys, they're all fairly comparable nowadays.

You know, it just depends on how deep of pockets the companies are that are willing to use that stuff. I just thought it was pretty funny. Like if you talk to like a typical Java developer and ask them about databases, they'll typically say something like, Oracle, people actually use SQL Server? I thought that was pretty funny. If you talk to a typical Java developer and ask them about databases, they'll typically say something like, Oracle, people actually use SQL Server? I thought that was a joke. And you go talk to a.NET developer, and it's the total opposite.

Like, Oracle, are you joking? Right. And then you meet me, and you're like, wait, what? Yeah, you're just messing with me. No, and then you have the other people in the NoSQL camp, right, which is document storage stuff so that's a whole nother ball of wax which we're not going to get in today but be aware there's a bunch of no sql databases out there now yeah i feel like if you know if you're

looking at the free open source route then your your decision is really my sql or postgres and you're probably going to go my sql even though everyone on the internet talks like postgres is really you know really nice but everyone ends up using mySQL. There's a ton of resources for MySQL. Oh, yeah. I mean, I know personally I've done, you know, work just in MySQL. I didn't bother. And for me, it just came down to, you know, at those times, there was just a ton of information available to work around MySQL. Yeah. Every single WordPress site by default runs with MySQL. So every question that can be asked has been asked of it. Yeah, actually, that's one of the reasons

why I've used MySQL is because not just WordPress, but a ton of open source projects go straight for MySQL. They don't even look at anything else. So it's like, okay, if I want to work with this stuff, then I'm just going to go this route

and make it easy on myself. So, you know, definitely some things to keep in mind. Anyone done any.NET for MySQL? I have not. Nope. We're zero for three. Yeah.

I've thought about it, but I haven't. Yeah, I don't know what the license is for Postgres, but I know MySQL. It's MIT. MIT. Is it really? So you can distribute it. You can do whatever you want. That's enough reason to use it right there.

MySQL is not. I think it's GPL. So if you want to distribute that sucker, like you want to embed it inside your application that you install on other people's computers, forget about it. You're going to be paying some bucks. Oh, and by the way, if you have no idea what we're talking about

with the GPL, the MIT, what session was that, guys? Five. Man, trick question, really? Yeah, yeah. We're doing that? So we do have a- We did an episode on licensing.

Yeah, and- We still don't understand open source licensing was the name of it. I don't know which one it was. Somebody's going to look it up while I randomly spout crap. There we go. Did you get it? I'll keep spouting. Oh, I'm still spouting crap then.

It's episode five. Episode five, yeah. We still don't understand open source licensing. So if you want to check that out, www.codingblogs.net slash episode five. But that is a huge deal. And that's what real-time note-taking looks like. That's right.

And that's huge, though. So seriously, the MIT thing means that basically you can do whatever you want with it. You don't have to do – you do it. It's yours. So that's beautiful. Just got to give credit. Yep, got to give credit where credit's due. All right, so now I want to get into something before we actually start hitting on the meat of some of this conversation was

one of the things when I was writing my blog article about the database schemas, I started looking at doing hierarchies. And so if you know what a hierarchy is in a database table, usually you have, let's just talk about a simple employee table. You have an employee that has an employee ID, and then you have a manager ID, all right? And then essentially all that manager ID field is is a link to another employee ID.

So literally you can have this parent-child relationship. Or a foreign key, if we're going to be. It's a foreign key, yeah. More grammatical. In the case of that, literally, your primary key is employee ID. Your foreign key is the manager ID column that points back to the employee ID. So you have a parent-child relationship. SQL Server makes that so easy to query.

You can do a hierarchical common table expression to where literally you can say, all right, spit me out everything where this manager, all the employees under this manager at any level. It's so easy. Maybe we need to back up, though. Maybe we need to define CTEs before we get too far. Common table expression. Well, I'm beyond just the acronym blown out. So go ahead.

You'll know it when you see it. Okay, you'll know it when you see it. No, it's like an in-memory query result. That's the short answer to it. You could do a query, stuff those results into memory, and then later you can write a query to uh scan that result that previous result and then it gets thrown away it's no longer in scope after it's used the first time it disappears and you can at least in sql server they have this um they have this idea of of recursive ctes to where it can literally just you you know, do exactly what I was talking about, build

the relationship. So getting into that. Yeah, but it looks so weird when you see a recursive. Because at first you're like, wait a minute, I'm writing the CTE. So I have like my as blah, blah, blah statement. And then it's selecting from itself, but yet it's still not finished. Right. So how does it know?

It's so beautiful. You'll dream about it if you ever see one. It's just, it's like, finished right so how does it know it's so beautiful you'll dream about it if you ever see it just it just it's like it this is one of those scenarios where it's just it just works but you kind of question it as you write it it looks weird to me but i think alan's really grokked it like he just he kind of lives and breathes ctes i really do like ctes so he has an unnatural obsession with sequel in general though yeah i might it's kind of like javascript i'm the lone man here but so here was the thing that came up when i was designing something the other day in my sql that really frustrated me so when you code something wait in a mysql database yeah mysql database oh so. So I was messing with that because I wanted to get my head wrapped around all this stuff, right?

Okay, I'm with you. And here was my frustration. So SQL Server makes it to where you can literally just set up your table, employee and manager, right? You have those two columns in there, and if you want to query them, bam, you got your CTE. You're done. MySQL, not so. You want to pull that out now. You've basically got to write yourself some sort of stored proc or some sort of method that is going to loop all your results. So you're going to say, all right, select everything where the manager ID is one. All right, that gives me my set of results.

All right, now loop over those results and give me everything where those IDs match up. So literally, you just keep looping and looping and looping until you get your entire set. So let's build out an example, right? If you were trying to do a query to pull in the full management chain, so starting at the top of the management chain, you have the CEO, and you want where he's the manager ID, but then the query will recursively find all the managers that report to him, then all the managers that report to him,

and all the managers that report to them, et cetera, et cetera, et cetera. That's what you're trying to describe. Yep, so you could go five, six, seven levels steep, right? So here's my problem with that. SQL Server, I can do it like basically what would be two queries right i feel like we're on a game show so i can write that query in one query boom but if you go into my sequel like literally you're not writing queries you're writing looping logic to say all right give me all these guys all right now give me these guys as guys now give me these guys

as guys and so literally you're looping until you've filled up maybe a temp table or something that you then return out at the end. So here was my question. When you go to program something like an application for MySQL, do you program to its deficiencies? So in the case that you've got... Do you code around the fact that you can't do a hierarchical query oh i totally would yeah well once you start talking about doing loops and sequel like uh-uh no cursors

yeah i mean that's a cursor i got link you so so i guess that's my thing is is at that point then so let's think about it from let's think about it from a category of products on a website. Everybody's used to looking at that stuff, right? So you go on Newegg, and I think that's probably pretty good for this show because if you haven't heard of Newegg, you should go there immediately. Pause this and go look. Well, don't pause. Oh, yeah, listen while you're going. You're fine.

So you go over there and you go to the computers category, right? And then under computers, you're going to have laptops and desktops and servers. Then under laptops, you're going to have ultrabooks and whatever. You've got all these categories. Whatever's put at that bottom most category belongs to that top one. If you have a laptop down here, you have a MacBook Pro down at the very bottom under awesome laptops, it's going to show up under your computers as well so here was my thing if i'm programming a database that cannot do this kind of stuff do i insert a

a relationship record for that macbook pro to the computer category and the laptop category and the awesome laptop category? Or do I just put it at the awesome laptop category and then write my loop to go get all the data? You see what I'm saying? Yeah, I mean, you're talking about like trying to like, really your question should be, do you make your data ugly because of deficiencies in the language? The SQL language functionality. The database manager that you're using or do you code around it?

Or do you let the ORM do it? But the ORM wouldn't buy anything. You'd still have to loop in the ORM, right? Well, I guess for a CTE, yeah. So I guess that's my question. How would you go about doing that? As a person who's having to deal with this database,

we know that some handle it gracefully and others, you've got to create these crazy hacks. What would you go about doing that? As a person who's having to deal with this database, we know that some handle it gracefully, and others, you've got to create these crazy hacks. What would you do? Would you insert more data to make your queries easier to write? And faster, by the way, because if you're looping, you're slowing things down, and you're actually eating up a lot of read-writes from the database. Or do you just write a bunch more records so you're filling up disk

space but now now you got a maintenance problem as well because if you ever decide to take that thing out of that top level i definitely don't like the additional data that that sounds gross no like that where you query every every row that's like yeah it's the n plus one problem that's no good but uh you know i the thing with ctess for me is I end up needing like two a year, and I just ask Alan for help. It's not really a big problem for me. I don't know. It's always one of those things. You know, as a programmer, even if it's not SQL, you run into this deficiency and this gaping hole in whatever you're programming in.

And you look at it and you go, really? I've got to do this, and I know it's not going to perform well. And I know it's going to be a resource hog because now I got to loop a hundred thousand times to get this stuff. Right. It's like something where like maybe you'd use a view. How do you do a view that's that links itself to itself, to itself,

join, join, join, join, join. And that's what you typically see. That's actually what you see. A lot of times is people will say,

you know what? Screw this. It'll never be more than 10 levels deep. And then they'll join itself see. Take a max depth. That's actually what you see a lot of times is people will say, you know what, screw this. It'll never be more than 10 levels deep. And then they'll join itself, you know, 10 times. I'm sorry. I mentioned the view then. I'll take that back. I've done it before, no doubt. Just some food for thought.

Like, I don't really know the right answer to this. I know that I don't really like any of the solutions. Well, it makes me question, like, could the data be structured differently, though, that you wouldn't need to do that? Yes, it can. Recursive query. Yes. Because your deficiency here is only because of the way you structure the data. But if the data had been structured differently, then it might not be a problem.

It could be moot. I'll tell you exactly how you can do it because I've done this in the past. Before SQL Server had CTEs, this is how you can do it because I've done this in the past before SQL Server had CTEs this is how you do it so that is a true normalized format that we were just talking about where you have the the child and the parent record in the same row right that or in the same table that's truly normalized that's good a way you can do it and Joe Selco as far as I know came up with this essentially what you do is you have a left and a right on every record. So let's, let's go back to the employee state, right? So you have a manager who's at the top. Let's say his left value is one and his right value is a thousand.

All right. He is the owner. He is the boss of everybody in the company. So now let's say that he has a manager under him that has a few employees under him. So his left is going to be two because it's inside now the outer guys. So the top guy has a one and a thousand. The second guy is a two and we'll say a hundred because let's say that he has like 50 people under him, right? Now he's got a dude under him that doesn't have any reports he's going to be three and four all right so his left

is three his right is four there's nobody that can fall in between his numbers all right so then let's say that the person next to him also doesn't have any reports his numbers are going to be five for his left and six for his right all right so they fall in between now you get inserts are going to be hell they are and that's where the problem comes but this is how you can query it easily so now let's say that the person next to him right so we had five and six i don't want to think of any more people let's just go to the query all right so the query for this is actually really easy you want to see everybody in the company give me give me the dude at the top select everything from this table where left is in between 1 and 1,000.

You now have everybody under him. Now, show me everybody in the company that is under him who is a direct report. Select everything from the table where left is left is between one and a thousand and right equals left plus one so literally now you have basically you've flattened out that structure but the problem is is anytime you have an insert that seems like a nightmare yeah you gotta just i mean like maybe in a contrived example like you're given here but like if we're talking about new egg as the as the example because that's that's where this spawn, right? It was like we brought up new egg as a product catalog.

Just so you could have the category stuff. Right. But OK. But I mean, like in the OK, but going back to the blog article that we mentioned that you wrote earlier, that was specifically around like product catalog type, you know, product to attribute type situation. But I didn't get into hierarchies there yet. Right, but that's where I was thinking about that. And if you had a catalog the size of a Newegg, that scheme that you are describing would be a nightmare, as Joe pointed out. In fairness, typically when that has been done, it's not something that's alive.

You generally speaking generate that table. Yeah, I was has been done, it's not something that's alive. You generally speaking, generate that table. Yeah, I was just thinking like we're talking about Newegg and stuff. I would throw that stuff into something like solar or Elastisearch. Yeah, you wouldn't really use this for that. But when you go to that method in databases that have those deficiencies, the problem where they can't query hierarchically, typically what you do is you have something. If this is data that doesn't change a lot,

if it's data that changes a lot, you have problems. Because then you have to come up with ways to grow the tree underneath it, and that means growing all the way up. So typically what you do is this is a table to where the data isn't all that transactional, and so you generate that table that has your left and your rights. It makes querying incredibly fast. Well, I could see it as like a one-time kind of thing.

Yeah, yeah. So it's something to where like maybe you have nightly builds of a table that you push out. But now you had to write a query to generate that. Yeah, yeah. No, then you write your recursive loop one time that generates that table that you can query so easy that it's so that's one way that you can go about it and so for anybody who didn't follow along here i will try and find a link to

that to what that structure looks like um but it's it's a way to get around a deficiency and that's that's kind of what i'm saying is it seems like there are times where you have to hack things just to be able to work with a particular database system yeah database is just um relational databases just aren't that good at storing trees no but you know for something specific to like a an e-commerce situation there are search engines which um store things a little bit differently that just work perfectly for that use case solar being one which was mentioned solr and it's uh it's outstanding heck all the big boys use them yeah i think it's called like reverse index databases or something where it basically kind of turns the concept of,

it really abuses the concept of indexes and just really stores the data in a range in a bunch of different ways that makes searching much quicker. Yep. Let's get into some resources we like. Let's change the, bring it down a notch. Yeah, here we go.

Ease your brains up a little bit. The one that I like is Mastering C Sharp. It's a book that I downloaded through Kindle. I don't remember how much it was, but it is a fantastic C Sharp reference. It goes into things like the CLR. It goes all into interfaces, hiding, things that we've talked about before.

I mean, if you really want to become an expert at C Sharp, this book is a very handy guide. And it reads well. Like, I find that I just read through it as opposed to skip around like I do in a lot of reference books. It reads very well. So it walks you through examples and all that. So Mastering C Sharp, we'll have a link on the show notes for this. And that's mine. You got an author for that? it reads very well so it walks you through examples and all that so mastering c sharp

will have a link on the show notes for this and that's my you got an author for that oh god somewhere oh sorry i think i do go ahead with you so i'll just be about random garbage then there we go okay so so while he's looking up that i'm going to go into a resource that i stumbled across that i i like so there are times when you're out there in the wild and you just need to know, hey man, what IP does the external world see me as right now, right? What's my IP? Now, there's some websites out there and even Google has built this into where you could just search what's my IP and Google will tell you. But that's not the fun way of doing it though. Sometimes you want like a little humor in your day right so i'm gonna spell this one out follow along with me it's a wtf is my ip.com wtf is my ip.com yes that's right wtf is my ip.com. That's right. WTF is my IP.com.

And there is some true gold on that. So how can I say this? So it's going to tell you what your IP address is. But just pretend, like read it as if an angry person from Brooklyn was telling you what your IP was. And you know what? Maybe you'd like to know what headers your browser is using. Or maybe you have a need to get this in the form of XML or JSON. Well, guess what?

You can. And if you look at the XML representation or the JSON representation, again, i want you to picture that angry brooklyn person telling you these values it's lewis black's ip address it is hilarious you know just just it's it's those little nuggets of humor through the day that get us by right this is one of those resources yeah and now that you've gone to that website while you're listening to the podcast we should probably tell you that it's not safe for work yeah yeah don't do that well that's actually where i was gonna go to next because then imagine that your boss asks you and you just send him this url and then he's gonna be like hey wait a minute you're like no seriously try it

that's like instant promotion right there because then he's gonna get some humor out of it all right so now that the fun stuff's out of the way let me back up i was completely wrong it was not mastering c-sharp actually i think i've seen that book and i didn't like it this is c-sharp five unleashed let me be a little creative with the edits here let me be politically correct wtfismybook.com it does have a stupid cover

oh man I looked it up on Amazon. I was like, hmm, Alan. No, no, it's not that one. All right, so hold on. Going back here. What was the name of that again? All right, so the book that I was talking about is actually C Sharp 5 Unleashed,

and it is by Bart De Smet is the author, and it's a fantastic book. Like I said, it reads well well all that kind of stuff so uh that would be my resource and your thoughts on mastering dude i've actually i've looked at that book it was terrible all right so i really did just look at it i like, I'm not putting that in the show notes. Dude. Oh. And I think I did that twice.

That was awesome. All right, so now let's move on to the tips of the week. What we got, Jay-Z? Yep, we've been talking about SQL, so I'd like to mention a free tool put out by Redgate called SQL Search. And if you're familiar with Redgate, it's typically very expensive and awesome products but their tools are fantastic yeah they really are i love

sequel search it's basically a plug-in for um for sequel server management studio and it allows you to search sequel really quickly so you can do stuff like uh you know look up a column and you're like oh man what was first name in again or where address? You just kind of type in a couple letters, hit enter, and it finds it really quickly. It looks at all your columns, and it's just really fast and a really great way to work. Well, their tool set, though, is just extreme. I mean, they had one, like, it would do, like, schema diffs, if I recall. Yeah, you could check in your database. The one absolute that was my favorite, and it's the most basic of features,

I really don't understand why Microsoft hasn't included this in SQL Server Management Studio, is the SQL formatter. Oh, no. Oh, my God. That was my favorite part of having the Redgate tool suite. I would agree. I have a webpage I go to, and I paste it in. Oh, there's a better one one if you're worried about that.

Notepad++. Yeah, he turned me on to that. The poor man's T-SQL formatter. It's beautiful. I mean, and you don't have to worry about somebody actually stealing your queries. So those are all fine and dandy, but the downside is that you're copying it out of one tool to put it into another tool, format it, and then bring it back. Right? And that's why, like I said, Redgate has some fantastic tools, but my absolute favorite one is probably the one that took them the least amount of time to build, and that's the SQL Formatter.

Yeah, I would agree with that. All right, so my pick of the week is the navigate to feature in visual studio. And it's one of those features that doesn't exist in any menus anywhere. So if you don't know about it, you just don't know about it. If you didn't know to look for it, you don't know about it. So the, the use cases, let's say that you named a method and you can't remember exactly what you named it, but you're pretty sure that you use the word pricing and you might have used the word calculator in it somewhere but you don't know if you called it calculate price or or price calculator or what it was what you can do is if you hit control and the comma key inside visual studio you can type in one word and then space

another word and it will actually do a fuzzy search through your files for you. So it will look for those two words anywhere in that file. So instead of having to write some nasty regex in a file search, you do this and you can quickly come up with your pages that have your methods in there that you thought you made. So that's, that's a fantastic little feature that they just don't document. Nice. All right. And, uh, so here's one that, that, well, it was new to me. All right. So in, uh, one of the meetups, I was at a Windows phone meetup earlier this week, and they referenced this tool that, well, first I want to tell you what it is before I get into the name of it. What it does is

it's a way to create HTML5 and JavaScript 2D games. But then once you have, you could create the game in this tool, which is called Construct 2, and we'll have links to it in the show notes. But if you wanted to export that out to one of many different app stores, obviously the Windows app store was one of them that they demoed. But there was also, you know, other stores in in there like amazon's app store was included you know as well as all the big ones right you know those are by default but you could create this you could create your your game in this tool and then export that out and let's say you wanted

to go into windows app store with it? What it would do that I thought was really cool was it would create a Visual Studio solution for you that it didn't even prompt you like, hey, which part of the Windows App Store do you want to target? Are you targeting desktop? Or are you targeting mobile, whether it be phone or tablet, it didn't even prompt you for that. It just says, oh, you want to go to Windows App Store? It builds out the solution, the Visual Studio solution for you that includes both the Windows desktop application as well as the mobile application, whether it be phone or tablet. And it was just a really cool little tool. I want to play around with this some more. It's not too terribly expensive.

It is priced in pounds, so you can compare that for yourself. But it was a very cool little tool. And even in some of the examples that they were showing in the meetup this week, they did an Angry Birds clone within minutes with it, just as like some of the default baked in functionality that it had. If 2D gaming is something that you wanted to be able to do and you wanted to be able to

have that app on multiple platforms, then Construct 2 is an application you might want to look into. It's really cool. So, with that, we'll be putting the links in the show notes as well as there'll be a lot of other information in there, a lot of good information in the show notes that you can go back to.

Be sure to subscribe to us on iTunes, Stitcher, and more using your favorite podcasting app. And as we've mentioned, please, we really appreciate the reviews on iTunes and Stitcher. That goes a long way to help us find new listeners so that they can find us more easily and provides inspiration as well. Also visit us at CodingBlocks.net where you can find show notes, examples, discussions, and more. And send your feedback,

questions, rants, and comments and Steam friend invites to comments at CodingBlocks.net and make sure you follow us on twitter at codingblocks all right i think that's it guys i am dead tired so i'm gonna play divinity for maybe two hours and then go to bed