Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

This Week I Learned 2018 - Week 05

Last week post or something from the archive.

New week and we're embracing the second month of 2018. The summer (for Southern Hemisphere, summer starts from December 1 till February 28 or 29) wind was blowing gently indicating the near of Chinese New Year. 活了大半辈子,原来南半球的季节正好跟北半球相反,真的是受教了!俗语说,“一年之计在于春,一日之计在于晨”, 自己要好好检讨及规划!

Going through my daily steps count, less activity this week due to sickness. Icy cold drinks during midnight and middle-age guy don't was not a good combination. But luckily we have long vacation, good to catch up with my needed rest and recovery. Comparison of total steps walked as illustrated below. (Yes I know the missing legend) Target for next week is to average out the steps till 5000 steps and above.


Dropsy and the whole body is pineconing? Nothing much we can do besides except humanely euthanize the fish. We lost three this week and unexpectedly the Blue Mable is going down also. The typical treatment is daily water changes combines with aquarium salt, Methylene blue, or Malachite green. We're not sure but I suspicious was due to overfeeding which leads to stress and constipation as well as infection.

When visited enough Aquarium shops, you will learn a thing or two. First, antibiotic powder (yellowish colour, suspect the main ingredient is Sodium Nifurstyrenate) usage was quite common especially expensive tropical fish like Discus (not a fan and seriously, a moving water pancake?!). Second, fish sellers in Market Road packed their fishes in plastic bags treated green water (suspect is Malachite green) and unpack it again in plastic square tank for selling each water in plain water. You can achieve profit if you can sustain the longevity of the fishes until they are sold to the customer.

More conversations breeders. It seems that female Bettas are far more expensive than male and fellow breeders don't want to sell their precious female Bettas to retain their quality breed of lines. Only for those who have financial gains from doing this. I wish more breeders should focus partially on the locally Malaysian Bettas. There is one breeder and I wish him all the best. Wild Bettas found locally may not be that colourful but it's good to breed and increase the population.

You know the play was so good that even the opposite bench players applauded it. This reminded me of Kobe Bryant's baseline reverse dunks. Along with Giannis AntetokounmpoBen Simmons is another NBA player to watch out for in the coming few years. Only time will tell.

Too many blockchain wannabe tech startups in Malaysia. Enough said. The never ending of startups chasing the next hype cycle.

How do you unstaged changes in Git? There are cases where merging or rebasing kind of messed up and you're left with a list of modified files.
$ git stash save --keep-index --include-untracked

Meanwhile, you can clean up your local working repository in Git through interactive mode or Git clean interactive.
$ git clean -di

The list reduction functions in List::Util. Some good examples:
$foo = reduce { $a > $b ? $a : $b } 1..10       # max
$foo = reduce { $a gt $b ? $a : $b } 'A'..'Z'   # maxstr
$foo = reduce { $a < $b ? $a : $b } 1..10       # min
$foo = reduce { $a lt $b ? $a : $b } 'aa'..'zz' # minstr
$foo = reduce { $a + $b } 1 .. 10               # sum
$foo = reduce { $a . $b } @bar                  # concat

More effed up stuff in our beloved MySQL. This time, "SQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails ....". To quote from the documentation (emphasis added),
If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

In other words, self-referenced foreign key(s) or recursive deletes doesn't work in MySQL (it WORKS in a PROPER database management system like PostgreSQL). You have to create another new table to link the relationship, something like many-to-many data model. If you needs deep nested hierarchy data structure, use Closure Table.

This Week I Learned 2017 - Week 52

Last week post and something from the current and previous years.

Last week of the year 2017. Is good to have some reflection by looking back at all the blog posts for the whole year. In total, including this post, I've written 75 posts, 17 more posts that last year. My plan for next year is hopefully even more post but on diverse topics rather than usual technical stuff.

Sedentary lifestyle is still an issue although how much I tried to get more active in my daily life either at my office and my home. The heatmap below illustrates that. Compare to last year, I'm comparatively less active. As you age, metabolism drop and lacking physical activity does impact you health someway or another. One good indicator is you gain more weight. Furthermore, you have to spend more money to buy new cloth. Having a pedometer does help but it's good you can have a weekly review of your daily steps taken. Having a weekly review on the step taken or exercises done is the plan for the next year. Adjustment through adding one or two micro habits into your daily life here. Awareness is the key here.



