#!/usr/bin/env python
# coding: utf-8

# # ODM2 Example 1:  Create a Blank ODM2 Database Using DDL SQL Script

# This example shows how to create a blank ODM2 database and load the controlled vocabulary terms from http://vocabulary.odm2.org. This example uses SQLite for the database because it doesn't require a server. However, the process for creating databases using other relational database management systems is very similar. Data Definition Language scripts for creating blank instances of ODM2 are available for:
# * Microsoft SQL Server
# * MySQL
# * PostgresSQL
# * SQLite
# 
# For details of the blank schema scripts, see:  https://github.com/ODM2/ODM2/tree/master/src/blank_schema_scripts

# First import the necessary libraries

# In[ ]:


import os
import sqlite3
from IPython.core.display import display, HTML


# ### Create New ODM2 Database
# 
# Create a new, blank ODM2 SQLite database into which data can be loaded.  The DDL SQL script for creating the blank ODM2 database has been uploaded to the "utilities" folder on this Jupyter server for your convenience, but it is available in GitHub at: https://github.com/ODM2/ODM2/tree/master/src/blank_schema_scripts/sqlite
# 
# NOTE:  The output SQLite database is written to the "Data" directory on this server. Click the "Control Panel" button at the top of this window then click the "My Server" link.  Navigate to the Notebooks --> Data folder.

# In[ ]:


# First check to see if the ODM2 SQLite file already exists from previous runs of this example. If so, delete it.
if os.path.isfile('data/ODM2_Example1.sqlite'):
    os.remove('data/ODM2_Example1.sqlite')

# Create a new SQLite database and get a cursor
conn = sqlite3.connect('data/ODM2_Example1.sqlite')
c = conn.cursor()

# Open the DDL SQL file for ODM2
sqlString = open('code/ODM2_for_SQLite.sql', 'r').read()

# Execute the DDL SQL script on the blank SQLite database
c.executescript(sqlString)

# Close the connection to the database
conn.close()

print "Done creating ODM2 database!"


# ### Load Controlled Vocabulary Terms
# 
# Load the controlled vocabularies into the blank database. The cvload.py script has been uploaded to the "utilities" folder on this Jupyter server for your convenience, but it is available in GitHub at: https://github.com/ODM2/ODM2/tree/master/src/load_cvs.
# 
# The cvload.py script retrieves all of the CV terms from the online CV management system at http://vocabulary.odm2.org and loads them into the corresponding tables in the ODM2 database.

# In[ ]:


# Run the CV Loader script
get_ipython().run_line_magic('run', 'code/cvload.py sqlite:///data/ODM2_Example1.sqlite')

print "Done loading controlled vocabularies!"


# Check to make sure the CVs got loaded correctly. Do a quick query on one of the CV tables to make sure. Print one term from the SiteType CV table. Other CVs you may be interested include: CV_ActionType, CV_MethodType, CV_Medium, etc. To explore other CVs, just change the name of the CV and re-run the code block below.

# In[ ]:


conn = sqlite3.connect('data/ODM2_Example1.sqlite')
c = conn.cursor()
cvName = 'CV_SiteType'
sqlString = 'SELECT Name, Definition FROM ' + cvName
c.execute(sqlString)
rows = c.fetchall()
print rows[0]
conn.close()


# ### Result
# 
# At this point, I now have a blank ODM2 database in a SQLite file with all of the controlled vocabulary terms from http://vocabulary.odm2.org that I could load data into. Execute the following code to get a link to download the SQLite file that you just created. SQLite files can be viewed and queried using a number of different tools, including the Firefox browser SQLite Manager Program available at: https://addons.mozilla.org/En-us/firefox/addon/sqlite-manager/

# In[ ]:


print("\nYou can download the blank ODM2 SQLite database with populated CVs using the following link:\n")
dbname = 'ODM2_Example1.sqlite'
display(HTML('<a href=%s target="_blank">%s<a>' % ('data/%s' % dbname, dbname)))


# In[ ]: