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 +dir
| Tables in dir | ||
|---|---|---|
| Table Name | Rows | DDL |
| Cat_Key | 460 | CREATE TABLE Cat_Key (
id INTEGER,
claimed_yn char(1) NOT NULL DEFAULT 'N',
PRIMARY KEY (id))
|
| Check_Cat | 3004 | CREATE TABLE Check_Cat (
cat_id INT,
user_id INT,
ts REAL)
|
| Fandango | 16 | CREATE TABLE Fandango (
fandango_id INTEGER PRIMARY KEY,
site_id INTEGER,
hit_cnt INTEGER,
theater_cd TEXT,
theater_nm TEXT,
address TEXT,
city TEXT,
state TEXT,
zip TEXT)
|
| Go_Link | 209 | CREATE TABLE Go_Link (
short_nm TEXT,
hit_cnt INTEGER NOT NULL DEFAULT '0',
domain_nm TEXT,
path_nm TEXT,
link_nm TEXT,
add_ts REAL,
PRIMARY KEY (short_nm));
|
| KS_Review | 271 | CREATE TABLE KS_Review (
ks_id INT,
review_id INT,
PRIMARY KEY (ks_id, review_id));
|
| Keyword_Scope | 7 | CREATE TABLE Keyword_Scope (
scope_id INTEGER PRIMARY KEY,
target_id INT,
scope_nm TEXT)
|
| Kickstarter | 273 | CREATE TABLE Kickstarter (
ks_id INTEGER PRIMARY KEY,
end_dt INT,
project_dir TEXT,
creator_nm TEXT,
city_nm TEXT,
state_cd TEXT);
CREATE INDEX Kickstarter_State_IX
ON Kickstarter (state_cd)
|
| Link | 33288 | CREATE TABLE Link (
link_id INTEGER PRIMARY KEY,
category_id INT,
page_id INT,
hit_cnt INT,
user_id INT,
merchant_id INT,
weight INT,
exp_dt INT,
status_fl char(1),
track_yn char(1) DEFAULT 'D',
add_ts REAL,
link_nm TEXT,
ssl_cd char(1) default '',
host_nm TEXT,
path_nm TEXT,
query_nm TEXT,
short_desc TEXT,
display_nm TEXT, title_extra TEXT);
CREATE INDEX Link_Cat_IX
ON Link (category_id);
CREATE INDEX Link_Page_IX
ON Link (page_id)
|
| Link_Cat_FWD | 35 | CREATE TABLE Link_Cat_FWD (
old_nm TEXT,
new_nm TEXT,
PRIMARY KEY (old_nm)
);
|
| Link_Category | 4959 | CREATE TABLE Link_Category (
category_id INTEGER PRIMARY KEY,
parent_id INTEGER,
site_id INTEGER,
catpos INTEGER,
blob_id INTEGER,
side_id INTEGER,
target_id INTEGER,
bottom_id INTEGER,
redirect_id INTEGER,
sitemap_yn char(1),
file_nm TEXT,
category_nm TEXT,
short_desc TEXT,
keywords TEXT,
hit_cnt INTEGER,
update_ts REAL,
event_cnt INTEGER DEFAULT 0);
CREATE INDEX Category_Parent_IX
ON Link_Category (parent_id);
CREATE INDEX Category_Name_IX
ON Link_Category(category_nm)
|
| Link_Category_Old | 4497 | CREATE TABLE "Link_Category_Old" (
category_id INTEGER PRIMARY KEY,
parent_id INTEGER,
site_id INTEGER,
catpos INTEGER,
blob_id INTEGER,
side_id INTEGER,
ad_id INTEGER,
bottom_id INTEGER,
redirect_id INTEGER,
sitemap_yn char(1),
file_nm TEXT,
category_nm TEXT,
short_desc TEXT,
keywords TEXT,
hit_cnt INTEGER,
update_ts REAL,
event_cnt INTEGER DEFAULT 0);
CREATE INDEX CategoryParent_IX
ON "Link_Category_Old" (parent_id)
|
| Link_Check | 54 | CREATE TABLE Link_Check (
site_id INTEGER PRIMARY KEY,
ok_cnt INT DEFAULT 0,
fixed_cnt INT DEFAULT 0,
del_cnt INT DEFAULT 0,
link_cnt INT DEFAULT 0,
deleted_cnt INT DEFAULT 0,
ts REAL DEFAULT 0,
oldest_exp INT DEFAULT 0)
|
| Link_Delete | 20464 | CREATE TABLE Link_Delete (
deleter_id int,
delete_ts REAL,
link_id INT,
category_id INT,
page_id INT,
hit_cnt INT,
user_id INT,
merchant_id INT,
weight INT,
exp_dt INT,
status_fl char(1),
track_yn char(1) DEFAULT 'D',
add_ts REAL,
link_nm TEXT,
ssl_cd char(1) default '',
host_nm TEXT,
path_nm TEXT,
query_nm TEXT,
short_desc TEXT,
display_nm TEXT, title_extra TEXT);
CREATE INDEX Link_Delete_Cat_IX
ON Link_Delete (category_id)
|
| Link_Keyword | 60842 | CREATE TABLE Link_Keyword (
link_id INTEGER,
site_id INTEGER,
keyword TEXT,
PRIMARY KEY (link_id, keyword)
);
;
CREATE INDEX Keyword_IX
ON Link_Keyword (keyword)
|
| Link_Not_Found | 10225 | CREATE TABLE Link_Not_Found (
link_id INTEGER,
ip_id INTEGER,
ts REAL,
site_id INTEGER,
status_cd INTEGER default 0,
refer_txt TEXT)
|
| Link_Search | 55204 | CREATE TABLE Link_Search (
site_id INT,
term TEXT,
last_ts REAL,
cnt INT,
PRIMARY KEY (site_id,term)
);
|
| Review_Books | 3 | CREATE TABLE Review_Books (
rowid INTEGER PRIMARY KEY,
review_id INT,
isbn INT,
pub_yr INT,
pages INT,
add_ts REAL,
type_cd TEXT,
title_nm TEXT,
author_nm TEXT,
press_nm TEXT,
short_desc TEXT);
CREATE INDEX Review_Books_IX
ON Review_Books (review_id)
|
| SRO | 7932 | CREATE TABLE SRO( link_id INT, review_id INT, site_id INT, status_cd INT, review_dt INT, hit_cnt INT, prev_btn TEXT, next_btn TEXT, add_ts REAL ) |
| Site_Review | 5838 | CREATE TABLE Site_Review (
review_id INTEGER PRIMARY KEY,
owner_id INT,
hit_cnt INT,
target_id INT,
ad_id INT,
status_cd INT DEFAULT 1,
width_no INT,
bg_color INT,
text_color INT,
link_color INT,
review_nm TEXT,
domain_nm TEXT,
path_nm TEXT,
site_desc TEXT,
canonical_site INT,
update_ts REAL, picture_url TEXT)
|
| Site_Review_Link | 7778 | CREATE TABLE "Site_Review_Link" (
link_id INT,
review_id INT,
site_id INT,
status_cd INT default 1,
review_dt INT,
hit_cnt INT default 0,
prev_btn TEXT,
next_btn TEXT,
add_ts REAL default (julianday('now')) ,
PRIMARY KEY (site_id, review_dt));
;
CREATE INDEX SRL_Link_IX
ON Site_Review_Link (link_id);
CREATE INDEX SRL_Review_IX
ON Site_Review_Link (review_id)
|
| sqlite_stat1 | 23 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |