Show HN: PipeSQL – Building SQL queries bottom-up using pipes and filters
pipesql.comSo using this tool and building scripts in it just ties users to yet another vendor, right?
In that case, if you are already tied to Oracle why not just use their tools rather than something like this? Oracle's tools will hopefully work in lock-step with their releases and there should be no support lag.
This looks neat, but why add another layer of lock-in to yet another vendor with the uncertainty that entails?
Main aim of this tool is to create complex queries in a bottom-up approach. Say you want to create a query by joining 5 tables. Even though we declare 5 tables in the FROM clause, database will join 2 at a time. And then join another table with the previous result set. So this tool helps to build query similar to execution plan tree structure.
Isn't that kind of doing the job of the query planner ? Do you always come up with something better by yourself ? Genuinely asking here.
Purchasing an add-on product from a third-party vendor is basically the exact opposite of vendor lock-in. Vendor lock-in is when you do what you advocate, which is only buy from your existing vendor (for whatever reason).
I'd like some clarification. If you purchase a closed source add-on from a third-party, aren't you now locked in to 2 vendors instead of 1?
No, "using a product" from a vendor is not vendor lock-in. Especially in the case of a product like this, which turns your employees' work product (queries) from "something that works only with Oracle" (as would be the case if you used Oracle's tooling) into "queries that work with potentially any database."
So when your Oracle rep calls and says "bad news, your license costs went up by 150% this year. What are you going to do, switch databases? Hahahaha!" you can reply "well you know, we can actually flip a switch and run on Postgres now. We'll get back to you."
Yeah, it never quite works out that easily in practice. But by using products from a selection of vendors that support other ecosystems than just one, you're reducing your vendor lock-in.
Yes.
And your risk of finding that all the code you've created using a proprietary tool that runs on top of a proprietary platform owned by another entity is greater than just using the tools provided by the platform vendor who controls "the stack" of software below any other vendor providing an add-on.
Basically, (in this case) Oracle can break 3rd party add-on tools and programs anytime they want or make them operate in a suboptimal way by changing their product below. Oracle, Microsoft and other platform vendors have repeated done this in the past.
So buying cloud based or closed source tools that run on top of other closed source products controlled by another entity is a poor bet.
I'm not sure I understand your point about being tied to a vendor. This isn't tied to Oracle and it's mentioned at the bottom that you can also generate SQL for MySQL, MSSQL, and others.
One would have to purchase this, yes? One would use this tool to build one's queries, yes? So what's not to understand about being tied to yet another vendor?
I thought that was a clear and honest question.
Ah, sorry. I was being naive and assuming this was open source.
Seriously this is going to be charged by usage? A query builder? I am really starting to hate these new pricing models.
When your SaasS startup's product can be implemented as a clojure macro, you probably need to expand your goals a bit.
API would be charge by usage. But the desktop APP will be one time payment(per year) since it will include the parser to translate the pipeSQL to database dialect. Still not finalised, but happy to hear your thoughts on the pricing model.
Most of your favorite languages have sql composition libraries that'll get you 90% of this (Arel for Ruby, SqlAlchemy for Python, etc, etc).
The remainder, the "piping" syntax, is pretty easy to implement (Takes about 30-40 lines of ruby on top of Arel, speaking from experience).
So then, is this just a matter of wrapping a library in a web page and calling it SAAS? Or am I just totally missing the point?
Piping is used to merge the tables rows. Tables can be merged in multiple ways, for example by join, left join, exists, not exists, union, minus etc.. And you can have the pipe chain up to 255 levels.
This might be good for developers, but I'm not sure how it's helpful for either heavy users or beginners. For business analysts, it's just another layer on top of SQL, which slows down processing time, and for beginners, it's now two types of syntax and order they have to learn instead of one.
I don't even think it should be used with Devs. For one, a good dev should know how to build queries, otherwise he/she is missing a good foundation, in which case shouldn't be working with the db. Just my personal take though
I know how to write assembly, but I still develop most of my software in Python. If this makes hairy queries more readable, I can see the use case, even if it doesn't replace SQL itself or the need to know it.
I don't think it is another layer, and I don't think it ties you to a vendor. This is a query builder tool that produces SQL code. You then take that SQL and run it against the DB. Once you've generated the SQL, you can modify it, you're not tied to the original pipesql.
Exactly, its a tool to generate SQL in a elegant manner. Once the SQL is generated, we can uses the generated SQL in the app. There is no need of a new additional layer between apps and database.
That doesn't make any sense. What happens when you need to change the SQL?
To an application developer, this looks like nothing more than a code generator. Generated code is not source code and it should not be edited directly. Rather, you should change the generated code's source code and then re-generate it. While it may not be a layer between the app and the database, it's definitely an extra step in your build chain.
You'd be foolish to tie your application's build process to a cloud service that may disappear without warning. No sane developer would do such a thing. I don't see how this is any use to developers in any form other than a standalone compiler.
This is a tool to generate complex queries in a bottom-up approach. Once the SQL is generated, then we can use the SQL in the application for future use. It's not another layer between apps and database.
http://nareshv.github.io/atomsql/ -- for simple sql operations, inspired by unix shell operators
You can already have a very similar query writing process using the sql WITH clause.
on Oracle:
http://docs.oracle.com/cd/E11882_01/server.112/e10592/statem...
on SQL Server: http://msdn.microsoft.com/en-us/library/ms175972.aspx
And also in Postgres. But once you've got queries that run to about 10 WITH clauses chained one after the other, just because you can't calculate a value in the SELECT clause and then refer to it from within the same statement, you tend to start wishing there was a better way to do it.
As useful as SQL is in so many ways, as a piece of language design it's a total bloody travesty. I'm delighted to see any movement towards a query language that keeps the nice declarative dataflow property that complex SQL queries can have, but provides better means of abstraction, and the possibility to write more intention-revealing code.
Postgres implement WITH in a way that doesn't lend itself to modularity. When you declare a subquery in a WITH block, the query optimiser won't attempt to integrate your query over that boundary. In other words, your entire subquery will be materialised before the lower query is run.
In Oracle's implementation WITH is more like a VIEW.
Edit: BTW, this is not an accident or oversight on the part of Postgres. This is in the spec!
This is not a replacement for SQL. Its a tool to create SQL in a different angle. An angle which is similar to UNIX pipeline concept.
Yes, but what are the advantages over SQL?
I did quite a bit of SQL in my last job, and I can't think of a single case where I wish I had a pipeline operator. If I wanted to do something similar to the example on your homepage the simplest way is just to use subqueries.
Here's a rewrite of your query in T-SQL...
------------------------------------------------------
SELECT EMP.Country, EMP.LastName, EMP.FirstName, OD.OrderID, ORD.ShippedDate, OD.SaleAmount
FROM (SELECT TOP 10 T1.OrderID, T1.ORDSUM FROM (SELECT OrderID, SUM(UnitPrice * Quantity * (1 - Discount)) AS SaleAmount FROM "Order Details" GROUP BY OrderID) AS T1 ORDER BY T1.ORDSUM DESC) AS OD
LEFT JOIN Orders AS ORD ON OD.OrderID = ORD.OrderID
LEFT JOIN Employees AS EMP ON ORD.EmployeeID = EMP.EmployeeID
------------------------------------------------------
That's fairly concise, and was quick to bash out. I'd wager it could be made even smaller if I used LINQ instead of SQL. Perhaps your method has some advantages in other types of query? How do you see it?
This is an example where SQL can be written in multiple ways to get the same result. It doesn't matter how many lines SQL contains since its all depends on how the database engine going to execute it. Therefore, pipeSQL's main aim is to make it easier to read and understand the query after couple of days/weeks :) .
That's what comments are for. Plus, I didn't find the PIPESQL syntax particularly easy to parse, of course that's natural for a new language, but in order to encourage adoption it'd be best for you to look to simplify it further.
Why not use (virtual) views?
IMO, this would be more interesting as an alternative way to write complex queries in an ORM.
Currently, all ORMs that I've seen try to mimic the declarative SQL syntax, which doesn't always look pretty.
The more I look at programs that generate SQL, the more I think we should just learn how to write proper SQL.
Layers are just hiding things, and when you hide it, bad things happen.
I've been wondering the same thing.
But I don't have any real world experience writing software that actually ends up being used with different databases.
I may use all these abstractions, but really we just use Postgres or MySQL in the end.
The entire software industry is built on layers that hide things.
In 99% of cases you don't need to write SQL in order to deliver business functionality. Just like we don't need to write low level machine code in 99% of cases.
You should look at proper SQL. It looks horrible.
One benefit a layer can add is the ability to optimize post compilation. Most of the fancy pants Hadoop libraries are doing this one way or another.