scraperwiki / quick-nerdy-sql-tool Goto Github PK

For use in your own APIs

It's useful to run a number of SQL queries to assess the quality of the data (ie look for rows where both profit and loss are nonzero)

I don't really want to publish these, just stash them for later use.

I don't want to have to remember names of tables/fields, or any SQL syntax.

Sometimes it gets into a state where the editor window keeps clearing itself.

i.e. You type a key, and it appears. However, it is selected. So the next letter you type replaces the last one. And you never get any text.

Nobody knows how to reproduce this - i.e. get into the mode where this happens.

For example, I have a (typed in) dataset with a column called "Geek superpower". When I first use the SQL tool the query shown is:

select 
    Name,
    Geek superpower
from data
limit 20

and the result shown is:

Error in SQL

SQL error: no such column: Geek

Trying to use instr in a SQL query results in a "no such function" error message.

Or maybe I should have posted this as "External help docs out of sync with supplied SQL functionality"...

The in-use version of SQLlite is 3.7.9 but the help link for "SQLite core functions" points to a page that describes a more recent (latest?) version. From the looks of things, instr was introduced in 3.7.15.

(originally at sensiblecodeio/custard#168)

Perhaps even make the run button appear in cases where things are slow?

At the very least show better progress, and prevent it firing off quite so many events with stalls between them.

My SQL database has column names containing spaces, when I click on them to select them into the SQL query tool " marks are not added so the SQL query is invalid.

Example:
http://x.scraperwiki.com/dataset/biu6ioa/view/c3c5iai
This is in my datahub and is a view of the Psychology dataset.

Add an option to download results of query as CSV

Build a string containing the CSV in real_run function, code.js, lines 20-37. Then write the string to the opened window api_json line 136

And causes the default query to have an unfriendly syntax error.

This for twitter-connections-tool

Originally reported: scraperwiki/twitter-follows-tool#70

Free user wanted to do a query using REGEXP.

According to the docs:

"The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message."

This particularly annoys Ian. He just wants a "Run" button.

For interesting reasons. Partly he's typing complex queries slowly on a large dataset. So it isn't very fast at doing the queries anyway, which I bet is causing trouble.

Most importantly, he says he really wants to concentrate on and craft the query, explicitly running it when it is ready. It's a kind of concentration and a attention.

took me ages to find the git repo. Who is this frabcus guy?

Ross asked for this

A box-shadow. I'm not sure but I think so.

So that the menu fits on

The following query generates an error, presumably the first line:

CREATE TABLE linked_tweets AS
SELECT
tweet_url,
text
FROM tweets
WHERE text LIKE "%http://t.co%"
ORDER BY created_at DESC;

I would like a button which does a CREATE VIEW from the currently displayed SQL in the database.

This would allow me to publish URLs contains ie "SELECT * from enlightening_view" rather than a huge SELECT query with lots of joins etc.

This is useful for a number of reasons:

• I don't necessarily want the user exposed to my schema
• I'd like to be able to add tables, join on them, and update this without having to modify my published URL
• I'd like to be able to modify column names because I'm pedantic.
• I'd like to have multiple alternative data sources "voting" and I want this to be transparent to the user
• I might want to have a private raw data set and a public (limited) view on it

Perhaps by clicking on its border.

When we have _ tables :)