An Intro on SQLite

SQLite is a Relational Database Management System (RDMS). but unlike other RDMS, it does not require a ceentral server to be run or client to access the process. and lightweight. due to its small size it is use in various application, Operating System, Embedded System etc. The best part of it, it is in Public Domain. Which mean any software maker, individual or software company can use and impliment it into their application. SQLite is use in various open source and closed source system.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
      - sqlite.org

SQLite is use in various software appication:
• Mozilla Firefox
• Google Chrome
• Opera
• Ruby On Rails
• Adobe System
and more..

SQLite is also use in various OS:
• NetBSD
• OpenBSD
• Apple iOS
• Google Android
• Windows Phone 8
• Symbian OS
• BlackBerry 10 OS
and more..

Despite it small size, it implement almost all SQL standard query. but few features are missing, like 'ALTER TABLE'.

---

A short tutorial

# Checking SQLite version:

sqlite3 -version

-- Loading resources from /home/user/.sqliterc

3.7.15.2 2013-01-09 11:53:05 c0e09560d26f0a6456be9dd3447f5311eb4f238f

# Getting Usage help:

sqlite3 -help

# Create a database 'foo.db' :

sqlite3 foo.db

This will create the foo.db (empty database) and drop to sqlite shell.

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

# To create a table, we will run an SQL query:

sqlite> CREATE TABLE person (name VARCHAR(12), age VARCHAR(3));

a table 'person' will be created with column name & age.

# Display all tables in the database:

sqlite> .tables

Or similar tables..

sqlite> .tables ??TABLE??

# Adding data to table:

sqlite> INSERT INTO person VALUES ('jack', '50');

sqlite> INSERT INTO person ('name') VALUES ('henry');

# Displaying the inserted data:

sqlite> SELECT * FROM person;

SQLite can also read SQL query from a text file.

sql.txt

select * from person;
select * from person where name='john';

# To run SQL query that is given in sql.txt .

sqlite> .read sql.txt

To manipulate any data in the databases the user will have to run SQL query. This author guess that the reader has prior knowledge of SQL query.

# To exit SQLite shell:

sqlite> .quit

sqlite> .exit

Disclaimer:
Tutorial above are only for educational purposes. The author will not take any liability for any damage cause, either software or hardware, by use, misuse or improper use of tutorial above.

update: 28th September 2013

---

more SQL tutorial

Let us build a database  'test' with table  'person' inside it, having column  name, age, gender. see how to do this

Insert a line into table:

INSERT INTO person (name, gender) VALUES ('omphu', 'female')

This will add a new line or data into the table  'person' on column  name, gender with value omphu, female respectively and will leave the value of age blank.

Select line using WHERE:

SELECT * FROM person WHERE gender='female'

Select specific column:

SELECT name FROM person WHERE gender='male'

This will output the value of column name where gender is female.

Specify more argument to refine search:

SELECT name, age FROM person WHERE age='21' AND gender='male'

Will output result if this two condition are true,  age & gender.

SELECT name, age FROM person where age='21' OR gender='female'

This will output result if one of the two condition is true,  age & gender.

Limit output to 5 result:

SELECT * FROM person LIMIT 5

Sort result by Ascending/Descending order:

SELECT * FROM person ORDER BY name ASC

SELECT * FROM person ORDER BY name DESC

Query name which contain the letter  a.

SELECT * FROM person WHERE name LIKE '%a%'

Query only unique name:

SELECT DISTINCT(name) FROM person

This query is useful when we have many duplicate data of the same value.

Count total line in the table:

SELECT COUNT(name) FROM person

Disclaimer:
Tutorial above are only for educational purposes. The author will not take any liability for any damage cause by use or improper use of tutorial above.

---

Basic SQL tutorial

INTRO:
SQL is a programming languages designed for managing data. It is used for data storage or database storage. MySQL is the most popular among database management storage system. Others are Oracle, Windows Access, PostgreSQL, SQLite etc. Database are use from Office productivity to storage of various data type.

This tutorial will cover all the basic of the above and more database system..

SQL look like this..

SELECT * FROM table

Create a database:

CREATE DATABASE test

This will create a database with name  'test'.

Create a table:

CREATE TABLE person ( `name` VARCHAR(12), `age` VARCHAR(3), `gender` VARCHAR(6) )

This will create a table with name  'person' having column  name, age, gender.

Add a data to table:

INSERT INTO person (name, age, gender) VALUES ('anil', '18', 'male')

This will add a line into the table  person  to the column  name, age, gender  with value  anil, 18, male  respectively.

Check if the data/line has been added:

SELECT * FROM person

Output:

name age gender

anil 18 male

Note:
* the number in  VARCHAR(12)  signify only 12 characters will be saved and rest omitted. If we enter a name  'verylonglongname' (16 char), the saved name will only be  'verylonglong' (12 char), last 4 char omitted.

Disclaimer:
Tutorial above are only for educational purposes. The author will not take any liability for any damage cause by use or improper use of tutorial above.

---

BACKUP FACEBOOK PROFILE

Facebook is the leading social network, there is no doubt. It has more than 1 billion registered user. There is hardly any one who had not heard (or using) it.

This tutorial is an example on how to backup/download 'facebook profile' (our own profile). Frustated at not finding a single tutorial on how to backup my user profile. i went ahead and created this tutorial. back to the point, we will use 'cURL' (and 'wget') for this purpose as it is available for almost all system.

Facebook data are available from 'https://graph.facebook.com'. Basic info like id, username, name, link, gender & locale are available publicily. Throw in an access token you have all data that is in public or visible to friends. Example below:

https://graph.facebook.com/jor.teron

Required:
* cURL or wget
* User ID (UID)
* facebook access_token
* Basic idea on FQL and FQL tables (optional)

This method work by invoking graph explorer...

https://graph.facebook.com/fql?q=FQL_QUERY&access_token=ACCESS_TOKEN

where  FQL_QUERY  is the FQL query implemented by facebook. If  FQL_QUERY  is..

select description from group where gid=XXXXXXXX

..then url will be like.

https://graph.facebook.com/fql?q=select+description+from+group+where+gid=XXXXXXXX&access_token=ACCESS_TOKEN

STEP TO FOLLOW:
1) Goto Graph API Explorer, https://developers.facebook.com/tools/explorer. Login if necessary. Click 'Get Access Token' button. Select appropriate permission. save it. Copy the access token (those jumbled alphabet).

2) Note down your UID.

3) Open 'Command Prompt' and type in the following.
for cURL:

curl --user-agent 'Firefox 10' \
--output file.txt \
"https://graph.facebook.com/fql?q=select+uid,username,name+from+user+where+uid=UID&access_token=ACCESS_TOKEN"

for wget:

wget --user-agent='Firefox 10' \
--output-document=file.txt \
"https://graph.facebook.com/fql?q=select+uid,username,name+from+user+where+uid=UID&access_token=ACCESS_TOKEN"

note:
* Replace UID and ACCESS_TOKEN with its respective values you got in step 2 and 1 respectively.
* ACCESS_TOKEN is valid only for 1 hour, so you will need to get it (again) later if needed.

In the above example it'll download User Profile (containing id, username, name) and save it to 'file.txt'. You can specify more column if needed.

To download photos:

curl --user-agent 'Firefox 10' \
--output photo.txt \
"https://graph.facebook.com/fql?q=select+src_big+from+photo+where+owner=UID&access_token=ACCESS_TOKEN"

Sort out photos links using grep, sed, awk or regex to download them. In these way one can download facebook data from profile to photos, comments to likes, pokes to messages etc.

If FQL intimate you, there is an easier way to fetch data by using Graph Search....

https://graph.facebook.com/USER?fields=GRAPH_OBJECT&access_token=ACCESS_TOKEN

Example:

https://graph.facebook.com/jor.teron?fields=id,username,name&access_token=ACCESS_TOKEN

This tutorial will benefit those who want to backup their facebook data. Alternatively if you find this process hard to follow you can use facebook own feature 'Copy Facebook'.

Download curl:
http://curl.haxx.se/download.html

FQL reference docs:
http://developers.facebook.com/docs/reference/fql/

Disclaimer:
Tutorial above are only for educational purposes. The author will not take any liability for any damage cause by use or improper use of tutorial above.

update: 18th April 2013

---