MySql Database

1. Database In General

Relation database, Document database. 

Database Table Relationships

One to one:

mainly used for optimize a big table into two, one table contains often-visited columns, and another one contains rarely-visited columns. By this way, min. IO operations, i.e. reducing the amount data set that need to be loaded from persistences to memory.   

One to many: 

one entry ref. to many entries in another table

Many to many:

mutually ref. to many entries.

Self-reference:

entries ref. to anther entry located at the same table. 

Appendix 1. 

When we use SQL database, and when we select NoSQL database

SQL (Structured Query Language) databases and NoSQL databases are both types of databases that can be used to store and manage data. The choice between the two depends on several factors, including:

SQL databases are best suited for structured data, meaning data that can be organized into tables with defined relationships between the tables. This type of data is well suited for tasks such as financial reporting, where data is typically highly organized and predictable. SQL databases are also well suited for applications that require complex transactions, such as banking or e-commerce, where data consistency and integrity is critical.

NoSQL databases, on the other hand, are best suited for unstructured or semi-structured data, meaning data that doesn't fit neatly into a fixed table structure. NoSQL databases are often used for big data and real-time web applications, where data is generated at high volumes and speeds and needs to be processed and analyzed quickly. They are also well suited for cloud-based applications and distributed systems, where horizontal scalability is important.

In summary, the choice between SQL and NoSQL databases depends on the type of data you need to store and the demands of your application. If you have structured data and need to support complex transactions, an SQL database may be the best choice. If you have large amounts of unstructured data and need to handle real-time data processing, a NoSQL database may be a better fit.

 
 

Git commands

git-shell commands:

Git Local Config

git config --global user.email: showing current git user default email. 

git config --global user.name "John Doe": config git user name 

git config --list : check your local git config 

As committing
git init: creating a local git repository, i.e. .git in the current folder.
git add . : adding all files in a current folder for commit
git add filename.* : adding a specific file in the current folder for commit
git commit -m 'another commit' : commit added files to the local repository with a commit message.
git commit -a : commit all changes in the current dir.
git commit -am "message": commit all changes with a message in the current dir.
git remote add origin https://github.com/yichunzhao/Java8.git : link local repository to remote repository.
git push -u  dst  src ; git updating remote with a local source; -u: update

git remote rm origin: disconnect the link with the remote repository.

git pull: it is a shorthand for git fetch + git merge fetch_head

Info. 
git status: current branch status.
git remote -v  v: verbose displaying info. of remote repository

Branch 
git branch -r: remote branches
git branch: local branches

git branch -d|D branch-name: removing a local branch

git branch -d|D combined with -r branch-name: removing a remote branch

Push 
git push --set-upstream origin red: push local feature branch to the remote, when at the first time.

Stash 

git stash

git stash show

git stash show stash@{1}

git stash list : list all stash

git stash apply: applying the most recent stashed changes

Or

git stash pop : applying the most recently changes to your repo. 

Creating a new branch
git branch dev
git checkout master:  switching to another branch.

View history
git log :
git log --oneline
git reflog
git log --all --graph --decorate --online
git diff: working directory, a difference between commits

Switching between different branches
git checkout master: switch to master branch
git checkout dev: switch to the dev branch

by default, a remote repository is named as an origin by Git.
git push origin dev: push dev branch to the remote by the following command:

Removing a file or folder
git rm x.text
git commit -m "removing a file"
git push origin dev: don't forget to update remote

Git Merging
as standing on the master and merging changes from dev branch in
git checkout master
git merge dev
git add .  : add all changes from the dev branch
git commit -m "changes from dev"
git push origin master: commit local master into remote

When conflicts happen
git merge -- abort: get back the state before merge conflicts

editing confliction; OR:  choose

Alternatively, you can tell Git that you'll simply go with one of the edited versions, called "ours" or "theirs".

git checkout -- ours path/to/conflict-file.css

git checkout -- .  : git discard all changes that are not staged for commit

merger from a feature branch into local master

git checkout master

git pull

git checkout feature

git merge master

Git Revert
git revert undoes a specific commit and then creating a new commit.

git revert by default pops up a vi editor to modify the commit message; press ESC to command line, ZZ(Capital) command to save the file.

git revert specific-commit --n
git revert specific-commit -- no-edit

git log --oneline: it shows the commit ref(s)
git reflog

git revert commit-ref: then modifying the commit message; normally using the default text revert xxx

Press ESC, shift zz; then quit from the vi editor.
git log: it shows a revert commit created.

Git Reset

git reset: having three types of invocations. --soft(the commit tree head), --mixed(stagging index), or --hard(working directory).

Git/Linux Command
ps -ef|grep git: checking git process running
rm -f .git/index.lock : remove index locker file located in the ./git folder

Git Remove remote folder

making sure you are at the right branch

git checkout branch-expected

git rm --cached -rf ./.idea; -rf:  recursive and force

git commit -m "rm remote folder"

git push 

Git Remove remote and local folder

#remove a file

git rm password.text

#remove a folder

git rm -rf ./.idea

Config git

It allows to config git user name and email.

git config --global user.name "my-user-name"

git config --global user.email "my-email"

Git ignoring

Git disconnect remote repository

git remote -v: display all remote repositories linking to local repo.

git remote add remote_repo_name another_remote_git_repo_url: pointing to local repo. to another remote repo.

git remote rm remote_repo_name: removing the link to the remote repo, not rm remote repo.

How to write java doc

 

How to write Java doc

Connecting MySQL docker from IntelliJ Database

Fresh Mac Setup

MySQL 5.7 default DateTime

 MySQL 5.7 default dateTime mode, zero dateTime is not allowed.  

Since 0000-00-00 00:00:00 is not a valid DATETIME value, your database is broken. That is why MySQL 5.7 – which comes with NO_ZERO_DATE mode enabled by default – outputs an error when you try to perform a write operation.

So a solution to solve this error by reset the mode, i.e.

SET SQL_MODE='ALLOW_INVALID_DATES';

Docker

Docker Concepts

Docker 

In an English dictionary, a docker is a person employed in a port to load and unload ships. Docker refers to a product and a vendor that provides software to run containerized applications.

Container 

A container is enclosed, self-contained, and independent. It represents an independent os or a partial os that may run an application. 

Containers are isolated from each other, and it is also isolated from the underlying OS. It is a self-contained system and can be run on any OS. 

Image

Image may be considered as a blueprint, a piece of instructions to build a container. Docker uses the image as a blueprint to create containers and running them on top of the host OS. 

Docker Volume 

Docker containers are supposed to be ephemeral, created and destroyed by automation tools at any time so that they cannot persist data. To persist data from a container, we have to use a docker volume to plug a host file folder into a container. If a docker is not mounted a volume explicitly, a container will automatically create a volume inside the host. Otherwise, it allows mounting an explicit volume on a container.

docker volume ls:  to show all volumes

there are three types of volumes, a default volume, a mounted specific host folder, or a named volume. In production, a named volume is often applied. 

Docker File 

A DockerFile is a text document that contains all the commands that a user could call on the command line to assemble an image. Using the docker build, users can create an automated build that executes several command-line instructions in succession. 

commands in DockerFile

FROM: define the base image, on which we will be building

ADD: add files to the container being built. Add<source><destination in container>

RUN: add layers to the base image, by installing component

CMD: is used to run commands at the start of the container. these commands run only when there is no argument specified while running the container. 

ENTRYPOINT: os is used strictly to run commands the moment the container initializes. The difference between CMD and ENTRYPOINT will run irrespective of whether the argument is specified.

ENV: define environment variables in the container runtime. 

run apt-get update

run apt-get -y install apache2

Docker Commands

docker pull <image>: Pull an image or a repository from a registry

docker ps: show all running containers

docker ps -a: show all containers, including running and exited ones

docker run -it -d <image>: run a docker container from a image, returning a <container-id>

-it:  give me an interactive terminal 

