[sqlite] Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

[Justin Ng]

I just encountered something weird with "temp" and "sqlite_master".
I was wondering if it was another bug, or intentional.

-

This throws a "column not found" error,

SELECT
sqlite_master.sql
FROM
temp.sqlite_master;

-

This throws a "column not found" error,

SELECT
temp.sqlite_master.sql
FROM
temp.sqlite_master;

-

This works fine,

SELECT
x.sql
FROM
temp.sqlite_master AS x;

-

Referring to temp.sqlite_master columns directly in the WHERE clause also 
causes the error.
I assume it's the same for every other part of a query.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Justin Ng]

> I have a patch to fix the problem on a branch
> (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which
> you can experiment with. More changes and analysis are needed prior to
> landing on trunk.  I cannot guarantee that such a landing will in fact
> occur, though it seems more likely than not at this point.

I remember reporting a bug about COALESCE((... LIMIT 0), x) and you fixed it 
the very next day.

I forgot to thank you for that!

Thanks for the quick work, as always!

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

Re: [sqlite] COALESCE() does not short-circuit sometimes

[Justin Ng]

> It is not a "hack" because it does not work.  It is what is called a "failed 
> attempt" at a hack.

Yeah, the ABS() hack does not work.

Which led me to use `(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION 
ALL SELECT NULL))`
which **does** work.

> However, your assessment that coalesce is not "short-circuiting" is incorrect.
> If x is not null, then x is returned and the constant expression is not 
> returned.
> If the constant expression were a correlated expression, then it would not be 
> evaluated in the candidate context.
> If x were null and the expression was not constant (and thus required 
> evaluation)
> then it would be evaluated within the current context and its result returned.
>
> The documentation DOES NOT say that the arguments to coalesce will not be 
> evaluated, merely that the first one that is not-null will be returned.
>
> https://sqlite.org/lang_corefunc.html#coalesce

You're right that the documentation doesn't say so.

I did assume COALESCE() would always short-circuit because the SQL standard 
says COALESCE() is
equivalent to CASE expressions.

And I was pretty sure CASE expressions should always short-circuit.

However, turns out, CASE expressions don't always short-circuit, either. They 
generally do short-circuit but there are edge cases where they don't. It isn't 
entirely intuitive to me what the conditions are, though.

So, I guess I learned something new today.

> > Something like THROW_IF_NULL(x)
>
> You could certainly write a function that did that.  It would certainly be 
> better than a "hack" that does not even work.

I'd rather keep the hack that does work than use a user-defined function, if I 
can help it.

I feel like user-defined functions should be a last resort.

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

Re: [sqlite] COALESCE() does not short-circuit sometimes

[Justin Ng]

> It is not a "hack" because it does not work.  It is what is called a "failed 
> attempt" at a hack.

Yeah, the ABS() hack does not work.

Which led me to use `(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION 
ALL SELECT NULL))`
which **does** work.

> However, your assessment that coalesce is not "short-circuiting" is incorrect.
> If x is not null, then x is returned and the constant expression is not 
> returned.
> If the constant expression were a correlated expression, then it would not be 
> evaluated in the candidate context.
> If x were null and the expression was not constant (and thus required 
> evaluation)
> then it would be evaluated within the current context and its result returned.
>
> The documentation DOES NOT say that the arguments to coalesce will not be 
> evaluated, merely that the first one that is not-null will be returned.
>
> https://sqlite.org/lang_corefunc.html#coalesce

You're right that the documentation doesn't say so.

I did assume COALESCE() would always short-circuit because the SQL standard 
says COALESCE() is
equivalent to CASE expressions.

And I was pretty sure CASE expressions should always short-circuit.

However, turns out, CASE expressions don't always short-circuit, either. They 
generally do short-circuit but there are edge cases where they don't. It isn't 
entirely intuitive to me what the conditions are, though.

So, I guess I learned something new today.

> > Something like THROW_IF_NULL(x)
>
> You could certainly write a function that did that.  It would certainly be 
> better than a "hack" that does not even work.

I'd rather keep the hack that does work than use a user-defined function, if I 
can help it.

I feel like user-defined functions should be a last resort.

Thanks for your input, anyway.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COALESCE() does not short-circuit sometimes

[Justin Ng]

> Why do you think that that it should not evaluate ABS?  It is there and you 
> asked for it.  I believe it's a good idea to say, "hey, the amount you placed 
> here is out of boundary, think about what you are doing here." IMO, of 
> course. Thanks.
>
> josé

Sometimes, when querying data, rather than letting NULLs propagate,
it might be better to throw an error if NULLs are not expected from an 
expression.

The presence of NULLs might indicate an error in logic, data, or both.

So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to 
sanity-check queries.

Something like THROW_IF_NULL(x)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] COALESCE() does not short-circuit sometimes

[Justin Ng]

This happens in SQLite 3.28 and 3.31.

Consider the following queries,

-- Query 1
SELECT
  COALESCE(
NULL,
(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))
  );

-- Query 2
SELECT
  COALESCE(
(SELECT 'hello'),
(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))
  );

-- Query 3
SELECT
  COALESCE(
(SELECT 'hello'),
ABS(-9223372036854775808)
  );


Query 1 should throw `integer overflow`.
Query 2 should return 'hello'.
Query 3 should return 'hello'.

However, Query 3 throws `integer overflow`. It should short-circuit and not 
evaluate ABS()
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Extracting metadata about generated columns with SQL only?

[Justin Ng]

SQLite 3.31 introduced generated columns.

However, pragma table_xinfo() does not seem to give you the parenthesized 
expression of a generated column.

Is there a version of "dflt_value" (in table_xinfo()) for generated columns? 
How would I access that?

Also, does the "hidden" column (in table_xinfo()) with a value of "2" always 
mean the column is a generated column?

It looks like I'd have to do SELECT sql FROM sqlite_master; and parse the SQL 
string to get information about generated columns but this sounds like a 
terrible and error-prone idea; I'd like to avoid it, if possible.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not Null Constraint Issue?

[Justin Gielski]

>Could you show us the constraint you feel disallows those things ?  We
might be able to find a loophole in the exact way you phrased it.

Also, could someone explain to me what EMPTY means here ?  I've seen ''
called "empty string" but it doesn't seem to be normal SQL language.
-

Simon

You are correct. When I say "empty" I am meaning "empty string". My
apologies if my nomenclature is incorrect here in terms of SQL speak.

Regarding the constraint, to column is set as INTEGER NOT NULL, but because
of SQLite's dynamic typing system (something I forget about), empty strings
can be passed into INTEGER columns as they are technically not null. Now if
a non numeric text would have made it into an INTEGER field, we would have
dissected the issue a lot faster (and there is almost no way that would
have happened anyway). Because it was an empty string value that made it in
there, it appeared as a NULL to us when we tried reading the database as
our database reader expected an INTEGER. It took as a while to figure out
that the NULL value wasn't really NULL, but an empty string value sitting
in an INTEGER field.

I definitely know to look out for this now.

From: Simon Slavin 
To: SQLite mailing list 
Subject: Re: [sqlite] Not Null Constraint Issue?
Message-ID: <46053091-b9ab-43c4-b7f9-c89d70986...@bigfraud.org>
Content-Type: text/plain;   charset=us-ascii

On 17 Jan 2020, at 6:39pm, Justin Gielski  wrote:

> After looking into the issue a bit more, it appears the INTEGER value was
actually saved into the DB as an EMPTY not a NULL, which is currently
allowed by the database constraints.

Could you show us the constraint you feel disallows those things ?  We
might be able to find a loophole in the exact way you phrased it.

Also, could someone explain to me what EMPTY means here ?  I've seen ''
called "empty string" but it doesn't seem to be normal SQL language.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not Null Constraint Issue?

[Justin Gielski]

After looking into the issue a bit more, it appears the INTEGER value was
actually saved into the DB as an EMPTY not a NULL, which is currently
allowed by the database constraints. This makes me feel a bit better, as
now we have a reason for the constraint not triggering.

INTEGER objects within the .NET Framework do not allow for empty values
which is why we didn't notice that it was saved this way in the DB. It
appears the issue was application side as your suggestions suspected.

We're looking into why, but your responses helped us to realize this so
thank you.

-Justin



On Thu, Jan 16, 2020 at 4:00 AM <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:

> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>1. Re: Next Release? Visual release timeline? (R Smith)
>2. Re: Query Planner GROUP BY and HAVING clauses optimization?
>   (Keith Medcalf)
>3. Re: Query Planner GROUP BY and HAVING clauses optimization?
>   (Jean-Baptiste Gardette)
>4. Re: sqlite3_limit equivalent in System.Data.SQLite.dll
>   (Keith Bertram)
>5. Re: sqlite3_limit equivalent in System.Data.SQLite.dll
>   (Keith Medcalf)
>6. Not Null Constraint Issue? (Justin Gielski)
>7. Re: Not Null Constraint Issue? (Simon Slavin)
>8. Re: Not Null Constraint Issue? (R Smith)
>9. Test failures on GPFS (T J)
>   10. Re: Next Release? Visual release timeline? (Dominique Devienne)
>
>
> --
>
> Message: 1
> Date: Wed, 15 Jan 2020 17:54:24 +0200
> From: R Smith 
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Next Release? Visual release timeline?
> Message-ID: 
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> On 2020/01/15 1:24 PM, Richard Hipp wrote:
> > On 1/15/20, Dominique Devienne  wrote:
> >> I like Lua's way to graphically visualize releases at
> >> https://www.lua.org/versions.html
> >>
> >>
> >> Please send javascript that will generate such a graph, either as SVG
> >> or as an HTML Canvas.
> >>
> >> (1) For improved display on mobile, consider making the graph vertical
> >> instead of horizontal.
> >>
> >> (2) Assume the data is a JSON array of pairs.  The first element of
> >> each pair is the release name (ex: "3.30.0") and the second element is
> >> the time as a fractional year (ex: "2019.7775").
>
> We'd like to submit this layout as an option:
> https://sqlitespeed.com/sqlite_releases.html
>
> Shown alongside the current list in simple form. Tried a few layouts,
> not all work as well (SQLite releases are much more dense than Lua),
> finally settled on the above, but left some options open.
>
> It comes with some config structure in this form:
>
>var options = {
>  parentId: 'versionHistoryGraph',
>  width: 200,
>  yearWidth: 100,
>  heightPerYear: 300,
>  heightPerVersion: 14, // needs to match the style for .version
>  lineColor: "#22",
>  yearBackground: "#EE",
>  data:
>
> [["1.0",2000.6298197581566],["1.0.1",2000.6325576089437],["1.0.3",2000.6435090120922],["1.0.4",2000.659936116815],["1.0.5",2000.7064795801962],["1.0.8",2000.7502851927902],["1.0.9",2000.7749258498745],["1.0.10",2000.7804015514487],["1.0.12",2000.7968286561716],["1.0.14",2000.802304357746],["1.0.13",2000.802304357746],["1.0.15",2000.8132557608944],
>
> ...
>
> ["3.30.0",2019.7584987451517],["3.30.1",2019.7749258498745]]
>};
>
> Note: The Release-Date array must be given Ascending, else an additional
> sort step in Java is needed, but I think SQLite is better at that.
>
>
> I will send the full script directly via e-mail (not sure if the forum
> will allow the size), but it can of course also be directly copied from
> the above html.
>
> Official statement:
> We have solely created the content of that html page and th

[sqlite] Not Null Constraint Issue?

[Justin Gielski]

Good Afternoon

I wanted to pass along a really strange issue we just ran into in with one
of our products. We have a simple table with an INTEGER column set with a
NOT NULL DEFAULT 0 constraint.

We have no clue how it happened, but some how a null value was successfully
inserted into this column with out the constraint triggering an error or
defaulting to 0.

The application communicating with the DB is a .NET Framework app running
System.Data.SQLite. The strange thing is that the .NET application itself
will also not allow for NULL values as it converts them to 0 before sending
back to the database. This somehow occurred with no errors occurring during
the transaction.

Once the transaction completed, then we started seeing errors in both our
application and SQLite. SQLite. SQLite just kept triggering the following
message anytime we tried to adjust the null value to fix the issue":

*"database is locked release restore point sqlite"*

My first thought was a hung journal file keeping it locked, but it wasn't
the case. An application still had hold on the database though, almost as
if it hung. We closed anything that may have had the file open. Once that
was done we were able to successfully update the column with a value and
everything continued to work.

The database locking mode is set to NORMAL but the database is always
opened exclusively. Could this been a concurrency issue in which 2
connections hit the database at the exact same time? No data loss was
reported yet.

I've been working with SQLite for a while now, and this was the first time
I've seen a constraint not catch something like this. Just wanted to pass
along as I found it odd.

Have a great day

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

[sqlite] COALESCE() ignores LIMIT 0 clause in subquery?

[Justin Ng]

Is this the appropriate place to discuss this?

The below examples are expected to return 3.
The first example returns 4, the second returns 3.

It seems like LIMIT 0 is ignored by COALESCE().
https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/0
CREATE TABLE "myTable" (
  "myColumn" INT PRIMARY KEY
);
INSERT INTO
  "myTable"("myColumn")
VALUES
  (4);

SELECT
  COALESCE(
(
  SELECT
"myTable"."myColumn" AS "myTable--myColumn"
  FROM
"myTable"
  LIMIT
0
  OFFSET
0
),
3
  );

Adding WHERE FALSE does the trick.
https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/1
CREATE TABLE "myTable" (
  "myColumn" INT PRIMARY KEY
);
INSERT INTO
  "myTable"("myColumn")
VALUES
  (4);

SELECT
  COALESCE(
(
  SELECT
"myTable"."myColumn" AS "myTable--myColumn"
  FROM
"myTable"
  WHERE
FALSE
  LIMIT
0
  OFFSET
0
),
3
  );


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

Re: [sqlite] [EXTERNAL] Virtual Tables xConnect Called When?

[Justin Olbrantz]

The reason I asked the question is because for my virtual table which has a
"backing store" (more precisely it needs to have CREATE VIRTUAL TABLE
called to associate a loaded file with the table to be created via a table
parameter), I have separate implementations for xCreate and xConnect.
xCreate is working fine (in fact it's already handling queries properly),
but I'm not seeing any calls to xConnect including in queries (as you
suggest). So for a table with different xCreate and xConnect functions,
when would xConnect be called?

-- 
Justin Olbrantz (Quantam)
"Ardente veritate
Urite mala mundi
Ardente veritate
Incendite tenebras mundi"
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables xConnect Called When?

[Justin Olbrantz]

The documentation says that "The difference is that xConnect is called to
establish a new connection to an existing virtual table whereas xCreate is
called to create a new virtual table from scratch." But this leaves me
unclear on the circumstances where xConnect would be called (assuming I
have a non-eponymous table). How would you create a new connection to an
exiting virtual table?

-- 
Justin Olbrantz (Quantam)
"Ardente veritate
Urite mala mundi
Ardente veritate
Incendite tenebras mundi"
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Estimated Costs and Memory DBs

[Justin Olbrantz]

While looking through the SQLite3 source trying to find answers to some
questions I had about virtual tables, I noticed that the memory DB is
implemented as a VFS rather than a database. Is my understanding correct
that this means that the estimated cost the query planner uses for memory
tables will be equal to that of the same database on disk? Shouldn't memory
DBs always have a much lower cost to cause the query planner to prefer
intensive operations on memory DBs rather than disk DBs? I know there's a
field in the table definition for cost multiplier that could perhaps be
used for this purpose, but as far as I can tell this is only ever used by
ANALYZE and it's theoretically impossible that memory DBs could even use it.

As for the question I was originally looking for an answer to, I am writing
a virtual table for a different file format, and it is expected that my
virtual table will be held completely in memory. What should I do with the
estimatedCost value from xBestIndex? According to the documentation this
should be an approximation of the number of disk accesses for the query,
which would be 0 in this case. But it's clearly vastly faster to do a query
on an indexed column, meaning the cost for an indexed column should be much
lower than the cost for an unindexed column. How should I be doing this?

-- 
Justin Olbrantz (Quantam)
"Ardente veritate
Urite mala mundi
Ardente veritate
Incendite tenebras mundi"
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve "latest" fileio.c, test_windirect.c/h files?

[Justin Clift]

On 2019-05-12 21:39, Warren Young wrote:
On May 11, 2019, at 10:46 PM, Justin Clift  
wrote:


One of the steps uses curl to download fileio.c, test_windirent.c/.h
from fossil


[snip]


Is there a way to always get "the latest" version of the file? :)


$ curl -L -o src/extensions/fileio.c
'https://sqlite.org/src/raw?filename=ext/misc/fileio.c=trunk'


Thanks Warren, that works. :)



Or, you could just use Fossil directly. ...



It's a good thought, but not just yet.  Already have a head full of
other stuff being learned, don't really want to add a whole new
distributed version control system as well.  Maybe later. :)

Regards and best wishes,

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

[sqlite] How to retrieve "latest" fileio.c, test_windirect.c/h files?

[Justin Clift]

Hi all,

Is there a way to grab the "latest" version of a SQLite source file?

In this instance, we have an automatic nightly process (shell script)
that builds the DB Browser for SQLite nightly builds.

One of the steps uses curl to download fileio.c, test_windirent.c/.h
from fossil, so the fileio extension can be included in the nightly
builds.

It's using a specific commit for each of the downloads at the moment,
as that's the only thing I was able to figure out at the time of
creating the script:

  curl -L -o src/extensions/fileio.c 
https://sqlite.org/src/raw/ext/misc/fileio.c?name=288e7230e0fe464d71b0694e2d8bdd3a353118ac2e31da3964b95f460f09915f
  curl -L -o src/extensions/test_windirent.c 
https://sqlite.org/src/raw/src/test_windirent.c?name=a895e2c068a06644eef91a7f0a32182445a893b9a0f33d0cdb4283dca2486ac1
  curl -L -o src/extensions/test_windirent.h 
https://sqlite.org/src/raw/src/test_windirent.h?name=90dfbe95442c9762357fe128dc7ae3dc199d006de93eb33ba3972e0a90484215


Leaving off the `name=...` argument just returns a HTML page, instead
of giving the expect latest version too.

Is there a way to always get "the latest" version of the file? :)

Regards and best wishes,

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

Re: [sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

[Esplin, Justin]

(1) It is possible that this has occurred on other versions of Windows, but I 
can only confirm that it has occurred on machines running Windows 10. The most 
recent cited occurrence was on Windows 10 Enterprise, version 1607, OS Build 
14393.1593
(2) We are using version 1.0.98.1.
(3) We aren't compiling SQLite so I don't think we are setting any compile-time 
options. We are using the following nuget package defined in our 
packages.config:

(4) NTFS

Some other info that may or may not be useful:
- We are also seeing many occurrences of SQLite Error (5): Database is locked
- I assume they are pulled down as dependencies of the nuget package, but I am 
also seeing SQLiteInterop.dll v1.0.61.0, and Mono.Data.Sqlite.dll v1.0.61.0 in 
the bin of our solution.

Thanks,

Justin Esplin
Applications Dev II
Verisk Insurance Solutions - Xactware
Phone: +1.801.932.8039
jlesp...@verisk.com
http://www.verisk.com/insurance/



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Sunday, September 17, 2017 2:45 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Figuring out the Cause of SQLite Error 11: Corrupt 
Database

On 9/15/17, Esplin, Justin <jlesp...@verisk.com> wrote:
> I was able to access a computer which encountered these errors, about
> 10 minutes after they occurred. I ran "PRAGMA integrity_check" and it
> returned "ok". How/why would this have changed? I verified the user
> did not re-install our app or otherwise reconfigure the database.

You have not yet revealed to us:

(1) What operating system you are running on, including the specific version 
number
(2) What specific version number of SQLite that you are running
(3) What compile-time time options you are using
(4) What filesystem the database is stored on.

All of these things are important.

The only other time we have seen a problem like this, it turned out to be a bug 
in the file cache of the OS kernel, not in SQLite.  Just because SQLite is the 
software reporting the problem, does not necessarily mean that SQLite is the 
cause of the problem.  Details are important.  Please provide as much context 
as possible.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=birp9sjcGzT9DCP3EIAtLA=e6PxbQq9NdAJUZWHeJFpfhuVEDXWOo3XEghP1Z1arEs=07hq1KvkKn5WsEO9IVKerCbsYRYSpRRg-YhyIYSzxsA=cbIQ6kcghDi91HmDs_KkQOhHP7GmacRSYcgofZ60EFs=



This email is intended solely for the recipient. It may contain privileged, 
proprietary or confidential information or material. If you are not the 
intended recipient, please delete this email and any attachments and notify the 
sender of the error.



Xactware's opt-in mailing list allows you to receive Xactware News that is of 
interest to you. Visit my.xactware.com today to join or to update your email 
preferences!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

[Esplin, Justin]

I was able to access a computer which encountered these errors, about 10 
minutes after they occurred. I ran "PRAGMA integrity_check" and it returned 
"ok". How/why would this have changed? I verified the user did not re-install 
our app or otherwise reconfigure the database.

Justin Esplin
Applications Dev II
Verisk Insurance Solutions - Xactware
Phone: +1.801.932.8039
jlesp...@verisk.com
http://www.verisk.com/insurance/



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Friday, September 15, 2017 4:43 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Cc: Sines, Matt <mwsi...@verisk.com>
Subject: Re: [sqlite] Figuring out the Cause of SQLite Error 11: Corrupt 
Database

On 9/14/17, Esplin, Justin <jlesp...@verisk.com> wrote:
>
> We are seeing a couple worrisome (and not very informative) messages
> repeatedly in our logs:
>
> SQLite error (11): database corruption at line 55472 of
> [cf538e2783]
>
> SQLite error (11): database corruption at line 55514 of [cf538e2783]
>
> I have been looking around the web for a few days to try and figure
> out how to diagnose this, but have come up empty-handed. This is
> occurring on various Windows machines, though they appear to
> functional normally before, during and after these errors are written
> to the logs. It was by chance that we stumbled across these errors in the 
> logs.
>
> Is there a way to know what the lines are that the error messages are
> complaining about? Is there a chance that the corruption is of a
> nominal variety or that it is able to automatically recover, which is
> why it appears to continue to function normally?

Both errors are badly formatted b-tree pages in the database file.
These both indicate serious database corruption.  You should be able to verify 
the corruption by running "PRAGMA quick_check".

Is the database file located on a network filesystem?
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=birp9sjcGzT9DCP3EIAtLA=e6PxbQq9NdAJUZWHeJFpfhuVEDXWOo3XEghP1Z1arEs=mnCE15jLM9kRb8KAQTx351Gp9e63RWkXHyD_lTNVZnQ=xcQp6UKYF6Nj3TZlT_tEqPNPSLmPlX3WUZ0wtKyEvzc=



This email is intended solely for the recipient. It may contain privileged, 
proprietary or confidential information or material. If you are not the 
intended recipient, please delete this email and any attachments and notify the 
sender of the error.



Xactware's opt-in mailing list allows you to receive Xactware News that is of 
interest to you. Visit my.xactware.com today to join or to update your email 
preferences!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

[Esplin, Justin]

Howdy,


We are seeing a couple worrisome (and not very informative) messages repeatedly 
in our logs:

SQLite error (11): database corruption at line 55472 of [cf538e2783]

SQLite error (11): database corruption at line 55514 of [cf538e2783]

I have been looking around the web for a few days to try and figure out how to 
diagnose this, but have come up empty-handed. This is occurring on various 
Windows machines, though they appear to functional normally before, during and 
after these errors are written to the logs. It was by chance that we stumbled 
across these errors in the logs.

Is there a way to know what the lines are that the error messages are 
complaining about? Is there a chance that the corruption is of a nominal 
variety or that it is able to automatically recover, which is why it appears to 
continue to function normally?

I also did my best to post a question on SO which you can see 
here<https://stackoverflow.com/questions/46180021/how-to-debug-the-cause-for-sqliteerror11-database-corruption?noredirect=1#comment79326475_46180021>.

Thanks in advance for any help you may have to offer.



Justin Esplin
Applications Dev II | Xactware

Phone: +1.801.932.8039

[Verisk Insurance Solutions]<http://www.verisk.com/insurance/>

[Forbes 2016 Most Innovative 
Companies]<http://www.forbes.com/innovative-companies/list/#tab:rank_search:verisk>[America's
 Best Mid-Size 
Employers]<https://www.forbes.com/best-midsize-employers/list/#tab:rank_search:verisk>[Great
 Place to Work]<http://reviews.greatplacetowork.com/verisk-analytics>

verisk.com/xactware<http://verisk.com/xactware> | 
vCard<http://www.dynasend.com/signatures/vcard/jlesplin-at-verisk.com.vcf> | 
Map<https://www.google.com/maps/place/1100+W+Traverse+Pkwy,+Lehi,+UT+84043/@40.4350195,-111.8709977,17z/data=!3m1!4b1!4m2!3m1!1s0x87528004da6ecd33:0x621596ddfddd2474?hl=en>
 | Email<mailto:jlesp...@verisk.com>

[Blog]<http://www.verisk.com/blog>[LinkedIn]<https://www.linkedin.com/company/verisk-insurance-solutions>[Twitter]<https://twitter.com/veriskinsurance>





This email is intended solely for the recipient. It may contain privileged, 
proprietary or confidential information or material. If you are not the 
intended recipient, please delete this email and any attachments and notify the 
sender of the error.



Xactware's opt-in mailing list allows you to receive Xactware News that is of 
interest to you. Visit my.xactware.com today to join or to update your email 
preferences!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug

[Adams, Justin]

Hello,

I searched the bug tracker and the only thing I saw that looked relevant was 
ticket UUID 6c266900a22574d4d6474503da5bfe849c8b244f.
http://www.sqlite.org/src/tktview?name=6c266900a2

However, that seems to deal with special Unicode characters and I'm not dealing 
with that here.

I'm using SQLite 3.8.11.1 on Windows 7 Pro 64 built with Visual C++ 2010.

I was trying to create a table with a column for IPv4 addresses as VARCHAR. It 
seems when I set the .mode to column that some records are truncated on the 
right. Changing the .mode fixes the problem, so I would think it has to do with 
the column width calculation. Following my sig is some example code. You will 
see .mode is changed to column then "172.16.18.239" is truncated to 
"172.16.18.23". In the second example you will see "10.999.987.25" gets 
truncated to "10.999.987.".

Thanks to everyone for your help. If there is any more info you need please let 
me know. Also, if anybody can tell me what file/function in the source deals 
with this I would appreciate it.

Thank you,
Justin Adams

Example 1

C:\>C:\app\sqlite\sqlite3.exe C:\Temp\bugdemo.sqlite
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE ipAddresses(ip VARCHAR(20) PRIMARY KEY);
sqlite> INSERT INTO ipAddresses VALUES ('10.186.34.23'), ('192.168.1.4'), 
('172.16.18.239'), ('54.6.74.21'), ('8.8.8.8');
sqlite> SELECT * FROM ipAddresses;
10.186.34.23
192.168.1.4
172.16.18.239
54.6.74.21
8.8.8.8
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM ipAddresses;
ip

10.186.34.23
192.168.1.4
172.16.18.23
54.6.74.21
8.8.8.8
sqlite> .mode list
sqlite> SELECT * FROM ipAddresses;
ip
10.186.34.23
192.168.1.4
172.16.18.239
54.6.74.21
8.8.8.8
sqlite> .mode column
sqlite> SELECT * FROM ipAddresses;
ip

10.186.34.23
192.168.1.4
172.16.18.23
54.6.74.21
8.8.8.8




Example 2

C:\home\db\static>C:\app\sqlite\sqlite3.exe static.sqlite
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE tableCategories(category VARCHAR(16) PRIMARY KEY);
CREATE TABLE tableSites(siteName VARCHAR(64) PRIMARY KEY);
CREATE TABLE tableStatic
(
ip VARCHAR(16) PRIMARY KEY,
location VARCHAR(64) REFERENCES tableSites (siteName) ON UPDATE CASCADE,
category VARCHAR(16) REFERENCES tableCategories (category) ON UPDATE 
CASCADE,
description VARCHAR(64)
);
sqlite> SELECT ip, category FROM tableStatic;
10.537.79.7|PBX
10.999.987.25|PBX
sqlite> .mode column
sqlite> SELECT ip, category FROM tableStatic;
10.537.79.7  PBX
10.999.987.  PBX
sqlite> SELECT category, ip FROM tableStatic;
PBX 10.537.79.7
PBX 10.999.987.
sqlite> .mode list
sqlite> SELECT category, ip FROM tableStatic;
PBX|10.537.79.7
PBX|10.999.987.25

Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of the intended recipient(s) and may contain confidential and 
privileged information. Any unauthorized review, use, disclosure, or 
distribution is prohibited. If you are not the intended recipient, please 
contact the sender by reply e-mail and destroy all copies of the original 
message.

[sqlite] DB Browser 3.7.0 for SQLite released

[Justin Clift]

Quick note.  DB Browser 3.7.0 for SQLite has been released: :)

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.7.0

Win + OSX binaries are there, as is the source tarball.  Ubuntu PPA
should be done in a day or two, and hopefully the FreeBSD port will
updated in the near future as well. :)

+ Justin

[sqlite] SourceForge seems to have grabbed the "sqlite" project there

[Justin Clift]

Hi all,

It looks like the SourceForge admin staff have taken control
of the "sqlite" project on SourceForge:

  http://sourceforge.net/mirror/sqlite/

The owners there are now "sf-editor1" and "sf-editor2".

Any idea if this is a recent thing, related to their other
string of project seizures?  eg:

  
http://arstechnica.com/information-technology/2015/06/black-mirror-sourceforge-has-now-siezed-nmap-audit-tool-project/

Regards and best wishes,

Justin Clift

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Justin Clift]

On 4 May 2015, at 18:52, Scott Doctor  wrote:

> The day I can open source my rent, groceries, car repairs, is when everything 
> else can be free.

Rent and groceries... yeah, good luck there. ;)

But car repairs might actually come along at some point (decade or two?)
if the 3D printing scene goes big. :)

(and "... 3D printed groceries ..." hrmm ;>)

+ Justin

[sqlite] What software is deployed more than SQLite?

[Justin Clift]

On 3 May 2015, at 19:26, Eric Sink  wrote:
> Last time I asked myself this question, I ended up in the same place you
> did:  zlib, libpng and libjpeg may be the only candidates in the same
> ballpark as SQLite.

Likely libxml2 as well.  Seems to get embedded by just about everything.

Some of the crypto libraries are in lots of things too, but not as much
as libxml2.

+ Justin

[sqlite] Integrating sqlite with Core Data and iCloud

[Justin Clift]

On 25 Apr 2015, at 20:41, Jeff M  wrote:
>> On Apr 24, 2015, at 2:44 AM, Simon Slavin  wrote:
>> 
>> On 24 Apr 2015, at 6:59am, Jeff M  wrote:
>> 
>>> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
>>> Data.  Core Data then takes care of the iCloud issues.
>> 
>> I imagine you'd do that by writing a VFS which used Core Data for storage.  
>> Core Data could store your data in any of the formats it has drivers for, 
>> including plaintext files and SQLite.  And Core Data could store your data 
>> in any medium it has drivers for, including local storage and iCloud.
>> 
>> The result might be slow and inefficient, since you're building a DBMS 
>> (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite).
>> 
>> If you use Core Data the resulting file wouldn't look like a normal SQLite 
>> database.  Core Data stores objects.  It doesn't store the rows and columns 
>> you refer to with SQL commands.  Maybe your objects would be table rows.
>> 
>> Simon.
> 
> Originally, I wanted to map my tables, rows, and columns to similarly-named 
> Core Data entities and attributes so I could continue to use the SQL 
> language.  I was hoping to hook into the SQL parser to get the benefits of 
> where.c, but I realize now that's impractical.
> 
> Your suggestion of working at the file system level is interesting, but my 
> objects would be disk blocks.  I could use a simple Core Data model: one 
> entity (representing the entire database file) and create one object per 
> block (each having a binary attribute containing one block of data).  It 
> would be easy to map each file system read() and write() to the corresponding 
> objects.  Using Core Data as a memory array would earn me the Kludge of The 
> Year Award.  But, I see data corruption in my future.
> 
> Can you point me to some sample source code (outside of SQLite itself) that 
> implements sqlite3_vfs_register()?

Hmmm, searching for that function name across GitHub returns about 27k results.

16.6k+ for just C:

  https://github.com/search?l=c=sqlite3_vfs_register=Code=?

6k+ for C++:

  https://github.com/search?l=cpp=sqlite3_vfs_register=Code=?

You'll probably not be short of example stuff to drawn from... :D

Regards and best wishes,

Justin Clift

Re: [sqlite] sqlite3 mailing list broken

[justin]

On 2015-02-12 14:00, Stephan Beal wrote:

On Thu, Feb 12, 2015 at 2:45 PM, <jus...@postgresql.org> wrote:
And yeah, I'm aware of fossil, but (to my thinking ;>) that 
shouldn't hold

back _this_ bit of software. ;)


FWIW, fossil was/is designed _specifically_ for sqlite's hosting 
(that's
neither a joke nor an exaggeration), so it's _exceedingly_ unlikely 
to go

anywhere, regardless of how many +1s people collect to the contrary.


Yeah, I know. ;)

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

Re: [sqlite] sqlite3 mailing list broken

[justin]

On 2015-02-12 13:40, Richard Hipp wrote:

On 2/12/15, Cal Leeming <c...@iops.io> wrote:
Also, I'd like to +1 having this project on some sort of social 
collab

platform, be it github, bitbucket etc. It would make external
contributions much easier, as I nearly gave up trying to report this
issue out of frustration.


+1 for GitHub.  Purely because of the large qty of potential 
contributors

there, many of whom actively get involved.

And yeah, I'm aware of fossil, but (to my thinking ;>) that shouldn't 
hold

back _this_ bit of software. ;)

Regards and best wishes,

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

Re: [sqlite] SQLCipher - Full Database Encryption for SQLite

[justin]

On 2015-02-09 10:34, Simon Slavin wrote:

<https://www.zetetic.net/sqlcipher/>

SQLCipher is an open source extension to SQLite that provides
transparent 256-bit AES encryption of database files.  It comes as
free source for you to compile yourself, or you can buy pre-built
binary libraries for numerous platforms, with support.

The API extends SQLite by adding PRAGMAs and C functions to configure
the encryption.

As a full database encryptor it encrypts the database file as a whole
rather than individual rows or fields.  It therefore isn't weak to 
the
attacks which easily penetrate most quick-and-easy encryption 
systems.


Yeah, this is the encryption support we're using in the DB4S (open 
source)

GUI.  The Zetetic guys were really helpful in their forums with
implementation pointers too. :)

Btw, if anyone gets it working for the MXE cross-compilation 
environment,

that'd be great for us too.  (we don't support SQLCipher in our Win32
builds yet, due to this missing bit) ;)

Regards and best wishes,

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

Re: [sqlite] v3.5.0 of DB Browser for SQLite released

[justin]

It turned out there were a few important bugs in 3.5.0, so now there's
a v3.5.1 with fixes for them.

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.1

Hope that's useful to people. :)

+ Justin


On 2015-01-31 18:46, jus...@postgresql.org wrote:

Hi all,

We've just released v3.5.0 of DB Browser for SQLite:

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.0

This release has a fair amount of bug fixes (eg in CVS import), plus
several usability tweaks, and now supports Simplified Chinese.

This is the first release to support encrypted databases too, via
SQLCipher.  Only available in the non-Windows builds for now (eg
OSX, Linux, etc).

Windows and MacOS X binaries are available from the above URL.

Linux, FreeBSD, OS/2 users will need to compile it themselves (pretty
easy) for now, until ports/packages/etc become available.

Hope that's helpful for people. :)

Regards and best wishes,

Justin Clift
___
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] v3.5.0 of DB Browser for SQLite released

[justin]

Hi all,

We've just released v3.5.0 of DB Browser for SQLite:

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.0

This release has a fair amount of bug fixes (eg in CVS import), plus
several usability tweaks, and now supports Simplified Chinese.

This is the first release to support encrypted databases too, via
SQLCipher.  Only available in the non-Windows builds for now (eg
OSX, Linux, etc).

Windows and MacOS X binaries are available from the above URL.

Linux, FreeBSD, OS/2 users will need to compile it themselves (pretty
easy) for now, until ports/packages/etc become available.

Hope that's helpful for people. :)

Regards and best wishes,

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

Re: [sqlite] Encrypted database

[justin]

On 2014-12-16 11:42, Harmen de Jong - CoachR Group B.V. wrote:

We use Navicat for SQLite and simply replaced the sqlite.dll file
with our own compiled SQLite dll version that has the proper
encryption included. Maybe this will work for you too and allows you
to use a management tool that is quite feature rich. Maybe this
approach will work for other management tools too.


H... trying to visualise how that works... :)

How do you specify encryption options (eg password, key size, etc),
if the application itself doesn't know to pop open a dialog asking
for them?

Guessing you hard coded the options into the SQLite dll file you
compiled?

Regards and best wishes,

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

Re: [sqlite] Encrypted database

[justin]

On 2014-12-15 13:17, Ulrich Telle wrote:

No, at least the *original* component System.Data.SQLite, available 
from


https://system.data.sqlite.org

uses its own encryption implementation based on a 128-bit RSA cipher
(using the Windows Crypto API), while SQLCipher uses a 256-bit AES
cipher (using the OpenSSL library). SQLCipher provides a .Net
interface (SQLCipher for ADO.NET), which is *based on
System.Data.SQLite*, that is, the SQLCipher guys used the .Net 
wrapper

part of System.Data.SQLite, but replaced the internal implementation
of the encryption algorithm by their own implementation.

The page

https://www.zetetic.net/sqlcipher/sqlcipher-ado/

explains how to use SQLCipherfor ADO.Net together with Visual Studio.

Unfortunately the various encryption extensions for SQLite are not
compatible, so the developer has to take care that the development
environment and the database management tools intended to be used
support the same encryption extension.


Ahhh, thanks Ulrich, that's really good info. :)

Regards and best wishes,

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

Re: [sqlite] Encrypted database

[justin]

On 2014-12-13 19:29, Mujtaba Ali Panjwani wrote:

I have created an encrypted database using visual studio plugin of
system.data.sqlite. Now whichever other database management software 
than
VS, I try to open that database is failing. Can any of you please 
help me
with issue. I suspect this is some sort of encryption algorithm 
support

problem but I don't know how to resolve the issue.


We have a similar open issue with DB Browser for SQLite:

  https://github.com/sqlitebrowser/sqlitebrowser/issues/155

Apparently (from that issue), the encryption algorithm used by
System.Data.SQLite is SQLCipher:

  https://www.zetetic.net/sqlcipher/

Our nightly builds for MacOS X support that, as does compiling DB 
Browser

for SQLite yourself on *nix using our latest development code.  But, we
don't support SQLCipher encryption in our Windows builds (yet).

Do you have access to a MacOS X, Linux, or BSD desktop you could try
with?

Regards and best wishes,

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

Re: [sqlite] Locking errors on network

[justin]

On 2014-11-10 18:33, RSmith wrote:


There is one Client-Server implementation of SQLite (SQLightening I
think) but it is neither free nor easy to convert to.


Doing some Googling, this looks like the thing:

  http://sqlitening.com

They don't seem to sell it any more (last version was released Dec
2012), though the support forums are still online.



You can write
your own server too, but the best bet is using MySQL or PostGres in
these cases.


Use PostgreSQL (www.postgresql.org). :)

This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
Richard Hipp:

  SQLite: Protégé of PostgreSQL
  https://www.youtube.com/watch?v=ZvmMzI0X7fE

Just saying. ;)

Regards and best wishes,

Justin Clift

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

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[justin]

On 2014-11-06 03:12, nicolas riesch wrote:

  There is a bug in some Linux implementations (RedHat9 is the 
canonical

example) that prevents fcntl() locks created by one thread from being
modified in a different thread.


As a data point, "Red Hat 9" is incredibly ancient, from 2003:

  http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history

While there is probably some someone - somewhere - still running on it
an ancient PC under a desk... it's not worth worrying about.

Just saying. ;)

Regards and best wishes,

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

[sqlite] OT: Testers needed for full database encryption support

[Justin Clift]

As a quick FYI, we've just added initial full database encryption
support to DB Broweser for SQLite (using SQLCipher).  We're looking for
people using MacOS X, Linux, or FreeBSD to try it out and report
success/failure/etc back to us (on GitHub).

Initial MacOS X build:

  
http://mirror.salasaga.org/sqlitebrowser/onceoffs/sqlitebrowser_201411051855.dmg

For Linux and FreeBSD, you'll need to compile it yourself (pretty
easy):

  https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md

For anyone with time to do so, this will really help us out. :)

Regards and best wishes,

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

Re: [sqlite] v3.4.0 of DB Browser for SQLite released

[justin]

On 2014-10-29 23:45, jus...@postgresql.org wrote:

Hi everyone,

We've (finally) released version 3.4.0 of DB Browser for
SQLite.  This is the cross platform GUI application
that was known for many years as "SQLite Database
Browser" before the recent rename.

Downloads for Windows and OSX here:

  https://github.com/sqlitebrowser/sqlitebrowser/releases


Just to point out it also works on Linux, FreeBSD, and
pretty likely others.  (uses Qt and is written with fairly
cross platform code)

Ubuntu has older packages available, but they should be
getting updated for 3.4.0 soon-ish.  You can compile it
yourself very easily though.  Instructions here if it
helps:

  
https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md


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

[sqlite] v3.4.0 of DB Browser for SQLite released

[justin]

Hi everyone,

We've (finally) released version 3.4.0 of DB Browser for
SQLite.  This is the cross platform GUI application
that was known for many years as "SQLite Database
Browser" before the recent rename.

Downloads for Windows and OSX here:

  https://github.com/sqlitebrowser/sqlitebrowser/releases

:)

Interesting FYI - Pretty much as soon as we renamed
away from "SQLite Database Browser", the RazorSQL guys
seem to have put up a page calling their (proprietary)
application by that name as well.  Marketing ploy to get
ranked higher in Google or something.  Ugh. :/

Regards and best wishes,

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

Re: [sqlite] What if OpenDocument were built atop SQLite?

[justin]

On 2014-09-08 13:07, John McKown wrote:


Open/Libre Office is Java based.



Are you 100% about that?  When I used to be involved
with OpenOffice (years ago, prior to LibreOffice
split), the main code was C++, with Java used for
some things.  OpenOffice Base was written in Java
for example.

I *think* the Java usage came about politically
because Sun (back in the day) decided Java _going_
to be the language of choice.  Regardless of developer
sentiment.  So they went about developing / bolting on
all of the new pieces with it.

That's my dodgy memory of things anyway.  LibreOffice
might have changed things, etc. ;)

Regards and best wishes,

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

Re: [sqlite] GUI INfo

[justin]

On 2014-09-10 20:56, Maxine Nietz wrote:
I am an experienced Access VBA programmer. I know about the SQLite 
commands

to create and manipulate data in tables and queries.

What I want to know is where do I find info on creating a graphical 
user
interface such as menus, forms and reports. What additional programs 
are

required to do this?


It depends on what you want your graphical user interface to
do. :)

What's the main goal for your new graphical user interface?

As initial probing questions, do you want to build something
on top of MS Access, or do you want a completely stand-alone
program?

Asking because (in a general sense) there are many ways to
create a GUI.

So, narrowing down the possibilities by knowing the main goal
will help. :)

If you want to see an example of a good stand-alone SQLite GUI
program, this is one example:

  http://sqlitebrowser.org

The source code for it is here:

  https://github.com/sqlitebrowser/sqlitebrowser

It's written in a language called "C++", and uses a popular
cross platform toolkit called "Qt" (qt-project.org).

Does that help? :)

Regards and best wishes,

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

Re: [sqlite] SQLite Database Browser v3.3.0 released

[justin]

On 2014-08-31 16:07, Richard Hipp wrote:

On Sun, Aug 31, 2014 at 11:49 AM, <jus...@postgresql.org> wrote:



We've updated everything we control, and just pushed out a v3.3.1
release that uses the new project naming (with no functional
changes).


... and it turns out there's already a project called "Database
Browser".  Which supports SQLite (and others).

  http://www.etl-tools.com/database-browser/overview.html

Looks like we'll need to do some thinking about new name v2 (or
v3). "Fluffy Pink Rabbits of Doom for SQLite" has a certain
appeal at this point... ;)

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

Re: [sqlite] SQLite Database Browser v3.3.0 released

[justin]

On 2014-08-27 23:02, Richard Hipp wrote:

On Wed, Aug 27, 2014 at 6:18 PM, <jus...@postgresql.org> wrote:


This is in process now:

  https://github.com/sqlitebrowser/sqlitebrowser/pull/83

Might need a few code extra code tweaks (unsure), but we'll get it
done and push out an updated release with the new name sometime
in the next few days. :)


Thank you.  I think that will save everybody a lot of grief.


We've updated everything we control, and just pushed out a v3.3.1
release that uses the new project naming (with no functional
changes).

Hopefully this reduces the misdirected support calls. :)

If people do keep on calling Hwaci regarding our app, feel free
to direct them to our GitHub issues page.  Or have them email me
and I'll try to point them in the right direction.

Regards and best wishes,

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

Re: [sqlite] SQLite Database Browser v3.3.0 released

[justin]

On 2014-08-26 16:18, jus...@postgresql.org wrote:

On 2014-08-25 17:04, Richard Hipp wrote:

Thank you for creating this project.  I downloaded a copy.  It seems
very nice.

NOW, PLEASE CHANGE THE NAME!!!

"SQLite" is a trademark.  You are welcomed and encouraged to use the
code for SQLite, but not the name "SQLite".

This is not just a legal exercise.  A project like your "SQLite
Database Browser" will end up in the hands of many people who think
that your GUI is in fact the real SQLite and not just a GUI 
front-end

to SQLite.  And so if they encounter problems, they will contact the
SQLite developers for support.  The office phone number here is not
difficult to find with a Google search, and quite a few SQL-newbies
manage to find it, and ring me up.  Then I have to explain to them
that the program they are using is not in fact SQLite but is a
third-party wrapper around SQLite and that they will need to contact
the developers of the third-party wrapper to get support and that, 
no,

I cannot give them the phone number because I dont know what it is. 
Many callers get this.  Others, not so much.  A few are downright
rude.  But in every case, I have to deal with the issue, which takes
time away from working on SQLite.  The experience is also 
frustrating

for the users of your GUI.

Suggested new name:  "Database Browser for SQLite"

Moving the word "SQLite" into a prepositional phrase makes it clear 
to
(most) people that your program is an add-on to SQLite and not 
SQLite

itself.  I think that will be sufficient to solve the problem
described above.


This is in process now:

  https://github.com/sqlitebrowser/sqlitebrowser/pull/83

Might need a few code extra code tweaks (unsure), but we'll get it
done and push out an updated release with the new name sometime
in the next few days. :)

Regards and best wishes,

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

Re: [sqlite] SQLite Database Browser v3.3.0 released

[justin]

On 2014-08-25 17:04, Richard Hipp wrote:

Thank you for creating this project.  I downloaded a copy.  It seems
very nice.

NOW, PLEASE CHANGE THE NAME!!!

"SQLite" is a trademark.  You are welcomed and encouraged to use the
code for SQLite, but not the name "SQLite".

This is not just a legal exercise.  A project like your "SQLite
Database Browser" will end up in the hands of many people who think
that your GUI is in fact the real SQLite and not just a GUI front-end
to SQLite.  And so if they encounter problems, they will contact the
SQLite developers for support.  The office phone number here is not
difficult to find with a Google search, and quite a few SQL-newbies
manage to find it, and ring me up.  Then I have to explain to them
that the program they are using is not in fact SQLite but is a
third-party wrapper around SQLite and that they will need to contact
the developers of the third-party wrapper to get support and that, 
no,

I cannot give them the phone number because I dont know what it is. 
Many callers get this.  Others, not so much.  A few are downright
rude.  But in every case, I have to deal with the issue, which takes
time away from working on SQLite.  The experience is also frustrating
for the users of your GUI.

Suggested new name:  "Database Browser for SQLite"

Moving the word "SQLite" into a prepositional phrase makes it clear 
to

(most) people that your program is an add-on to SQLite and not SQLite
itself.  I think that will be sufficient to solve the problem
described above.


Oh, interesting thought.  Obviously creating hassles for yourselves
is kind of the opposite of what we had in mind. :)


Thank you for your understanding and for your prompt attention to 
this

detail!


No worries.  I'll communicate this to the rest of the team.  I don't
think it'll be too hard to do for the pieces we control (eg 
application,

website, some of the external project pages and similar).

Not sure how long it'll take for the information to propagate out, but
hopefully "Not too long". ;)

Regards and best wishes,

Justin Clift

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

[sqlite] SQLite Database Browser v3.3.0 released

[justin]

Hi all,

SQLite Database Browser v3.3.0 has been released. :)

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.3.0

This has several bug fixes in it around table parsing,
which fixes several "it crashes when I open my db" type
problems. (Oops) ;)

Plus a lot of general improvements, and we've begun
going multi-linugal. (English, Russian, German so far)

Hope that's of use to some people.  And if anyone's
interested in helping out with further translations
that'd be very welcome. :)

Regards and best wishes,

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

[sqlite] SQLite Database Browser v3.2 released

[justin]

A quick FYI.  SQLite Database Browser v3.2 has been
released.  This version has a OSX .dmg binary for
download as well as the Windows .exe + Linux/BSD/etc
compatible source.

  
https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/sqlb-3.2.0


Hope that's useful for people (there's a lot of
improvements over v3.1). :)

Regards and best wishes,

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

Re: [sqlite] Fwd: [fossil-users] DRH's PGCon 2014 Keynote (with Fossil sighting!)

[justin]

Just watched it.  Very cool. :D

+ Justin


On 2014-06-02 16:47, Andreas Kupries wrote:

-- Forwarded message --
From: Joel Bruick <j...@joelface.com>
Date: Sun, Jun 1, 2014 at 4:20 PM
Subject: [fossil-users] DRH's PGCon 2014 Keynote (with Fossil 
sighting!)

To: Fossil SCM user's discussion <fossil-us...@lists.fossil-scm.org>


I just wanted to share Richard's PGCon 2014 keynote for anyone here
that wasn't aware of it:

https://www.youtube.com/watch?v=ZvmMzI0X7fE

It's a great talk about the historical relationship between SQLite 
and

PostgreSQL as well as a celebration of the usefulness of SQL. Richard
goes through several specific examples of how SQLite is currently or
could potentially be used as a superior application file format to a
"pile of files." He also throws in a dig at NoSQL databases that
anyone who appreciates both SQL and the general concept of truth will
find highly amusing.

Near the end of the speech (around the 32:30 mark), a certain unnamed
SQL-based SCM makes a special appearance (spoiler alert: it's 
Fossil).


Thanks, Richard, and everyone else, enjoy!



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

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[justin]

On 2014-05-21 08:36, Tim Streater wrote:


Of course, it might be lying in its teeth for all I know, but the fan
did wind up.


Nah, it wasn't lying.  Qt is a huge amount of code,
and Homebrew uses all available processor cores to
compile it.  And it still take ages. ;)

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

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[justin]

On 2014-05-20 21:35, Tim Streater wrote:


I followed the instructions at the github URL, and was able to build
sqlitebrowser. Couple of points:

1) Warn folks not to have a single-quote (') in their path, it causes
the make of sqlitebrowser to fail. I moved the folder elsewhere and
redid the qmake, make, steps and it completed.


Thanks, that's good info.  Created a bug report for it:

  https://github.com/sqlitebrowser/sqlitebrowser/issues/10

... and updated GitHub+website with the info (including working on OSX
10.9).



2) It took 57 minutes on a 2011 2.3GHz Core i5 Mini to build qt
(whatever that may be) although perhaps a bit of that might have been
it waiting for me to agree the licence (I was out of the room). That
wound the CPU up to 400%, the fan to 6000rpm, and the core temp to
90C. Still, it seems fine.


Yeah, Qt is huge. ;)  It's a (generally) very high quality cross
platform window library.  There are smaller alternatives, but the
end result from them (in my opinion) just isn't as good.



I also had to install brew, but that was quite quick.

Now I'll have to figure out how to use it.


Hopefully it's mostly straight forward.  If you encounter bugs
or have suggestions, please feel free to open an Issue on GitHub
(preferred) or email. :)

Regards and best wishes,

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

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[justin]

On 2014-05-20 16:35, big stone wrote:

Hi Justin,

What are the necessary steps to rebuild SQLite Database Browser from
source, on a windows 7 PC ?

(I have no Qt experience, so not sure of what to download )


Good question.  We should write up some instructions for that. :)

Rene or Martin (both CC'd) are the C++ guys, so may be able to
help.

I *think* Rene is generating the Windows binaries by cross
compiling from Linux.  The binaries seem to work pretty well,
so (thus far) it's been an ok approach. ;)

I tried quickly myself about 2 weeks ago, but wasn't sure which
bits of what to download (eg msys? mingwin? sqlite-libs?) so
didn't have any success.  Kept with OSX Homebrew instead at
the time. ;)

Anyway, hopefully Rene or Martin can help. :)

Regards and best wishes,

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

[sqlite] SQLite Database Browser officially moved to GitHub

[justin]

Hi all,

Just a small heads up.  The old SQLite Database
Browser project on SourceForge is officially
on GitHub as of today, and we've made a new
release (v3.1) as well.  New website too (using
GitHub pages):

  http://sqlitebrowser.org
  https://github.com/sqlitebrowser/sqlitebrowser

Hope that's useful for people. :)

Regards and best wishes,

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

Re: [sqlite] ResultSetMetaData with javasqlite

[Justin Deoliveira]

Hi Christian,

Thanks for the quick response. I am not sure if this will be of any help 
or not but I wrote this (very crude) patch to get around the problem:

http://files.opengeo.org/sqlite_jni.c.patch

I say "crude" because my C hacking skills leave something to be desired. 
That and it only handles the HAVE_SQLITE3 path, not the HAVE_SQLITE2 or 
HAVE_BOTH paths.

-Justin

Christian Werner wrote:
> Justin Deoliveira wrote:
> 
> Justin,
> 
>> I have ran into an issue while using the javasqlite jdbc wrapper.The
>> issue can be illustrated with the following code:
>> ...
> 
> you're right, I've verified that behaviour with SQLite 3.x databases.
> The problem cannot be observed with SQLite 2.x databases.
> 
>> Basically it seems that the result set metdata is not properly
>> initialized when a query returns no rows.
>>
>> I guess my question is is this a bug or is it intended behavior?
> 
> Unintended consequences but present since the initial support
> for SQLite 3.x in 2004. Thus let's call it a misfeature. I'm
> working on a solution.
> 
> Best regards,
> Christian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ResultSetMetaData with javasqlite

[Justin Deoliveira]

Hi all,

I have ran into an issue while using the javasqlite jdbc wrapper.The 
issue can be illustrated with the following code:

import java.sql.*;

public class Main {

   public static void main( String[] args ) throws Exception {

 Class.forName( "SQLite.JDBCDriver");
 Connection cx = DriverManager.getConnection("jdbc:sqlite:/foo.db");

 Statement st = cx.createStatement();
 st.execute( "DROP TABLE IF EXISTS t1");
 st.execute( "DROP TABLE IF EXISTS t2");
 st.execute( "CREATE TABLE t1 (a int, b varchar)");
 st.execute( "CREATE TABLE t2 (a int, b varchar)");
 st.execute( "INSERT INTO t1 VALUES (1,'one')");

 ResultSet rs = st.executeQuery("SELECT * FROM t1");
 ResultSetMetaData md = rs.getMetaData();
 System.out.println("Number of columns t1 = " + md.getColumnCount());
 rs.close();

 rs = st.executeQuery("SELECT * FROM t2");
 md = rs.getMetaData();
 System.out.println("Number of columns t2 = " + md.getColumnCount());
 rs.close();

 st.close();
   }
}

Basically it seems that the result set metdata is not properly 
initialized when a query returns no rows.

I guess my question is is this a bug or is it intended behavior?

Implementing a similar program with the C api I am able to get the 
proper count of columns back so I would assume a bug.

I should note that I am running javasqlite 20090409 with sqlite 3.6.12 
on mac os x leopard.

Thanks,

-Justin

-- 
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multiple row insert

[Justin Lolofie]

Is there syntax to do multiple row insert? Something like:

insert into my_table (a,b,c) values ((1,2,3),(4,5,6));

The documentation for INSERT seems to imply this is not possible.

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

[sqlite] sqlite and NAN

[Justin Greenfield]

I'm trying to figure out how to insert and retrieve a NAN value from  
an sqlite database. I'm on Mac OS X 10.5 which is using sqlite3 3.4.0.

Obviously, using the sqlite3 command line tool, this is what I get:

$ sqlite3 nantest.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> CREATE TABLE IF NOT EXISTS nantest (x double);
sqlite> INSERT INTO nantest VALUES(123.4);
sqlite> INSERT INTO nantest VALUES(NAN);
SQL error: no such column: NAN

Ok, so maybe it's my lack of knowledge of SQL syntax, but I really  
want to do this from code, so on to a more relevant example.

Here's a sample of my code:

sqlite3* db;
sqlite3_open("nantest.db", );
sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS nantest (x double);",  
0,0,0);
sqlite3_stmt* stmt;
char* nanChars = "INSERT INTO nantest VALUES(?);";
int rc = ::sqlite3_prepare(db, nanChars, -1, , 0);
rc = ::sqlite3_bind_double(stmt, 1, 123.4);
rc = ::sqlite3_step(stmt);
rc = ::sqlite3_reset(stmt);

rc = ::sqlite3_prepare(db, nanChars, -1, , 0);
rc = ::sqlite3_bind_double(stmt, 1, NAN);
rc = ::sqlite3_step(stmt);
rc = ::sqlite3_reset(stmt);

rc = ::sqlite3_prepare(db, "SELECT x FROM nantest;", -1, , 0);

while ((rc = ::sqlite3_step(stmt)) == SQLITE_ROW)
{
double x = ::sqlite3_column_double(stmt, 0);
printf("%5.5f %d\n", x, isnan(x));
}

sqlite3_close(db);


Binding NAN to a statement works fine.  The inserts are successful.   
But what I put in as NAN comes back out as 0.0.

Is there any way to insert a NAN and get a NAN back out of an sqlite  
database?

Thanks,
Justin

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

Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

[Justin Fletcher]

[EMAIL PROTECTED] wrote:

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:

This allowed me to get the benefits of the single source file (more compiler
optimizations, etc.) while keeping the manageability, etc. of the separate
source file.



I'm still having trouble trying to understand how managing 60
separate code files is perceived to be easier than managing 
just 2 files (sqlite3.c and sqlite3.h).  It seems to me that
the management problem gets much easier the fewer files there 
are to manage.


I know that for my own projects, the use of the amalgamation
has greatly simplified configuration management.  Now I have
just two files (sqlite3.[ch]) that I drop into my project
and I'm done.  Before I had to either create an external
dependency on a external SQLite library and manage that, 
or import 60+ code files in to a subdirectory with its own 
makefile, etc.  What a pain.  


Chris Peachment points out that his "Pelles C" compiler (never
heard of that one before, but presumably it has its purposes) 
is unable to compile sqlite3.c,  presumably because the file

is is too large.  This is the only reason I have heard yet
for wanting to use separate source file that makes any sense
to me.

Can somebody please explain to my how 2 files is less manageable
than 60?


Having multiple files means that there is sub-division of purpose. If there
is known to be a problem with the function handling then one can find the
code in func.c. Recompilation times can be considerably less if changes can
be isolated to 1 file out of 60, with the remaining 59 being unchanged and
therefore not requiring recompilation. With an amalgamation, one is forced
to recompile the entire project for any single change.

I would have thought that if you were so convinced that the reduced numbers
of files were easier then you would have abandoned the distribution which
used 60 files entirely, and switched to developing with just the 2 files.
That you have not leads me to think that you find the 60 files more
manageable than 2.

In your example above you've cited that dropping a pair of files on to your
project has made things easier for you. I don't doubt this and I quite
agree. Personally I feel that having a dependency on a library is far more
desireable for a project, rather than direct inclusion of the source, but
that's a build issue, rather than a distribution issue. However, the point
you're making is from a black-box user's point of view. The user doesn't
care how it works or what it does, but just that it does what they want.

Other users - those who don't treat it as a black box and wish to extend
functionality or develop more usefully from the code base, and those who are
(because of the environment they must work in) forced to make changes to the
sources - are far more comfortable with a distribution that contains
multiple files split up as a developer might want to work with.

I would have said that there is scope for the 3 different forms of 
distribution :


  * The base distribution that is used for users who can run the parser.

  * The pre-processed distribution, as before, for those users who wish to 
work with the sources, but don't necessarily want to (or are unable to, 
because of their environment) run the parser.


  * The amalgamation distribution, as we have now, for those users who 
treat the database as a black-box and never wish to know any more than that 
it compiled and works.


--
Justin Fletcher, Senior Software Engineer
Picsel Technologies Ltd Tel: +44 141 8855588
Titanium House, Kings Inch Rd, Glasgow, G51 4BP, UK Web:  www.picsel.com

This email is subject to disclaimer at http://www.picsel.com/disclaimer.html



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] changes() function discrepancy

[Justin Fletcher]

Hiya,

I'm examining the test suite and I'm trying to reproduce the results for 
test laststmtchanges-1.1 (and friends). This test creates and populates

a small database as follows :

create table t0 (x);
insert into t0 values (1);
insert into t0 values (1);
insert into t0 values (2);
insert into t0 values (2);
insert into t0 values (1);
insert into t0 values (1);
insert into t0 values (1);
insert into t0 values (2);
select changes(), total_changes();

It then expects back results of "1 8" because the last insert operated on a 
single row (that is, exposing sqlite3_changes()) and because the total 
number of changes to the database is 8 (exposing sqlite3_total_changes()).


In my own code the sequence generates "2 8". I've tried reproducing the
sequence in the shell and I get a curious result. A copy of my attempts to 
reproduce the problem follows :


8<
[EMAIL PROTECTED]:~/sqlite-3.3.16$ ./sqlite3 temp.db
SQLite version 3.3.16
Enter ".help" for instructions
sqlite> create table t0 (x);
sqlite> insert into t0 values(1);
sqlite> select changes(), total_changes();
1|1
sqlite> .quit

[EMAIL PROTECTED]:~/sqlite-3.3.16$ rm temp.db
[EMAIL PROTECTED]:~/sqlite-3.3.16$ ./sqlite3 temp.db
SQLite version 3.3.16
Enter ".help" for instructions
sqlite> create table t0 (x);
sqlite> insert into t0 values(1); select changes(), total_changes();
2|1
8<

The second of these two results shows that the values of changes() may 
exceed the total number of changes made which seems odd in its own right.


From my own experiments changes() appears to be double the expected value 
when it is used in the second form, for example :


8<
sqlite> create table t0 (x);
sqlite> insert into t0 values (1);
sqlite> insert into t0 values (1);
sqlite> insert into t0 values (2);
sqlite> insert into t0 values (2);
sqlite> insert into t0 values (1);
sqlite> insert into t0 values (1);
sqlite> insert into t0 values (1);
sqlite> insert into t0 values (2);
sqlite>
sqlite> update t0 set x=9 where x=1; select changes(), total_changes();
10|13
8<

Is there a reason for this seeming discrepancy in the execution of the 
changes function ?


--
Justin Fletcher, Senior Software Engineer
Picsel Technologies Ltd Tel: +44 141 8855588
Titanium House, Kings Inch Rd, Glasgow, G51 4BP, UK Web:  www.picsel.com

This email is subject to disclaimer at http://www.picsel.com/disclaimer.html


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with understanding the test suite

[Justin Fletcher]

[EMAIL PROTECTED] wrote:

Justin Fletcher <[EMAIL PROTECTED]> wrote:

do_test select1-11.2.2 {
   execsql2 {
 SELECT * FROM t3, t4;
   }
} {a 3 b 4 a 3 b 4}

Can someone explain how the test can be correct ?



This comes about because of duplicate column names.
The execsql2 procedure looks at all column names in
the result set, then returns the value associated with
each column name.  So in the example above, it looks
at columns named "a", "b", "a", and "b", in that order.
But both "a" and "b" are ambiguous names.  So it picks
the value that corresponds to the column that is last
in the list.

This is goofy, perhaps, but it is the way the test
system works.


That's good because it means that I'm not going crazy -
thanks :-)

As test select1-11.2.1 already produced a useful result
for this class of test and 11.2.2 provides no additional
tests, it is probably redundant as a test there - the
test it's performing is actually being obscured by the
goofy test system :-|

I'm working my way through the test suite and will need
to identify such cases. Would it be useful to report these
in order that they can be flagged as being obscured by the
test system ?

--
Justin Fletcher, Senior Software Engineer
Picsel Technologies Ltd Tel: +44 141 8855588
Titanium House, Kings Inch Rd, Glasgow, G51 4BP, UK Web:  www.picsel.com

This email is subject to disclaimer at http://www.picsel.com/disclaimer.html


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Problem with understanding the test suite

[Justin Fletcher]

Hi,

I'm doing some work with the SQLite test suite and I'm confused as to the 
purpose or correctness of a couple of the tests. A few tests have shown

up odd issues, but the following is typical of the general case.

8<
do_test select1-11.1 {
  execsql {
DELETE FROM t3;
DELETE FROM t4;
INSERT INTO t3 VALUES(1,2);
INSERT INTO t4 VALUES(3,4);
SELECT * FROM t3, t4;
  }
} {1 2 3 4}
do_test select1-11.2.1 {
  execsql {
SELECT * FROM t3, t4;
  }
} {1 2 3 4}
do_test select1-11.2.2 {
  execsql2 {
SELECT * FROM t3, t4;
  }
} {a 3 b 4 a 3 b 4}
8<

My understanding of these descriptions is that ...
  'do_test   '
 - Runs the code in  and compares its results to .
   This is collectively known as test .
  'execsql '
 - Runs the SQL in  and accumulates the results in a list,
   each column's results being appended to the results. Row boundaries
   are ignored.
  'execsql2 '
 - As execsql, above, but precedes each value by the name of the column.
   This is similar to '.mode line' at the SQL command line.

Test select1-11.1, above, populates two tables with values. The first has
the a row with 2 columns, containing 1,2; the second has a row with 2 
columns containing 3,4. It then selects the values from these. The result,

'1 2 3 4' matches the expected result.

Test select1-11.2.1 selects all elements from the list, as above. Again,
it produces the expected results '1 2 3 4'.

Test select1-11.2.2 is the test I have a problem with. The same command is
used, but is expected to precede each column with the column name. I expect
the result to be 'a 1 b 2 a 3 b 4'. The test suite expects (and gets)
'a 3 b 4 a 3 b 4'.

Either this means that my understanding of execsql2 is wrong, or the test 
suite is incorrectly testing for this case.


To be a little clearer, I equate the final test to the following sequence
in the sqlite command line :

sqlite> CREATE TABLE t3(a,b);
sqlite> CREATE TABLE t4(a,b);
sqlite> .mode line
sqlite> SELECT * FROM t3,t4;
sqlite> INSERT INTO t3 VALUES(1,2);
sqlite> INSERT INTO t4 VALUES(3,4);
sqlite> SELECT * FROM t3,t4;
a = 1
b = 2
a = 3
b = 4
sqlite>

Can someone explain how the test can be correct ?



The execsql2 function in tester.tcl is as follows :

8<
# Another procedure to execute SQL.  This one includes the field
# names in the returned list.
#
proc execsql2 {sql} {
  set result {}
  db eval $sql data {
foreach f $data(*) {
  lappend result $f $data($f)
}
  }
  return $result
}
8<

My understanding of TCL is very very rough, but I believe that the loop
'foreach f $data(*)' loops over every column name and prepends the column
name to those values in the result string. As the column names match in
the results, I think that gives the strange results.

--
Justin Fletcher, Senior Software Engineer
Picsel Technologies Ltd Tel: +44 141 8855588
Titanium House, Kings Inch Rd, Glasgow, G51 4BP, UK Web:  www.picsel.com

This email is subject to disclaimer at http://www.picsel.com/disclaimer.html


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Installation with no root access

[Justin Adie]

i use a host which has  not compiled php5 with sqlite.  i have no telnet
or ssh access but I can use the php eval and shellexec commands to do
things on the server side.
 
for example, I am able to compile sqlite on the server (so it seems at
any rate).
 
i can't work out how to get php working with the sqlite that i have
compiled, however.  where do i get the shared object from (gnu libc
2.2.5)?  how do i marry that object with the sqlite that i have compiled
(it is (of course) not in the normal directory but in the web tree).
 
fyi a previous post recommended a live cd and to compile eveything on a
local server and upload.  i have tried 4 live cd distros and none will
boot on my computer (external cd rom) so have lucked out on that front.
 
basically i'm looking for a way to use sqlite with php on a shared host
that does not support it themselves.  
 
is this possible?
 
many thanks
Justin
 

[sqlite] database table is locked when trying to delete a record after select

[Justin Wu]

It will be always get 'database table is locked' message when trying to
delete
a record after select.

I'v tested on WinXP and Ubuntu 5.10, but got the same error.

SQLite version v3.2.8

please see the following code

#include 
#include 
#include 

#define DATFILE "test.dat"

int main(int argc, char *argv[]) {
   // sqlite
   sqlite3 *db = 0;
   sqlite3_stmt *stmt = 0;
   char *sql;
   int rc;
   int id = 0;

   // Open the database
   if (SQLITE_OK != sqlite3_open(DATFILE, )) {
   printf("!!! Couldn't open the database - %s\n", DATFILE);
   exit(1);
   }

   sqlite3_busy_timeout(db, 3000);

   if (SQLITE_OK != sqlite3_prepare(db, "SELECT ProxyId, Host, Port FROM
ss_proxy", -1, , 0)) {
   printf("!!! sqlite3_prepare::%s", sqlite3_errmsg(db));
   sqlite3_close(db);
   exit(1);
   }

   //sqlite3_exec(db, "BEGIN;", 0, 0, 0);
   while (SQLITE_ROW == sqlite3_step(stmt)) {
   id = sqlite3_column_int(stmt, 0);
   printf("*** %d. %s:%d\n", sqlite3_column_int(stmt, 0),
sqlite3_column_text(stmt, 1), sqlite3_column_int(stmt, 2));
   // i will do something with every record, currently simple use '(id
==
1742 || id == 1743)' instead of
   if (id == 1742 || id == 1743) {
   //sqlite3_reset(stmt);
   // database table is locked - [rc=6]
   sql = sqlite3_mprintf("DELETE FROM ss_proxy WHERE ProxyId=%d;",
id);
   rc = sqlite3_exec(db, sql, 0, 0, 0);
   if (SQLITE_OK != rc) {
   printf("*** delete failed - %s - [rc=%d]\n", sqlite3_errmsg
(db), rc);
   } else {
   printf("*** %d record deleted!\n", sqlite3_changes(db));
   }
   sqlite3_free(sql);
   }
   };
   //sqlite3_exec(db, "COMMIT;", 0, 0, 0);

   sqlite3_finalize(stmt);
   // Close the database
   sqlite3_close(db);
   return 0;
}

Re: [sqlite] Dotnet C# support

[Justin Greenwood]

I've used the C# .net provider for SQLite quite a bit. You can use SQL3 
with these providers, you just have to add something to the connection 
string to tell the driver which version you're using and also the text 
encoding (UTF8Encoding=true;Version=3;). To create a new database, you 
just have to add the "New=True" key/value pair to the connection string. 
Here are some links to get you started:


Microsoft.Net 1.x ADO.Net Provider
http://sourceforge.net/projects/adodotnetsqlite

Here's some sample code:
---

SQLiteConnection Conn = new SQLiteConnection();
Conn.ConnectionString = "Data 
Source=diary.db;New=True;Compress=True;Synchronous=Off";

Conn.Open();

SQLiteCommand Cmd = new SQLiteCommand();
Cmd = Conn.CreateCommand();
Cmd.CommandText = "CREATE TABLE GOALS(GOALS_ID integer primary key , 
CATEGORY varchar (50), PRIORITY integer , SUBJECT varchar (150) , 
DESCRIPTION varchar (500),START_DATE datetime , COMPLETION_DATE datetime)" ;

Cmd.ExecuteNonQuery();

Cmd.CommandText="CREATE TABLE NOTES (NOTES_ID integer primary key 
,NOTES_DATE datetime ,NOTES_TEXT varchar (8000) )";

Cmd.ExecuteNonQuery();

Cmd.CommandText =" CREATE TABLE REMINDERS (REMINDER_ID integer primary 
key ,REMINDER_DATE smalldatetime ,SUBJECT varchar (150) ,DESCRIPTION 
varchar (500) , ALARM1_DATE datetime ,ALARM2_DATE datetime ,ALARM3_DATE 
datetime ,EMAIL_ALARM bit )";

Cmd.ExecuteNonQuery();

Cmd.CommandText ="CREATE TABLE TODO ( TODO_ID integer primary 
key,CATEGORY varchar (20),PRIORITY int, PERCENT_COMPLETE float, 
START_DATE datetime ,END_DATE datetime , SUBJECT varchar (150) , DETAILS 
varchar (8000) ";

Cmd.ExecuteNonQuery();

Cmd.CommandText ="CREATE TABLE CATEGORIES (CATEGORY_ID INTEGER PRIMARY 
KEY,CATEGORY_NAME varchar (25))";

Cmd.ExecuteNonQuery();

Cmd.Dispose();
Conn.Close();
-

There is also a new .net 2.0 provider:

Microsoft.Net 2.0 ADO.Net Provider
http://sourceforge.net/projects/sqlite-dotnet2



Gregory Letellier wrote:


i'm trying tu use sqli3 with vb. net
he create the db but not the table..
what is the mistake ?

i've this code

Imports System.Runtime.InteropServices

Public Class Form1
  _

   Public Structure sqlite_callback
   Public Void As Long
   Public I1 As Long
   Public s1 As String
   Public s2 As String
   End Structure

   Public Declare Function sqlite3_open Lib "sqlite3.dll" (ByVal 
Filename As String, ByRef Handle As Long) As Long
   Public Declare Function sqlite3_exec Lib "sqlite3.dll" (ByVal 
Handle As Long, ByVal Query As String, ByRef CallbackFunction As 
sqlite_callback, ByRef CallBackArgs As Long, ByRef Erreur As String) 
As Long
   Public Declare Function sqlite3_close Lib "sqlite3.dll" (ByVal 
Handle As Long) As Long
   Public Declare Function sqlite3_errmsg Lib "sqlite3.dll" (ByVal 
Handle As Long) As String

 Public Sub Main()

   Dim lRet As Long
   Dim lHandle As Long
   Dim sErreur As String
   Dim sSQL As String

   lRet = sqlite3_open("c:\test.db", lHandle)

   sSQL = "CREATE Table Toto(titi varchar(15));"
   lRet = sqlite3_exec(lHandle, sSQL, Nothing, Nothing, sErreur)
  sqlite3_close(lHandle)
   End Sub
End Class



Darren Lodge a écrit :


Thankyou!

Darren Lodge
Software Engineer
CAP

0113 222 2058 (direct)
0113 222 2000 (switchboard)
0113 222 2001 (fax)

-Original Message-
From: Peter Berkenbosch [mailto:[EMAIL PROTECTED] Sent: 28 October 
2005 09:19

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dotnet C# support

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sigh..

http://www.google.com/search?hl=nl=SQLite+C%23=Google+zoeken
=


Darren Lodge wrote:
 


Hi there,

Is there a version which will work for dotnot?

Darren Lodge
Software Engineer
CAP

0113 222 2058 (direct)
0113 222 2000 (switchboard)
0113 222 2001 (fax)


  




- --
+---+--+
: Peter Berkenbosch:   :
:: t: +31 (0) 64 84 61653   :
: PeRo ICT Solutions: f: +31 (0) 84 22 09880   :
: Koemaad 26: m: [EMAIL PROTECTED] :
: 8431 TM Oosterwolde: w: www.pero-ict.nl   :
+---+--+
: OpenPGP 0x0F655F0D (random.sks.keyserver.penguin.de)   :
+--+





-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFDYd7g9bwznA9lXw0RAnb4AJ9oRUkvbXX5aX0HhXZEl6Lv4KNPyACgiUrq
yXTEUWDFVPk97iM5u14V1B4=
=0ECQ
-END PGP SIGNATURE-




 

Re: [sqlite] Object Relational Mapping Tools with Sqlite

[justin . greenwood]

MyGeneration is a template based code generator that runs in the Win32
environment. It is 100% free and supports SQLite. There is not yet a set
of templates that generate code for a specific SQLite supported framework,
but the Meta-Data API supports SQLite, so writing your own templates
should be a fairly simple task.

http://www.mygenerationsoftware.com/


> On Thu, 30 Dec 2004 10:41:50 -0800, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
>> Has anyone been successful using an object relational mapping tool with
>> Sqlite?  I'm looking for such a tool that has a non-commercial free
>> license.
>
> For what language?
>
>
> Kirk Haines
>

[sqlite] Freeware code generator now supports SQLite

[justin . greenwood]

MyGeneration software has added SQLite support to thier freeware template
based code generator.

http://www.mygenerationsoftware.com/

MyGeneration has a generic meta-data API that supports 9 databases with
more on the way. The meta-data (which includes columns, indeces, foreign
keys, etc) is made available in the templates which are written in
JScript, C#, VBScript, or VB.Net. among other things, you can generate a
middle tier, SQL scripts, documentation, export data, etc.

I personally added the support for SQLite to MyGeneration, so if anyone
out there finds it useful, please send me some feedback. I havn't had much
of a test group. In fact, I'm the only guy that's tested it so far. ;) I
also wrote a short article about collecting meta data from SQLite which
can be found on my blog:

http://justingreenwood.blogspot.com/2004/10/sqlite-100-free-database.html

I love SQLite! I'm going to be using it in my next project. Thanks to all
the people that are maintaining it!