# pyODBC
import pyodbc

try:
    cn1.close()
    cn2.close()
except:
    pass

# MariaDB/MySQL
conn = "DSN=tup" #"DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=pb.fri.uni-lj.si;DATABASE=sandbox;UID=tup;PWD=tupvaje"
cn1 = pyodbc.connect(conn, autocommit=False)
cn2 = pyodbc.connect(conn, autocommit=False)

# Izpis rezultatov poizvedbe (pomožna funkcija)
def tabela(rez):
  try:
    # Glava
    for g in rez.description:
        print(g[0],end="\t")
    print("\n"+"-"*31)
    # Vsebina
    for r in rez.fetchall():
        for a in r:
            print(a,end="\t")
        print()
    # Število vrstic
    print("Vseh vrstic je", rez.rowcount)
  except Exception(e):
    pass

c1=cn1.cursor()
c2=cn2.cursor()

# Nastavitve veljajo za vse nadaljnje NOVE transakcije

# MariaDB/MySQL
c1.execute("SET SESSION innodb_lock_wait_timeout = 5") # Cas v sekundah
c2.execute("SET SESSION innodb_lock_wait_timeout = 5") # Cas v sekundah

# MariaDB/MySQL
c1.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ") # Preizkusite obnašanje pri
c2.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ") # razlicnih stopnjah izolacije

# Zacnemo NOVE transakcije!
c1.commit()
c2.commit()

c1.execute("DROP TABLE IF EXISTS rezervacija123")
c1.execute("DROP TABLE IF EXISTS jadralec123")
c1.execute("""CREATE TABLE jadralec123 AS SELECT * from tup.jadralec""")
c1.execute("ALTER TABLE jadralec123 ADD PRIMARY KEY(jid)")
# c1.execute("ALTER TABLE jadralec ENGINE MYISAM") # Privzeto: InnoDB
c1.commit();

### Izpis vsebine tabele pred spremembo v obeh povezavah

c1.execute("SELECT * FROM jadralec123")
tabela(c1)

c2.execute("SELECT * FROM jadralec123 -- LOCK IN SHARE MODE") # Po potrebi briši SQL komentar --
tabela(c2)

### Sprememba tabele v prvi povezavi

try:
    c1.execute("UPDATE jadralec123  \
                SET rating = 2  \
                WHERE jid = 29")
except Exception as e:
    print(e)

###Izpis vsebine tabele po spremembi v obeh povezavah

tabela(c1.execute("SELECT * FROM jadralec123"))

tabela(c2.execute("SELECT * FROM jadralec123"))

c1.commit()

tabela(c1.execute("SELECT * FROM jadralec123"))

tabela(c2.execute("SELECT * FROM jadralec123"))

###Lost update?

# Zacnemo nove transakcije

c1.rollback()
c2.rollback()

c1.execute(""" SELECT rating  
               FROM jadralec123
               WHERE jid = 29""")
rating = c1.fetchone()[0]
print (rating)

c2.execute(""" SELECT rating  
               FROM jadralec123
               WHERE jid = 29""")
rating = c2.fetchone()[0]
print (rating)

c1.execute("UPDATE jadralec123  \
            SET rating = ?  \
            WHERE jid = 29", rating + 1)
#c1.commit()

c2.execute("UPDATE jadralec123  \
            SET rating = ?  \
            WHERE jid = 29", rating + 1)
#c2.commit()

c1.commit()
c2.commit()

tabela(c1.execute("SELECT * FROM jadralec123"))

tabela(c2.execute("SELECT * FROM jadralec123"))