Re: [sqlite] RPAD/LPAD

[Max Vlasov]

On Sat, Mar 8, 2014 at 10:52 AM, Max Vlasov  wrote:
> On Fri, Mar 7, 2014 at 11:51 PM, Dominique Devienne  
> wrote:
>>
>> basically register_function('rpad', 'x', 'y', 'printf(''%-*s'', y,
>> x)') would register a 2-arg function (register_function's argc-2)
>> named $argv[0], which executes the following statement
>>
>> with args($argv[1],  $argv[2], ... $argv[argc-2]) as (VALUES(?, ?))
>> select $argv[argc-1) from args;
>>
>
> Dominique, your variant is even better than using numbered parameters.
> If you use named ones supported by sqlite (:VVV) then sqlite will do
> the job of the textual replacement with bind api itself  (
> 'printf(''%-*s'', :y,> :x)'  )
>
> The small problem in this case is that there are two ways (times) to
> check whether named parameter exists in the expression.

No longer problem here :) SQLite3_Bind_Parameter_name is available at
the time of registration, so the prepared statement knows everything
about the number and the names of the parameters for full checking.

I have a working prototype, some things are left to do, but this
confirms that sqlite is content with the contexts and everything.
Examples:

SELECT RegisterExpressionFunction('myfunc', '45');
Select Myfunc()
45

SELECT RegisterExpressionFunction('mysum', 'x', ':x + :x');
select mysum(45)
90

SELECT RegisterExpressionFunction('mysumalt', '', '', '?1 + ?2');
select mysumalt(45, 67)
112

SELECT RegisterExpressionFunction('strconcat', 's1', 's2', ':s1||:s2');
Select strconcat('foo ', 'bar')
foo bar


Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Max Vlasov]

On Fri, Mar 7, 2014 at 11:51 PM, Dominique Devienne  wrote:
>
> basically register_function('rpad', 'x', 'y', 'printf(''%-*s'', y,
> x)') would register a 2-arg function (register_function's argc-2)
> named $argv[0], which executes the following statement
>
> with args($argv[1],  $argv[2], ... $argv[argc-2]) as (VALUES(?, ?))
> select $argv[argc-1) from args;
>

Dominique, your variant is even better than using numbered parameters.
If you use named ones supported by sqlite (:VVV) then sqlite will do
the job of the textual replacement with bind api itself  (
'printf(''%-*s'', :y,> :x)'  )

The small problem in this case is that there are two ways (times) to
check whether named parameter exists in the expression. Either when
the registered function executed (and report error about lacking some
parameters only here) or make a test call with some test parameters at
the time of registration.

The latter allows revealing errors earlier, but it has no knowledge
about the expression so might fire strange run-time errors (division
by zero etc). If we omit execution and leave only binding, we might
miss the case when not all binding variables mentioned in the
expression, actually provided (example ... 'somefunc', 'x', 'y', ':x +
:y + :z')

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Max Vlasov]

On Sat, Mar 8, 2014 at 2:16 AM, Clemens Ladisch  wrote:
> Eduardo Morras wrote:
>> So, if a webapp that uses SQLite doesn't check it's input, functions that 
>> renames SQLite internals can be injected
>>
>> SELECT register_simple_function('MAX', 1, 'DROP TABLE ?');
>
> Such a statement would not return a single column, so it wouldn't
> actually get executed.
>
> But it might be possible to execute something like "PRAGMA evil = on",
> so this function probably should be secured like load_extension().
>

Absolute evil ) I already thought that introducing such function
violates a common sense assumption that Select Api is side-effect free
(in context of database changes) since "Register" slightly violates
this by messing with namespace context. Allowing non-Select queries
might pose damage risk because it would complain after the damage is
done (no SQLITE_ROW result for a Update or Insert query, but database
had already changed to the moment). That's also why I still think that
constraining it to Select Api with assuming expression and automatic
wrapping in Select is a must.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Clemens Ladisch]

Dominique Devienne wrote:
>> On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch  wrote:
>>> SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>
> But doesn't the above assume you can bind values inside the select
> clause? I thought one couldn't...

Binding values is allowed everywhere where you have an expression.
"SELECT ?" is fine, just as "SELECT 42" is fine (but useless in most
circumstances).

In pratice, the select clause usually contains column names, which
is what can trip up beginners that try to use parameters for them.

> Of course, during inner query preparation, one can verify the
> expression returns a single scalar value, and at execution time, that
> it returns a single row (it can't I think, but I'm not experienced in
> SQL injection techniques, so I'd assert it myself ;)

You could easily put "UNION" or "FROM BigTable" in there.  And even
such queries can end up returning exactly one value, so I wouldn't
want to forbid them.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Clemens Ladisch]

Eduardo Morras wrote:
> Clemens Ladisch  wrote:
>> Actually, no change to SQLite itself would be needed.  It's possible
>> to create an extension that provides a function that allows to
>> register another function that executes a custom SQL expression:
>>
>>  SELECT register_simple_function('rpad', 2, 'SELECT printf
>> (''%-*s'', ?, ?)');
>
> So, if a webapp that uses SQLite doesn't check it's input, functions that 
> renames SQLite internals can be injected
>
> SELECT register_simple_function('MAX', 1, 'DROP TABLE ?');

Such a statement would not return a single column, so it wouldn't
actually get executed.

But it might be possible to execute something like "PRAGMA evil = on",
so this function probably should be secured like load_extension().


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 10:19 PM, Eduardo Morras  wrote:
> On Fri, 07 Mar 2014 15:39:57 +0100
> Clemens Ladisch  wrote:
>
>> Actually, no change to SQLite itself would be needed.  It's possible
>> to create an extension that provides a function that allows to
>> register another function that executes a custom SQL expression:
> So, if a webapp that uses SQLite doesn't check it's input, functions that 
> renames SQLite internals can be injected
>
> SELECT register_simple_function('MAX', 1, 'DROP TABLE ?');
>
> No?

Not of the select is implicit, because then "select drop table ?" is invalid.

See my previous post. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Eduardo Morras]

On Fri, 07 Mar 2014 15:39:57 +0100
Clemens Ladisch  wrote:

> Actually, no change to SQLite itself would be needed.  It's possible
> to create an extension that provides a function that allows to
> register another function that executes a custom SQL expression:
> 
>  SELECT register_simple_function('rpad', 2, 'SELECT printf
> (''%-*s'', ?, ?)');
> 
> 
> In practice, the biggest problem probably is that SQLite doesn't have
> that many built-in functions; most useful functions would require more
> than that.

So, if a webapp that uses SQLite doesn't check it's input, functions that 
renames SQLite internals can be injected

SELECT register_simple_function('MAX', 1, 'DROP TABLE ?');

No?

> 
> 
> Regards,
> Clemens

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 7:37 PM, Max Vlasov  wrote:
> On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch  wrote:
>> Max Vlasov wrote:
>>>
>>> Nice suggestion. This probably falls into case when a small new part
>>> needed on sqlite side
>>
>> Actually, no change to SQLite itself would be needed.  It's possible
>> to create an extension that provides a function that allows to register
>> another function that executes a custom SQL expression:
>>
>>  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>>
>>
>
> Brilliant :) this is a solution not requiring adding anything to
> sqlite. I'd only prefer not using full-features sql queries, but
> rather expression that is automatically wrapped in SELECT without
> FROM, otherwise they would create dimensions (columns, rows) when our
> function strictly expected one result. And also support for numbered
> parameters. So my example whould be implemented as
>
> SELECT register_function('complexcase', 2, 'Case when ?1 < 10 then ?2
> else ?2 + 10 end');
>
> It's interesting that such approach makes this super-function
> Pragma-like. It changes some internal state (reveals a new function)
> and also optionally returns some result, for example 1 for success.

OK, reading Max's answer, I realize I misunderstood your original post
Clemens. Sorry about that.

But doesn't the above assume you can bind values inside the select
clause? I thought one couldn't...

The new CTE might work-around that, if that's really the case, since
hopefully it should be possible to use bind variables in the first
statement below, while somehow I thought one couldn't in the second
statement.

sqlite> with args(x, y) as (VALUES('foo', 16)) select printf('%-*s',
y, x) from args;
foo
sqlite> select printf('%-*s', y, x) from (select 'foo' as x, 16 as y);
foo
sqlite>

basically register_function('rpad', 'x', 'y', 'printf(''%-*s'', y,
x)') would register a 2-arg function (register_function's argc-2)
named $argv[0], which executes the following statement

with args($argv[1],  $argv[2], ... $argv[argc-2]) as (VALUES(?, ?))
select $argv[argc-1) from args;

with $argv[i] textually replaced. On executing the registered rpad
function, one would forward the args to the inner statement prepared
above via sqlite3_bind_value, and execute it.

Of course, during inner query preparation, one can verify the
expression returns a single scalar value, and at execution time, that
it returns a single row (it can't I think, but I'm not experienced in
SQL injection techniques, so I'd assert it myself ;) I think I might
try to code such a super-function inside our app.

I agree with Max. Brilliant! --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Max Vlasov]

On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch  wrote:
> Max Vlasov wrote:
>>
>> Nice suggestion. This probably falls into case when a small new part
>> needed on sqlite side
>
> Actually, no change to SQLite itself would be needed.  It's possible
> to create an extension that provides a function that allows to register
> another function that executes a custom SQL expression:
>
>  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>
>

Brilliant :) this is a solution not requiring adding anything to
sqlite. I'd only prefer not using full-features sql queries, but
rather expression that is automatically wrapped in SELECT without
FROM, otherwise they would create dimensions (columns, rows) when our
function strictly expected one result. And also support for numbered
parameters. So my example whould be implemented as

SELECT register_function('complexcase', 2, 'Case when ?1 < 10 then ?2
else ?2 + 10 end');

It's interesting that such approach makes this super-function
Pragma-like. It changes some internal state (reveals a new function)
and also optionally returns some result, for example 1 for success.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 5:27 PM, Eleytherios Stamatogiannakis
 wrote:
> IMHO, SQLite is targeted towards being a relational "core" with very wide
> extensibility. Adding specific scripting engines to it is would be
> detrimental to its main purpose (being a very good relational "core").

You are misrepresentation the discussions in this thread IMHO.

We never discussed adding a scripting engine to SQLite, but making it
easy to supercharge SQLite with the ability to write extension
function via scripting (in the broad sense) instead of via compiled C
extensions. And we proposed a syntax to declare those scripted custom
function in a way that's fully compatible and consistent with the way
virtual tables are declared.

And once again, all of the above is already possible, via compiled
extension modules, as Clemens pointed out, and you also pointed out by
mentioning your own Python-implemented custom functions. But you had
to write custom C code to be able to have SQLite know about and use
your Python-implemented custom functions, and you likely embed SQLite
inside your Python environment, while I propose the reverse, and a
standard syntax to register those functions, which is extension-module
and thus scripting-engine independent.

In any case, Dr. Hipp does not participate in this thread, so it will
likely stay wishing thinking :). I still think my proposal is elegant,
SQLite-esque, useful, and importantly doable; and lightweight in code
size to boot. But of course documenting and testing this, to SQLite's
high standards, that's definitely work. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Eleytherios Stamatogiannakis]

IMHO, SQLite is targeted towards being a relational "core" with very 
wide extensibility. Adding specific scripting engines to it is would be 
detrimental to its main purpose (being a very good relational "core").


In our group, we use SQLite + UDFs written in Python. Creating new 
functions is very simple (most of the functions are a couple Python 
lines). The API of SQLite permits very advanced functionality to be tied 
to it (infinite streaming virtual tables, indexes written in Python, 
etc). And (in our benchmarks) the speed is better than Postgres and a 
LOT better than MySQL.


Finally, the simplicity and extensibility of SQLite has permitted us to 
create hundreds of functions in a very short time [*].


l.

[*] http://doc.madis.googlecode.com/hg/index.html

On 07/03/14 16:59, Dominique Devienne wrote:

On Fri, Mar 7, 2014 at 3:39 PM, Clemens Ladisch  wrote:

Max Vlasov wrote:

On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne  wrote:

I think what SQLite lacks is a syntax to define custom function like
it does for virtual tables. Something like:

create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";


Nice suggestion. This probably falls into case when a small new part
needed on sqlite side


Actually, no change to SQLite itself would be needed.  It's possible
to create an extension that provides a function that allows to register
another function that executes a custom SQL expression:

  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');


I've already mentioned that Clemens. But also pointed about that then
you don't have a standard way to register those dynamically generated
functions. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 3:39 PM, Clemens Ladisch  wrote:
> Max Vlasov wrote:
>> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne  
>> wrote:
>>> I think what SQLite lacks is a syntax to define custom function like
>>> it does for virtual tables. Something like:
>>>
>>> create function rpad(x, y) using scripty_module as "return 
>>> PRINTF('%-*s',y,x)";
>>
>> Nice suggestion. This probably falls into case when a small new part
>> needed on sqlite side
>
> Actually, no change to SQLite itself would be needed.  It's possible
> to create an extension that provides a function that allows to register
> another function that executes a custom SQL expression:
>
>  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');

I've already mentioned that Clemens. But also pointed about that then
you don't have a standard way to register those dynamically generated
functions. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Clemens Ladisch]

Max Vlasov wrote:
> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne  
> wrote:
>> I think what SQLite lacks is a syntax to define custom function like
>> it does for virtual tables. Something like:
>>
>> create function rpad(x, y) using scripty_module as "return 
>> PRINTF('%-*s',y,x)";
>
> Nice suggestion. This probably falls into case when a small new part
> needed on sqlite side

Actually, no change to SQLite itself would be needed.  It's possible
to create an extension that provides a function that allows to register
another function that executes a custom SQL expression:

 SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');


In practice, the biggest problem probably is that SQLite doesn't have
that many built-in functions; most useful functions would require more
than that.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 11:29 AM, Max Vlasov  wrote:
> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne  
> wrote:
>> I think what SQLite lacks is a syntax to define custom function like
>> it does for virtual tables. Something like:
>>
>> create function rpad(x, y) using scripty_module as "return 
>> PRINTF('%-*s',y,x)";
>>
>
> Nice suggestion. This probably falls into case when a small new part
> needed on sqlite side and large DSL specific features depending on the
> side of the registered module. But you still needs some binary module
> for your functions to work.

[DD] Right. But I'd expect such modules to appear for
tcl/lua/python/ruby quickly. There's not much glue code needed on the
module side, basically unpacking the sqlite3_value into an equivalent
scripty value, and eval the script, converting back the resulting
scripty value into a sqlite3_value via result context, as usual for C
extension function.

> How about "macro expression substitution" feature? Something that
> could accept any expression compatible with sqlite as the function
> body to be just translated into corresponding vdbe instructions.

I did think of a "native" SQLite "scripting", but didn't think of
using SQL expressions SQLite already support. Very interesting.

> For example,
>
> Create function complexcase(x,y) as Case when x < 10 then y else y + 10 end
> 
> Select complexcase(myField1, myField*10) from t;
>
> On sqlite site, the engine can only syntax check for the create
> function body, but the referencing it in actual query would require
> actual substitution with the corresponding context-sensitive errors.

That's the difficult part probably. And it's still compatible with
using create function ... using module syntax. w/o "using module" you
are using the implicit and native SQL-based syntax as the "scripty"
part.

> This definitely will be more constly in kB on sqlite side than module
> approach, but would create a more universal solution.
>
> Just quick thoughts, probably there are plenty of obstacles here :)

Which makes it more likely not to happen unfortunately. Whereas a
simple create function func(args...) using module as
module_specific_source_code would require much less code in SQLite
core, and just a sqlite3_module_v2 that extends sqlite3_module with
two new callbacks for scalar and aggregate functions registrations,
with bump of sqlite3_module.iVersion for BC. Ideally SQLite would gain
a pragma to list functions, something I've long wished for. More
wishful thinking basically :) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Max Vlasov]

On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne  wrote:
> I think what SQLite lacks is a syntax to define custom function like
> it does for virtual tables. Something like:
>
> create function rpad(x, y) using scripty_module as "return 
> PRINTF('%-*s',y,x)";
>

Nice suggestion. This probably falls into case when a small new part
needed on sqlite side and large DSL specific features depending on the
side of the registered module. But you still needs some binary module
for your functions to work

How about "macro expression substitution" feature? Something that
could accept any expression compatible with sqlite as the function
body to be just translated into corresponding vdbe instructions.

For example,

Create function complexcase(x,y) as Case when x < 10 then y else y + 10 end

Select complexcase(myField1, myField*10) from t;

On sqlite site, the engine can only syntax check for the create
function body, but the referencing it in actual query would require
actual substitution with the corresponding context-sensitive errors.

This definitely will be more constly in kB on sqlite side than module
approach, but would create a more universal solution.

Just quick thoughts, probably there are plenty of obstacles here :)

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 10:07 AM, Eduardo Morras  wrote:
> Creating extensions in SQLite is not difficult nor hard. You can define yours 
> with this line:

It's no hard, no, if you're already a C developer. But it's hardly
convenient when you need something quickly.

