What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 128 Stack Exchange communities.

StackQL iswas a direct-query site for the public data dump.

Like the original, JavaScript is required, and it's limited to 1000 rows in the result set. (You can bust out of that if you know how, but all queries are logged, and so I'll see it and block anyone caught abusing things too much). There's also a query governor to restrict any really expensive queries, but it should be set high enough right now to let most things through.

What distinguishes this from StatOverflow is that I'm on the October data release rather than July, I'm running SQL Server 2005 rather than MySQL 5 (with Brent Ozar's schema). Also, I'm using the Telerik controls provided to 10K users (an excuse to try them out was one of the reasons for the building this in the first place).

Thanks to the generosity of Mr DailyWTF himself, Alex Papadimoulis, StackQL is now running on a hosted machine with a much better connection. You can now access it here:

http://jcoehoorn.dyndns.org/stackql/

Features:

  • Schema dropdown menus in the toolbar to help jog your memory for table and column names (click one, and it's inserted directly into the text)
  • Basic client-side sorting
  • Logs table to show what others are looking at
  • Better error messages in most cases (a few errors will still sometimes just say "no results returned")
  • The F5 key give you a chance to cancel before re-submitting (you won't lose changed query text)
  • Resize/remove columns
  • Did not HTML encode post bodies, so formatting is at least partially preserved
  • Export to CSV (URL encode the query and pass it to quick.ashx?q=<your%20urlencoded%20query%20here>)

Notes on the Logs table:

  • I left it out of the dropdown lists on purpose
  • Right now it's mostly just my test queries: make sure the system works, make sure the logs table is visible, etc.
  • The QueryHash column uses an MD5 hash, but before taking the hash I convert the query to all uppercase and standardize whitespacing, in an effort to try to group together identical queries that may differ only by formatting.
  • Yes, this means all your vanity searches are visible to everyone.
  • An MD5 hash of your IP address is logged (not the IP address itself). That column doesn't show up by default if you just do a select * from logs, but I don't want anyone to be surprised that it's there or that you can query on it. If anyone can convince me this is a huge problem I might be willing to change how it works.

Notes on the Telerik controls (because I know some will be curious):

  • I had a working proof of concept done with no C# code at all before even installing the controls on my system, so they weren't an enabling factor in this. Of course, I eventually did add some C# code for validation, hash generation, etc, later. But a simpler version of this site is possible using basic, out-of-the-box ASP.NET WebForms controls with no server code.
  • The controls were an interesting of mix of 'wow, that's cool' and frustration. It feels like maybe I didn't get them installed quite right, as some things that should have worked better didn't, there were rendering or JavaScript issues at times, and other odd behaviors.
  • The editor definitely is not intended as a code editor, and even though there's a .Text property I wasn't able to get dynamic syntax highlighting working without seeing an occasional formatting tag end up in my query text. That said, it would be an awesome rich text editor, which is more what it's made for, and I think with more experience and time I might be able to get the syntax highlighting up (I'm not trying though).
  • The controls did enable me to do a few things: Column resizing would not have worked nearly as well without the RADGrid. I'm not a graphic designer, and so having ready-made skins was helpful. Once I understood things, the editor was a big time-saver getting the drop down lists for the schema working.
  • I only needed three of the controls (RADEditor, RADGrid, and RADScriptManager) for this project. There are many more controls included in the toolkit.
  • This was my first experience with these controls, and so some blame for any lingerings issues or oddness likely lies with me rather than the controls.

Planned features:

  • I'm really hoping to get some feedback and help making it look a little nicer, especially the banner. A black box with a white border just doesn't cut it, and I could use a better looking image to go with the logo.
  • DoneI want to add a button to the result column headers that you can use for columns that are PostIDs or UserIDs use to automatically create links to the corresponding question or user profile on Stack Overflow.... Actually, I'm now leaning towards triggering this based on the column names: ends with QuestionId, PostId, or AnswerID and I link to the question. Ends with UserId and I link to the user.
  • DoneA way to link to a query for sharing
  • DoneAdd a feedback link (probably point it here and let people add answers)
  • DoneAdd Url encode/decode buttons to editor toolbar, to support CSV export via the quick.ashx handler. Decided to use a "Results to CSV" button on the toolbar instead.
  • DoneFull-text indexes on Body and Title columns in the posts table, and comment text.
  • Other indexes?
  • Other ideas welcome.

Known bugs:

  • If you allow your session to expire, viewstate validation will fail and you'll get a yellow screen of death. Need to catch that and just reset the viewstate. Turning off viewstate validation doesn't help.
  • Some weird un-reproducable JavaScript weirdness from time to time.
  • Query governor and certain other errors just report 'no results returned' rather than an error
  • Newlines are lost in the query logs
  • HTML isn't stripped from pasted queries correctly
  • Please let me know if you find others.

Any feedback is welcome.

share|improve this question

closed as off-topic by animuson Aug 21 '13 at 2:27

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "The problem described here can no longer be reproduced. Changes to the system or to the circumstances affecting the asker have rendered it obsolete. If you encounter a similar problem, please post a new question." – animuson
If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Showing the entire post body can be nice, but it makes result lists very long. –  jjnguy Sep 21 '09 at 15:45
    
Also, I know horizontal scrolling can be a pain, I would like to see it here so I can show wider columns. –  jjnguy Sep 21 '09 at 15:48
    
It helps if you resize the column, and if you don't want the entire thing you can trim it in your sql code. –  Joel Coehoorn Sep 21 '09 at 15:48
1  
Well, I'm prolly not the best beta tester as I have a very limited knowledge of SQL. –  jjnguy Sep 21 '09 at 15:51
3  
It needs a way to link to a query for sharing. –  deleted Sep 21 '09 at 16:12
1  
@Joel: Why there's no Score column in the Comments table? –  LeakyCode Sep 21 '09 at 17:40
    
I hadn't noticed it was missing. I used the stored procedures posted by brent ozar after the first release to create the database, so I'm guessing those procedures weren't looking for a score column. Unfortunately, I'm not inclined to change the database until after next month's release. –  Joel Coehoorn Sep 21 '09 at 18:20
    
I am amazed at the number of queries I've seen where people tried to use mysql syntax rather than sql server. Things like unix timestamps, limit 100, backtick column names, and more. This SQL SERVER people. It's say T-SQL right up there at the top of the page. –  Joel Coehoorn Sep 21 '09 at 20:54
    
This is pretty cool, but can you get this hosted somewhere? It takes like 30 seconds to load the page from where I am. –  Kyle Cronin Sep 21 '09 at 21:10
1  
@Joel: i'm probably one of them - nearly all the queries i have handy for SO are targeting StatOverflow (and thus, MySQL) - it's easier to just paste and look for errors than it is to try remembering what's different (stupid SqlServer limit limitations...) Hey, look on the bright side: if it wasn't for StatOverflow, i'd be throwing SQLite queries at it... –  Shog9 Sep 21 '09 at 21:27
    
Kyle: unfortunately I don't have anywhere else to put it, but I'll share the code if anyone can come up with a host for it. Also: I applied several updates through the day and since I just announced it today it's likely taking more traffic right now than it will later on: trying checking again in a few days. –  Joel Coehoorn Sep 21 '09 at 21:31
    
@Joel: After waiting a few hours it seems to be much more responsive. I did discover a minor bug - the sorting seems to be text based, even on columns of numbers. Would it be possible to look at the type of the column and use that to direct the kind of sort that's done? –  Kyle Cronin Sep 22 '09 at 0:49
1  
@Joel -- have you given up on this? I get an administrator login panel when I attempt to access the site. –  tvanfosson Mar 20 '10 at 15:07
2  
@tvanfosson - check here: jcoehoorn.dyndns.org/stackql It's a slow machine with no indexes, but it works 'til I have new hosting ready. –  Joel Coehoorn Mar 20 '10 at 18:24
1  
Yes. I don't want to have to clean up all the links to my testing site later. But this one is probably worth it, so I made the edit. –  Joel Coehoorn Apr 5 '10 at 13:09

6 Answers 6

It'd be great if i could pass a query in a querystring, and get a clean table of results back as a response (no AJAX)!

This'd let me suck query results into a Google Docs spreadsheet, create forms for stuffing parameters into queries,and produce graphs and such.

share|improve this answer
    
I'll look into it. Probably build a new page for that, you'll have to urlencode your query, blah blah blah –  Joel Coehoorn Sep 21 '09 at 16:50
    
Check this url: jcoehoorn.dyndns.org/StackQL/… –  Joel Coehoorn Sep 21 '09 at 17:23
    
Works: spreadsheets.google.com/… –  Shog9 Sep 21 '09 at 18:08

via Isaac Waller:

It needs a way to link to a query for sharing

share|improve this answer
    
Good idea. I'm torn: should I add an identity column to the logs table or use the query hash as the key? Probably the former, since the query hash is by design not unique. From there I can just start checking a querystring param for the log id if (!IsPostBack) and using that for the query text. –  Joel Coehoorn Sep 21 '09 at 16:20
    
Then I need to show the log id somewhere in the interface as a permalink. Any ideas where to put it? –  Joel Coehoorn Sep 21 '09 at 16:21
    
Id column is now added to the logs table and populated, so step one finished. –  Joel Coehoorn Sep 21 '09 at 17:25
    
Sorry :( –  Joel Coehoorn Sep 21 '09 at 19:53
1  
Loading the link via querystring parameter works as well (qid). Now just need to show the link when you run the query. –  Joel Coehoorn Sep 22 '09 at 3:35
    
Updated StackQL this evening; the link shows in a the new version. –  Joel Coehoorn Oct 12 '09 at 2:33

Cookies

Tried in Opera, but wasn't able to get anything of a screen. Just the blank. Then figured it might be cookies/sessions. So it goes. Will it only spit back if you have cookies-allow?

Old stuff below:

At first it was like trying to query this:

SELECT ans.ParentId as QuestionID, ans.Score, qs.Title
FROM posts as ans
INNER JOIN posts as qs 
ON ans.ParentId=qs.Id
WHERE ans.PostTypeId='2' and 
      ans.OwnerUserId='23354' and 
      ans.Score > '4' and 
      (ans.CreationDate - qs.CreationDate) > '5097600'
ORDER BY ans.CreationDate DESC

But then it was like:

No records to display.

Which given that it ran on the Stat Overflow Sandbox, is missing out on two rows at least. Is it case sensitive? No maths allowed?

Honestly thought that the Run Query button was for the Media Player to stream some radio.

break the sql

Seriously, you're also missing this:

λ

share|improve this answer
    
I'm sure the problem is this part: (ans.CreationDate - qs.CreationDate) > '5097600'. StatOverflow uses unix timestamps. This does not. It is a problem with the error not being reported correctly though, and I haven't figured out how to trap those yet. –  Joel Coehoorn Sep 21 '09 at 16:03
    
Run the query like this: SELECT ans.ParentId as QuestionID, ans.Score, qs.Title FROM posts as ans INNER JOIN posts as qs ON ans.ParentId=qs.Id WHERE ans.PostTypeId='2' and ans.OwnerUserId='23354' and ans.Score > '4' and datediff(d, qs.CreationDate, ans.CreationDate) > 59 ORDER BY ans.CreationDate DESC –  Joel Coehoorn Sep 21 '09 at 16:06
    
And yes, that is the media player logo. I had a limited icon set to pick from, and that one most-closely matched what I wanted to do. Also note the "i" icon next to column: I have ShowIcon="False" for that button, but there it is anyway. –  Joel Coehoorn Sep 21 '09 at 16:07
5  
This raises another good question: what's appropriate music for SQL coding? –  Shog9 Sep 21 '09 at 16:18
5  
@Shog9: Heavy metal so that I can at least bash my face on the keyboard to the rhythm. –  TheTXI Sep 21 '09 at 16:31
    
The Runtime Error isn't making for easy testing. –  random Sep 22 '09 at 3:27
    
Back to normal now, for the time being. "Normal" should be changing soon, for the better ;) –  Joel Coehoorn Sep 22 '09 at 4:04
1  
every time you say "Opera", god kills an angel. –  Jeff Atwood Oct 4 '09 at 4:17
    
Just saw the cookies note. I have no idea: i'm pretty sure that cookie is set by one of the telerik contorls, and I'd never used them before this so I couldn't yet begin to tell you what I need to do to get around it. –  Joel Coehoorn Oct 12 '09 at 2:32

Just adding a note where people will see it that I've temporarily raised the query governor limit. A lot of queries that used an order by clause were bumping up against it, and so I raised the limit.

Note that I expect this to be temporary. I suspect there was only a problem because the database is so new, and so there were no actual statistics to use as a base-line for cost estimates. I'll leave it up like this for week or two to establish better costs, and then run some test to see where the final number should be.

The 500 record limit on the result set remains.

share|improve this answer
1  
this should not be an answer but an edit to the question, ditto for your other "answer" –  Jeff Atwood Oct 4 '09 at 4:17

Would it be possible to export a SQL query as HTML?

share|improve this answer
    
What does html do for you that csv or the current grid doesn't? –  Joel Coehoorn Sep 22 '09 at 21:30
    
I'd like to have a set of questions/answers offline occasionally, but be able to view the data in a readable format (close to the original format ideally) –  Chris S Sep 23 '09 at 8:24
    
I think I'm gonna decline this- since the html formatting is including inside the csv column it's easy enough to build your own offline reference from the csv data. –  Joel Coehoorn Oct 12 '09 at 16:27

Another update:

Increased command timeout to 2 minutes (it was set far too low) and trapped a certain class of error I was missing that should eliminate most yellow screens.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged .