# Version Check
import sqlalchemy
print(sqlalchemy.__version__)

sqlite_engine_prefix_for_relative_paths = 'sqlite://'
sqlite_engine_prefix_for_absolute_paths = 'sqlite:///'

print(Path.cwd())
print(str(Path.cwd() / "example.db"))

# Create subdirectory if it doesn't exists
data_path = Path.cwd() / "data"
if not data_path.exists():
    data_path.mkdir(mode=0o777)
print(data_path.exists())
data_path.resolve()

from sqlalchemy import create_engine

create_engine_input = \
    sqlite_engine_prefix_for_absolute_paths + \
        str((data_path / "example.db").resolve())
print(create_engine_input)

# Works
#create_engine_input = \
#    sqlite_engine_prefix_for_relative_paths + \
#        "/example.db"

print(create_engine_input)

engine = \
    create_engine(create_engine_input,
                 echo=True)

"""
Explanation: Object Relational Tutorial
cf. https://docs.sqlalchemy.org/en/13/orm/tutorial.html
End of explanation
"""


from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

"""
Explanation: The return value of create_engine() is an instance of Engine, and it represents the core interface to the database.
The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the databse, which is ten used to emit the SQL. 
  - When using the ORM, we typically don't use the Engine directly once created; instead it's used behind the scenes by the ORM. 
  - lazy connecting, Engine, when first returned by create_engine(), hasn't actually tried to connect to the database yet; that happens only the first time it's asked to perform a task against the databse.
  - See also Database Urls examples of create_engine()
Declare a Mapping (between database tables and our own classes)
When using ORM, the configurational process starts by describing
database tables we're dealing with, and define own classes that'll be mapped to those tables.
These 2 tasks are usually performed together in modern SQLAlchemy, using a system known as Declarative, which allows us to create classes that include directives to describe the actual database table they'll be mapped to.
Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base, known as the declarative base class. Our application will usually have just 1 instance of this base in a commonly imported module.
Create this base class using declarative_base():
End of explanation
"""


from sqlalchemy import Column, Integer, String

class User(Base):
    # class using Declarative at minimum needs __tablename__ atttribute
    __tablename__ = 'users'

    # class using Declarative needs at least 1 Column which is part of a 
    # primary key.
    #
    # SQLAlchemy never makes any assumptions by itself about table to which
    # a class refers, including that it has no built-in conventions for
    # names, datatypes, or constraints.
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)
    
    def __repr__(self):
        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
            self.name, self.fullname, self.nickname)

"""
Explanation: cf. https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html
sqlalchemy.ext.declarative.declarative_base(bind=None, metadata=None, mapper=None, cls=&lt;class 'object'&gt;, name='Base', constructor=&lt;function_declarative_constructor&gt;, class_registry=None, metaclass=&lt;class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'&gt;
new base class will be given a metaclass that produces appropriate Table objects and makes the appropriate mapper() calls based on information provided declaratively in the class and any subclasses of the class.
Parameters:
* bind - optional Connectable, will be assigned the bind attribute on MetaData instance.
* metadata - optional MetaData instance. All Table objects implicitly declared by subclasses of base will share this MetaData.

class_registry - optional dictionary that'll serve as the registry of class names->mapped classes when string names are used to identify classes inside of relationship(), and others.

Now that we have a "base", define any number of mapped classes in terms of it.
start with
table called "users" <-> class User map to this table "users"
End of explanation
"""


User.__table__

"""
Explanation: Create your own automated conventions using helper functions and mixin classes, described in Mixin and Custom Base Classes
When class constructed, Declarative replaces all Column objects with special Python accessors known as descriptors, process known as instrumentation.
  - The "instrumented" mapped class will provide us with the means to refer to our table in a SQL context as well as to persist and load values of columns from the database.
Create a Schema
With User class constructed via Declarative system, we have defined information about our table, known as table metadata. 
Object used by SQLAlchemy to represent this information for a specific table is called Table object, and here Declarative has made 1 for us. We see this by inspecting __table__ attribute:
End of explanation
"""


Base.metadata

User.metadata

# MetaData.create_all() checks first presence of tables, in actual 
# CREATE TABLE statement
# Note, if the database hadn't been created yet, this will literally create the database in the file system.
Base.metadata.create_all(engine)

"""
Explanation: When we declared our class, Declarative also created Table object according to our specifications, and associated it with class by constructing Mapper object.
Classical Mappings, any plain Python class can be mapped to any Table using mapper() function, described in Classical Mappings
Table object is a member of larger collection known as MetaData. When using Declarative, this object is available using .metadata attribute.
End of explanation
"""


ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
print(ed_user.name)

ed_user.nickname

str(ed_user.id)

"""
Explanation: Minimal Table Descriptions vs. Full Descriptions
VARCHAR columns were generated without length on SQLite and PostgreSQL, this is a valid datatype, but not on others.
Length may be provided to String type:
Column(String(50))
The length field on String as well as similar precision/scale fields available on Integer, Numeric, etc. aren't referenced by SQLAlchemy other than when creating tables.
Additionally, Firebird and Oracle require sequences to generate primary key identifiers, and SQLAlchemy doesn't generate or assume these without being instructed.
If otherwise, use Sequence construct
```
from sqlalchemy import Sequence
Column(Integer, sequence('user_id_seq'), primary_key=True)
class User(Base):
    tablename = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(string(50))
    fullname = Column(String(50))
    nickname = Column(String(50))
def __repr__(self):
    return "&lt;Username&gt;='%s', fullname='%s', nickname='%s')&gt;" % (
        self.name, self.fullname, self.nickname)

```
Create Instance of the Mapped Class
End of explanation
"""


from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

"""
Explanation: Even though we didn't specify it in ctor, id attribute produces value None when we access it (as opposed to Python's raising AttributeError for undefined attribute). SQLAlchemy's instrumentation normally produces this default value for column-mapped attributes when 1st accessed.
Creating a Session
Start talking to database; ORM's "handle" to db is the Session. When we first set up the application, at same level as our create_engine(), we define Session class which will serve as factor for new Session objects:
End of explanation
"""


session = Session()

"""
Explanation: In the case where your application doesn't have an Engine when you define your module-level objects, just set it up like this:
Session = sessionmaker()
Later, when you create your engine with create_engine(), connect it to Session using configure():
Session.configure(bind=engine)
When do I construct a Session when do I commit it, and when do I close it?
When you need to have a conversation with the database, instantiate a Session; Session associated with our SQLite-enabled Engine, but hasn't opened any connections yet.
End of explanation
"""


ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
session.add(ed_user)

"""
Explanation: Adding and Updating Objects
To persist our User object, add() it to our Session:
End of explanation
"""


our_user = session.query(User).filter_by(name='ed').first()

our_user

# Session identified that row returned is the same row as 1 already
# represented within its internal map of objects
ed_user is our_user

"""
Explanation: At this point, instance is pending; no SQL has yet been issued and object isn't represented yet by row in the database.
  - The Session will issue SQL to persist Ed Jones as soon as needed, process known as flush
  - If we query database for Ed Jones, all pending information will be first be flushed, and query immediately issued.
e.g. below, we create a new `Query` object which loads instances of `Users`. We "filter by" `name` attribute of `ed`
End of explanation
"""


# Add more `User` objects at once using `add_all()`
session.add_all([
    User(name='wendy', fullname='Wendy Williams', nickname='windy'),
    User(name='mary', fullname='Mary Contrary', nickname='mary'),
    User(name='fred', fullname='Fred Flintstone', nickname='freddy')
])

# change Ed's nickname
ed_user.nickname = 'eddie'

# Session is paying attention, e.g.
session.dirty

# Sessino knows 3 new User objects are pending
session.new

"""
Explanation: ORM concept of identity map ensures that all operations upon a particular row within a Session operate upon same set of data.
End of explanation
"""


session.commit()

ed_user.id # Now is 1

ed_user

"""
Explanation: We tell Session we'd like to issue all remaining changes to the database and commit the transaction, which has been in progress throughout.
- do this via commit()
- e.g. Session emits UPDATE statement for the nickname change on "ed", as well as INSERT statements for 3 new User objects added
End of explanation
"""


ed_user.name = 'Edwardo'

fake_user = User(name='fakeuser', fullname='Invalid', nickname='12345')
session.add(fake_user)

# Querying session, they're flushed into current transaction
session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()

"""
Explanation: Session Object States
transient - User object moved from being outside the Session,
pending - inside the Session without primary key, to
persistent - actually being inserted
read Quickie Intro to Object States
Rolling Back
End of explanation
"""


session.rollback()

ed_user.name

fake_user in session

session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()

"""
Explanation: Rolling back, we can see that ed_user's name is back to ed, and fake_user has been kicked out of the session
End of explanation
"""


for instance in session.query(User).order_by(User.id):
    print(instance.name, instance.fullname)

for name, fullname in session.query(User.name, User.fullname):
    print(name, fullname)

for row in session.query(User, User.name).all():
    print(row.User, row.name)

for row in session.query(User.name.label('name_label')).all():
    print(row.name_label)

"""
Explanation: Querying
End of explanation
"""


from sqlalchemy.orm import aliased
user_alias = aliased(User, name='user_alias')

for row in session.query(user_alias, user_alias.name).all():
    print(row.user_alias)

"""
Explanation: The name given to a full entity such as User, assuming that multiple entities are present in the call to query(), can be controlled using aliased()
End of explanation
"""


for u in session.query(User).order_by(User.id)[1:3]:
    print(u)

# filtering results
for name, in session.query(User.name).filter_by(fullname='Ed Jones'):
    print(name)

"""
Explanation: Basic operations with Query (i.e. actual SQL commands) include LIMIT and OFFSET, most conveniently using Python array slices, in conjunction with ORDER BY:
End of explanation
"""


for name, in session.query(User.name).filter(User.fullname=='Ed Jones'):
    print(name)

# further criteria may be added
for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'):
    print(user)

"""
Explanation: or filter(), which uses more flexible SQL expression language constructs. These allow you to use regular Python operators with the class-level attributes on your mapped class:
End of explanation
"""


# equals
list(session.query(User).filter(User.name == 'ed'))

# not equals
list(session.query(User).filter(User.name != 'ed'))

# LIKE
list(session.query(User).filter(User.name.like('%ed%')))

# ILIKE (case-insensitive LIKE)
list(session.query(User).filter(User.name.ilike('%ed')))

# IN
list(session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])))

"""
Explanation: Common Filter Operators
End of explanation
"""


from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)

    # ForeignKey here expresses that values in the addresses.user_id
    # column should be constrained to those values in the users.id column,
    # i.e. its primary key.
    user_id = Column(Integer, ForeignKey('users.id'))
    
    user = relationship("User", back_populates="addresses")
    
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

# relationship tells the ORM that Address class itself should be linked to
# User class, using the attribute Address.user
User.addresses = relationship(
    "Address", order_by=Address.id, back_populates="user")

"""
Explanation: Building a Relationship
End of explanation
"""