Between creating a new C file, coding it, compiling it, packaging it
as a DLL/SO, .load it, test it, repeat on errors/crashes, and using
straight sqlite3 CLI with a downloaded scripty_module extension and
using direct trial-and-error at a REPL using already known "scripty"
language, that's night and day IMHO. Plus the scripty solution will
typically be cross-platform, easily packaged in a .sql one can .read
(which also does the necessary .load, acting as scripted function
"libraries"), as long as the generic scrypty_module is available for
that platform. Going down the C route just easiest a viable option to
many, and even for those who can do it in C, the scripty route is more
productive to prototype, since immediate and interactive at the SQLite
REPL. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Eduardo Morras]

On Thu, 6 Mar 2014 22:41:31 -0500
Gabor Grothendieck  wrote:

> On Thu, Mar 6, 2014 at 8:41 PM, RSmith  wrote:
> >
> > On 2014/03/07 01:59, Gabor Grothendieck wrote:
> >>
> >>
> >>>
> 
> > A small enhancement request:
> >
> > It would be great if the RPAD and LPAD functions could be
> > implemented in
> > sqlite.
> >
>  The SQLite you can get the effect of RPAD(x,y) using PRINTF
>  ('%-*s',y,x). See
>  http://www.sqlite.org/lang_corefunc.html#printf for details.
> >>>
> >>> Thanks, but you snipped the relevant part of my post:
> >>> "I know I can easily achieve the equivalent ... but if the
> >>> functions were available natively it would avoid the need to hack
> >>> third party SQL scripts."
> >>>
> >> I have also found that it was tedious to retarget MySQL scripts to
> >> SQLite because many of the function calls are different.  Its not
> >> just rpad and lpad but other functions too.
> >
> >
> > Speaking as someone who retargets (nice word btw.) SQL scripts
> > often, yes I agree, it's a bit of a chore to retarget SQL scripts
> > to SQLite sometimes, but not really moreso than retargeting a
> > script from MSSQL to PostGres or
> 
> I have also retargeted MySQL scripts to H2 and it was easier than to
> SQLite. 

Creating extensions in SQLite is not difficult nor hard. You can define yours 
with this line:

 sqlite3_create_function(db, "RPAD", SQLITE_UTF8, 
SQLITE_ANY||SQLITE_DETERMINISTIC, 0, sqlcmd_rpad, 0, 0);

And create your function with:

 static void sqlcmd_rpad(sqlite3_context *context, int argc,sqlite3_value 
**argv){

   // argc has number of parameters in **argv
   // parse them as you do within C main 
   // 
   sqlite3_result_text(context, char_to_return, length_char_to_return, 
SQLITE_TRANSIENT);
 }

Some weeks ago, there was a mail collecting several sites where find usefule 
functions && extensions.

HTH

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Dominique Devienne]

On Fri, Mar 7, 2014 at 7:38 AM, Zsbán Ambrus  wrote:
> On 3/7/14, RSmith  wrote:
>> Add to this the fact that you can - through SQL's ability to add
>> user-defined functions (an almost unique ability among SQL engines)
>
> Is that really so?  I thought most sql engines had that.  You can
> define functions in at least postgreSQL:
> http://www.postgresql.org/docs/9.3/interactive/xfunc.html

You can only define custom SQL function in C in SQLite.

The SQLite core lacks a built-in "scripty" engine which would allow it
to define new SQL function in "SQL/scripty", unlike most
bigger/heavier RDBMSs.

I think what SQLite lacks is a syntax to define custom function like
it does for virtual tables. Something like:

create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";

This above statement would have SQLite call some API on scripty_module
in charge of registering a function_pointer+userdata+userdatadelfunc
using the usual SQLite api to register custom functions, and
scripty_module will have to "compile" or "interpret" the function's
"code" (e.g. "return PRINTF('%-*s',y,x)") and run it with the supplied
sqlite3_value[] it receives (including unpacking the args to the
correct type the scripty function expects).

extension modules can also add custom functions, but a fixed set of
hard-coded functions. It could of course export a function that does
the equivalent of the "create function" SQL I dreamed up/showed above,
but then there's no standard/unified way to write custom scripty
functions, because of course we'd have a scripty_module to write
functions in js/lua/python/ruby/tcl/etc...

The SQLite core team extensively uses TCL already, so they could do a
proof-of-concept with TCL.

Most scripting languages wrap SQLite inside, but here I'm talking
about the reverse, it would be SQLite wrapping the scripting language
to SQL extensions, usable from the command-line sqlite3 familiar tool.
Again, it's already possibly now via extension module, but an official
syntax for scripted extension SQL functions, with readily available
such modules would improve the SQLite ecosystem IMHO.

--DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Zsbán Ambrus]

