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 | 130242 | 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 | 10694 | 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 | 21319 | 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 | 222490 | CREATE TABLE IP_Block ( ip_id INT, ts REAL, link_id INT, ad_id INT, page_id INT) |
IP_Hits | 3189 | 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 | 491263 | 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 | 394816 | 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 | 646960 | 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_202010 | 3477565 | CREATE TABLE "Log_Hit_202010" ( 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) |
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_202102 | 3273605 | CREATE TABLE "Log_Hit_202102" ( 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) |
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 | 36496 | 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 | 31 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |