Re: [sqlite] New word to replace "serverless"

[Gerry Snyder]

I think of it as being "standalone."

Gerry Snyder

On Mon, Jan 27, 2020, 3:19 PM Richard Hipp  wrote:

> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
>
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
>
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
>
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Upgrading from version 3.6.16 to 3.30.1

[Gerry Snyder]

On Fri, Jan 3, 2020 at 10:57 AM Richard Watt  wrote:

> I'm currently updating a C# .NET application that uses SQLite 3.6.16 to
> run under a new Siemens Sinumerik version and I'd also like to update it
> to use the latest SQLite, which is 3.30.1.
>
> Does anyone know of any potential issues I might encounter and how to
> correct them please? I've tried a Google search but not really finding
> much that helps.
>
> Best regards,
>
> --
> Richard Watt
>
>
 Few packages are maintained with more care about backward compatibility
than SQLite.

The best estimate for number of issues you will run into is Zero.

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

Re: [sqlite] Difference between hex notation and string notation

[Gerry Snyder]

I would suggest that if you ran:

SELECT ROWID,MSG,typeof(MSG) FROM LOG;

and read https://sqlite.org/datatype3.html

you would have a better idea of what is going on. You might still consider
it a but, but it is expected behavior.

Gerry

On Fri, Dec 13, 2019 at 8:51 AM Sascha Ziemann  wrote:

> I have a problem to find rows in a database when I write in hex notation:
>
> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
> INSERT INTO LOG VALUES
>
> (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
> down');
> SELECT ROWID,MSG FROM LOG; --
> returns both rows
> SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
> returns just the second
> SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; --
> returns both rows
>
> This looks like a bug to me.
>
> Regards
> ___
> 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] what is the optimized way to do the vaccum?

[Gerry Snyder]

On Wed, Dec 4, 2019, 9:28 PM Zhu, Liang [AUTOSOL/ASSY/US] <
liang@emerson.com> wrote:

> All Sqlite Expert,
>
> I have one table,  I am inserting and deleting record to and from this
> table very 250ms.  I always maintain 1000 rows in this table.  I have
> another table,  I am inserting and deleting data to and from this table
> every 1s.  The data record in this table maintains at 200,000 rows.
> Can I get some recommendation on what is optimized technique to do the
> vaccum for my database?
>
> Thank you,
> Liang
>

Why do you think that you need to vacuum at all?


> ___
> 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] Slight problem compiling Tcl bindings -- can't strip .dll file

[Gerry Snyder]

I just compiled 3.30.1 on Win 10 using msys (./configure   make).  I saw
what seemed to be the usual "permission denied" and warning messages.

As usual, I tried to strip the binary (force of habit rather than needing a
smaller .dll), and got the following message:

$ strip sqlite3301.dll
C:\MinGW\bin\strip.exe: unable to rename 'sqlite3301.dll'; reason:
Permission denied

But the funniest thing is that after the attempted strip the .dll
disappears.

If copied before the strip, the .dll functions.

Strange.

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

Re: [sqlite] Failing SQLite .import returns exit status 0

[Gerry Snyder]

A constraint doing its job is not an error.

On Fri, Jun 7, 2019, 7:49 PM Faheem Mitha  wrote:

>
> Hi,
>
> Consider the following shell script:
>
> rm -f empty.db empty.csv
> echo "" > empty.csv
> echo "a, b" >> empty.csv
> sqlite3 empty.db \
> 'DROP TABLE IF EXISTS empty;' \
> '.mode csv' \
> 'CREATE TABLE IF NOT EXISTS empty(A, B NOT NULL);' \
> '.import empty.csv empty' '.exit'
> echo "error code is" $?
>
> This returns:
>
> sh sqlite_err.sh
>
> empty.csv:1: expected 2 columns but found 1 - filling the rest with NULL
> empty.csv:1: INSERT failed: NOT NULL constraint failed: empty.B
> error code is 0
>
> There is an error, but the exit status is 0. Isn't that wrong?
>
> Regards, Faheem Mitha
> ___
> 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] Custom aggregate functions in Tcl

[Gerry Snyder]

I hope your work makes into the SQLite source code. It will be useful.

Gerry Snyder

On Tue, Jan 29, 2019 at 12:16 AM Andy Goth  wrote:

> I wish to define custom aggregate functions in Tcl, but this capability is
> currently not exposed through the Tcl interface. Thus I am thinking about
> how best to add it. Here's a first crack at a design proposal:
>
> Extend the [db function] command to accept an -aggregate switch that makes
> the new function be an aggregate function. Otherwise, the function defaults
> to being a scalar function.
>
> When an aggregate Tcl function is called from SQL, it is invoked in two
> phases: step and final. The step phase receives the input data, and the
> final phase produces the result.
>
> During step, the function is invoked once for each row (in the group). For
> the first row (in the group), the first argument to the function will be
> empty string, and subsequent arguments are the SQL values from the row
> being processed. For each additional row (in the group), the first argument
> is the Tcl value returned by the prior invocation, and subsequent arguments
> are as above.
>
> During final, the function is invoked one last time (at the end of each
> group). Its sole argument is the return value of the last step invocation,
> or empty string if there were no rows. The return value of the Tcl function
> is used as the return value of the SQL function.
>
> If there were no rows and GROUP BY was used, the function is not invoked at
> all, because there were no groups.
>
> The Tcl function can tell whether it is in the step or final phase by how
> many arguments it receives. If it receives multiple, it is in step. If it
> receives only one, it is in final.
>
> Depending on how it is written, the Tcl function may be able to tell if
> it's being called for the first time (within the group) by checking if its
> first argument is empty string. If non-empty, it definitely has been called
> before (within the group). If empty, it is most likely on the first row
> and, if in step, may need to initialize.
>
> However, if the aggregate function wishes to disregard some of its input
> data, it may choose to return empty string to discard any state data
> arising from the current and prior rows (in the group). This will result in
> empty string being the first argument to the next invocation. This mode of
> operation is ill-advised but not illegal, though maybe some valid use cases
> may exist, provided that ORDER BY is being used.
>
> It is an error to pass an aggregate Tcl function zero arguments.
>
> Here's an example that will return the entire Fossil global_config table as
> a key/value dict:
>
> db function list -deterministic -aggregate {
> apply {{state args} {
> concat $state $args
> }
> }
>
> db onecolumn {
> SELECT list(name, value)
>   FROM global_config
>  ORDER BY name
> }
>
> As for implementation, I think the existing code can be leveraged to a
> great extent. Adapt the existing tclSqlFunc() to be the backend to new
> tclSqlFuncScalar(), tclSqlFuncStep(), and tclSqlFuncFinal() functions, and
> adjust the DB_FUNCTION code to recognize -aggregate and pass different
> function pointers accordingly. Use sqlite3_aggregate_context() to hold the
> Tcl_Obj * resulting from each invocation, then let it be the first argument
> to the next invocation.
>
> I would be happy to implement this myself, since this is functionality I
> will be needing soon. Once I'm satisfied with it and have test suite
> updates, what's the recommended method for sharing my patch?
>
> Question: does xFinal() get called if an error occurs during (or between)
> calling xStep()? Are errors even possible? I'm curious if there's any way
> to leak the Tcl_Obj pointed to by the aggregate context.
> ___
> 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] Question about floating point

[Gerry Snyder]

On Wed, Dec 19, 2018 at 4:57 PM Keith Medcalf  wrote:

>
>
> >All I meant was that with a decimal exponent, the units could be
> >dollars,
> >and additions and subtractions of cents would be exact (assuming the
> >mantissa has enough bits), with no worries about rounding. Which is
> >the
> >basis for this whole discussion.
>
> This is called fixed point.  All that is required is that you keep track
> of the decimal point yourself...sort of like using a slide rule.
>

Er, no. Not at all.

It's called floating point with a decimal exponent. Calculations in cents
come out exact*, calculations in mils come out exact*, and the machine
keeps track of the decimal point.

* Assuming that the input numbers are integer cents (exponent -2) or mils
(exponent -3)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Mon, Dec 17, 2018 at 10:04 AM Larry Brasfield 
wrote:

> Gerry Snyder wrote:
> < I don't think anyone has pointed out that the "evil" is not floating
> point, it is the binary exponent.
>
> Disregarding the “evil” appellation, the fundamental fact is that, with
> modern floating point hardware (implementing the IEEE-754 standard), only
> that subset of rational numbers having a denominator which is a power of 2
> can be represented.  If that is what you were trying to say, I would point
> out that it is not the representation of the exponent (binary or otherwise)
> that creates the mismatch with (many) rational numbers having a denominator
> which is a power of 10; it is that many such denominators cannot be
> represented at all when the interpretation of the exponent Ne is as 2 ^ Ne.


All I meant was that with a decimal exponent, the units could be dollars,
and additions and subtractions of cents would be exact (assuming the
mantissa has enough bits), with no worries about rounding. Which is the
basis for this whole discussion.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Mon, Dec 17, 2018 at 2:43 AM Tim Streater  wrote:

> On 17 Dec 2018, at 04:32, D Burgess  wrote:
>
> >> Banks still use, as they have for a very long time, Binary Coded
> >> Decimal, or some equivalent that does not suffer from a loss of
> >> accuracy, so all this foofaraw to do with floating point representation
> >> of various amounts of currency does not apply to the real world.
>
> > As do insurance companies and many in the manufacturing world
> (inventory).
> > There is a lot to like about BCD.
>
> And do any processors/computers have hardware for that these days?
>
>
>
I don't think anyone has pointed out that the "evil" is not floating point,
it is the binary exponent.

Just have the exponent be a decimal number, and accept that the mantissa
may have some high-order zeroes.

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

Re: [sqlite] Persistent snapshots and rollbacks

[Gerry Snyder]

On Fri, Oct 5, 2018 at 8:40 AM Daniel Kraft  wrote:

> Hi!
>
> I need the ability to make multiple changes / commits to my SQLite
> database but keep snapshots of previous states and potentially roll back
> to those states later on.
>

If the database is small enough that you can have multiple copies of it,
backup and restore could be a cumbersome way to do what you want.


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

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Gerry Snyder]

If you want the row with the minimum B, and the row with a minimum C, then
the union of two queries would seem to be appropriate.

Gerry Snyder

On Sat, Jun 30, 2018, 6:45 AM Luuk  wrote:

>
>
> On 30-6-2018 15:39, Abroży Nieprzełoży wrote:
> >> SELECT
> >>a,
> >>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> >>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> >> FROM t1 t
> >> GROUP BY a
> > Why not
> > select a, min(b) as b, min(c) as c from t1 group by a;
> > ?
>
> It still does not quarantee that the valuse show for b and c are comming
> from the same row...
>
>
> >
> > 2018-06-30 15:12 GMT+02:00, Luuk :
> >> On 30-6-2018 14:55, Keith Medcalf wrote:
> >>> Note that this is SQLite3 specific (and specific to Sybase of the era
> >>> where Microsoft SQL Server was actually just a rebranded Sybase, and
> >>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
> >>> cannot do a query of the form:
> >>>
> >>> SELECT c1, c2
> >>>   FROM t1
> >>> GROUP BY c2;
> >>>
> >>> because each column in the select list must be either an aggregate or
> >>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
> >>> however and the value returned is taken from "some random row" of the
> >>> group.  If there are multiple such columns, they all come from the same
> >>> row in the group.  Although documented as a "random" row of the group,
> it
> >>> is the first (or last) row visited in the group while solving the query
> >>> (and this is of course subject to change but within the same version of
> >>> SQLite3 will deterministically be the row either first or last in the
> >>> visitation order -- the actual row may of course change depending on
> use
> >>> of indexes, etc).  You can re-write this part so it will work in other
> SQL
> >>> dialects that strictly enforce the requirement for c1 to be either an
> >>> aggregate or listed in the group by clause.
> >>>
> >>> ---
> >>> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> >>> a lot about anticipated traffic volume.
> >>>
> >> Ok ,my highway to hell start here (regargind the use of SQL)
> >>
> >> In SQLite3 you are allowed to do this:
> >> SELECT a,b,c
> >> FROM t1
> >> GROUP BY a
> >>
> >> The values of 'b' and 'c' will be taken from a 'random' row...
> >>
> >> But if we rewrite this in SQL, i am getting something like this:
> >> SELECT
> >>a,
> >>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> >>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> >> FROM t1 t
> >> GROUP BY a
> >>
> >> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> >> i mean how can one be use that both values are from the same row?
> >> This is not a problem to SQLite, because in SQLite the values of b and c
> >> seems to be originating from the same row, but what about *SQL* (if that
> >> exists...?)
> >>
> >> --
> >> some test results:
> >> sqlite> insert into t1 values (1,1,2);
> >> sqlite> insert into t1 values (1,2,1);
> >> sqlite> insert into t1 values (2,2,1);
> >> sqlite> insert into t1 values (2,1,2);
> >> sqlite> select a,b,c from t1 group by a;
> >> 1|2|1
> >> 2|1|2
> >> sqlite> SELECT
> >>...>a,
> >>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> >>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> >>...> FROM t1 t
> >>...> GROUP BY a;
> >> 1|1|1
> >> 2|1|1
> >> sqlite>
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

[Gerry Snyder]

From the docs:

*INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.

So perhaps you should have said " SQLite integers are all up to 64 bit."

Gerry

On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson  wrote:

> SQLite integers are all 64 bit - I don't about postgress, so unless
> postgress allows integers bigger than 64 bit, and you use them, you should
> be OK with your table definitions above.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 1 May 2018 at 16:29, dmp  wrote:
>
> > Hello,
> >
> > Given a conversion from a database table that contains BigInt, long,
> > field from PostgreSQL to a SQLite similar table.
> >
> > CREATE TABLE postgresqltypes (
> >   data_type_id serial,
> >   bigInt_type bigint)
> >
> > CREATE TABLE sqlitetypes (
> >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   int_type INTEGER)
> >
> > How to store, for values outside range for Integer,
> > String or Real?
> >
> > danap.
> >
> > ___
> > 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] JDBC driver experience

[Gerry Snyder]

1.  I use an old version of xerial with Aejaks for an on-line voting system.
2.  I started around 2010, and updated only once, in order to get the
backup command (greatly simplifying my process).
3.  My only issue is paranoia about breaking something, which keeps me from
doing further updates.
4.  None. After learning how to use it I have had no problems.

Gerry Snyder

On Wed, Apr 18, 2018 at 12:34 PM, Richard Hipp <d...@sqlite.org> wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Good resources for TCL/TK

[Gerry Snyder]

Sorry, I did not notice that your question was about exec rather than expr.

Gerry

On Dec 4, 2017 8:40 AM, "Gerry Snyder" <mesmerizer...@gmail.com> wrote:

> No. One set of braces around the whole list of arguments.
>
> Gerry
>
> On Dec 4, 2017 8:27 AM, "Cecil Westerhof" <cldwester...@gmail.com> wrote:
>
>> 2017-12-04 15:24 GMT+01:00 Gerry Snyder <mesmerizer...@gmail.com>:
>>
>> > It is always a good idea to put the arguments of [expr] in braces. That
>> way
>> > they are byte-compiled.
>> >
>>
>> ​You mean like:
>> exec {swapon} (--noheadings} {--show}
>>
>> --
>> Cecil Westerhof
>> ___
>> 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] Good resources for TCL/TK

[Gerry Snyder]

No. One set of braces around the whole list of arguments.

Gerry

On Dec 4, 2017 8:27 AM, "Cecil Westerhof" <cldwester...@gmail.com> wrote:

> 2017-12-04 15:24 GMT+01:00 Gerry Snyder <mesmerizer...@gmail.com>:
>
> > It is always a good idea to put the arguments of [expr] in braces. That
> way
> > they are byte-compiled.
> >
>
> ​You mean like:
> exec {swapon} (--noheadings} {--show}
>
> --
> Cecil Westerhof
> ___
> 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] Good resources for TCL/TK

[Gerry Snyder]

It is always a good idea to put the arguments of [expr] in braces. That way
they are byte-compiled.

Gerry

On Dec 4, 2017 6:33 AM, "Cecil Westerhof"  wrote:

> 2017-11-19 23:00 GMT+01:00 jungle boogie :
>
> > Thus said Cecil Westerhof on Sat, 18 Nov 2017 14:43:23 +0100
> >
> >> I found the benefits for TCL/TK. But this is a SQLite mailing list, so
> not
> >> the right place to ask questions if it is not connected to SQLite also.
> >> What would be good resources for TCL/TK?
> >>
> >>
> > There's also a pretty active IRC room on freenode, it's #tcl.
> >
> > Let us know how your experiences go with tcl.
>
>
> ​I like it very much. It is a bit get used to, but I will manage I think.
> ;-)
>
> One think I like that global variables are not default exposed in
> procedures.
>
> I wrote something to help me choose which (of my about 30) teas I am going
> to brew. ;-)
>  ​
>
> ​I also wrote a program to store systems statistics in a SQLite database:
> ​#!/usr/bin/env tclsh
>
> ### Improvements
> # Get database from conf-file
>
>
> package require sqlite3
>
>
> proc getCPUTemp {} {
> if {1 != [regexp -all -line {^CPU_TEMP: +\+([0-9.]+)°C } [exec
> sensors] -> temp]} {
> error {Did not get exactly a single temperature line from [exec
> sensors] output}
> }
> return ${temp}
> }
>
> proc storeCPUTemp {} {
> storeMessage cpu-temp [getCPUTemp]
> }
>
> proc storeMessage {type message} {
> db eval "
>   INSERT INTO messages
>   (type, message)
>   VALUES
>   (:type, :message)
> "
> }
>
> proc storeSwap {} {
> storeMessage swap-usage [exec swapon --noheadings --show]
> }
>
> if {$argc != 1} {
> error "Error: ${argv0} DATABASE"
> }
> sqlite db  [lindex $argv 0]
> while {true} {
> after [expr 1000 * (60 - [clock format [clock seconds] -format
> %S])]
> set   currentSeconds [clock seconds]
> db transaction {
> storeCPUTemp
> # At the whole hour we save swap usage
> if {[clock format ${currentSeconds} -format %M] == "00"} {
> storeSwap
> }
> }
> }
> # Not really necessary because the above loop never ends
> # But I find this more clear and is robuster against change
> db close
>
> ​I am open for improvements.​
>
> --
> Cecil Westerhof
> ___
> 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] Many ML emails going to GMail's SPAM

[Gerry Snyder]

I hope that ways are found to keep the email list viable.

If not, my only request is that it should push the messages to me, rather
than making me pull them.

If I had to pull, I might have skipped the thread on inserting date in the
middle and updating the ID column--and I have learned a lot from all the
posts there.

Thank you,

Gerry Snyder (55 years of working with computers and still an ignoramus)

On Tue, Nov 21, 2017 at 7:30 AM, Richard Hipp <d...@sqlite.org> wrote:

> On 11/21/17, Paul Sanderson <sandersonforens...@gmail.com> wrote:
> > Coincidence!  I have just been in my gmail folder marking a load of
> SQLite
> > email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Enforcing uniqueness from multiple indexes

[Gerry Snyder]

Should it be :

CREATE UNIQUE INDEX ...


Gerry Snyder

On Aug 18, 2017 4:47 AM, "Richard Hipp" <d...@sqlite.org> wrote:

> On 8/18/17, Wout Mertens <wout.mert...@gmail.com> wrote:
> >
> > So, bottom line, is there a way to insert or replace a row so that first
> > the id constraint is observed (replacing a previous row with the same
> id),
> > and then the k constraint is verified (failing to replace if k is already
> > present in the table)?
>
>
> CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
> CREATE INDEX demo_k ON demo(k);
> CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
>   SELECT raise(ABORT,'uniqueness constraint failed on k')
>FROM demo WHERE k=new.k;
> END;
>
> The above will force uniqueness on k for INSERT statements.  You'll
> want a second "BEFORE UPDATE" trigger to do similar enforcement for
> UPDATEs if that is an issue for you.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Possible inaccuracy in "Isolation In SQLite" paper

[Gerry Snyder]

"Deferred means that no locks are acquired on the database until the
database is first accessed. Thus with a deferred transaction, the BEGIN
statement itself does nothing to the filesystem. Locks are not acquired
until the first read or write operation."

On Jul 18, 2017 6:10 AM, "Gwendal Roué"  wrote:

> Hello all,
>
> The following sentence in https://www.sqlite.org/isolation.html does not
> exactly describe the behavior of SQLite (since many versions):
>
> > In WAL mode, SQLite exhibits "snapshot isolation". When a read
> transaction starts, that reader continues to see an unchanging "snapshot"
> of the database file as it existed at the moment in time when the read
> transaction started. Any write transactions that commit while the read
> transaction is active are still invisible to the read transaction, because
> the reader is seeing a snapshot of database file from a prior moment in
> time.
>
>
> I'll exhibit the inaccuracy below.
>
> Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not
> only does it grant any read-only connection that opens a deferred
> transaction an immutable and consistent view of the database. But when you
> can guarantee that there is a single writer connection, snapshot isolation
> allows *precise scheduling*, such as blocking the writer connection until a
> reader has established snapshot isolation. With such precision, one can
> exactly control what's available to a reader, while not blocking the writer
> longer than necessary.
>
> And this is where the documentation paragraph starts becoming inaccurate.
> For the simplicity of the argument, I'll execute statements sequentially
> from two connections W and R. To reproduce, just open two shells, and
> execute statements in the following order:
>
> $ sqlite3 /tmp/snapshotisolation.sqlite
> SQLite version 3.16.0 2016-11-04 19:09:39
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
>
> This is unexpected. After connection R has started a deferred transaction,
> it should continue to see an "unchanging snapshot of the database file as
> it existed at the moment in time when the read transaction started".
> Obviously, this is not the case, since the insertion performed by W is
> visible from R even though it has been performed *after* R has started its
> deferred transaction. The "Any write transactions that commit while the
> read transaction is active are still invisible to the read transaction" is
> also flat wrong here.
>
> If we continue, things behave as expected:
>
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
>
> R does not see the new insertion, which means that it indeed lives in an
> unchanging snapshot. It just happens that the snapshot was not established
> when the transaction has started, as documented, but *later*. But when?
>
> After a few experiments, it looks like the snapshot is established on the
> first select:
>
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the
> snapshot
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 0 -- as expected
>
> If now we stop entering commands by hand in the CLI, and start working
> with threads, the *precise scheduling* I mention at the beginning of the
> mail requires to block the writer connection W until the reader connection
> R has started a deferred transaction, and sqlite3_step() has been any
> executed once from any select statement. Only then can the writer
> connection be released, with a absolute control of the content of the
> reader's snapshot.
>
> I'm not sure the behavior I've just described can be called a bug. The
> snapshot is unchanging indeed. When there are several processes connected
> to the database, a reader can't know whether a change has been performed
> before its deferred transaction has started, or between the start of the
> deferred transaction and its first select statement, and therefore should
> not care at all. With this interpretation, there is nothing wrong in the
> current behavior of SQLite.
>
> However, when an application developper is sure that the database has a
> single writer connection, the question of the content of the snapshot
> suddenly becomes relevant. And the documentation becomes inaccurate.
>
> What do SQLite concurrency masters of this mailing list think? Besides
> that threads are evil, I mean ;-) Shouldn't the documentation be updated?
>
> Cheers to all,
> Gwendal
> PS: For the record, I've been talking about "precise scheduling" and
> "absolute control of the content of the snapshot" because they are the
> necessary conditions for some database observation features such as
> reloading fresh values from a request as soon as a transaction has modified
> its content. See https://github.com/RxSwiftCommunity/RxGRDB for some
> 

Re: [sqlite] Bulk load strategy

[Gerry Snyder]

On Wed, May 17, 2017 at 3:52 AM, Joseph L. Casale <jcas...@activenetwerx.com
> wrote:

> I am trying to bulk load about a million records each with ~20 related
> records
> into two tables. I am using WAL journal mode, synchronous is off and
> temp_store
> is memory. The source data is static and the database will only be used as
> a means
> to generate reporting and is not vital. I am deferring index creation to
> after the load.
> The load proceeds along quickly to about 150k records where I encounter
> statements
> which perform modifications to previous entries. The incoming data is
> structured
> this way and has relational dependencies so these modifications spread
> throughout
> affect subsequent inserts.
>
> In a scenario such as this, what is the recommended approach?
>
> Thanks,
> jlc
>


> If the updates pertain just to the 150k rows immediately preceding them,
> could you put each 150k chunk into its own table, and then do a join when
> accessing the data? Or even a merge at that point? Could be a lot faster.
>
> Gerry Snyder
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unlock_notify missing from Tcl interface writeup

[Gerry Snyder]

The Tcl docs have a reference to unlock_notify but the link does not go
anywhere.

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

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[Gerry Snyder]

Would it be possible for you to write the other columns just once, after
all the blob has been inserted?

On Mar 4, 2017 4:34 PM, "Yuri"  wrote:

> On 03/04/2017 01:22, Clemens Ladisch wrote:
>
>> Do you have a concrete example for such a statement?
>>
>
>
> Yes. I need to re-assemble the large data packet that comes in portions. I
> would like to use a blob field for this. I don't want to store parts in
> separate records, because this is essentially one file.
>
> So far sqlite is failing, because the handle returned by
> sqlite3_blob_write is invalidated by the updates to the other fields, and
> sqlite3_blob_open/sqlite3_blob_close are too slow to call them on every
> fragment.
>
> I would like to maintain the record like this:
>
> table file {
>
> id integer,
>
> num_done integer,
>
> num_total integer,
>
> data blob
>
> }
>
> File metadata and data all in one record. Blob handle gets invalidated
> when metadata is updated, and it's too expensive to open/close the blob
> every time.
>
> Due to these two problems sqlite can't support this reasonable, IMO, use
> case.
>
>
> Yuri
>
> ___
> 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] SQLite IN operator on a Tcl list

[Gerry Snyder]

This gets asked once in a while, and the answer is "No." The SQL statements
used in Tcl get compiled, and the compiled statement has to know how many
values are being looked at by IN.

At least up til now. Perhaps having row values will change this. We should
know soon.

Gerry Snyder

On Sun, Sep 25, 2016 at 10:21 AM, Adam Jensen <han...@riseup.net> wrote:

> Would it be very challenging to tweak the SQLite/Tcl code such that
> something like this (below) would work?
>
> ---
> #!/usr/bin/env tclsh8.6
>
> package require sqlite3
> sqlite3 db "test.db"
>
> db eval { CREATE TABLE ex_table (
> ex_id INTEGER NOT NULL,
> ex_name TEXT NOT NULL,
> PRIMARY KEY (ex_id) ) }
>
> db eval { INSERT INTO ex_table (ex_id, ex_name)
> VALUES (1,"alice"),(2,"bob"),(3,"fred"),(4,"jan") }
>
> #v THIS CURRENTLY DOES NOT WORK v#
>
> set my_id_list [list 1 2 4]
>
> set my_name_list [db eval { SELECT ex_name
> FROM ex_table WHERE ex_id IN $my_id_list }]
>
> puts $my_name_list
>
> ##
>
> db close
> file delete "test.db"
> ---
> ___
> 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] SQLite 3.15.0 scheduled for 2016-10-14

[Gerry Snyder]

Just curious--will Tcl lists be usable as row values?

Gerry Snyder

On Thu, Sep 22, 2016 at 12:04 PM, Richard Hipp <d...@sqlite.org> wrote:

> Our current schedule for the next SQLite release (3.15.0) is for
> 2016-10-14.
>
> Your beta-tests are appreciated.  You can get a pre-release snapshot
> from the download page (https://www.sqlite.org/download.html) and you
> can review the change log
> (https://www.sqlite.org/draft/releaselog/3_15_0.html).
>
> The trunk (https://www.sqlite.org/src/timeline?r=trunk) is stable and
> is being used by the SQLite developers for mission-critical processes.
>
> Thank you for your attention.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] UPDATE statement without FROM clause

[Gerry Snyder]

If SQLite implemented the FROM it would just be a translation into the
complex and slow statements you want to avoid.

Gerry Snyder
On Jun 4, 2016 9:19 AM, "skywind mailing lists" <mailingli...@skywind.eu>
wrote:

> Hi,
>
> I am using quite often SQL statements that update the data of one table
> with data from another table. This leads to some quite complex (and slow)
> statements because SQLite3 is not supporting a FROM clause in update
> statements. I am just wondering why the FROM clause is not supported by
> SQLite3?! Is this too complex to implement or is there simply no demand for
> these type of statements?
>
> Regards,
> Hartwig
>
>
>
> ___
> 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] Incremental backup/sync facility?

[Gerry Snyder]

On 5/6/2016 5:32 AM, Stephan Buchert wrote:
> We are using Sqlite for data from satellite Earth observations. It
> works very well. Thanks to everybody contributing to Sqlite, uppermost
> Dr. Hipp.
>
> The largest database file has now grown to about 180 GB.

One feature of SQLite -- the whole database in one file -- is normally 
an advantage but becomes less so when the file is huge.


>   I need to have
> copies of the files at at least two different places. The databases are
> updated regularly as new data from the satellites become available.

Others have suggested keeping track of changes using a logging file. 
That allows all the SELECT statements to remain unchanged, while 
complicating the input side of things.

Another approach could be to have new observations go into a separate 
file (one day's worth, or whatever makes sense). This much smaller file 
could be shipped to the other site(s) and then merged into each copy of 
the main database. Almost no changes to the input logic, but every 
SELECT would have to use a JOIN. No idea how painful  process that would 
be.
>
> Having the copies of the file synced becomes increasingly tedious
> as their sizes increase. Ideal would be some kind of
> incremental backup/sync facility.
>
> I looked at https://www.sqlite.org/backup.html, but, as it does
> a complete backup, this takes a long time for such large
> databases (files are mounted over networks), and other work gets
> blocked.
>
> An alternative is perhaps https://www.sqlite.org/sqldiff.html, but it
> is also slow for large databases. Most of the data are binary, and the
> SQL input/output of sqldiff/sqlite3 is probably not very efficient.
>
> A solution might be to add an extension to the standard VFS
> http://www.sqlite.org/vfs.html. Each page would need a counter which
> increments when the page is modified (and there seems to
> be spare place for such counters). Then the backup could test which
> pages need to be updated. Is there any chance that such an extension
> could be added?
>
> A kind of hack-ish solution might be to update the primary database
> files in WAL mode, copy only the WAL file to the secondary place,
> and force there WAL checkpoint. Would this work?
>
> /Stephan
>
Gerry

[sqlite] Temporary table in SQLite

[Gerry Snyder]

Short answer, Yes.

Long answer, have you read the SQLite documents?

Gerry
On Feb 13, 2016 8:13 PM,  wrote:

> Hi,
>
> Is it possible to create a temporary table in SQLite so that it will only
> be
> visible to the current session and be destroyed after the session is
> closed?
> Just like SQL Server
>
> http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in
> -SQL-Server ?
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Best way to store only date

[Gerry Snyder]

On Jan 30, 2016 6:18 AM, "E.Pasma"  wrote:
>
> The diagram got broken in my email and here is another try:
>
>  Needs to be light | Needs to be| Needs to do  |
>  (small footprint) | Human-Readable | calculations |
>  - | ---|  |
>  YES   | YES| NO   | Integer as
>||  | Igor's suggestion
>||  |
>  YES   | NO | YES  | Float/Int
>||  | Julianday
>||  |
>  NO| YES| YES  | Datetime/Numeric
>||  | ISO Standard
>
> With respect to Igor's suggestion, mmdd (as integer), why not leave
out
> the century? I prefer the oldfashoned yymmdd.

The advantage of the four-digit year is that it can be used for sorting
over a wide range.

Gerry
>
> Thanks, E. Pasma
> 30-01-2016 00:31, R Smith:
>
> >
> > On 2016/01/29 5:23 PM, Igor Tandetnik wrote:
> >>
> >> Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other
> >> words, storing calendar dates as integers like 20160129.
> >
> > The main advantage of this format is that it is of course
> > human-readable, even as an integer.
> > The important disadvantage is that you cannot do date calculations
> > without first casting and translating - something the Julian day or more
> > expensive 19-char ISO format (-MM-DD HH:NN:SS which is
> > human-readable AND in most systems calculatable) is better at.
> >
> > My point being: when I decide which date format to use, I first try to
> > establish whether I will use it for calculations or simply record/log
> > purposes, and if readability (from data source) would be needed/helpful
> > or not. The decision matrix ends up something like this:
> >
> >
> > Needs to be light (small footprint)| Needs to be Human-Readable
> > | Needs to do calculations   |
> > -- | --
> > | -- |
--
> > YES  | YES |
> > NO | Integer (as Igor's suggestion)
> > YES  |NO |
> > YES|  Float/Int Julianday
> > NO   | YES |
> > YES| Datetime/Numeric ISO Standard
> > -- | --
> > | -- |
--
> >
> > If you can say "No" to two of these criteria, go for the most efficient.
> >
> > If you can say "No" to all three criteria, perhaps reconsider whether
> > you really need that column in your table.
> >
> >
> > Cheers,
> > Ryan
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 file as database

[Gerry Snyder]

Have you tried simply renaming the file?
On Sep 12, 2015 8:29 AM,  wrote:

> Hello dears
> I have a database file as database.db3 but I have to use database.sqlite3 .
> How can I convert the db3 file to sqlite3 file?
> Thanks for your help
> sanam
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Command line sqlite3 program bug

[Gerry Snyder]

The Command Line Interface has the command:

.bail on

which will do what you want.

HTH,

Gerry Snyder
---
On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> After seem several emails from a user asking about how to use sqlite3 through
> shell scripts, I remember my experiences with sqlite3 but didn't mind to
> report it, but now I think that it's worth mention it because it'll hurt
> several users.
>
> The bug/problem is that the sqlite3 command line when feed with a sql script
> with commands wrapped by a transaction if there is any error in the middle of
> it sqlite3 reports the error but do not stop/abort the transaction and the
> database end up in a dirty state.
>
> __example to show the bug/problem
>
> BEGIN;
>
> DROP TABLE IF EXISTS a; --to allow run more than once
>
> CREATE TABLE a(b); --after the next line error this should be rolled back
>
> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>
> INSERT INTO a(b) values(4);
>
> COMMIT;
>
> __
>
> __blank database after been feed by the above sql script
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE a(b);
> INSERT INTO "a" VALUES(4);
> COMMIT;
>
> __
>
> Cheers !
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Safe use of custom collations that are not available in all tools

[Gerry Snyder]

The problem is that I want to make the data available to folks who do 
not have my  application (emergency-only sort of thing--my Tcl-based GUI 
works well for me, but is probably not ready for prime time). If 
something happens to me I want the data in the file to be recoverable. 
Losing the collation is fine, losing the table data is not.

Thank you for the suggestion, though. It got me thinking along the lines 
of making a starpack (Tcl executable plus my s/w in a single file) 
available, which may be the way to go if there does not turn out to be a 
safe way of handling missing collations.

Gerry

On 3/17/2015 11:15 AM, Hick Gunter wrote:
> How about coding the collation in C and statically linking it into the SQLite 
> library you provide with your application?
>
> -Urspr?ngliche Nachricht-
> Von: Gerry Snyder [mailto:mesmerizerfan at gmail.com]
> Gesendet: Dienstag, 17. M?rz 2015 18:02
> An: 'General Discussion of SQLite Database'
> Betreff: [sqlite] Safe use of custom collations that are not available in all 
> tools
>
> In one of my tables I need a collation that is an extension of NOCASE (it 
> ignores spaces and punctuation, translates a very limited set of accented 
> characters into their unaccented forms). I wrote it in Tcl, and it works well 
> in my tools.
>
> At first I used the collation only in SELECT statements, but more than once I 
> failed to specify it and did not get the desired ordering (of course). So, I 
> put the collation in the column definition in the table definition. This 
> works perfectly in my s/w, but other tools complain about a missing collation.
>
> So, two questions:
>
> 1) Any advice (other than take the collation out of the table definition and 
> always code carefully)?
>
> 2) How does SQLite handle missing collations? Are there any things that are 
> safe to do using tools that do not have the collation? (For instance, is 
> read-only access ok? It seems to be, and using DB Browser for SQLite gives a 
> warning but then handles the table correctly, including ordering by the 
> column with the missing collation. The Command Line Shell refuses to do 
> anything with the table except .dump it.)
>
> All suggestions welcome.
>
> Gerry Snyder
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Safe use of custom collations that are not available in all tools

[Gerry Snyder]

In one of my tables I need a collation that is an extension of NOCASE 
(it ignores spaces and punctuation, translates a very limited set of 
accented characters into their unaccented forms). I wrote it in Tcl, and 
it works well in my tools.

At first I used the collation only in SELECT statements, but more than 
once I failed to specify it and did not get the desired ordering (of 
course). So, I put the collation in the column definition in the table 
definition. This works perfectly in my s/w, but other tools complain 
about a missing collation.

So, two questions:

1) Any advice (other than take the collation out of the table definition 
and always code carefully)?

2) How does SQLite handle missing collations? Are there any things that 
are safe to do using tools that do not have the collation? (For 
instance, is read-only access ok? It seems to be, and using DB Browser 
for SQLite gives a warning but then handles the table correctly, 
including ordering by the column with the missing collation. The Command 
Line Shell refuses to do anything with the table except .dump it.)

All suggestions welcome.

Gerry Snyder

Re: [sqlite] Whish List for 2015

[Gerry Snyder]

The only thing on my SQLite wish list is for the development team to 
have a meaningful holiday season and a happy, healthy, and productive 
new year. The details of the "productive" part I leave in their capable 
hands.


What has been added each year has far surpassed my expectations, and I 
have no worries about that trend continuing.


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

Re: [sqlite] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)

[Gerry Snyder]

Some further comments on VIEWs:

A regular (non TEMPORARY) VIEW can reference only tables in the file 
where it is stored. SQLite allows qualified names, but the practice 
should be avoided because the VIEW won't work if the file is attached 
under a different name.


A TEMPORARY VIEW can reference tables in any attached file, and the use 
of qualified names is allowed and encouraged (at least by me).


Gerry
-

On 10/28/2014 12:24 AM, Hick Gunter wrote:

Indices, foreign keys,  ... all work only within a single DB file, so allowing 
a qualifier would suggest functionality that is not present and probably quite 
hard to provide.

How would one keep an index residing in one DB file consistent with a table in 
a different file if only one of the files is attached?

In these cases, the qualification is implied. NB: IIRC this also applies to 
views, but seems not to be prominently documented.

Eg.

CREATE INDEX db.index_name ON [=>db.]table_name ...

CREATE TABLE db.table_name (...) FOREIGN KEY (...) REFERENCES 
[=>db.]referenced_table

CREATE TRIGGER db.trigger_name ... ON [=>db.]table_name ...

CREATE VIEW db.view_name AS SELECT ... FROM [=>db.]table_name ...




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

Re: [sqlite] unicode case insensitive

[Gerry Snyder]

By "brute force" I just meant specifying each conversion (such as Ö to 
ö) individually. In my Tcl code, it is done with a single [string map 
...] statement containing all of the conversions. The down side being, 
as I mentioned earlier, that each time I run it on a new set of data I 
have to check that a new accented character has not been added.


Gerry

On 10/24/2014 10:44 AM, dd wrote:

Hi,

   Any sample/open source avail to custom collation. Will it work for like
queries. Any performance degradation?


Convert everything to upper (or lower) case brute force.

Sorry. I am not clear. Can you please elaborate this.

Thanks.

On Fri, Oct 24, 2014 at 9:16 PM, Gerry Snyder <mesmerizer...@gmail.com>
wrote:


In a vaguely similar situation I wrote a custom collation that converted
accented letters to their non-accented cousins. Since the conversion is on
a case-by-case basis I also had to do a pre-screening that would show any
non-ascii characters that I wasn't converting, so that I could add them to
my collation.

This is not quite what you want, since (I think) you want O and Ö and Ó to
be distinct, but the same sort of technique should work for you. Convert
everything to upper (or lower) case brute force. Perhaps tedious to set up,
but straightforward.

Gerry






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

Re: [sqlite] unicode case insensitive

[Gerry Snyder]

In a vaguely similar situation I wrote a custom collation that converted 
accented letters to their non-accented cousins. Since the conversion is 
on a case-by-case basis I also had to do a pre-screening that would show 
any non-ascii characters that I wasn't converting, so that I could add 
them to my collation.


This is not quite what you want, since (I think) you want O and Ö and Ó 
to be distinct, but the same sort of technique should work for you. 
Convert everything to upper (or lower) case brute force. Perhaps tedious 
to set up, but straightforward.


Gerry


On 10/24/2014 9:54 AM, dd wrote:

Hi,

ö and Ö same character but case different. I dont want to allow to insert
two entries for same data with different case. It works well with ascii
set. How to handle this? any inputs welcome.

$./sqlite3 '/home//sqlite/test/a.db'
SQLite version 3.8.7 2014-10-17 11:24:17
Enter ".help" for usage hints.
sqlite> .fullschema
CREATE TABLE test(id integer primary key autoincrement, t text collate
nocase, unique(t));
/* No STAT tables available */
sqlite> insert into test(t) values('a');
sqlite> insert into test(t) values('A');
Error: UNIQUE constraint failed: test.t
sqlite> .headers on
sqlite> select * from test;
id|t
1|a
sqlite> insert into test(t) values('ö');
sqlite> insert into test(t) values('Ö');//issue: allowed to insert. Expects
constraint failed err. But, not.
sqlite> select * from test;
id|t
1|a
2|ö
3|Ö
sqlite> .q

Thanks,
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] Any tips on reducing memory requirements for small MCU?

[Gerry Snyder]

One possibility might be to use the long-obsolete SQLite2, which was 
around when PC's had much smaller memories.


I know it is heresy to suggest it, and you would have a lot of recoding 
to do, but it seems that it might be workable.


Gerry Snyder
---
On 10/20/2014 2:21 PM, Dennis Field wrote:

I have SQLite compiled for a Cortex M4 with 256 KB of RAM. Currently, as
other things on the system are taking up a grand total of about 190 KB

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

Re: [sqlite] sqlite max arguments assistance

[Gerry Snyder]

I feel sure the transaction amounts are strings, not numbers. Here is a 
quick example:


create temp table gigo(a real)
insert into gigo values ('$5.00')
select a, typeof(a) from gigo

gives:

$5.00  text

If you can remove the dollar signs in the CSV file you should do better.

Hope this helps,

Gerry

On 9/22/2014 12:12 PM, Jungle Boogie wrote:

Hello All,

select * from august where transaction_amount = (select
max(transaction_amount) from august)

This statement should show be the merchant account with the top most expensive
transaction from my table called august.

Result:
$999.63
(I trimmed out other items that I can't show).

Same results with this: select max(transaction_amount) from august
$999.63


But this is NOT the most expensive amount, but it is for a three digit dollar
amount.


For example, this record is much higher in terms of transaction_amount:
$16695.36

This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
3.8.6 as well as
FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep 15
14:32:29 UTC 2014
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  i386
also with sqlite3.8.6


How am i misunderstanding max?


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

Re: [sqlite] Handling Timezones

[Gerry Snyder]

Have you read http://sqlite.org/lang_datefunc.html   ?

On 7/29/2014 6:41 AM, Will Fong wrote:

Hi,

How are timezones best handled? Since dates are stored in GMT, when I
go to display them, I need to add/subtract the timezone. That's not
too hard when I can just store the timezone as "-5" for EST. When I'm
providing a date to query on, I would have to apply the reverse of the
timezone, "+5", to normalize it to GMT.

That kinda sucks :(

I come from PostgreSQL, so I normally set at the connection level the
timezone and PG handles all the conversions. Does SQLite have a
similar feature?

Is there a standard way to handle this?

Thanks,
-will
___
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] Hexadecimal integer literals

[Gerry Snyder]

I can think of situations where I would want the result to be truncated 
to 64 bits.


I can think of situations where I would want SQLite to raise an error.

I cannot imagine wanting a floating point result.

Gerry Snyder
-
On 7/23/2014 4:07 AM, Richard Hipp wrote:

We are looking into adding hexadecimal integer literals to SQLite.  In
other words, we are looking to enhance SQLite to understand 0x1234 as
another way of writing 4660.  Hex literals are useful in conjunction with
the bit-wise AND and OR operators (& and |) and in applications that make
use of bit fields.

The question is what to do with hex literals that are larger than 64 bits.



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

Re: [sqlite] BLOBs and NULLs

[Gerry Snyder]

On 4/23/2014 10:21 AM, Drago, William @ MWG - NARDAEAST wrote:

 If I was sure I wouldn't be merging data I might use timer ticks as my ID, 
but I'm not sure and I can't take the chance.

-Bill


Would it be possible to use INTEGER PRIMARY KEY AUTOINCREMENT for the 
ID, and manually start each test station at an ID value a billion larger 
than the previous one? Or whatever delta makes sense? Then collision 
could never happen. And, as a possible bonus, the ID would indicate 
which station the row came from.



Gerry

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

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

[Gerry Snyder]

On 4/17/2014 12:43 AM, Tyumentsev Alexander wrote:

sqlite in some cases dumps views in wrong order.



Interesting situation. My take on it would be that dump is a simple 
little tool designed to help move a database file from one place to 
another. In tricky situations, some editing of its output may be needed.


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

Re: [sqlite] SQLite sorting/searching algorithm

[Gerry Snyder]

Look at COLLATE in the ORDER BY clause.

Gerry
On Mar 25, 2014 11:32 AM, "michal.pilszak"  wrote:

> "you cannot tell SQLite to use a particular algorithm to do those things"
> that's exactly what I wanted to know. So, I won't waste my time on looking
> for impossible on Internet. Thank you for this answear. And thank you for
> suggestion with ANALYZE command. This maybe be useful. I'll try to get
> familiar with it and use it when/if I'll have some time.
> Dnia 25 marca 2014 18:55 Simon Slavin slav...@bigfraud.org
> napisał(a):
> On 25 Mar 2014, at 5:48pm, michal.pilszak michal.pils...@o2.pl
> wrote:
>  Is there any parameter I can set to select another algorithm (e.g.
> another algorithm of ORDER BY) and check its efficiency?
> You can tell SQLite to search for different rows or order them in a
> different order. But you cannot tell SQLite to use a particular algorithm
> to do those things. SQLite decides how to obey your instructions itself.
> The best thing you can do is to CREATE an INDEX ideally suited to your
> SELECT command.
> If your database is large and you are concerned about how long your
> operations may take you might like to execute the ANALYZE command after you
> have INSERTed your data in the database and CREATEd your INDEX(es):
> http://www.sqlite.org/lang_analyze.html;
> Hope this helps.
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> --
> Pozdrawiam, MP.
> ___
> 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] Documentation suggestion

[Gerry Snyder]

There is no mention in the write-up of "PRAGMA table_info"  that it 
works for a VIEW as well as for a TABLE.


It takes only a few seconds to verify this, but saving others the 
trouble of doing so seems like a good idea to me.


Thank you,

Gerry Snyder



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

Re: [sqlite] Free Page Data usage

[Gerry Snyder]

Instead of delete and then insert, can you somehow just keep track of which
rows are to be deleted, and when new rows come in replace if you can and
otherwise insert?

A little more bookkeeping, but it might save the space you need.

Gerry
On Feb 7, 2014 10:57 PM, "Raheel Gupta"  wrote:

> Hi,
>
> Sir, the 32 TB size is not always going to be reached.
> The Database is going to be used to store blocks of a Block Device like
> /dev/sda1
> The size can reach 3-4 TB easily and would start from atleast 20-100 GB. 32
> TB of data though impractical as of today will be possible in 2-3 years.
> The issue happens when I delete the rows and new rows are inserted at the
> end of the database the size of the database exceeds that of the actual
> block device size even though many pages are having free space.
> Hence I am simply trying to optimize the utilization of the free space
> available.
>
> I would have loved to use the page size of 2KB which would give me a
> practical size of 4TB. But that would have this hard limit of 4TB.
> So I have two possible options which I am trying to help me solve this
> issue :
> 1) Either make the page size to 2KB and increase the maximum page count to
> 2^64 which will be more than sufficient.
> 2) Improve the free space utilization of each page when the page size is
> 64KB.
>
> I hope this makes sense.
>
>
>
> On Sat, Feb 8, 2014 at 12:54 AM, RSmith  wrote:
>
> > A database that is geared for 32TB size and you are concerned about
> rather
> > insignificant space wasted by the page size that is needed to reach the
> > 32TB max size... does not make any sense unless you are simply paranoid
> > about space.  Removing the gaps in the table space when deleting a row
> (or
> > rows) will render a delete query several magnitudes slower.
> >
> > If it IS that big of a concern, then maybe use standard files rather than
> > SQLite to save data in?  If the SQL functionality is a must, you can use
> > vacuum as often as is needed to clear unused space - but beware, 1 -
> Vacuum
> > takes some processing to re-pack a DB, especially a near 32TB one... in
> the
> > order of minutes on a computer I would guess, and much much more on
> > anything else.  2 - a 32TB DB will need up to 64TB total free disk space
> to
> > be sure to vacuum correctly - so having issues with it taking up maybe
> 40TB
> > for 32TB of data is in itself an irrelevant concern. Even large queries,
> > temporary tables etc will all need additional interim space for the sorts
> > of queries that might be requested of a 32TB data-set.
> >
> > The real point being: if you do not have at least 64TB free on whatever
> > that 32TB DB will sit, you are doing it wrong, and if you do have that
> much
> > free, you can ignore the 25% wasted deletion space problem.
> >
> > If the problem is simply your own pedanticism (at least I can sympathise
> > with that!) then it's simply a case of "Welcome to efficient databasing",
> > but if it is a real space deficit, then I'm afraid you will have to
> re-plan
> > or reconsider either the max allowable DB, or the physical layer's space
> > availability - sorry.
> >
> >
> >
> > On 2014/02/07 20:35, Raheel Gupta wrote:
> >
> >> Hi,
> >>
> >> I use a page size of 64 KB. But my row consists of 2 columns that is :
> >> i - Auto Increment Integer,
> >> b - 4096 Bytes of BLOB data
> >>
> >> Now for the sake of calculation, lets say 16 rows fit in a page and my
> >> table has 1 rows when I start.
> >>
> >> Now, lets say I delete some data which is not in sequence i.e. it can be
> >> deleted as per data which is not in use. To create such a hypothetical
> >> situation for explaining this to you, here is a simple query :
> >> DELETE from TABLE where i%4 = 0;
> >>
> >> As you may see that there is now 25% data deleted in each page.
> >>
> >> Now even if I do insert another 2500 rows (25% of original size) my
> >> database size reaches 125% of the original size when I inserted the
> 1
> >> rows initially.
> >>
> >> Hence there is significant space wastage. Anyway i can improve that ?
> >> It would be nice if the database size would be close to the original
> size
> >> after deleting 25% and adding some new 25% data.
> >>
> >> I know you would recommend to use smaller page sizes. Ideally 2KP page
> >> size
> >> is good but then, the number of pages is restricted to a max of 2^32
> which
> >> will restrict the total database size to 4TB only. I need the max size
> to
> >> be capable of atleast 32TB.
> >>
> >>
> >>
> >> On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs 
> >> wrote:
> >>
> >>  Can you write more about how this is causing you a problem? Most users
> >>> don't experience this as a problem
> >>> On Feb 7, 2014 10:30 AM, "Raheel Gupta"  wrote:
> >>>
> >>>  SQLite's tables are B-trees, sorted by the rowid.  Your new data will
> > probably get an autoincremented rowid, which will be appended at the
> >
> 

Re: [sqlite] Boolean and DataReader

[Gerry Snyder]

On 1/29/2014 1:08 PM, Johnny wrote:

Sorry, I have again a question about reading a sqlite db from c#.
Suppose you have a simple Boolean column in a table.
I want to use a .net DataReader (connected layer).
My question is:
Why I get a cast exception when calling the getBoolean method (solution A)?
Casting the DataReader to Boolean (solution B) works perfectly.
A) Boolean my_bool = DR.getBoolean(...
B) Boolean my_bool = (Boolean)DR[...
Obviously I can choose B but I would prefer the A programming style.

SQLite does not have a Boolean data type, so it stores the values as an 
integer or a string.

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

Re: [sqlite] Performing Multiple SQL Statements Within A Transactions Using TCL

[Gerry Snyder]

On 10/10/2013 2:14 PM, Tilsley, Jerry M. wrote:

All,

Does anybody have any examples of wrapping multiple SQL insert/update 
statements in a transaction using the TCL API?



Sure:

db transaction {
db eval {create table if not exists s2011.tclcode(procname text, 
version text, tcl text, comments text, unique(procname, version))}

db eval {delete from s2011.tclcode}
foreach table $tablelist {
set shorttablename [lindex [split $table .] 1]
set tc [db eval "select tcl, comments from $table limit 1"]
set t [lindex $tc 0]
set c [lindex $tc 1]
db eval "insert into s2011.tclcode(procname, version, tcl, 
comments)  values(:shorttablename,:::GEB::defaultversion,:t,:c)"

}
}


HTH,

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

Re: [sqlite] multiple connection to the same DB

[Gerry Snyder]

My track record of giving helpful advice here is not great, but this 
does not sound like an NFS problem to me.


Your example made it look like everyone is logging into the server, and 
running the SQLite executable located there, rather than running an 
executable located on their own machine and all accessing the db file on 
the server. These are very different things.


If I am right, then something else is happening, such as somehow using 
different files, or something else deleting or otherwise modifying the 
file between accesses.


HTH,

Gerry

On 9/22/2013 8:34 AM, olivier Ménard wrote:

The os in use is Linux Ubuntu, so i suppose the protocol NFS too.

If i have well understood :
in theory, multiple access should work with sqlite, but in practice, it doesn't 
because of the os : the mechanism of locking doesn't work ?

I supose it won't change anything but Is it possible to improve the mechanism 
If everyone writes an explicit
BEGIN IMMEDIATE (or EXCLUSIVE ?)
INSERT ...
END

Thanks for all the answers.




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

Re: [sqlite] SQLite clusters?

[Gerry Snyder]

On 9/17/2013 8:51 AM, Tony Papadimitriou wrote:

A "temp" view, however, can access table from different DBs.

-


Thank you.

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

Re: [sqlite] SQLite clusters?

[Gerry Snyder]

On 9/17/2013 6:24 AM, Simon Slavin wrote:


Just a quick couple of things you didn't mention that might help.  You probably 
already know about them but you mentioned ATTACH and didn't mention them so I 
thought I might niggle you.

First, look into VIEWs.  You can save any SELECT as a VIEW, then consult it 
like you would a table.  So if you have split your data up in separate tables, 
and even separate databases using ATTACH, you can reunite it by defining a VIEW 
that includes one or more JOINs.


Hmmm, I don't think this is correct.

I was under the impression that a view is limited to the tables in the 
db file where it resides, and have received error messages whenever I 
tried to access another file's tables.



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

Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Gerry Snyder]

O'Reilly just started a 50%-off sale on all ebooks (60% on orders of $100
or more), good through Sept. 10. A good chance to pick up these or anything
else, SQL-oriented or otherwise.

The discount code* *is B2S3 (but is also shown on the website).


Gerry
*
*


On Thu, Jul 18, 2013 at 5:31 AM, Richard Hipp  wrote:

> On Tue, Jul 9, 2013 at 9:27 PM, Jay A. Kreibich  wrote:
>
> >
> >   If you want to learn more about the theory and concepts behind SQL, I
> >   would strongly recommend these two books:
> >
> > SQL and Relational Theory (2nd Ed) by C.J. Date
> > http://shop.oreilly.com/product/0636920022879.do
> >
> > Relational Theory for Computer Professionals by C.J. Date
> > http://shop.oreilly.com/product/0636920029649.do
> >
>
> O'Reilly is running a half-price sale on the latter book, today only.  ($16
> instead of the usual $32.)  Use the discount code "DEAL" to claim the
> reduced price.
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Gerry Snyder]

On 7/15/2013 1:18 PM, Bernd wrote:
 I'm reading that text out of an Oracle-DB into a SQLite table 
which has the affected column defined as 'String' - which maps to TEXT 
in native SQLite


No. Look at section 2.1 of http://sqlite.org/datatype3.html   Only CHAR, 
CLOB, or TEXT cause the column to have TEXT affinity.


HTH,

Gerry Snyder


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

Re: [sqlite] SQLite database on Dropbox, Google Drive, MS SkyDrive, Ubuntu One or SAMBA share

[Gerry Snyder]

On 6/27/2013 12:38 PM, joe.fis...@tanguaylab.com wrote:

Anyone,

Does anyone have good or bad experiences using a SQLite database in a 
shared folder?

The 'Dropbox / Drive / SkyDrive / One' 


I use Dropbox for SQLite files a lot. If I am not careful to be making 
changes to a file on only one PC at a time, I can get a "Conflicted 
copy" on one of the machines, but by and large it has worked very well.


I can not think of any way Dropbox could be handling things better.


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

[sqlite] configure file is missing from sqlite-autoconf-3071600/tea directory

[Gerry Snyder]

Probably why the file is a bit smaller than other recent ones.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the easiest way of changing the field type?

[Gerry Snyder]

On 12/2/2012 1:52 PM, Igor Korot wrote:


So, does this mean that I need to drop the DB in the text file, edit
it and then re-create
it from this file?



How to make alterations to a table that can not be done with the ALTER 
TABLE command is outlined in topic 11 of the FAQ:


http://sqlite.org/faq.html#q11

You just have to make a change to a column definition rather than add or 
drop columns, but the idea is the same.


HTH,

Gerry


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

Re: [sqlite] Status analyze with Sqlite

[Gerry Snyder]

On 11/22/2012 4:47 PM, Steffen Mangold wrote:

HI sqlite community,

I have a problem I get stucked, maybe someone can help me. :(

My issue:

For instance if we have 10 rows with following data

ID  | TimeStamp | Status

0   | 2012-07-24 22:23:00   | status1
1   | 2012-07-24 22:23:05   | status1
2   | 2012-07-24 22:23:10   | status2
3   | 2012-07-24 22:23:16   | status2
4   | 2012-07-24 22:23:21   | status2
5   | 2012-07-24 22:23:26   | status2
6   | 2012-07-24 22:23:32   | status2
7   | 2012-07-24 22:23:37   | status3
8   | 2012-07-24 22:23:42   | status3
9   | 2012-07-24 22:23:47   | status3

What I want as result is
ID  | Begin | End   | Status
---
0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3



Hmmm, the ID in the result bears virtually no relation to the ID in the 
data. Is that intentional?


Anyhow, some of what you want could come from

select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data 
group by Status order by Status



HTH,

Gerry


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

Re: [sqlite] DELETE Query Assistance Please

[Gerry Snyder]

On 9/24/2012 9:25 AM, Don Goyette wrote:



So, I still need to know how to convert the Excel format timestamp (Days
since 1900-01-01) into a Unix Epoch format timestamp (Seconds since
1970-01-01).



I agree with Bart's reply, but to convert epochs, subtract the Excel 
format timestamp of
1970-01-01 (easily found in excel), and to convert units multiply the 
result by seconds per day (86400).

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

Re: [sqlite] Count(*) help

[Gerry Snyder]

On 9/16/2012 9:17 AM, John Clegg wrote:

I have a table Members with 896 rows and a text field "Year2012". It
contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
in sqlitebrowser as "empty")

Back in the olden days when this table was in Access, select
count("Year2013") from Members used to return 169. In LibreOfiice with the
data stored in embedded HSQL it returns 169. In LibreOffice connecting to
sqlite3 it returns 896.

Any ideas please?


Read the documentation?   http://sqlite.org/lang_aggfunc.html

"The count(X) function returns a count of the number of times that /X/ 
is not NULL in a group.
The count(*) function (with no arguments) returns the total number of 
rows in the group. "



HTH,

Gerry

PS  Note that it is count(X) and not count("X")
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tcl binaries for SQLite 3.7.14, Win & Linux

[Gerry Snyder]

Hello, all,

I like to keep up with SQLite deliveries, and have started compiling the 
Tcl bindings for Win 32 and Linux 32.


If anyone would like them, they are available at:

https://www.dropbox.com/sh/1ropl1g9xsif1ci/Vc2l-zUEgp

Both binaries were compiled with ./configure, make, and strip.

The Linux file was renamed to match the Win one (except .so instead of 
.dll) so that my cross-platform tclkit can use it.


I have run both minimally, so "they work for me," but YMMV.

No guarantee, warrantee, or promises.


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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Gerry Snyder]

The file name does not necessarily have anything to do with the table name.
On May 7, 2012 2:25 PM, "peter korinis"  wrote:

> Simon
>
> I searched the entire disk for the table name and no matches.
>
>
>
> pk
>
>
>
> ___
> 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] Concurrency

[Gerry Snyder]

On 4/30/2012 5:10 PM, Sean Cui wrote:

Here is a simple scenario to explain what the OP meant:

Under WAL mode,

In connection A, we issue SQL "UPDATE Employee SET Salary=0" to SQLite.

While the command is executing, from another connection, we issue "SELECT Salary 
FROM Employee".

In this case, even under WAL, the SELECT command will still be blocked by the 
UPDATE command, right?



The document referred to several emails ago in this thread states:

The WAL approach inverts this. The original content is preserved in the 
database file and the changes are appended into a separate WAL file. A 
COMMIT  occurs when a 
special record indicating a commit is appended to the WAL. Thus a COMMIT 
can happen without ever writing to the original database, which _allows 
readers to continue operating from the original unaltered database while 
changes are simultaneously being committed into the WAL_. Multiple 
transactions can be appended to the end of a single WAL file.


(Emphasis mine)

Otherwise it would not be very concurrent, would it?

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

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Gerry Snyder]

At worst you could use another table to keep track of the maximum and
minimum, and update it with triggers when something is added to or deleted
from the virtual table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem compiling Tcl bindings on Win

[Gerry Snyder]

I thought it might be nice to be able to keep my Tcl bindings for SQLite 
up to date, so I downloaded the autoconf tarball on both my linux and 
Win (Vista) machines. Not surprisingly, the compile went fine on linux.


On Windows I also downloaded and installed MSYS / MINGW.

I got into the tea directory and ran configure, which ran to completion 
with only one warning " WARNING:  'Makefile.in' seems to ignore the 
--datarootdir setting"


In running make, there were a few warnings and then:

gcc -shared -o sqlite3711.dll tclsqlite3.o  "/c/Tcl/lib/tclstub85.lib"
tclsqlite3.o:tclsqlite3.c:(.text+0x4883): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48a0): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48b9): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48d2): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48e0): undefined reference to 
`tclStubsPtr'
tclsqlite3.o:tclsqlite3.c:(.text+0x48f1): more undefined references to 
`tclStubs Ptr' follow
tclsqlite3.o:tclsqlite3.c:(.text+0x6da54): undefined reference to 
`Tcl_InitStubs

...
collect2: ld returned 1 exit status
make: *** [sqlite3711.dll] Error 1

The "/c/Tcl/lib/tclstub85.lib" points to the Active State 8.5.11 Tcl I use.

Is my problem due to omitted steps, wrong steps, or configuration?

TIA,

Gerry



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

Re: [sqlite] replacing several nested queries and UNION ALLs with one query

[Gerry Snyder]

a2 != '' seems redundant when a1 = a2 and a1 != ''
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Gerry Snyder]

On 2/6/2012 9:22 AM, Bill McCormick wrote:
Sorry, I should have mentioned that I did see that, but it doesn't 
quite fit my application. I need a script that doesn't care what the 
existing table looks like. In my situation, I may have dozens of 
databases among different locations, perhaps not all at the same 
revision level. The script I need would be able to bring each up to 
the current revision.


So, if I had a fist step:

CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;

and then
DROP TABLE t1;

and then add the table with it's latest schema revision
CREATE TABLE t1( ... );

It seems difficult to get the saved data back in ...
INSERT INTO t1 SELECT * FROM t1_backup;

... without know what the previous schema looks like. It complains 
like this:

Error: table prod has 27 columns but 25 values were supplied


Yes, the INSERT statement has to specify all of the original column 
names in the proper order.


I wrote a general ALTER TABLE code in Tcl, and it is one of the largest 
functions in the system. It includes moving columns within a table, 
since I agree some times a simple spreadsheet-like display is useful.


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

Re: [sqlite] ALTER TABLE

[Gerry Snyder]

On 2/6/2012 8:36 AM, Bill McCormick wrote:
Is there no way to force columns added to a table with alter table to 
be added at certain column positions?


Alternatively, if there is some way to save the data in an existing 
table; drop the table; re-create the table with the desired schema; 
and then reload the data, this would be useful as well. However, I 
cannot see how to do this simply.




A very quick search at the SQLite website (hint, hint) found:

http://www.sqlite.org/faq.html#q11


HTH,

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

Re: [sqlite] No error on selecting non-grouped column

[Gerry Snyder]

Would be pragma to reverse unordered selects show a different result?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Gerry Snyder]

On 9/13/2011 8:38 AM, Tim Streater wrote:

I don't see an easy way of solving conflicting absids.
The hard way of solving this is to select all the fields of the messages table 
explicitly (except absid), so I can then insert them into a new row in the 
destination table. But I'm trying to avoid this as a maintenance headache (I 
may wish to change the schema for messages from time to time).


The explicit column selection sure seems like the right way of solving 
it to me. What you are doing to avoid it is (obviously from the 
discussion) difficult and error-prone.


Yes, you will have to change the statement when the schema changes. I 
think of that as a task, not a headache.



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

Re: [sqlite] ISNULL in sqlite

[Gerry Snyder]

On 6/25/2011 12:33 PM, logan...@gmail.com wrote:
> Hello,
>
> How do I check for a null or empty string in SQLite.

In addition to the other replies you have received, you need to be made 
aware that an empty string and a NULL are very different, and (perhaps) 
both have to be checked for, depending on how the data gor into the table.


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

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Gerry Snyder]

If this has already been suggested, I apologize.

Add an integer column with a UNIQUE ON CONFLICT REPLACE constraint.Then 
after you figure out how many entries are enough (maxcount), insert each 
row, specifying that column as mod((lastinsertrowid()+1),maxcount) or 
however you specify a modulus or remainder. That column will just wrap 
around when it hits maxcount; and you extract based on rowid, not that 
column to keep things in proper order.

And don't worry about maxing out on rowid.

HTH,

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

Re: [sqlite] ADV: "Using SQLite" ebook, 50% off today

[Gerry Snyder]

I learned a lot from it, too.

Gerry Snyder

On 5/3/11, Nico Williams <n...@cryptonector.com> wrote:
> On Tue, May 3, 2011 at 11:20 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>> On 3 May 2011, at 5:09pm, Jay A. Kreibich wrote:
>>
>>>  "Using SQLite" is today's "Ebook Deal of the Day" over at O'Reilly
>>>  Media.  Today only (Tuesday, May 3rd) the ebook is 50% off, at
>>>  $15.99.
>>
>> Well I don't know, Jay.  Have you read it ?  Is it any good ?
>>
>> For those who don't know, Jay A. Kreibich is in fact the author of said
>> book.  I can't tell you if it's any good, because I'm not the target
>> audience, but you can read a /lot/ of the content from the link he
>> provided and try before you buy.
>
> I use it as a reference, along with the docs at sqlite.org.  It's quite
> good.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Gerry Snyder]

Do money values really get multiplied together?

What is the meaning of square cents as a unit?

Gerry

On 3/26/11, Patrick Earl  wrote:
> That is true, but then when you are formulating generic queries within
> a place such as an ORM like NHibernate, you would need to figure out
> when to translate the user's "100" into "1".  As well, if you
> multiplied numbers, you'd need to re-scale the result.  For example,
> (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(  If one
> wanted to get excessively complicated, they could implement a series
> of user functions that perform decimal operations using strings and
> then reformulate queries to replace + with decimal_add(x,y).  That
> said, it'd be so much nicer if there was just native support for
> base-10 numbers. :)
>
>Patrick Earl
>
> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare 
> wrote:
>> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
>>
>>> Base-10 numbers are frequently used in financial calculations because
>>> of their exact nature.  SQLite forces us to store decimal numbers as
>>> text to ensure precision is not lost.  Unfortunately, this prevents
>>> even simple operations such as retrieving all rows where an employee's
>>> salary is greater than '100' (coded as a string since decimal types
>>> are stored as strings).
>>
>> Can you store all money amounts as integers, as the cents value? That is
>> exact, searchable etc.
>>
>> Thanks,
>> Tom
>> BareFeetWare
>>
>> --
>> iPhone/iPad/iPod and Mac software development, specialising in databases
>> develo...@barefeetware.com
>>  --
>> Comparison of SQLite GUI tools:
>> http://www.barefeetware.com/sqlite/compare/?ml
>>
>> ___
>> 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
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import FILE TABLE

[Gerry Snyder]

On 3/10/2011 1:28 PM, jcilibe...@comcast.net wrote:
> Hello,
>
>
> Unbelievably active user group!
>
>
> I have been unable to import a CSV text file from MS Access to sqlite:
> 1. Created a small table (3 fields and 1 record) in Access and exported it to 
> a CSV text file named "myCSVfile.txt"
>
>
> 2. Transferred from PC to Mac. Opened file "myCSVfile.txt" ...looks OK eg: 
> [1, "Jack", "Sammamish"]
>
>
> 3. Created a new DB ("myDB") and table ("myTable") in SQLite Database Browser 
> eg: [ID:primaryKey Name:text City:text]
>
>
> 4. Opened the DB in terminal with>  sqlite myPath/myDB

Does adding the line:

.separator ","

help?

>
> 5. Entered command>  .import myPath/myCSVfile myTable
>
>
> Always get back message: "line 1: expected 3 columns of data but found 1"
>
>
> Help! I've read many archived posts...so I know this should work.
> ___
> 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] Prescott Iris Soc

[Gerry Snyder]

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected cascading delete

[Gerry Snyder]

On 1/12/2011 2:54 PM, Duquette, William H (318K) wrote:
> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.

 From the "ON CONFLICT" section of the docs:

"When a UNIQUE constraint violation occurs, the REPLACE algorithm 
deletes pre-existing rows that are causing the constraint violation 
prior to inserting or updating the current row and the command continues 
executing normally."

This seems to make it expected behavior.


Gerry (JPL retiree)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write Query to do this?

[Gerry Snyder]

On 12/13/2010 7:55 AM, steve mtangoo wrote:
> Yes, ID is autoincrement and hence sequential.
> The problem with using it is, I have to know the ID of the beginning (for eg
> Book 1 Chapter 1 Verse 2) and the Id of the end (eg Book 4 Chapter 10 Verse
> 3). Then Simple BETWEEN will resolve it.
>
> Thanks for replying!
>

select verse from Bible where ID between (select ID from Bible where 
book = 1 and Chapter = 1 and Verse = 1) and (select ID from Bible where 
book = 2 and Chapter = 3 and Verse = 1)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write Query to do this?

[Gerry Snyder]

On 12/13/2010 6:54 AM, steve mtangoo wrote:
>   I have a  script that is supposed to query the Bible scriptures between two
> intervals. My table is named Bible and have columns: ID (int), Book (int),
> Chapter(int), Verse (int) and Scripture(text).

Is your ID column a sequential numbering of the verses? If so, using it 
could simplify the query.


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

Re: [sqlite] How to optimize this simple select query ?

[Gerry Snyder]

On 12/8/2010 9:56 AM, Vander Clock Stephane wrote:
> Hello,
>
> on the table :
>
> CREATE TABLE HASH(
> ID INTEGER PRIMARY KEY ASC,
> x1_y1 INTEGER,
> x1_y2 INTEGER,
> ...
> x5_y5 INTEGER
> );
> CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1);
> CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2);
> CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3);
> CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4);
> CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5);
>
>
> with millions of rows, how to optimize such query :
>
> Select
> ID
> from
> HASH
> where
> x1_y1>=<#randomnumber1>  and
> x1_y1<=<#randomnumber1>+ 20 and
> ...
> x5_y5>=<#randomnumber73>  and
> x5_y5<=<#randomnumber73>  + 20;
>
> because they takes very very lot of time (hourS) to return :(
> on other SGBD (like Firebird) with same amount of data
> they return immediatly ...
>

I usually seem to be wrong when I try to help here, but I keep trying.

My guess is that SQLite uses only one index per query (or per table per 
query or something like that), and so has to do a whole bunch of full 
table scans (or at least full scans of the remaining rows).

Maybe you need a query like:


select id from (select id from hash where

x1_y1 BETWEEN #randomnumber1 AND (#randomnumber1 + 20)) where x1_y2 BETWEEN 
#randomnumber4 AND (#randomnumber4 + 20)


nested many more levels deep. This might allow using all the indices.


HTH,   Gerry


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

Re: [sqlite] Possible bug: wrong query result when using an index

[Gerry Snyder]

On Sun, Dec 5, 2010 at 7:25 AM, Gavrie Philipson  wrote:

> Hi,
>
> 
> The query is as follows:
>
> SELECT entry_type AS entry_type_int, entry_types.name as
> entry_type_name, entry_id
> FROM timeline JOIN entry_types ON entry_type_int = entry_types.id
> WHERE
> (entry_type_name = 'cli_command' AND entry_id IN (SELECT command_id
> FROM object_changes WHERE obj_context = 'exported_pools'))
> OR
> (entry_type_name = 'object_change' AND entry_id IN (SELECT change_id
> FROM object_changes WHERE obj_context = 'exported_pools'))
>
>
I also got no rows with the above query, but got:

300  object_change  2048

after changing the query to:

SELECT entry_type AS entry_type_int, entry_types.name as
entry_type_name, entry_id
FROM timeline JOIN entry_types ON entry_type = entry_types.id
WHERE
(entry_types.name = 'cli_command' AND entry_type_name IN (SELECT
command_id
FROM object_changes WHERE obj_context = 'exported_pools'))
OR
(entry_type_name = 'object_change' AND entry_id IN (SELECT change_id
FROM object_changes WHERE obj_context = 'exported_pools'))


The changes were to use the real column names and not the aliases from the
SELECT clause.

I believe the problem arises (and the book "Using SQLite" explains it a lot
better than I can) because the FROM and WHERE clauses are executed before
the SELECT clause. I can not explain why the absence or presence if an INDEX
changes the result.

It is a known feature of SQLite that it does not contain huge amounts of
error checking, and the results when you do things you shouldn't can be
surprising.

Hope this helps,

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

Re: [sqlite] [ADV] 60% off "Using SQLite" and other O'Reilly ebooks

[Gerry Snyder]

I bought this book on opening day and now can highly recommend it.

It is a very useful supplement to the online docs. The info is pretty
up-to-date, but SQLite keeps advancing so the most recent enhancements
perforce are missing. For example, wal mode is not covered, but there
is a good description of foreign keys.

In the general SQL sections, I found "The SELECT Pipeline" among the
most illuminating, giving good explanations of what happens in the
processing of each clause.

The book is one of my better recent purchases.

Gerry

On 11/29/10, Jay A. Kreibich  wrote:
>
>   O'Reilly Media is running a one-day sale, Monday, 29 Nov, only.
>
>   *All* ebooks and videos are 60% off, including "Using SQLite" and
>   over 2000 other titles.  Just use the discount code DDF2H when
>   placing your order.
>
>   With this discount, the ebook version of "Using SQLite" is only
>   $12.80 USD.  Purchasing this title provides lifetime access to
>   DRM-free PDF, ePub, Mobi, and APK files.
>
>   http://oreilly.com/store/index.html Sale info
>   http://oreilly.com/catalog/9780596521196Using SQLite
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_sequence table

[Gerry Snyder]

On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote:
> Howdy!
>
> According to the docs on sqlite.org, a table with "INTEGER PRIMARY KEY 
> AUTOINCREMENT" gets an entry in the sqlite_sequence table.  I've got some 
> code that contains such a table; but if I query the sqlite_sequence table I 
> don't see it being updated; it's always empty.  Anyone have any idea what's 
> going on?
>

Have you put anything in the table that is

INTEGER PRIMARY KEY AUTOINCREMENT

so there is a sequence number to store in sqlite_sequence?

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

Re: [sqlite] SQLite database sync

[Gerry Snyder]

Keep track of changes, keep track of backups.

On 10/6/10, David Haymond  wrote:
> If I copy, I don't want to transfer EVERY record to the server each time I
> sync, because that would be a waste of bandwidth. What is the best way to
> copy only those records that have changed to the server?
>
> David
>
> -Original Message-
> From: Simon Slavin
> Sent: Wednesday, October 06, 2010 7:18 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite database sync
>
>
> On 6 Oct 2010, at 1:29pm, David Haymond wrote:
>
>> I am completely new to SQLite (and SQL in general), and I am currently
>> working on an iPhone app that uses the embedded SQLite engine to cache
>> offline data. What is the best way to implement synchronization
>> capabilities (change tracking, state tracking, etc.) in a separate table
>> (such as meta), so that the app can sync to the server?
>>
>> My database contains two tables: locations and trips. trips is the child
>> of locations.
>
> Does it actually have to synchronise ?  In other words, are changes made to
> both copies, or only to the copy on the iPhone ?  If changes are made to
> only one copy, you can just copy that copy (if you see what I mean).
>
> If changes are made to both copies, you're in for a world of hurt because
> you really need to separate out intentional changes from the changes
> actually made.  One way to do it is to keep a log of the operations executed
> since the last synch.  For example you could make another table 'changelog':
>
> timestamp operation
> 2398479 INSERT INTO locations ...
> 2402372 UPDATE trips ...
>
> To synchronise you just execute the log, in timestamp order, on all the
> other copies.  This does not work properly under all circumstances because
> UPDATE commands can interfere with one-another, but in a simple database it
> should work fine.
>
> To do things properly you need to maintain a separate unchanged model, synch
> it with all the satellites, then copy it to all the satellites.  It's
> complicated and requires lots of bytes moved about.
>
> Simon.
> ___
> 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
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query suggestion?

[Gerry Snyder]

  On 9/9/2010 11:32 AM, Doug wrote:
> Thank you Igor.
>
> You've helped me before with what also turned out to be a similar
> select referencing the same table twice.  I guess it's a concept
> that I don't fully get.  If there is a name for this technique
> I'll go Google and study up on it.
>

You should be able to find some good info by googling :"correlated subquery"



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

Re: [sqlite] "Using SQLite" - O'Reilly Deal of the Day

[Gerry Snyder]

  On 8/24/2010 8:09 AM, Jay A. Kreibich wrote:
>
>"Using SQLite" has gone to press!  To celebrate, "Using SQLite" is
>is today's O'Reilly "Ebook Deal of the Day."

And quite a deal it is!  Thank you for posting the announcement, Jay.

I had just been wondering what book to start reading on my BlackBerry, 
and now I know.


Thanks again,

Gerry


PS  Quick review:  The index looks pretty decent.  :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why the deadlock?

[Gerry Snyder]

Er, did you not see Dan Kennedy's comments a fed days ago??

On 8/24/10, Nikolaus Rath  wrote:
> Nikolaus Rath  writes:
>> Still no one able to clarify the issues raised in this thread?
>>
>> Let me try to summarize what I still don't understand:
>>
>>  - Will SQLite acquire and release an EXCLUSIVE lock while keeping a
>>SHARED lock if one executes a UPDATE query with one cursor while a
>>different cursor is in the middle of a SELECT query,
>>
>>-or-
>>
>>will the EXCLUSIVE lock be held until the SELECT query finishes?
>>
>>  - Is there a way to prevent SQLite from keeping the SHARED lock
>>while waiting for an EXCLUSIVE lock if doing so would result in a
>>deadlock (because another connection holding a SHARED lock needs to
>>get an EXCLUSIVE lock before it can release the SHARED lock)?
>
>
> Hmm. Still no answer. But thanks to Simon I know at least that some
> people are reading this thread :-).
>
>
> So different question: does anyone know how to get this thread to the
> attention of an SQLite developer who might be able to help?
>
>
> Best,
>
>-Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>   PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using variable is queries

[Gerry Snyder]

  On 7/30/2010 5:40 AM, Igor Tandetnik wrote:
> chris23879  wrote:
>> I'm tring to create a paging function in sqlite. Is it possible to declare
>> and use a variable in sqlite.
> No. But since SQLite is embedded in your application, you can use variables 
> in your host programming language.

And the other answer is that every row of every table is filled with 
nothing but variables.

Accessing them is a little more verbose, admittedly. If you don't want 
to keep the variables, use a temporary table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with running test suite

[Gerry Snyder]

package require Tcl 8.5

is all it takes


Gerry

On 7/29/10, Richard Hipp  wrote:
> On Thu, Jul 29, 2010 at 4:44 PM, Paweł Hajdan, Jr.
> wrote:
>
>> I'm planning to contribute some patches (upstreaming patches Chromium
>> project applies to its local copy of sqlite).
>>
>> I've checked out the fossil repository, configured and compiled sqlite,
>> and
>> tried running "make test" (I didn't make any changes).
>>
>> I'm pasting below the test result and some fossil info. Please let me know
>> if you need more. This is on Ubuntu Lucid. Do you have any ideas what
>> makes
>> these tests fail? Should I be doing it some other way?
>>
>> wal2-10.2.1... Ok
>> wal2-10.2.2... Ok
>> wal2-10.2.3... Ok
>> wal2-11.0... Ok
>> wal2-11.1.1... Ok
>> ./testfixture: bad field specifier "t"
>>while executing
>>
>
>
> The test suite now requires Tcl 8.5.  You appear to be running Tcl 8.4.
>
> It would be relatively easy for use to enhance the test suite so that it
> automatically detects a too-old version of Tcl and prints a warning or
> error.  We'll try to make that change for you soon.
>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with complex UPDATE question

[Gerry Snyder]

  On 7/23/2010 10:09 AM, Jim Morris wrote:
> What you are trying to do is unclear to me.  It seems that table1
> doesn't have enough data to unambiguously identify the rows.
>
> On 7/23/2010 8:03 AM, peterwinson1 wrote:
>> Thanks Eric and Alan for your help.  I tried to apply your code to my problem
>> and it works to a limited extent because the problem is more complicated
>> than the example I gave in the post.  I tries to simplify my exact problem
>> but that didn't work out.  So here is the problem that I trying to solve.
>>
>> table1 (KEY, COL1)
>>
>> 0, 1
>> 0, 2
>> 1, 3
>> 1, 4
>> 2, 5
>> 2, 6
>> 3, 7
>> 3, 8
>>

It seems to me that you should really have:

table1 (KEY, COL1, COL2)

0, 1, 2
1, 3, 4
2, 5, 6
3, 7, 8

based on the rest of your question. Is there a good reason you can not 
use a schema like this?


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

[sqlite] Is there busy handling in SQLite jdbc drivers?

[Gerry Snyder]

I could not find any information on either the zentus or xerial websites 
on any built-in provisions for busy handling.

Did I miss it, or is the functionality there but not documented, or do I 
need to check error codes and retry in my code?

TIA,

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

Re: [sqlite] How to determine when to VACUUM?

[Gerry Snyder]

On 7/6/2010 2:58 PM, Kristoffer Danielsson wrote:
> What you are saying makes sense. Thanks for your advice!
>
>
>
> However, I do believe there are times when vacuuming would be beneficial. For 
> instance, if a database for software X is detected to have 90% unused space 
> for a couple of weeks, then why bloat the harddrive?
>

If there is lots of free space on the drive, why work at giving it a 
little bit more?

In any event, knowing the database and hard disk usage patterns will 
lead to much, much, much better criteria for vacuuming than any general 
rule of thumb.

Optimizing plentiful resources is non-optimal.


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

Re: [sqlite] EXTERNAL:Re: How to select an entry that appears <=ntimes and only show n times if it appears more than n times?

[Gerry Snyder]

On 7/2/2010 10:09 AM, P Kishor wrote:

> I was going to add "That is an Igor-question" to "I don't know how to
> do that with sql." I have no idea how you do this, but if ever I meet
> you in person, I will be too awestruck to say anything beyond
> "SELECT.."
>

 From me it would more likely be an irate "Who are you and how dare you 
be so much smarter than I am?"

But +1 on the "awestruck" for sure.


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

Re: [sqlite] unexpected large journal file

[Gerry Snyder]

On 6/18/2010 7:01 PM, Scott Hess wrote:
>
> The old-school solution to this problem is an external sort (*).  The
> basic idea is that you process the incoming data in memory-sized
> chunks (ie, fast), then write out sorted subfiles.  Then you process
> the sorted files in parallel, merging to the output.
>

The other old-school solution involving preprocessing is block sorting 
or binning. You go through the data once and create a bunch of files of 
unsorted but grouped data. Then insert the data from the files in order. 
If the files are transaction-sized, each one will be adding to a fairly 
small range of values.

Gerry


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

Re: [sqlite] select %column% from table

[Gerry Snyder]

So why not columns keyword and value?

Gerry

On 4/30/10, David Lyon  wrote:
> Thanks for everyones efforts let me expand:
>
>
> if I had many many files like this:
> http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt
>
> you see 2 columns keyword and value, the keywords would be the fields  (1st
> column in the html link above) in the table while the values (second column)
> are the data I would insert.
>
> The fields will be many and include
>
> P$nR
> P$nS
> P$nB
>
> etc.
>
> thats why I wanted a quick way to access "select P%R from TABLE";
>
>
> thanks again
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Original Message 
> From: P Kishor 
> To: General Discussion of SQLite Database 
> Sent: Fri, April 30, 2010 10:48:41 AM
> Subject: Re: [sqlite] select %column% from table
>
> crap! I completely misunderstood your question... be confused, and
> then ignore my reply.
>
> On Fri, Apr 30, 2010 at 9:43 AM, P Kishor  wrote:
>> On Fri, Apr 30, 2010 at 9:28 AM, David Lyon  wrote:
>>> If I had a table called TABLE with fields P1N..P50N is there a way to
>>> select something like:
>>>
>>> "select P%N from TABLE"
>>>
>>>  to return all the results from columns P1N..P50N or do I have to do
>>> it manually:
>>>
>>> "select P1N, P2N, P3N, P$nN from TABLE"
>>>
>>
>> use GLOB. See below
>>
>> Last login: Wed Apr 28 09:42:46 on console
>> punk...@lucknow ~$sqlite3
>> -- Loading resources from /Users/punkish/.sqliterc
>> SQLite version 3.6.23
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE t(a);
>> sqlite> INSERT INTO t VALUES ('P1N');
>> sqlite> INSERT INTO t VALUES ('P3N');
>> sqlite> INSERT INTO t VALUES ('P30N');
>> sqlite> INSERT INTO t VALUES ('P303N');
>> sqlite> INSERT INTO t VALUES ('P303X');
>> sqlite> INSERT INTO t VALUES ('P30Z');
>> sqlite> SELECT * FROM t;
>> a
>> --
>> P1N
>> P3N
>> P30N
>> P303N
>> P303X
>> P30Z
>> sqlite> SELECT * FROM t WHERE a GLOB 'P*N';
>> a
>> --
>> P1N
>> P3N
>> P30N
>> P303N
>> sqlite>
>>
>>
>>> I can obviously do it via scripting but wanted a more elegant way.
>>>
>>> Thanks for your help in advance.
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> 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
>> ===
>>
>
>
>
> --
> 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
> ===
> ___
> 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
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Direct access of table data

[Gerry Snyder]

How long would it take to test using an index with 18 fields? Might
not be crazy.

Gerry

On 4/23/10, Nathan Biggs  wrote:
> Max, thanks for the information.  That will be very useful for other
> table queries, but not for this one.  For my table in questions there
> are 18 fields.  I think an index with 18 fields would be a little crazy.
>
>
> On 4/23/2010 3:06 AM, Max Vlasov wrote:
>>
>> > ...As I add more restrictions on the where-clause it
>> > tends to slow down.  I realize that this is due to my indexes, but can't
>> > add a lot of indexes because it slows down the insert speed which is
>> > more important than the query speed.
>> >
>>
>>
>> Nathan, maybe you already knew but just in case...
>>
>> if your select relies on an index and also queries fields not presented in
>> that index, consider appending these extra fields to the index. It doesn't
>> make sense in terms of search speed, (moreover it will increas the db
>> size),
>> but this will save time since no extra lookup will be taking place.
>>
>> So if you have table
>> CREATE TABLE  a, b, c,
>>
>> and index
>> CREATE INDEX  ON a, b
>>
>> and use query similar to
>> SELECT a, b, c ... WHERE a= and b =
>>
>> sqlite will do extra lookup to get c from the table,
>>
>> but if you change the index to
>>
>> CREATE INDEX  ON a, b, c
>> the same query will get all the data from the index itself saving time and
>> the amount of data flow.
>>
>> I did a quick test and it showed not only a noticable difference in time,
>> but also a significant difference in amount of the data read.
>>
>> Max,
>> maxerist.net
>> ___
>> 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
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 26

[Gerry Snyder]

Peter Haworth wrote:
>
> I have this question too.  I'm not programming in C so don;t have  
> access to the sqlite_last_insert_rowid
>   

Yes you do. You just didn't realize it. Using the cmd line tool:

F:\sqlite>sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table a(b);
sqlite> insert into a values(17);
sqlite> select last_insert_rowid();
1
sqlite> insert into a values(18);
sqlite> insert into a values(19);
sqlite> select last_insert_rowid();
3
sqlite>


HTH, and happy turkey day!!


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

Re: [sqlite] Replacing a table

[Gerry Snyder]

Pavel Ivanov wrote:
> 
>
> To update column in all rows of the table you need to issue the
> following statement:
>
> UPDATE table_name SET column_name = value
>   

And note that the "value" above does not have to be a constant. It can, 
for instance, depend on other values in the row being updated.


Gerry

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

Re: [sqlite] dump in-memory db to file in tcl

[Gerry Snyder]

Ned Fleming wrote:
> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?
>
> I create it like so:
>
>   sqlite3 dbFireData :memory:
>
> and insert a bunch of records, and then commit.

Is there reason not to attach a file (old or new), and either

create table realfile.newtablecopy as select * from main.originaltable

or else create the new table with ~ the same create table statement used 
for the original table, and then

insert into realfile.newtablecopy select * from main.originaltable

?  The first method copies all the data, but will drop any special 
thingies from the column definitions.


HTH,


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

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

[Gerry Snyder]

Rod Dav4is wrote:
> Thanks for reminding me: A thing's value is generally proportional to 
> its cost. And the attitude of its support team figures in there, too.
> -R.

There is only one person with attitude I see here, and it is not Dr. 
Hipp and it is not P. Kishor.

I have never seen a program, free or commercial, with better support.


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

Re: [sqlite] Integer Storage class

[Gerry Snyder]

Beau Wilkinson wrote:
>> i chose Integer for Performance isues... i'm develop. an POS Software 
>> and our articles table has lot's of >records. i need to optimize 
>> search. an Integer (1,2,4,6,8 bytes) is faster that 13-bytes-ean text 
>> for >comparison.
>> 
>
> That's reasonable, but I think Sqlite stores everything as textual data 
> anyway. I think the implication is that this data will take at least 
> one-byte-per-digit. 

Not true in SQLite 3. See: http://www.sqlite.org/datatype3.html


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