On 3/7/14, RSmith  wrote:
> Add to this the fact that you can - through SQL's ability to add
> user-defined functions (an almost unique ability among SQL engines)

Is that really so?  I thought most sql engines had that.  You can
define functions in at least postgreSQL:

http://www.postgresql.org/docs/9.3/interactive/xfunc.html

Ambrus
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Gabor Grothendieck]

On Thu, Mar 6, 2014 at 8:41 PM, RSmith  wrote:
>
> On 2014/03/07 01:59, Gabor Grothendieck wrote:
>>
>>
>>>

> A small enhancement request:
>
> It would be great if the RPAD and LPAD functions could be implemented
> in
> sqlite.
>
 The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
 See http://www.sqlite.org/lang_corefunc.html#printf for details.
>>>
>>> Thanks, but you snipped the relevant part of my post:
>>> "I know I can easily achieve the equivalent ... but if the functions were
>>> available natively it would avoid the need to hack third party SQL scripts."
>>>
>> I have also found that it was tedious to retarget MySQL scripts to
>> SQLite because many of the function calls are different.  Its not just
>> rpad and lpad but other functions too.
>
>
> Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I
> agree, it's a bit of a chore to retarget SQL scripts to SQLite sometimes,
> but not really moreso than retargeting a script from MSSQL to PostGres or

I have also retargeted MySQL scripts to H2 and it was easier than to SQLite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[RSmith]

On 2014/03/07 01:59, Gabor Grothendieck wrote:







A small enhancement request:

It would be great if the RPAD and LPAD functions could be implemented in
sqlite.


The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.

Thanks, but you snipped the relevant part of my post:
"I know I can easily achieve the equivalent ... but if the functions were available 
natively it would avoid the need to hack third party SQL scripts."


I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different.  Its not just
rpad and lpad but other functions too.


Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I agree, it's a bit of a chore to retarget SQL scripts to 
SQLite sometimes, but not really moreso than retargeting a script from MSSQL to PostGres or Oracle, or PostGres to MySQL (which btw. 
doesn't support WITH RECURSIVE at all) etc.[1]   To single out the SQLite differences as anything more than standard incompatibility 
between SQL engines would be... exaggerative. (I just made up that word!) - And still with the bit of incompatibility we need to 
deal with for a world of clever, trusted and fast querying in an engine that is a fraction the size of anything else, so much so 
that you can run it on a smart calculator  hardly a deficit.


Add to this the fact that you can - through SQL's ability to add user-defined functions (an almost unique ability among SQL engines) 
- add your own RPAD and LPAD functions that work exactly how you envision... you have the power to solve your own problem.


[1]: Yes I know MySQL has other ways to deal with recursion, but the point is that revising the scripts will need a LOT of 
adjustment to achieve the same, which is the point of this note.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Gabor Grothendieck]

On Thu, Mar 6, 2014 at 6:29 PM, Walter Hurry  wrote:
> Richard Hipp wrote:
>
>> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry  wrote:
>>
>>> A small enhancement request:
>>>
>>> It would be great if the RPAD and LPAD functions could be implemented in
>>> sqlite.
>>>
>>
>> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
>> See http://www.sqlite.org/lang_corefunc.html#printf for details.
>
> Thanks, but you snipped the relevant part of my post:
>
> "I know I can easily achieve the equivalent ... but if the functions were 
> available natively it would avoid the need to hack third party SQL scripts."
>

I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different.  Its not just
rpad and lpad but other functions too.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Walter Hurry]

Richard Hipp wrote:

> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry  wrote:
>
>> A small enhancement request:
>>
>> It would be great if the RPAD and LPAD functions could be implemented in
>> sqlite.
>>
>
> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
> See http://www.sqlite.org/lang_corefunc.html#printf for details.

Thanks, but you snipped the relevant part of my post:

"I know I can easily achieve the equivalent ... but if the functions were 
available natively it would avoid the need to hack third party SQL scripts."

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Richard Hipp]

On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry  wrote:

> A small enhancement request:
>
> It would be great if the RPAD and LPAD functions could be implemented in
> sqlite.
>

The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] RPAD/LPAD

[Walter Hurry]

A small enhancement request:

It would be great if the RPAD and LPAD functions could be implemented in sqlite.

I know I can easily achieve the equivalent by concatenating and TRUNCing, but 
if the functions were available natively it would avoid the need to hack third 
party SQL scripts.

Dr. Hipp?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users