Re: [sqlite] PRAGMA database_list: insert into table?

[Tim Romano]

A reversed-string index, which would be used transparently on ends-with
queries, would not have to store the data redundantly in the base table.
 That is what I think you are suggesting?Though I would welcome the "smart"
approach you suggest, it is far  more ambitious than a raw-reverse function.
I was trying to keep my request in the shallow end of the swimming pool.

The main drawback of the raw-reverse version of the index-optimized
ends-with query is the need to store the reversed form in the base
table. Disk is very cheap nowadays, so that's not where the real problem
lies; the real problem is that the unreversed and the reversed form of the
text data might fall out of synch with each other.  A smart unicode-savvy
"reverse index" for use with ends-with queries could avoid that particular
pitfall. But it would require a change to the query-parsing logic.

SQLite's unoptimized ends-with queries are fast on desktop PCs if the table
fits in available RAM :  5.5 seconds on the first query when the table is
still disk-resident and 120ms  after the table is ram-resident, this against
 a table with 265K rows.  The common scenario where an index would
significantly improve ends-with performance is a (typically handheld)
computing device with gigabytes of disk but a relatively underpowered CPU
and limited RAM .  Shared-resource web hosting environments are another. The
shared-hosting environment might be used to justify a request for the smart
unicode-savvy approach but not a request for the raw-reverse function,
because one would have ready access to the naive solution via the UDF
mechanism.

Regards
Tim Romano
Swarthmore PA

P.S. A raw-reverse function should probably be called FLIP() so as not to
get bollixed up with any reverse() function in UDF libraries.  FLIP connotes
something inelegant as well :-)
On Fri, Jul 30, 2010 at 1:30 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/30/2010 08:00 AM, Tim Romano wrote:
> > Several things might be responsible for there being no "outpouring of
> > support" for a raw reverse function.
>
> Also note that you are proposing it as a very specific workaround for a
> specific scenario which is why you want the codepoints reversed rather than
> being done "correctly" when dealing with combining unicode codepoints.  ie
> your required reverse semantics are not correct for 100% of people who
> would
> want a reverse function :)
>
> - From your further description it is apparent that you don't actually want
> a
> reverse function, but rather a string endswith function that is not O(n).
>
> Another example usage is processing web logs, where for example you want to
> find all accesses to names ending in 'google.com'.
>
> > That the SQL dev team has not responded I take as an indication that this
> > would be very low priority for them,
>
> I don't think the issue was articulated well enough (I think my second
> paragraph above is better :-) and the solution you required is not a SQL
> standard nor fits in with how SQLite development works in general, and you
> rejected the existing means by which SQLite allows people to customise it.
>
> You can of course demand their attention through the mechanisms listed at:
>
>  http://www.hwaci.com/sw/sqlite/prosupport.html
>
> > Anyway, the lack of such a function is only a problem for me vis-a-vis an
> > amalgamation distribution of SQLite.
>
> It is only a problem if you are using an environment where SQLite is hidden
> inside a black box and that environment prevents you from using any of the
> SQLite extension mechanisms.  Given SQLite's very liberal license that
> really is a self inflicted problem.
>
> What I suggest you do is a survey of how other databases do this, and
> provide some timings when using SQLite as it currently stands and with a
> workaround such as using a custom reverse function.  It becomes a far more
> compelling case when you can show query times going from unusable to quick,
> as well as some example usage scenarios.  (Yes this is a fair bit of work,
> but that is exactly what you are requesting of the SQLite team - why should
> they do lots of work if you are not prepared to.)
>
> There are also many other possible solutions than a visible reverse
> function.  For example an index behind the scenes could be created that
> allows searching from either end, or something similar with a collation.
>
> > I may decide to forget about it as a
> > target platform and rewrite the app as a web-service, where I would
> indeed
> > have access to the UDF mechanism.  But there was merit in having the
> > application work in offline mode too.
>
> Are the data sets the same size?  I'd expect offline use to be done with
> smaller data sets and using a server for when they are larger.  Pulling
> numbers out of thin air, maybe using O(n) queries up to a million rows is
> just fine and after that you use the server with performance enhancements?
>
> Roger
> -BEGIN 

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/30/2010 08:00 AM, Tim Romano wrote:
> Several things might be responsible for there being no "outpouring of
> support" for a raw reverse function.

Also note that you are proposing it as a very specific workaround for a
specific scenario which is why you want the codepoints reversed rather than
being done "correctly" when dealing with combining unicode codepoints.  ie
your required reverse semantics are not correct for 100% of people who would
want a reverse function :)

- From your further description it is apparent that you don't actually want a
reverse function, but rather a string endswith function that is not O(n).

Another example usage is processing web logs, where for example you want to
find all accesses to names ending in 'google.com'.

> That the SQL dev team has not responded I take as an indication that this
> would be very low priority for them, 

I don't think the issue was articulated well enough (I think my second
paragraph above is better :-) and the solution you required is not a SQL
standard nor fits in with how SQLite development works in general, and you
rejected the existing means by which SQLite allows people to customise it.

You can of course demand their attention through the mechanisms listed at:

  http://www.hwaci.com/sw/sqlite/prosupport.html

> Anyway, the lack of such a function is only a problem for me vis-a-vis an
> amalgamation distribution of SQLite.  

It is only a problem if you are using an environment where SQLite is hidden
inside a black box and that environment prevents you from using any of the
SQLite extension mechanisms.  Given SQLite's very liberal license that
really is a self inflicted problem.

What I suggest you do is a survey of how other databases do this, and
provide some timings when using SQLite as it currently stands and with a
workaround such as using a custom reverse function.  It becomes a far more
compelling case when you can show query times going from unusable to quick,
as well as some example usage scenarios.  (Yes this is a fair bit of work,
but that is exactly what you are requesting of the SQLite team - why should
they do lots of work if you are not prepared to.)

There are also many other possible solutions than a visible reverse
function.  For example an index behind the scenes could be created that
allows searching from either end, or something similar with a collation.

> I may decide to forget about it as a
> target platform and rewrite the app as a web-service, where I would indeed
> have access to the UDF mechanism.  But there was merit in having the
> application work in offline mode too.

Are the data sets the same size?  I'd expect offline use to be done with
smaller data sets and using a server for when they are larger.  Pulling
numbers out of thin air, maybe using O(n) queries up to a million rows is
just fine and after that you use the server with performance enhancements?

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

iEYEARECAAYFAkxTDDMACgkQmOOfHg372QS4zACgg+X3EhdX0Ue4tz4IC9bUH+Ov
CioAoN1+zhreXEGCtw0MCh5k7QcH/0Yn
=zmY5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Tim Romano]

Roger,
I'm not thinking of you as a gatekeeper but as a member of the "vocal
opposition".   You did oppose my request, after all, you didn't simply
ignore it.  But your opposition was not unreasonable -- it was simply based
on certain assumptions that I saw an opportunity to address.

Several things might be responsible for there being no "outpouring of
support" for a raw reverse function. Probably the two most important are 1)
it can be readily accomplished as a UDF, if you have access to the UDF
mechanism;  and  2) how many people are writing applications where "string
ends-with" queries are just as important as "string begins-with" queries?



Such queries are the bread-and-butter of some lexicographical or grammatical
natural-language oriented applications. I would use the function to flip the
raw codepoint order of text, storing the reversed version redundantly in a
second column, so that ends-with queries would have access to an index (with
GLOB): these queries would be restated as starts-with queries against the
reversed-format column. The search-term would be flipped by the function
too.

... where reversedColumn GLOB   reverse( ? )

Or maybe even there will be "virtual columns" in SQLite so that there would
be no need to store the reversed form redundantly in the base table. The
second column would be defined as virtual, pointing at an actual column, but
given a reversed collation, then indexed.

That the SQL dev team has not responded I take as an indication that this
would be very low priority for them, below the horizon of their attention,
 though I think the waters of the discussion may have been clouded by the
issue of an intelligent reverse function which would keep multi-codepoint
combining forms intact during the reversal process. I see no reason why the
two functions could not simply coexist under different names. Oracle and
SQLServer both have raw reverse functions now.

Anyway, the lack of such a function is only a problem for me vis-a-vis an
amalgamation distribution of SQLite.  I may decide to forget about it as a
target platform and rewrite the app as a web-service, where I would indeed
have access to the UDF mechanism.  But there was merit in having the
application work in offline mode too.

Regards
Tim Romano
Swarthmore PA



On Tue, Jul 27, 2010 at 12:10 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/27/2010 07:07 AM, Tim Romano wrote:
> > I will consider it progress if we could get beyond the two stock replies:
> > "you should do that in your application" and "you could  do that in an
> > extension".
>
> You seem to think I am sort of gatekeeper of what goes into SQLite.  I am
> not.  However I do try help - ie try to discern your goals and show how
> they
> can best be met given the architecture of SQLite and how the development
> team is most likely to behave.  Please feel free to ignore me.
>
> Also note:
>
> 1: There was no great outpouring of consensus that SQLite should be changed
>
> 2: The SQLite dev team have not responded at all
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxPBQAACgkQmOOfHg372QTUSACeM5kPZRxHWONCXpP22EbXp7b0
> TDMAoNaEY+9HIohMvf2XIUwC5EucxYdt
> =1BbC
> -END PGP SIGNATURE-
> ___
> 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] PRAGMA database_list: insert into table?

[Alexey Pechnikov]

2010/7/28 Roger Binns 

> If they don't allow you to turn on extension loading then only core changes
> to SQLite will (eventually) get through, or the development environment
> getting friendlier or the user deciding to use a different environment that
> isn't so restrictive and unwilling to change.


SQLite can be linked statically, isn't it? I think the extensions repository
is needed but the core SQLite code may be as compact as possible. There are
a lot of code patterns in SQLite source tree which may be packed as
extensions (intarray SQLite3 extension for Tcl, as example. I use it as
helpful extension but in upstream sources this is test only code. And md5
extension can be easy builded from upstream test code).

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Alexey Pechnikov]

Yes, pragma table_info on a non-existent table is good example. I think we
can only return NULL and don't create the table in this case:

sqlite> .load ./libsqlitepragmacols.so
sqlite> select pragma_cols('pragma table_info(sqlite_master2)')='';

sqlite> select pragma_cols('pragma table_info(sqlite_master2)') IS NULL;
1
sqlite> select pragma_cols('pragma table_info(sqlite_master)');
cid, name, type, notnull, dflt_value, pk, readonly, enforced

Note: my SQLite build has additional fields "readonly" and "enforced".

Updated pragmacols.c include this lines:

