Database/table information

This entry is part 2 of 2 in the series SQL tips and tricks

In the last few weeks, I improved/corrected lots of my existing stored procedures for my current project. It feels good knowing that my understanding of inline views has definitely improved. I’ve additionally learned a few SQL queries that provide me with database/table information.

I also faced a few issues while trying to use views in SQL Server. I have a few views in SQL Server that I can’t actually use because as soon as I save them, the content gets automatically formatted and changed. I’d be fine with it if the change didn’t influence the outcome, but it does. When I have several joins and then a WHERE clause, SQL Server always changes it to joins with an AND instead of the WHERE. That’s really annoying since the outcome with the AND is wrong. I hope I can figure out what’s going on soon but till then, I’ll end up saving those queries in a stored procedure directly (where it doesn’t get auto-formatted and changed).

A few things I’ve learned lately:

SQL Servers sys.sql_modules

In my current project, I ended up having quite a lot of stored procedures. I started getting a bit overwhelmed. For example, I knew I had used certain keywords (like TRANSACTION) in some procedures, but how could I quickly find them. Going through each stored procedure is an option, but a very slow, annoying, and frustrating one. Then I found out about the sys.sql_modules table. It has a ‘description’ column which contains SQL scripts for creating each existing view and stored procedure.

I started using following search

SELECT definition FROM sys.sql_modules 
WHERE definition LIKE '%TRANSACTION%'; 

That helped me tremendously in finding certain stored procedures very quickly.

SQL Servers sys.objects

The SQL query above helps in finding the wanted stored procedure/view since each ‘description’ column contains the name of the stored procedure/view within a few words. But I figured it would be nicer if I could just get the stored procedure/view name instead. And that’s where the sys.objects table comes handy.

SELECT DISTINCT o.name FROM sys.sql_modules sm 
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%account%';

Other useful SQL queries to learn more about the database

The more tables/columns/views/stored procedures/functions/etc., the harder it gets to maintain the database and to keep an overview.

I found querying the Information Schema views very useful. For example, if you want to quickly know the columns of a table or view, use following query:

SELECT TABLE_NAME, COLUMN_NAME
FROM my_database.INFORMATION_SCHEMA.COLUMNS;
WHERE TABLE_NAME = 'my_table'

I’ve already mentioned how to search for certain keywords in views/stored procedures via sys.sql_modules. Another way of doing exactly this is via the Information Schema views. The following code snippet shows all CREATE VIEW/PROCEDURE functions for your database:

SELECT TABLE_NAME, VIEW_DEFINITION
FROM my_table.INFORMATION_SCHEMA.VIEWS;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM my_table.INFORMATION_SCHEMA.ROUTINES;

Example usage

During development, I often have more than one database of the same thing. I have the main database which will eventually be deployed and which I use for all kind of tests. Then I have for each person that plays around with my software (for testing purposes or other) an additional copied database.

During development, I often need to correct some views or/and stored procedures which I update on my database and then retest. As soon as I’m happy with the changes, I need to also make my ‘testers’ happy and provide them with the latest version. I wrote a little script extra for that purpose to make updating the other database views/stored procedures so much easier and quicker.

use tester_x_db;
DECLARE @row int = 1;
DECLARE @sp varchar(8000);
DECLARE @maxViews int = 
    (SELECT COUNT(*) FROM my_db.INFORMATION_SCHEMA.VIEWS);
WHILE @row <= @maxViews
BEGIN
    SELECT @sp=REPLACE(VIEW_DEFINITION, 'CREATE', 'ALTER') FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) AS 
            rownumber,VIEW_DEFINITION
	    FROM my_db.INFORMATION_SCHEMA.VIEWS
	) AS tmp
    WHERE rownumber = @row;
    EXEC (@sp);
    SET @row = @row + 1;
END

DECLARE @maxRoutines int = 
    (SELECT COUNT(*) FROM my_db.INFORMATION_SCHEMA.ROUTINES);
SET @row = 1;
WHILE @row <= @maxRoutines
BEGIN
    SELECT @sp=REPLACE(ROUTINE_DEFINITION, 'CREATE', 'ALTER') FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY ROUTINE_NAME ASC) AS 
            rownumber,ROUTINE_DEFINITION
	    FROM my_db.INFORMATION_SCHEMA.ROUTINES
	 ) AS foo
    WHERE rownumber = @row;
    EXEC (@sp);
    SET @row = @row + 1;
END

OpenGL with MFC ‘Hello world’ tutorial

This entry is part 1 of 4 in the series Learning OpenGL

For my first ‘Hello World’ program, I decided to use OpenGL with MFC since I’ve created OpenGL apps with just glew and freeglut before and there are lots of tutorials on the net for just that. I just wanted to know how to integrate OpenGL in an SDI MFC app and it turned out to be quite easy.

I’ve written a few starter OpenGL programs in the past but never really continued studying it for very long. This time, my goal is to actually finish the 3D Computer Graphics Using OpenGL YouTube series by Jamie King.

Jamie’s videos are awesome since he is really good in explaining things. The nice thing about YouTube tutorials comparing to real-life lessons is that you can fast forward when a subject is already known or repeat a section when you didn’t pay attention or just didn’t quite get it.

I’m also using other sources for studying OpenGL. I’m going to use the book ‘OpenGL Programming Guide’ by Dave Shreiner and Graham Sellers as a reference book. Furthermore, I’m going to read through Introduction to Computer Graphics material. Last but not least, I just recently found the ‘OpenGL & Vulkan’ podcast by Kai Niklas. That podcast is perfect to listen to while I play with my son.

Why am I interested in OpenGL? During university and years after, I haven’t really paid much attention to computer graphic design. I mean there was a reason I specialized in embedded systems. After years of microcontroller programming, I started getting a bit bored with it. I still enjoy it, but I just started missing the beauty of object oriented programming languages.

Then I started programming in GDI+ which I actually began to enjoy a lot. Now, I feel like I really want to get deeper into the guts of 2D and 3D graphics, which means OpenGL.

In this series, I’d like to document my OpenGL programming progress and also link to my created programs in github.

I’ve used following sites to guide me create my first MFC OpenGL program:

  • For GLEW and freeglut installation, I followed the ‘Installing freeglut’ and ‘Install GLEW’ guides in ‘Introduction to Computer Graphics’ installation guide.
  • Instead of using the OpenGL helper classes/functions freely accessible from the course site above, I decided to use the GLRenderer class from opengl.org.
  • Now, all I needed to do was call several GLRenderer functions from my SDI view class. I created message handlers for
    • WM_CREATE
    • WM_ERASEBKGND
    • WM_SIZE
    • WM_DESTROY

    and also overloaded OnInitialUpdate().

The code for this first program can be found in github.

For this first program, I wasn’t really concerned about the drawing functionality. My goal was to just have a basic OpenGL with MFC skeleton for future programs.

My impression of Canadian Christmas markets

I’m living for about 7 years in Canada now and I haven’t visited any Christmas markets up until a couple weeks ago. I’m from Austria where we have awesome Advent markets (if you’ve never been in Austria, I’d suggest visiting it anytime between middle of November and end of December – in my opinion the best time to visit – apart from it being a bit cold and you’ll likely search for the sun without successful) and I figured it’s time to visit some markets over here.

Every year around this time, I miss home a lot. Walk through Vienna and you don’t only see the all-year-around beautiful architecture, but also awesome Christmas lights, stands with hot drinks and my absolute favorite, hot chestnuts. This year, I decided to give Canadian Christmas markets a chance. It’s kind of weird, but apparently lots of Canadians don’t ever visit these markets here. My husband has never been at one before and I’ve met many people here that haven’t either.

We’ve visited a smaller Christmas market in a nearby city and a week after a big one with about 200 stands. I was actually positively surprised. I really like that at those markets you see mostly local sellers and you find a lot of nice stuff. Unfortunately, you pay entry for many markets (the ones we went to, but there are also free ones). On the one side, I understand that it costs a lot of money to have those markets inside (since honestly, who wants to be surprised by -20 degrees Celsius or worse outside), but on the other side, it prevents people to visit those markets more than once a year.

The whole family enjoyed the trips (especially to the big one), but it wasn’t quite the same as at home. I asked my husband, why he didn’t enjoy it quite that much and he stated that Austrian Advent markets are very social events. You pretty much always meet friends at those markets, walk a bit through, drink a few hot wines to warm up (not the best if you’re on a diet considering the amount of sugar in them, but they are just delicious), maybe have hot chestnuts as a snack and chat for a few hours.

I remember, I used to spend frequently time at the market in front of the university with a few colleagues. We drank one, two hot wines and then we walked to the math tutorial. It didn’t make it more fun, but at least a bit more bearable (I do enjoy math, but those tutorials were definitely hardcore).

Nevertheless, I’m planning to visit more markets over here in the coming years. They aren’t what I’m used to but some of them are definitely worth visiting.

How I manage to be a productive mother

I got my little boy about 14 months ago and only was about 2 months on maternity leave due to financial reasons. My friends that have little kids themselves always asked me how I managed being a mom of a newborn and working full time from at home. My answer always was that if you really want something, you can do it. And begin a productive mother was important for me.

I was fortunate enough to have a boss that let me work from at home for the first year. Also, I haven’t used up any holidays that year so I could take over 2 weeks as soon as my 2 months maternity leave were over. During those 2 and a half months, I found a routine with my little sweetheart that worked for us and which turned out to work perfectly for later on as well. W was a bad sleeper and used to only sleep during the day if I carried him while walking. Therefore, I started putting him frequently in my carrier, stepped onto the treadmill and walked. I then started placing my laptop on the treadmill and worked for as long as W slept. That way, I got about 4 hours of work done during the day.

The rest of the work hours I did in the evenings when W slept. The evenings used to be the hardest for the first 9 months or so. W tended to wake up a lot and cry. Usually, if he laid down at 8 pm and I stayed up till 2 am, I got about 3 1/2 hours of work done (on good days). The rest of the time I laid next to him and calmed/caressed him. Did I have weekends off? No, the only way to get my work hours done was by also working Saturdays and Sundays. Was it a fun time? You will have problems believing that but for the most part, yes. W made me and my husband laugh so much every day (and he still does), so that the hard times weren’t quite as bad. Furthermore, I love working and I enjoyed being able to still do some programming every day.

Did I get enough sleep? Nope, but which mother with a newborn does? And I’m lucky that I can fall asleep within minutes. I’d stop working at around 2 am and fall asleep 1-2 minutes later. I’d say in average I still got about 5 hours of sleep daily. Don’t take me wrong, I used to sleep 9 hours and more, but I found out that I still function properly with about 5 hours. Anything below for more than a few days and I’m a wreck.

One thing I’ve learned is that it’s important to not stress yourself with work, especially when you have to look after a newborn which can be quite exhausting at times. You still want to enjoy the time with your baby. On days, where I needed a break, I took it (just took a spontaneous holiday from work). Therefore, even if you have a big todo list (work or home related), don’t force yourself to fulfill it up to exhaustion. There are days/weeks that are just really hard (baby hardly sleeps, is maybe sick, cries a lot for no obvious reason, etc.) and the more you feel stressed, the less patience you have with your loved ones (and patience is extremely important with little as well as with older kids). If possible, ask your partner for help. Maybe you have relatives/friends that can help you out here and there.

