Siliceous and Delicious

Believe it or not, Ripley, but there’s another snowstorm underway right now. And another one coming, tomorrow. For good measure.

Managed to make it in to work, however, and even settled down to actually do some work after getting some emailing out of the way (including emailing the remaining candidate reviewer for the morphospace characters, who turns out to be less of a diatom expert and more of a morphospace guy; we’ll see what happens). Irritatingly enough, the Practical SQL book I’ve been using is an electronic resource with an apparently limited number of viewing licenses, and it’s currently unavailable (somebody else must be viewing it). How annoying. Well, perhaps I can blaze ahead with choosing data types for each of my columns, since that’s something I’ll need to decide before I can set up the database.

Here is the current view of the schema:

Here are the most appropriate data types I can think of, available types being listed here:

Holes:

• Hole_ID: integer primary key (this column type in SQLite automatically increments)
• Latitude: real
• Longitude: real

Slides:

• Slide_ID: integer primary key
• Hole_ID: integer references Holes
• Depth: real
• Age: real
• Preservation: text check (Preservation in (‘ p’ , ‘ pm’ , ‘ m’ , ‘ mg’ ,  ‘ g’ ) ) ,

Individuals

• Indiv_ID: integer primary key
• Slide_ID: integer references Slides
• Species: text check (Species in (‘ Species one’ , ‘ Species two’ , etc ) ) ,

Measurements

• Meas_ID: integer primary key
• Indiv_ID: integer references Individuals
• Meas_type: text
• Measurement: real
• Image: text

From my reading of the SQLite documentation it seems that the CHECK constraint is not implemented in SQLite, which is a bit of a bummer. I’m going to have to be careful to do some data validation, for fields like Preservation and Species, where typos could lead to data not being retrieved properly in queries—either after collecting the data, or by building some validation into the R front-end I’ll need to develop for the database.

Tinkered around with RSQLite a little more, and figured out most of what I need to know in order to be able to set up these basic tables… Awesome! I think I’m ready to go. Unfortunately, there are a few more things demanding my attention today. I need to return an interlibrary loan book about automated diatom identification that might be quite useful for the mathematical morphospace project, but I haven’t read—so I need to make copies of the relevant sections, then lug it across campus to Widener library.