Re: [sqlite] The next release of SQLite....

[Jean-Christophe Deschamps]

Gidday Tim,

>I will look further into this approach:
>
>  select sqlite3_load_extension('mylibrary', 'entrypoint');
>
>to see if Adobe's security permits it. However,  the Adobe FlashBuilder
>database application developer must confront this uncertainty: Adobe has
>been unresponsive to questions about their implementation of SQLite, and
>so one cannot be confident that the approach above would still be
>available tomorrow even if it happened to succeed today.

I guess Adobe didn't badly play with SQLite code structure and I hardly 
see them going thru user's selects and block execution depending on 
what's inside.

The only catch would be that they removed the extension loading feature 
but they must have found valid reasons for that.  Given that Adobe's 
product are probably designed for international targets, they must have 
ICU ou other Unicode support.  I don't know the code by heart but it's 
likely that this precludes removing extension loading support.

Regarding long-term stability, I doubt they would introduce such code 
breaking removal without very strong reasons.



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

Re: [sqlite] The next release of SQLite....

[Tim Romano]

Adobe has security "sandbox" requirements, so I would not want to 
disparage them for their decisions, though I do wish they would be more 
forthcoming with information.
Regards
Tim Romano

Jean-Christophe Deschamps wrote:
>>> Does Adobe actually filter out statements similar to:
>>>
>>>select sqlite3_load_extension('mylibrary', 'entrypoint');
>>>   
>>   It is much more likely they simply do not call the C function
>>   sqlite3_enable_load_extension( ) either on purpose, or just as an
>>   oversight.
>> 
>
> I don't know this product but those are two distinct things in my view.
>
> A layer (whatever it is) may offer a specific interface to load an 
> extension (using SQLite API) - a know example is SQLite CLI with its 
> .load command.
>
> If the user issues a SELECT statement similar to above, then does Adobe 
> code really dissect it, rewrite it internally and invoke its own 
> lobotomized interpretation of what was inside the statement?  If true 
> then their tool is best avoided!
>
> Another possibility is that they have included only part of SQLite in 
> their compiled product and left out selected functions.  Looks like dumb.
>
> Perhaps could Tim try things and report what actually happens to be the 
> case.
>
>
>   

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

Re: [sqlite] The next release of SQLite....

[Tim Romano]

Thanks very much for pointing this out,  Jean-Christophe. Yes, glob is 
exactly what I wanted.

I will look further into this approach:

 select sqlite3_load_extension('mylibrary', 'entrypoint');

to see if Adobe's security permits it. However,  the Adobe FlashBuilder 
database application developer must confront this uncertainty: Adobe has 
been unresponsive to questions about their implementation of SQLite, and 
so one cannot be confident that the approach above would still be 
available tomorrow even if it happened to succeed today. 

Tim

Jean-Christophe Deschamps wrote:
>> B. Create another LIKE that is *always* case-sensitive. RAWLIKE or SLIKE
>> or whatever. It does a simple codepoint-by-codepoint test and doesn't
>> have any special intelligence for ASCII/LATIN.
>> 
> That's GLOB, already in the box and free.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The next release of SQLite....

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Artur Reilin wrote:
> Is somewhere a list of which sites of the documentation has changed? It
> would be a little difficult to search for differences.

Documentation is kept in a separate fossil repository than the SQLite
source.  You can see the timeline at:

  http://www.sqlite.org/docsrc/timeline

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksYxKEACgkQmOOfHg372QQSEwCdEVQmi4UxzQFXAuKs5UQWzlUi
9G4AoLX4ilhoGFzqqP0Hd1GDuwlRDDNR
=BNnk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The next release of SQLite....

[Dan Kennedy]

On Dec 4, 2009, at 7:28 AM, John Brooks wrote:

> In the new fts3.html, the statement:
>
> "each FTS3 table has a 'rowid' column that behaves like an INTEGER
> PRIMARY KEY, except that values remain unchanged if the database is
> rebuilt using the VACUUM command."
>
> is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged
> during VACUUM, in contrast to a usual ROWID. It is unclear here if the
> FTS3 rowid does or does not change during vacuum.

Changed to:

   ", each FTS3 table has a "rowid" column. The rowid of an FTS3 table
behaves in the same way as the rowid column of an ordinary SQLite
table, except that the values stored in the rowid column of an FTS3
table remain unchanged if the database is rebuilt using the VACUUM
command."

Dan.

>
>  - John Brooks
>
> On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams
>  wrote:
>> Use the glob operator.
>> ___
>> 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

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

Re: [sqlite] The next release of SQLite....

[Artur Reilin]

Is somewhere a list of which sites of the documentation has changed? It
would be a little difficult to search for differences.

with best wishes

- Artur -



> BTW, this is why I added docid as an alias for rowid for fts3
> (rowid-versus-VACUUM was the initial reason for revving fts2 to fts3).
>  Saying "rowid is just like rowid in other tables, except different"
> seemed more confusing than it was worth.
>
> Though since I'm not writing the new documentation, I'm more than
> happy to be ignored :-).
>
> -scott
>
>
> On Thu, Dec 3, 2009 at 4:28 PM, John Brooks 
> wrote:
>> In the new fts3.html, the statement:
>>
>> "each FTS3 table has a 'rowid' column that behaves like an INTEGER
>> PRIMARY KEY, except that values remain unchanged if the database is
>> rebuilt using the VACUUM command."
>>
>> is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged
>> during VACUUM, in contrast to a usual ROWID. It is unclear here if the
>> FTS3 rowid does or does not change during vacuum.
>>
>>  - John Brooks
>>
>> On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams
>>  wrote:
>>> Use the glob operator.
>>> ___
>>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


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

Re: [sqlite] The next release of SQLite....

[John Brooks]

In the new fts3.html, the statement:

"each FTS3 table has a 'rowid' column that behaves like an INTEGER
PRIMARY KEY, except that values remain unchanged if the database is
rebuilt using the VACUUM command."

is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged
during VACUUM, in contrast to a usual ROWID. It is unclear here if the
FTS3 rowid does or does not change during vacuum.

  - John Brooks

On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams
 wrote:
> Use the glob operator.
> ___
> 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] The next release of SQLite....

[Nicolas Williams]

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

Re: [sqlite] The next release of SQLite....

[Tim Romano]

Jay,
That's a reasonable perspective, and indeed I have asked Adobe for the 
feature. But another not unreasonable position is this: to prevent 
fragmentation, enhancements that have general applicability are made to 
the core rather than to individual implementations.  The more 
implementations diverge from the core, the less portable the database 
becomes.

The raw functions I requested would be useful features for the database 
to have. Once you have a raw LIKE operator, one that tests codepoint by 
codepoint, it doesn't matter so much what direction the SQLite 
architects might take in the future in terms of how they decide to 
handle normalisation of Unicode composed characters (.e.g  is a + 
combining diaresis LIKE a-umlaut?).   One can always rely upon the raw 
string function; not so with functions that incorporate higher-order 
Unicode awareness.

Tim Romano

Jay A. Kreibich wrote:
>   If you want changes to Adobe's implementation, you should probably be
>   talking to Adobe.
>
>-j
>   

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

Re: [sqlite] The next release of SQLite....

[Jay A. Kreibich]

On Thu, Dec 03, 2009 at 11:16:43PM +0100, Jean-Christophe Deschamps scratched 
on the wall:
>
>> > Does Adobe actually filter out statements similar to:
>> >
>> >select sqlite3_load_extension('mylibrary', 'entrypoint');
>>
>>
>>   It is much more likely they simply do not call the C function
>>   sqlite3_enable_load_extension( ) either on purpose, or just as an
>>   oversight.
>
> I don't know this product but those are two distinct things in my view.

  They are distinct, but _loadable_ extensions are off by default.
  All types of loadable extensions must be explicitly enabled via that
  API call for security reasons.  The sqlite3 shell does this.
  
  If extensions are not explicitly enabled, neither the C API call
  sqlite3_load_extension(), nor the SQL call load_extension() will work.

> Another possibility is that they have included only part of SQLite in  
> their compiled product

  Or, simply forgotten to enable selected function.  Maybe on accident,
  maybe on purpose.  If they're intercepting and canceling PRAGMA
  commands, they likely did it on purpose.

  Regardless, it is an application issue, not an SQLite one.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The next release of SQLite....

[Jay A. Kreibich]

On Thu, Dec 03, 2009 at 10:07:07PM +0100, Jean-Christophe Deschamps scratched 
on the wall:

> Does Adobe actually filter out statements similar to:
> 
>select sqlite3_load_extension('mylibrary', 'entrypoint');


  It is much more likely they simply do not call the C function
  sqlite3_enable_load_extension( ) either on purpose, or just as an
  oversight.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The next release of SQLite....

[Shawn Wilsher]

On Thu, Dec 3, 2009 at 10:26 AM, Tim Romano  wrote:

> The alternative, NOCASE collation, also does not get me out of the
> woods. For some reason in Adobe (and in Mozilla) the index is not used
> on LIKE clauses when the column in question has NOCASE collation, though
> SQLite3.EXE does use the index in its query plan on the same query.
> These consortium members might be overriding the LIKE function or
> compiling statements not with _V2 or doing something else that prevents
> the optimization. I don't know.
>
Mozilla does override the LIKE function because we need to be able to handle
Unicode, which the default implementation does not do.  The implementation
is here:
http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageSQLFunctions.cpp#408

And we'd happily accept patches to fix this issue.

Cheers,

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

Re: [sqlite] The next release of SQLite....

[Jean-Christophe Deschamps]

Hi Tim,

>   ... where myTextColumnUsingDefaultBinaryCollation like 'foo%'

Did you try

   ... where myTextColumnUsingDefaultBinaryCollation glob 'foo*'

GLOB is hardcoded as case-sensitive and more likely a candidate to 
using index.  Just check it.


>2.  In Adobe, one is not able to load a user-defined function as an
>extension.  I need a *raw* codepoint-by-codepoint reversal of a text
>string, which makes no attempt to distinguish between Unicode combining
>characters and base-characters, similar to what can be done in Oracle
>and SQLServer.

Does Adobe actually filter out statements similar to:

   select sqlite3_load_extension('mylibrary', 'entrypoint');

If they do, then they really have something against _you_ ;-)

If they don't (which is what I would wild guess) then I've offered you 
something that should perform per your requirements.  BTW I've a 
revise, expanded version available with (portable) a NUMERIC Unicode 
sort (lexicagraphic sort of variable-size text numbers sucks).

>B. Create another LIKE that is *always* case-sensitive. RAWLIKE or SLIKE
>or whatever. It does a simple codepoint-by-codepoint test and doesn't
>have any special intelligence for ASCII/LATIN.

That's GLOB, already in the box and free.

>RAW STRING REVERSAL
>This is a simple function and the other major players have it.  It seems
>to me that it should be easy to implement. Concerns about combining
>characters and base characters and higher order Unicode intelligence
>could be saved for a UREVERSE() function, one which preserves Unicode
>composed characters.

If you need I can make a limited verion of my extension for you with 
only the functions you need.





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

Re: [sqlite] The next release of SQLite....

[Jean-Christophe Deschamps]

>Last minute comments on the pending release of SQLite 3.6.21 are
>welcomed.

Thank you for your continued efforts.

Can you consider making sqlite3_auto_extension and 
sqlite3_reset_auto_extension available into the API structure so that 
they can both be invoked from within an extension without bringing 
dependancy on sqlite3.dll/so

For reasons that would require lengthy explanations, loading extensions 
is a vital feature but it is unduly limited as it stands today.  Making 
the above inclusion would help a _lot_ in some situations, really.

There are related severe issues with loading extensions thru SQL, but 
this would need more than a last minute easy change.

I can elaborate on both subjects, as I've been banging my bones on 
these questions ad nauseam.

TIA.



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

Re: [sqlite] The next release of SQLite....

[Jay A. Kreibich]

On Thu, Dec 03, 2009 at 01:26:40PM -0500, Tim Romano scratched on the wall:

> I have some ideas on how these two limiting features of Adobe's 
> implementation of SQLite might be addressed. I don't know how feasible 
> they might be given the existing codebase because I am not a C coder. 

  If you want changes to Adobe's implementation, you should probably be
  talking to Adobe.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The next release of SQLite....

[Tim Romano]

Since the consortium members are prominently listed on those pages, I 
hope it is not inappropriate if my comments refer to a couple of them. 
Nothing disparaging.

As I am in the process of discovering (and so subject to 
revision/correction), there are a number of capabilities in SQLite that 
are disabled or not enabled in consortium member Adobe's 
implementation.  LoadableExtensions don't seem to be available, and 
PRAGMA is not.  Adobe has good reasons for this, I'm sure, but these 
limitations leave my application rather high and dry on two counts:

1. Without PRAGMA, I cannot set the case-sensitivity of the LIKE 
operator and so cannot get use of index optimization on queries like this:

  ... where myTextColumnUsingDefaultBinaryCollation like 'foo%'

The alternative, NOCASE collation, also does not get me out of the 
woods. For some reason in Adobe (and in Mozilla) the index is not used 
on LIKE clauses when the column in question has NOCASE collation, though 
SQLite3.EXE does use the index in its query plan on the same query.  
These consortium members might be overriding the LIKE function or 
compiling statements not with _V2 or doing something else that prevents 
the optimization. I don't know.

This has a major impact on performance. Queries that take < 100ms in 
SQLite3.EXE take 40-50 seconds in Adobe (and Mozilla) because of the 
full-table-scan.

2.  In Adobe, one is not able to load a user-defined function as an 
extension.  I need a *raw* codepoint-by-codepoint reversal of a text 
string, which makes no attempt to distinguish between Unicode combining 
characters and base-characters, similar to what can be done in Oracle 
and SQLServer. 

I have some ideas on how these two limiting features of Adobe's 
implementation of SQLite might be addressed. I don't know how feasible 
they might be given the existing codebase because I am not a C coder. 

INDEX-OPTIMIZATION WITH LIKE
A.  Make the existing LIKE operator able to recognize the column 
COLLATION and adapt its case-sensitivity to that COLLATION.
B. Create another LIKE that is *always* case-sensitive. RAWLIKE or SLIKE 
or whatever. It does a simple codepoint-by-codepoint test and doesn't 
have any special intelligence for ASCII/LATIN.

RAW STRING REVERSAL
This is a simple function and the other major players have it.  It seems 
to me that it should be easy to implement. Concerns about combining 
characters and base characters and higher order Unicode intelligence 
could be saved for a UREVERSE() function, one which preserves Unicode 
composed characters. 

Thanks for considering.
Regards
Tim Romano




D. Richard Hipp wrote:
> Last minute comments on the pending release of SQLite 3.6.21 are  
> welcomed.  Visit
>
>  http://www.sqlite.org/draft/index.html
>  http://www.sqlite.org/draft/releaselog/3_6_21.html
>   

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

Re: [sqlite] The next release of SQLite....

[P Kishor]

On Thu, Dec 3, 2009 at 12:01 PM, D. Richard Hipp  wrote:
> Last minute comments on the pending release of SQLite 3.6.21 are
> welcomed.  Visit
>
>     http://www.sqlite.org/draft/index.html
>     http://www.sqlite.org/draft/releaselog/3_6_21.html
>


with regards to the newly reworked FTS3 and its new docs (see
http://www.sqlite.org/draft/fts3.html)... bravo! What can I say, with
tears of joy in my eyes. My one compelling reason (besides other
compelling reasons) to continue using SQLite got more compelling.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The next release of SQLite....

[D. Richard Hipp]

Last minute comments on the pending release of SQLite 3.6.21 are  
welcomed.  Visit

 http://www.sqlite.org/draft/index.html
 http://www.sqlite.org/draft/releaselog/3_6_21.html

D. Richard Hipp
d...@hwaci.com



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