sqlite3

Create New database

In DOS,

sqlite3 test.db

Now we go to sqlite prompt

Help

sqlite> .help

List Tables

sqlite> .tables

Describe Table

sqlite> .schema TableName

For example,

sqlite> .schema SoundOption
CREATE TABLE SoundOption (isSoundOn int);

SELECT

sqlite> SELECT * FROM SoundOption;

UPDATE

sqlite> UPDATE SoundOption SET isSoundOn=1;

EXIT

sqlite> .exit

execute from bash

$ echo "select * from adstxt;" | sqlite3 adstxt.db
$echo "delete from adstxt;" | sqlite3 adstxt.db

Import sql file

sqlite3 adstxt.db < adstxt_crawler.sql

adstxt_crawler.sql

BEGIN TRANSACTION;
DROP TABLE IF EXISTS adstxt;

CREATE TABLE adstxt(
       SITE_DOMAIN                  TEXT    NOT NULL,
       EXCHANGE_DOMAIN              TEXT    NOT NULL,
       ADSYSTEM_DOMAIN		    INTEGER     NOT NULL,
       SELLER_ACCOUNT_ID            TEXT    NOT NULL,
       ACCOUNT_TYPE                 TEXT    NOT NULL,
       TAG_ID                       TEXT    NOT NULL,
       ENTRY_COMMENT                TEXT    NOT NULL,
       UPDATED                      DATE    DEFAULT (datetime('now','localtime')),
    PRIMARY KEY (SITE_DOMAIN,EXCHANGE_DOMAIN,SELLER_ACCOUNT_ID)
);

-- Contribution by Ian Trider
DROP TABLE IF EXISTS adsystem_domain;

CREATE TABLE "adsystem_domain" (
	DOMAIN	TEXT,
	ID	INTEGER,
	PRIMARY KEY(DOMAIN,ID)
);
INSERT INTO `adsystem_domain` VALUES ('adtech.com',11);
...

DROP TABLE IF EXISTS adsystem;
CREATE TABLE "adsystem" (
	ID	INTEGER,
	NAME	TEXT,
	CANONICAL_DOMAIN	TEXT,
	PRIMARY KEY(ID)
);
INSERT INTO `adsystem` VALUES (1,'Rubicon',NULL);
...
COMMIT;

ref : https://github.com/InteractiveAdvertisingBureau/adstxtcrawler

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s