Database Collation and UTF8MB4

Database collation defines how characters are compared and hence the order of rows in query results.

  • UTF8: Uses 3 bytes for a character
  • UTF8MB4: Uses 4 bytes for a character, so allows more characters.

How to Decode Collation

utf8mb4_unicode_520_ci

  1. UTF8MB4
  2. Unicode 5.2.0 comparison for characters
  3. ci: Case Insensitive comparisons

Reference

Written with StackEdit.


Go, gorm, nested JSON and associations

Go, gorm, nested JSON and associations

gorm provides a clean way to store a nested JSON with the relation of associations among tables. The following code creates a DB with three levels of nesting.

package main

import (
	"encoding/json"
	"fmt"

	"github.com/jinzhu/gorm"
	_ "github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "postgres"
	password = ""
	dbname   = "postgres"
)

type Page struct {
	ID     int64  `sql:"auto_increment" json:"-"`
	Number int64  `json:"number"`
	Book   Book   `gorm:"foreignkey:book_id" json:"-"`
	BookID int64  `json:"book_id"`
	Text   string `json:"text"`
}

type Book struct {
	ID          int64  `sql:"auto_increment" json:"-"`
	ShelfPlace  int64  `json:"shelf_place"`
	Shelf       Shelf  `gorm:"foreignkey:shelf_id" json:"-"`
	ShelfID     int64  `json:"shelf_id"`
	Author      string `json:"author" gorm:"unique;not null"`
	Publisher   string `json:"publisher"`
	PagesAmount int64  `json:"pages_amount"`
	Pages       []Page `json:"pages"`
}

type Shelf struct {
	ID          int64  `sql:"auto_increment" json:"-"`
	Number      int64  `json:"number"`
	BooksAmount int64  `json:"books_amount"`
	Book        []Book `json:"books"`
}

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := gorm.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Create
	//db.Create(&Shelf{
	record := `{
			"number": 1,
			"books": [
			  {
				"shelf_place": 5,
				"author": "Lewis Carroll",
				"publisher": "EA",
				"pages_amount": 2,
				"pages": [
				  {
					"number": 2,
					"text": "lorem ipsum"
				  },
				  {
					"number": 4,
					"text": "dolor sit amet"
				  }
				]
			  },
			  {
				"shelf_place": 7,
				"author": "Mark Twain",
				"publisher": "Activision",
				"pages_amount": 3,
				"pages": [
				  {
					"number": 1,
					"text": "this is"
				  },
				  {
					"number": 3,
					"text": "a test"
				  },
				  {
					"number": 6,
					"text": "of json"
				  }
				]
			  }
			]
		  }`
	var shelf Shelf

	err = json.Unmarshal([]byte(record), &shelf)
	fmt.Printf("err=%v\n", err)

	db.DropTableIfExists(&Shelf{})
	db.DropTableIfExists(&Page{})
	db.DropTableIfExists(&Book{})

	// Migrate the schema
	db.AutoMigrate(&Shelf{})
	db.AutoMigrate(&Page{})
	db.AutoMigrate(&Book{})

	db.Create(&shelf)
	
	// Fails because author is a unique attribute
	//db.Create(&shelf)

    // Preload is necessary to query nested structure.
	db.Preload("Book").Where("author = ?", "Mark Twain").Find(&shelf)
	fmt.Printf("shelf=%v", shelf)
}

References

Written with StackEdit.


Mac OS X: role “postgres” does not exist

  1. Install Postgres

    brew install Postgres
    
  2. Try to log in.

    $ psql -h localhost -d postgres -U postgres  
    psql: error: could not connect to server: 
    FATAL:  role "postgres" does not exist
    
  3. Create the user

     $ /usr/local/opt/postgres/bin/createuser -s postgres 
    
  4. Start the server

    $ pg_ctl -D /usr/local/var/postgres start 
    pg_ctl: another server might be running; trying to start server anyway
    waiting for server to start....2020-06-27 18:12:07.784 +08 [20812]
     FATAL:  lock file "postmaster.pid" already exists
    2020-06-27 18:12:07.784 +08 [20812] HINT:  Is another postmaster (PID 4901)
     running in data directory "/usr/local/var/postgres"?
     stopped waiting
    pg_ctl: could not start server
    Examine the log output.
    

It just works!!

$ psql -h localhost -d postgres -U postgres
psql (12.3)
Type "help" for help.

postgres=#

References

Written with StackEdit.


Building a Video Conferencing Service With WebRTC

What I learned in WebRTC

  • It’s a peer to peer communication protocol to transfer media, text, and data.
  • It does need a server for the control path that includes device discovery and agreement. The process is called signaling.
  • The signaling server is free of any protocol and just needs to make two or more WebRTC clients share data.
  • WebRTC is available on all popular browsers (Chrome, Firefox, Safari & Brave).
  • It’s very efficient because there is no server involved in data transfer.

How to Build a Video Conferencing Solution

It requires a host (cloud or physical) for WebRTC clients, ICE & signaling server. A very production like implementation is https://github.com/webrtc/apprtc.

