Well how basic is basic ?

Showing some of the features you expect might help.

I think it shouldn't be too difficult to use functional programming to define and combine map like functions like

 select {} from {} where {}

Not exactly SQL, but I'm not sure which complexity and performance you exactly expect without installing complicated CPAN-modules.

But I would be surprised if nobody did this already before...

update

this older discussion about LINQ may be of interest: LINQ/Ambition for Perl?

IMHO you should at least clarify how you want the "basic SQL" DSL to be realized: internally or externally?

Cool. Thanks. By "basic", I mean SELECTs of not more than moderate complexity. No inserts/updates/deletes. What I'm imagining is that what I want can be done by any SQL engine available in Perl.

The data I have is definitely relationally structured; it's well normalized, and there are relation tables for many-to-many relationships.

So if I could do relational calculus using something other than SQL, that would be fine; but remember that I'm limited to whatever is available in a standard perl installation. ActiveState, I believe, though I'm not sure which version. Probably 5.12 or so.

Still don't know if you want an external or internal DSL.

External means you need an interpreter (parser + compiler) for a string holding SQL.

Internal means chaining/cascading Perl functions/methods with "SQL-like" names and semantics in combination with Perl builtins like shown before.

I prefer the latter.

> I mean SELECTs of not more than moderate complexity.

Please gimme some explicit examples of those SQLs of "moderate complexity" and I'll be pleased to show an internal DSL approach built on FP.

Cheers Rolf

( addicted to the Perl Programming Language)

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....

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. :-)

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.

I seem to remember that DBD::AnyData has code to read from arrays (instead of a database).

See probably "Working with in-memory tables" and "Creating an in-memory table from Arrays".

Perhaps that code can be extracted and used.

(If all else fails, check if you do not happen to have postgres available: it has the excellent hstore)

Brilliant. That is exactly what I was looking for. At least, I think it is, based on the doco; I haven't tried it yet. :-)

I have several "tables" of related information in memory, in the form of arrays of hashes, where the elements of the hashes represent named columns of the tables.

Why don't you put that stuff into a real table in the database?

Alexander

If he can't install modules from CPAN wouldn't he have to write a pure perl driver from scratch?

Yes, even you can use CPAN ;)

I appreciate the smiley. Trust me when I say I cannot install modules in this environment. It's not physically impossible, it is forbidden by $management. I'm not going to risk my job over this. That said, I could probably sneak in a few dozen lines of horked code without too much repercussions.

That bit about "working with your manager" is particularly funny, because it isn't just a "manager", it's an entire bureaucracy.

Do you have a personal laptop?

Install perl. Generate some data that mirrors the nature and size of your work data. Write the program that throws it into an sqllite db and queries every which way using sql.

Show you boss. Tell him how long it took you. Tell him how long it would take you do it without the modules you used. Ask him to ask one of your colleagues to give a similar estimate.

I worked one place where I knew up-front such a demonstration would have been taken very badly and I wouldn't have tried it, but most places it stood a chance of making things better.

---

With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

"Science is about questioning the status quo. Questioning authority".

In the absence of evidence, opinion is indistinguishable from prejudice.

Do you really need SQL or would an OO interface similar to File::Find::Rule do what you need ?

You'd have to write it yourself, but it's not going to be that difficult, although testing it might be fun ;).

I've written similar code in the past using iterators and closures that allowed me to program arbitrary queries very quickly.

I think writing a full parser for SQL would be a lot more work, but that's going to depend what problem you're actually trying to solve.