-d: run the container as a daemon

docker run -it <container > : leaving user an active console that showing app logs and input

docker run <container > : console input is deactivated; user cannot interact with the console

docker exec <container-id>: get the terminal of the running container. 

docker exec -it container-id bash

-it: give me an interactive terminal

bash: is a Linux terminal to interact with

step out of a container, typing 'exit'; 

docker start <container>: Start one or more stopped containers

docker stop <container>: Stop one or more running containers 

docker container rm <containername|containerId>: Removing one or more exited containers 

docker container rename <containername> <newname>: renaming a container

docker kill <container-id>: stoping the container in a forced way. 

docker rm <container-id>: removing the container from the docker supervision.

docker rmi <image-id>: removing a specific image.

docker rmi $(docker images) : removing all docker images

docker volume ls: list all volumes

docker volume crate postgres_vol-1: explicitly create a volume and give it a name. 

flag -v and specify a name for the volume when you run the Container and map it to the directory, like this

docker run -v postgres_vol_2:/var/lib/postgresql/data --name volume-postgres -p 5433:5432 -d craig/postgres:version1

-p: binding a laptop port to a container port

-v: binding a host file directory to a virtual folder of the container; this case binding a named-volume to a specific virtual folder of the container.  

docker volume --help: finding out other docker volume options

docker logs <containerName|containerId>: showing specific container logs. 

show docker container log in a real time

docker logs --follow docker_container_id

'--follow' a full option name; or for a short, just '-f'. this option will tail the latest logs on the screen in a real time.

Docker Network

Docker network is another docker concept.  When deploying more than one container on the isolated docker network, containers can talk with others directly by using container names, without localhost, port number, etc.  

docker network ls : list all docker networks.

docker network create new-network-name: create a new docker network

Docker Compose

it is a tool to define and instruct how running several docker containers together.

version: '3'

services:

  web:

    image: nginx:latest

    ports:

      - "8080:80"

    depends_on:

      - db

  db:

    image: mysql:5.7

    environment:

      MYSQL_ROOT_PASSWORD: examplepassword

      MYSQL_DATABASE: testdb

      MYSQL_USER: user

      MYSQL_PASSWORD: userpassword

    volumes:

      - db-data:/var/lib/mysql

volumes:

  db-data:

Run Apache2 within Ubuntu container

now we run the Ubuntu container and mapping Apache2 80 to the Local system

dcoker run -it -p 80:80 -d ubuntu

-p: port

Apache2 normally working on port 80; 

Apache2 will be running inside a container on the top of the docker, which resides on the local OS. 

So we need to map the port of this container onto my local system. 

now go inside the container

docker exec -it <container-id>

apt-get update

checking if Apache2 is running

service apache2 status

start the Apache2

service apache2 start

Run Postgres from Docker Container

Click the link above. 

Initialization scripts

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

Difference between Docker and VM

To fully understand Docker, we also need to talk about the difference between Docker and a Virtual Machine (VM). The latter often runs in cloud environments like AWS and Azure. Whenever you create a VM you are sharing the hardware with others and other VMs. What these cloud environments are doing is 'virtualise' the hardware. Docker doesn't do that and does it differently. In a VM you can have multiple Operating Systems running on the same hardware, whilst with Docker, you virtualise the Operating System. Therefore, the big difference between VMs and Docker containers is that the former can have multiple (Guest) Operating Systems on the same hardware, through for example VMWare (which is called a Hypervisor). when you install Docker, you are going to use the Docker Engine to create isolated entities on the OS. These entities are called containers. Docker, therefore, allows you to automate the deployment of applications in these containers.

run mySql from container

docker run --name=testMySql -e MYSQL_ROOT_PASSWORD=test -p 3306:3306 -d mysql:latest 

sudo docker exec -it testMySql bash 

bash-4.4# mysql -u root -p

mysql> show databases;

docker run --name=testMySql -e MYSQL_ROOT_PASSWORD=test -p 3306:3306 -d mysql:5.7.39