store the lines in files

record the contents of files in a separate table

change the one-to-many relationships on project->file->line to allow
cascading deletes

aeromancer/db/alembic/versions/1fb08a62dd91_add_line_table.py

@@ -0,0 +1,29 @@
+"""add line table
+
+Revision ID: 1fb08a62dd91
+Revises: 5123eb59e1bb
+Create Date: 2014-10-30 17:52:17.984359
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = '1fb08a62dd91'
+down_revision = '5123eb59e1bb'
+
+from alembic import op
+import sqlalchemy as sa
+
+
+def upgrade():
+    op.create_table(
+        'line',
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('file_id', sa.Integer,
+                  sa.ForeignKey('file.id', name='fk_line_file_id')),
+        sa.Column('number', sa.Integer, nullable=False),
+        sa.Column('content', sa.String()),
+    )
+
+
+def downgrade():
+    op.drop_table('line')

aeromancer/db/models.py

@@ -10,6 +10,9 @@ class Project(Base):
     id = Column(Integer, primary_key=True)
     name = Column(String, unique=True, nullable=False)
     path = Column(String)
+    files = relationship('File',
+                         backref='project',
+                         cascade="all, delete, delete-orphan")
 
 
 class File(Base):
@@ -18,5 +21,14 @@ class File(Base):
     project_id = Column(Integer, ForeignKey('project.id'))
     name = Column(String, nullable=False)
     path = Column(String)
+    lines = relationship('Line',
+                         backref='file',
+                         cascade="all, delete, delete-orphan")
 
-    project = relationship("Project", backref=backref('files', order_by=name))
+
+class Line(Base):
+    __tablename__ = 'line'
+    id = Column(Integer, primary_key=True)
+    file_id = Column(Integer, ForeignKey('file.id'))
+    number = Column(Integer, nullable=False)
+    content = Column(String)

aeromancer/project.py

@@ -5,7 +5,7 @@ import logging
 import os
 import subprocess
 
-from aeromancer.db.models import Project, File
+from aeromancer.db.models import Project, File, Line
 from aeromancer import utils
 
 from sqlalchemy.orm.exc import NoResultFound
@@ -72,6 +72,8 @@ def _update_project_files(session, proj_obj):
         fullname = os.path.join(proj_obj.path, filename)
         if not os.path.isfile(fullname):
             continue
+        new_file = File(project=proj_obj, name=filename, path=fullname)
+        session.add(new_file)
         with io.open(fullname, mode='r', encoding='utf-8') as f:
             try:
                 body = f.read()
@@ -83,7 +85,8 @@ def _update_project_files(session, proj_obj):
                 continue
             lines = body.splitlines()
             LOG.info('%s/%s has %s lines', proj_obj.name, filename, len(lines))
-        session.add(File(project=proj_obj, name=filename, path=fullname))
+            for num, content in enumerate(lines, 1):
+                session.add(Line(file=new_file, number=num, content=content))
 
 
 def discover(repo_root):