FOSS contribution? Unfortunately, still nothing much even though the daily commit streak was there. Is an ongoing battle on what to work on and pushing the first commit ahead. Although I've tried to created a few new project but didn't pursuit much ahead. My weekly reflection indicated that trying to do too many stuff at one time will make you overwhelmed and nothing much get done. The usual Analysis Paralysis symptom. Adjustment for 2018? Keep the daily streak but be more focus and set priority on what you want to do. Finish what you plan to do. If you can't, delegate or postponed it to a scheduled date, and move on to something else.



And this is how I plan to do in coming year of 2018.

Enough rambling, back to the usual stuff.

What did I miss? That probably the most important question you should either ask yourself or the domain expert in the pursuit of improving or mastering your craft. I've long seek the answer to this question. How good can you be? What is your threshold of your skill? The book The Talent Code did provide some answers (one of it is practice or the way how to practice but there is more to that). However, the missing piece is still how to sustain the curiousity to move forward, especially you've done the craft long enough and stuck in the "same sh*t different day" or "been there done that" mindset. This is very true when comes to software development where each year, some new techs were "rediscovered" again and again. However, this does not apply to professional sport where your genetic and age will play a crucial role in advancing your craft.

In MySQL, ON DELETE NO ACTION is the same as NO ACTION RESTRICT. Yet another quirk of our beloved MySQL database. Compare to the standard SQL or other real databases,  InnoDB storage engine does not supports deferred check, meaning that you can take no action on deleting a record during a transaction and resolve it before completing the transaction. Yay! Embrace the powerful feature of MySQL!

React is the future, inevitably you have to embrace it and add it to your front end development stack one way or another.

This Week I Learned - 2017 Week 18

Last week post or you want to read through the whole series.

Since the prevalent of Internet these days, we're more connected that ever. And yet, we're even more isolated. More and more shopping malls and residential areas are being built especially around the transit stations. Basically all looks the same and people are discourage to travel to another town anymore because everything is available either online or at your nearest malls.

Sick. Down with fever. It has been a while since last time. No one but myself to blame. For people around my age, good rest and food is a way to healthy life. We're not seeking prolonged life but prolonged health.

#1 So, what's your Plan B? Good discussion from the comments. While there is some sort of discrimination against older programmers, but the reality is older programmers may just burn out or bore doing the same mind-numbling stuff over and over again. Yes, the same sh*t but different day. Not everyone have the opportunity to work meaningful projects (yes, subjective view), but most of us just work on some glorified spreadsheet or database skin. You see the same mistakes repeated over and over again from different systems and it's just pointless or bother to raise it up again.

#2 On becoming a low-level programmer. There is a curated list of information especially on the hardware part.. There is also another list of learning the fundamental of computer science. So many things to learn and explore and yet, so little time. Sometimes the reality of life commitments limit our choice on the thing we want to pursuit. I need to finish everything by July before I can start to clear the list from my bucket list. One step at a time. Follow the system.

#3 First, make it work, then make it right, and finally, make it fast. Get the thing to work first before you're thinking about making it right (giving the right name) and optimize it. Fulfill the requirements of the stakeholders first (prototyping) before you're looking into doing it right and fast. Something I need to remind myself constantly as we're often obsess with perfection. What's the point if your build it the right way and fast but does not meet the stakeholder needs?

#4 How to write a REST client in Perl. Most Perl modules have limited documentation, for example, little example on how to use the APIs. This is where PHP (it has been so long since I mentioned something about PHP) really shines. As the language is slowing dying, there are little or no good example on how to start something up fast.

Meanwhile, something rather interesting about Perl. I've seen quite a few Perl codes with subroutines that prepended with ampersand (&) operator. Basically, the ampersand is needed if you're calling a subroutine before its declaration. Off course, there are many other reasons and usages to use the ampersand, but commonly as reference to a subroutine.
&hello;

sub hello {
    print "hello";
}

When using OO with Perl, you will need to instantiate the constructor or method invocation through the arrow (->) operator (a reference). Good examples given below.

Using the arrow notation or virtual method.
my $apple = Fruit->new("apple");
$apple->get_price('usd');

Using the double colons notation or static method.
my $apple = Fruit::new("apple");
Fruit::get_price($apple, "usd");

However, to invoke the method either statically or virtually, you'll need to write the constructor as follow.
sub new {
    my $self  = shift;
    my $class = ref($self) || $self;
    return bless {}, $class;
}

#5 "error: src refspec master does not match any." Silly me. You need to commit something locally before pushing to remote Git server.

#6 Tiny Python Notebook. Definitely not tiny but quite comprehensive guide to Python 3.6.

#7 How to unstage all staged files in Git? It's so easy that the best answer is not upvoted enough.
$ git reset

#8 Postgres Weekly. When come to FOSS database choices for web application for Intranet application, I'm strong opinion that PostgreSQL should be the default choice. Feature like Range Types and others solves quite a lot of issues and save lots of developer-hours. Yes, MySQL has its own usage, but for any business application which involves monetary and strong ACID compliance, it's not a right choice.

This Week I Learned - 2017 Week 10

Last week post or just follow the whole series.

Got my result today. It seems I've officially "crossed" the borderlines stage. I'm still contemplating on the next step but drastic life style changes is inevitable (more in item #5). If left untreated, it can grow into bigger problems, not only for yourself, but for your loved one as well.

#1 You May Want to Marry My Husband (via HN). Beautifully written. We need to be constantly reminded of the fragility of life especially contemplating one's mortality. The lack of awareness will bite you and your loved ones somehow in the future. Over 30-something and still bogged down by random trivialities in life? Nothing but continual reflection on your life priorities (often misplaced) and gratefulness helps to make peace with current and past self. Regrets? Yeah, plenty. Who doesn't? But not to a point where you will lament later in life that you should, would, or could. Wish you've taken a different path? Nah. I'm good. Yes, there is still room for improvement. Looking forward to that.

Further reading. The 10 Best Modern Love Columns Ever.

#2 On Perl. Interesting thing learned. How do you clone a hash and at the same time, reassign new values to some of the existing keys? Simple and yet so effective. To explain this,  `%$foo` expands expands the hash into a key-value pair and the curly braces (anonymous hash constructor) will create a new hash reference. Output is from Devel::REPL module, the modern Perl interactive shell.
$ my $foo = {a => 1, b => 2, c => 3};
\ {
    a   1,
    b   2,
    c   3
}
$ my $bar = {%$foo, a => 4, b => 5}
\ {
    a   4,
    b   5,
    c   3
}

Instead of just doing this.
$ my $foo = {a => 1, b => 2, c => 3};
\ {
    a   1,
    b   2,
    c   3
}
$ my $bar = {%$foo};               
\ {
    a   1,
    b   2,
    c   3
}
$ $bar->{a} = 4;
$ $bar->{b} = 5;
$ $bar
\ {
    a   4,
    b   5,
    c   3
}

#3 Carton. Bundler for Perl. In other words, module dependency manager for Perl. Is Perl a lost cause that only a few diehard users still using and maintaining it? Seems likely to me.

#4 Ship daily. That probably the way to get your side project moving and don't feels like you're not completing anything. Can't do it? Maybe you're setting the bar too high and setting yourself up to failure. Of course, some are created to be thrown away. While I don't totally agree on the needs to to finish all your side projects, but when comes to actual paid work, we need rethink our role. Instead of a programmer, think yourself as a problem solver instead. A tiny shift of that mentality will get you closer to the deliverable.

#5 Getting older? Getting weaker? Maybe you should check your weight and measure your Body mass index (BMI)? If so, you should to readjust your diet. Stop eating like you're in college days. More (not just) portions of fruits and vegetables? Furthermore, be informed and mindful of what you put inside your body. Of course, exercise is needed as well. However, exercise alone does not lose weight. Joao Venture have experienced weight lost by reducing most of his sugar intake and maintaining 3 km daily walks per day. Using this calculator, 3 km is 3937 steps, rounded to 4000 steps. Those who works as software developer with sedentary life style, it's a struggle to reach 4000 on daily basis.

Looking through my steps count below, both my mean and median are roughly around 3000 to 4000 range. This fulfill at least one of the criteria. The next step is to adjust my diet by reducing my sugar intake. Interestingly, since the beginning of 2017, I only manage to hit 10000 steps only four times!


On a related note, sleep is also a crucial factor as well. What Kind of Sleeper Are You?

#6 The Unofficial MySQL 8.0 Optimizer Guide. The section on profiling is interesting and I really don't have time to go through each item on databases I'm working one. Definitely going to improve any legacy and messy databases.

#7 When comes to programming, some of us still missing the point that we're a problem solver first and a programmer second. Follow a process and the convention. Prioritize. Keep the solution simple (yes it maybe boring) and don't try to do things the fancy way unless really, really necessary.

This Week I Learned - 2016 Week 43

Last week post or the whole series.

This is one of those week where I become very wordy. ;-)

Previous post was too long when I realized that Blogger don't let me add more than 20 tags. That threshold should be a good indicator for me to stop jotting down the item and move to next week post. Some of these notes below are old items which I haven't have time (lack of discipline) to jot it down in a timely manner (lack of consistency). Don't sweat on this. Reflect, learn, adapt, adjust, monitor, and continue as usual. Although there are some minor hiccups, there is still like 9 weeks to go for this year.

Generating and attached iCal invite through email? It may seems simple as first, but, as usual, it may works for one email client but not for others (we haven't take into consideration of desktop, mobile, and web client). Furthermore, there is iCal and vCal, both are VCALENDAR standard where the former is succesor of the later. Digging deeper, it seems that different email client behaves differently when come to REQUEST METHOD used. The typical troubleshooting procedure is first, make sure you can attach send the iCal correctly through email properly using the email library (using MIME::Lite in my case). Next, read other people code on implementing similar solution. You can may miss out certain API calls or use the library incorrectly.

Testing in Perl. You can go through these articles for a start before you jump in to Test Drive Deveopment (TDD) best practices. Some of the issues I've encountered. First, to compare two different different ordered arrays, `cmp_bag` in Test::Deep is your friend. Second, To simulate delay, instead of using `sleep`, you can use Test::Mocktime's `set_relative_time` to do so. But off course, you will need to learn more tricks about the default standard testing module in Perl, Test::More. Writing a new Perl's module, integrate testing as early as possible

While we on testing. To benchmark the speed of your SQL query, you can disable MySQL query cache. This ensure that no caching is used which may give the wrong impression regarding the speed of the query.

While this is quite old news, two features in Perl 5.10 which I think make a lot of senses. The `defined or` is useful to check if a variable is defined or else use the default value (more examples). And the `state` for declaring a variable as static variable. Useful when you want to implement localized caching.

On Perl again. Check if a substring exists in a string without using Regex through using `index`. I wish Perl have more Object-Oriented way of doing so. Maybe Perl 6 have it?

Note to self. This is very useful. Several alternative ways to monitoring event in Javascript, useful for debugging. My favourite is using the `monitorEvents($0)` method. So simple and straightforward.


While we're on Javascript. Developing a web application with heavy AJAX usages? Well, you have to use `history.pushState` somehow. Why not just switch to Turbolinks?

Storing hierarchical data in database? Adjacent List is the most common and first to use approach when modelling hierarchical tree. However, Bill Karwin's presentation and example and follow up example convinces me of using Closure Table or Bridge Table may be the balance and right approach. This is useful when you're using database system like MySQL that does not supports hierarchical and recursive SQL queries natively.


This Week I Learned - 2016 Week 42

Last week post or the whole series.

Interesting week indeed. It has been a while since I last encountered so many different type of personalities who want or don't want to be a developer.

As usual, what have I learned this week? The usual stuff.

If you're running on GNU/Linux and want a way to manage different Windows OS through Vagrant, you can try this Vagrantfile. Installation and setup is pretty much straightforward, just make sure the Vagrantfile is downloaded. Unfortunately, the login still fail to work.
$ sudo install virtualbox vagrant
$ vagrant plugin install winrm winrm-fs

$ mkdir vagrant_win
$ cd vagrant_win
$ wget https://goo.gl/ATpaEY
$ IE=Win7IE8 vagrant up

Sanic, Python 3.5+ asynchronous web server. The discussion at HN seems rather interesting. While this is nothing new, asynchronous database layer like asyncpg seems rather userful to improve your DB query speed.

Issue with Babun's memory conflict after Windows updates? Try rebasing, not that Git rebasing thought. Cygwin still is the better and prefered choice for Unix experience in Windows. Yes, I know there is Bash on Windows.
1) Exit babun.
2) cmd /c %SYSTEMDRIVE%\Users\%USERNAME%\.babun\cygwin\bin\dash.exe -c '/usr/bin/rebaseall -v'

