MySQL: Convert Character Set and Collation

The character set and collation changes for a DB have important changes on the storage requirements and query result behavior. The character set for a table can be changed in two ways:
a. Using CONVERT TO query
b. Using MODIFY <column name>

Methods

The CONVERT TO method makes sure that each column fits the new character set range after conversion. So a column type TEXT of character set Latin would not accommodate the character set of UTF8MB4. Since the latter needs 4 bytes for a character, while in Latin one byte is required for a character.

So CONVERT TO rounds of the column type to the nearest next size.

The next method of MODIFY does not make any change to the column type.

Conclusion

I prefer the first method because it’s assuring that the table column sizes are technically large enough and also it eliminates any manual changes for a column.

Examples

ALTER DATABASE <db name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `test_table` MODIFY `column1` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

ALTER TABLE `test_table` MODIFY `column2` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

References

How to: MySQL Get Database Name and Use in Another Query

How to: MySQL Get Database Name and Use in Another Query

The following SQL query get the database name and use the name to run another query.

SET @dbname = DATABASE();

ALTER DATABASE @dbname CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

References

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
%d bloggers like this: