Re: search data structures using SQL

First, this is rather vague. I don't know how complex SQL you have to support and you have to figure that as soon as you implement one feature someone else will say they can't live without another feature.

What you are essentially talking about is implementing an in-memory db in Perl. this is not likely to remain "easy" or "only using modules in the standard library" for long and it is critically important that you inform $management of these problems before you get started. Let them figure out what they *really* want from this. Otherwise you will end up trapped in a hole that will just keep getting deeper and $management will decide that the way out is to tunnel to $antipode.

Now this being said, the next thing is to say that if you are going to support it, you are only going to support very simple searches in the form of SELECT column_list FROM table_name WHERE condition. From there simple parsers should be possible using regexps. Note that this is a *tiny* subset of SQL and if they want anything else, or to be able to have a column named "from" (not an unreasonable request) you are going to need something like Parse::RecDescent and restart this from scratch. Basically at that point, you get into hierarchy of language problems.

However, my advice is that before you do this, stop, and have the difficult discussion with your boss. Inform him of what the real problems are, and that this will involve reinventing lots of stuff from CPAN if it goes beyond basic parsing with a total prohibition on use of reserved words as names. $management gets paid to make the decisions. Let them make it. But make sure that they know what they are getting into....

Comment on Re: search data structures using SQL

Replies are listed 'Best First'.
Re^2: search data structures using SQL by jdporter (Paladin) on Nov 16, 2013 at 16:40 UTC
Thanks. I can give you more details when I get back to the office on Monday. But there's no point in talking about my boss's involvement in any of this, because he has none. This is a tool I'm building only for myself (and my successor, jah forbid). I've written a bletcherous framework in Plain Ol' Perl, for threading through table relations and such, and I just feel like it would be cleaner to replace all that with SQL. But I'm willing to concede that I may have asked an XY question. :-)	[reply]
Re^3: search data structures using SQL by einhverfr (Friar) on Nov 17, 2013 at 05:05 UTC
If you are going to do that, why not just skip SQL, take what is good from SQL, and apply Perlisms to it? I.e. instead of SELECT foo FROM bar WHERE baz is not true; why not come up with an SQL-inspired interface that avoids the parsing issues, something like: `select( columns => ['foo'], start_table => 'bar', condition => '? or !defined ?', bind => ['baz', 'baz'] );` [download] This would avoid the parsing problems, allow you to add joins, cte's, inline views, and more if you ever need it. It would avoid the hard problems while giving you something where you and your successor could leverage sql knowledge. That means essentially passing in the parse tree rather than the declarative statement to the function, and as such it also avoids the possibility of sql injection.	[reply] [d/l]
Re^4: search data structures using SQL by jdporter (Paladin) on Nov 18, 2013 at 02:03 UTC
Good point; but it's not the interface I care about. I'm not insisting that it be SQL. What I'm looking for is a replacement for the bletcherous blob of an engine I wrote. I assume (yeah, I know) that an engine that implements an SQL interface will be able to do what I want. If I can get the engine without the SQL, that's fine too.	[reply]


We don't bite newbies here... much
	PerlMonks