Yes. Queries to me is a "annoying" requirement of software/product/website development. What is strange to me is that we are living in 2010 and yet here we are writing queries for a backend layer that should simply be a lot more easy and fast to do. Imagine for websites, you have to create a database with tables and fields and so on, then you have to write extra code in some perhaps to you foreign language where you select what data you wish to present based on some parameter.<p>Why oh god why!? I don't want to write queries, and it's not because I think it's hard or anything like that, the reason is more about time and funfactor. Writing queries to me is the most boring part of any project and often a very time consuming process, imagine spending this time designing or improving your app in other ways. So what could we have instead?<p>Well imagine a perfect world where all you would have todo is create some table/fields or documents depending if it's a relational or no sql db. Then you would simply through some amazingly easy and efficient interface select what data you want to present and it would automagically generate whatever code/query or a process that is necessary, then you would just invoke that process to obtain that data.<p>Nooo no no I am NOT talking about query builders, those are horrible! They take even longer time to work with than just writing plain simple query code, I am talking about a revolution in the way we deal with this layer - "queries" I call it a "layer" because to me it's like a layer of a development process that is necessary but should not be as time consuming or boring as it currently is. Sorry to all you query writing lovers out there!<p>There should be some tool or way to generate a function/stored procedure/x that outputs your data as arrays/objects/json/xml/whatever based on some simple dragging or dropping or marking that you would do with a super neat efficient interface. The tool would then do all the work for you, and whatever query/procedure/function that it generates would already be amazingly optimized, now you would just invoke it and bam there is your result set ready in some array or object for you to use as you want. Que rico, verdad?<p>Maybe I am living in the 90s still, maybe this already exists, and I have been living under a rock, perhaps I am asking for too much, who knows I am just letting the world know and I am sure there is a lot of people that can agree with me. Especially people more oriented at design or frontend stuff...<p>Gaah the frustration,
Please don't hate, ps english is not my mother tongue so forgive me if you can't understand it ;-)
I don't get it. Queries are a precise, textual specification of the data you need from a database. How can you hate that?<p>Perhaps what you mean is you hate dealing with converting the results to the data types you will use in your application?<p>Perhaps what you mean is you hate the administrative overhead of creating separate files and dealing with deployment when creating stored procedures?<p>Perhaps what you mean is you hate properly inserting your native data types into a written SQL query in a secure way?<p>Or do you actually hate writing SQL? To me, that's the best part of interacting with databases.
Try Ruby on Rails!<p>With Active Record you rarely if ever need to write SQL any more.<p><a href="http://guides.rubyonrails.org/association_basics.html" rel="nofollow">http://guides.rubyonrails.org/association_basics.html</a><p><a href="http://guides.rubyonrails.org/active_record_querying.html" rel="nofollow">http://guides.rubyonrails.org/active_record_querying.html</a><p>I think SQL is slowly going the way of assembly language over time - still useful to write by hand in special cases but most of the time you want to write in something higher level and have it generated automatically.
Here is why SQL exists: <a href="http://en.wikipedia.org/wiki/Relational_algebra" rel="nofollow">http://en.wikipedia.org/wiki/Relational_algebra</a>. Most criticisms of SQL are wrong because the people who write them never bother to read the mathematics or theory behind their construction.<p>Any language with similar power will be just as complex.<p>If you just have a limited use case that you want optimized, that's what most ORMs try to do.
ORMs do that, mostly. Look at Active Record: <a href="http://en.wikipedia.org/wiki/Active_record_pattern" rel="nofollow">http://en.wikipedia.org/wiki/Active_record_pattern</a><p>The problem with ORMs is that relational databases are complicated, and you often do wind up having to do manual SQL writing anyway if you want well-performing queries in edge cases. But for simple cases where you have a person table and need to make Person objects in your app... it works.<p>In fact, Rails used this as one of its big selling points, combined with convention-over-configuration. If you stuck to the naming patterns Rails preferred, it really would Just Work without you having to tell it anything beyond the names of your classes.
Sounds like Microsoft Access.<p>Couple things:<p>1. Most of the times, when you're building a webapp, your queries depend upon user input. How would you model that in your GUI? Once you start having to do significant processing on your input to figure out what to query, the visual model falls down pretty hard.<p>2. I suspect that if you actually used this interface in day-to-day work, you wouldn't like it so much. There's a reason why many hardcore hackers still use the command line: once you've burned the language syntax into your brain, you can get things done much quicker than with a GUI. If you <i>do</i> like it, I'd encourage you to try MS Access.
I havent written a query in almost 4 years.<p><a href="http://rubyonrails.org" rel="nofollow">http://rubyonrails.org</a><p><a href="http://code.google.com/p/redis/" rel="nofollow">http://code.google.com/p/redis/</a><p><a href="http://github.com/nateware/redis-objects" rel="nofollow">http://github.com/nateware/redis-objects</a>
Why don't you imagine a perfect world where business people define their rules and easily draw the screen they want, and the rest is auto generated by the tool (<a href="http://en.wikipedia.org/wiki/Computer-aided_software_engineering" rel="nofollow">http://en.wikipedia.org/wiki/Computer-aided_software_enginee...</a>)?<p>Apparently, there are some great complexities in the software engineering process which can't be visualized or simplified enough to be aided by a tool.<p>SQL or the programming language you have is textual dsl's, abstracting you from the details / complexities and powerful enough that you can access all the underlying functionality. ORM's, ActiveRecord pattern etc. are all another abstraction but they're not as powerful as bare SQL. As long as you don't hit their wall, you'll be OK. But keep in mind that each layer makes some functionality inaccessible.<p>I once wrote a visual designer for Castle ActiveRecord & NHibernate, called ActiveWriter. It was great for the begginer on that technologies. Once you need more, the tool lacks the functionality since it gets harder and harder to implement all the details of a textual DSL in a graphical DSL. Today, I prefer Fluent NHibernate for my mappings since it's more complete on the mapping front. It seems more work but it deals with the complexity better than my own graphical tool.
Grails with GORM is the closest I've found. Not perfect, but I rarely write 'queries' for most projects unless I have specific complex reporting needs with performance considerations.<p><a href="http://www.grails.org/GORM" rel="nofollow">http://www.grails.org/GORM</a><p>I don't write create scripts, or data management stuff, or SQL of any kind.<p>def smithUsers = User.findAllByLastname("smith")<p>It's not drag/drop/gui, but it's a lot less mental friction for me when writing code.
I feel your pain writing SQL Queries is bug prone exercise. And trying to optimize a SQL query shows that it is a leaky abstraction.<p>Things that I think are a better option are:<p>1. Object Oriented Databases (OODB) A database that behaves like your objects. I like Gemston(Ruby & smalltalk) and db4o (java & .Net)<p>2. Graph databases: <a href="http://neo4j.org/" rel="nofollow">http://neo4j.org/</a>
Neo4j is a graph database. It is an embedded, disk-based, fully transactional Java persistence engine that stores data structured in graphs rather than in tables. A graph (mathematical lingo for a network) is a flexible data structure that allows a more agile and rapid style of development.<p>Try this alternative databases... I think they are close to the answer.<p>PS: ORMapping is not a solution... it always ends up being messy: "Object-Relational Mapping is the Vietnam of Computer Science" <a href="http://www.codinghorror.com/blog/2006/06/object-relational-mapping-is-the-vietnam-of-computer-science.html" rel="nofollow">http://www.codinghorror.com/blog/2006/06/object-relational-m...</a>
It might be good to go beyond your comfort zone and dive into relational theory and practices. Data tend to live way longer than applications. You want to have a language and application agnostic way to store and access data so that different applications in different languages can share the same data. You also can evolve your application platform and languages using the same data over time. Want to re-write your app in C# instead of Ruby? No problem. The data and access method stay the same.<p>Of course if you are just building throw-away prototype or single usage app, you don't have to use RDBMS. Use object serialization, persistent hashtable, or OODBMS. Those tend to mesh well with the language you are using.
Of course, there's almost a guaranteed trade-off between making things super easy, and making things super-specific and customizable. There are lots and lots of different requirement possibilities that are possible even with a small amount of tables, and anything that builds these queries for you needs to know about all of them, or <i>be able</i> to know about all of them, at which case you're going to have to describe some things to it in some sort of, um, query.<p>I'm positive there is some framework that makes touching a DB barely needed, but I don't personal know it. I'm going to upvote your thread and hope somebody comes along and tells you what it is.
<i>There should be some tool or way to generate a function/stored procedure/x that outputs your data as arrays/objects/json/xml/whatever based on some simple dragging or dropping or marking that you would do with a super neat efficient interface. The tool would then do all the work for you, and whatever query/procedure/function that it generates would already be amazingly optimized, now you would just invoke it and bam there is your result set ready in some array or object for you to use as you want.</i><p>Write it yourself? If you did it well, maybe it would help other people understand your point.
Clearly you have never needed to do anything remotely complex with a database. There is a reason visual programing languages aren't successful outside of a few extremely simple domains.
What you are describing is the combination of a fifth generation programming language, a universal semantic/ontology data layer, and an intelligent search engine with insight into the information you want, where it is, and how you want it computed and displayed.<p>I assure you, this is an even harder problem than it sounds.
Queries work -<p>An SQL statement tells you in almost-English exactly what to expect<p>The SQL standard allows me to understand what's going on in an MSSQL query when I've never used MSSQL before.<p>Conclusion: even though you say writing queries is easy for you, it probably isn't. Once you familiarize yourself better with SQL, you will appreciate it.
Writing queries is one of my favorite activities. Some of the queries I had to write for w3roi.com are two pages long printed in 10 point font. They're efficient as possible and quite beautiful to me.
The best you can do is either write a layer on top of SQL or simply use a NoSQL database... Dragging and dropping won't work, because you would still have to use the results in your application.
The reason we still need 'queries' or maybe 'handmade data access' is because you dont know all the questions your systems will have to answer upfront.
I prefer Django's ORM but using the ORM can be quite tricky if the database has not been created with the ORM in mind (i.e. you have a few pages of subqueries).