SQLite format Output

The following commands can be used to get formatted output from the sqlite3 shell program.

• .header(s) ON|OFF
• .mode
• .output
• .prompt
• .separator
• .show
• .width

Following are the short help strings from the command prompt for the above commands.

Code Listing 1. Short descriptions of output formatting commands from SQLite shell

.header(s) ON|OFF Turn display of headers on or off

.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML

code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements

.nullvalue STRING Print STRING in place of NULL values

.output FILENAME Send output to FILENAME

.output stdout Send output to the screen

.prompt MAIN CONTINUE Replace the standard prompts

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.width NUM NUM ... Set column widths for "column" mode

The Default Output

As you can see in the listing below, the formatting commands will be executed on a simple database with contacts and addresses.

Code Listing 2. A simple db with contacts and addresses

D:\Research\sqlite\sqlite-3_5_7>sqlite3.exe contactsext.db
SQLite version 3.5.7
Enter ".help" for instructions


sqlite> .tables
Address Contacts


sqlite> .schema
CREATE TABLE Address (_id INTEGER PRIMARY KEY, city TEXT, country TEXT, line1 TE
XT, line2 TEXT, region TEXT);
CREATE TABLE Contacts (title TEXT, email TEXT, _id INTEGER PRIMARY KEY, addrid N
UMERIC, name TEXT);


sqlite> select * from contacts;
Chairman|billg@microsoft.com|1|1|Bill Gates
CEO|steve@apple.com|2|2|Steve Jobs
Senator|hillary@senate.us.gov|3|3|Hillary Clinton
Senator|mccain@senate.us.gov|4|3|John McCain
Senator|obama@senate.us.gov|5|3|Barack Obama


sqlite> select * from address;
1|Seattle|USA|A Street||Washington
2|California|USA|B Street||Cupertino
3|Washington|USA|K Street||DC

If you look at the output from contacts and address, the output is not very pretty. It’s barely readable. And, it would be hard to understand the data without the columns (especially if the tables are non-obvious unlike contacts and address).

The Current Settings

We can see the current settings by using the command .show. Since no changes were made to the settings, these are the default settings.

Code Listing 3. The Default Settings

sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:

Changing to Column Mode

The mode by default is list. Using the .mode command, lot of formatting can be accomplished. Setting the display to column, you will see a spread-out column display for the results.

Code Listing 4. The Column Display

sqlite> .mode column


sqlite> .show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:


sqlite> select * from contacts;
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.g 3 3 Hillary Cl
Senator mccain@senate.us.go 4 3 John McCai
Senator obama@senate.us.gov 5 3 Barack Oba

The Column Headers

In the above output, we don’t know the column names. For obvious tables like contacts, you probably don’t need column names, but in general they will be helpful. The command ‘.headers on’ would turn on the headers.

Code Listing 4. Turning the headers on

sqlite> .headers on


sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:


sqlite> select * from contacts;
title email _id addrid name
---------- ------------------- ---------- ---------- ----------
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.g 3 3 Hillary Cl
Senator mccain@senate.us.go 4 3 John McCai
Senator obama@senate.us.gov 5 3 Barack Oba

The first line from the results to the SELECT statement is the listing of columns. There is one more problem – the output (email and names) is truncated beyond the tenth character.

Width of the columns

Using the .width command, you can adjust the width of the columns so that you can see more of the data. Separate the width of the columns (in number of characters) by a space.

Code Listing 5. Specifying the width of the columns

sqlite> .width 10 25 3 6 15


sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width: 10 25 3 6 15


sqlite> select * from contacts;
title email _id addrid name
---------- ------------------------- --- ------ ---------------
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.gov 3 3 Hillary Clinton
Senator mccain@senate.us.gov 4 3 John McCain
Senator obama@senate.us.gov 5 3 Barack Obama

As shown in the above listing, you can see that the column headers are there, all the data is shown in a column fashion.

Other Options in Mode

The command .mode is pretty powerful. Setting it to csv will give you the results in a ‘Comma Separated Values’ format – which is good for Excel, etc. You can also generate HTML code by setting the mode to html. You can get the columns on a line by choosing line. Or separate the values by tabs. You can also generate insert statements.

Code Listing 6. Various modes of data display

sqlite> .mode csv
sqlite> select * from contacts;
title,email,_id,addrid,name
Chairman,billg@microsoft.com,1,1,"Bill Gates"
CEO,steve@apple.com,2,2,"Steve Jobs"
Senator,hillary@senate.us.gov,3,3,"Hillary Clinton"
Senator,mccain@senate.us.gov,4,3,"John McCain"
Senator,obama@senate.us.gov,5,3,"Barack Obama"


sqlite> .mode html
sqlite> select * from contacts;

sqlite> .mode line
sqlite> select * from contacts;
title = Chairman
email = billg@microsoft.com
_id = 1
addrid = 1
name = Bill Gates

title = CEO
email = steve@apple.com
_id = 2
addrid = 2
name = Steve Jobs

title = Senator
email = hillary@senate.us.gov
_id = 3
addrid = 3
name = Hillary Clinton

title = Senator
email = mccain@senate.us.gov
_id = 4
addrid = 3
name = John McCain

title = Senator
email = obama@senate.us.gov
_id = 5
addrid = 3
name = Barack Obama


sqlite> .mode tab
sqlite> select * from contacts;
title email _id addrid name
Chairman billg@microsoft.com 1 1 Bill Gates
CEO steve@apple.com 2 2 Steve Jobs
Senator hillary@senate.us.gov 3 3 Hillary Clinton
Senator mccain@senate.us.gov 4 3 John McCain
Senator obama@senate.us.gov 5 3 Barack Obama

sqlite> .mode insert
sqlite> select * from contacts;
INSERT INTO table VALUES(’Chairman’,’billg@microsoft.com’,1,1,’Bill Gates’);
INSERT INTO table VALUES(’CEO’,’steve@apple.com’,2,2,’Steve Jobs’);
INSERT INTO table VALUES(’Senator’,’hillary@senate.us.gov’,3,3,’Hillary Clinton’
);
INSERT INTO table VALUES(’Senator’,’mccain@senate.us.gov’,4,3,’John McCain’);
INSERT INTO table VALUES(’Senator’,’obama@senate.us.gov’,5,3,’Barack Obama’);

title	email	_id	addrid	name
Chairman	billg@microsoft.com	1	1	Bill Gates
CEO	steve@apple.com	2	2	Steve Jobs
Senator	hillary@senate.us.gov	3	3	Hillary Clinton
Senator	mccain@senate.us.gov	4	3	John McCain
Senator	obama@senate.us.gov	5	3	Barack Obama

Box-shadow, one of CSS3′s best new features

Box-shadow, one of CSS3′s best new features

box-shadow property allows designers to easily implement multiple drop shadows (outer or inner) on box elements, specifying values for color, size, blur and offset.

Browser support is growing of late with Mozilla (Firefox), Webkit (Safari/Chrome/Konqueror), Opera and the IE9 Platform Preview all offering a decent implementation of the spec, although Mozilla and Webkit still require their respective -moz- and -webkit- prefixes (note Mozilla Firefox 4.0+ no longer requires the -moz- prefix).

Here’s a basic example:

Firefox, Safari/Chrome, Opera and IE9 users should see a grey fading shadow under this box.

In theory, the code for this is straightforward:

#example1 {
box-shadow: 10px 10px 5px #888;
}

But for the moment, as with many other ‘experimental’ CSS3 properties, you’ll need to use the following prefixes to support Mozilla and Webkit:

#example1 {
-moz-box-shadow: 10px 10px 5px #888;
-webkit-box-shadow: 10px 10px 5px #888;
box-shadow: 10px 10px 5px #888;
}

How it Works

The box-shadow property can accept a comma-serparated list of shadows, each defined by 2-4 length values (specifying in order the horizontal offset, vertical offset, optional blur distance and optional spread distance of the shadow), an optional color value and an optional ‘inset‘ keyword (to create an inner shadow, rather than the default outer shadow).

The Syntax:

box-shadow: none | <shadow> [ , <shadow> ]*

<shadow> = inset? && [ <length>{2,4} && <color>? ]

Examples:

box-shadow: 10px 10px;
box-shadow: 10px 10px 5px #888;
box-shadow: inset 2px 2px 2px 2px black;
box-shadow: 10px 10px #888, -10px -10px #f4f4f4, 0px 0px 5px 5px #cc6600;

Let’s first look at how to create a basic outer shadow, before going on to look at the inset keyword, layering multiple shadows and how to spice up your shadows with RGBa colors(?).

Creating a basic drop shadow

By default, shadows are drawn on the outside of elements. According to the specification;

An outer box-shadow casts a shadow as if the border-box of the element were opaque. The shadow is drawn outside the border edge only: it is clipped inside theborder-box of the element.

The first step is to define the shape of the shadow by specifying 2-4 length values.

The first value defines the horizontal offset of the shadow, with a positive value offseting the shadow to the right of the element, and a negative value to the left.

The second value defines the vertifical offset of the shadow, with a positive value offsetting the shadow from the bottom of the element, and a negative value from the top.

If supplied, an optional third value defines the blur distance of the shadow. Only positive values are allowed, and the larger the value, the more the shadow’s edge is blurred. The specification does not include an exact algorithm for how the blur distance should be calculated, however it does elaborate as follows:

…for a long, straight shadow edge, this should create a color transition the length of the blur distance that is perpendicular to and centered on the shadow’s edge, and that ranges from the full shadow color at the radius endpoint inside the shadow to fully transparent at the endpoint outside it.

An optional fourth value can be supplied to define the spread distance of the shadow. A positive value will cause the shadow shape to expand in all directions, while a negative value will cause the shadow shape to contract. The specification goes into much greater detail on how the shadow shape is calculated as follows:

If a spread distance is defined, the shadow is expanded outward or contracted inward by an operation equivalent to applying twice the absolute value of the spread value to a blur operation as defined below and thresholding the result such that for a positive spread distance all non-transparent pixels are given the full shadow color and for a negative spread distance all non-opaque pixels are made transparent. The UA may approximate this operation by taking an outward outset of the specified amount normal to the original shadow perimeter. Alternatively the UA may approximate the transformed shadow perimeter shape by outsetting (insetting, for inner shadows) the shadow’s straight edges by the spread distance and increasing (decreasing, for inner shadows) and flooring at zero the corner radii by the same amount. (The UA may even combine these methods, using one method for outer shadows and another for inner ones.) For corners with a zero border-radius, however, the corner must remain sharp—the operation is equivalent to scaling the shadow shape. In any case, the effective width and height of the shadow shape is floored at zero. (A zero-sized shadow shape would cause an outer shadow to disappear, and an inner shadow to cover the entire padding-box.)

The diagram below (taken from the W3C Backgrounds and Borders Candidate Recommendation) offers a good example of the effects of spread and blur on the shadow:

An optional color value can also be supplied, directly after the 2-4 length values, to define the shadow’s color. If not supplied, a UA-chosen default should be applied, however, whilst in Firefox/Opera/IE9 the default color is black, in Safari/Chrome (webkit) no shadow is visible unless a color is specified.

Here are a few examples of shadows with differing offsets, spread and blur.

Examples:

A

B

C

D

E

F

Example A shows a shadow offset to the left and top by 5px:

#Example_A {
-moz-box-shadow: -5px -5px #888;
-webkit-box-shadow: -5px -5px #888;
box-shadow: -5px -5px #888;
}

Example B shows the same shadow with a blur distance of 5px:

#Example_B {
-moz-box-shadow: -5px -5px 5px #888;
-webkit-box-shadow: -5px -5px 5px #888;
box-shadow: -5px -5px 5px #888;
}

Example C shows the same shadow with a spread distance of 5px:

#Example_C {
-moz-box-shadow: -5px -5px 0 5px #888;
-webkit-box-shadow: -5px -5px 0 5px#888;
box-shadow: -5px -5px 0 5px #888;
}

Example D shows the same shadow with both a blur distance of 5px and a spread distance of 5px:

#Example_D {
-moz-box-shadow: -5px -5px 5px 5px #888;
-webkit-box-shadow: -5px -5px 5px 5px#888;
box-shadow: -5px -5px 5px 5px #888;
}

Example E shows a shadow with no offset and a blur distance of 5px:

#Example_E {
-moz-box-shadow: 0 0 5px #888;
-webkit-box-shadow: 0 0 5px#888;
box-shadow: 0 0 5px #888;
}

Example F shows a shadow with no offset and both a blur distance of 5px and a spread distance of 5px:

#Example_F {
-moz-box-shadow: 0 0 5px 5px #888;
-webkit-box-shadow: 0 0 5px 5px#888;
box-shadow: 0 0 5px 5px #888;
}

Creating an inner shadow with the ‘inset’ keyword

An optional ‘inset‘ keyword can be supplied, preceding the length and color values. If present, this keyword causes the shadow to be drawn inside the element. According to the specification:

An inner box-shadow casts a shadow as if everything outside the padding edge were opaque. The inner shadow is drawn inside the padding edge only: it is clipped outside the padding box of the element.

Here are the same examples as above, this time with the ‘inset‘ keyword present.

Examples:

G

H

I

J

K

L

Example G shows an inner shadow offset to the left and top by 5px:

#Example_G {
-moz-box-shadow: inset -5px -5px #888;
-webkit-box-shadow: inset -5px -5px #888;
box-shadow: inset -5px -5px #888;
}

Example H shows the same inner shadow with a blur distance of 5px:

#Example_H {
-moz-box-shadow: inset -5px -5px 5px #888;
-webkit-box-shadow: inset -5px -5px 5px #888;
box-shadow: inset -5px -5px 5px #888;
}

Example I shows the same inner shadow with a spread distance of 5px:

#Example_I {
-moz-box-shadow: inset -5px -5px 0 5px #888;
-webkit-box-shadow: inset -5px -5px 0 5px#888;
box-shadow: inset -5px -5px 0 5px #888;
}

Example J shows the same inner shadow with both a blur distance of 5px and a spread distance of 5px:

#Example_J {
-moz-box-shadow: inset -5px -5px 5px 5px #888;
-webkit-box-shadow: inset -5px -5px 5px 5px#888;
box-shadow: inset -5px -5px 5px 5px #888;
}

Example K shows an inner shadow with no offset and a blur distance of 5px:

#Example_K {
-moz-box-shadow: inset 0 0 5px #888;
-webkit-box-shadow: inset 0 0 5px#888;
box-shadow: inner 0 0 5px #888;
}

Example L shows an inner shadow with no offset and both a blur distance of 5px and a spread distance of 5px:

#Example_L {
-moz-box-shadow: inset 0 0 5px 5px #888;
-webkit-box-shadow: inset 0 0 5px 5px#888;
box-shadow: inset 0 0 5px 5px #888;
}

Layering multiple shadows

The box-shadow property allows elements to have multiple shadows, specified by a comma seperated list. When more than one shadow is specified, the shadows are layered front to back, as in the following example.

Example:

M

Example M shows five shadows specified in the following order; firstly a black shadow with a spread distance of px and a blur distance of px, secondly a lime shadow offset to the top right, thirdly a red shadow offset to the bottom right with a blur distance applied, fourthly a yellow shadow offset to the bottom left, and lastly a blue shadow offset to the top left with a blur distance applied:

#Example_M {
-moz-box-shadow: 0 0 10px 5px black, 40px -30px lime, 40px 30px 50px red, -40px 30px yellow, -40px -30px 50px blue;
-webkit-box-shadow: 0 0 10px 5px black, 40px -30px lime, 40px 30px 50px red, -40px 30px yellow, -40px -30px 50px blue;
box-shadow: 0 0 10px 5px black, 40px -30px lime, 40px 30px 50px red, -40px 30px yellow, -40px -30px 50px blue;
}

Spicing up shadows with RGBa color & border-radius

The box-shadow property can be further enhanced using CSS3 RGBa colors to create shadows with differing levels of opacity, as demonstrated by the examples below.

Examples:

N

O

P

Example N shows a black shadow, specified using standard RGB color, offset to the right and bottom by 5px:

#Example_N {
-moz-box-shadow: 5px 5px rgb(0,0,0);
-webkit-box-shadow: 5px 5px rgb(0,0,0);
box-shadow: 5px 5px rgb(0,0,0);
}

Example O shows the same shadow, this time with the color black specified using RGBa color with an opacity of 70%:

#Example_O {
-moz-box-shadow: 5px 5px rgba(0,0,0,0.7);
-webkit-box-shadow: 5px 5px rgba(0,0,0,0.7);
box-shadow: 5px 5px rgba(0,0,0,0.7);
}

Example P shows the same shadow, this time with the color black specified using RGBa color with an opacity of 50%:

#Example_P {
-moz-box-shadow: 5px 5px rgba(0,0,0,0.5);
-webkit-box-shadow: 5px 5px rgba(0,0,0,0.5);
box-shadow: 5px 5px rgba(0,0,0,0.5);
}

The box-shadow property can also be applied to elements with rounded corners, created using theborder-radius property, in which case the shadow will follow the curve specified by the border-radius property (note: although IE9 seems to struggle with this).

Examples:

Q

R

Example Q shows a shadow offset to the bottom and right by 5px, with a border-radius of 5px applied to each corner:

#Example_Q {
-moz-border-radius: 5px;
border-radius: 5px;
-moz-box-shadow: 5px 5px black;
-webkit-box-shadow: 5px 5px black;
box-shadow: 5px 5px black;
}

Example R shows the same shadow with a blur distance of 5px:

#Example_R {
-moz-border-radius: 5px;
border-radius: 5px;
-moz-box-shadow: 5px 5px 5px black;
-webkit-box-shadow: 5px 5px 5px black;
box-shadow: 5px 5px 5px black;
}

Browser Support

The box-shadow property has not caught on as quickly as some of its peers (such as the border-radius property) and the property was removed from the CSS3 Backgrounds and Borders specification when it reached Candidate Recommendation earlier this year, pending further development, however the property has been reintroduced in the latest version of the specification and browser support has been growing steadily of late.

R Language Quick Tips

Built-in Functions

Almost everything in R is done through functions. Here I'm only refering to numeric and character functions that are commonly used in creating or recoding variables.

NUMERIC FUNCTIONS

Function	Description
abs(x)	absolute value
sqrt(x)	square root
ceiling(x)	ceiling(3.475) is 4
floor(x)	floor(3.475) is 3
trunc(x)	trunc(5.99) is 5
round(x, digits=n)	round(3.475, digits=2) is 3.48
signif(x, digits=n)	signif(3.475, digits=2) is 3.5
cos(x), sin(x), tan(x)	also acos(x), cosh(x), acosh(x), etc.
log(x)	natural logarithm
log10(x)	common logarithm
exp(x)	e^x

CHARACTER FUNCTIONS

Function	Description
substr(x, start=n1, stop=n2)	Extract or replace substrings in a character vector. x <- "abcdef" substr(x, 2, 4) is "bcd" substr(x, 2, 4) <- "22222" is "a222ef"
grep(pattern, x , ignore.case=FALSE, fixed=FALSE)	Search for pattern in x. If fixed =FALSE then pattern is aregular expression. If fixed=TRUE then pattern is a text string. Returns matching indices. grep("A", c("b","A","c"), fixed=TRUE) returns 2
sub(pattern,replacement, x, ignore.case =FALSE, fixed=FALSE)	Find pattern in x and replace with replacement text. If fixed=FALSE then pattern is a regular expression.If fixed = T then pattern is a text string. sub("\\s",".","Hello There") returns "Hello.There"
strsplit(x, split)	Split the elements of character vector x at split. strsplit("abc", "") returns 3 element vector "a","b","c"
paste(..., sep="")	Concatenate strings after using sep string to seperate them. paste("x",1:3,sep="") returns c("x1","x2" "x3") paste("x",1:3,sep="M") returns c("xM1","xM2" "xM3") paste("Today is", date())
toupper(x)	Uppercase
tolower(x)	Lowercase

STATISTICAL PROBABILITY FUNCTIONS

The following table describes functions related to probaility distributions. For random number generators below, you can use set.seed(1234) or some other integer to create reproducible pseudo-random numbers.

Function	Description
dnorm(x)	normal density function (by default m=0 sd=1) # plot standard normal curve x <- pretty(c(-3,3), 30) y <- dnorm(x) plot(x, y, type='l', xlab="Normal Deviate", ylab="Density", yaxs="i")
pnorm(q)	cumulative normal probability for q (area under the normal curve to the right of q) pnorm(1.96) is 0.975
qnorm(p)	normal quantile. value at the p percentile of normal distribution qnorm(.9) is 1.28 # 90th percentile
rnorm(n, m=0,sd=1)	n random normal deviates with mean m and standard deviation sd. #50 random normal variates with mean=50, sd=10 x <- rnorm(50, m=50, sd=10)
dbinom(x, size, prob) pbinom(q, size, prob) qbinom(p, size, prob) rbinom(n, size, prob)	binomial distribution where size is the sample size and prob is the probability of a heads (pi) # prob of 0 to 5 heads of fair coin out of 10 flips dbinom(0:5, 10, .5) # prob of 5 or less heads of fair coin out of 10 flips pbinom(5, 10, .5)
dpois(x, lamda) ppois(q, lamda) qpois(p, lamda) rpois(n, lamda)	poisson distribution with m=std=lamda #probability of 0,1, or 2 events with lamda=4 dpois(0:2, 4) # probability of at least 3 events with lamda=4 1- ppois(2,4)
dunif(x, min=0, max=1) punif(q, min=0, max=1) qunif(p, min=0, max=1) runif(n, min=0, max=1)	uniform distribution, follows the same pattern as the normal distribution above. #10 uniform random variates x <- runif(10)

OTHER STATISTICAL FUNCTIONS

Other useful statistical functions are provided in the following table. Each has the option na.rm to strip missing values before calculations. Otherwise the presence of missing values will lead to a missing result. Object can be a numeric vector or dataframe.

Function	Description
mean(x, trim=0, na.rm=FALSE)	mean of object x # trimmed mean, removing any missing values and # 5 percent of highest and lowest scores mx <- mean(x,trim=.05,na.rm=TRUE)
sd(x)	standard deviation of object(x). also look at var(x) for variance and mad(x) for median absolute deviation.
median(x)	median
quantile(x,probs)	quantiles where x is the numeric vector whose quantiles are desired and probs is a numeric vector with probabilities in [0,1]. # 30th and 84th percentiles of x y <- quantile(x, c(.3,.84))
range(x)	range
sum(x)	sum
diff(x, lag=1)	lagged differences, with lag indicating which lag to use
min(x)	minimum
max(x)	maximum
scale(x, center=TRUE, scale=TRUE)	column center or standardize a matrix.

OTHER USEFUL FUNCTIONS

Function	Description
seq(from , to, by)	generate a sequence indices <- seq(1,10,2) #indices is c(1, 3, 5, 7, 9)
rep(x, ntimes)	repeat x n times y <- rep(1:3, 2) # y is c(1, 2, 3, 1, 2, 3)
cut(x, n)	divide continuous variable in factor with n levels y <- cut(x, 5)

Note that while the examples on this page apply functions to individual variables, many can be applied to vectors and matrices as well.