SQL Server

From Seamonster

Introduction

My (Rob's) goal is to build a Microsoft SQL Server version of the SEAMONSTER database engine. SQL Server is integrated with other Microsoft platforms and tools and software so (once one gets started) there is a lot of additional functionality available for working with the data.

This page lists a long list of sub-pages which contain query-language "code", verbatim. This is a one-time deal at this wiki: Once these pages are complete, the entire summer 2007 dataset for SEAMONSTER will be available in the context of SQL transactions. This is a rather nuts-and-bolts (low level / low brow / choose your own acronym) thing to do, and we do it for anyone interested in getting going on SQL databases from the ground up.

Finally a little caveat emptor: SMP-1 is the code term for the first version of this database. We're transitioning to SMP-2 which is a better design, so please consider all the SMP-1 stuff to be developmental and learning-curve oriented; there are much better ways of doing things! (See Gnomes).

In light of advances this stuff may become obsolete but for the moment:...

• SS CreateBRICKDbase.sql
• SS CreateBRICKDbaseTables.sql
• SS CreateCAMPUSDbase.sql
• SS CreateCAMPUSDbaseTables.sql
• SS CreateFISHCREEKDbase.sql
• SS CreateFISHCREEKDbaseTables.sql
• SS CreateLLCDbase.sql
• SS CreateLLCDbaseTables.sql
• SS CreateLOCATIONSDbase.sql
• SS CreateLOCATIONSDbaseTables.sql
• SS CreateMGTDbase.sql
• SS CreateMGTDbaseTables.sql
• SS CreateNSRLDbase.sql
• SS CreateNSRLDbaseTables.sql
• SS CreateULGDbase.sql
• SS CreateULGDbaseTables.sql
• SS CreateULLDbase.sql
• SS CreateULLDbaseTables.sql
• SS CreateUSGSDbase.sql
• SS CreateUSGSDbaseTables.sql
• SS CreateYSIDbase.sql
• SS CreateYSIDbaseTables.sql

Shadowing PostgreSQL

As noted the SEAMONSTER native database is built on PostgreSQL. The first version of this implementation we will call SMP-1 (for SEAMONSTER PostgreSQL Version 1). We call it an implementation rather than a database because of a fine point in the terminology: SMP-1 consists of many associated databases.

The idea of a possible "SMP-2" would be to evolve a single database that builds on SMP-1. It would generalize the sensor web structure, but to elaborate let me digress into details for a moment.

SMP-1 has a database called CAMPUS which contains data from instruments located at a particular site on the UAS campus. This is a searchable database as long as you happen to know that CAMPUS exists. SMP-1 will include another database called LOCATIONS which will provide site names (i.e. database names) in response to a spatial query. You say "Are there any sensors within this particular lat-lon box?" and SMP-1 will say "Yes we have some instruments in that region described in a database called CAMPUS." So you proceed to ask SMP-1 what sort of instruments are there, and eventually you ask for data from the CAMPUS database.

This is simpler to implement but leans towards being hardcoded. SMP-2 would move towards more generality as motivated by [www.opengeospatial.org OGC] objectives and standards. The trick of course is to generalize the design enough to accommodate every conceivable sensor web eventuality (which is impossible).

Since SEAMONSTER SQL Server shadoes SEAMONSTER PostgreSQL, there is also SMSS-1 and possibly SMSS-2. SQL Server operates on a command language (or 'query' language) called transact-SQL or tSQL. PostgreSQL uses a different dialect than tSQL so we do some translation to get SMP-1 and SMSS-1 "on the same page".

SMP-1 List of Databases

Each of the following is a database with corresponding tables. I list them as links where each link goes to a page of text that is a functional tSQL script. This sequence of scripts creates each database and then defines one or more tables within that database.

In what follows the SS stands for SQL Server, then there is a space and the filename of the tSQL script, where the file extension is always .sql. Each database is named using ALL CAPS. Subsequent table-creation scripts simply append 'Tables' to the file name.

[[SS CreateBRICKDbase.sql]]     [[SS CreateBRICKDbaseTables.sql]]
[[SS CreateCAMPUSDbase.sql]]    [[SS CreateCAMPUSDbaseTables.sql]]
[[SS CreateUSGSDbase.sql]]      [[SS CreateUSGSDbaseTables.sql]]
[[SS CreateNSRLDbase.sql]]      [[SS CreateNSRLDbaseTables.sql]]
[[SS CreateYSIDbase.sql]]       [[SS CreateYSIDbaseTables.sql]]
[[SS CreateULLDbase.sql]]       [[SS CreateULLDbaseTables.sql]]
[[SS CreateULGDbase.sql]]       [[SS CreateULGDbaseTables.sql]]
[[SS CreateLLCDbase.sql]]       [[SS CreateLLCDbaseTables.sql]]
[[SS CreateMGTDbase.sql]]       [[SS CreateMGTDbaseTables.sql]]
[[SS CreateFISHCREEKDbase.sql]] [[SS CreateFISHCREEKDbaseTables.sql]]
[[SS CreateLOCATIONSDbase.sql]] [[SS CreateLOCATIONSDbaseTables.sql]]