Added sqlalchemy support

made models for database
changed views to handle sqlalchemy

pylons_app/lib/timerproxy.py

@@ -0,0 +1,15 b''
+from sqlalchemy.interfaces import ConnectionProxy
+import time
+import logging
+log = logging.getLogger(__name__)
+
+class TimerProxy(ConnectionProxy):
+    def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
+        now = time.time()
+        try:
+            log.info(">>>>> STARTING QUERY >>>>>")
+            return execute(cursor, statement, parameters, context)
+        finally:
+            total = time.time() - now
+            log.info("Query: %s" % statement % parameters)
+            log.info("<<<<< TOTAL TIME: %f <<<<<" % total)

pylons_app/model/db.py

@@ -0,0 +1,24 b''
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import relation, backref
+from sqlalchemy import ForeignKey, Column, Table, Sequence
+from sqlalchemy.types import *
+from sqlalchemy.databases.sqlite import *
+from pylons_app.model.meta import Base
+
+
+class Users(Base): 
+    __tablename__ = 'users'
+    __table_args__ = {'useexisting':True}
+    user_id = Column("user_id", SLInteger(), nullable=False, unique=True, default=None, primary_key=1)
+    username = Column("username", SLText(length=None, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
+    password = Column("password", SLText(length=None, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
+    active = Column("active", SLInteger(), nullable=True, unique=None, default=None)
+    admin = Column("admin", SLInteger(), nullable=True, unique=None, default=None)
+    
+class UserLogs(Base): 
+    __tablename__ = 'user_logs'
+    __table_args__ = {'useexisting':True}
+    id = Column("id", SLInteger(), nullable=False, unique=True, default=None, primary_key=1)
+    user_id = Column("user_id", SLInteger(), nullable=True, unique=None, default=None)
+    last_action = Column("last_action", SLText(length=None, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
+    last_action_date = Column("last_action_date", SLDateTime(timezone=False), nullable=True, unique=None, default=None)

development.ini

@@ -31,6 +31,15 b' lang=en'
 cache_dir = %(here)s/data
 repos_name = etelko
 
+####################################
+###         BEAKER CACHE        ####
+####################################
+beaker.cache.data_dir=/tmp/cache/data
+beaker.cache.lock_dir=/tmp/cache/lock
+beaker.cache.regions=short_term
+beaker.cache.short_term.type=memory
+beaker.cache.short_term.expire=3600
+    
 ################################################################################
 ## WARNING: *THE LINE BELOW MUST BE UNCOMMENTED ON A PRODUCTION ENVIRONMENT*  ##
 ## Debug mode will enable the interactive debugging tool, allowing ANYONE to  ##
@@ -38,6 +47,21 b' repos_name = etelko'
 ################################################################################
 #set debug = false
 
+##################################
+###       LOGVIEW CONFIG       ###
+##################################
+logview.sqlalchemy = #faa
+logview.pylons.templating = #bfb
+logview.pylons.util = #eee
+
+#########################################################
+### DB CONFIGS - EACH DB WILL HAVE IT'S OWN CONFIG    ###
+#########################################################
+sqlalchemy.db1.url = sqlite:///%(here)s/auth.sqlite
+#sqlalchemy.db1.echo = True
+#sqlalchemy.db1.pool_recycle = 3600
+sqlalchemy.convert_unicode = true
+
 ################################
 ### LOGGING CONFIGURATION   ####
 ################################

production.ini

@@ -31,6 +31,15 b' lang=en'
 cache_dir = %(here)s/data
 repos_name = etelko
 
+####################################
+###         BEAKER CACHE        ####
+####################################
+beaker.cache.data_dir=/tmp/cache/data
+beaker.cache.lock_dir=/tmp/cache/lock
+beaker.cache.regions=short_term
+beaker.cache.short_term.type=memory
+beaker.cache.short_term.expire=3600
+    
 ################################################################################
 ## WARNING: *THE LINE BELOW MUST BE UNCOMMENTED ON A PRODUCTION ENVIRONMENT*  ##
 ## Debug mode will enable the interactive debugging tool, allowing ANYONE to  ##
@@ -38,6 +47,21 b' repos_name = etelko'
 ################################################################################
 #set debug = false
 
+##################################
+###       LOGVIEW CONFIG       ###
+##################################
+logview.sqlalchemy = #faa
+logview.pylons.templating = #bfb
+logview.pylons.util = #eee
+
+#########################################################
+### DB CONFIGS - EACH DB WILL HAVE IT'S OWN CONFIG    ###
+#########################################################
+sqlalchemy.db1.url = sqlite:///%(here)s/auth.sqlite
+#sqlalchemy.db1.echo = True
+#sqlalchemy.db1.pool_recycle = 3600
+sqlalchemy.convert_unicode = true
+
 ################################
 ### LOGGING CONFIGURATION   ####
 ################################

pylons_app/config/environment.py

@@ -52,15 +52,15 b' def load_environment(global_conf, app_co'
     
     #MULTIPLE DB configs
     # Setup the SQLAlchemy database engine
-#    if config['debug']:
-#        #use query time debugging.
-#        from pylons_app.lib.timer_proxy import TimerProxy
-#        sa_engine_db1 = engine_from_config(config, 'sqlalchemy.db1.',
-#                                                            proxy=TimerProxy())
-#    else:
-#        sa_engine_db1 = engine_from_config(config, 'sqlalchemy.db1.')
+    if config['debug']:
+        #use query time debugging.
+        from pylons_app.lib.timerproxy import TimerProxy
+        sa_engine_db1 = engine_from_config(config, 'sqlalchemy.db1.',
+                                                            proxy=TimerProxy())
+    else:
+        sa_engine_db1 = engine_from_config(config, 'sqlalchemy.db1.')
 
-    #init_model(sa_engine_db1)
+    init_model(sa_engine_db1)
 
     # CONFIGURATION OPTIONS HERE (note: all config options will override
     # any Pylons config options)

pylons_app/config/middleware.py

@@ -52,7 +52,7 b' def make_app(global_conf, full_stack=Tru'
         # 500 when debug is disabled)
         if asbool(config['debug']):
             #don't handle 404, since mercurial does it for us.
-            app = StatusCodeRedirect(app, [400, 401, 403, 500])
+            app = StatusCodeRedirect(app, [400, 401, 403])
         else:
             app = StatusCodeRedirect(app, [400, 401, 403, 500])
     

pylons_app/controllers/repos.py

@@ -4,7 +4,8 b' from pylons import request, response, se'
 from pylons.controllers.util import abort, redirect
 from pylons_app.lib import auth
 from pylons_app.lib.base import BaseController, render
-
+from pylons_app.model import meta
+from pylons_app.model.db import Users, UserLogs
 log = logging.getLogger(__name__)
 
 class ReposController(BaseController):
@@ -16,7 +17,8 b' class ReposController(BaseController):'
         c.staticurl = g.statics
         c.admin_user = session.get('admin_user')
         c.admin_username = session.get('admin_username')
-        
+        self.sa = meta.Session
+                
     def index(self, format='html'):
         """GET /repos: All items in the collection"""
         # url('repos')

pylons_app/controllers/users.py

@@ -4,7 +4,9 b' from pylons import request, response, se'
 from pylons.controllers.util import abort, redirect
 
 from pylons_app.lib.base import BaseController, render
-from pylons_app.lib import auth
+from formencode import htmlfill
+from pylons_app.model import meta
+from pylons_app.model.db import Users, UserLogs
 log = logging.getLogger(__name__)
 
 class UsersController(BaseController):
@@ -16,14 +18,13 b' class UsersController(BaseController):'
         c.staticurl = g.statics
         c.admin_user = session.get('admin_user')
         c.admin_username = session.get('admin_username')
-        self.conn, self.cur = auth.get_sqlite_conn_cur()
+        self.sa = meta.Session
         
     def index(self, format='html'):
         """GET /users: All items in the collection"""
         # url('users')
         
-        self.cur.execute('SELECT * FROM users')
-        c.users_list = self.cur.fetchall()        
+        c.users_list = self.sa.query(Users).all()     
         return render('/users.html')
     
     def create(self):
@@ -52,20 +53,24 b' class UsersController(BaseController):'
         #           method='delete')
         # url('user', id=ID)
         try:
-            self.cur.execute("DELETE FROM users WHERE user_id=?", (id,))
-            self.conn.commit()
+            self.sa.delete(self.sa.query(Users).get(id))
+            self.sa.commit()
         except:
-            self.conn.rollback()
+            self.sa.rollback()
             raise
         return redirect(url('users'))
         
     def show(self, id, format='html'):
         """GET /users/id: Show a specific item"""
         # url('user', id=ID)
-        self.cur.execute("SELECT * FROM users WHERE user_id=?", (id,))
-        ret = self.cur.fetchone()
-        c.user_name = ret[1]
-        return render('/users_show.html')
+        c.user = self.sa.query(Users).get(id)
+
+        return htmlfill.render(
+            render('/users_show.html'),
+            defaults=c.user.__dict__,
+            encoding="UTF-8",
+            force_defaults=False
+        )        
     
     def edit(self, id, format='html'):
         """GET /users/id/edit: Form to edit an existing item"""

pylons_app/templates/users.html

@@ -32,14 +32,14 b''
             <th>Admin</th>
             <th>Action</th>
          </tr>
-            %for i in c.users_list:
+            %for user in c.users_list:
                 <tr>
-                    <td>${i[0]}</td>
-                    <td>${h.link_to(i[1],h.url('user', id=i[0]))}</td>
-                    <td>${i[3]}</td>
-                    <td>${i[4]}</td>
+                    <td>${user.user_id}</td>
+                    <td>${h.link_to(user.username,h.url('user', id=user.user_id))}</td>
+                    <td>${user.active}</td>
+                    <td>${user.admin}</td>
                     <td>
-	                    ${h.form(url('user', id=i[0]),method='delete')}
+	                    ${h.form(url('user', id=user.user_id),method='delete')}
 	                    	${h.submit('remove','remove',class_="submit")}
 	                    ${h.end_form()}
         			</td>

pylons_app/templates/users_show.html

@@ -1,6 +1,6 b''
 <%inherit file="base/base.html"/>
 <%def name="title()">
-    ${_('User c.user_name')}
+    ${_('User')} - ${c.user.username}
 </%def>
 <%def name="breadcrumbs()">
     ${h.link_to(u'Home',h.url('/'))}
@@ -23,6 +23,28 b''
         </li>
     </ul>
 	<div>
-        <h2>${_('User')} - ${c.user_name}</h2>
+        <h2>${_('User')} - ${c.user.username}</h2>
+        ${h.form(url('user', id=c.user.user_id),method='put')}
+        <table>
+        	<tr>
+        		<td>${_('Username')}</td>
+        		<td>${h.text('username')}</td>
+        	</tr>
+        	<tr>
+        		<td>${_('New password')}</td>
+        		<td>${h.text('new_password')}</td>
+        	</tr>
+        	<tr>
+        		<td>${_('Active')}</td>
+        		<td>${h.checkbox('active')}</td>
+        	</tr>
+        	<tr>
+        		<td></td>
+        		<td>${h.submit('save','save')}</td>
+        	</tr>
+        	        	        	
+        </table>
+        	
+        ${h.end_form()}
     </div>
 </%def>    
  No newline at end of file