Sqlite3

Sqlite3 is a light-weighted database server. It comes directly with Django.

Here is an introduction to its use: http://www.sqlite.org/sessions/sqlite.html

Some common commands:

- to enter sqlite3 shell: sqlite3 [sqlite3 db filename]
- show all databases: .databases
- show all tables: .tables
- query a table: select * from sqlite_master;
- show all table schema: .schema
- backup database to another file: .backup another_db.db
- exit shell: .q, or .exit

Note that there is only 1 database per database file in Sqlite3. So there is no command like "use database1".

Some notes:

- For sqlite3 database to be writable, it should be writable by the web account, its containing folder should also be writable by the web account.

- If a column in missing in a table, you can do this to add it:
sqlite3 yourdb.db
> drop table bookmarks_bookmark;
> .quit
cd yourpythonproj
python manage.py syncdb
python manage.py runserver

Basically, syncdb cannot change integrate schema once the tables are created. You have to drop that table first (or maybe need to remove the database totally, which can be done by mv the current db.sqlite3 to db.sqlite3.bak and do syncdb).

shell script to concat all files

This shell script runs on *nix and concatenates all files into a single one, including file name before file content. This is useful, for example, when you want to print out all files to read.

#!/bin/bash

#
# Dump multiple files to one text file.
# By: HomeTom. 3/11/2015
#

cd my_dir;  # go to the target file directory.

ct=0;  # keep a counter of file.

for i in README.md manage.py my_dir/*.py;
do
    ct=$(($ct + 1));
    echo;
    echo == File $ct: "$i" ==;  # print file name and its counter.
    echo;
    cat "$i";
done

This second shell script is improved from the above, by adding a table of contents (a list of all files) to the beginning of output.

#!/bin/bash

#
# Dump multiple files to one text file.
# By: HomeTom. 3/11/2015
#

cd my_dir;  # go to target file directory.

# define file list.
arr=(README.md manage.py my_dir/*.py);

# 1) get table of contents.
echo Table of Contents;
echo;
ct=0;  # keep a counter of file.
for i in ${arr[@]};
do
    ct=$(($ct + 1));
    echo == File $ct: "$i";  # print file name and its counter.
done

# 2) get file content.
ct=0;  # keep a counter of file.
for i in ${arr[@]};
do
    ct=$(($ct + 1));
    echo;
    echo == File $ct: "$i" ==;  # print file name and its counter.
    echo;
    cat "$i";
done

Django framework

To tell if you have Django installed with Python:
$ python -c "import django; print(django.get_version())"

Setup Django on bluehost:

[1] Python 2.7 + Django 1.4 on Bluehost
[2] Django on Bluehost in Five Minutes
[3] Django documentation
[4] Django documentation - tutorial

To access database using Django, see:
[5] Writing your first Django app, part 1 - create project and model (database)
[6] Writing your first Django app, part 2 - create admin site superuser
      basically it's by command: $ python manage.py createsuperuser

== Appendix 1. Python 2.7 + Django 1.4 on Bluehost ==

To avoid loss of previous web page, the basic process [1] is copied here:
Monday, April 30, 2012
Python 2.7 + Django 1.4 on Bluehost

Bluehost is a cheap shared hosting provider, that allows to run applications using fastCGI, among others, webapps created with django - python web framework. In this post you will find information how to install the newest (in April 2012) versions of python and django and how to configure them for  bluehost.

First you need to enable ssh access to your bluehost account. Sign in to their control panel, enter Security > SSH/Shell Access and click Manage SSH Access button. Select SSH Access enabled and submit. Now connect via ssh to your bluehost account.

Python
Download, extract and install python 2.7 in your home directory:
mkdir python27
wget http://www.python.org/ftp/python/2.7.2/Python-2.7.2.tgz
tar xzvf Python-2.7.2.tgz
cd Python-2.7.2
./configure -prefix=/homeX/your_username/python27 --enable-unicode=ucs4
make
make install

Rename the python binary (to avoid overriding current system python version - if you're not using default bluehost python for anything else you can skip this step):
mv ~/python27/bin/python ~/python27/bin/python27
Add new python directory with binaries to your PATH environment variable. To do this edit .bashrc file in your HOME directory, for example with vim:
vim ~/.bashrc
and add the line at the end of the .bashrc file:
PATH=/homeX/your_username/python27/bin:$PATH
load the new settings:
source .bashrc
and then test it:
python27
You should see something similar to:
Python 2.7.2 (default, Apr 11 2012, 01:29:09)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-52)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>

New python is available for you. Remember to use command python27 instead of python from now on.

Pip
We're going to use pip here - easy to use python package manager. It requires setuptools before it can be installed:

wget http://pypi.python.org/packages/source/s/setuptools/setuptools-0.6c11.tar.gz
tar xzvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python27 setup.py install
cd
wget http://pypi.python.org/packages/source/p/pip/pip-1.1.tar.gz
tar xzvf pip-1.1.tar.gz
cd pip-1.1
python27 setup.py install

Now thanks to our earlier PATH settings pip is available from command line.

Django installation
With pip you can install latest stable Django (in my case 1.4) simply by entering:
pip install Django
Wait for download and installation process to complete. After that django is installed and command django-admin.py available. You will also probably need python driver for mysql database. Install it with pip:
pip install MySQL-python
If you're going to use PosgreSQL install psycopg2 library:
pip install psycopg2

Django configuration
Create django project:
django-admin.py startproject myproject

Decide what url should be used to access your django app. In my case it will be subdirectory:
http://somedomain.com/myproject

Prepare directory in bluehost public_html:
mkdir public_html/myproject
cd public_html/myproject


In this directory create fastcgi file (for example: mysite.fcgi) with content:
#!/homeX/your_username/python27/bin/python27
import sys, os

# Add a custom Python path.
sys.path.insert(0, "/homeX/your_username/python27")
sys.path.insert(13, "/homeX/your_username/myproject")

os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
from django.core.servers.fastcgi import runfastcgi
runfastcgi(method="threaded", daemonize="false")

Remember to change /homeX/your_username to your home directory path on bluehost. Change myproject.settings to correct project name. The first line contains path to your custom python binary.

Django fastcgi requires flup so install it with pip:
pip install flup
Also change file permissions to mysite.fcgi:
chmod 0755 mysite.fcgi

Now create .htaccess file in the public_html/myproject directory with content:
AddHandler fcgid-script .fcgi
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^(.*)$ mysite.fcgi/$1 [QSA,L]


That's all. You should see your django start page on: http://somedomain.com/myproject. You can start coding some real stuff in django now. Remember to install every python and django packages using pip - that way they will be available to your django app. Also keep in mind that every django commands like syncdb, collectstatic  etc. should be run using python27 for example:
python27 manage.py syncdb


== Appendix 2. Python/Django setup on Mac ==

-- Intall wget on Mac --

Note, on Mac, to install wget, you need to download from http://ftp.gnu.org/gnu/wget/, and then do:
./configure --with-ssl=openssl
make
make install

See:
[5] http://coolestguidesontheplanet.com/install-and-configure-wget-on-os-x/

-- Install setuptools --

For more recent version of setup tools, follow instruction at:
[6] https://pypi.python.org/pypi/setuptools

You may need superuser privilege:
wget https://bootstrap.pypa.io/ez_setup.py -O - | sudo python

-- Install mysql-python --

When install mysql-python, it may report "EnvironmentError: mysql_config not found". See
[7] http://stackoverflow.com/questions/25459386/mac-os-x-environmenterror-mysql-config-not-found
In that case, do:

> locate mysql_config
# this should report something like: /usr/local/mysql-5.5.18-osx10.6-x86_64/bin/mysql_config.
# then add it to current path:
> PATH=$PATH:/usr/local/mysql-5.5.18-osx10.6-x86_64/bin/
> mysql_config
# this should show that mysql_config is available. Finally:
> sudo pip install mysql-python

-- Install mod_fcgid --

On mac, you may need to install mod_fcgid. Without mod_fcgid, will get this error:
/mysite.fcgi/ was not found on this server.
E.g.: http://stackoverflow.com/questions/24446496/deploying-django-error-mysite-fcgi-was-not-found-on-this-server-in-shared

To install mod_fcgid, you need to install macport first. See:
[8] http://www.dionysopoulos.me/apache-mysql-php-server-on-mac-os-x-with-multiple-simultaneous-php-versions/

To install macport, go to: http://www.macports.org/install.php. Use "pkg" installer.
Then do:
sudo port selfupdate
sudo port install mod_fcgid
sudo cp /opt/local/apache2/modules/mod_fcgid.so /usr/libexec/apache2

Then: sudo vi /etc/apache2/httpd.conf

Find all the LoadModule lines. After the last one add:
LoadModule fcgid_module libexec/apache2/mod_fcgid.so

Then restart apache:
sudo apachectl restart


== Appendix 3. Use Python in the web ==

Use Python in the web: https://docs.python.org/2/howto/webservers.html

Save code below as a.fcgi. This can run as fastcgi.

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

from cgi import escape
import sys, os
from flup.server.fcgi import WSGIServer

def app(environ, start_response):
    start_response('200 OK', [('Content-Type', 'text/html')])

    yield 'Use Python in the web: https://docs.python.org/2/howto/webservers.html'
    yield '<h1>FastCGI Environment</h1>'
    yield '<table>'
    for k, v in sorted(environ.items()):
         yield '<tr><th>%s</th><td>%s</td></tr>' % (escape(k), escape(v))
    yield '</table>'

WSGIServer(app).run()


== Appendix 4. Install rest_framework ==

sudo pip install djangorestframework

怎样尊重一个程序员

怎样尊重一个程序员 (from http://www.yinwang.org)

Why I left Google.

Replace failed RAID controller

OK, here is some experience on building a RAID server and fixing hardware failure. This is quite interesting as software engineers do not often have the chance to work on hardware.

The server uses PERC 6/E card to connect to a disk array. The disks in the disk array have the same physical capacity, say 1TB, but configured as logical drives with large capacity each (which effectively means you combine several physical drives into one logical drive).  PERC stands for PowerEdge RAID Controller. "E" in 6/E stands for External, which means HDDs are in a expansion box separate from the server box, attached to server using a SCSI interface such as MD1000 or MD3000. Correspondingly "I" in 6/I means "Internal", where HDDs are in the same server box. PowerEdge is a server brand of Dell. Relevant servers are listed on this wiki page: List of Dell PowerEdge Servers.

[1] is a very good step by step guide on how to do the configuration on a PERC 6/I card. It is the same for configuring a PERC 6/E card. [2] is a more general and complete guide.

There are several things one can do.

At boot time, press CTRL-R to enter PERC configuration utility. Once in the configuration utility, you can see a list of PERC cards, click "enter" on one to enter its configuration. In the next page "VD Mgmt", press "F2" for actions to choose from, including: Create New VD, Clear Config, Foreign Config (Import/Clear).

In the case the PERC card failed, the speed to access PERC configurations can be painfully slow each operation. Once the new good card is replaced on board, all steps are very much faster. The difference is like 10-30 minutes versus a few seconds.

PERC configuration is written to each RAID disk. In case of a PERC failure, just replace the PERC card and "import" foreign configuration. A configuration is "foreign" means the PERC configuration does not match configuration information on the RAID disks. When a PERC card fails its configuration may become inconsistent with information on the disks, and you may see warning lights on the disks blinking and the status of the lights is inconsistent upon each reboot.

If 1 or 2 disks in array failed, you just pull it out and insert a new disk. RAID should rebuild automatically.

If a large number of disks are in foreign state, you may want to clear foreign configuration and recreate from scratch. Note that for new setup you can "Initialize" the disks to clean them, but for disks with existing data (in the case of recovering from a failed PERC card, where disks with data should keep intact) you should not initialize the disks since you want to preserve the data.

If you need to create new VD (Virtual Disks), you need to choose RAID level, then assign disks to use, and set some options.

It's actually quite a lot of fun.

[1] DELL Tutorial: Create RAID Using PERC 6/i Integrated BIOS Configuration Utility
[2] Dell™ PERC 6/i, PERC 6/E and CERC 6/i User's Guide

Redis better than memcache?

- Why Redis beats Memcached for caching

Memcached: good when 1) static/small data, 2) better horizontal scaling

Redis: good when:
1) more data structure support
2) faster for more clients
3) larger object size limit (512MB v.s. 1MB of memcached)
4) stored data can be manipulated, not opaque
5) more control over LRU policies (6)
6) tunable persistence
7) offers replication

- On Redis, Memcached, Speed, Benchmarks and The Toilet
  Redis: ~ 80-100k GET/SET per sec, better for more clients.
  Memcached: ~ 60-80k GET/SET per sec

- An update on the Memcached/Redis benchmark

  Memcached: 1 instance running multiple threads, overhead causes less speed per thread.

  Redis: single thread. Can run multiple instances together.

Detach a series of databases in MSSQL

declare CUR cursor for
    SELECT name
    FROM master..sysdatabases
    where name like 'ABC_%'

declare @db varchar(50)
declare @msg varchar(200)

open CUR

fetch next from CUR into @db
while @@FETCH_STATUS = 0
begin
    set @msg = 'detach ' + @db
    raiserror (@msg, 0, 1) with nowait
   
    exec sp_detach_db @db, 'true'
   
    fetch next from CUR into @db
end

close CUR
deallocate CUR


---- Below is batch script to quickly remove disk files. Note you can use multiple wild card match. ----

ECHO OFF

FOR /f "tokens=*" %%i in ('DIR /a:d /b D:\mssql_db\*abc* D:\mssql_db\*xyz*') DO (
    ECHO %%i
    rmdir /s /q "D:\mssql_db\%%i"
)