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 | 125885 | CREATE TABLE Ad_Hit (
ad_id INT,
target_id INT,
site_id INT,
ip_id INT,
domain_id INT,
ts REAL);
CREATE INDEX Ad_Hit_IX
ON Ad_Hit (ad_id)
|
| Ad_Hit_Old | 7013 | CREATE TABLE "Ad_Hit_Old" (
ad_id INTEGER,
target_id INTEGER,
site_id INTEGER,
ip_id INTEGER,
domain_id INTEGER,
ts REAL)
|
| Ajax_Hit | 10582 | 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 | 18866 | 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_Block | 146024 | CREATE TABLE IP_Block (
ip_id INT,
ts REAL,
link_id INT,
ad_id INT,
page_id INT)
|
| IP_Hits | 2036 | CREATE TABLE IP_Hits (
ip_id INT,
ad_hits INT,
link_hits INT,
page_hits INT,
recent_hits INT,
latest_ts REAL);
CREATE UNIQUE INDEX IP_Hit_IX on IP_Hits(ip_id)
|
| IP_Info | 455899 | 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 | 386053 | 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)
|
| Link_Hit_Old | 102679 | CREATE TABLE "Link_Hit_Old" (
link_id INTEGER,
user_id INTEGER,
ip_id INTEGER,
site_id INTEGER,
domain_id INTEGER,
ts REAL)
|
| 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 | 3318567 | CREATE TABLE Log_Hit (
ip_id INT,
ts REAL,
site_id INT,
page_id INT DEFAULT 0,
fk_id INT DEFAULT NULL,
user_id INT DEFAULT 0,
campaign_id INT DEFAULT NULL,
domain_id INT DEFAULT NULL,
ad_id INT DEFAULT null,
target_id INT DEFAULT null,
page_nm TEXT DEFAULT NULL,
path_nm TEXT DEFAULT NULL,
query_str TEXT DEFAULT NULL,
q TEXT DEFAULT NULL,
db_hits INT DEFAULT NULL,
microseconds INT DEFAULT NULL);
CREATE INDEX LH_Ad_IX
ON Log_Hit (ad_id);
CREATE INDEX LH_Targetdro_IX
ON Log_Hit (site_id, target_id);
CREATE INDEX LH_Domainx_IX
ON Log_Hit (domain_id)
|
| Log_Hit_201905 | 5882409 | CREATE TABLE "Log_Hit_201905" (
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)
|
| Log_Hit_201910 | 3069921 | CREATE TABLE "Log_Hit_201910" (
ip_id INTEGER,
ts REAL,
site_id INT,
page_id INT DEFAULT 0,
fk_id INT DEFAULT NULL,
user_id INT DEFAULT 0,
campaign_id INT DEFAULT NULL,
domain_id INT DEFAULT NULL,
ad_id INT DEFAULT null,
target_id INT DEFAULT null,
page_nm TEXT DEFAULT NULL,
path_nm TEXT DEFAULT NULL,
query_str TEXT DEFAULT NULL,
q TEXT DEFAULT NULL,
db_hits INT DEFAULT NULL,
microseconds INT DEFAULT NULL)
|
| Log_Hit_201911 | 1172310 | CREATE TABLE Log_Hit_201911( ip_id INT, ts REAL, site_id INT, page_id INT, fk_id INT, user_id INT, campaign_id INT, domain_id INT, ad_id INT, target_id INT, page_nm TEXT, path_nm TEXT, query_str TEXT, q TEXT, db_hits INT, microseconds INT ) |
| Log_Hit_2020_06 | 6419129 | CREATE TABLE "Log_Hit_2020_06" (
ip_id INT,
ts REAL,
site_id INT,
page_id INT DEFAULT 0,
fk_id INT DEFAULT NULL,
user_id INT DEFAULT 0,
campaign_id INT DEFAULT NULL,
domain_id INT DEFAULT NULL,
ad_id INT DEFAULT null,
target_id INT DEFAULT null,
page_nm TEXT DEFAULT NULL,
path_nm TEXT DEFAULT NULL,
query_str TEXT DEFAULT NULL,
q TEXT DEFAULT NULL,
db_hits INT DEFAULT NULL,
microseconds INT DEFAULT NULL);
CREATE INDEX LHa_Ad_IX
ON "Log_Hit_2020_06" (ad_id);
CREATE INDEX LHa_Targetdro_IX
ON "Log_Hit_2020_06" (site_id, target_id)
|
| Log_Hit_Old | 3649356 | 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 | 34893 | CREATE TABLE OS_Hit (
os_sku INT,
site_id INT,
ip_id INT,
ts REAL
)
|
| SVG_Counter | 14 | 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 | 452797 | CREATE TABLE Stat_Buffer (
buffer_id INT,
category_id INT,
hits INT,
PRIMARY KEY (buffer_id,category_id)
);
|
| Stat_Buffer_Def | 240 | 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 | 29 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |