Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Config file with support for defining canned queries #20

Closed
simonw opened this issue Oct 23, 2017 · 9 comments
Closed

Config file with support for defining canned queries #20

simonw opened this issue Oct 23, 2017 · 9 comments

Comments

@simonw
Copy link
Owner

simonw commented Oct 23, 2017

Probably using YAML because then we get support for multiline strings:

bats:
  db: bats.sqlite3
  name: "Bat sightings"
  queries:
    specific_row: |
      select * from Bats
      where a = 1;
@simonw simonw modified the milestones: Ship v1, v1 stretch goals Oct 23, 2017
@simonw
Copy link
Owner Author

simonw commented Oct 23, 2017

Maybe this should be handled by views instead?

https://stateless-datasets-wreplxalgu.now.sh/ lists some views

https://stateless-datasets-wreplxalgu.now.sh/?sql=select%20*%20from%20%22Order%20Subtotals%22 is an example showing the content of a view.

What would the URL to views be? I don't think a view can share a name with a table, so the same URL scheme could work for both.

@simonw
Copy link
Owner Author

simonw commented Nov 10, 2017

I'm going to handle this a different way. I'm going to support a local history of your own queries stored in localStorage, but if you want to share a query you have to do it with a URL.

If people really want canned query support, they can do that using custom templates - see #12 - or by adding views to their database before they publish it.

@simonw simonw closed this as completed Nov 10, 2017
@simonw simonw added the wontfix label Nov 10, 2017
@simonw
Copy link
Owner Author

simonw commented Dec 1, 2017

I've found some examples of canned queries I want to support that can't be represented as views, so I'm going to reopen this.

@simonw simonw reopened this Dec 1, 2017
@simonw
Copy link
Owner Author

simonw commented Dec 1, 2017

I'll use the existing metadata.json file:

{
    "databases": {
        "mydb": {
            "queries": {
                 "custom_thingy": {...

The query definition can either be just a string of SQL, or it can be an object with a sql key and optional title and description keys.

@simonw simonw removed the wontfix label Dec 4, 2017
@simonw
Copy link
Owner Author

simonw commented Dec 4, 2017

While I'm doing this, I could add per-database and per-table metadata too ala #68

@simonw
Copy link
Owner Author

simonw commented Dec 4, 2017

This is also a good opportunity to re-factor out a separate query.html template - right now the database.html template is doing two jobs.

@simonw simonw self-assigned this Dec 5, 2017
simonw added a commit that referenced this issue Dec 5, 2017
Named canned queries can now be defined in metadata.json like this:

    {
        "databases": {
            "timezones": {
                "queries": {
                    "timezone_for_point": "select tzid from timezones ..."
                }
            }
        }
    }

These will be shown in a new "Queries" section beneath "Views" on the database page.

As part of this, I refactored the logic for the database index page. It used
to combine the functionality for listing available tables and the
functionality for executing custom SQL queries in a single template and view.
I have split that template out into database.html and query.html and reworked
the view to more clearly separate the custom SQL executing code.

Refs #20
@simonw
Copy link
Owner Author

simonw commented Dec 5, 2017

Named canned queries can now be defined in metadata.json like this:

    {
        "databases": {
            "timezones": {
                "queries": {
                    "timezone_for_point": "select tzid from timezones ..."
                }
            }
        }
    }

These will be shown in a new "Queries" section beneath "Views" on the database page.

timezones

@simonw
Copy link
Owner Author

simonw commented Dec 5, 2017

@simonw
Copy link
Owner Author

simonw commented Dec 5, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant