#!/usr/bin/python
# -*- coding: utf-8 -*-
"""
 Looking at SqlSoup , a "do the right thing" layer on top of SqlAlchemy

 For documentation on the ORM, see
   http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/sqlsoup.html
   http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#ormtutorial-querying

 The sqlite database is summarized in ./notes.txt, and includes tables like
   Employee     (employee_id, name, position, salary, ..)
   Planet       (planet_id, name, coordinates)
   Shipment     (shipment_id, date, manager_id (FK Employee), planet_id (FK Planet))

 and so includes relations such as
   "Employee has_many shipments"     (one to many)
   "Planet has_many shipments"       (one to many)
   "each Employee has_many Planets"  (many to many)
 which are defined as methods using the .relate() method.

 The basic CRUD operations are (create, read, update, delete),
 all of which are illustrated below.

 This all works, and is very cool, but doesn't (I think) entirely have
 the oomph that you'd want in a production database, particularly
 because the object classes aren't explict, and therefore can't be
 easily extended in an application-specific way.

 I've updated this on Oct 25 to use Python-consistent upper and lower
 case, setting table names to e.g. Employee since that corresponds
 to a SqlSoup object class.

 Jim Mahoney | cs.marlboro.edu | Oct 2012 
"""

from sqlalchemy import *
from sqlalchemy.orm import backref
from sqlalchemy.ext.sqlsoup import SqlSoup

def printifmain(message = ""):
   """ print if this is the main program """
   # The idea is that all printing is suppressed if this is loaded interactively.
   #   $ ./sqlsoup.py
   #   ... print lots of stuff
   #   $ python
   #   >>> from sqlsoup import *   # print nothing
   #   >>>
   if __name__ == "__main__":
      print message

printifmain("\n====== Connecting to database ...")

db = SqlSoup('sqlite:///planet_express.db')

# The 'db' object has type <class 'sqlalchemy.ext.sqlsoup.SqlSoup'>
# and is effectively an interface to the rest of the API,
# including the SqlAlchemy db.engine, db.session objects.

# ===============================================
printifmain("\n====== executing SQL ...")

printifmain(" -"*30)
printifmain("Looking at table sqlite_master (special SqlLite meta-table)")
result = db.execute("SELECT * FROM sqlite_master;")

# result is a ResultProxy, which has methods to fetch all data, or one
# row at a time. Similar to a file handle, it will be closed after use.
# See docs.sqlalchemy.org/
#     en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy
# Methods include
#   .fetchone()
#   .fetchall()
#   .fetchmany(size=None)
#   .keys()
#   .rowcount
# It's an iterator and so can be used in a "for" loop or converted to a list.
#
# Note that sqlite text is utf8 by default, and python
# notates utf8 strings u'string', e.g. u'«Ø»' .
# 

keys = result.keys()
printifmain("result keys are {}".format(keys))

for row in result:
   # type(row) is <class 'sqlalchemy.engine.base.RowProxy'>
   # Can index fields by row[index], row['column_name'], row[db.table.column_name]
   for i in range(len(row)):
      printifmain(" {:12} : {} ".format(keys[i], row[i]))
   printifmain()

# ===============================================
printifmain("\n====== defining relations and shorter names ... ")

Shipment = db.Shipment
Planet = db.Planet
Employee = db.Employee

# WARNING: the new methods defined below will only
#          work if they are *not* column names already.

# Define methods for both sides of some many-to-one relations.
# One one side, return objects ; on the other, list of objects.
#   Employee3.shipments
#   shipment4.manager
#   Planet2.shipments
#   shipment4.Planet
Shipment.relate('manager', Employee, backref=backref("shipments"))
Shipment.relate('planet', Planet, backref=backref("shipments"))

## These reduent relations define methods for one side of the many-to-one,
## but the backref stuff above takes care of this.
# db.Employee.relate('shipments', db.Shipment)  
# db.Planet.relate('shipments', db.Shipment)    # one to many

