store project name and path

define db ORM models to allow storing data in the existing project table
and add a list command to query it

aeromancer/cli/app.py

@@ -5,8 +5,9 @@ import sys
 from cliff.app import App
 from cliff.commandmanager import CommandManager
 import pkg_resources
+from sqlalchemy.orm import sessionmaker
 
-from aeromancer.db import migrations
+from aeromancer.db import connect, migrations
 
 
 class Aeromancer(App):
@@ -51,7 +52,11 @@ class Aeromancer(App):
     def initialize_app(self, argv):
         self.log.debug('updating database')
         migrations.run_migrations()
+        self.engine = connect.connect()
+        self._session_maker = sessionmaker(bind=self.engine)
 
+    def get_db_session(self):
+        return self._session_maker()
 
     # def prepare_to_run_command(self, cmd):
     #     self.log.debug('prepare_to_run_command %s', cmd.__class__.__name__)

aeromancer/cli/project.py

@@ -1,8 +1,11 @@
 import logging
 import os
 
-from cliff.command import Command
+from aeromancer.db.models import *
 
+from cliff.command import Command
+from cliff.lister import Lister
+from sqlalchemy.orm.exc import NoResultFound
 
 class Add(Command):
     "(Re)register a project to be scanned"
@@ -15,11 +18,32 @@ class Add(Command):
             'project',
             nargs='+',
             default=[],
-            help='project directory names under the project root, e.g., "stackforge/aeromancer"',
+            help=('project directory names under the project root, '
+                  'for example: "stackforge/aeromancer"'),
         )
         return parser
 
     def take_action(self, parsed_args):
+        session = self.app.get_db_session()
         for project in parsed_args.project:
             project_path = os.path.join(self.app.options.repo_root, project)
-            self.log.info('adding project %s from %s', project, project_path)
+            query = session.query(Project).filter(Project.name == project)
+            try:
+                proj_obj = query.one()
+                proj_obj.path = project_path
+                self.log.info('updating project %s from %s', project, project_path)
+            except NoResultFound:
+                proj_obj = Project(name=project, path=project_path)
+                self.log.info('adding project %s from %s', project, project_path)
+                session.add(proj_obj)
+        session.commit()
+
+
+class List(Lister):
+    """Show the registered projects"""
+
+    def take_action(self, parsed_args):
+        session = self.app.get_db_session()
+        query = session.query(Project).order_by(Project.name)
+        return (('Name', 'Path'),
+                ((p.name, p.path) for p in query.all()))

aeromancer/db/alembic/versions/575c6e7ef2ea_create_project_table.py

@@ -18,7 +18,7 @@ def upgrade():
     op.create_table(
         'project',
         sa.Column('id', sa.Integer, primary_key=True),
-        sa.Column('name', sa.String(), nullable=False),
+        sa.Column('name', sa.String(), nullable=False, unique=True),
         sa.Column('path', sa.String()),
     )
 

aeromancer/db/connect.py

@@ -0,0 +1,14 @@
+import os
+
+from sqlalchemy import create_engine
+
+
+def get_url():
+    """Return the database URL"""
+    db_file_path = os.path.expanduser('~/.aeromancer/aeromancer.db')
+    return "sqlite:///%s" % db_file_path
+
+
+def connect():
+    """Return a database engine"""
+    return create_engine(get_url())

aeromancer/db/migrations.py

@@ -6,6 +6,7 @@ from alembic.environment import EnvironmentContext
 from alembic.script import ScriptDirectory
 from sqlalchemy import engine_from_config, pool
 
+from aeromancer.db import connect
 
 LOG = logging.getLogger(__name__)
 
@@ -13,7 +14,8 @@ LOG = logging.getLogger(__name__)
 def run_migrations():
     config = Config()
     config.set_main_option("script_location", "aeromancer.db:alembic")
-    config.set_main_option("sqlalchemy.url", "sqlite:////Users/dhellmann/.aeromancer/aeromancer.db")
+    url = connect.get_url()
+    config.set_main_option("sqlalchemy.url", url)
     command.upgrade(config, 'head')
 
 

aeromancer/db/models.py

@@ -0,0 +1,11 @@
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy import Column, Integer, String
+
+Base = declarative_base()
+
+
+class Project(Base):
+    __tablename__ = 'project'
+    id = Column(Integer, primary_key=True)
+    name = Column(String, unique=True, nullable=False)
+    path = Column(String)

setup.cfg

@@ -51,3 +51,4 @@ console_scripts =
 
 aeromancer.cli =
     add = aeromancer.cli.project:Add
+    list = aeromancer.cli.project:List