Adventures in POSTGRES: Lowercase Everything

So I’ve been trying to transition from using MSSQL to POSTGRES, and I’ve encountered a few bumps here and there.

One of the tutorials I was following recommended using the Chinook database (a handy test database which has set ups for multiple db providers) as a sandbox. The database uses the naming convention I’m familiar with which is UpperCamelCase for some db objects like tables and its columns. For other things like stored procedures or functions, it’s in LowerCamelCase. Imagine my shock when I ran a simple query to the table “Albums” in the default schema “public”.

select title from album

I got served a

relation “album” does not exist

It turns out that if objects like table names are in UpperCamelCase, then you have to wrap them in quotes before POSTGRES understands what you’re actually trying to do, like so

select "Title" from "Album"

So if you want to avoid all the pain and frustration of having to type in those extra double quotes every single time, keep your objects in lowercase.

create table artist(
id serial primary key,
title varchar(160) not null
);

The POSTGRES docs give good guidelines on this. I guess the convention is good in that it’s easier to type things everywhere, but I can just imagine how painful it would be to attempt to migrate an MSSQL database to POSTGRES later on.

As a bonus, not only did I learn about the casing problem, but I also learned that relations in “relational databases” had nothing to do with the lines drawn between tables we call “relationships”.

Attempts at NoSQL

In my frantic search for the holy grail of data storage I’ve now and again been tempted to dabble in the mystic arts of NoSQL. But this is a classic case of falsely believing that there’s a silver bullet to database development. And this hasn’t happened only once. I’ve actually tried building a prototype (read: did what some tutorials told me to) from MongoDb about 3 times now. I keep forgetting why I stopped last time and I kept hitting that same wall so I figured that I’d put it in writing this time.

Documents

Talking specifically about document-databases like MongoDb, the idea that I didn’t need tables seemed novel. Why do I even need to save to multiple tables? If I saved the whole object as one document in the database, I wouldn’t need to worry about speed of writes. If I needed the object I saved back to my application, I wouldn’t need to rebuild it from multiple joins. Nor would I have to worry about things like the object relational impedance mismatch.

But now that I think about it, if all I’m worried about is that I’m not saving my data as documents, then why not just save the whole document in a column on a table? That way I can deserialize that document back to an object and solve my object-relational impedance mismatch. Fortunately, modern relational databases seem to be taking this approach. POSTGRES has extensive support for JSON types with its JSON and JSONB data types. The other relational databases seem to be catching up as well as companies like Microsoft add better support for JSON strings if not JSON types to the latest version of their software. Implementing it like this allows developers to have the best of both worlds: a document-oriented approach when you need it, while keeping all the features existing relational databases provide.

And speaking of those features, there’s a critical one that NoSQL solutions can’t seem to provide.

Transactions

At every attempt I’ve made to try to prototype Mongodb (not bashing on it, just so happened that I haven’t had time to try the others yet), it fails when I want to store multiple related documents at the same time. I have to rely on eventual consistency, and that’s where the problem comes up. There are cases where eventual consistency just won’t do. Personally, I’ve even had trouble implementing eventual consistency where it really was the only option because of the complexity it adds to an application.

Simply put these document databases aren’t ACID compliant, because maybe they’re not meant to be in the first place. But there are a lot of cases in building an application where you want to be ACID compliant. When implementing DDD for instance, I may need to update multiple aggregates in the same transaction, or save domain events in one fell swoop. Failing to save one, and allowing to save another would destroy the integrity of my data. Having the option to switch between a relational approach and a document-oriented approach as is the case with dbs like POSTGRES gives the developer freedom to apply the best tool suited for the task.

Hybrid Approaches

If you feel the need to dive into NoSQL because it’s the next big thing and you think it’ll magically solve all the difficulties you’ve had with your relational database, you may want to hold onto doing that just yet. The time you’ll spend in learning new tech may be just as well spent in improving your understanding and skills in the current ones you know. That’s where I found myself. If you’re interested in giving this a shot here a few resources which might be of help:

About Me

