Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Daniel Kamil Kozar]

Patches are still welcome, I guess. I haven't seen anybody claiming
that this would be done in any way.

On 6 June 2017 at 15:17, PICCORO McKAY Lenz  wrote:
> how its the status of this work?
>
> a limited implementation will be good!
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
>
> 2017-03-15 12:24 GMT-04:00 Simon Slavin :
>
>>
>> On 15 Mar 2017, at 4:09pm, Dominique Devienne  wrote:
>>
>> > On Wed, Mar 15, 2017 at 4:57 PM, R Smith  wrote:
>> >
>> >> I wonder, sqlite Devs, if a pragma or other adaption (such as the
>> current
>> >> pragma table_info()) or such could produce the same exact data but with
>> an
>> >> added field called "Comment" that simply gives the parsed comment from
>> >> after each column definition (if any) like the above table example. This
>> >> would probably be a very small adaptation, be completely backwards
>> >> compatible, doesn't break any standard (since there isn't any) and
>> answer
>> >> the need expressed by this thread and others before it.
>> >
>> > That's one way to solve it, in a mostly BC (Backward Compatible) way.
>> > (modulo the output from table_info() changing, which could be opt-in to
>> > make it fully BC).
>>
>> Problem is, it requires parsing the CREATE command looking for comments in
>> a certain format.  Notoriously difficult, considering that they can contain
>> CR, LF, tab, and unforeseen Unicode characters.
>>
>> I’m utterly against anything that tries to read C-style comments.
>> Comments are comments.  Computers are meant to ignore them to the point
>> that they don’t even know they exist.
>>
>> On the other hand, if we establish a standard for storing comments in
>> database tables — which would require a consistent table name, column
>> names, and values — it might take too much extra time to show those
>> comments as an extra column in the response to PRAGMA tale_info() and
>> similar PRAGMAs.  But I think it’s overkill.  Anyone who would want that
>> would know how to retrieve the information.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[Daniel Kamil Kozar]

Seeing how SQLite was created in 2000, it seems like nobody really
needed this feature for the last 17 years enough in order to actually
implement it.

Last I heard, patches are welcome on this mailing list. Don't keep us waiting.

Kind regards,
Daniel

On 20 March 2017 at 21:09, PICCORO McKAY Lenz  wrote:
> i got this
>
> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> Unable to execute statement
>
> still today in 21 ts century?
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] tclsqlite3 db function exports not visible on other connections. Why not?

[Daniel Kamil Kozar]

You can use sqlite3_auto_extension for this.

On 19 March 2017 at 11:35, R Smith  wrote:
>
> On 2017/03/19 11:05 AM, petern wrote:
>>
>> Taking DRH's remarks about learning tclsqlite for the efficient coding to
>> heart, I discovered a big problem.
>>
>> Here is the simplest example from the docs and DRH presentation:
>>
>> TCLSH
>> % db function myhex {format 0x%X};
>> % db eval {select myhex(10);} x {parray x};
>> x(*) = myhex(10)
>> x(myhex(10)) = 0xA
>>
>> Now, on the same database with simultaneous connection eg: shell, odbc,
>> etc:
>>
>> sqlite> select myhex(10);
>> Run Time: real 0.000 user 0.00 sys 0.00
>> Error: no such function: myhex
>>
>>
>> -
>> Did I missing something in the docs?
>> Is there a trick/pragma to make this work?
>>
>> Tclsqlite is extremely efficiently for extending sqlite but this facility
>> is generally useless if the whole application must be ported to (or at
>> least all db queries funneled through) TCLSH to use those easy to build
>> extensions.  What if the outer application can't be ported to TCL?
>
>
> Adding a custom function (Whether done in your code using the API or using
> TCL or whatever) makes that function belong to the connection, a function
> cannot belong to the database or persist outside of the creating connection.
> tclsqlite is a great way to code for examples or reproducible bugs or even
> full DB maintenance scripts - but you can't use it together with your normal
> program code from a different connection. I'm not sure if there exist any
> way (or hack) to achieve this, maybe someone else knows, but what you tried
> won't work.
>
> This is not a tclsqlite problem, if you open a connection from one of your
> programs, add to it a custom function, and then open another connection to
> the same DB from one of your other programs, the custom function will of
> course NOT exist for the second connection - it's strictly a per-connection
> thing, and the second connection must register its own custom function.
>
> There exist some add-ons for sqlite which introduce ways of creating custom
> functions INSIDE the SQL via a query. Not sure if this will solve your
> problem, if so ask again (or google) for links to them.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

[Daniel Kamil Kozar]

Thanks a ton for the replies. This pretty much confirms what I've been
suspecting.

On 3 February 2017 at 11:24, Clemens Ladisch  wrote:
> The FTS and R-tree virtual tables do not contain NULL values, so
> I guess it was never seen as necessary.

Does this make it an accidental omission, then? Would you guys be open
to implementing this type of constraint in the virtual table
interface?

On 3 February 2017 at 11:26, Hick Gunter  wrote:
> Notice that there is no SQLITE_INDEX_CONSTRAINT_NULL. You can of course 
> implement that "= NULL" works like "IS NULL" on your virtual table, but that 
> does result in some strange looking SQL, and you have to be sure to tell 
> SQLite to omit it's own constraint check. Alternatively, consider computed 
> virtual fields that will return 0 or 1 depending on the base field.
>
> In your table declaration, add _IS_NULL INTEGER for all fields that 
> need to support this.
>
> SELECT ... FROM your_table WHERE ..._IS_NULL = 1 ...
>
> This should make SQLite pass the constraint to xBestIndex and the value (0 or 
> 1, depending if you need IS NULL or NOT NULL) to xFilter.

I guess that would work. Thanks for the suggestion.

Kind regards,
Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

[Daniel Kamil Kozar]

Hi.
I'd like to ask why using a IS NULL or IS NOT NULL constraint in a
query made to a virtual table does not result in these constraints
being present in the sqlite3_index_info structure passed to the
virtual table's xBestIndex.
Currently, using one of these constraints results in no constraints at
all being passed, thus forcing the virtual table to use the most
"generic" index for accessing it. This seems like an omission to me,
since virtual tables could very well provide their own handling for
this special kind of constraint, without having to revert to a full
scan of the table just so that SQLite can look for rows that satisfy
this constraint.
Interestingly, specifying a = NULL constraint in a query results in
the constraint being passed to xBestIndex. This is curious, since - at
least to my understanding - no two NULLs are supposed to compare
equal, and SQLite behaves just like that when using its normal table
implementations : running a query with = NULL gives an empty result,
while IS NULL gives the expected result.
I modified src/test_intarray.c in order for it to be self-contained
and make some pre-defined queries which show what I'm talking about.
The source is available here :
https://gist.githubusercontent.com/xavery/976981b84d3bba90ef6bb480adc7a16d/raw/61ed629ef3dc4af0735e50ad40d33c16c7d11a13/sqlite-vtab-isnull.c
I could only find one thread which also deals with this issue :
http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg62850.html
- however, it does not offer an explanation why the implementation
behaves this way.

Kind regards,
Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users