Design database schema for history of each project

[migurski]

After issue 7,

• [x] Design DB schema for Postgres.
• [x] Connect webhook URL for project updates.
• [x] Connect view URL for office screen.

[monfresh]

I have zero experience with Flask, but this looks a lot leaner and simpler to install than the original Pivotal Rails app, so thanks for that! The one thing missing is instructions for setting up the database, and I see it's one of your to-do items. Do you know when that might happen? Or perhaps you already have a schema.sql you can share?

[migurski]

Check out statuses.pgsql, it has the schema currently in use.

I will improve the README!

On Feb 19, 2015, at 6:41 AM, Moncef Belyamani notifications@github.com wrote:

I have zero experience with Flask, but this looks a lot leaner and simpler to install than the original Pivotal Rails app, so thanks for that! The one thing missing is instructions for setting up the database, and I see it's one of your to-do items. Do you know when that might happen? Or perhaps you already have a schema.sql you can share?

— Reply to this email directly or view it on GitHub.

[monfresh]

Thanks! I made an educated guess about the column types. This is what I ended up doing, and it seems to work. Well, at least the local website is not crashing.

createdb projectmonitor
psql projectmonitor

create table projects (
  id integer NOT NULL PRIMARY KEY,
  guid text
);

create table project_statuses (
  id integer NOT NULL PRIMARY KEY,
  build_id text,
  created_at timestamp without time zone,
  error text,
  project_id integer NOT NULL,
  published_id text,
  success text,
  url text,
  updated_at timestamp without time zone,
  valid_readme text
);

CREATE VIEW statuses
AS
  SELECT p.guid,
         s.success, s.url, s.updated_at, s.valid_readme
  FROM projects AS p
  LEFT JOIN project_statuses AS s
  ON s.project_id = p.id
  ORDER BY updated_at ASC;

\q 

export DATABASE_URL="postgresql://localhost/projectmonitor"
python runserver.py

[migurski]

Ah—there’s not actually a need for a project table. The master list of projects comes from a JSON file, and I'd like to ask people to submit new projects via PR’s rather than by getting something into the database. The schema might actually be complete.

[monfresh]

It makes sense to have projects read from JSON, but if there is no projects table, how does the creation of the statuses view know that projects refers to the JSON file and not a DB table? When I run the psql command to create the statuses view while a projects table does not exist, it says "relation projects does not exist" (or something to that effect).

Also, I see DB actions related to a projects table in __init__.py, like here and here. Is that not an actual table?

I deployed the app successfully to Heroku, but it's not showing me my project-monitor app: https://project-monitor.herokuapp.com/

I verified that the Travis build passed, and that the webhook is pointing to the right URL, and that it matches the project guid.

What am I missing? I don't see anything helpful in the heroku logs.

[migurski]

Oh crap, you're absolutely right.

I neglected to drag over the original schema from the last Rails version, which would be needed to make this work.

I think it’s correct for there to be only a statuses table, but the schema needs to be changed from a view on a phantom table like it is now to a table of its own. That’s what the first unticked box is on this issue, Design DB schema.

Sorry for answering before I had reminded myself about the most-recent state of this project.

[monfresh]

Fixed all the things. Pull request coming soon.

[migurski]

Finished with 5ca4c16 and #14.