Customozing HTML's file inputs. Probably the most comprensive guide on different techniques to change the default behavour.

Web framework benchmarks. The Round 10 has one of the best humourous write-up.
The project returns with significant restructuring of the toolset and Travis CI integration. Fierce battles raged between the Compiled Empire and the Dynamic Rebellion and many requests died to bring us this data. Yes, there is some comic relief, but do not fear—the only jar-jars here are Java.
What happens when you rename a branch in Git? Plenty of things. First, you rename it locally. Next, you rename it remotely (is the same as remove the old branch and add a new branch). After that, either you update your upstream URL or checkout a fresh copy of the said new branch. Lastly, you may needs to batch update your commit messages.
$ git branch -m new_name
$ git branch -m old_name new_name
$ git push origin :old_name
$ git push --set-upstream origin new_name
$ git filter-branch -f --msg-filter 'sed "s/foo/bar/"' master..HEAD

The database schema for StackOverflow is publically accessible. I was surprised that it's such a straight forward design and nothing fancy at all. Well, is just a CRUD app with some additional tweaks here and there. However, the ranking formulae is far more interesting when compare to different algorithms used by other popular forum-like sites.

So may ways to iterate through the Perl's array. Implementation 1, 4, and 5 is what I normally used but the 5th method is still my favourite.

Source code syntax higlighter through Javascript? Just found out today, besides highlight.js, there is also Prism.js. The former seems to have more languages support but the later is used for quite a few popular projects.

NBA season going to start soon, maybe is time for me to learn some Statistics through certain API? Can't wait what surprises the 2016/2017 season will give us.

This Week I Learned - 2016 Week 35

Last week post or you might want to check out the whole series. As usual, some findings around the Internet.

September. It's almost at the end of the third quarter of the year. What have you done for the past eight months? No energy to do anything else after work? Switch. Do important things before work instead. This means you'll need to be a morning person and change your sleeping habits. Let's see how this goes. One small thing at a time but in a habitual and persistent way.

Everyone should be inspired by Norman Borlaug. We need more people like him instead of psychopath who wants to see the world burn.

How to make a copy another hash in Perl? Easy, for shallow copy, just use syntax below. Or simply use the Clone module if you have deep nested hash.
my %copy = %$hash;

Two ways of using grep in Perl, which I always confuse. First, by expression and secondly, by code block. Examples as shown.
# grep (EXPRESSION, @ARRAY)
if (grep /a/, @cities) {..}

# grep BLOCK @ARRAY
if (grep { length($_) == 6 } @cities) {..}

The strange case of Perl's DBI returning 0e0 (considered as true zero) if zero rows were affected.
sub do {
    my($dbh, $statement, $attr, @bind_values) = @_;
    my $sth = $dbh->prepare($statement, $attr) or return undef;
    $sth->execute(@bind_values) or return undef;
    my $rows = $sth->rows;
    ($rows == 0) ? "0E0" : $rows; # always return true if no error
}

"MySQL error 1449: The user specified as a definer does not exist." How annoying and rather inconvenient for me.

What are Git's caret (^) and tilde (~)?