# Now similarly define a many-to-many relationship returning
# lists on both sides, with the Shipments table as the intermediary.
#   Employee3.planets_delivered_to
#   planet2.shipment_managers
Employee.relate('planets_delivered_to', Planet,
                 secondary=Shipment._table,
                 backref=backref("shipment_managers"))

# That last code used a clue from
#   stackoverflow.com/questions/4319610/
#   sqlsoup-relate-for-many-to-many-relation-throwing-exception
# in that the secondary= needed a table, which can be accessed
# as a field within the mapped_class".

# And now to do this right, we'd define similar relations
# for all the other classes in the database.

# ===============================================
printifmain("\n====== get (i.e. read) an object (phil)")

# For each table "foo", db.foo includes methods 
# from Session.query(Table) from SqlAlchemy, i.e. things like
#         .filter( or_(db.table.column==this, db.table2.column2==that) )
#         .filter_by(key=value, key=value)
#         .order_by()
#         .get(primary_key)
#         .join()
#         .all()
#         .one()
#         .first()
#         .update()
#         .delete()
#         .insert()
#         .from_statement()  # explicit sql
#         .count()
# filter and order_by can also use sql strings

phil = Employee.filter(Employee.name.like('%Phil%')).first()

# That was pretty awkward.
# Ruby active record would have Employee.find_by_name("...")
# and many similar automatically defined methods for each column.

# or
# john = db.Employee.filter_by(name="John A. Zoidberg").one()
## Note: .one() throws an error if > 1 returned

printifmain("Looking at Phil's shipments")
for s in phil.shipments:
   printifmain(" to planet {} on {} ".format(s.planet.name, s.date))

printifmain("Planets that Phil delivers to")
printifmain(map(lambda x: x.name, phil.planets_delivered_to))

# ===============================================
printifmain("\n====== create / delete")

printifmain("Creating Paul Bunyon ...")
paul = Employee(name="Paul Bunyon", salary=2, position="chopper")
db.flush()
# or
# db.Employee.insert(name="Paul Bunyon", salary=2, position="chopper")

# check to make sure his info is really in the database :
pauls_position = db.execute(
   "SELECT position FROM Employee WHERE name like '%Bunyon%'"
   ).fetchall()[0]

printifmain("He's a {}.".format(str(pauls_position[0])))

printifmain("Deleting Paul Bunyon ...")
db.delete(paul)
db.flush()
# or
# db.Employee.delete(db.Employee.name=="Paul Bunyon")

# Note that changes to the database take effect after db.flush() or db.commit().
# (The latter is transaction oriented; same (I think) in this case.)

# ===============================================
printifmain("\n====== updating a record")

# p = db.package.get(1)
# p.contents = "Stuff"
# db.flush()

printifmain("Phil's first Shipment ... ")
s0 = phil.shipments[0]
printifmain("was shipped by {} (duh)".format(s0.manager.name))
p0 = s0.planet
printifmain("to planet {}".format(p0.name))
printifmain("which has id = {}".format(p0.planet_id))

amazonia = Planet.filter_by(name="Amazonia").one()
printifmain("Changing to amazonia with id={}...".format(amazonia.planet_id))

s0.planet = amazonia
db.flush()

# Notice how that last bit did something that's really pretty cool.
# s0 is a shipment object, which has a planet_id field.
# amazonia is an Planet object, which also has a planet_id field.
# So really what's happening is s0.planet_id = amazonia.planet_id .
# In fact, shipment doesn't even *have* a "Planet" field.
# ... but because the relations are understood, the software
# just "does the right thing."

# check to make sure that new info is really in the database :
planet_new_name = db.execute(
   "SELECT Planet_id FROM Shipment WHERE shipment_id={}".format(s0.shipment_id)
   ).fetchall()[0]
printifmain("Shipment's Planet new id = {}".format(planet_new_name[0]))

printifmain("And changing it back ...")
s0.planet = p0
db.flush()

# ========================================
printifmain("\nOK, we're done.")