Use sqlalchemy, flask-migrate, flask-login and flask-script

No peewee anymore. All dependencies are available as debian packages now.

29 files changed, 938 insertions, 495 deletions

diff --git a/app.py b/app.py
index c085527..658be69 100644
--- a/app.py
+++ b/app.py
@@ -1,8 +1,12 @@
 from flask import Flask
-from flask_peewee.db import Database
+from flask.ext.login import LoginManager
+from flask.ext.sqlalchemy import SQLAlchemy
+
 from padlite import PadLite
 
 app = Flask(__name__)
 app.config.from_pyfile('settings.py')
-db = Database(app)
+login = LoginManager(app)
+login.login_view = 'login'
+db = SQLAlchemy(app)
 pad = PadLite(app.config['PAD']['apikey'], app.config['PAD']['host'])
diff --git a/auth.py b/auth.py
deleted file mode 100644
index 7f330db..0000000
--- a/auth.py
+++ /dev/null
@@ -1,88 +0,0 @@
-from flask_peewee.auth import Auth
-from flask_peewee.utils import get_next
-from flask import session, url_for, request, redirect
-from models import User, Session
-from app import app, db, pad
-from datetime import datetime
-from padlite import APIException
-import ldap
-import uuid
-import functools
-
-class LdapAuth(Auth):
-    def get_user_model(self):
-        return User
-
-    def authenticate(self, username, password):
-        ldap.protocol_version = 3
-        l = ldap.initialize(app.config['LDAP']['host'])
-	l.set_option( ldap.OPT_X_TLS_DEMAND, True )
-        try:
-            user_dn = self._format_dn([('uid', username)])
-            l.simple_bind_s(user_dn, password)
-        except ldap.INVALID_CREDENTIALS:
-            return False
-
-	try:
-            user = User.get(User.username == username)
-        except User.DoesNotExist:
-            user_data = l.search_s(user_dn, ldap.SCOPE_BASE)
-            if (len(user_data) != 1):
-                return False
-
-            (dn, user_data) = user_data[0]
-            user = User.create(
-                username = username,
-                email = user_data['mail'][0],
-                api_id = pad.createAuthorIfNotExistsFor(user_dn, username))
-
-        return user
-
-    def login_user(self, user):
-        user.last_login = datetime.now()
-        user.save()
-        session['uuid'] = uuid.uuid4()
-        return super(LdapAuth, self).login_user(user)
-
-    def logout_user(self):
-        if 'uuid' in session:
-            for s in Session.select().where(Session.uuid == session['uuid']):
-                try:
-                    s.delete_instance()
-                except APIException:
-                    pass
-            del session['uuid']
-        return super(LdapAuth, self).logout_user()
-
-    def _format_dn(self, attr, with_base_dn = True):
-        if with_base_dn:
-            attr.extend(app.config['LDAP']['base_dn'])
-
-        dn = ['%s=%s' % (item[0], self._escape(item[1])) for item in attr]
-
-        return ','.join(dn)
-
-    def _escape(self, s, wildcard=False):
-        chars_to_escape = ['\\',',','=','+','<','>',';','"','\'','#','(',')','\0']
-
-        if not wildcard:
-            chars_to_escape.append('*')
-
-        escape = lambda x,y: x.replace(y,'\%02X' % ord(y))
-
-        return reduce(escape, chars_to_escape, s)
-
-    def test_user(self, test_fn):
-        def decorator(fn):
-            @functools.wraps(fn)
-            def inner(*args, **kwargs):
-                user = self.get_logged_in_user()
-
-                if not user or not test_fn(user):
-                    login_url = url_for('%s.login' % self.blueprint.name, next="%s%s" % (request.environ['SCRIPT_NAME'], get_next()))
-                    return redirect(login_url)
-                return fn(*args, **kwargs)
-            return inner
-        return decorator
-
-auth = LdapAuth(app, db, user_model=User, default_next_url='/teams')
diff --git a/forms.py b/forms.py
index 969ea73..9a2ea85 100644
--- a/forms.py
+++ b/forms.py
@@ -1,43 +1,57 @@
-from wtforms import HiddenField, PasswordField, validators, ValidationError
-from wtfpeewee.orm import model_form, ModelConverter
 from flask.ext.wtf import Form
-from utils import Unique, ReadonlyField
+from wtforms import StringField, HiddenField, PasswordField, BooleanField, \
+     validators, ValidationError
+from wtforms.ext.sqlalchemy.orm import model_form, ModelConverter
+
+from app import db
 from models import Group, Pad
-from widgets import TextArea
-
-
-CreateGroup = model_form(Group, base_class=Form, exclude=['api_id'], field_args={
-    'name': {'validators': [
-        validators.Required(),
-        validators.Regexp('^[a-zA-Z1-9_-]+$', message=u'Invalid group name '
-            '(only simple characters, numbers, - and _).'),
-        validators.Regexp('^[a-zA-Z1-9]', message=u'Group name should not '
-            'start with a special character.'),
-        Unique(Group, Group.name, message=u'A group with this name '
-            'already exists.')]
+from utils.forms import Unique, ReadonlyField, RedirectMixin
+from utils.widgets import TextArea
+
+
+CreateGroup = model_form(
+    Group, base_class=Form, only=['name', 'description', 'public', 'browsable'],
+    field_args={
+        'name': {'validators': [
+            validators.Required(),
+            validators.Regexp('^[a-zA-Z1-9_-]+$', message=u'Invalid group name '
+                '(only simple characters, numbers, - and _).'),
+            validators.Regexp('^[a-zA-Z1-9]', message=u'Group name should not '
+                'start with a special character.'),
+            Unique(Group, Group.name, message=u'A group with this name '
+                'already exists.')]
+        },
+        'description': {'widget': TextArea(rows=7)},
+        'public': {'validators': []},
+        'browsable': {'validators': []},
     },
-    'description': {'widget': TextArea(rows=7)}})
+    db_session=db.session)
 
 
-ChangeGroup = model_form(Group, base_class=Form, exclude=['api_id'], field_args={
-    'description': {'widget': TextArea(rows=7)}},
-    converter=ModelConverter(overrides={'name': ReadonlyField}))
+ChangeGroup = model_form(
+    Group, base_class=Form, only=['name', 'description', 'public', 'browsable'],
+    field_args={
+        'description': {'widget': TextArea(rows=7)},
+        'public': {'validators': []},
+        'browsable': {'validators': []},
+    },
+    converter=ModelConverter({'name': ReadonlyField}),
+    db_session=db.session)
 
 
 _CreatePad = model_form(
-    Pad, base_class=Form, exclude=['api_id', 'created', 'group'], field_args={
+    Pad, base_class=Form, exclude=['api_id', 'created', 'group'],
+    field_args={
         'name': {'validators': [
             validators.Required(),
             validators.Regexp('^[a-zA-Z1-9_-]+$', message=u'Invalid pad name '
                 '(only simple characters, numbers, - and _).'),
             validators.Regexp('^[a-zA-Z1-9]', message=u'Pad name should not '
-                'start with a special character.')]}},
-    converter=ModelConverter(overrides={'password': PasswordField}))
-
-
-ChangePad = model_form(
-    Pad, base_class=Form, exclude=['api_id', 'created', 'group'],
-    converter=ModelConverter(overrides={'password': PasswordField, 'name': ReadonlyField}))
+                'start with a special character.')]},
+        'public': {'validators': []},
+    },
+    converter=ModelConverter({'password': PasswordField}),
+    db_session=db.session)
 
 
 class CreatePad(_CreatePad):
@@ -47,13 +61,25 @@ class CreatePad(_CreatePad):
 
     def validate_name(self, field):
         if self.group is not None:
-            try:
-                Pad.get(Pad.name == field.data, Pad.group == self.group)
+            pad_query = Pad.query.filter_by(name=field.data, group=self.group)
+            if pad_query.count() > 0:
                 raise ValidationError(u'A pad with this name already '
                                       'exists in this group.')
-            except Pad.DoesNotExist:
-                pass
 
+ChangePad = model_form(
+    Pad, base_class=Form, exclude=['api_id', 'created', 'group'],
+    field_args={
+        'public': {'validators': []},
+    },
+    converter=ModelConverter({'password': PasswordField,
+                              'name': ReadonlyField}),
+    db_session=db.session)
+
+
+class LoginForm(RedirectMixin, Form):
+    user = StringField('login', [validators.Required()])
+    password = PasswordField('password', [validators.Required()])
 
+    
 class DeleteForm(Form):
     sure = HiddenField('are you sure', default='yes')
diff --git a/main.py b/main.py
deleted file mode 100755
index c521ad0..0000000
--- a/main.py
+++ /dev/null
@@ -1,11 +0,0 @@
-#!/usr/bin/env python
-
-from app import app, db
-from admin import admin
-from models import create_tables
-from views import *
-
-if __name__ == '__main__':
-    db.connect_db()
-    create_tables()
-    app.run(host = '::')
diff --git a/manage.py b/manage.py
new file mode 100755
index 0000000..bb5e760
--- /dev/null
+++ b/manage.py
@@ -0,0 +1,25 @@
+#!/usr/bin/env python
+from flask.ext.script import Manager, Server, Shell
+from flask.ext.migrate import Migrate, MigrateCommand
+
+import app
+import views
+import models
+
+
+def main():
+    manager = Manager(app.app)
+    manager.add_command("runserver", Server(host='::'))
+    manager.add_command("shell", Shell(
+        make_context=lambda: dict(app=app.app, db=app.db, pad=app.pad,
+                                  models=models)))
+
+    # flask-migrate for alembic migrations
+    migrate = Migrate(app.app, app.db)
+    manager.add_command('db', MigrateCommand)
+
+    manager.run()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/migrations/alembic.ini b/migrations/alembic.ini
new file mode 100644
index 0000000..6022013
--- /dev/null
+++ b/migrations/alembic.ini
@@ -0,0 +1,36 @@
+[alembic]
+file_template = %%(year)d-%%(month).2d-%%(day).2d_%%(rev)s_%%(slug)s
+
+[loggers]
+keys = root,sqlalchemy,alembic
+
+[handlers]
+keys = console
+
+[formatters]
+keys = generic
+
+[logger_root]
+level = WARN
+handlers = console
+qualname =
+
+[logger_sqlalchemy]
+level = WARN
+handlers =
+qualname = sqlalchemy.engine
+
+[logger_alembic]
+level = INFO
+handlers =
+qualname = alembic
+
+[handler_console]
+class = StreamHandler
+args = (sys.stderr,)
+level = NOTSET
+formatter = generic
+
+[formatter_generic]
+format = %(levelname)-5.5s [%(name)s] %(message)s
+datefmt = %H:%M:%S
diff --git a/migrations/env.py b/migrations/env.py
new file mode 100644
index 0000000..70961ce
--- /dev/null
+++ b/migrations/env.py
@@ -0,0 +1,73 @@
+from __future__ import with_statement
+from alembic import context
+from sqlalchemy import engine_from_config, pool
+from logging.config import fileConfig
+
+# this is the Alembic Config object, which provides
+# access to the values within the .ini file in use.
+config = context.config
+
+# Interpret the config file for Python logging.
+# This line sets up loggers basically.
+fileConfig(config.config_file_name)
+
+# add your model's MetaData object here
+# for 'autogenerate' support
+# from myapp import mymodel
+# target_metadata = mymodel.Base.metadata
+from flask import current_app
+config.set_main_option('sqlalchemy.url', current_app.config.get('SQLALCHEMY_DATABASE_URI'))
+target_metadata = current_app.extensions['migrate'].db.metadata
+
+# other values from the config, defined by the needs of env.py,
+# can be acquired:
+# my_important_option = config.get_main_option("my_important_option")
+# ... etc.
+
+def run_migrations_offline():
+    """Run migrations in 'offline' mode.
+
+    This configures the context with just a URL
+    and not an Engine, though an Engine is acceptable
+    here as well.  By skipping the Engine creation
+    we don't even need a DBAPI to be available.
+
+    Calls to context.execute() here emit the given string to the
+    script output.
+
+    """
+    url = config.get_main_option("sqlalchemy.url")
+    context.configure(url=url)
+
+    with context.begin_transaction():
+        context.run_migrations()
+
+def run_migrations_online():
+    """Run migrations in 'online' mode.
+
+    In this scenario we need to create an Engine
+    and associate a connection with the context.
+
+    """
+    engine = engine_from_config(
+                config.get_section(config.config_ini_section),
+                prefix='sqlalchemy.',
+                poolclass=pool.NullPool)
+
+    connection = engine.connect()
+    context.configure(
+                connection=connection,
+                target_metadata=target_metadata
+                )
+
+    try:
+        with context.begin_transaction():
+            context.run_migrations()
+    finally:
+        connection.close()
+
+if context.is_offline_mode():
+    run_migrations_offline()
+else:
+    run_migrations_online()
+
diff --git a/migrations/script.py.mako b/migrations/script.py.mako
new file mode 100644
index 0000000..9570201
--- /dev/null
+++ b/migrations/script.py.mako
@@ -0,0 +1,22 @@
+"""${message}
+
+Revision ID: ${up_revision}
+Revises: ${down_revision}
+Create Date: ${create_date}
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = ${repr(up_revision)}
+down_revision = ${repr(down_revision)}
+
+from alembic import op
+import sqlalchemy as sa
+${imports if imports else ""}
+
+def upgrade():
+    ${upgrades if upgrades else "pass"}
+
+
+def downgrade():
+    ${downgrades if downgrades else "pass"}
diff --git a/migrations/versions/2016-01-10_1a81cf0e0862_initial_migration.py b/migrations/versions/2016-01-10_1a81cf0e0862_initial_migration.py
new file mode 100644
index 0000000..4ee4051
--- /dev/null
+++ b/migrations/versions/2016-01-10_1a81cf0e0862_initial_migration.py
@@ -0,0 +1,84 @@
+"""Initial migration
+
+Revision ID: 1a81cf0e0862
+Revises: None
+Create Date: 2016-01-10 03:41:56.795099
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = '1a81cf0e0862'
+down_revision = None
+
+from alembic import op
+import sqlalchemy as sa
+
+
+def upgrade():
+    ### commands auto generated by Alembic - please adjust! ###
+    op.create_table('users',
+    sa.Column('api_id', sa.String(length=255), nullable=False),
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('name', sa.String(length=255), nullable=False),
+    sa.Column('email', sa.String(length=255), nullable=False),
+    sa.Column('last_login', sa.DateTime(timezone=True), server_default='CURRENT_TIMESTAMP', nullable=False),
+    sa.Column('active', sa.Boolean(), server_default='1', nullable=False),
+    sa.Column('admin', sa.Boolean(), server_default='0', nullable=False),
+    sa.PrimaryKeyConstraint('id'),
+    sa.UniqueConstraint('name')
+    )
+    op.create_table('groups',
+    sa.Column('api_id', sa.String(length=255), nullable=False),
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('name', sa.String(length=255), nullable=False),
+    sa.Column('public', sa.Boolean(), server_default='0', nullable=False),
+    sa.Column('browsable', sa.Boolean(), server_default='0', nullable=False),
+    sa.Column('description', sa.Text(), nullable=True),
+    sa.PrimaryKeyConstraint('id'),
+    sa.UniqueConstraint('name')
+    )
+    op.create_table('pads',
+    sa.Column('api_id', sa.String(length=255), nullable=False),
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('name', sa.String(length=255), nullable=False),
+    sa.Column('group_id', sa.Integer(), nullable=False),
+    sa.Column('created', sa.DateTime(timezone=True), server_default='CURRENT_TIMESTAMP', nullable=False),
+    sa.Column('public', sa.Boolean(), server_default='0', nullable=False),
+    sa.Column('password', sa.String(length=255), nullable=True),
+    sa.ForeignKeyConstraint(['group_id'], ['groups.id'], ),
+    sa.PrimaryKeyConstraint('id'),
+    sa.UniqueConstraint('name', 'group_id')
+    )
+    op.create_table('members',
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('group_id', sa.Integer(), nullable=False),
+    sa.Column('user_id', sa.Integer(), nullable=False),
+    sa.Column('manager', sa.Boolean(), server_default='0', nullable=False),
+    sa.Column('admin', sa.Boolean(), server_default='0', nullable=False),
+    sa.Column('active', sa.Boolean(), server_default='0', nullable=False),
+    sa.ForeignKeyConstraint(['group_id'], ['groups.id'], ),
+    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
+    sa.PrimaryKeyConstraint('id')
+    )
+    op.create_table('sessions',
+    sa.Column('api_id', sa.String(length=255), nullable=False),
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('user_id', sa.Integer(), nullable=False),
+    sa.Column('group_id', sa.Integer(), nullable=False),
+    sa.Column('uuid', sa.String(length=36), nullable=False),
+    sa.Column('valid_until', sa.DateTime(timezone=True), nullable=False),
+    sa.ForeignKeyConstraint(['group_id'], ['groups.id'], ),
+    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
+    sa.PrimaryKeyConstraint('id')
+    )
+    ### end Alembic commands ###
+
+
+def downgrade():
+    ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table('sessions')
+    op.drop_table('members')
+    op.drop_table('pads')
+    op.drop_table('groups')
+    op.drop_table('users')
+    ### end Alembic commands ###
diff --git a/models.py b/models.py
index d2f1ae4..564c27c 100644
--- a/models.py
+++ b/models.py
@@ -1,89 +1,132 @@
-from peewee import CharField, DateTimeField, BooleanField, ForeignKeyField, TextField
-from peewee import create_model_tables
 from datetime import datetime, timedelta
-from app import db, pad
+from flask.ext.login import UserMixin
+from sqlalchemy.orm import backref
+from sqlalchemy.sql import func, expression
+
+from app import db, pad, login
 from padlite import APIException
+from utils.apimixin import APIMixin
+from utils.login import user_cls
 
-class User(db.Model):
-    username = CharField()
-    api_id = CharField(null=True)
-    email = CharField()
-    last_login = DateTimeField(default=datetime.now)
-    active = BooleanField(default=True)
-    admin = BooleanField(default=False)
 
-    def __str__(self):
-        return self.username
+def column(*args, **kwargs):
+    """ I want to have a Column with nullable defaults to True. """
+    kwargs["nullable"] = kwargs.get("nullable", False)
+    return db.Column(*args, **kwargs)
+
+
+class SessionMixin(object):
+    @classmethod
+    def create(cls, *args, **kwargs):
+        obj = cls(*args, **kwargs)
+        db.session.add(obj)
+        return obj
+
 
-    def __unicode__(self):
-        return self.username
+@user_cls(login)
+class User(UserMixin, APIMixin, SessionMixin, db.Model):
+    __tablename__ = 'users'
 
-class Group(db.Model):
-    name = CharField(unique=True)
-    api_id = CharField(null=True)
-    public = BooleanField(default=False)
-    browsable = BooleanField(default=False)
-    description = TextField(null=True)
+    id = column(db.Integer, primary_key=True)
+    name = column(db.String(255), unique=True)
+    email = column(db.String(255))
+    last_login = column(db.DateTime(timezone=True), server_default=func.now())
+    active = column(db.Boolean, default=True,
+                    server_default=expression.true())
+    admin = column(db.Boolean, default=False,
+                   server_default=expression.false())
 
     def __str__(self):
         return self.name
 
-    def __unicode__(self):
+    def __repr__(self):
+        return '<User: %r>' % self.name
+
+    def create_api_object(self):
+        self.api_id = pad.createAuthor(self.name)
+
+    def remove_api_object(self):
+        # authors could not be deleted with padlite api
+        pass
+
+
+class Group(APIMixin, SessionMixin, db.Model):
+    __tablename__ = 'groups'
+
+    id = column(db.Integer, primary_key=True)
+    name = column(db.String(255), unique=True)
+    public = column(db.Boolean, default=False,
+                    server_default=expression.false())
+    browsable = column(db.Boolean, default=False,
+                       server_default=expression.false())
+    description = column(db.Text, nullable=True)
+
+    def __str__(self):
         return self.name
 
-    def save(self, force_insert=False, only=None):
-        if self.id is None or force_insert:
-            self.api_id = pad.createGroup()
+    def __repr__(self):
+        return '<Group: %r>' % self.name
 
-        super(Group, self).save(force_insert=force_insert, only=only)
+    def create_api_object(self):
+        self.api_id = pad.createGroup()
 
-    def delete_instance(self, **kwargs):
-        if self.api_id is not None:
-            pad.deleteGroup(self.api_id)
-            self.api_id = None
-            self.save()
-        super(Group, self).delete_instance(**kwargs)
-
-class Member(db.Model):
-    group = ForeignKeyField(Group, related_name='members')
-    user = ForeignKeyField(User, related_name='groups')
-    manager = BooleanField(default=False)
-    admin = BooleanField(default=False)
-    active = BooleanField(default=False)
+    def remove_api_object(self):
+        pad.deleteGroup(self.api_id)
+
+
+class Member(SessionMixin, db.Model):
+    __tablename__ = 'members'
+
+    id = column(db.Integer, primary_key=True)
+    group_id = column(db.Integer, db.ForeignKey('groups.id'))
+    user_id = column(db.Integer, db.ForeignKey('users.id'))
+    manager = column(db.Boolean, server_default=expression.false())
+    admin = column(db.Boolean, server_default=expression.false())
+    active = column(db.Boolean, server_default=expression.false())
+
+    user = db.relationship(
+        "User", backref=backref("memberships", cascade="delete"))
+    group = db.relationship(
+        "Group", backref=backref("members", cascade="delete"))
 
     def __str__(self):
-        return "%s member of %s" % (self.user.username, self.group.name)
-
-    def __unicode__(self):
-        return "%s member of %s" % (self.user.username, self.group.name)
-
-class Session(db.Model):
-    api_id = CharField(null=True)
-    user = ForeignKeyField(User, related_name='sessions')
-    group = ForeignKeyField(Group, related_name='sessions')
-    uuid = CharField()
-    valid_until = DateTimeField(null=True)
-
-    def save(self, force_insert=False, only=None):
-        if self.id is None or force_insert:
-            if self.group.api_id is None:
-                self.group.api_id = pad.createGroup()
-            self.valid_until = datetime.now() + timedelta(hours=4)
-            self.api_id = pad.createSession(self.group.api_id, self.user.api_id,
-                                            self.valid_until.strftime("%s"))
-        super(Session, self).save(force_insert=force_insert, only=only)
-
-    def delete_instance(self, **kwargs):
-        if self.api_id is not None:
-            try:
-                pad.deleteSession(self.api_id)
-            except APIException as e:
-                # we want to ignore code 1 = sessionID does not exist
-                if e.code != 1:
-                    raise
-            self.api_id = None
-            self.save()
-        super(Session, self).delete_instance(**kwargs)
+        return "%s member of %s" % (self.user.name, self.group.name)
+
+    def __repr__(self):
+        return "<Member: %r of %r>" % (self.user, self.group)
+
+
+class Session(APIMixin, SessionMixin, db.Model):
+    __tablename__ = 'sessions'
+
+    id = column(db.Integer, primary_key=True)
+    user_id = column(db.Integer, db.ForeignKey('users.id'))
+    group_id = column(db.Integer, db.ForeignKey('groups.id'))
+    uuid = column(db.String(36))
+    valid_until = column(db.DateTime(timezone=True))
+
+    user = db.relationship(
+        "User", backref=backref("sessions", cascade="delete"))
+    group = db.relationship(
+        "Group", backref=backref("sessions", cascade="delete"))
+
+    def __repr__(self):
+        return "<Session: %r>" % (self.user)
+
+    def create_api_object(self):
+        self.valid_until = datetime.now() + timedelta(hours=4)
+        self.api_id = pad.createSession(
+            self.group.get_api_id(),
+            self.user.get_api_id(),
+            self.valid_until.strftime("%s"))
+
+    def remove_api_object(self):
+        try:
+            pad.deleteSession(self.api_id)
+        except APIException as e:
+            # we want to ignore code 1 = sessionID does not exist
+            if e.code != 1:
+                raise
 
     def is_valid(self):
         if self.api_id is None:
@@ -95,37 +138,39 @@ class Session(db.Model):
 
         return False
 
-class Pad(db.Model):
-    name = CharField(verbose_name='pad name')
-    api_id = CharField(null=True)
-    group = ForeignKeyField(Group, related_name='pads')
-    created = DateTimeField(default=datetime.now)
-    public = BooleanField(default=False)
-    password = CharField(null=True)
+
+class Pad(APIMixin, SessionMixin, db.Model):
+    __tablename__ = 'pads'
+    __table_args__ = (
+        db.UniqueConstraint('name', 'group_id'),
+    )
+
+    id = column(db.Integer, primary_key=True)
+    name = column(db.String(255))
+    group_id = column(db.Integer, db.ForeignKey('groups.id'))
+    created = column(db.DateTime(timezone=True), server_default=func.now())
+    public = column(db.Boolean, default=False,
+                    server_default=expression.false())
+    password = column(db.String(255), default='',
+                      nullable=True)
+
+    group = db.relationship(
+        "Group", backref=backref("pads", cascade="delete"))
 
     def __str__(self):
         return self.name
 
-    def __unicode__(self):
-        return self.name
+    def __repr__(self):
+        return "<Pad: %r for %r>" % (self.name, self.group)
 
-    def save(self, force_insert=False, only=None):
-        if self.id is None or force_insert:
-            if self.group.api_id is None:
-                self.group.api_id = pad.createGroup()
-            self.api_id = pad.createGroupPad(self.group.api_id, self.name, 'testing')
+    def create_api_object(self):
+        self.api_id = pad.createGroupPad(
+            self.group.get_api_id(), self.name, 'testing')
 
+    def remove_api_object(self):
+        pad.deletePad(self.api_id)
+
+    def after_commit(self):
         if self.api_id is not None:
             pad.setPublicStatus(self.api_id, self.public)
             pad.setPassword(self.api_id, self.password)
-        super(Pad, self).save(force_insert=force_insert, only=only)
-
-    def delete_instance(self, **kwargs):
-        if self.api_id is not None:
-            pad.deletePad(self.api_id)
-            self.api_id = None
-            self.save()
-        super(Pad, self).delete_instance(**kwargs)
-
-def create_tables():
-    create_model_tables([User, Group, Member, Session, Pad], fail_silently = True)
diff --git a/pagination.py b/pagination.py
deleted file mode 100644
index 58fb869..0000000
--- a/pagination.py
+++ /dev/null
@@ -1,40 +0,0 @@
-from math import ceil
-from app import app
-from flask import url_for, request
-
-class Pagination(object):
-    def __init__(self, page, per_page, total_count):
-        self.page = page
-        self.per_page = per_page
-        self.total_count = total_count
-
-    @property
-    def pages(self):
-        return int(ceil(self.total_count / float(self.per_page)))
-
-    @property
-    def has_prev(self):
-        return self.page > 1
-
-    @property
-    def has_next(self):
-        return self.page < self.pages
-
-    def iter_pages(self, left_edge=2, left_current=2,
-                   right_current=5, right_edge=2):
-        last = 0
-        for num in xrange(1, self.pages + 1):
-            if num <= left_edge or \
-               (num > self.page - left_current - 1 and \
-                num < self.page + right_current) or \
-               num > self.pages - right_edge:
-                if last + 1 != num:
-                    yield None
-                yield num
-                last = num
-
-def url_for_other_page(page):
-    args = request.view_args.copy()
-    args['page'] = page
-    return url_for(request.endpoint, **args)
-app.jinja_env.globals['url_for_other_page'] = url_for_other_page
diff --git a/settings.py.default b/settings.py.default
index 3379f94..d38ae99 100644
--- a/settings.py.default
+++ b/settings.py.default
@@ -8,10 +8,7 @@ LDAP = {
     'base_dn': [('ou', 'people'), ('dc', 'example'), ('dc', 'org')],
 }
 
-DATABASE = {
-    'name': 'example.db',
-    'engine': 'peewee.SqliteDatabase',
-}
+SQLALCHEMY_DATABASE_URI = 'sqlite:///example.db'
 
 DEBUG = False
 SECRET_KEY = 'youShouldChangeThis'
diff --git a/templates/_pagination.html b/templates/_pagination.html
index f53df57..a8d1114 100644
--- a/templates/_pagination.html
+++ b/templates/_pagination.html
@@ -1,7 +1,7 @@
 {% macro render_pagination(pagination) %}
 <ul class="pagination" style="margin-top: 5px; margin-bottom: 0; padding: 0">
   {% if pagination.has_prev %}
-    <li><a href="{{ url_for_other_page(pagination.page - 1) }}">&laquo;</a></li>
+    <li><a href="{{ url_for_other_page(pagination.prev_num) }}">&laquo;</a></li>
   {% else %}
     <li class="disabled"><a href="#">&laquo;</a></li>
   {% endif %}
@@ -13,13 +13,13 @@
       {% else %}
         <li class="active"><a href="#">{{ page }}</a></li>
       {% endif %}
-    {% else %} 
+    {% else %}
       <li class="disabled"><a href="#">…</a></li>
     {% endif %}
   {%- endfor %}
 
   {% if pagination.has_next %}
-    <li><a href="{{ url_for_other_page(pagination.page + 1) }}">&raquo;</a></li>
+    <li><a href="{{ url_for_other_page(pagination.next_num) }}">&raquo;</a></li>
   {% else %}
     <li class="disabled"><a href="#">&raquo;</a></li>
   {% endif %}
diff --git a/templates/group.html b/templates/group.html
index 6a5236c..38e078f 100644
--- a/templates/group.html
+++ b/templates/group.html
@@ -16,6 +16,12 @@
       No description
     {% endif %}
   </div>
+
+  {% if not public_view and group.public %}
+    <div class="panel-footer text-center">
+      This group is public viewable.
+    </div>
+  {% endif %}
 </div>
 
 {% if not public_view %}
@@ -30,10 +36,10 @@
     {% endif %}
 
     <tr>
-      <td class="col-sm-11 nopadding">
+      <td class="col-sm-10 nopadding">
         <a href="{{ url_for('pad', group_name=group.name, pad_name=pad.name) }}" class="block">{{pad}}</a>
       </td>
-      <td class="col-sm-1 text-right">
+      <td class="col-sm-2 text-right">
         <a href="{{ url_for('pad_change', group_name=group.name, pad_name=pad.name) }}" class="btn btn-xs btn-info">
           <span class="glyphicon glyphicon-cog" />
         </a>
@@ -87,7 +93,7 @@
         </tr>
     {% endif %}
         <tr>
-          <td>{{member.user.username}}</td>
+          <td>{{member.user.name}}</td>
           <td class="text-center">{{member.user.email}}</td>
           <td class="text-right">
             <a href="{{ url_for('group_join', group_name=group.name, member_id=member.id, accept='yes') }}" class="btn btn-xs btn-success">Accept</a>
diff --git a/templates/index.html b/templates/index.html
index 1c4e612..f79fb68 100644
--- a/templates/index.html
+++ b/templates/index.html
@@ -1,7 +1,7 @@
 {% extends "layout.html" %}
 {% from "_formhelpers.html" import render_field %}
 
-{% block head %}Hello {{user.username}}!{% endblock %}
+{% block head %}Hello {{current_user.name}}!{% endblock %}
 
 {% block content %}
 <div class="panel panel-default">
diff --git a/templates/layout.html b/templates/layout.html
index 4019773..4ba5c4a 100644
--- a/templates/layout.html
+++ b/templates/layout.html
@@ -54,21 +54,19 @@
         <!-- Collect the nav links, forms, and other content for toggling -->
         <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
           <ul class="nav navbar-nav navbar-right">
-            {% if user %}
-              {% if user.admin %}
+            {% if current_user.is_authenticated %}
+              {% if current_user.admin %}
                 <li><a href="{{ url_for('admin.index') }}"><span class="glyphicon glyphicon-dashboard" /> Admin</a></li>
               {% endif %}
 
-              <li><a href="{{ url_for('auth.logout') }}"><span class="glyphicon glyphicon-log-out" /> Logout</a></li>
+              <li><a href="{{ url_for('logout') }}"><span class="glyphicon glyphicon-log-out" /> Logout</a></li>
             {% else %}
-              <li><a href="{{ url_for('auth.login') }}"><span class="glyphicon glyphicon-log-in" /> Login</a></li>
+              <li><a href="{{ url_for('login') }}"><span class="glyphicon glyphicon-log-in" /> Login</a></li>
             {% endif %}
           </ul>
         </div><!-- /.navbar-collapse -->
       </nav>
 
-
-  
       {% for categorie, message in get_flashed_messages(with_categories=true) %}
         {% if categorie == 'message' %}
           <div class="alert alert-danger">{{ message }}</div>
@@ -76,7 +74,7 @@
           <div class="alert alert-{{categorie}}">{{ message }}</div>
         {% endif %}
       {% endfor %}
-    
+
       {% block content %}{% endblock %}
     </div>
   </body>
diff --git a/templates/auth/login.html b/templates/login.html
index 3ba4e2e..3ba4e2e 100644
--- a/templates/auth/login.html
+++ b/templates/login.html
diff --git a/test.py b/test.py
deleted file mode 100755
index 0e39d01..0000000
--- a/test.py
+++ /dev/null
@@ -1,44 +0,0 @@
-#!/usr/bin/env python
-
-import padlite
-import settings
-import code
-
-def load_interpreters():
-    """ Load a dict of available Python interpreters """
-    interpreters = dict(python=lambda v: InteractiveConsole(v).interact())
-    best = "python"
-    try:
-        import bpython.cli
-        interpreters["bpython"] = lambda v: bpython.cli.main(args=[],
-                                                             locals_=v)
-        best = "bpython"
-    except ImportError:
-        pass
-
-    try:
-        # whether ipython is actually better than bpython is
-        # up for debate, but this is the behavior that existed
-        # before --interpreter was added, so we call IPython
-        # better
-        import IPython
-        # pylint: disable=E1101
-        if hasattr(IPython, "Shell"):
-            interpreters["ipython"] = lambda v: \
-                IPython.Shell.IPShell(argv=[], user_ns=v).mainloop()
-            best = "ipython"
-        elif hasattr(IPython, "embed"):
-            interpreters["ipython"] = lambda v: IPython.embed(user_ns=v)
-            best = "ipython"
-        else:
-            print("Unknown IPython API version")
-        # pylint: enable=E1101
-    except ImportError:
-        pass
-
-    interpreters['best'] = interpreters[best]
-    return interpreters
-
-p = padlite.PadLite(settings.PAD['apikey'], settings.PAD['host'])
-interpreters = load_interpreters()
-interpreters['best'](locals())
diff --git a/utils.py b/utils.py
deleted file mode 100644
index c424850..0000000
--- a/utils.py
+++ /dev/null
@@ -1,53 +0,0 @@
-from functools import wraps
-from flask import g, request, render_template
-from wtforms import Field, ValidationError
-from widgets import Static
-
-# using http://flask.pocoo.org/docs/patterns/viewdecorators/
-def templated(template=None):
-    def decorator(f):
-        @wraps(f)
-        def decorated_function(*args, **kwargs):
-            template_name = template
-            if template_name is None:
-                template_name = request.endpoint \
-                    .replace('.', '/') + '.html'
-            ctx = f(*args, **kwargs)
-            if ctx is None:
-                ctx = {}
-            elif not isinstance(ctx, dict):
-                return ctx
-            return render_template(template_name, **ctx)
-        return decorated_function
-    return decorator
-
-
-def after_this_request(f):
-    if not hasattr(g, 'after_request_callbacks'):
-        g.after_request_callbacks = []
-    g.after_request_callbacks.append(f)
-    return f
-
-
-class Unique(object):
-    """ validator that checks field uniqueness """
-    def __init__(self, model, field, message=None):
-        self.model = model
-        self.field = field
-        if not message:
-            message = u'This element already exists.'
-        self.message = message
-
-    def __call__(self, form, field):         
-        try:
-            self.model.get(self.field == field.data)
-            raise ValidationError(self.message)
-        except self.model.DoesNotExist:
-            pass
-
-
-class ReadonlyField(Field):
-    widget = Static()
-
-    def process_formdata(self, _):
-        pass
diff --git a/utils/__init__.py b/utils/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/utils/__init__.py
diff --git a/utils/apimixin.py b/utils/apimixin.py
new file mode 100644
index 0000000..b48fd60
--- /dev/null
+++ b/utils/apimixin.py
@@ -0,0 +1,85 @@
+from sqlalchemy import event, Column, String
+from sqlalchemy.orm.session import Session
+
+
+class APIMixin(object):
+    api_id = Column(String(255), nullable=False)
+
+    def get_api_id(self):
+        if self.api_id is None:
+            self.create_api_object()
+
+        return self.api_id
+
+    def before_created(self):
+        if self.api_id is None:
+            self.create_api_object()
+
+    def after_deleted(self):
+        if self.api_id is not None:
+            self.remove_api_object()
+            self.api_id = None
+
+    def create_api_object(self):
+        raise NotImplementedError
+
+    def remove_api_object(self):
+        raise NotImplementedError
+
+    def after_commit(self):
+        pass
+
+
+class SessionHelper(object):
+
+    def __init__(self):
+        self.new = set()
+        self.dirty = set()
+        self.deleted = set()
+
+        event.listen(Session, 'before_flush', self.before_flush)
+        event.listen(Session, 'after_flush', self.after_flush)
+        event.listen(Session, 'after_commit', self.after_commit)
+        event.listen(Session, 'after_rollback', self.after_rollback)
+
+    def before_flush(self, session, flush_context, instances):
+        for obj in session.new:
+            if isinstance(obj, APIMixin):
+                obj.before_created()
+
+    def after_flush(self, session, flush_context):
+        self.new.update(
+            obj for obj in session.new
+            if isinstance(obj, APIMixin))
+
+        self.dirty.update(
+            obj for obj in session.dirty
+            if isinstance(obj, APIMixin))
+
+        self.deleted.update(
+            obj for obj in session.deleted
+            if isinstance(obj, APIMixin))
+
+    def after_commit(self, session):
+        for obj in self.new:
+            obj.after_commit()
+        self.new.clear()
+
+        for obj in self.dirty:
+            obj.after_commit()
+        self.dirty.clear()
+
+        for obj in self.deleted:
+            obj.after_deleted()
+        self.deleted.clear()
+
+    def after_rollback(self, session):
+        self.dirty.clear()
+        self.deleted.clear()
+
+        for obj in self.new:
+            obj.after_deleted()
+        self.new.clear()
+
+
+helper = SessionHelper()
diff --git a/filters.py b/utils/filters.py
index b8b0d47..eb0e1c8 100644
--- a/filters.py
+++ b/utils/filters.py
@@ -2,16 +2,19 @@ from app import app
 from jinja2 import contextfilter
 from jinja2.filters import make_attrgetter
 
+
 @app.template_filter('selectattr')
 @contextfilter
 def do_selectattr(*args, **kwargs):
     return _select_or_reject(args, kwargs, lambda x: x)
 
+
 @app.template_filter('rejectattr')
 @contextfilter
 def do_rejectattr(*args, **kwargs):
     return _select_or_reject(args, kwargs, lambda x: not x)
 
+
 def _select_or_reject(args, kwargs, modfunc):
     context = args[0]
     seq = args[1]
diff --git a/utils/forms.py b/utils/forms.py
new file mode 100644
index 0000000..a6ff4de
--- /dev/null
+++ b/utils/forms.py
@@ -0,0 +1,55 @@
+from flask import request, url_for, redirect
+from urlparse import urlparse, urljoin
+from wtforms import Field, HiddenField, ValidationError
+
+from widgets import Static
+
+
+class Unique(object):
+    """ validator that checks field uniqueness """
+    def __init__(self, model, field, message=None):
+        self.model = model
+        self.field = field
+        if not message:
+            message = u'This element already exists.'
+        self.message = message
+
+    def __call__(self, form, field):
+        if self.model.query.filter(self.field == field.data).count() > 0:
+            raise ValidationError(self.message)
+
+
+class ReadonlyField(Field):
+    widget = Static()
+
+    def process_formdata(self, _):
+        pass
+
+
+class RedirectMixin(object):
+    next = HiddenField()
+
+    def __init__(self, *args, **kwargs):
+        super(RedirectMixin, self).__init__(*args, **kwargs)
+        if not self.next.data:
+            self.next.data = self._get_redirect_target() or ''
+    
+    def _get_redirect_target(self):
+        for target in request.args.get('next'), request.referrer:
+            if not target:
+                continue
+            if self._is_safe_url(target):
+                return target
+
+    def _is_safe_url(self, target):
+        ref_url = urlparse(request.host_url)
+        test_url = urlparse(urljoin(request.host_url, target))
+        return test_url.scheme in ('http', 'https') and \
+            ref_url.netloc == test_url.netloc
+    
+    def redirect(self, endpoint='index', **values):
+        if self._is_safe_url(self.next.data):
+            return redirect(self.next.data)
+
+        target = self._get_redirect_target()
+        return redirect(target or url_for(endpoint, **values))
diff --git a/utils/login.py b/utils/login.py
new file mode 100644
index 0000000..e6c8f21
--- /dev/null
+++ b/utils/login.py
@@ -0,0 +1,40 @@
+import ldap
+from functools import reduce
+
+
+def user_cls(login):
+    def decorator(cls):
+        login.user_loader(lambda uid: cls.query.get(uid))
+        return cls
+    return decorator
+
+
+def _format_dn(attr, base_dn=None):
+    attr = [attr]
+    if base_dn is not None:
+        attr.extend(base_dn)
+
+    return ','.join(['%s=%s' % (key, ldap.dn.escape_dn_chars(value))
+                     for (key, value) in attr])
+
+
+def auth(config, model, username, password):
+    ldap.protocol_version = 3
+    l = ldap.initialize(config['host'])
+    l.set_option(ldap.OPT_X_TLS_DEMAND, True)
+    try:
+	user_dn = _format_dn(('uid', username), config['base_dn'])
+	l.simple_bind_s(user_dn, password)
+    except ldap.INVALID_CREDENTIALS:
+	return None
+
+    user = model.query.filter_by(name=username).first()
+    if user is None:
+	user_data = l.search_s(user_dn, ldap.SCOPE_BASE)
+	if len(user_data) != 1:
+	    return None
+
+	(dn, user_data) = user_data[0]
+	user = model.create(name=username, email=user_data['mail'][0])
+
+    return user
diff --git a/utils/pagination.py b/utils/pagination.py
new file mode 100644
index 0000000..8d2cb60
--- /dev/null
+++ b/utils/pagination.py
@@ -0,0 +1,18 @@
+from flask import url_for, request
+from app import app
+
+
+def url_for_other_page(page):
+    args = request.view_args.copy()
+    args['page'] = page
+    return url_for(request.endpoint, **args)
+app.jinja_env.globals['url_for_other_page'] = url_for_other_page
+
+
+# @app.context_processor
+# def register_method():
+#     def url_for_other_page(page):
+#         args = request.view_args.copy()
+#         args['page'] = page
+#         return url_for(request.endpoint, **args)
+#     return dict(url_for_other_page=url_for_other_page)
diff --git a/utils/request.py b/utils/request.py
new file mode 100644
index 0000000..8d36ed6
--- /dev/null
+++ b/utils/request.py
@@ -0,0 +1,25 @@
+from flask import g, request
+
+from app import app
+
+
+def after_this_request(f):
+    """
+    Decorator to execute methods after the request is handled, to
+    modify the response before sending back to the client. This could
+    be used to set cookies.
+    """
+
+    if not hasattr(g, 'after_request_callbacks'):
+        g.after_request_callbacks = []
+
+    g.after_request_callbacks.append(f)
+    return f
+
+
+@app.after_request
+def call_after_request_callbacks(response):
+    for callback in getattr(g, 'after_request_callbacks', ()):
+        callback(response)
+
+    return response
diff --git a/utils/viewdecorators.py b/utils/viewdecorators.py
new file mode 100644
index 0000000..8f96c07
--- /dev/null
+++ b/utils/viewdecorators.py
@@ -0,0 +1,21 @@
+from functools import wraps
+from flask import render_template, request
+
+
+# using http://flask.pocoo.org/docs/patterns/viewdecorators/
+def templated(template=None):
+    def decorator(f):
+        @wraps(f)
+        def decorated_function(*args, **kwargs):
+            template_name = template
+            if template_name is None:
+                template_name = request.endpoint \
+                    .replace('.', '/') + '.html'
+            ctx = f(*args, **kwargs)
+            if ctx is None:
+                ctx = {}
+            elif not isinstance(ctx, dict):
+                return ctx
+            return render_template(template_name, **ctx)
+        return decorated_function
+    return decorator
diff --git a/widgets.py b/utils/widgets.py
index 3e5d2b8..3e5d2b8 100644
--- a/widgets.py
+++ b/utils/widgets.py
diff --git a/views.py b/views.py
index 53dba97..9117146 100644
--- a/views.py
+++ b/views.py
@@ -1,64 +1,110 @@
-from app import app
-from auth import auth
 from flask import g, request, redirect, render_template, url_for, flash, \