rc = sqlite3_prepare_v2(db, (char*)sqlite3_value_text(argv[0]), -1,
, NULL);
if (rc != SQLITE_OK) {
sqlite3_result_error_code(context, rc);
return;
}

str = sqlite3_mprintf("%s", sqlite3_column_name(stmt,0));
for (i=1;i

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/27/2010 01:52 PM, Alexey Pechnikov wrote:
> Why difficulty? 

I meant supporting any possible pragma, without having to hard code a list
of column names for each one.  Implementing your approach and using virtual
tables both require knowing the column names.  Example usages:

  select pragma_to_table("pragma something or other", "temp.foo");
  select * from temp.foo;

or

  create virtual table pragma_something("pragma something or other");
  select * from pragma_something;

I think it is sufficient to call prepare on the pragma and then column_name
on the prepared statement, but don't know what happens when there are no
results (eg pragma table_info on a non-existent table).

> It's more simple than to discuss about :-)

The underlying issue was not really about the difficulty of making pragmas
behave more like selects (easy to solve), but rather using hostile
development environments that statically wrap up SQLite inside, don't
provide a reasonably complete access to SQLite APIs, and won't update their
wrapping to do so.

If they don't allow you to turn on extension loading then only core changes
to SQLite will (eventually) get through, or the development environment
getting friendlier or the user deciding to use a different environment that
isn't so restrictive and unwilling to change.

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

iEYEARECAAYFAkxPWRUACgkQmOOfHg372QQ1dQCgiChbyJpBkcUoe3hXIY3H9aEf
AjUAoNyROutO6tG9B3gDpJ8NaWEPlvOM
=roCg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Alexey Pechnikov]

I'm sorry, see right link for added file pragmacols.c:
http://sqlite.mobigroup.ru/artifact?name=1ef363e38a2fef3ed64a6659079264524d6bc0e0

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Alexey Pechnikov]

2010/7/27 Roger Binns 

> I had also considered doing one.  The approach I would have taken is using
> virtual tables where you could supply the pragma statement as the first
> parameter to the table.  The difficulty is in knowing what column names
> should be returned as I really don't want to hard code them.
>
>
Why difficulty? I did add example:
http://sqlite.mobigroup.ru/artifact/373384470e6b2e00fc503e674def057986ae00b4

.load ./libsqlitepragmacols.so
select pragma_cols('pragma database_list');
seq, name, file

I note you ended up hard coding column names and "polluting" both the
> temporary database and function list :-)
>

It's more simple than to discuss about :-)


> I'd be a little nervous about using SQLITE_STATIC instead of
> SQLITE_TRANSIENT.


You are right, thanks.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/27/2010 11:36 AM, Alexey Pechnikov wrote:
> I did write pragmawrapper extension

I had also considered doing one.  The approach I would have taken is using
virtual tables where you could supply the pragma statement as the first
parameter to the table.  The difficulty is in knowing what column names
should be returned as I really don't want to hard code them.

I note you ended up hard coding column names and "polluting" both the
temporary database and function list :-)

I'd be a little nervous about using SQLITE_STATIC instead of SQLITE_TRANSIENT.

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

iEYEARECAAYFAkxPNpYACgkQmOOfHg372QTLPACgxIszUgLbLPx7/lhMKrr9lMiM
QQEAn2AlPxXtYs0+Yk2NX7xRrA/8iedx
=lRW6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Alexey Pechnikov]

I did write pragmawrapper extension because there are a lot of traffic in
this topic. It's terrible to read these messages ;-)

See http://sqlite.mobigroup.ru/dir?name=ext/_pragmawrapper

Compile as
 gcc -shared pragmawrapper.c -o libsqlitepragmawrapper.so

Use as

.load ./libsqlitepragmawrapper.so
select database_list();
select * from temp.database_list;

Of cource you can add other pragmas similar to pragma database list.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/27/2010 07:07 AM, Tim Romano wrote:
> I will consider it progress if we could get beyond the two stock replies:
> "you should do that in your application" and "you could  do that in an
> extension".

You seem to think I am sort of gatekeeper of what goes into SQLite.  I am
not.  However I do try help - ie try to discern your goals and show how they
can best be met given the architecture of SQLite and how the development
team is most likely to behave.  Please feel free to ignore me.

Also note:

1: There was no great outpouring of consensus that SQLite should be changed

2: The SQLite dev team have not responded at all

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

iEYEARECAAYFAkxPBQAACgkQmOOfHg372QTUSACeM5kPZRxHWONCXpP22EbXp7b0
TDMAoNaEY+9HIohMvf2XIUwC5EucxYdt
=1BbC
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Tim Romano]

Roger,
I do not expect my request to be a priority.  I have suggested only that
such black-box amalgamations be kept in mind as a mitigating factor when the
architects are assigning a priority to a feature request. In those cases
where it is impossible for the end-user to avail himself of the
load_extension capabilities of SQLite, the core may be the only place to
implement a desired feature. The function I asked for (raw/naive
codepoint-by-codepoint  string reverse) does exist in other mainstream
databases, including Oracle and SQLServer.
I will consider it progress if we could get beyond the two stock replies:
"you should do that in your application" and "you could  do that in an
extension".

Regards
Tim Romano
Swarthmore PA



On Mon, Jul 26, 2010 at 6:37 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/26/2010 12:17 PM, Tim Romano wrote:
> > But Roger, the  "layer sitting in front of SQLite"  is a programming
> > environment which provides its own (black-box) connectivity to SQLite,
> and
> > it isn't going to be calling any DLL into which one will have injected a
> UDF
> > library in the manner you have laid out, and it's not going to let the
> > developer load an extension either.   That's what I've been trying to
> make
> > clear.
>
> You keep missing what I am saying :-)
>
> This is only an issue if the layer includes SQLite as an amalgamation
> statically in which case it is a black box as you described.(*)
>
> If that layer uses SQLite as a DLL then you load the same SQLite DLL
> first, tell it call the callback of your choice on connections being
> opened, and then when the layer uses SQLite (which will be the same DLL)
> and opens a connection, your callback is called.
>
> (*) If your problem is now that you have chosen to use some sort of
> access layer, and that layer has chosen to hide SQLite internally, and
> they haven't provided access to some SQLite functionality, and they
> won't if you ask them to, then expecting this to be a priority to the
> SQLite team is rather strange.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxODhwACgkQmOOfHg372QStjwCfdBiKhnNrcpMHCqcWPJI3DzSu
> ejUAoL2PmX3pJ8/1c/RH8zYXRfq1pZyA
> =T6Bb
> -END PGP SIGNATURE-
> ___
> 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] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/26/2010 12:17 PM, Tim Romano wrote:
> But Roger, the  "layer sitting in front of SQLite"  is a programming
> environment which provides its own (black-box) connectivity to SQLite, and
> it isn't going to be calling any DLL into which one will have injected a UDF
> library in the manner you have laid out, and it's not going to let the
> developer load an extension either.   That's what I've been trying to make
> clear.

You keep missing what I am saying :-)

This is only an issue if the layer includes SQLite as an amalgamation
statically in which case it is a black box as you described.(*)

If that layer uses SQLite as a DLL then you load the same SQLite DLL
first, tell it call the callback of your choice on connections being
opened, and then when the layer uses SQLite (which will be the same DLL)
and opens a connection, your callback is called.

(*) If your problem is now that you have chosen to use some sort of
access layer, and that layer has chosen to hide SQLite internally, and
they haven't provided access to some SQLite functionality, and they
won't if you ask them to, then expecting this to be a priority to the
SQLite team is rather strange.

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

iEYEARECAAYFAkxODhwACgkQmOOfHg372QStjwCfdBiKhnNrcpMHCqcWPJI3DzSu
ejUAoL2PmX3pJ8/1c/RH8zYXRfq1pZyA
=T6Bb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Jean-Christophe Deschamps]

>But Roger, the  "layer sitting in front of SQLite"  is a programming
>environment which provides its own (black-box) connectivity to SQLite, and
>it isn't going to be calling any DLL into which one will have injected 
>a UDF
>library in the manner you have laid out, and it's not going to let the
>developer load an extension either.   That's what I've been trying to make
>clear.

I second that.

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

Re: [sqlite] PRAGMA database_list: insert into table?

[Tim Romano]

But Roger, the  "layer sitting in front of SQLite"  is a programming
environment which provides its own (black-box) connectivity to SQLite, and
it isn't going to be calling any DLL into which one will have injected a UDF
library in the manner you have laid out, and it's not going to let the
developer load an extension either.   That's what I've been trying to make
clear.

Regards
Tim Romano
Swarthmore PA


On Mon, Jul 26, 2010 at 12:07 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/26/2010 06:03 AM, Tim Romano wrote:
> >  The goal is to expose the UDF to the "layer sitting in front".  Are you
> > saying sqlite3_auto_extension allows me to run a program, once, that will
> > register a piece of code that is loaded whenever ANY OTHER PROGRAM
> creates a
> > connection to my database file?  And that this happens *transparently* --
>
> Nope.  You have to do something each program session.  The steps:
>
> - - LoadLibrary("sqlite.dll") then call sqlite3_auto_extension registering
> callback that adds UDF etc
> - - Now use your database access layer
>
> The operating system will use the already loaded sqlite dll rather than
> loading an additional copy when the database access layer requests SQLite
> and then the callback is called when each database is opened (aka
> "connection").
>
> You can also do the first step using a technique known as dll injection
> where you have a shared library that loads SQLite, calls auto_extension,
> implements whatever UDF functions you want etc.  Your library is then
> automatically loaded and run in any process:
>
>  http://en.wikipedia.org/wiki/DLL_injection
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxNstQACgkQmOOfHg372QTzyACg3R2pBsKSJT/a5g6ApMc1Ul1D
> +i0AoIAHw4m+X8sIXA0EXPmbd3blmcSp
> =E8dR
> -END PGP SIGNATURE-
> ___
> 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] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/26/2010 06:03 AM, Tim Romano wrote:
>  The goal is to expose the UDF to the "layer sitting in front".  Are you
> saying sqlite3_auto_extension allows me to run a program, once, that will
> register a piece of code that is loaded whenever ANY OTHER PROGRAM creates a
> connection to my database file?  And that this happens *transparently* --

Nope.  You have to do something each program session.  The steps:

- - LoadLibrary("sqlite.dll") then call sqlite3_auto_extension registering
callback that adds UDF etc
- - Now use your database access layer

The operating system will use the already loaded sqlite dll rather than
loading an additional copy when the database access layer requests SQLite
and then the callback is called when each database is opened (aka "connection").

You can also do the first step using a technique known as dll injection
where you have a shared library that loads SQLite, calls auto_extension,
implements whatever UDF functions you want etc.  Your library is then
automatically loaded and run in any process:

  http://en.wikipedia.org/wiki/DLL_injection

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

iEYEARECAAYFAkxNstQACgkQmOOfHg372QTzyACg3R2pBsKSJT/a5g6ApMc1Ul1D
+i0AoIAHw4m+X8sIXA0EXPmbd3blmcSp
=E8dR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Tim Romano]

Since this discussion might help the OP too, I hope it's not regarded as a
thread hijack.  I will create a new thread otherwise. Mr. v3meo, your
thoughts?

A question about this advice:

"If the layer sitting in front of SQLite doesn't
expose create function then you can still do so.  Load the SQLite DLL
separately and use sqlite3_auto_extension to register a piece of code that
is called back for each new connection that registers the UDF."

 The goal is to expose the UDF to the "layer sitting in front".  Are you
saying sqlite3_auto_extension allows me to run a program, once, that will
register a piece of code that is loaded whenever ANY OTHER PROGRAM creates a
connection to my database file?  And that this happens *transparently* --
the other program simply creates a connection as usual, and does nothing
else, and the extension is ready to be used in  sql statements? The other
program (the "layer sitting in front of SQLite") does not first need to
invoke sqlite3_enable_load_extension?
Is the entry point to the extension being written to the database by
sqlite3_auto_extension?

Regards
Tim Romano
Swarthmore PA













On Sun, Jul 25, 2010 at 1:24 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/25/2010 07:08 AM, Tim Romano wrote:
> > Something that can be knocked out in a half-hour
>
> Nothing gets banged out in half an hour with SQLite :-)  Using a string
> reverse example:
>
> - - The code has to be hooked in various places
> - - The function has to deal with the various ways strings could be passed
> and
> returned, especially UTF8 vs UTF16 encoding
> - - The function has to make sense with things like combining diacritics
> and
> surrogate pairs
> - - The function has to deal with bad parameters (eg passed an integer,
> float
> or blob to reverse)
> - - The function has to deal with running out of memory
> - - Documentation has to be added in several different places including
> inside
> the code, "core functions" in syntax pages etc
> - - Various specifications have to be added as well as evidence marks in
> the
> code (for example look at Reading Data in
> http://www.sqlite.org/fileio.html
> and note the [H35010] and similar annotations and at the timelime for EV
> style annotations)
> - - The TCL test suite has to be updated, not just for correct behaviour
> but
> also verifying behaviour with all the bad inputs, running out of memory,
> invalid UTF8/16 etc
> - - The TH3 test suite has to be updated (C code generation based) and
> verify
> that it maintains 100% MC/DC test coverage
> - - If this function is present in other database engines then the SQL
> Logic
> Test suite has to be updated and outputs compared
> - - The changelog has to be updated
>
> The code then has to be maintained and tested for all future SQLite 3
> releases and internal changes (eg if UTF32 was added, new/changing memory
> allocators).
>
> See http://ometer.com/features.html and especially the Linus Torvalds
> posts
> it links to.
>
> > Some application
> > runtime environments are "sandboxed" and do not give the developer the
> > freedom to call an external library,
>
> But eventually the SQLite library is used so there is some mechanism to
> call
> external libraries, although there may be restrictions.
>
> > or to add UDFs to their implementation of SQLite.
>
> This is a good example.  If the layer sitting in front of SQLite doesn't
> expose create function then you can still do so.  Load the SQLite DLL
> separately and use sqlite3_auto_extension to register a piece of code that
> is called back for each new connection that registers the UDF.
>
> > Of course I understand it is not the fault  of the SQLite architects or
> its
> > author when a runtime environment restricts what can be done with SQLite.
>
> And it is really the authors of those environments that should be contacted
> about making their wrapping more complete and facilitating full usage of
> SQLite.
>
> And failing that you can still implement what you need in your host
> programming environment (eg a reverse function).  Sure it may be a lot less
> efficient or more hassle, but you choose that environment :-)
>
> > I'm only asking that such environments be kept in mind as one of the
> > criteria when assigning a priority to a requested feature.
>
> You are probably not aware that the SQLite C API is designed specifically
> to
> be easily used by other development environments.  It is an absolute joy to
> wrap.  (Yes there a few minor quirks, but compared to some other APIs they
> are insignificant.)
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxMcyoACgkQmOOfHg372QTr4gCg0Og8C3nin0Ffui+ngnuu5eJU
> CKYAni8PPX4YwIi3FV6B3WIMbMebnnvE
> =L6lP
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/25/2010 07:08 AM, Tim Romano wrote:
> Something that can be knocked out in a half-hour

Nothing gets banged out in half an hour with SQLite :-)  Using a string
reverse example:

- - The code has to be hooked in various places
- - The function has to deal with the various ways strings could be passed and
returned, especially UTF8 vs UTF16 encoding
- - The function has to make sense with things like combining diacritics and
surrogate pairs
- - The function has to deal with bad parameters (eg passed an integer, float
or blob to reverse)
- - The function has to deal with running out of memory
- - Documentation has to be added in several different places including inside
the code, "core functions" in syntax pages etc
- - Various specifications have to be added as well as evidence marks in the
code (for example look at Reading Data in http://www.sqlite.org/fileio.html
and note the [H35010] and similar annotations and at the timelime for EV
style annotations)
- - The TCL test suite has to be updated, not just for correct behaviour but
also verifying behaviour with all the bad inputs, running out of memory,
invalid UTF8/16 etc
- - The TH3 test suite has to be updated (C code generation based) and verify
that it maintains 100% MC/DC test coverage
- - If this function is present in other database engines then the SQL Logic
Test suite has to be updated and outputs compared
- - The changelog has to be updated

The code then has to be maintained and tested for all future SQLite 3
releases and internal changes (eg if UTF32 was added, new/changing memory
allocators).

See http://ometer.com/features.html and especially the Linus Torvalds posts
it links to.

> Some application
> runtime environments are "sandboxed" and do not give the developer the
> freedom to call an external library,

But eventually the SQLite library is used so there is some mechanism to call
external libraries, although there may be restrictions.

> or to add UDFs to their implementation of SQLite.

This is a good example.  If the layer sitting in front of SQLite doesn't
expose create function then you can still do so.  Load the SQLite DLL
separately and use sqlite3_auto_extension to register a piece of code that
is called back for each new connection that registers the UDF.

> Of course I understand it is not the fault  of the SQLite architects or its
> author when a runtime environment restricts what can be done with SQLite.

And it is really the authors of those environments that should be contacted
about making their wrapping more complete and facilitating full usage of SQLite.

And failing that you can still implement what you need in your host
programming environment (eg a reverse function).  Sure it may be a lot less
efficient or more hassle, but you choose that environment :-)

> I'm only asking that such environments be kept in mind as one of the
> criteria when assigning a priority to a requested feature.

You are probably not aware that the SQLite C API is designed specifically to
be easily used by other development environments.  It is an absolute joy to
wrap.  (Yes there a few minor quirks, but compared to some other APIs they
are insignificant.)

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

iEYEARECAAYFAkxMcyoACgkQmOOfHg372QTr4gCg0Og8C3nin0Ffui+ngnuu5eJU
CKYAni8PPX4YwIi3FV6B3WIMbMebnnvE
=L6lP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Tim Romano]

Roger,
Clearly not every feature that has found its way into SQLite is useful
to "the majority" of the user base, but I will accept the core philosophical
position here as not unreasonable--a requested feature that benefits very
few use-cases might well be placed lower on the to-do list than one
everybody wants. And yet there are some very simple and lightweight
functions that are easy to add to the core without fear of bloat. Something
that can be knocked out in a half-hour might get a higher a priority than
something everyone desires but is far more involved, and could take many
man-weeks to get right.

 You write:

"There is no reason why you can't talk to the library simultaneously via
ADO.net as well as via the SQLite API directly."

It is not always possible to do what you have suggested.   Some application
runtime environments are "sandboxed" and do not give the developer the
freedom to call an external library, or to add UDFs to their implementation
of SQLite. If the functionality is not present in the core database, the
developer could be out of luck when programming in such environments.

Of course I understand it is not the fault  of the SQLite architects or its
author when a runtime environment restricts what can be done with SQLite.
I'm only asking that such environments be kept in mind as one of the
criteria when assigning a priority to a requested feature.

@Sam: I understand the open-source and TCL origins of SQLite, but the
essence of something does not always come out of its origins; destiny and
destination can shape things too. Who could have known, way back when, that
SQLite would influence the direction of the web?

Regards
Tim Romano
Swarthmore PA






On Sat, Jul 24, 2010 at 10:57 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/24/2010 04:42 AM, Tim Romano wrote:
> > Quite a few users of SQLite these days are not wrapping
> > the SQLite libraries in their own client app but are communicating with
> the
> > database via a bridge as if it were a remote server engine.
>
> Yes, but the SQLite library is still local within the process in that case.
>  There is no reason why you can't talk to the library simultaneously via
> ADO.net as well as via the SQLite API directly.  (If you are using pragmas
> then you are already having SQLite specific code.)
>
> > Your opposition to my request several months ago for a raw reverse
> > function was colored in this way. You did not acknowledge at the time
> that a
> > raw-reversed  (and hence possibly malformed) sequence of unicode
> codepoints
> >  could give middleware the hiccups, and insisted that it this reversal be
> > done "in the application".
>
> SQLite doesn't have a reverse function as shipped, and so is not the one
> creating invalid data.  I'll happily acknowledge that malformed Unicode is
> a
> bad thing under all circumstances.
>
> The license of SQLite allows you to do anything you want with it.  (The
> trademark prevents you calling the result 'SQLite'.)  You can add, change,
> delete etc anything.  You can redistribute the changes or keep them secret.
>  You can charge for them.
>
> What many of these requests amount to is wanting someone else to make a
> change (typically the SQLite developers) and for the change to be
> distributed as part of SQLite.  The bar for that is *considerably* higher
> and you would need to demonstrate the value to the majority of the user
> base
> and why the extensive existing mechanisms (extensions, the SQLite API etc)
> are not sufficient.
>
> The "opposition" is pointing out that bar, and suggesting alternate
> approaches.  (Note I am not a core developer nor do I speak for them but
> have been around long enough to observe what they usually do.)
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxK/2kACgkQmOOfHg372QSyrgCfaMDkggv6PObyADTR+Cfdz68E
> b3YAnj/ihpG0DVet4Y/5Z/RlSDs9QuWR
> =K1/M
> -END PGP SIGNATURE-
> ___
> 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] PRAGMA database_list: insert into table?

[ve3meo]

"Roger Binns"  wrote in 
message news:4c4b1ca7.1040...@rogerbinns.com...
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/24/2010 05:38 AM, ve3meo wrote:
>> I am a humble user of the command line implementation and of various 
>> SQLite
>> managers and not an application developer so I cannot do it in code.
>
> You are coding!  The command line tool is merely a thin wrapper around the
> library and the managers are a gui equivalent.
>
> I'd encourage you to try one of the scripting languages out there.  They
> provide an easy way of adding a little more control flow around the SQL,
> variables, better interoperability with files etc.  These languages are 
> easy
> to learn and use.
>
> My personal preference is Python, but you won't go far wrong with Lua, 
> Tcl,
> Perl, PHP, Basic etc.  See a long list of languages that have SQLite
> available at:
>
>  http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

I have dabbled years ago with Tcl and even resurrected Quick Basic recently 
so I could do something - even probably a DOS batch file. Before heading 
there, I wanted to exhaust the possibility of doing it within a SQL query. 
That's convenient for me and other users to be able to do that and many 
other queries from within the same SQLite manager.

>
>>> Depending on what the information is you are obtaining, it may be 
>>> possible
>>> to get it via direct queries on sqlite_master.
>>
>> Thanks, I'll investigate further.
>
> How about telling us what you are trying to achieve (not how you are 
> trying
> to achieve it :-)

I use a genealogical software that employs SQLite as its database engine. 
That software is deficient in some respects and I have developed quite a few 
SQLite queries that I can run outside the application to examine and modify 
data in ways that the app cannot. One query aims to extract various 
properties of the genealogical database that can tell me its characteristics 
in more detail than the software does and may flag possible problems or 
areas needing attention. The query compares certain data against that of an 
attached reference database which may change with a new release of the 
software. What I want to add to the results of this query are the names of 
the test database file and the reference file. I was hoping to do that from 
within the same sql file.

Tom

>
> Roger



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

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/24/2010 05:38 AM, ve3meo wrote:
> I am a humble user of the command line implementation and of various SQLite 
> managers and not an application developer so I cannot do it in code.

You are coding!  The command line tool is merely a thin wrapper around the
library and the managers are a gui equivalent.

I'd encourage you to try one of the scripting languages out there.  They
provide an easy way of adding a little more control flow around the SQL,
variables, better interoperability with files etc.  These languages are easy
to learn and use.

My personal preference is Python, but you won't go far wrong with Lua, Tcl,
Perl, PHP, Basic etc.  See a long list of languages that have SQLite
available at:

  http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

>> Depending on what the information is you are obtaining, it may be possible
>> to get it via direct queries on sqlite_master.
>
> Thanks, I'll investigate further.

How about telling us what you are trying to achieve (not how you are trying
to achieve it :-)

Roger

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

iEYEARECAAYFAkxLHKMACgkQmOOfHg372QTLRgCgu0hgNSamhf3TnpZXQGYrZw5Q
V7IAnjKqukZ3ZqDEuFvFwIn8ir4CHzBa
=kg4J
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Kees Nuyt]

On Sat, 24 Jul 2010 08:38:31 -0400, "ve3meo"
 wrote:

>"Roger Binns"  wrote in 
>message news:4c4a5bd5.5010...@rogerbinns.com...
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 07/23/2010 07:47 PM, ve3meo wrote:
>>> Is it possible to store the results of a PRAGMA statement, especially 
>>> PRAGMA
>>> database_list in a SQLite temporary table using only SQLite commands?
>>
>> No.  Is there any particular reason your code can't copy them internally?
>> Remember that SQLite is a library - it lives inside your application - and
>> is not some remote unchangeable component.
>
>I am a humble user of the command line implementation and of various SQLite 
>managers and not an application developer so I cannot do it in code.
>
>> Depending on what the information is you are obtaining, it may be possible
>> to get it via direct queries on sqlite_master.
>
>Thanks, I'll investigate further.


A humble example:

k...@ozon ~/sql $ cat ve3meo.ksh
#!/usr/bin/ksh
#
## demo shell / sql mixture
#
SQLITE=$(which sqlite3)
printf "SELECT sqlite_version() AS version;\n.schema\n" \
| ${SQLITE} $1
for t in $(printf ".tables\n" \
| ${SQLITE} $1)
do
printf "table %s:\n" $t
printf ".headers on\nPRAGMA table_info(%s);\n" $t \
| ${SQLITE} $1
done

k...@ozon ~/sql $ ./ve3meo.ksh unusedids.db3
3.6.23.1
CREATE TABLE integers (
id INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE test (
id INTEGER PRIMARY KEY NOT NULL,
tx TEXT
);
table integers:
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
table test:
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|1||1
1|tx|TEXT|0||0
k...@ozon ~/sql $ 
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/24/2010 04:42 AM, Tim Romano wrote:
> Quite a few users of SQLite these days are not wrapping
> the SQLite libraries in their own client app but are communicating with the
> database via a bridge as if it were a remote server engine.

Yes, but the SQLite library is still local within the process in that case.
 There is no reason why you can't talk to the library simultaneously via
ADO.net as well as via the SQLite API directly.  (If you are using pragmas
then you are already having SQLite specific code.)

> Your opposition to my request several months ago for a raw reverse
> function was colored in this way. You did not acknowledge at the time that a
> raw-reversed  (and hence possibly malformed) sequence of unicode codepoints
>  could give middleware the hiccups, and insisted that it this reversal be
> done "in the application".

SQLite doesn't have a reverse function as shipped, and so is not the one
creating invalid data.  I'll happily acknowledge that malformed Unicode is a
bad thing under all circumstances.

The license of SQLite allows you to do anything you want with it.  (The
trademark prevents you calling the result 'SQLite'.)  You can add, change,
delete etc anything.  You can redistribute the changes or keep them secret.
 You can charge for them.

What many of these requests amount to is wanting someone else to make a
change (typically the SQLite developers) and for the change to be
distributed as part of SQLite.  The bar for that is *considerably* higher
and you would need to demonstrate the value to the majority of the user base
and why the extensive existing mechanisms (extensions, the SQLite API etc)
are not sufficient.

The "opposition" is pointing out that bar, and suggesting alternate
approaches.  (Note I am not a core developer nor do I speak for them but
have been around long enough to observe what they usually do.)

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

iEYEARECAAYFAkxK/2kACgkQmOOfHg372QSyrgCfaMDkggv6PObyADTR+Cfdz68E
b3YAnj/ihpG0DVet4Y/5Z/RlSDs9QuWR
=K1/M
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[ve3meo]

"Roger Binns"  wrote in 
message news:4c4a5bd5.5010...@rogerbinns.com...
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/23/2010 07:47 PM, ve3meo wrote:
>> Is it possible to store the results of a PRAGMA statement, especially 
>> PRAGMA
>> database_list in a SQLite temporary table using only SQLite commands?
>
> No.  Is there any particular reason your code can't copy them internally?
> Remember that SQLite is a library - it lives inside your application - and
> is not some remote unchangeable component.

I am a humble user of the command line implementation and of various SQLite 
managers and not an application developer so I cannot do it in code.

> Depending on what the information is you are obtaining, it may be possible
> to get it via direct queries on sqlite_master.

Thanks, I'll investigate further.

>
> Roger
> ___
> 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] PRAGMA database_list: insert into table?

[Sam Carleton]

Tim,

I, like you, am using SQLite as the DB to a web server.  But I have to agree
with Roger.  I do so from the prospective that web client cannot make native
calls to SQLite.  Thus the web server is the client.  Further more I would
say that in all web server based solutions, the web server is the DB client.

With fully integrated solutions like ASP.Net and MS SQL Server, a lot of the
connection stuff is automatically handled by the vender (Microsoft) so that
application developers like you and me don't have to mess with it.  But when
we move to the world of open source, we have to deal with some plumbing.
Not high on my list of things I like to do, but it has to be done:(

Sam

On Sat, Jul 24, 2010 at 7:42 AM, Tim Romano  wrote:

> Roger,
> It would be more accurate to say that SQL is a library which *can* live
> inside an application.  Whether it actually does live inside it will depend
> on how one is using SQLite.  When it is used as a "back-end" reached via
> middleware, such as the ADO.NET driver,  SQLite does not live inside the
> client application.  Quite a few users of SQLite these days are not
> wrapping
> the SQLite libraries in their own client app but are communicating with the
> database via a bridge as if it were a remote server engine.
>
> The reason I bring this up is that  your replies are sometimes colored by
>  the assumption that one is using SQLite in library-mode, which may not be
> the case. Your opposition to my request several months ago for a raw
> reverse
> function was colored in this way. You did not acknowledge at the time that
> a
> raw-reversed  (and hence possibly malformed) sequence of unicode codepoints
>  could give middleware the hiccups, and insisted that it this reversal be
> done "in the application".
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
>
> On Fri, Jul 23, 2010 at 11:19 PM, Roger Binns 
> wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> >
> > Remember that SQLite is a library - it lives inside your application -
> and
> > is not some remote unchangeable component.
> >
> >
> >
> ___
> 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] PRAGMA database_list: insert into table?

[Tim Romano]

Roger,
It would be more accurate to say that SQL is a library which *can* live
inside an application.  Whether it actually does live inside it will depend
on how one is using SQLite.  When it is used as a "back-end" reached via
middleware, such as the ADO.NET driver,  SQLite does not live inside the
client application.  Quite a few users of SQLite these days are not wrapping
the SQLite libraries in their own client app but are communicating with the
database via a bridge as if it were a remote server engine.

The reason I bring this up is that  your replies are sometimes colored by
 the assumption that one is using SQLite in library-mode, which may not be
the case. Your opposition to my request several months ago for a raw reverse
function was colored in this way. You did not acknowledge at the time that a
raw-reversed  (and hence possibly malformed) sequence of unicode codepoints
 could give middleware the hiccups, and insisted that it this reversal be
done "in the application".

Regards
Tim Romano
Swarthmore PA



On Fri, Jul 23, 2010 at 11:19 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
>
> Remember that SQLite is a library - it lives inside your application - and
> is not some remote unchangeable component.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/23/2010 07:47 PM, ve3meo wrote:
> Is it possible to store the results of a PRAGMA statement, especially PRAGMA 
> database_list in a SQLite temporary table using only SQLite commands?

No.  Is there any particular reason your code can't copy them internally?
Remember that SQLite is a library - it lives inside your application - and
is not some remote unchangeable component.

Depending on what the information is you are obtaining, it may be possible
to get it via direct queries on sqlite_master.

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

iEYEARECAAYFAkxKW9EACgkQmOOfHg372QQTSwCgwNSQxsC4utvX53H/iVYAJSiF
nlgAniMMP0svxm59BjTeMJnr+EBGTnJt
=kAD0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users