Hi, I’m Jonn. I’m a professing Christian (Reformed Baptist), a Software Engineer by trade, and an avid gamer from the Philippines. This blog is more of an exercise in writing down my thoughts in the hopes that it will help me form a more concrete and consistent understanding of how the world works. It’s currently a mix of my stream of thought, and the occasional programming post.

Creative Commons License
Images, Videos, and other content in my blog are licensed under Creative Commons Attribution 4.0 International License unless otherwise stated.

Additional disclaimer

This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.

Any views or opinions are not intended to malign any religion, ethnic group, club, organization, company, or individual.

All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site.

The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

Downloadable Files and Images

Any downloadable file, including but not limited to pdfs, docs, jpegs, pngs, is provided at the user’s own risk. The owner will not be liable for any losses, injuries, or damages resulting from a corrupted or damaged file.

Comments

Comments are welcome. However, the blog owner reserves the right to edit or delete any comments submitted to this blog without notice due to :

– Comments deemed to be spam or questionable spam.
– Comments including profanity.
– Comments containing language or concepts that could be deemed offensive.
– Comments containing hate speech, credible threats, or direct attacks on an individual or group.

The blog owner is not responsible for the content in comments.

This blog disclaimer is subject to change at anytime.

Disclaimer helpfully provided by http://sampleblogdisclaimer.blogspot.com

Book Review: Dreaming in Code

Dreaming in Code: Two Dozen Programmers, Three Years, 4,732 Bugs, and One Quest for Transcendent SoftwareDreaming in Code: Two Dozen Programmers, Three Years, 4,732 Bugs, and One Quest for Transcendent Software by Scott Rosenberg
My rating: 5 of 5 stars

“Nobody should start to undertake a large project. You start with a small trivial project, and you should never expect it to get large… If it doesn’t solve some fairly immediate need, it’s almost certainly overdesigned.” – Linus Torvalds, Linux Times, 2004

Life is complex. Trying to model even one real-life process into an ethereal tool will no doubt embody part of life’s complexity. But this isn’t immediately obvious to the optimistic programmer. A grand vision of building the-next-big-thing is enough to fuel one’s optimism and blind them to the fact that the-next-big-thing is like really, really big. This isn’t uncommon in the Software Industry where building things (small things) seem to not take as much time as other industries. Open an editor, write a few lines of code and you’ve got something on your screen unlike say a bridge where the first may take a few weeks or months after someone draws up the first draft (I have limited knowledge on bridge building so don’t quote me on this comparison). And when a programmer finds out that building software isn’t as easy as they thought it would be, they can’t help but wonder if there’s something they’re missing. And then they get a book like this and try to read about how other people are doing.

Your average pretentious software engineer might read the title of the book and think that “These must be some enterprise software engineers unlucky to be in one of those process heavy companies where they have to do some archaic process like waterfall or RUP. If only they’d done agile they wouldn’t be in this mess.” Surprisingly though, your average pretentious software engineer would be proven wrong as they find themselves in the shoes of the Chandler team. The team involved a lot of the brightest minds working on an open-source project with a huge budget that’s just enough to keep them going for more than 5 years. Depressingly enough they did go for more than 5 years. I’m very grateful to Mitch Kapor for giving the world a window into one of his most challenging projects. His openness and willingness to have this documented and shared just shows what kind of person he is.

The book itself is a fun read. It isn’t your computer science or management textbook nor is it your boring documentation of the Chandler team where the narrator jumps from one event to the next. Scott Rosenberg gives his readers a view of the conversations, meetings and other happenings inside the Chandler team’s office. From a programmer’s perspective, some events may even sound eerily familiar to their own encounters and conversations. I do appreciate his attempt at providing the occasional explanation of a principle or idea to the reader, sometimes even giving a short backstory at how that came to be. Some find that distracting, but I do find it helpful. This can even serve as a gateway to newbie software engineers trying to understand the world they work in and introducing them to some very helpful engineering principes while they’re at it. Though there are some concepts that he does seem hard pressed to explain in layman’s terms, but hey who doesn’t have to go through that challenge every once in a while? Concerning the book’s theme, one might get the impression that this book is one big what-not-to-do-when-building-software. There is much of that yes. We can benefit from other people’s hindsight, examples of which are when in early chapters/years they rebuilt much of what they could have reused from elsewhere. But some of those simply reflect the difficulty of building software. Where collaboration and figuring out how the team that builds the software works together and the direction they should take is a great deal important as actually building the software itself. There are other points in the project where taking a calculated risk does not mean that it stops being a risk such as the team’s investment against the web technologies of their time. There’s a lot to learn from every team. The team’s members themselves would no doubt have learned much from the experience. And readers of this book will find much to learn from them as well.

Great read. Learned a lot. Would recommend. 5 stars.

View all my reviews

This review was first posted on Goodreads

Wat To Do: Yet another To Do app

So I’ve set out to build my own to do app. Yeah, I know what you’re thinking, “But Jonn, there’s already a thousand to do apps already, why would you want to make another one?”. Because.. Reasons. Don’t worry. I’ve asked myself the same question a thousand times, and here’s what I came up with:

First of all, I figured that I’ve never really worked on a project that’s wholly my own. The side projects I do at home are really related to the work I’ve been doing for the past 7 years. I think I did a generic Excel parser once and dynamic fields just so I could repeat the same process when I was at work. This is probably only the second time that I’m going to work on a project I would have others use.

Another reason is that I really, really wanted to learn React.js. Been hearing a lot of good things about it. I tried Angular on my last side project (http://holidayba.com), but it’s a good thing that I postponed devoting more time to it until React came around. I work with some legacy systems (read: systems I wrote 4 or 5 years ago). Back then, as a fresh graduate, given the immense responsibility of writing a Javascript framework which would be used for future projects, I wrote a lot of code I’m not proud of today. And ever since I’ve been trying to check out Javascript frameworks along with some patterns that would help me organize the chaos that was my code. But a lot of those frameworks worked best if you already started with them and required that I overhaul the whole thing, which of course we would never be able to fit into the schedule. I hear good things about React and from what I’ve used so far, the component model is a great way to organize all these files. We’ll see.

Third, the other to do apps are not my to do app. I’ve tried a few other to do apps, and kudos to those who made them. They’re really well done. I’ve been a fan, and user of Any.Do for around 4-ish years now I think. Great design. Very simple. Loved the whole thing. I’ve flirted with one or another to do app in between too. But as I’ve learned more and more of how to think of productivity, I’ve found it hard to fit those to do apps into my own personal workflow. That and the lack of self-control and discipline I have at times when I’m at home really makes those apps useless. So I’m gonna build a to do app for lazy me. And use that to do app to help me build the to do app. If that somehow works, and it works to change my workflow for the better despite my habits, then I’d have built an app for lazy people (sample size: me). If it doesn’t work out for other people, then at least it would work out for myself. I could certainly use a good productivity boost so it works for me either way.

And lastly, this gives me something to blog about. It’s so hard to keep up this blogging habit. I know, I really, really know that writing helps me better understand what I’m doing, but it’s just soooo hard to get into the habit of it. I have to write, then read, and re-read my work. So much time is spent when I could have been straight up coding all the while. But it’s a good habit and skill to develop so I’m going to hit two birds with one stone here and blog about the experience. The danger here is that if I publicize what I’m doing, I run the risk of instantly gratifying myself from the social acknowledgment of what I’m doing. This TED talk sums up what I’m talking about here. That’s happened more than once before. But on the other hand, publicizing what I’m doing should nag me enough to keep on doing it since I now have a publicly declared obligation to keep at this habit. Or I feel that way. Here’s to hoping this whole thing works.

And with that, I’d like to introduce you to Wattodo?, a to do app for people who don’t know what to do.

Brand-marketing

I don’t know how that’s gonna do it yet, but I hope to blog more about my journey writing it. I’d be thrilled if you could join me as I try to work this out.

Book Review: Brain Rules

Brain Rules: 12 Principles for Surviving and Thriving at Work, Home, and SchoolBrain Rules: 12 Principles for Surviving and Thriving at Work, Home, and School by John Medina
My rating: 4 of 5 stars

Reading books on the brain always blows my mind (Yes. That’s intended.). There’s only so much thinking about thinking that you can do before your thoughts go everywhere. But if you’ve got one place you want to improve on, then why not improve on how you think. What better part to lifehack than the thinking process? Everything else depends on that. Now I’m sure that most of you, like me, didn’t take up brain science in College. And as cool as reading a book on Brain science sounds it would probably bore you to death and kill more brain cells than it would help create. Fortunately for you and me, this here book makes all those fancy concepts easy to understand!

There are 12 brain rules John Medina covers. He does a good job of going over each rule. Each chapter would usually start with the occasional witty story to grab your attention. Then he would mention the discoveries behind each rule, the theories surrounding it, and one or two studies that back the theory up. The chapter ends with the practical impacts of each rule in your personal life or even for society at large (e.g. Brain Rule – Exercise: Because more Oxygen in brain; Brain Rule – Sleep: Sleep well, think well.). You’ll find the occasional illustration in between every few paragraphs. Those make me feel that in writing the book itself he applied the very principles that he’s writing about! (Brain Rule – Attention: We don’t pay attention to boring things.)

The book takes on a very evolutionist stance. The first chapter makes it clear enough (Brain Rule – Survival: The human brain evolved, too.). As a Creationist holding to the Bible’s claims I have trouble accepting some of the premises of some of the rules. But hey, you can still explain most of these from the Creationists point of view. For example, instead of the theory that the brain focuses on survival because of the Savannah, we can just as well say that God our Creator made our brains react to threats for our safekeeping.

The information in the book is valuable stuff, which like I said, have practical tidbits that you can apply to your daily life. You would probably find the same in other sources. Some even seem to be too common-sensical (You don’t need a book to tell you that stress hurts your brain). But with all the drivel you’d find in tabloids, in websites online, or the conflicting claims every week in your Facebook news feed, it doesn’t hurt to actually hear from someone with authority on the matter (with of course the references to back the claims).

If you’ve already forgotten some of the rules after reading the book (Brain Rule – Memory: You gotta repeat it before it sticks), there’s also a neat website companion or the book! http://brainrules.net/

4 out of 5 stars. Would read again (Brain rule – Memory). Also, I’m a guy, so I find details hard to remember (Brain Rule – Gender: Male and female brains are different.). You never know what other info you might unearth (Brain Rule – Exploration: We are powerful and natural explorers).

View all my reviews

 

This review was first posted on Goodreads

Dying Thoughts

What would you do if someone held you at gunpoint? Would you run, or would you try to fight? That was the topic of a conversation with friends a few days ago. Best answer I could give is a general “it depends”. Is the gunner after something? Am I in a location where there’s a place to hide, or is it an open field? Am I with other people? One of my friends shared that he and his girlfriend already agreed on what they’d do if the situation does happen. That’s good planning. You’ll never know when something like that would happen, and if you’re not prepared it might already be too late.

Personally, I have a hard time preparing for death. Whether it’s planning for what to do in a life-and-death situation, or something less tragic like buying insurance. It seems like a pointless activity, a futile effort. Some activities extend your life for just about one moment more. Some are plans for future setups that extend beyond my lifetime, a future as unsure as the future where I do exist, like tomorrow. Plans we make collapse as we execute them. What reason is there for us to expect that our plans beyond our lifetime will fare any better?

Would you believe that there’s a whole book in the Bible sharing these same concerns? Our Pastor, Pastor Noel Espinosa, began a series on the book of Ecclesiastes years ago, and the message still resonates with me.

I hated all my toil in which I toil under the sun, seeing that I must leave it to the man who will come after me, and who knows whether he will be wise or a fool? Yet he will be master of all for which I toiled and used my wisdom under the sun. This also is vanity.

– Ecclesiastes 2:18-19

Vanity is the common theme in the book. At first look it seems that the writer’s repeated refrain is that of the meaninglessness of his work, his achievements, his pleasures, or his knowledge. But as Pastor introduces the series, he shows how the Hebrew word hebel actually means breath, vapor, and is the key to understanding this book. Our lives are brief, like vapors that pass by and are no more when faced with death. Why worry, and plan so that I can extend whatever joyous moments can be hand in this all too brief life? Pastor summarizes it in one of his points:

The reality of death makes any happiness all too brief, and without God, a mere illusion

The writer, thankfully, does not leave his readers to their despair as it seems he has. The book points us to the reality where life is not mere illusion, but that we exist for Him who created us, our God. The Lord will work continue to work in our lives for His glory. What we do matters. Preparing for our eventual departure may still not yield what we expect, but as Christians we know that whatever happens it will be for the good of those who love Him. The future that extends beyond our lifetime will be governed by the same unchanging God, as will the moments that will lead up to it.

Book Review: Si

SiSi by Bob Ong
My rating: 5 of 5 stars

Walang kupas talaga si Bob Ong sa larangan ng pagsusulat. At sa paglabas ng kanyang ikasampung libro ay napatunayan na niyang kaya niya iba’t ibang kategorya ng kwento ma-horror man yan (Kaibigan ni Mama Susan; na hindi ko naman binasa), tungkol sa superhero (Kapitan Sino), script man ng iba’t ibang klase ng sine (Lumayo Ka Nga sa Akin), o love story na paksa ng librong ito.

Pagbuklat mo pa lang sa unang kabanata ay mapapaisip ka na, dahil ang nakalagay ay pang-72 ito. Ngayon pa lang ako nakakita ng librong sinulat ng ganitong istilo. Naiintindihan ko na kung bakit, ayon kay Bob, sinimulan niya tong isulat kasabay pa ng Stainless Longganisa pero ngayon lang natapos.

KONTING SPOILER LANG
Gaya ng nabanggit ko kanina tila pabaligtad ang pagkakasulat ng librong ito. Pagdating pa lang sa kabanata 60 ko napagtanto na ang bawat kabanata pala ay tumutugma sa edad ng bida. Bawat kabanata ay may inilalahad siyang alaala na karamihan ay may kinalaman sa pagmamahalan niya ng kanyang naging asawa. Paalala, hindi ito librong hapon na dapat basahin simmula sa huling pahina. Sinadya ni Bob na magsimula sa edad na 72. Maganda ang kinalabasan. Maihahalintulad ang karanasan na ito sa kung may nakilala ka ngayon lang sa matandang edad na at ikinuwento niya ang mga nangyari sa buhay niya. May mga detalye sa kwento na mapapatanong ka, “Sino naman yung karakter na iyon?” tapos sa sunod na kabanata ay saka lang niya maipapaliwanag kung ano ba ang kinalaman ng taong iyon sa buhay niya.

Natuwa din ako sa kung paano niya ipinahiwatig ang buhay niya. Kalimitan ang karakter ng isang libro ay may isang dimensiyon lamang. Kung ang karakter na ito ay matapang, mabait, madaldal man o hindi sa simula ng libro dadalhin mo ang impresyon na iyon hanggang katapusan. Pero dahil nga nilalahad ng librong ito ang pagtanda at paglago ng isang tao sa bawat yugto ng kanyang buhay, makikita mo ang pagkakaiba ng bida noong siya ay 20, at noong siya ay 60. Napahalagahan ko rin kung paano naipakita nito na ang mga nakatatanda sa atin ay nabiyayaan ng Diyos ng mas maraming karanasan na kalimitan ay nangangahulugan din na may dagdag na karunungan.

Marami pa akong ikinagusto sa librong ito. At kung nagkataon na 4-stars ang ibibigay ko sa librong ito, ay siguradong mapipilitan akong dagdagan ang rating na iyon para lang sa huling kabanata at pahina ng libro kung saan ay nabigyan ng panibagong kahulugan ang naunang 71 na kabanata na binasa ko bago makarating doon. Naglalaman ito ng isang mahalagang mensahe na sa panahon natin ngayon ay mahalagang pag-isipan.

View all my reviews

This review was originally posted on Goodreads.

Round Three

We’re gonna give this another go. Hey guys, I’m Jonn, software enthusiast and stuff, and I’m gonna give this blog thing another go. Just a dump for some random topics until I build up the habit and find out what I really want to end up talking about. Not gonna write a too-long introduction so that I don’t feel guilty when I eventually slack off on this writing exercise.