Another thing that comes to my mind that helped me was to be flexible. I now have my office in the bedroom and work next to my sleeping baby at night. That way, I can react right away when he needs me and I get some work done. I also put my baby in the carrier and wipe the kitchen floor and do other chores around the house during the day. My little one enjoys it since he is close to me and gets to watch close up what I’m doing. Don’t set yourself timeframes. You might be like me a morning person, but if your little one wakes up early and wants to be held and played with for hours, then don’t stress yourself with an unrealistic morning todo list. Having a detailed day schedule might be very helpful when you don’t have to look after kids, but with kids, the best schedule is a very flexible one.

I can honestly say that I’ve never been as productive as now. You wonder why? I used to waste a lot of time in front of the TV. After a hard work day, I would flop onto the couch and stare for hours at the big screen. As you might guess, I haven’t seen a single movie/episode for, hmm, I don’t know how long. I just don’t have time for it and honestly, I don’t even miss it. Of course, I still need some relax time here and there. In the evenings, before and after work, I read the news, eat a snack or just simply watch my little one sleeping (while trying to stay awake myself). It’s important to have some off time but the danger with TV is that the off time tends to extend to hours at a time (at least for me).

You don’t get to read magazines/books anymore? Listen to audiobooks and podcasts instead. You can do that while playing on the floor with your sweetheart.

I’m not saying that it is easy to be super productive while watching your baby, but if you really want to, you will find ways without sacrificing time with your newborn nor your own sanity. Consider what’s important for you and what makes you happy. Don’t stop everything you’ve enjoyed doing now that you have a baby. Be creative! But don’t have unrealistic expectations either. Your life will change (there is no doubt) and you hopefully will love the change with your newborn. In my case, I’m as busy as I’ve never been in my life and I love it. I love being able to spend so much time with my son, but I also enjoy getting back working/reading when he is sleeping.

Jobs I surprisingly enjoyed

A while ago, I got asked what my worst job ever was. Most people then think of jobs they did while they were young to make some extra money.

I’ve worked for 4 months at McDonalds back in Vienna during Senior High. But as a matter of fact, that was not my worst job. Actually, I think it was the funniest job I’ve ever had. I had a bunch of really friendly and funny work colleagues and I actually enjoyed working there a lot. We joked a lot while serving burgers and fries to customers. I trained in making the fastest fries ever (though my technique was against the books and not well looked upon) and tried to make my customers smile (which can be challenging when you have to deal with mostly serious Austrians). I wasn’t really fond of the wardrobe, but at least I didn’t worry about spilling anything on it (nothing could make the cloth look worse anyhow).

The funny part of me having that job was that I hate fast food. I don’t eat burgers (though I don’t mind fries – I mean seriously, who does?). I also wasn’t fond of the salads there (though the advantage of working there is that you make your salad yourself, which means you don’t find crappy leaves on the bottom of the bowl while eating).

Surprisingly, I’ve also learned quite a bit while working at the fast food chain. For example, if you smile for no reason at a complete stranger, he most likely will smile back and feel shortly better (kind of weird how a smile can change your mood). Smiling at your customers not only causes them to give the favor back to you but also makes work so much more enjoyable. I also was surprised to meet so many very intelligent people at the restaurant. A bunch of them came from poor countries with university degrees.

Next to McDonalds, I’ve also been employed at a coffee shop for 9 months (nope, not Starbucks) while working on my thesis. Again, it was definitely not my worst job. My boss and one of my colleagues were awesome and we had lots of fun. We also had a bit of a competition running: Who could find out and remember the most amount of customer names. I sucked though since I don’t have the best memory when it comes to names. Nevertheless, I think people started enjoying coming for a coffee since they would encounter a great team behind the counter that made fun of each other. Seeing some friendly faces during your work break can make a huge difference.

What I’ve learned from these jobs is that a good working environment can make a basically boring job fun and enjoyable. Of course, I prefer being challenged with programming tasks instead of having to serve burgers and coffee to people. Nevertheless, a good working climate is essential for having happy employees.