References

Written with StackEdit.


Interface Quirks in Golang

An interface defines a set of methods. A struct type implements these methods and qualifies the object type as an interface type.

However, how the struct implements the interface?

There are two ways:

  • As a pointer handler
  • As a value handler

Implementation with a struct value handler

package main

import (
	"fmt"
)

type Dummy interface {
  Add(int, int)int
}

type Adder struct {
  magic int
}

func (s Adder) Add(a, b int) int {
  fmt.Printf("magic:%d\n", s.magic)

  s.magic = a + b + s.magic
  return s.magic
}

func GenericAdder(object Dummy) {
    object.Add(2,3)
}

func main() {
  nums := Adder{magic: 43}
  nums.Add(2,3)
  GenericAdder(nums)
}

Output

magic:43
magic:43

Implementation with a pointer handler

package main

import (
	"fmt"
)

type Dummy interface {
  Add(int, int)int
}

type Adder struct {
  magic int
}

func (s *Adder) Add(a, b int) int {
  fmt.Printf("magic:%d\n", s.magic)

  s.magic = a + b + s.magic
  return s.magic
}

func GenericAdder(object Dummy) {
    object.Add(2,3)
}

func main() {
  nums := &Adder{magic: 43}
  nums.Add(2,3)
  GenericAdder(nums)
}

Output

magic:43
magic:48

Conclusion

  • A pointer handler is useful if the struct variable is updated by multiple handlers and the variable state needs to persist.
  • When the method is called with a value of struct, Go uses a copy of the struct variable.
  • The golang function signature stays the same for interface argument. It does not care for the passed argument type (pointer to struct or value of struct)

References

Written with StackEdit.



Design Pattern: Active Record

Active Record

An Active Record Object represents a row in a DB table. The object has fields mapping to a column in the table and methods to access table data.

Active Record connects classes to relational database tables to establish an almost zero-configuration persistence layer for applications.
The library provides a base class that, when subclassed, sets up a mapping between the new class and an existing table in the database.
In the context of an application, these classes are commonly referred to as models.
Models can also be connected to other models; this is done by defining associations.

Source

Active Record objects don’t specify their attributes directly, but rather infer them from the table definition with which they’re linked.
Adding, removing, and changing attributes and their type is done directly in the database. Any change is instantly reflected in the Active Record objects.
The mapping that binds a given Active Record class to a certain database table will happen automatically in most common cases but can be overwritten for the uncommon ones.

Source

Use Cases

  • Golang gorm library is the implementation of the Active Record design pattern.
  • Ruby gem ActiveRecord


Python datetime strptime: unconverted data remains

Problem

    timeobj = datetime.datetime.strptime(my_time, '%Y-%m-%d %H:%M:%S').isoformat()
  File "/usr/lib/python2.7/_strptime.py", line 335, in _strptime
    data_string[found.end():])
ValueError: unconverted data remains:

Solution

The error was an elusive one. The solution was to make sure that the input time string is exactly as the format expected.
The missing piece was an extra space after the string which was causing format mismatch.
So the solution was to call strip() on the string before passing it to strptime().

Useful Tips

  • Read the format strip syntax of strptime().
  • datetime objects are very handy and general operations such as comparison are inbuilt.

References


Database Trigger Procedure in Postgres

Introduction

A trigger is an easy way to enforce a check or constraint, log mutations on a table. It is available for each row or a statement modification.
You can ask the DB to run a function triggered on an action (row append, update, delete) and a clause and timeframe (before/ after the operation).

Ingredients

  • Watch the Youtube video on Postgres trigger for 10 minutes (link)
  • Postgres psql client
  • Any text editor

Writing a Trigger Procedure is different than a Query

  • Find out Postgres datatypes
  • Typecasts using CAST() method
  • Learn Trigger Procedure Syntax

Sample Trigger Procedure

CREATE OR REPLACE FUNCTION time_range_check() RETURNS trigger AS
'
DECLARE
    acceptedTime timestamp;
    newTime timestamp;
    defaultTime timestamp;
  BEGIN
    acceptedTime = LOCALTIMESTAMP(0);
    defaultTime = to_timestamp(1577800000);
    newTime = to_timestamp(CAST(NEW.value as bigint));
	
    IF  newTime < defaultTime  THEN
		 raise EXCEPTION ''invalid past timestamp passed'';
         return NULL;
	END IF;
	IF  newTime > acceptedTime  THEN
		 raise EXCEPTION ''invalid future timestamp passed'';
         return NULL;
	END IF;
	RETURN NEW;
  END;
'LANGUAGE 'plpgsql';

Trigger Code

create trigger my_range_check 
before insert on my_table 
for each row 
when (NEW.item_id = 1)
execute procedure time_range_check();

How to Execute

After composing the trigger procedure and trigger, just paste the text on the psql command line.

How to Debug and Develop

You will get a hint of an error on pasting the trigger code on psql CLI. Every time the procedure code changes, we need to drop the trigger.

# drop trigger my_range_check on my_table;

List Triggers

# \d table_name

Reference