I am using SQLite3 for my primary database. SQLite has extremely simple schema that consists of one table called "sqlite_master". This program lets you see the schema of select databases on my server.
I will also open source the code for the site which you can browse with the Code Viewer
Available Databases | ||||
|---|---|---|---|---|
| ID | DB Name | Platform | File | |
| 1 | main | sqlite | main.db | |
| Holds global information for the site including User Database and Web_Site Description | ||||
| 2 | log | sqlite | log.db | |
| Holds large logging database for the site | ||||
| 3 | dir | sqlite | dir.db | |
| Contains files to create a link directory. | ||||
| 4 | cal | sqlite | cal.db | |
| Contains Information for the web site calendar. | ||||
| 5 | mstock | sqlite | mstock.db | |
| Contains Meta Information about Mirostock used on the site. | ||||
| 6 | code | sqlite | code.db | |
| Program used to display the code used on this open source web site. | ||||
| 7 | comm | sqlite | comm.db | |
| Database provides a model for analyzing a community | ||||
| 8 | msg | sqlite | msg.db | |
| Database contains messages from and to users | ||||
Connecting +log
| Tables in log | ||
|---|---|---|
| Table Name | Rows | DDL |
| Ad_Hit | 5450 | CREATE TABLE Ad_Hit (
ad_id INTEGER,
target_id INTEGER,
site_id INTEGER,
ip_id INTEGER,
domain_id INTEGER,
ts REAL);
CREATE INDEX Ad_Hit_IX
ON Ad_Hit (ad_id)
|
| Ajax_Hit | 2290 | CREATE TABLE Ajax_Hit (
aj_id INT,
session_id INT,
ip_id INT,
site_id INT,
rv_id INT,
ts REAL,
data_str TEXT,
old_str TEXT)
|
| Color_Scheme | 6 | CREATE TABLE Color_Scheme (
scheme_id INTEGER PRIMARY KEY,
owner_id INTEGER DEFAULT 0,
scheme_nm TEXT,
body_color TEXT,
bg_color TEXT,
txt_color TEXT,
th_bg_color TEXT,
th_txt_color TEXT,
row1_color TEXT,
row2_color TEXT,
prompt_color TEXT,
prompt_text_color TEXT,
cell_color TEXT,
menu_img TEXT DEFAULT '/images/bar40.gif',
th_link_color TEXT,
th_link_visited TEXT,
th_link_hover TEXT,
a_link TEXT,
a_visited TEXT,
a_hover TEXT,
a_bg TEXT)
|
| Counter_Old | 17 | CREATE TABLE "Counter_Old" (
counter_cd TEXT,
counter_nm TEXT,
counter_url TEXT,
add_ts REAL,
counter_seq INTEGER DEFAULT 0,
PRIMARY KEY (counter_cd));
|
| Domain_Seconds | 389 | CREATE TABLE "Domain_Seconds" (
tld TEXT,
sld TEXT,
action_cd INTEGER default 0 not null,
additional_info TEXT,
short_desc TEXT default '');
CREATE UNIQUE INDEX seconds_pk ON Domain_Seconds (tld, sld)
|
| Domains | 5853 | CREATE TABLE Domains (
domain_id INTEGER PRIMARY KEY,
checked_cd INTEGER DEFAULT 0,
tld TEXT,
domain_nm TEXT,
subdomain TEXT);
CREATE UNIQUE INDEX tld_domain ON Domains (tld, domain_nm, subdomain)
|
| IP_Info | 49655 | CREATE TABLE IP_Info (
ip_id INTEGER PRIMARY KEY,
type_id INTEGER DEFAULT 0,
ip_big BLOB,
domain_id INTEGER,
host_type INTEGER DEFAULT 0,
add_ts REAL,
host_str TEXT,
ip_str TEXT DEFAULT null,
block_cd INTEGER default 0);
CREATE INDEX IP_Blob_IX
ON IP_info (ip_big)
|
| Link_Hit | 65758 | CREATE TABLE Link_Hit (
link_id INTEGER,
user_id INTEGER,
ip_id INTEGER,
site_id INTEGER,
domain_id INTEGER,
ts REAL);
CREATE INDEX Link_IP_IX
ON Link_Hit (ip_id)
|
| Log_Ajax | 465 | CREATE TABLE Log_Ajax (
aj_id INT,
session_id INT,
ip_id INT,
site_id INT,
rv_id INT,
ts REAL,
data_str TEXT)
|
| Log_Hit | 1814000 | CREATE TABLE Log_Hit (
ip_id INTEGER,
ts REAL,
site_id INTEGER,
page_id INTEGER DEFAULT 0,
fk_id INTEGER DEFAULT NULL,
user_id INTEGER DEFAULT 0,
campaign_id INTEGER DEFAULT NULL,
domain_id INTEGER DEFAULT NULL,
ad_id INTEGER DEFAULT null,
target_id INTEGER DEFAULT null,
page_nm TEXT DEFAULT NULL,
path_nm TEXT DEFAULT NULL,
query_str TEXT DEFAULT NULL,
q TEXT DEFAULT NULL,
db_hits INTEGER DEFAULT NULL,
microseconds INTEGER DEFAULT NULL);
CREATE INDEX LH_Ad_IX
ON Log_Hit (ad_id);
CREATE INDEX LH_Domain_IX
ON Log_Hit (domain_id);
CREATE INDEX LH_Target_IX
ON Log_Hit (site_id, target_id)
|
| Log_Hit_Old | 145399 | CREATE TABLE "Log_Hit_Old" (
ip_id INTEGER,
ts REAL,
site_id INTEGER,
page_id INTEGER DEFAULT 0,
fk_id INTEGER DEFAULT NULL,
user_id INTEGER DEFAULT 0,
campaign_id INTEGER DEFAULT NULL,
domain_id INTEGER DEFAULT NULL,
ad_id INTEGER DEFAULT null,
target_id INTEGER DEFAULT null,
page_nm TEXT DEFAULT NULL,
path_nm TEXT DEFAULT NULL,
query_str TEXT DEFAULT NULL,
q TEXT DEFAULT NULL,
db_hits INTEGER DEFAULT NULL,
microseconds INTEGER DEFAULT NULL)
|
| OS_Hit | 22940 | CREATE TABLE OS_Hit (
os_sku INT,
site_id INT,
ip_id INT,
ts REAL
)
|
| SVG_Counter | 20 | CREATE TABLE SVG_Counter (
counter_cd TEXT,
owner_id INTEGER,
counter_nm TEXT,
counter_url TEXT,
counter_grp TEXT,
add_ts REAL,
counter_seq INTEGER DEFAULT 0,
PRIMARY KEY (counter_cd));
|
| Stat_Buffer | 450006 | CREATE TABLE Stat_Buffer (
buffer_id INT,
category_id INT,
hits INT,
PRIMARY KEY (buffer_id,category_id)
);
|
| Stat_Buffer_Def | 239 | CREATE TABLE Stat_Buffer_Def ( buffer_id INT PRIMARY KEY, buffer_ts REAL ); |
| Stat_Buffer_Site | 7849 | CREATE TABLE Stat_Buffer_Site (
buffer_id INT,
site_id INT,
hits INT,
category_cnt INT,
PRIMARY KEY (buffer_id,site_id)
);
|
| Target_Analysis | 192 | CREATE TABLE Target_Analysis (
site_id INT,
target_id INT,
ad_cnt INT,
hit_cnt INT,
ip_cnt INT,
page_cnt INT,
ref_cnt INT);
CREATE UNIQUE INDEX Target_Analysis_UIX
ON Target_Analysis (site_id, target_id)
|
| Widget | 1178 | CREATE TABLE Widget (
widget_id INTEGER PRIMARY KEY,
owner_id INTEGER,
site_id INTEGER,
merchant_id INTEGER,
type_id INTEGER,
widget_nm TEXT,
widget_desc TEXT,
link_nm TEXT,
link_url TEXT,
link_id INTEGER,
page_id INTEGER,
update_ts REAL,
fk TEXT,
channel_nm TEXT,
widget_dt INTEGER,
group_id INTEGER
);
CREATE INDEX Widget_Site_IX
ON Widget (site_id)
|
| sqlite_stat1 | 20 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |