From de7610a90aaee5d2c29900371326813578c38493 Mon Sep 17 00:00:00 2001
From: Kaj-Michael Lang <milang@onion.tal.org>
Date: Mon, 4 Feb 2008 15:27:39 +0200
Subject: [PATCH] Add SQL table and indexe definitions

---
 src/osm-sql-tables.h | 124 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 124 insertions(+)
 create mode 100644 src/osm-sql-tables.h

diff --git a/src/osm-sql-tables.h b/src/osm-sql-tables.h
new file mode 100644
index 0000000..11649d0
--- /dev/null
+++ b/src/osm-sql-tables.h
@@ -0,0 +1,124 @@
+#ifndef _OSM_SQL_TABLES
+#define _OSM_SQL_TABLES
+
+
+/**
+ * SQL Table and index definitions
+ *
+ */
+
+/*
+PRAGMA page_size=4096;
+PRAGMA encoding = "UTF-8"; 
+*/
+
+#define OSM_TABLE_NODES "create table nodes IF NOT EXISTS ( \
+	nid	int primary key, \
+	ilat	int not null, \
+	ilon	int not null, \
+	rlat	real not null, \
+	rlon	real not null, \
+	l	int not null default 0, \
+	f	int not null default 0);"
+
+#define OSM_TABLE_NODE_TAGS "create table node_tags IF NOT EXISTS ( \
+	nid	int not null, t text);"
+
+#define OSM_TABLE_WAY "create table way IF NOT EXISTS ( \
+	wid		int primary key, \
+	type	int not null, \
+	nodes	int not null, \
+	flags	int not null, \
+	speed	int not null default 0, \
+	isin_c	int not null default 0, \
+	isin_p	int not null default 0, \
+	lat		real not null, \
+	lon		real not null);"
+
+#define OSM_TABLE_WAY_TAGS "create table way_tags IF NOT EXISTS ( \
+	wid int not null, \
+	t text);"
+
+#define OSM_TABLE_WAY_UPDATES "create table way_updates IF NOT EXISTS ( \
+	wid		int primary key, \
+	utime	int not null, \
+	notes	text);"
+
+#define OSM_TABLE_WAY_N2N "create table way_n2n IF NOT EXISTS ( \
+	wid		int not null, \
+	f		int not null, \
+	t		int not null);"
+
+#define OSM_TABLE_WAY_NAMES "create table way_names IF NOT EXISTS ( \
+	wid		int primary key, \
+	name	varchar(256), \
+	norm	varchar(256));"
+
+#define OSM_TABLE_WAY_PC "create table way_pc IF NOT EXISTS ( \
+	wid		int primary key, \
+	pc		varchar(256));"
+
+#define OSM_TABLE_WAY_NAMES_NLS "create table way_names_nls IF NOT EXISTS ( \
+	wid		int not null, \
+	lang	char(4) not null, \
+	name	varchar(256), \
+	norm	varchar(256));"
+
+#define OSM_TABLE_ "create table way_ref IF NOT EXISTS ( \
+	rid		int primary key, \
+	ref		varchar(32), \
+	int_ref	varchar(32));"
+
+#define OSM_TABLE_PLACES "create table places IF NOT EXISTS ( \
+	nid		int primary key, \
+	type	int not null, \
+	name	text, \
+	isin_p	int not null, \
+	isin_c	int not null);"
+
+#define OSM_TABLE_POI "create table poi IF NOT EXISTS ( \
+	poi_id	INTEGER PRIMARY KEY AUTOINCREMENT, \
+	lat		real not null, \
+	lon		real not null, \
+	elev	real default 0.0, \
+	label	text, \
+	desc	text, \
+	url		text, \
+	postal_code text, \
+	public	int default 1, \
+	source	int default 0, \
+	cat_id	int, \
+	osm_id	int default 0, \
+	isin_p	int default 0, \
+	isin_c	int default 0, \
+	priority int default 2, \
+	addtime	int default 0);"
+
+#define OSM_TABLE_POI_CATEGORY "create table category IF NOT EXISTS ( \
+	cat_id		INTEGER PRIMARY KEY AUTOINCREMENT, \
+	pcat_id		int not null default 0, \
+	priority	int not null default 2, \
+	label		text, \
+	desc		text, \
+	color		char(6) not null default '#AAAA40', \
+	icon		varchar(32), \
+	enabled		int not null default 1);"
+
+/* Indexes */
+#define OSM_INDEX_1 CREATE UNIQUE INDEX IF NOT EXISTS poi_lat_lon_idx on poi (lat, lon, cat_id);"
+#define OSM_INDEX_2 CREATE INDEX IF NOT EXISTS poi_priority on poi (priority);"
+#define OSM_INDEX_3 CREATE INDEX IF NOT EXISTS node_rlat_idx on nodes (rlat);"
+#define OSM_INDEX_4 CREATE INDEX IF NOT EXISTS node_rlon_idx on nodes (rlon);"
+#define OSM_INDEX_5 CREATE INDEX IF NOT EXISTS node_ilat_idx on nodes (ilat);"
+#define OSM_INDEX_6 CREATE INDEX IF NOT EXISTS node_ilon_idx on nodes (ilon);"
+#define OSM_INDEX_7 CREATE INDEX IF NOT EXISTS way_name_idx on way_names (name);"
+#define OSM_INDEX_8 CREATE INDEX IF NOT EXISTS way_norm_name_idx on way_names (norm);"
+#define OSM_INDEX_9 CREATE INDEX IF NOT EXISTS way_name_nls_id_idx on way_names_nls (wid);"
+#define OSM_INDEX_10 CREATE INDEX IF NOT EXISTS way_name_nls_name_idx on way_names_nls (name);"
+#define OSM_INDEX_11 CREATE INDEX IF NOT EXISTS way_norm_nls_name_idx on way_names_nls (norm);"
+#define OSM_INDEX_12 CREATE INDEX IF NOT EXISTS way_ref_idx on way_ref (ref);"
+#define OSM_INDEX_13 CREATE INDEX IF NOT EXISTS way_n2n_wid_idx on way_n2n (wid);"
+#define OSM_INDEX_14 CREATE INDEX IF NOT EXISTS way_n2n_f_idx on way_n2n (f);"
+#define OSM_INDEX_15 CREATE INDEX IF NOT EXISTS way_n2n_t_idx on way_n2n (t);"
+
+#endif
-- 
2.39.5