iswas a direct-query site for the public data dump.
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:
- 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
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 editor definitely is not intended as a code editor, and even though there's a
.Textproperty 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.
- 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.
I 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
AnswerIDand I link to the question. Ends with
UserIdand 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)
Add Url encode/decode buttons to editor toolbar, to support CSV export via theDecided 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.
- 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.
- 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.