From: Michael Rasmussen Date: Wed, 16 May 2018 06:28:46 +0000 (+0200) Subject: Half way through migration away from sqlalchemy X-Git-Url: http://git.datanom.net/pwp.git/commitdiff_plain Half way through migration away from sqlalchemy Signed-off-by: Michael Rasmussen --- diff --git a/app/DB/__init__.py b/app/DB/__init__.py index c0d9c3d..8b13789 100644 --- a/app/DB/__init__.py +++ b/app/DB/__init__.py @@ -1 +1 @@ -from DB import baseorm + diff --git a/app/DB/base.py b/app/DB/base.py index cf3e11a..5f1d501 100644 --- a/app/DB/base.py +++ b/app/DB/base.py @@ -1,12 +1,17 @@ +import logging +logging.basicConfig(level=logging.INFO) +logger = logging.getLogger(__name__) + class Base(object): - def query(self, sql): + def query(self, sql, placeholders): res = None cur = self.conn.cursor() try: - cur.execute(sql) + cur.execute(sql, placeholders) res = cur.fetchall() - except: + except Exception as e: + logger.error("{0}".format(e)) pass finally: cur.close() diff --git a/app/DB/baseorm.py b/app/DB/baseorm.py index ef7962e..1d34536 100644 --- a/app/DB/baseorm.py +++ b/app/DB/baseorm.py @@ -1,6 +1,9 @@ import abc -class OrmSubject(metaclass=abc.ABCMeta): +class QueryException(Exception): + pass + +class OrmSubject(object, metaclass=abc.ABCMeta): @abc.abstractmethod def addObserver(self, ormObserver): pass @@ -13,7 +16,7 @@ class OrmSubject(metaclass=abc.ABCMeta): def notify(self, subject): pass -class Observer(metaclass=abc.ABCMeta): +class Observer(object, metaclass=abc.ABCMeta): @abc.abstractmethod def update(self, subject): pass @@ -22,6 +25,7 @@ class BaseOrm(OrmSubject): def __init__(self): self.__observers = [] + self.__oldValues = {} def addObserver(self, ormObserver): print("Added: {0}".format(ormObserver)) @@ -34,20 +38,44 @@ class BaseOrm(OrmSubject): for observer in self.__observers: observer.update(object) + @classmethod + def query(cls, **kwargs): + objects = [] + from app import db + if kwargs: + if hasattr(cls, '__tablename__'): + table = cls.__tablename__ + else: + table = cls.__name__ + o = db.query(table, **kwargs) + if table.lower() == 'user': + for user in o: + objects.append(cls(user[1], user[2], user[3], user[4], user[0])) + elif table.lower() == 'portfolio': + for portfolio in o: + user = db.query('user', id = portfolio[2])[0] + objects.append(cls(portfolio[1], user, portfolio[3], portfolio[4], portfolio[0])) + else: + raise QueryException("{0}: Missing at least one query parameter".format(cls)) + return objects + + def commit(self): + self.notify(self) + self.__oldValues = {} + + def rollback(self): + for k, v in self.__oldValues.items(): + setattr(self, k, v) + self.__oldValues = {} + @property def id(self): return self.__id @id.setter def id(self, id): - notify = True try: - old = self.id - notify = False - except AttributeError as e: + self.__oldValues['id'] = self.id + except AttributeError: pass -# print(e) -# notify = False self.__id = id - if notify: - self.notify(self) diff --git a/app/DB/db.py b/app/DB/db.py index b381459..08f8da2 100644 --- a/app/DB/db.py +++ b/app/DB/db.py @@ -1,17 +1,24 @@ -import sys -sys.path.append('..') -sys.path.append('../..') -from models import User, Portfolio, Album, Photo, AccessRight from enum import IntEnum, unique import inspect +from abc import ABCMeta from baseorm import Observer +import logging +logging.basicConfig(level=logging.INFO) +logger = logging.getLogger(__name__) @unique class DBDriver(IntEnum): PG = 1 MySQL = 2 - -class DB(Observer): + +class Singleton(ABCMeta): + _instances = {} + def __call__(cls, password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None): + if cls not in cls._instances: + cls._instances[cls] = super(Singleton, cls).__call__(password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None) + return cls._instances[cls] + +class DB(Observer, metaclass=Singleton): __filename__ = None def __init__(self, password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None): @@ -31,6 +38,7 @@ class DB(Observer): if self.driver is DBDriver.PG: from postgres import Postgres try: + logger.info("Connect: database: {0} user: {1} host: {2} port: {3}".format(self.database, self.user, self.host, self.port)) self.db = Postgres(self.database, self.user, self.password, self.host, self.port) except Exception as e: raise Exception(e) @@ -44,7 +52,6 @@ class DB(Observer): raise Exception("%s: Unknown DB driver" % self.driver) def update(self, subject): - print("update: {0}".format(subject)) self.store(subject) def initDb(self): @@ -57,6 +64,30 @@ class DB(Observer): except: raise + def query(self, object, **kwargs): + if kwargs: + if object.lower() == 'user' and self.driver is DBDriver.PG: + sql = 'select * from "{0}" where '.format(object) + else: + sql = 'select * from {0} where '.format(object) + placeholder = [] + clause = None + for name, value in kwargs.items(): + if name.lower() == 'user' and self.driver is DBDriver.PG: + attr = '"{0}"'.format(name) + else: + attr = name + if clause: + clause += ' and {0} = %s '.format(attr) + else: + clause = '{0} = %s'.format(attr) + placeholder.append(value) + sql += clause + logger.info("{0} -> {1}".format(sql, placeholder)) + return self.db.query(sql, placeholder) + else: + raise Exception("{0}: Missing at least one query parameter".format(object)) + def store(self, object): if inspect.isclass(object): raise Exception("{0}: Class not instance".format(object)) @@ -66,10 +97,7 @@ class DB(Observer): else: table = object.__class__.__name__ - print("Table<{0}>".format(table)) v = [i for i in dir(object) if isinstance(getattr(type(object), i, None), property)] - for p in v: - print("{0}: {1}".format(p, getattr(object, p))) action = None column = [] @@ -92,7 +120,6 @@ class DB(Observer): else: values.append(value) column.append(p) - print(action) if action == 'insert': if table.lower() == 'user' and self.driver is DBDriver.PG: sql = 'insert into "' + table + '" (' @@ -127,9 +154,6 @@ class DB(Observer): sql += ' where id = {0}'.format(id) self.db.update(sql, values) - print(sql) - print(values) - def __repr__(self): default = '<%s.%s object at %s>' % (self.__class__.__module__, self.__class__.__name__, hex(id(self))) return "%s " % (default, self.driver.name, self.user, self.database) @@ -158,22 +182,26 @@ class Test(object): else: self.__x = x -db = DB('test', DBDriver.MySQL) -#db = DB('test') -db.initDb() +if __name__ == "__main__": + import sys + sys.path.append('../..') + from app.models import User, Portfolio, Album, Photo, AccessRight + #db = DB('test', DBDriver.MySQL) + db = DB('test') + db.initDb() -user = User('test', 'test@test.dk', 'test', 'test') -user.addObserver(db) -print(user) -db.store(user) -portfolio = Portfolio('test', user) -print(portfolio) -album = Album('test', portfolio) -print(album) -accessright = AccessRight(user) -print(accessright) -photo = Photo('test', album) -print(photo) -db.store(portfolio) -user.name = 'MIR' -del db + user = User('test', 'test@test.dk', 'test', 'sha256$1HX2n73E$ac27f843b4342df7b6c12e5ac340e063ea958d52ce62c3883c124385c96b263a') + user.addObserver(db) + print(user) + db.store(user) + portfolio = Portfolio('test', user) + print(portfolio) + album = Album('test', portfolio) + print(album) + accessright = AccessRight(user) + print(accessright) + photo = Photo('test', album) + print(photo) + db.store(portfolio) + user.name = 'MIR' + del db diff --git a/app/DB/db_mysql.sql b/app/DB/db_mysql.sql index 8a27eab..a3a14f5 100644 --- a/app/DB/db_mysql.sql +++ b/app/DB/db_mysql.sql @@ -58,9 +58,9 @@ CREATE TABLE album name varchar(255), visible boolean NOT NULL, public boolean NOT NULL, - portfolioid integer NOT NULL, + portfolio integer NOT NULL, CONSTRAINT album_pkey PRIMARY KEY (id), - CONSTRAINT album_portfolioid_fkey FOREIGN KEY (portfolioid) + CONSTRAINT album_portfolio_fkey FOREIGN KEY (portfolio) REFERENCES portfolio (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = InnoDB; @@ -82,11 +82,11 @@ CREATE TABLE photo name varchar(255), exif varchar(255), format integer NOT NULL, - albumid integer NOT NULL, + album integer NOT NULL, visible boolean NOT NULL, public boolean NOT NULL, CONSTRAINT photo_pkey PRIMARY KEY (id), - CONSTRAINT photo_albumid_fkey FOREIGN KEY (albumid) + CONSTRAINT photo_album_fkey FOREIGN KEY (album) REFERENCES album (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT photo_format_fkey FOREIGN KEY (format) diff --git a/app/DB/db_postgres.sql b/app/DB/db_postgres.sql index e302dcf..1b8ac79 100644 --- a/app/DB/db_postgres.sql +++ b/app/DB/db_postgres.sql @@ -107,9 +107,9 @@ CREATE TABLE public.album name character varying(255), visible boolean NOT NULL, public boolean NOT NULL, - portfolioid integer NOT NULL, + portfolio integer NOT NULL, CONSTRAINT album_pkey PRIMARY KEY (id), - CONSTRAINT album_portfolioid_fkey FOREIGN KEY (portfolioid) + CONSTRAINT album_portfolio_fkey FOREIGN KEY (portfolio) REFERENCES public.portfolio (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); @@ -149,11 +149,11 @@ CREATE TABLE public.photo name character varying(255), exif text, format integer NOT NULL, - albumid integer NOT NULL, + album integer NOT NULL, visible boolean NOT NULL, public boolean NOT NULL, CONSTRAINT photo_pkey PRIMARY KEY (id), - CONSTRAINT photo_albumid_fkey FOREIGN KEY (albumid) + CONSTRAINT photo_album_fkey FOREIGN KEY (album) REFERENCES public.album (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT photo_format_fkey FOREIGN KEY (format) diff --git a/app/DB/postgres.py b/app/DB/postgres.py index ce2ef23..6a67caa 100644 --- a/app/DB/postgres.py +++ b/app/DB/postgres.py @@ -6,6 +6,6 @@ class Postgres(Base): if port is None: port = 5432 try: - self.conn = psycopg2.connect("dbname=%s user=%s password=%s host=%s port=%d" % (database, user, password, host, port)) + self.conn = psycopg2.connect("dbname={0} user={1} password={2} host={3} port={4}".format(database, user, password, host, port)) except psycopg2.Error as e: raise Exception(e) diff --git a/app/__init__.py b/app/__init__.py index e5fd11d..195f77b 100644 --- a/app/__init__.py +++ b/app/__init__.py @@ -1,9 +1,8 @@ from flask import Flask from flask_bootstrap import Bootstrap -from config import ADMINS, MAIL_SERVER, MAIL_PORT, MAIL_USERNAME, MAIL_PASSWORD +from config import DB_DRIVER, DB_PASSWORD, DB_USER, DB_DATABASE, DB_HOST, DB_PORT from flask_login import LoginManager from flask_wtf.csrf import CSRFProtect - app = Flask(__name__) app.config.from_object('config') lm = LoginManager() @@ -13,25 +12,8 @@ bootstrap = Bootstrap(app) csrf = CSRFProtect() csrf.init_app(app) -if not app.debug: - import logging - - from logging.handlers import SMTPHandler, RotatingFileHandler - credentials = None - if MAIL_USERNAME or MAIL_PASSWORD: - credentials = (MAIL_USERNAME, MAIL_PASSWORD) - mail_handler = SMTPHandler((MAIL_SERVER, MAIL_PORT), 'no-reply@' + MAIL_SERVER, ADMINS, 'PWP failure', credentials) - mail_handler.setLevel(logging.ERROR) - mail_handler.setFormatter(logging.Formatter('%(asctime)s %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d]')) - app.logger.addHandler(mail_handler) - - file_handler = RotatingFileHandler('log/pwp.log', 'a', 1 * 1024 * 1024, 10) - file_handler.setFormatter(logging.Formatter('%(asctime)s %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d]')) - app.logger.addHandler(file_handler) - - app.logger.setLevel(logging.INFO) - file_handler.setLevel(logging.INFO) - app.logger.info('pwp startup') +from db import DB +db = DB(DB_PASSWORD, DB_DRIVER, DB_USER, DB_DATABASE, DB_HOST, DB_PORT) from app import views, models lm.anonymous_user = models.MyAnonymous diff --git a/app/models.py b/app/models.py index 3171baf..5c9f74e 100644 --- a/app/models.py +++ b/app/models.py @@ -1,7 +1,8 @@ from config import ADMINS from enum import IntEnum -import DB -#from flask_login import mixins +from baseorm import BaseOrm +from flask_login import mixins +from app import app class Format(IntEnum): jpg = 1 @@ -13,7 +14,7 @@ class Role(IntEnum): write = 2 admin = 3 -class User(DB.baseorm.BaseOrm): +class User(BaseOrm): __tablename__ = 'user' @property @@ -22,8 +23,11 @@ class User(DB.baseorm.BaseOrm): @name.setter def name(self, name): + try: + self.__oldValues['name'] = self.name + except AttributeError: + pass self.__name = name - self.notify(self) @property def email(self): @@ -31,8 +35,11 @@ class User(DB.baseorm.BaseOrm): @email.setter def email(self, email): + try: + self.__oldValues['email'] = self.email + except AttributeError: + pass self.__email = email - self.notify(self) @property def username(self): @@ -40,8 +47,11 @@ class User(DB.baseorm.BaseOrm): @username.setter def username(self, username): + try: + self.__oldValues['username'] = self.username + except AttributeError: + pass self.__username = username - self.notify(self) @property def password(self): @@ -49,13 +59,16 @@ class User(DB.baseorm.BaseOrm): @password.setter def password(self, password): + try: + self.__oldValues['password'] = self.password + except AttributeError: + pass self.__password = password - self.notify(self) - def __init__(self, name, email, username, password): + def __init__(self, name, email, username, password, id = -1): super().__init__() self.email = email - self.id = -1 + self.id = id self.name = name self.username = username self.password = password @@ -68,14 +81,17 @@ class User(DB.baseorm.BaseOrm): def is_anonymous(self): return False - + + def get_id(self): + return self.id + def is_admin(self): return self.email in ADMINS def __repr__(self): return '' % (self.id, self.name, self.email, self.username) -class Portfolio(DB.baseorm.BaseOrm): +class Portfolio(BaseOrm): __tablename__ = 'portfolio' @property @@ -84,6 +100,10 @@ class Portfolio(DB.baseorm.BaseOrm): @name.setter def name(self, name): + try: + self.__oldValues['name'] = self.name + except AttributeError: + pass self.__name = name @property @@ -92,6 +112,10 @@ class Portfolio(DB.baseorm.BaseOrm): @public.setter def public(self, public): + try: + self.__oldValues['public'] = self.public + except AttributeError: + pass self.__public = public @property @@ -100,6 +124,10 @@ class Portfolio(DB.baseorm.BaseOrm): @visible.setter def visible(self, visible): + try: + self.__oldValues['visible'] = self.visible + except AttributeError: + pass self.__visible = visible @property @@ -108,25 +136,34 @@ class Portfolio(DB.baseorm.BaseOrm): @user.setter def user(self, user): + try: + self.__oldValues['user'] = self.user + except AttributeError: + pass self.__user = user - def __init__(self, name, user): + def __init__(self, name, user, public = False, visible = False, id = -1): super().__init__() - self.id = -1 + self.id = id self.name = name - self.public = False - self.visible = False - if not isinstance(user, User): + self.public = public + self.visible = visible + app.logger.info("User: {0}".format(user)) + if (isinstance(user, User)): + u = user + else: + u = User(user[1], user[2], user[3], user[4], user[0]) + if not isinstance(u, User): raise Exception("{0}: Not a 'User'".format(user)) - self.user = user + self.user = u def set_user_count(self, usercount): self.usercount = usercount def __repr__(self): - return '' % (self.name, self.user) + return '' % (self.id, self.name, self.user) -class Album(DB.baseorm.BaseOrm): +class Album(BaseOrm): __tablename__ = 'album' @property @@ -135,6 +172,10 @@ class Album(DB.baseorm.BaseOrm): @name.setter def name(self, name): + try: + self.__oldValues['name'] = self.name + except AttributeError: + pass self.__name = name @property @@ -143,6 +184,10 @@ class Album(DB.baseorm.BaseOrm): @public.setter def public(self, public): + try: + self.__oldValues['public'] = self.public + except AttributeError: + pass self.__public = public @property @@ -151,6 +196,10 @@ class Album(DB.baseorm.BaseOrm): @visible.setter def visible(self, visible): + try: + self.__oldValues['visible'] = self.visible + except AttributeError: + pass self.__visible = visible @property @@ -159,6 +208,10 @@ class Album(DB.baseorm.BaseOrm): @portfolio.setter def portfolio(self, portfolio): + try: + self.__oldValues['portfolio'] = self.portfolio + except AttributeError: + pass self.__portfolio = portfolio def __init__(self, name, portfolio): @@ -177,7 +230,7 @@ class Album(DB.baseorm.BaseOrm): def __repr__(self): return '' % (self.name, self.portfolio) -class Photo(DB.baseorm.BaseOrm): +class Photo(BaseOrm): __tablename__ = 'photo' @property @@ -186,6 +239,10 @@ class Photo(DB.baseorm.BaseOrm): @name.setter def name(self, name): + try: + self.__oldValues['name'] = self.name + except AttributeError: + pass self.__name = name @property @@ -194,6 +251,10 @@ class Photo(DB.baseorm.BaseOrm): @public.setter def public(self, public): + try: + self.__oldValues['public'] = self.public + except AttributeError: + pass self.__public = public @property @@ -202,6 +263,10 @@ class Photo(DB.baseorm.BaseOrm): @visible.setter def visible(self, visible): + try: + self.__oldValues['visible'] = self.visible + except AttributeError: + pass self.__visible = visible @property @@ -210,6 +275,10 @@ class Photo(DB.baseorm.BaseOrm): @album.setter def album(self, album): + try: + self.__oldValues['album'] = self.album + except AttributeError: + pass self.__album = album @property @@ -218,6 +287,10 @@ class Photo(DB.baseorm.BaseOrm): @file.setter def file(self, file): + try: + self.__oldValues['file'] = self.file + except AttributeError: + pass self.__file = file @property @@ -226,6 +299,10 @@ class Photo(DB.baseorm.BaseOrm): @exif.setter def exif(self, exif): + try: + self.__oldValues['exif'] = self.exif + except AttributeError: + pass self.__exif = exif @property @@ -234,6 +311,10 @@ class Photo(DB.baseorm.BaseOrm): @format.setter def format(self, format): + try: + self.__oldValues['format'] = self.format + except AttributeError: + pass self.__format = format def __init__(self, name, album): @@ -252,7 +333,7 @@ class Photo(DB.baseorm.BaseOrm): def __repr__(self): return '' % (self.name, self.file, self.format, self.album) -class AccessRight(DB.baseorm.BaseOrm): +class AccessRight(BaseOrm): __tablename__ = 'accessright' @property @@ -261,6 +342,10 @@ class AccessRight(DB.baseorm.BaseOrm): @perm.setter def perm(self, perm): + try: + self.__oldValues['perm'] = self.perm + except AttributeError: + pass self.__perm = perm @property @@ -269,6 +354,10 @@ class AccessRight(DB.baseorm.BaseOrm): @user.setter def user(self, user): + try: + self.__oldValues['user'] = self.user + except AttributeError: + pass self.__user = user def __init__(self, user): @@ -282,22 +371,22 @@ class AccessRight(DB.baseorm.BaseOrm): def __repr__(self): return '' % (self.perm, self.user) -#class MyAnonymous(mixins.AnonymousUserMixin): -# id = -1 -# name = 'Anonymous User' -# email = 'no@email.com' -# username = 'None' -# -# @property -# def is_authenticated(self): -# return False -# -# @property -# def is_admin(self): -# return False -# -# def get_id(self): -# return self.id -# -# def __repr__(self): -# return '' % (self.name, self.email, self.username) +class MyAnonymous(mixins.AnonymousUserMixin): + id = -1 + name = 'Anonymous User' + email = 'no@email.com' + username = 'None' + + @property + def is_authenticated(self): + return False + + @property + def is_admin(self): + return False + + def get_id(self): + return self.id + + def __repr__(self): + return '' % (self.name, self.email, self.username) diff --git a/app/templates/user.html b/app/templates/user.html index e5b0da1..0f7d46c 100644 --- a/app/templates/user.html +++ b/app/templates/user.html @@ -110,7 +110,7 @@ $(document).on('click', '#deleteResponse', function () { {{p['portfolio']['name']}} {{p['portfolio']['visible']}} {{p['portfolio']['public']}} - {{p['portfolio']['users']}} + {{p['portfolio']['usercount']}}
@@ -128,7 +128,7 @@ $(document).on('click', '#deleteResponse', function () { {{p['portfolio']['name']}} {{a.name}} {{a.visible}} {{a.public}} - {{a.users}} + {{a.usercount}}
diff --git a/app/tools.py b/app/tools.py index e930a2e..f8e05d5 100644 --- a/app/tools.py +++ b/app/tools.py @@ -1,4 +1,5 @@ from .models import User, Portfolio, Album, Photo, AccessRight, Role, MyAnonymous +from app import app #from flask_login import mixins class DBQueryException(Exception): @@ -10,11 +11,11 @@ class DBQuery: albums = [] if hasattr(object, '__tablename__'): if isinstance(object, User): - portfolios = Portfolio.query.filter_by(owner=object) + portfolios = Portfolio.query(user=object.id) for p in portfolios: albums.extend(Album.query.filter_by(portfolio=p)) elif isinstance(object, Portfolio): - albums = Album.query.filter_by(portfolio=object) + albums = Album.query(portfolio=object.id) elif isinstance(object, Photo): albums.append(object.album) else: @@ -30,7 +31,7 @@ class DBQuery: portfolios = [] if hasattr(object, '__tablename__'): if isinstance(object, User): - portfolios = Portfolio.query.filter_by(owner=object) + portfolios = Portfolio.query(user=object.id) elif isinstance(object, Album): portfolios.append(object.portfolio) elif isinstance(object, Photo): @@ -48,14 +49,14 @@ class DBQuery: def get_owner(self, object): if hasattr(object, '__tablename__'): if isinstance(object, Portfolio): - return object.owner + return object.user elif isinstance(object, Album): p = object.portfolio - return p.owner + return p.user elif isinstance(object, Photo): a = object.album p = a.portfolio - return p.owner + return p.user else: raise DBQueryException("%s: not supported" % object.__tablename__) else: @@ -65,6 +66,7 @@ class DBQuery: if hasattr(object, '__tablename__'): users = [] if isinstance(object, Album): + # TODO: get rit of query.join objects = AccessRight.query.join(AccessRight.album).filter(Album.id == object.id) for o in objects: users.append(o.user) @@ -229,7 +231,7 @@ class DBQuery: # Last get all visible and public albums u = MyAnonymous() temp = self.get_albums_for_user(u) - print("1) Other albums: %s" % temp) + app.logger.info("1) Other albums: %s" % temp) albs = [] portfolios = self.get_portfolios(user) for p in portfolios: @@ -240,10 +242,10 @@ class DBQuery: if not hidden and not a.visible: continue albums.append(a) - print("2) Own albums: %s" % albums) + app.logger.info("2) Own albums: %s" % albums) # Remove own albums temp = list(set(temp) - set(albs)) - print("3) Other albums: %s" % temp) + app.logger.info("3) Other albums: %s" % temp) # Merge albums with temp removing duplicates albums = albums + temp else: @@ -251,10 +253,10 @@ class DBQuery: # Anonymous user if user.is_anonymous: # Find all albums which is public and visible - albums = Album.query.filter_by(public=True,visible=True).all() + albums = Album.query(public=True, visible=True) except AttributeError: raise DBQueryException("%s: Not a User object" % user) - print("Anonymous albums: %s" % albums) + app.logger.info("Anonymous albums: %s" % albums) return albums def is_safe_url(target): diff --git a/app/views.py b/app/views.py index 768786e..170d4e0 100644 --- a/app/views.py +++ b/app/views.py @@ -1,6 +1,6 @@ from flask import render_template, flash, redirect, session, url_for, request, g, abort from flask_login import login_user, logout_user, current_user, login_required -from app import app, lm, tools +from app import app, lm, tools, db from .forms import LoginForm, RegisterForm, UpdateForm, PwForm, SearchForm, DeleteForm, PortfolioForm, AlbumForm from .models import User, Portfolio, Album, AccessRight, Role, MyAnonymous from werkzeug.security import generate_password_hash, check_password_hash @@ -21,7 +21,7 @@ def before_request(): @lm.user_loader def load_user(id): - return User.query.get(int(id)) + return User.query(id=id)[0] @app.route('/') @app.route('/index') @@ -37,14 +37,15 @@ def login(): return redirect(request.referrer)#redirect(url_for('index')) form = LoginForm() if form.validate_on_submit(): - user = User.query.filter_by(username=form.username.data).first() + user = User.query(username=form.username.data)[0] if user is None: flash('Unknown username. Please try again or register.') else: - app.logger.info("db-pwd: %s form-pwd: %s" % (user.password, form.password.data)) if check_password_hash(user.password, form.password.data): app.logger.info("Login: %s" % user) login_user(user, remember=False) + g.user = user + user.addObserver(db) next = request.args.get('next') if next is not None: if not tools.is_safe_url(next): @@ -110,18 +111,14 @@ def album(): query = tools.DBQuery() portfolios = query.get_portfolios(u) new_album = Album(name=form.name.data, public=form.public.data, visible=form.visible.data, portfolio_id=portfolios[0].id) - db.session.add(new_album) - db.session.commit() + db.store(new_album) app.logger.warning("Created album: %s" % new_album) except DBException as ex: - db.session.rollback() flash(ex) - except exc.IntegrityError as ex: - db.session.rollback() - flash('Create album failed: %s' % ex) except Exception as ex: - db.session.rollback() - flash("Unknown error {0}".format(ex)) + flash('Create album failed: %s' % ex) +# except Exception as ex: +# flash("Unknown error {0}".format(ex)) return redirect(request.referrer) return render_template('album.html', title='Create Album', @@ -163,76 +160,74 @@ def user(username): uname=form.username.data email=form.email.data name=form.name.data - user = User.query.filter_by(username=username).first() + user = User.query(username=username)[0] if uname != username: - u = User.query.filter_by(username=uname).first() + u = User.query(username=uname)[0] if u is not None: raise DBException("%s: Username exist" % uname) user.username = uname referrer = "/user/%s" % user.username if email != user.email: - e = User.query.filter_by(email=email).first() + e = User.query(email=email)[0] if e is not None: raise DBException("%s: Email exist" % email) user.email = email if name != user.name: user.name = name except DBException as ex: - db.session.rollback() + user.rollback() flash("{0}".format(ex)) app.logger.warning("Update user failed: {0}".format(ex)) - except exc.IntegrityError as ex: - db.session.rollback() + except Exception as ex: + user.rollback() flash("Update user failed: {0}".format(ex)) app.logger.warning("Update user failed: {0}".format(ex)) - except Exception as ex: - db.session.rollback() - flash("Unknown error {0}".format(ex)) - app.logger.warning("Update user failed: Unknown error {0}".format(ex)) +# except Exception as ex: +# db.rollback() +# flash("Unknown error {0}".format(ex)) +# app.logger.warning("Update user failed: Unknown error {0}".format(ex)) else: try: - db.session.commit() + user.commit() login_user(user, remember=False) app.logger.warning("Updated user: %s" % user) flash("Userdata successfully updated") - except exc.IntegrityError as ex: - db.session.rollback() - flash("Update user failed: {0}".format(ex)) except Exception as ex: - db.session.rollback() - flash("Unknown error {0}".format(ex)) + flash("Update user failed: {0}".format(ex)) +# except Exception as ex: +# flash("Unknown error {0}".format(ex)) return redirect(referrer) elif pwform.pwchange.data and pwform.validate_on_submit(): - user = User.query.filter_by(username=username).first() + user = User.query(username=username)[0] if pwform.password.data == pwform.passwordchk.data and check_password_hash(user.password, pwform.passwordcur.data): hashed_password = generate_password_hash(pwform.password.data, method='sha256') user.password = hashed_password try: - db.session.commit() + user.commit() login_user(user, remember=False) app.logger.warning("Updated user - password: %s" % user) flash("Password successfully changed") - except exc.IntegrityError as ex: - db.session.rollback() - flash("Update user failed: {0}".format(ex)) except Exception as ex: - db.session.rollback() - flash("Unknown error {0}".format(ex)) + flash("Update user failed: {0}".format(ex)) +# except Exception as ex: +# flash("Unknown error {0}".format(ex)) else: flash('Current password does not match or password different from password check') return redirect(referrer) else: - user = User.query.filter_by(username=username).first() + user = User.query(username=username)[0] if user is None: flash('User %s not found.' % username) return redirect(url_for('index')) app.logger.info("Show profile: %s" % user) query = tools.DBQuery() portfolios = query.get_portfolios(user) + app.logger.info("Portfolios: {0}".format(portfolios)) private = [] for p in portfolios: albums = query.get_albums(p) - p.set_user_count(len(query.get_users(p))) + #p.set_user_count(len(query.get_users(p))) + p.set_user_count(1) a1 = [] for a in albums: a.set_user_count(len(query.get_users(a))) @@ -273,36 +268,31 @@ def register(): hashed_password = generate_password_hash(form.password.data, method='sha256') new_user = User(name=form.name.data, username=form.username.data, email=form.email.data, password=hashed_password) except DBException as ex: - db.session.rollback() flash(ex) - except exc.IntegrityError as ex: - db.session.rollback() - flash('Create user failed: %s' % ex) except Exception as ex: - db.session.rollback() - flash("Unknown error {0}".format(ex)) + flash('Create user failed: %s' % ex) +# except Exception as ex: +# flash("Unknown error {0}".format(ex)) else: try: portfolio = Portfolio(name = new_user.name, owner = new_user) - db.session.add(portfolio) + #db.session.add(portfolio) new_user.portfolios.append(portfolio) - db.session.add(new_user) + #db.session.add(new_user) acl = AccessRight(right = Role.read, user = new_user) - db.session.add(acl) + #db.session.add(acl) acl = AccessRight(right = Role.write, user = new_user) - db.session.add(acl) + #db.session.add(acl) acl = AccessRight(right = Role.admin, user = new_user) - db.session.add(acl) - db.session.commit() + #db.session.add(acl) + #db.session.commit() app.logger.warning("Registered: %s" % new_user) flash("You have been registered with username: " + form.username.data + os.linesep) flash("Default Portfolio: " + portfolio.name) return redirect(url_for('login')) - except exc.IntegrityError as ex: - db.session.rollback() + except Exception as ex: flash('Create user failed: %s' % ex) except Exception as ex: - db.session.rollback() flash("Unknown error {0}".format(ex)) else: flash('Password did not match password check') diff --git a/config.py b/config.py index 0f573ff..1e35193 100644 --- a/config.py +++ b/config.py @@ -1,5 +1,15 @@ -import os +import os, sys basedir = os.path.abspath(os.path.dirname(__file__)) +sys.path.append('..') +sys.path.append(basedir + '/app/DB') + +from db import DBDriver +DB_DRIVER = DBDriver.PG +DB_PASSWORD = 'test' +DB_USER = 'pwp' +DB_DATABASE = 'pwp' +DB_HOST = 'localhost' +DB_PORT = 5432 WTF_CSRF_ENABLED = True SECRET_KEY = os.urandom(32) diff --git a/pwp.e4p b/pwp.e4p index c7e0a2b..5f59bb9 100644 --- a/pwp.e4p +++ b/pwp.e4p @@ -1,7 +1,7 @@ - + @@ -14,11 +14,14 @@ .gitignore + app/DB/__init__.py app/DB/base.py + app/DB/baseorm.py app/DB/db.py app/DB/db_mysql.sql app/DB/db_postgres.sql app/DB/mysqld.py + app/DB/observer.py app/DB/postgres.py app/__init__.py app/forms.py diff --git a/run.py b/run.py index ea7ae15..1812f48 100755 --- a/run.py +++ b/run.py @@ -1,5 +1,33 @@ #!flask/bin/python from app import app -app.jinja_env.auto_reload = True -app.config['TEMPLATES_AUTO_RELOAD'] = True -app.run(debug=True) +from config import ADMINS, MAIL_SERVER, MAIL_PORT, MAIL_USERNAME, MAIL_PASSWORD +import logging + +app.debug = True + +if not app.debug: + from logging.handlers import SMTPHandler, RotatingFileHandler + credentials = None + if MAIL_USERNAME or MAIL_PASSWORD: + credentials = (MAIL_USERNAME, MAIL_PASSWORD) + mail_handler = SMTPHandler((MAIL_SERVER, MAIL_PORT), 'no-reply@' + MAIL_SERVER, ADMINS, 'PWP failure', credentials) + mail_handler.setLevel(logging.ERROR) + mail_handler.setFormatter(logging.Formatter('%(asctime)s %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d]')) + app.logger.addHandler(mail_handler) + + file_handler = RotatingFileHandler('log/pwp.log', 'a', 1 * 1024 * 1024, 10) + file_handler.setFormatter(logging.Formatter('%(asctime)s %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d]')) + app.logger.addHandler(file_handler) + file_handler.setLevel(logging.DEBUG) +else: +# from flask_debugtoolbar import DebugToolbarExtension + +# toolbar = DebugToolbarExtension(app) + app.jinja_env.auto_reload = True + app.config['TEMPLATES_AUTO_RELOAD'] = True + +app.logger.setLevel(logging.DEBUG) +app.logger.info('pwp startup') +#from werkzeug.security import generate_password_hash +#print (generate_password_hash('test', method='sha256')) +app.run() diff --git a/setup.sh b/setup.sh index 93319a7..d29e6d7 100755 --- a/setup.sh +++ b/setup.sh @@ -4,8 +4,10 @@ flask/bin/pip install flask flask/bin/pip install flask-login flask/bin/pip install flask-mail flask/bin/pip install flask-wtf +# Use host binary psycopg2 driver flask/bin/pip install psycopg2-binary -flask/bin/pip install psycopg2 +# Download source and compile in environment +#flask/bin/pip install pip install --no-binary :all: psycopg2 flask/bin/pip install mysql-connector flask/bin/pip install flask-bootstrap