-                  session, get_flashed_messages, abort
-from flask_peewee.utils import get_object_or_404
-from models import Group, Member, Pad, Session
-from forms import CreateGroup, DeleteForm, ChangeGroup, CreatePad, ChangePad
-from utils import templated, after_this_request
-from pagination import Pagination
+    session, get_flashed_messages, abort
+from flask.ext.login import login_required, login_user, logout_user, \
+     current_user
 from urlparse import urlparse
-from filters import *
+from sqlalchemy import and_
+from datetime import datetime
+import uuid
+
+from app import app, db
+from models import User, Group, Member, Pad, Session
+from forms import CreateGroup, DeleteForm, ChangeGroup, CreatePad, ChangePad, \
+    LoginForm
+from utils.login import auth
+from utils.viewdecorators import templated
+from utils.request import after_this_request
+from utils.filters import *
+import utils.pagination
+
+
+@app.route('/login', methods=['GET', 'POST'])
+@templated()
+def login():
+    form = LoginForm()
+    if form.validate_on_submit():
+        user = auth(app.config['LDAP'], User,
+                    form.user.data, form.password.data)
+
+        if user is not None:
+            user.last_login = datetime.now()
+            db.session.commit()
+            login_user(user)
+            db.session.commit()
+
+            session['uuid'] = unicode(uuid.uuid4())
+            return form.redirect('index')
+
+        flash('Wrong user or password')
 
-def get_group_or_404(*query):
-    group = get_object_or_404(Group.select().join(Member),
-                              Member.user == g.user, *query)
-    return group
+    return dict(form=form)
 
 
-@app.after_request
-def call_after_request_callbacks(response):
-    for callback in getattr(g, 'after_request_callbacks', ()):
-        callback(response)
-    return response
+@app.route('/logout', methods=['GET'])
+def logout():
+    logout_user()
+    if 'uuid' in session:
+        Session.query.filter(Session.uuid == session['uuid']).delete()
+        del session['uuid']
+    return redirect(url_for('index'))
 
 
 @app.route('/', methods=['GET', 'POST'])
-@templated('index.html')
-@auth.login_required
+@templated()
+@login_required
 def index():
     form = CreateGroup(request.form)
     if form.validate_on_submit():
         group = Group()
         form.populate_obj(group)
-        group.save()
-        Member.create(user=g.user, group=group, admin=True, active=True)
+        db.session.add(group)
         form = CreateGroup()
-    groups = [member.group for member in g.user.groups if member.active]
-    return {'groups': groups, 'create_form': form}
+
+        Member.create(user=current_user, group=group,
+                      admin=True, active=True)
+        db.session.commit()
+
+    memberships = Member.query.filter(
+        Member.user == current_user,
+        Member.active == True,
+    ).all()
+
+    groups = [member.group for member in memberships]
+    return dict(groups=groups, create_form=form)
 
 
 @app.route('/_all/', defaults={'page': 1})
 @app.route('/_all/_page/<int:page>')
-@templated('all.html')
+@templated()
+@login_required
 def all(page):
-    user_groups = Group.select().join(Member).where(Member.user == g.user)
-    public_groups = Group.select().where(~(Group.id << user_groups)).where(Group.browsable == True)
-    count = public_groups.count()
-    return {'groups': public_groups.paginate(page, 10),
-            'count': count,
-            'pagination': Pagination(page, 10, count),
+    public_groups = Group.query.filter(
+        ~Group.members.any(Member.user == current_user),
+        Group.browsable == True,
+    )
+
+    pageination = public_groups.paginate(page, 10)
+    return {'groups': pageination.items,
+            'count': pageination.total,
+            'pagination': pageination,
             'breadcrumbs': [{'text': 'Public groups'}]}
 
 
 @app.route('/_all/<group_name>/', methods=['GET', 'POST'])
 @templated('group.html')
+@login_required
 def public_group(group_name):
-    user_groups = Group.select().join(Member).where(Member.user == g.user)
-    group = get_object_or_404(Group.select(), ~(Group.id << user_groups), Group.name == group_name, Group.public == True)
+    group = Group.query.filter(
+        ~Group.members.any(Member.user == current_user),
+        Group.name == group_name,
+        Group.browsable == True,
+    ).first_or_404()
+
     if request.method == 'POST':
-        Member.create(user=g.user, group=group)
+        Member.create(user=current_user, group=group)
+        db.session.commit()
         return redirect(url_for('all'))
+
     return {'group': group,
             'public_view': True,
             'breadcrumbs': [
@@ -67,161 +113,223 @@ def public_group(group_name):
 
 
 @app.route('/<group_name>/_delete/', methods=['GET', 'POST'])
-@templated('group_delete.html')
-@auth.login_required
+@templated()
+@login_required
 def group_delete(group_name):
-    group = get_group_or_404(Group.name == group_name, Member.admin == True)
+    group = Group.query.filter(
+        Group.name == group_name,
+        Group.members.any(and_(Member.user == current_user,
+                               Member.admin == True,
+                               Member.active == True)),
+    ).first_or_404()
+
     form = DeleteForm(request.form)
     if form.validate_on_submit():
         if form.sure.data == 'yes':
-            group.delete_instance(recursive=True)
+            db.session.delete(group)
+            db.session.commit()
         return redirect(url_for('index'))
     return {'group': group,
             'delete_form': form,
-            'breadcrumbs': [{'href': url_for('group', group_name=group.name), 'text': group},
+            'breadcrumbs': [{'href': url_for('group', group_name=group.name),
+                             'text': group},
                             {'text': 'Delete group'}]}
 
 
 @app.route('/<group_name>/_change/', methods=['GET', 'POST'])
-@templated('group_change.html')
-@auth.login_required
+@templated()
+@login_required
 def group_change(group_name):
-    group = get_group_or_404(Group.name == group_name, Member.admin == True)
+    group = Group.query.filter(
+        Group.name == group_name,
+        Group.members.any(and_(Member.user == current_user,
+                               Member.admin == True,
+                               Member.active == True)),
+    ).first_or_404()
+
     form = ChangeGroup(request.form, obj=group)
     if form.validate_on_submit():
         del form.name
         form.populate_obj(group)
-        group.save()
+        db.session.commit()
         return redirect(url_for('group', group_name=group.name))
+
     return {'group': group,
             'change_form': form,
-            'breadcrumbs': [{'href': url_for('group', group_name=group.name), 'text': group},
+            'breadcrumbs': [{'href': url_for('group', group_name=group.name),
+                             'text': group},
                             {'text': 'Edit group'}]}
 
 
 @app.route('/<group_name>/_join/<int:member_id>/<accept>/')
-@auth.login_required
+@login_required
 def group_join(group_name, member_id, accept):
-    group = get_group_or_404(Group.name == group_name, Member.admin == True)
-    member = get_object_or_404(Member, Member.id == member_id, Member.group == group)
+    group = Group.query.filter(
+        Group.name == group_name,
+        Group.members.any(and_(Member.user == current_user,
+                               Member.admin == True,
+                               Member.active == True)),
+    ).first_or_404()
+
+    member = Member.query.filter(
+        Member.id == member_id,
+        Member.group == group,
+        Member.active == False,
+    ).first_or_404()
+
     if accept == 'yes':
         member.active = True
-        member.save()
+        db.session.commit()
     elif accept == 'no':
-        member.delete_instance()
+        db.session.delete(member)
+        db.session.commit()
+
     return redirect(url_for('group', group_name=group_name))
 
 
 @app.route('/<group_name>/_create_pad/', methods=['GET', 'POST'])
 @templated('pad_change.html')
-@auth.login_required
+@login_required
 def pad_create(group_name):
-    group = get_group_or_404(Group.name == group_name, Member.admin == True)
+    group = Group.query.filter(
+        Group.name == group_name,
+        Group.members.any(and_(Member.user == current_user,
+                               Member.admin == True,
+                               Member.active == True)),
+    ).first_or_404()
+
     form = CreatePad(request.form, group=group)
     if form.validate_on_submit():
         pad = Pad()
         form.populate_obj(pad)
         pad.group = group
-        pad.save()
+        db.session.add(pad)
+        db.session.commit()
         return redirect(url_for('group', group_name = group_name))
 
     return {'group': group,
             'change_form': form,
-            'breadcrumbs': [{'href': url_for('group', group_name=group.name), 'text': group},
+            'breadcrumbs': [{'href': url_for('group', group_name=group.name),
+                             'text': group},
                             {'text': 'Create pad'}]}
 
 
 @app.route('/<group_name>/<pad_name>/_edit/', methods=['GET', 'POST'])
-@templated('pad_change.html')
-@auth.login_required
+@templated()
+@login_required
 def pad_change(group_name, pad_name):
-    group = get_group_or_404(Group.name == group_name, Member.admin == True)
-
-    try:
-        pad = Pad.get(Pad.name == pad_name, Pad.group == group)
-    except Pad.DoesNotExist:
-        if member.admin == True:
-            return redirect(url_for('group', group_name = group_name))
-        abort(404)
+    group = Group.query.filter(
+        Group.name == group_name,
+        Group.members.any(and_(Member.user == current_user,
+                               Member.admin == True,
+                               Member.active == True)),
+    ).first_or_404()
+
+    pad = Pad.query.filter(
+        Pad.name == pad_name,
+        Pad.group == group,
+    ).first_or_404()
 
     form = ChangePad(request.form, obj=pad)
     if form.validate_on_submit():
         del form.name
         form.populate_obj(pad)
-        pad.save()
+        db.session.commit()
         return redirect(url_for('group', group_name=group.name))
 
     return {'group': group,
             'pad': pad,
             'change_form': form,
-            'breadcrumbs': [{'href': url_for('group', group_name=group.name), 'text': group},
+            'breadcrumbs': [{'href': url_for('group', group_name=group.name),
+                             'text': group},
                             {'text': 'Edit pad: %s' % pad.name}]}
 
 @app.route('/<group_name>/<pad_name>/_delete/', methods=['GET', 'POST'])
-@templated('pad_delete.html')
-@auth.login_required
+@templated()
+@login_required
 def pad_delete(group_name, pad_name):
-    group = get_group_or_404(Group.name == group_name, Member.admin == True)
-
-    try:
-        pad = Pad.get(Pad.name == pad_name, Pad.group == group)
-    except Pad.DoesNotExist:
-        if member.admin == True:
-            return redirect(url_for('group', group_name = group_name))
-        abort(404)
+    group = Group.query.filter(
+        Group.name == group_name,
+        Group.members.any(and_(Member.user == current_user,
+                               Member.admin == True,
+                               Member.active == True)),
+    ).first_or_404()
+
+    pad = Pad.query.filter(
+        Pad.name == pad_name,
+        Pad.group == group,
+    ).first_or_404()
 
     form = DeleteForm(request.form)
     if form.validate_on_submit():
         if form.sure.data == 'yes':
-            pad.delete_instance(recursive=True)
+            db.session.delete(pad)
+            db.session.commit()
         return redirect(url_for('group', group_name=group.name))
 
     return {'group': group,
             'pad': pad,
             'delete_form': form,
-            'breadcrumbs': [{'href': url_for('group', group_name=group.name), 'text': group},
+            'breadcrumbs': [{'href': url_for('group', group_name=group.name),
+                             'text': group},
                             {'text': 'Delete pad: %s' % pad.name}]}
 
 
 @app.route('/<group_name>/<pad_name>/')
-@templated('pad.html')
-@auth.login_required
+@templated()
+@login_required
 def pad(group_name, pad_name):
-    try:
-        group = get_object_or_404(Group, Group.name == group_name)
-        member = Member.get(Member.group == group, Member.user == g.user)
-    except Member.DoesNotExist:
-        if group.public == False:
+    group = Group.query.filter(
+        Group.name == group_name,
+    ).first_or_404()
+
+    member = Member.query.filter(
+        Member.group == group,
+        Member.user == current_user,
+        Member.active == True,
+    ).first()
+
+    if member is None:
+        if not group.public:
             abort(404)
+
         flash('You are not member of this group. You may request membership.')
         return redirect(url_for('public_group', group_name = group.name))
 
-    try:
-        pad = Pad.get(Pad.name == pad_name, Pad.group == group)
-    except Pad.DoesNotExist:
-        if member.admin == True:
-            return redirect(url_for('group', group_name = group_name))
-        abort(404)
-
-    api_session = None
-    try:
-        api_session = Session.get(Session.group == group,
-                                  Session.user == g.user,
-                                  Session.uuid == session['uuid'])
-        if not api_session.is_valid():
-            api_session.delete_instance()
-            api_session = None
-    except:
-        pass
-
-    if api_session is None:
-        Session.create(user = g.user, group = group, uuid = session['uuid'])
-
-    sessions = Session.select().where(Session.user == g.user, Session.uuid == session['uuid'])
+    pad = Pad.query.filter(
+        Pad.name == pad_name,
+        Pad.group == group,
+    ).first()
+
+    if pad is None:
+        if not member.admin:
+            abort(404)
+
+        flash('Pad "%s" not found.' % pad_name)
+        return redirect(url_for('group', group_name = group_name))
+
+    api_session = Session.query.filter(
+        Session.group == group,
+        Session.user == current_user,
+        Session.uuid == session['uuid'],
+    ).first()
+
+    if api_session is None or not api_session.is_valid():
+        if api_session:
+            db.session.delete(api_session)
+
+        Session.create(user=current_user, group=group, uuid=session['uuid'])
+        db.session.commit()
+
+    sessions = Session.query.filter(
+        Session.user == current_user,
+        Session.uuid == session['uuid'],
+    ).all()
 
     @after_this_request
     def set_session(response):
-        response.set_cookie('sessionID' , '%2C'.join([s.api_id for s in sessions]))
+        response.set_cookie('sessionID' ,
+                            '%2C'.join([s.api_id for s in sessions]))
 
     # ignore user logged in messages
     get_flashed_messages()
@@ -230,13 +338,21 @@ def pad(group_name, pad_name):
 
 
 @app.route('/<group_name>/')
-@templated('group.html')
-@auth.login_required
+@templated()
+@login_required
 def group(group_name):
-    group = get_group_or_404(Group.name == group_name)
-    member = get_object_or_404(Member, Member.user == g.user, Member.group == group)
+    group = Group.query.filter(
+        Group.name == group_name,
+    ).first_or_404()
+
+    member = Member.query.filter(
+        Member.user == current_user,
+        Member.group == group,
+        Member.active == True,
+    ).first_or_404()
+
     return {'group': group,
-            'pads': list(group.pads),
+            'pads': group.pads,
             'admin': member.admin,
-            'members': [m for m in group.members.execute()],
+            'members': group.members,
             'breadcrumbs': [{'text': group}]}