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 | ||||
| Tables in main | ||
|---|---|---|
| Table Name | Rows | DDL |
| Ad_Action | 1285 | CREATE TABLE Ad_Action (
action_id INTEGER PRIMARY KEY,
request_id INT,
ad_id INT,
action_cd INT,
session_id INT,
view_cnt INT,
unique_ips INT,
hit_cnt INT,
action_ts REAL,
notes_txt TEXT);
CREATE INDEX Ad_Action_IX
ON Ad_Action (request_id);
CREATE INDEX Ad_Action_Request_IX
ON Ad_Action (request_id)
|
| Ad_Def | 217 | CREATE TABLE "Ad_Def" (
ad_id INTEGER PRIMARY KEY,
type_id INT,
status_cd INT,
track_cd INT,
owner_id INT,
source_id INT,
foreign_id INT,
merchant_id INT,
exp_dt INT,
add_ts REAL default (julianday('now')),
ad_nm TEXT,
url TEXT,
img_url TEXT,
title_txt TEXT,
line_1 TEXT,
line_2 TEXT,
line_3 TEXT);
CREATE UNIQUE INDEX Ad_Source_UIX
ON Ad_Def (source_id, foreign_id)
|
| Ad_Export | 172 | CREATE TABLE Ad_Export( ad_id INT, target_id INT, site_id INT ) |
| Ad_Request | 123 | CREATE TABLE Ad_Request (
request_id INTEGER PRIMARY KEY,
random_int INT,
type_id INT,
site_id INT,
target_id INT,
ad_id INT,
status_cd INT,
user_id INT,
agreement_id INT,
track_cd INT,
request_ts REAL,
email_addr TEXT,
requestor_nm TEXT,
ad_nm TEXT,
url TEXT,
img_url TEXT,
title_txt TEXT,
line_1 TEXT,
line_2 TEXT,
line_3 TEXT,
expiration TEXT, cryto_nm TEXT)
|
| Ad_Target | 115 | CREATE TABLE Ad_Target (
target_id INTEGER PRIMARY KEY,
target_nm TEXT,
rate_amt INT,
box_cd INT,
short_desc TEXT)
|
| Ad_Target_Ad | 4876 | CREATE TABLE Ad_Target_Ad (
ad_id INT,
target_id INT,
site_id INT,
PRIMARY KEY (target_id, site_id, ad_id));
|
| Affiliate_Ads | 806 | CREATE TABLE Affiliate_Ads (
ad_id INTEGER PRIMARY KEY,
active_yn char(1),
size_id INT,
ad_txt TEXT,
ad_nm TEXT,
merchant_id INT,
user_id INT);
CREATE INDEX Ad_Def_Size_IX
ON Affiliate_Ads (size_id)
|
| Ajax_Action | 9 | CREATE TABLE Ajax_Action (
aj_id INTEGER PRIMARY KEY,
role_id INT DEFAULT 2,
val_cnt INT DEFAULT 1,
db_nm TEXT,
valid_str TEXT,
ajax_nm TEXT,
sql_str TEXT)
|
| Blob_Class | 9 | CREATE TABLE Blob_Class (
class_id INTEGER PRIMARY KEY,
class_nm TEXT,
table_nm TEXT,
table_pk TEXT,
column_nm TEXT,
column_desc TEXT,
return_prog TEXT)
|
| Bottom_Link | 65 | CREATE TABLE Bottom_Link (
bottom_id INTEGER PRIMARY KEY,
display_nm TEXT,
link_url TEXT,
title_txt TEXT)
|
| CC_Role | 20 | CREATE TABLE CC_Role (
role_id INTEGER PRIMARY KEY,
role_nm TEXT,
add_ts REAL,
role_type_cd INTEGER,
blob_id INTEGER)
|
| Color_Scheme | 6 | CREATE TABLE Color_Scheme (
scheme_id INTEGER PRIMARY KEY,
owner_id INTEGER,
scheme_nm TEXT,
body_color TEXT,
bg_color TEXT,
txt_color TEXT,
th_bg TEXT,
th_txt TEXT,
row1_bg TEXT,
row2_bg TEXT,
prompt_bg TEXT,
prompt_text TEXT,
cell_bg TEXT,
menu_img TEXT DEFAULT '/images/bar40.gif',
thl_color TEXT,
thl_visited TEXT,
thl_hover TEXT,
a_link TEXT,
a_visited TEXT,
a_hover TEXT,
a_bg TEXT)
|
| DB_Database | 8 | CREATE TABLE DB_Database (
db_id INTEGER PRIMARY KEY,
platform_type TEXT,
database_nm TEXT,
file_nm TEXT,
short_desc TEXT)
|
| Email_X | 7405 | CREATE TABLE Email_X (
email_id INTEGER PRIMARY KEY,
email_txt TEXT,
email_bin BLOB,
decrypt_cnt INTEGER NOT NULL DEFAULT 0,
block_yn char(1) NOT NULL DEFAULT 'N')
|
| HTML_Blob | 5017 | CREATE TABLE HTML_Blob (
blob_id INTEGER PRIMARY KEY,
user_id INTEGER,
text_type char(1) DEFAULT 'H',
use_id INTEGER,
include_file TEXT DEFAULT '',
foreign_key TEXT NOT NULL DEFAULT '',
add_ts REAL,
blob_txt TEXT,
title_nm TEXT)
|
| HTML_Help | 41 | CREATE TABLE HTML_Help (
help_id INTEGER PRIMARY KEY,
owner_id INTEGER,
title TEXT,
html_txt TEXT,
category_id INTEGER NOT NULL DEFAULT '0')
|
| IP_Change | 1401 | CREATE TABLE IP_Change (
session_id INTEGER,
old_id INTEGER,
new_id INTEGER,
change_ts REAL);
CREATE INDEX IP_Change_IX on IP_Change(session_id)
|
| Login_Key | 58075 | CREATE TABLE Login_Key (
key_id INTEGER PRIMARY KEY,
random_int INTEGER,
site_id INTEGER,
ip_id INTEGER,
status_cd INTEGER,
try_cnt INTEGER,
add_ts REAL,
login_ts REAL,
session_id INTEGER);
CREATE INDEX Login_Key_IP_IX ON Login_Key (ip_id)
|
| Merchant | 2055 | CREATE TABLE Merchant (
merchant_id INTEGER PRIMARY KEY,
prog TEXT,
active_cd char(1) NOT NULL,
merchant_nm TEXT,
url TEXT,
join_dt TEXT,
term_dt TEXT,
foreign_id TEXT,
state_cd TEXT,
tot_hits INT,
tot_commissions REAL)
|
| Merchant_Promo | 5 | CREATE TABLE Merchant_Promo (
fob_id INTEGER PRIMARY KEY,
merchant_id INTEGER,
cj_id INTEGER,
merchant_nm TEXT,
fob_nm TEXT,
start_dt INTEGER,
end_dt INTEGER,
image_url TEXT,
width_no INTEGER,
height_no INTEGER,
link_url TEXT,
display_txt TEXT,
html TEXT)
|
| Number_Class | 55 | CREATE TABLE Number_Class (
class_id INTEGER PRIMARY KEY,
class_nm TEXT NOT NULL,
class_desc TEXT,
character_yn char(1) NOT NULL DEFAULT 'N');
CREATE UNIQUE INDEX Number_Class_UIX
ON Number_Class (class_nm)
|
| Number_Code | 352 | CREATE TABLE Number_Code (
class_id INTEGER,
code_id INTEGER,
pos INTEGER,
code_nm TEXT,
code_desc TEXT,
PRIMARY KEY (class_id, code_id));
|
| Old_Password | 9135 | CREATE TABLE Old_Password (
user_id INTEGER PRIMARY KEY,
old_pwd BLOB)
|
| PHP_Page | 113 | CREATE TABLE PHP_Page (
page_id INTEGER PRIMARY KEY,
role_id INTEGER DEFAULT 0,
title TEXT,
owner_id INTEGER,
add_ts REAL,
page_desc TEXT,
keywords TEXT,
file_nm TEXT,
layout_no INTEGER,
track_yn char(1) NOT NULL DEFAULT 'N',
help_id INTEGER,
footer TEXT)
|
| Page_Data | 1264 | CREATE TABLE Page_Data (
page_id INTEGER PRIMARY KEY,
status_fl char(1),
user_id INT,
hit_cnt INT,
site_id INT,
category_id INT,
group_id INT,
merchant_id INT,
width_no INT,
layout INT,
ad_id INT,
target_id INT,
update_ts REAL,
file_nm TEXT,
title TEXT,
short_desc TEXT,
keywords TEXT,
picture_url TEXT,
og_type TEXT,
html_txt TEXT,
pn TEXT);
CREATE INDEX PD_SiteFile_IX
ON Page_Data(site_id, file_nm)
|
| Page_Data_Deleted | 71 | CREATE TABLE Page_Data_Deleted (
deleter_id INT,
delete_ts REAL,
page_id INT,
status_fl char(1),
user_id INT,
hit_cnt INT,
site_id INT,
category_id INT,
group_id INT,
merchant_id INT,
width_no INT,
layout INT,
ad_id INT,
target_id INT,
update_ts REAL,
file_nm TEXT,
title TEXT,
short_desc TEXT,
keywords TEXT,
picture_url TEXT,
og_type TEXT,
html_txt TEXT,
pn TEXT)
|
| Page_Data_Save | 811 | CREATE TABLE Page_Data_Save( page_id INT, status_fl TEXT, user_id INT, hit_cnt INT, site_id INT, category_id INT, group_id INT, merchant_id INT, width_no INT, layout INT, ad_id INT, target_id INT, update_ts REAL, file_nm TEXT, title TEXT, short_desc TEXT, keywords TEXT, picture_url TEXT, og_type TEXT, html_txt TEXT, pn TEXT ) |
| Page_Group | 17 | CREATE TABLE Page_Group (
group_id INTEGER PRIMARY KEY,
pos INT,
group_nm TEXT,
ext_url TEXT,
ext_nm TEXT,
html TEXT
)
|
| Page_Prosper | 41 | CREATE TABLE Page_Prosper (
pg INTEGER PRIMARY KEY,
prosper_id INT)
|
| Page_Ref | 1506 | CREATE TABLE "Page_Ref" (
page_id INT,
ref_id INT,
domain_id INT,
drawn_dt INT,
source_nm TEXT,
source_url TEXT,
notes TEXT
);
CREATE UNIQUE INDEX Page_Ref_IX
ON Page_Ref (page_id, ref_id)
|
| Page_Ref_Old | 993 | CREATE TABLE "Page_Ref_Old" (
page_id INT,
ref_id INT,
drawn_dt INT,
source_nm TEXT,
source_url TEXT,
notes TEXT
)
|
| RM_Export | 1 | CREATE TABLE RM_Export( user_id INT, user_nm TEXT, pwd, rights_flag INT, first_nm TEXT, last_nm TEXT, birth_dt INT, email_id INT, site_id INT, country_id INT, state_cd TEXT, county_id INT, msg_cnt INT, last_dt INT, dt_cnt INT ) |
| RM_Role | 22 | CREATE TABLE RM_Role (
role_id INTEGER PRIMARY KEY,
role_nm TEXT,
add_ts INTEGER,
role_type_cd INTEGER,
blob_id INTEGER
);
CREATE UNIQUE INDEX User_Role_UIX
ON RM_Role(role_nm)
|
| RM_Script | 98 | CREATE TABLE RM_Script (
script_id INTEGER PRIMARY KEY,
role_id INTEGER DEFAULT 0,
title TEXT DEFAULT 'new',
owner_id INTEGER DEFAULT 0,
add_ts REAL,
page_desc TEXT,
keywords TEXT,
script_nm TEXT,
file_nm TEXT,
layout_no INTEGER DEFAULT 0,
track_yn char(1) NOT NULL DEFAULT 'Y',
help_id INTEGER DEFAULT 0,
footer TEXT,
target_id INTEGER);
CREATE UNIQUE INDEX RM_Script_Path_IX
ON RM_Script (file_nm)
|
| RM_Session | 3619 | CREATE TABLE RM_Session (
session_id INTEGER PRIMARY KEY,
session_key INTEGER,
user_id INTEGER,
status_cd INTEGER,
start_ts REAL,
ip_id INTEGER,
site_id INTEGER);
CREATE INDEX RM_Session_User_IX
ON RM_Session(user_id)
|
| RM_User | 9136 | CREATE TABLE RM_User (
user_id INTEGER PRIMARY KEY,
user_nm TEXT NOT NULL,
pwd BLOB,
rights_flag INTEGER NOT NULL DEFAULT 1,
first_nm TEXT,
last_nm TEXT,
birth_dt INTEGER,
email_id INTEGER,
site_id INTEGER DEFAULT 0,
country_id INTEGER DEFAULT 0,
state_cd char(2) DEFAULT 'GL',
county_id INTEGER NOT NULL DEFAULT 0,
msg_cnt INTEGER NOT NULL DEFAULT -1,
last_dt INTEGER,
dt_cnt INTEGER NOT NULL DEFAULT 0);
CREATE UNIQUE INDEX RMU_UIX
ON RM_User (user_nm)
|
| RM_User_Role | 105 | CREATE TABLE RM_User_Role (
user_id INT,
role_id INT,
site_id INT,
PRIMARY KEY (user_id,role_id));
|
| Seq_Def | 24 | CREATE TABLE Seq_Def (
seq_nm TEXT NOT NULL,
seq INTEGER DEFAULT 0,
seq_type INTEGER,
table_nm TEXT,
column_nm TEXT,
PRIMARY KEY (seq_nm));
|
| Site_Buffer | 70 | CREATE TABLE Site_Buffer (
script_id INTEGER,
site_id INTEGER,
buff_cnt INTEGER,
buff_a TEXT,
buff_b TEXT,
buff_c TEXT,
PRIMARY KEY(script_id, site_id));
|
| User_Agreement | 6 | CREATE TABLE User_Agreement (
user_id INTEGER,
terms_id INTEGER,
session_id INTEGER,
ts TEXT,
PRIMARY KEY (user_id,terms_id));
|
| Web_Site | 76 | CREATE TABLE Web_Site (
site_id INTEGER PRIMARY KEY,
status_id INT DEFAULT 0,
bottom_id INT,
mobi_id INT,
css_id INT,
al_id INT,
cj_id INT,
ssl_cd CHAR(1),
domain_nm TEXT,
site_nm TEXT,
short_nm TEXT,
subject_nm TEXT,
state_cd TEXT,
sod_cd CHAR(1));
CREATE INDEX WS_Domain_IX
ON Web_Site (domain_nm)
|
| Widget | 1178 | CREATE TABLE Widget (
widget_id INTEGER PRIMARY KEY,
owner_id INT,
site_id INT,
merchant_id INT,
type_id INT,
widget_nm TEXT,
widget_desc TEXT,
link_nm TEXT,
link_url TEXT,
link_id INT,
page_id INT,
update_ts REAL,
fk TEXT,
channel_nm TEXT,
widget_dt INT,
group_id INT
);
CREATE INDEX Widget_Site_IX
ON Widget (site_id)
|
| Widget_Group | 14 | CREATE TABLE Widget_Group (
group_id INTEGER PRIMARY KEY,
group_nm TEXT,
dir_cat TEXT,
short_desc TEXT)
|
| Widget_Platform | 16 | CREATE TABLE Widget_Platform (
platform_id INTEGER PRIMARY KEY,
weight INTEGER,
mobi_yn char(1) NOT NULL DEFAULT 'N',
platform_nm TEXT,
url_str TEXT,
mobi_url TEXT,
short_desc TEXT,
channel_url TEXT,
video_viewer TEXT)
|
| sqlite_stat1 | 40 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |