824228 - Implement data export to replace data.sql

Status: RESOLVED WONTFIX

(Webtools Graveyard :: Graph Server, defect)

Description

[andershol]

If you want to set up a local instance of the graphs server for development purposes, it would be helpful be able to get the base data from the live server to have some real data to test with.

The data.sql file is not really serving this purpose since it does not include the ids, but instead relies on auto-numbering. Since the data was not added to the server in the same order as it appears in the file a local installation will get different ids than the server. This makes it harder to import data and reuse urls, since ids are used for fetching data and in urls.

Comment 1

[andershol]

Attachment: Implement data export to replace data.sql

This implements data export in json, sql and html format and deletes the data.sql file. Note that the pages-table is also exported although it was not included in data.sql.

Comment 2

[andershol]

Perhaps a "bug_id"-column should be added to the "branches", "tests" and "machines"-tables for future reference, indicating the bug that caused the rows to be created. For the existing rows these can be populated from Mercurials annotated version of data.sql. But this don't need to hold back the export functionality.

Comment 3

[Robert Helmer [:rhelmer]]

This is ok with me in principle, assuming it's ok for the people that actually work on data.sql (cc'd people I see in the commit log for that)

Comment 4

[Hal Wine [:hwine] use NI!]

While I'd love to get rid of data.sql (tons of bad syntax, etc.), I'm not sure folks in releng would have permissions to run this query.

We especially need the "branches" and "machines" tables. I know we don't have access to to the branches table at the sql level.

Also not quite sure how this interacts with bug 832353.

Comment 5

[Armen [:armenzg]]

This would be great.

Would the export from here be landed on data.sql to have an update record?

Comment 6

[andershol]

(In reply to Hal Wine [:hwine] from comment #4)
> I'm not sure folks in releng would have permissions to run this query.
(as talked about in irc) The queries for the export, would be run by the app, so I do not believe releng (nor the app) would need any new permissions.

> Also not quite sure how this interacts with bug 832353.
That bug seems to be about a complete copy of the db (which I estimate to be several gigabytes), not just the base tables.

(In reply to Armen Zambrano G. [:armenzg] from comment #5)
> Would the export from here be landed on data.sql to have an update record?
Not sure what you mean. If data.sql is removed, no more changes would be made to it.

Any thoughts about if the change suggested in comment 2 would be needed/wanted?

Comment 7

[Armen [:armenzg]]

On another note, the A-team is working towards replacing Graphs DB by the end of Q2 (for older branches we might need to keep posting to the graphs DB riding the trains).
Just fyi in case we want to balance what we need fixed VS waiting on replacement.

(In reply to andershol from comment #6)
> (In reply to Armen Zambrano G. [:armenzg] from comment #5)
> > Would the export from here be landed on data.sql to have an update record?
> Not sure what you mean. If data.sql is removed, no more changes would be
> made to it.
> 
data.sql has been used as a reference since in the past people did not have access to query the data in the tables. It was useful to create patches and give patches for IT to run.

Whatever is getting that on this bug would it help us to be able to write new insertions?

> Any thoughts about if the change suggested in comment 2 would be
> needed/wanted?

Nice to have but I don't find it essential.

Comment 8

[andershol]

(In reply to Armen Zambrano G. [:armenzg] from comment #7)
> On another note, the A-team is working towards replacing Graphs DB
Yes, I was told. But I at that point I had already created the patches.

> Whatever is getting that on this bug would it help us to be able to write
> new insertions?
This url would give you an sql script with inserts, just about the same as data.sql does now, but including column names and "id"-columns:
http://graphs.mozilla.org/graphs/api/basedata?format=sql

Comment 9

[Armen [:armenzg]]

(In reply to andershol from comment #8)
> > Whatever is getting that on this bug would it help us to be able to write
> > new insertions?
> This url would give you an sql script with inserts, just about the same as
> data.sql does now, but including column names and "id"-columns:
> http://graphs.mozilla.org/graphs/api/basedata?format=sql

Cool! That will be great. Thanks for fixing this!

Comment 10

[Robert Helmer [:rhelmer]]

Comment on attachment 695131 [details] [diff] [review]
Implement data export to replace data.sql

This looks ok to me in principle, next step is to land on default branch and try it on dev (and stage if it looks ok).

Thanks for your patience here! This isn't a particularly active project as you've probably noticed :)

Comment 11

[andershol]

The graphs server is apparently going to be replaced at some point, so this bug have no interest.