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 +cal
| Tables in cal | ||
|---|---|---|
| Table Name | Rows | DDL |
| Day_Data | 9 | CREATE TABLE Day_Data (
day_jd INTEGER PRIMARY KEY,
canonical_id INT,
update_ts REAL DEFAULT (julianday('now')),
day_nm TEXT,
day_url TEXT,
bg_class TEXT,
num_class TEXT,
bg_img_url TEXT,
picture_url TEXT,
az_html TEXT,
co_html TEXT,
ut_html TEXT,
all_html TEXT)
|
| Deleted_Event | 28963 | CREATE TABLE Deleted_Event (
event_id INTEGER PRIMARY KEY,
deleter_id INTEGER,
hit_cnt INTEGER,
calendar_id INTEGER,
location_id INTEGER,
category_id INTEGER,
recurring_id INTEGER,
ad_id INTEGER,
weight INTEGER,
owner_id INTEGER,
event_nm TEXT,
start_dt INTEGER,
start_min INTEGER,
duration_min INTEGER,
add_ts REAL,
short_desc TEXT,
event_url TEXT,
html_layout TEXT,
mobi_url TEXT,
phone_no TEXT,
html TEXT
)
|
| Event_Action | 72 | CREATE TABLE Event_Action (
event_id INT,
event_dt INT,
action_cd INT,
user_id INT,
hit_cnt INT,
affected_cnt INT,
action_ts REAL);
CREATE INDEX Event_Action_IX
ON Event_Action (event_id, action_ts)
|
| Event_Def | 31 | CREATE TABLE Event_Def (
event_id INTEGER PRIMARY KEY,
hit_cnt INT,
site_id INT,
target_id INT,
ad_id INT,
owner_id INT,
loc_id INT,
category_id INT,
start_dt INT,
start_min INT,
duration_min INT,
end_dt INT,
type_id INT,
x_day INT,
units INT,
update_ts REAL,
event_nm TEXT,
title_str TEXT,
event_desc TEXT,
event_url TEXT);
CREATE INDEX Event_IX
ON Event_Def(site_id, category_id);
CREATE UNIQUE INDEX Event_URL_UIX
ON Event_Def (event_url)
|
| Event_Display | 0 | CREATE TABLE Event_Display (
site_id INT,
event_dt INT,
event_id INT,
loc_id INT,
category_id INT,
weight INT,
start_min INT,
duration_min INT,
display_nm TEXT,
display_txt TEXT,
PRIMARY KEY(site_id, event_dt, event_id));
;
CREATE INDEX Event_Display_IX
ON Event_Display(event_id);
CREATE INDEX Event_Disp_Cat_IX
ON Event_Display (category_id)
|
| Loc_Change | 398 | CREATE TABLE Loc_Change (
loc_id INTEGER PRIMARY KEY,
user_id INT,
change_cd INT,
ts REAL
)
|
| Loc_Def | 399 | CREATE TABLE Loc_Def (
loc_id INTEGER PRIMARY KEY,
type_id INT,
site_id INT,
owner_id INT,
target_id INT,
hit_cnt INT,
public_yn char(1) DEFAULT 'Y',
location_nm TEXT,
html TEXT,
address_str TEXT,
url_str TEXT,
city_nm TEXT,
state_cd char(2),
zip_cd TEXT
);
CREATE UNIQUE INDEX Loc_Def_Site_UIX
ON Loc_Def (site_id,location_nm)
|
| Loc_Link | 580 | CREATE TABLE "Loc_Link" (
parent_id INT,
child_id INT,
pos INT,
short_desc TEXT,
PRIMARY KEY (parent_id,child_id)
);
|
| Loc_Waypoint | 193 | CREATE TABLE Loc_Waypoint (
loc_id INT,
pos INT,
latitude INT,
longitude INT,
elevation INT default null,
point_nm TEXT,
PRIMARY KEY(loc_id, pos));
|
| Location_Type | 19 | CREATE TABLE Location_Type (
type_id INTEGER PRIMARY KEY,
type_nm TEXT,
category_nm TEXT)
|
| Occurrence | 927 | CREATE TABLE Occurrence (
event_id INT,
site_id INT,
event_dt INT,
loc_id INT,
category_id INT,
weight INT,
start_min INT,
duration_min INT,
display_nm TEXT,
display_txt TEXT,
PRIMARY KEY(site_id, event_dt, event_id));
;
CREATE INDEX Occurrence_IX
ON Occurrence (event_id);
CREATE INDEX Occurence_Cat_IX
ON Occurrence (category_id)
|
| Waypoint_Old | 193 | CREATE TABLE "Waypoint_Old" (
point_id INTEGER PRIMARY KEY,
loc_id INT,
latitude REAL,
longitude REAL,
elevation INTEGER)
|
| sqlite_stat1 | 14 | CREATE TABLE sqlite_stat1(tbl,idx,stat) |