[sqlite] Is this behavior expected?

[Baruch Burstein]

SQLite version 3.20.1 2017-08-24 16:21:36
sqlite> create table T(C);
sqlite> insert into T values("test 1");
sqlite> select last_insert_rowid();
1
sqlite> begin;
sqlite> insert into T values("test 2");
sqlite> select last_insert_rowid();
2
sqlite> rollback;
sqlite> select last_insert_rowid();
2

In other words, the rollback doesn't roll back the rowid.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] caching sqlite3_db_mutex()

[Baruch Burstein]

Hi,

Is it safe to cache the mutex pointer returned by `sqlite3_db_mutex(sqlite3*)`?
Can/does the mutex pointer change thought the life of the `sqlite3` object?

Thanks,
Baruch

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] locating a minimum in SQLite 2

[Baruch Burstein]

On Thu, Dec 1, 2016 at 9:12 PM, James Walker 
wrote:

> Let's say I have a table INFO with columns PRICE and IDENT, and I want to
> find the IDENT of the row with the minimum value of PRICE.  In SQLite 3, I
> can say
>
> SELECT MIN(PRICE), IDENT FROM INFO;
>
> and get what I want.  But in SQLite 2 (legacy code), this doesn't work...
> I get the minimum value, but NULL in the IDENT column.  I could say
>
> SELECT PRICE, IDENT FROM INFO ORDER BY PRICE;
>
> and ignore all but the first row of the result, but I'm sure there must be
> a better way?


LIMIT 1?

˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does PRIMARY KEY imply NOT NULL?

[Baruch Burstein]

On Thu, Sep 10, 2015 at 12:04 AM, John McKown 
wrote:

> On Wed, Sep 9, 2015 at 3:56 PM, Baruch Burstein 
> wrote:
>
> > Question in the subject
> >
> > --
> >
> >
> ?Answered on the SQLite web site: http://sqlite.org/lang_createtable.html


Thank you. I knew I had seen something about this once, but didn't find it.


-- 
?u?op-?p?sdn s? ?o??uo? ?no? 's??? p??? u?? no? ??

[sqlite] Does PRIMARY KEY imply NOT NULL?

[Baruch Burstein]

Question in the subject

-- 
?u?op-?p?sdn s? ?o??uo? ?no? 's??? p??? u?? no? ??

[sqlite] Fwd: SAVEPOINT name

[Baruch Burstein]

On Thu, May 21, 2015 at 7:15 PM, Simon Slavin  wrote:
>
>
> Also, savepoint names can't be used as parameters when binding.


Does that mean that I can't prepare this: "SAVEPOINT :name"? What would be
the recommended method of preventing SQL injection for this?

-- 
?u?op-?p?sdn s? ?o??uo? ?no? 's??? p??? u?? no? ??

[sqlite] Fwd: SAVEPOINT name

[Baruch Burstein]

Hi,

What are the restrictions (if any) on a sve point name? Alphanumeric? Same
as a string (enclosed in ' ' if necessary)? Something else?

Thank you,
Baruch

-- 
?u?op-?p?sdn s? ?o??uo? ?no? 's??? p??? u?? no? ??

[sqlite] Inserting/removing large number of rows with index

[Baruch Burstein]

Hi,

If I have a table with an index, and INSERT or DELETE a large number of
rows in one statement, does sqlite stop to update the index for each
record, or is it smart enough to update the index just once for all the
changed records?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Baruch Burstein]

On Wed, Jan 14, 2015 at 3:09 PM, Chris Keilitz  wrote:

> Since sqlite and most RDMS implementations have functions to convert to and
> from both options and using a LONG should allow the date/time to function
> way past 2038, it seems it comes down to how many bytes it takes to store
> the timestamp and how fast are the conversion routines. The application I'm
> writing won't push any performance boundaries and likely won't need to
> overly worry about storage.
>

Just for the reference, to answer your size/performance question: The
timestamp will take 4 bytes of data (excluding headers etc.) per entry
(until 2038, after which it will be 6). The text version will be the length
of the string (20 bytes for ISO8601 with second precision).
Naturally, things like comparing and sorting will be faster with the
timestamp then the text version (there is just less data to compare).
Of course, this is just at the theoretical level. As yo said, your app
probably wouldn't need to worry about this.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion for syntax enhancement for virtual tables

[Baruch Burstein]

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular tables)?

CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Partial index to find maximum

[Baruch Burstein]

Hi,

I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a
given value of 'a', the highest matching 'b'. The query itself it simple:

SELECT max(b) FROM t WHERE a=:whatever

To speed this up, I would add an index on 'a'. Now, the question is is
there some way to tell the index that I am only interested in the maximum
value of b? For example, for the following table:

a|b
1|1
1|2
2|2
2|3

I only need the index to contain the rows (1,2) and (2,3). The docs for
partial indexes say that they can't contain functions (like max()). Any
suggestions?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[Baruch Burstein]

On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> If you are a coder then it is a relatively straight forward process
> along the lines of
>
> Loop through each table
>Loop through each column
>

This is the part I am having trouble with. I can loop through tables using
sqlite3_master, but how do I loop through columns? Parse the schema?


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Search for text in all tables

[Baruch Burstein]

Hi,

Is it possible to somehow search for/replace a string in all columns of all
tables?

Thanks

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing to NULL in prepared statment

[Baruch Burstein]

On Wed, Nov 5, 2014 at 10:52 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hi Baruch,
>
> in such cases I use one of the following methods:
>
> SELECT col1 FROM table1 WHERE col2=:val or (col2 is null and :val is null)
>
> SELECT col1 FROM table1 WHERE coalesce(col2, '#') = coalesce(:val, '#')
>
> where '#' is a value that i know is never used in col2 (this is a drawback)


Thanks. Assuming col2 is indexed, any idea which is faster (I am guessing
the first, but didn't test)


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comparing to NULL in prepared statment

[Baruch Burstein]

Hi all,

This is not really a sqlite specific question, but I was wondering if there
might be a sqlite specific answer.
I prepare the following statement:

"SELECT col1 FROM table1 WHERE col2=:val"

col2 is a textual string, and may sometimes be NULL. If I bind a string to
:val it works fine. If I bind a null-pointer the comparison fails since it
should be using ISNULL. Is there a way to do this correctly with a single
statement? I know I can have 2 statments and test the string pointer at
runtime to determine which one to run, but that seems awkward.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on sqlite handling of mixed aggregate and non-aggregate columns

[Baruch Burstein]

On Wed, Oct 29, 2014 at 8:43 PM, Richard Hipp  wrote:

> > If I have a table, "t", with 2 columns, "a" and "b". Assuming that "a"
> is a
> > unique number, will the following query always return the whole row (that
> > is, with the correct "b" column) where "a" is the highest number below
> 50?
> >
> > SELECT max(a), b FROM t WHERE a<50;
> >
> That is what it is suppose to do, yes.
>

Is there some way to filter *after* this is applied? For example, say in
the above query I only want the rows where "b" is not null (side question:
What is the difference between "ISNULL" \ "NOTNULL" and "IS NULL" \ "IS NOT
NULL"?). So for this data:

a | b
--+--
30|10
40|NULL

I will get no results, but for

a | b
--+--
30|NULL
40|10

I will get

40|10

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Clarification on sqlite handling of mixed aggregate and non-aggregate columns

[Baruch Burstein]

Hi,

If I have a table, "t", with 2 columns, "a" and "b". Assuming that "a" is a
unique number, will the following query always return the whole row (that
is, with the correct "b" column) where "a" is the highest number below 50?

SELECT max(a), b FROM t WHERE a<50;

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index usage for order by and where clauses

[Baruch Burstein]

Hi,

If I have an index on table1(colA, colB), will it be used for both the
where and the order by in either of these cases:

select * from table1 where colA=1 order by colB;
select * from table1 where colB=1 order by colA;

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Finding second occurrence of character in string

[Baruch Burstein]

Hi!

I have a column which represents a file path:

ab/cd/gf
ab/qw/ert
ab/fgrd/ert
ab/foo/bar/fgr
ab/bar/foo/foobar/etc
ab/etc/d
etc...

I happen to know in my case that the first part of the path is a certain
fixed string ('ab' in the above example). I need to get the path with the
first 2 parts stripped off. Currently I am doing:

substr(path, 4+instr(substr(path,4),'/'))

But that seems long and probably inefficient.
What is the best/simplest way to find the second occurrence of the '/' in a
string?

Also, a suggestion for an SQLite improvement: The builtin function instr()
should have another form that takes 3 arguments, with the 3rd being either
an offset from where to start the search, or which occurrence to search for
(1st, 2nd, etc.)

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unbinding parameters

[Baruch Burstein]

Sorry for the noise, but I found it:
https://www.sqlite.org/c3ref/clear_bindings.html

On Thu, Oct 23, 2014 at 2:46 PM, Baruch Burstein <bmburst...@gmail.com>
wrote:

> It says here (https://www.sqlite.org/c3ref/bind_blob.html) that
> a) Unbound parameters are interpreted as NULL, and
> b) Bindings are not cleared by sqlite3_reset()
>
> Is there any way to clear all bindings, so that if I don't set them again
> they will insert NULL?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unbinding parameters

[Baruch Burstein]

It says here (https://www.sqlite.org/c3ref/bind_blob.html) that
a) Unbound parameters are interpreted as NULL, and
b) Bindings are not cleared by sqlite3_reset()

Is there any way to clear all bindings, so that if I don't set them again
they will insert NULL?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'INTEGER PRIMARY KEY' start value

[Baruch Burstein]

Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually
guaranteed to start from 1? Or at least from a positive number?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I know the python functions registered on SQLite ?

[Baruch Burstein]

On Sun, May 11, 2014 at 12:58 PM, Baruch Burstein <bmburst...@gmail.com>
wrote:

>
> On Thu, May 8, 2014 at 11:46 PM, Roger Binns <rog...@rogerbinns.com>
> wrote:
>
>>
>> SQLite could provide the information as a virtual table or similar.  A
>> ticket was created 7 years ago asking for it, and closed 2 months ago by
>> the team with resolution "Rejected":
>>
>>   https://www.sqlite.org/src/tktview?name=5896edbe46
>
>
> Just out of curiosity, why was this rejected? Especially after being left
> open (supposedly "might do someday") for almost 7 years. What changed? It
> seems like a logical request.
>

Any explanation? I ran into this issue today when using the fossil built-in
sqlite shell, and I thought I remembered that it has a function registered
for getting a raw blob, but couldn't remember the name. So I was looking
for a sqlite shell dot-command for listing registered functions, but of
course there is no such thing because it would need this API implemented. I
had to go dig in the fossil code to figure it out.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite on flash filesystem

[Baruch Burstein]

On Fri, Aug 15, 2014 at 10:45 AM, Levente  wrote:

> I am thinking about putting an SQLite database on a flash drive (SD card).
> I would like to know what kind of file system is the optimal. I want to
> query a lot, and insert or update a few times per minute.
>

Not directly related to your question, but I am curious: Since sqlite's
atomicity  guarantee assumes that write to a disk sector are linear (e.g.
either beginning to end or end to beginning, but never the middle before
either end), I was wondering if this assumption was ever verified for flash
drives. I have no information to contrary, but while that assumption makes
a lot of sense for a spinning disk, I see no reason to assume this for
flash drives and other SSDs. I would think that they may parallelize writes
to different parts of the "sector" for efficiency.
I don't have any knoladge to base this on, I just don't know if this was
ever verified with actual flash drive driver manufacturers.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite.net (a.k.a. System.Data.SQLite) support

[Baruch Burstein]

This is not technically sqlite specific, rather .net in general, but it
came up using sqlite.net, so I am asking here. If someone can point me to a
general answer elsewhere, that would be great, too.
I downloaded and "installed" (read: copied) the files to my project. I can
create connections, commands, etc. but how do I get it to recognize my data
structure? I only need to display a single table (with editing) in a
datagrid, but it is very frustrating when I don't have any of the famed
Visual Studio support for my types. Even if I create a structure manually
that represents a single row, how do I load the data into it? How do I bind
the grid to it? How do I search? I am not very familiar with C#, and the
little I have done always involved connecting to a "datasource", which
auto-populates everything, from creating a class to hold a row to Linq
intellisense support (I suspect the two are related). How do I get this to
work if I "Xcopy" installed, as recommended on the website?

Basically, can someone point me to a resource on using databases in C# only
via manual code?

Baruch

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Baruch Burstein]

On Wed, May 28, 2014 at 9:43 PM, Richard Hipp  wrote:

> On Wed, May 28, 2014 at 1:20 PM, jose isaias cabrera
> wrote:
>
> >
> > 3. Is there a spot anywhere that has clear steps on creating the Sqlite3
> > DLL?
> >
>
> http://www.sqlite.org/draft/howtocompile.html#dll
>
> The "draft" page above will be promoted to the official website at the next
> release.

A comment on the draft: mingw64 has 32-bit compilers, too (they even have
32/64 mixed compilers), and they are generally more ahead than mingw as far
as gcc versions (they have had a gcc 4.9.0 compiler since it was in beta)


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Baruch Burstein]

On Wed, May 28, 2014 at 8:09 PM, Richard Hipp  wrote:

> On Wed, May 28, 2014 at 12:56 PM, jose isaias cabrera <
> cabr...@wrc.xerox.com
> > wrote:
>
> >
> > Just noticed something...  It may be nothing, but the MinGW built DLL has
> > a size of 645KB while the MSVC built one has a size of 962KB.  Just under
> > 33% bigger. I hope there is nothing missing on the MinGW one. :-)  It's
> so
> > weird how MS DLLs and programs are always so much bigger in size then
> > non-MS built ones.  Just food for thoughts...
> >
>
> The MSVC DLL is 64-bit and the MinGW DLL is 32-bit.  That accounts for part
> of the difference.  Additionally, MinGW was run with the -Os option
> (optimize for small size) whereas MSVC was run with -O2 (optimize for
> maximum speed).  So MSVC is probably doing lots of function in-lining and
> loop-unrolling that might make the code a little faster, but also makes it
> bigger.
>

I compiled with mingw64 - a 64-bit DLL with -O2 is 650K


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Baruch Burstein]

On Thu, May 29, 2014 at 3:55 AM, RSmith  wrote:

>
> On 2014/05/28 20:26, Warren Young wrote:
>
>> On 5/28/2014 11:20, jose isaias cabrera wrote:
>>
>>>
>>> I would rather have the speed
>>> then the size.
>>>
>>
>>
> Rather speed than size is an Engineering decision which is easy on a
> desktop/server system (such as most Windows/OSX/*nix implementations) but
> it may well be the opposite in a phone or other handheld device or small
> integrated system where IO is so significantly slower and size is paramount.

Since the comment was about the Windows DLL, it seems an easy decision (as
you said yourself) to prefer speed over size.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inconsistency in API

[Baruch Burstein]

Hi,

It seems to me that there is some inconsistency in the API. Some APIs that
take a string have an option to pass the length in order to improve
performance. Some, like sqlite3_prepare*, *include* the terminating NULL in
the length, so for "abcd" the number passed would be 5. Others, like
sqlite3_bind_text*, *don;t* count the terminating NULL, so for the above
string it would be 4.
Is there some fundamental difference between these two APIs that cause this
difference to make sense, am I misunderstanding the docs and they *do* in
fact use the same number (4 or 5?), or is it just an inconsistency that I
should live with?

Baruch

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Baruch Burstein]

On Sun, May 18, 2014 at 10:46 PM, James K. Lowden
wrote:

> On Sun, 18 May 2014 19:15:18 +0200
> RSmith  wrote:
>
> > > As Igor says, http://sqlite.org/c3ref/prepare.html would be
> > > appropriate. However, a database connection is required for this.
> >
> > But of course  What kind of syntactical correctness can you hope
> > to check without a connection?
>
> You could hope for the kind of syntactical correctness that conforms to
> the rules of the syntax.  No schema infomation is required for that.
>
> select A from T where W = 'foo';
>
> is valid syntax.  It might not execute correctly, expecially if there's
> no  table T with columns A and W, but that's not a syntax issue.
>

This is indeed what I was thinking of. A function that would mark the above
statement as 'valid', but mark

select from T where W='foo';
select name from from where 1;

as as 'invalid'.

I don't know if such a function would actually be useful, but I thought I
remembered that it already existed, and so I thought I would use it as a
basis for another function I wanted to write, which would compare 2 SQL
statements for equivalence, even if they don't memcmp() equal. The more I
think of it, though, I think that the solution is as simple as converting
all letters to lower(/upper) case and converting all whitespace to a single
space each, except for within matching [ ], " ", ' ' or ` `. After that, I
can do a memcmp().


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Baruch Burstein]

On Sun, May 18, 2014 at 5:32 PM, Baruch Burstein <bmburst...@gmail.com>wrote:

> Sqlite is case-insensitive as far as table/column/db names. Is this
> documented as official behavior or it may change?
>
> Also, is there a function in the API to validate a SQL statement, either
> in the context of the current connection (validate also table/column/db
> names), or without context (just validate syntax, e.g. that it can be
> parsed)?
>
I am asking about this API since I think I remember seeing it once, but
can't find it now


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is sqlite case-sensitive?

[Baruch Burstein]

Sqlite is case-insensitive as far as table/column/db names. Is this
documented as official behavior or it may change?

Also, is there a function in the API to validate a SQL statement, either in
the context of the current connection (validate also table/column/db
names), or without context (just validate syntax, e.g. that it can be
parsed)?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pre-preparing querys

[Baruch Burstein]

What is the overhead of holding open a prepared statement? If my program is
not time critical at all (it is mostly UI bound), but every once in a while
(anywhere from 10 times a second to once every 10 minutes) it needs to run
a few querys, would it make more sense to prepare all of the querys once at
the start of the program (40-50 different querys for the whole program, but
only 2-3 are run at a time), prepare-step-finalize each time as needed, or
(most likely) it doesn't really make a difference in this situation?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I know the python functions registered on SQLite ?

[Baruch Burstein]

On Thu, May 8, 2014 at 11:46 PM, Roger Binns  wrote:

>
> SQLite could provide the information as a virtual table or similar.  A
> ticket was created 7 years ago asking for it, and closed 2 months ago by
> the team with resolution "Rejected":
>
>   https://www.sqlite.org/src/tktview?name=5896edbe46


Just out of curiosity, why was this rejected? Especially after being left
open (supposedly "might do someday") for almost 7 years. What changed? It
seems like a logical request.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding transactions

[Baruch Burstein]

Thank you for the explanations. If I wrap a few SELECTs in a transaction,
does this guarantee that the data I read will be consistent across all of
the SELECTs?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding transactions

[Baruch Burstein]

I am a little unclear on some of the ways transactions affect multiple
connections. I am assuming that multiple sqlite3 objects in one program is
the same as multiple programs.

1) How does a transaction affect SELECTs? If I start a transaction and do
an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
see? What about a SELECT in a different connection?
2) Can 2 connections run 'BEGIN;' ? If so, who gets the final say on the
data? The first to do the 'COMMIT;'? What will happen when the other does a
COMMIT?

Thanks

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[Baruch Burstein]

On Fri, Sep 13, 2013 at 5:29 AM, Ryan Johnson
wrote:

> -- Join cardinality: Bach was a *very* prolific composer whose output
> likely dwarfs the (surviving) output of his contemporaries
> select p.title, c.name, p.year from composers c join pieces p on p.c_id =
> c.id where c.name like '%bach%' and p.year between 1700 and 1750
>

How would you mark this even using the suggested function syntax? The
likelihood of "c.name like '%bach%' " being true depends on the order the
query optimizer decides to evaluate the 2 predicates in, which in turn
depends on the likelihood of the predicate!


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[Baruch Burstein]

I also think it should not be directly in the SQL. I like the
not-really-a-comment syntax. Another option might be a few PRAGMAs,
something like

PRAGMA hint("table1.col1 IN (1,2,5)", 0.05);
PRAGMA hint("table1.col2 LIKE '%bach%'". 0.4);

these would add the hints to an internal table. When preparing a query, the
planner would check the hints table to see if any hints match the
table/column/condition triplet, and if so optionally use the hint. Removing
a hint and removing all hints would also be a couple of PRAGMAs.


On Wed, Sep 11, 2013 at 3:53 AM, kyan  wrote:

> Hello Dr Hipp,
>
> First of all, I apologize for this rather off-topic suggestion knowing that
> you may have already implemented the syntax you describe, but there is an
> IMHO good reason for it, read ahead.
>
> On Tue, Sep 10, 2013 at 10:26 PM, Richard Hipp  wrote:
>
> > SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE unlikely(cname LIKE '%bach%')
> >AND composer.cid=track.cid
> >AND album.aid=track.aid;
> >
>
> I would prefer that the planner hint is not interleaved inside normal SQL
> syntax. Instead I propose a special comment-like syntax instead, as
> Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">":
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE cname LIKE '%bach%'
> > /*> unlikely */
> >  AND composer.cid=track.cid AND album.aid=track.aid;
> >
>
> or:
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE cname LIKE '%bach%'
> > --> unlikely
> >AND composer.cid=track.cid
> >AND album.aid=track.aid;
>
>
> If the hint is to be applied to an expression that combines many column
> predicates with AND (I am not sure if this actually makes sense):
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE unlikely(cname LIKE '%bach%'
> >AND composer.cid=track.cid)
> >AND album.aid=track.aid;
> >
>
> then a -normally redundant- pair of parentheses can be used to specify the
> scope of the hint:
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE (cname LIKE '%bach%' AND composer.cid=track.cid) /*> unlikely */
> >AND album.aid=track.aid;
> >
>
> The SQLite SQL parser will have to look for exactly "/*>" or "-->" without
> whitespace between the characters, so it can easily tell a planner hint
> from a plain comment with a single character read-ahead. Also, the fact
> that hints are "transparent" to the SQL syntax will allow the query parser
> to handle them in an "orthogonal" way (e.g. a small separate parser for
> hints) to normal SQL parsing, IMO making handling of any future hints
> easier to add.
>
> The main reason for this proposal is that the planner hint will be ignored
> by default by other SQL parsers without the need to modify them, which in
> some cases may not even be possible. For instance it will allow someone to
> write SQL that is valid in databases of alternative DB vendors and still
> provide planner hints when the DB vendor is SQLite (that is why I replaced
> "+" with ">", to avoid conflicts with a hypothetical alternate Oracle query
> optimizer) without having to modify the SQL in the application code to
> remove the hints. This is a property of the Oracle optimizer hint syntax I
> have always appreciated when writing SQL that is to be executed in
> databases of alternative DB vendors with the same schema, for applications
> where the user chooses the database vendor from a list of supported ones.
>
> For more on Oracle optimizer hints see
> http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm.
>
> As for the name of the hint itself I would propose:
>
> --> PROBABLY(True) -- the current default
> --> PROBABLY(False)
> --> PROBABLY(False, 0.7)
> --> PROBABLY(False, 0.6, 0.3)  --re "pedantic detail", the second value if
> for True, the remainder for NULL.
>
> Kind regards,
>
> Constantine Yannakopoulos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a foreign key reference a named rowid?

[Baruch Burstein]

On Mon, Aug 12, 2013 at 9:24 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 08/12/2013 01:20 PM, Baruch Burstein wrote:
>
>>  From the documentation for CREATE TABLE:
>>
>>  The parent key of a foreign key constraint is not allowed to use the
>> rowid. The parent key must used named columns only.
>>
>> Does this mean it can or can't use a named column that is an alias for the
>> rowid? Is the problem using the rowid, or is the problem that if the rowid
>> is unaliased, it can change?
>>
>
> An INTEGER PRIMARY KEY can be a parent key:
>
> Thank you. The documentation should probably be clarified.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can a foreign key reference a named rowid?

[Baruch Burstein]

From the documentation for CREATE TABLE:

The parent key of a foreign key constraint is not allowed to use the
rowid. The parent key must used named columns only.

Does this mean it can or can't use a named column that is an alias for the
rowid? Is the problem using the rowid, or is the problem that if the rowid
is unaliased, it can change?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT with rowid column

[Baruch Burstein]

I ran the following 2 commands:

create table tests (id INTEGER PRIMARY KEY, name TEXT UNIQUE);
> insert into tests values ('test 1');
>

and got the following error:

Error: table tests has 2 columns but 1 values were supplied
>

Since the id column is an alias for the rowid, shouldn't I be able to not
supply it?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 extended API usage

[Baruch Burstein]

Did you update your code to sqlite*3*_get_table()? (note the '3').

On Mon, Jul 8, 2013 at 11:21 AM, techi eth  wrote:

> We arrive at same place were we start.I am using Sqlite3 version 3.7.9
> & by refrence from below link i can use to get tabel function but i am
> getting undefined refrence error.
> http://www.sqlite.org/c3ref/free_table.html
>
> Is API still available with 3.7.9 version ? Or i want to use than what
> is the way ?
>
> Thanks
>
>
> On 7/8/13, Simon Slavin  wrote:
> >
> > On 8 Jul 2013, at 8:54am, Kees Nuyt  wrote:
> >
> >> On Mon, 8 Jul 2013 06:43:33 +0100, Simon Slavin 
> >> wrote:
> >>
> >>> There is no call 'sqlite_get_table()' in SQLite version 3.
> >>> Nor is there anything like it.
> >>> The closest thing is 'sqlite3_exec()'.
> >>
> >> Not completely true. The C API documentation mentions a legacy
> >> interface: sqlite3_get_table().
> >> http://www.sqlite.org/c3ref/free_table.html
> >
> > Oh wow.  Thanks.  I especially like the triple-starred parameter.
> >
> > 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
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.Sqlite NuGet

[Baruch Burstein]

I installed System.Data.Sqlite from via NuGet, but I don't see it in the
options for creating a new database (server explorer or whatever it is
called). I am using VS2010 Ultimate.
Am I doing something wrong?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Partial sorting

[Baruch Burstein]

I was thinking more of general (not just LIMIT 1) optimization:
http://en.wikipedia.org/wiki/Partial_sorting

On Sun, Apr 7, 2013 at 6:16 PM, Simon Slavin  wrote:

>
> On 7 Apr 2013, at 3:51pm, Stephen Chrzanowski  wrote:
>
> > I don't know if it'd be an interesting optimization.  Who's to say what
> the
> > order ends up as prior to the sort?  Take for example if I have a list of
> > dollars and cents being returned from a query.  I want the 5 top highest
> > cost items out of 6 possibilities, if I keep the top 5 unsorted, item 6
> > could have been the very top of the list.  Thereby making the desired
> > result wrong.
>
> The optimization I was thinking of was simpler than that.  An example
> would have been ORDER BY score DESC LIMIT 1.  Theoretically the engine
> could notice that all it needed was the top-scoring result, and just do a
> quick scan for the highest score, which would be less effort than sorting
> all the rows into order.  I can imagine one of the big heavy-duty SQL
> engines doing this but perhaps not SQLite because it would require a lot of
> extra code and introduce a lot of test cases.
>
> A server/client SQL system can do a kind of progressive indexing because
> it runs on a server and can do a lot of processing without tying up the
> client computer.  So with the above type of SELECT the engine will return
> the first result as the client asks for it.  Then it will immediately try
> to find the second result, then the third, etc. aiming to have each result
> ready before the client asks for it.  I believe MySQL does this under
> certain conditions.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Partial sorting

[Baruch Burstein]

If I issue a select statement with a ORDER BY clause and a LIMIT clause,
does SQLite do a full sort (assuming no index) and then return the first X
rows, or just a partial sort to get the first X sorted results?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to create a foreign key in existing table?

[Baruch Burstein]

On Mon, Dec 31, 2012 at 8:13 PM, Ward Willats wrote:

>
> On Dec 31, 2012, at 12:57 AM, Simon Slavin  wrote:
>
> >
> > On 31 Dec 2012, at 8:54am, Igor Korot  wrote:
> >
> >> I simply forgot to do it on the table creation. And now the table has
> >> many rows...
> >
> > You can easily modify a TABLE definition or even an entire database by
> using the SQLite shell tool to dump the database as text file of SQL
> commands, then edit the text file using a text editor, then use the same
> shell tool to read the text file back in again.
>
>
> Or, if you want to do it "live:" use ALTER TABLE to rename the existing
> table, CREATE TABLE to make the table with the FK you want, INSERT SELECT
> to bring the records from the renamed table to the new table, and DROP
> TABLE to get rid of the renamed original.
>
and VACUUM to get rid of the extra space on disk

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



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to verify referential integrity of SQLite database

[Baruch Burstein]

On Fri, Dec 21, 2012 at 6:04 AM, Richard Hipp  wrote:

> On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
> lhask...@bloomberg.net> wrote:
>
> > Tested, works perfectly. Thanks!
> >
> > Two questions/observation:
> >
> > 1. Should there be a way to determine the parent key w/o looking at or
> > parsing the schema DDL commands?
> >
> > For example:
> > SQLite version 3.7.16 2012-12-20 01:15:20
> >
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> create table p(a, b, primary key(a, b), unique(b, a));
> > sqlite> create table c(x, y, foreign key(x, y) references p);
> > sqlite> insert into p values (1, 2);
> > sqlite> insert into c values (1, 2), (2, 1);
> > sqlite> pragma foreign_key_check(c);
> > c|2|p|0
> >
>
> The fourth column is the foreign_key_id.  If you look at the output of
> PRAGMA foreign_key_list(c), you'll find all the information about parent
> table and the columns that map between parent and child, for that id.
>
>
> >
> > Now I know that the second record is in violation but I don't know what
> > key/index the foreign key actually refers to (and no other combination of
> > existing pragmas will tell me).
> >
> > 2. While I do like your API far better than what I originally proposed, I
> > found that returning no result in case of success may lead to confusion
> > since unknown pragmas behave the same way. So if I run "pragma
> > foreign_key_check;" and get empty result it can mean any of the
> following:
> >
> > 1. There are no foreign key violations - good!
> > 2. My version of SQLite does not support this pragma yet
> > 3. (In case of using the shell) I made a typo in the pragma name
> >
>
> The pragma throws an error if you enter the name of a table that does not
> exist.  That handles case 3.  To verify 2, that the version of SQLite you
> are using support foreign_key_check, simply use the name of a table that
> does not exist and verify that you get an error back:
>
>   PRAGMA foreign_key_check('no-such-table');  --- expect an error
>
Or check sqlite_version() >= 3.7.16 ?


>
>
>
>
> >
> > While I don't have a better suggestion now, I just wanted to point it out
> > to you.
> >
> > Again thanks a lot!
> > - Levi
> >
> > - Original Message -
> > From: d...@sqlite.org
> > To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org
> > At: Dec 19 2012 21:10:52
> >
> >
> >
> > On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
> > lhask...@bloomberg.net> wrote:
> >
> >> My suggestion would be to have check_integrity command verify
> referential
> >> integrity as well only if it's executed while the foreign key
> enforcement
> >> is enabled on the connection.
> >>
> >
> > The latest SQLite from trunk (not the 3.7.15.1 patch release, but the
> code
> > that is destined to become 3.7.16) has a new pragma:
> >
> > PRAGMA foreign_key_check;
> > PRAGMA foreign_key_check(TABLE);
> >
> > The second from checks all of the REFERENCES clauses in TABLE.  The first
> > form checks the keys on all tables in the database.
> >
> > The result of the pragma is a table, with one row per mismatched key.
>  The
> > row contains the name of the child table, the rowid of the child table,
> the
> > name of the parent table, and the "foreign key index" which is an integer
> > that describes the foreign key in PRAGMA foreign_key_list(CHILD).  If the
> > foreign_key_check pragma returns an empty set, that means that all of the
> > keys are correct.
> >
> > PRAGMA foreign_key_check works regardless of whether or not foreign keys
> > are currently enabled or disabled.
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> >
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newest 3 entries

[Baruch Burstein]

On Thu, Nov 8, 2012 at 10:17 PM, Igor Tandetnik  wrote:

> On 11/8/2012 3:10 PM, deltagam...@gmx.net wrote:
>
>> I have a select statement like:
>> "Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
>> ('special')  "
>>
>> Now I like to receive only the newest 3 entries in the table eventlog
>> where the eventtype is 'special'
>>
>
> select id, eventdate, eventtype FROM eventlog WHERE eventtype in
> ('special')
> order by eventdate desc limit 3;
>
I'm guessing that  "WHERE eventtype = 'special' " is more efficient then
"WHERE eventtype in ('special') "
Just a guess.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell import field separators

[Baruch Burstein]

On Tue, Nov 6, 2012 at 1:40 PM, Colin Hardwick  wrote:

> Ladies & Gentlemen,
>
> My first post to this list, please be gentle!
>
> I'm trying to import tab-separated data, example here:
>
> 40009402FUNDNwL "NMF"NEAVB:GB;1 NQ  NwL "NMF"NEAVB:GB;1 NQ
>  10031991-02-14  1991-02-14
> 40009403FUNDNwl'MF"NEA VB:GB;1 QNwl'MF"NEA VB:GB;1 Q
>  10031991-02-14
>
> Table:
>
> CREATE TABLE Asset
> (
> AssetCode TEXT PRIMARY KEY NOT NULL COLLATE NOCASE,
> AssetTypeCode TEXT NOT NULL COLLATE NOCASE,
> Name TEXT NOT NULL COLLATE NOCASE,
> ShortName TEXT COLLATE NOCASE,
> CurrencyCode TEXT COLLATE NOCASE,
> PerformanceTaxCountryCode TEXT COLLATE NOCASE,
> StartDate DATE,
> QualifiedStartDate DATE
> );
>
> I am not in control of the format or content, but have a command file, the
> gist of which is:
>
> .mode tabs
> .import Feeds/Asset.txt Asset
>
> Line 1 of the import file is fine, but line 2 is rejected as only having 7
> fields instead of 8. I've experimented with ".separator" commands to no
> avail (e.g. ".separator='\t'). It appears the the shell tool is thrown by
> the (clearly) mismatched single and double quotes, but since I want it to
> use tabs as the separator should this be the case?
>
> Of course I could write some code to look for strings like this and "fix"
> them, but then I won't end up with what the supplier has provided.
>
> Am I missing a command here? Thanks for any input.
>
> Mac OS-X 10.8.2, shell SQLite 3.7.12 2012-04-03 19:43:07
> 86b8481be7e7692d14ce762d21bfb69504af (latest).
>
> Best regards,
> Colin Hardwick
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

If you are comfortable compiling your own shell, it is just a matter of
commenting out one line here:
http://www.sqlite.org/cgi/src/artifact/24cd0aa74aff73ea08594629faead564c4c2a286?ln=1872to
make it ignore quotes and treat *all* tabs as delimiters.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Baruch Burstein]

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

On Sun, Nov 4, 2012 at 9:34 AM, Григорий Григоренко wrote:

>
>
>
> Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" <
> michael.bla...@ngc.com>:
> >CREATE TABLE t(id,time);
> >
> INSERT INTO t VALUES(1,CURRENT_DATE);
> >
> INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
> >
> INSERT INTO t VALUES(3,datetime('now'));
> >
> INSERT INTO t VALUES(4,date('now'));
> >
> SELECT * FROM t;
> >
> 1|2012-11-02
> >
> 2|2012-11-02 14:10:15
> >
> 3|2012-11-02 14:10:15
> >
> 4|2012-11-02
> >
> >
> Perhaps the documentation needs to be better?  Apparently you couldn't
> find this info...
> Indeed, I was never aware of CURRENT_*.
>
> Anyway, all these functions return current moment as _string_ and this is
> not a great way to store datetime in db, isn't it?
> - more memory occupied;
> - slower compare;
> - cannot add & substract;
> etc.
>
>
>
>
> >
> >
> Michael D. Black
> >
> Senior Scientist
> >
> Advanced Analytics Directorate
> >
> Advanced GEOINT Solutions Operating Unit
> >
> Northrop Grumman Information Systems
> >
> >
> 
> >
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Григорий Григоренко [grigore...@mail.ru]
> >
> Sent: Friday, November 02, 2012 8:08 AM
> >
> To: General Discussion of SQLite Database
> >
> Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for
> "strftime('%s','now')"
> >
> >
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
> >
> >
> >
> >
> On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:
> >
> >
> >
> >
> >
> > it is a common practice to store datetime values as UNIX time UTC.
> >
> >
> >
> > Maybe, Sqlite should have some shortcut for evaluating current moment?
> >
> >
> >
> >
> >
> Please read
> >
> >
> >
> >
> >
> 
> >
> >
> >
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
> functions that modify or format date values.
> >
> >
> It's about having useful shortcut for getting current moment that doesn't
> have (string) parameters and so can be easily remembered and typed.
> >
> >
> Compare:
> >
> MS SQL: CURRENT_TIMESTAMP
> >
> PostgreSQL: now()
> >
> Oracle: sysdate
> >
> >
> To:
> >
> Sqlite: strftime('%s','now')
> >
> >
> >
> >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
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Which non-default options are used for the shell?

[Baruch Burstein]

I know FTS is enabled (SQLITE_ENABLE_FTS*), but are any other flags used in
compiling the default shell?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permalink to latest amalgamation

[Baruch Burstein]

On Thu, Oct 25, 2012 at 12:42 AM, Darren Duncan <dar...@darrenduncan.net>wrote:

> Kees Nuyt wrote:
>
>> On Thu, 25 Oct 2012 00:12:16 +0200, Baruch Burstein
>> <bmburst...@gmail.com> wrote:
>>
>>  Is there a permanent link I can use that will always point to the latest
>>> amalgamation (or .zip containing it)? I would like to automate a make
>>> script that will use the latest sqlite.
>>> I know I can use a link to the latest release in the repo, but that
>>> means I
>>> would need to build the amalgamation as part of this make step.
>>>
>>
>> You make it sound very difficult to build the amalgamation source,
>> but actually it's just "make sqlite3.c".
>>
>> So the easiest way really is to:
>> * clone the fossil repo (once),
>> * pull in updates periodically,
>> * ./configure with the proper options/defines/omits
>> for whatever your project needs,
>> * make sqlite3.c, * make your project.
>>
>
> I don't think that answer is appropriate for some common use cases, which
> may include the original requestor.  Say for example and end user of the
> DBD::SQLite Perl module that wants to pull in the latest SQLite version to
> build it against, without having to specify a version.  We shouldn't expect
> such a user to have a fossil client, they should just be able to pull the
> amalgamation tarball over the web. -- Darren Duncan
>
I don't remember the exact path offhand, but fossil web ui has a URL that
return the requested checkin as a tarball, no need for a fossil client.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Permalink to latest amalgamation

[Baruch Burstein]

Is there a permanent link I can use that will always point to the latest
amalgamation (or .zip containing it)? I would like to automate a make
script that will use the latest sqlite.
I know I can use a link to the latest release in the repo, but that means I
would need to build the amalgamation as part of this make step.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unofficial poll

[Baruch Burstein]

On Sun, Sep 23, 2012 at 2:23 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Sun, Sep 23, 2012 at 6:37 AM, Baruch Burstein <bmburst...@gmail.com
> >wrote:
>
> > I am curious about the usefulness of sqlite's "unique" type handling,
>
>
> SQLite is not unique in this respect.  Lots of other languages use
> flexible, dynamic typing:  Javascript, Perl, Python, Tcl, AWK come quickly
> to mind.  SQLite began as a TCL extension, so it should not be surprising
> that it follows Tcl's dynamic typing model.
>

It is unique (to my knowledge) among SQL engines, which is the language
category SQLite falls into. Those others are all general programming
languages which is a very different category.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unofficial poll

[Baruch Burstein]

I am curious about the usefulness of sqlite's "unique" type handling, and
so would like to know if anyone has ever actually found any practical use
for it/used it in some project? I am referring to the typeless handling,
e.g. storing strings in integer columns etc., not to the non-truncating
system e.g. storing any size number or any length string (which is
obviously very useful in many cases).
Has anyone ever actually taken advantage of this feature? In what case?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] selecting real values

[Baruch Burstein]

When selecting real (float) values, does the sqlite return (and the shell
display) the full precision it has by default, or does it have a higher
precision stored in the database than it displays?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Read-only media

[Baruch Burstein]

On Thu, Sep 6, 2012 at 5:26 PM, Simon Slavin  wrote:

>
> On 6 Sep 2012, at 3:13pm, Richard Hipp  wrote:
>
> > If the last writer to the database file crashed and left a hot
> > journalthen the
> > next reader to come along must rollback that journal before it can
> > start reading, and that will require write access.  We have encountered
> > cases where companies accidently publish a gazillion copies of a CDROM
> that
> > contains a hot journal.  The database on such CDROMs are unreadable.
>
> Ouch.  Yeah, I guess that's going to happen occasionally.
>
Not that I think this is necessarily a good idea, but maybe if opening a
database with a hot journal from read-only media (or with _READONLY), the
database pages in the journal can be loaded into the page cache and marked
as never_remove_from_cache or some such, effectively giving read-only
access to the database (I think this would work, but might not be a good
idea. But that company would have saved their reputation if this existed)

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Read-only media

[Baruch Burstein]

Can sqlite databases be read from a read-only media? I seem to remember
seeing something about this on the website, but can't find it.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause

[Baruch Burstein]

VALUES is used for INSERTing into a table, not for SELECTing. This is not
valid SQL (I would help you fix it, but I can't figure out what you were
trying to achieve.)
Here is a great reference: http://sqlite.org/lang_select.html

On Thu, Sep 6, 2012 at 11:18 AM, Arbol One  wrote:

> As many of you know, I am trying to learn SQL using C++.
>
> Below is an error I get when I try using the C++ example below it.
>
>
>
> Error Code: 1
>
> Error Message: near "VALUES": syntax error
>
> 
>
>
>
> Glib::ustring apstr;
>
> Glib::ustring sName;
>
> int apint;
>
> mySQLite3* db;
>
> try {
>
> db = new mySQLite3(db_name.c_str());
>
> } catch(somexception& e) {
>
> //do something
>
> }
>
>
>
>// SQL statement
>
> apstr = "SELECT fname FROM ";
>
> apstr += this->db_table_name;
>
> apstr += " WHERE title = ";
>
> apstr += token;
>
> apstr += " (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?, ?)";
> // here is where the problem is see the method below
>
> apint = 1;
>
> db->setStmt(apstr);
>
> sName = db->read_str(apint);
>
>
>
> 
>
> const Glib::ustring& mySQLite3::read_str(const int pos)
>
> throw(jme::Exception) {
>
>
>
> rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, ,
> NULL);
>
> if(rc != SQLITE_OK) {
>
> // do something
>
> }
>
> rc = sqlite3_step(mystmt);
>
> if(rc == SQLITE_ROW ) {
>
> apstr = (const char*)sqlite3_column_text(mystmt,pos);
>
> }
>
> try {
>
> this->finalize();
>
> } catch(somexception& e) {
>
> throw e;
>
> }
>
> return apstr;
>
> }
>
> What am I doing wrong?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Baruch Burstein]

Temporary table? Then you will have rowids.

On Wed, Sep 5, 2012 at 4:16 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein <bmburst...@gmail.com
> >wrote:
>
> >  SELECT id, a, b, ..., mtime
> > FROM tab
> > WHERE rowid IN (
> > SELECT id, min(mtime)
> > FROM tab
> > GROUP BY id
> > )
> > ORDER BY mtime DESC;
> >
>
> In the actual application, "tab" is not a real table but is a join:
>
>  http://www.fossil-scm.org/fossil/artifact/5dfd626877f?ln=271-273
>
> So there isn't a ROWID available to select from.
>
>
> >
> > On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp <d...@sqlite.org> wrote:
> >
> > >
> > > SELECT id, a, b, ..., min(mtime)
> > > FROM tab
> > > GROUP BY id
> > > ORDER BY min(mtime) DESC;
> >
> >
> >
> >
> > --
> > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Baruch Burstein]

Sorry, that won't work.

On Wed, Sep 5, 2012 at 4:12 PM, Baruch Burstein <bmburst...@gmail.com>wrote:

> SELECT id, a, b, ..., mtime
> FROM tab
> WHERE rowid IN (
> SELECT id, min(mtime)
>
> FROM tab
> GROUP BY id
> )
> ORDER BY mtime DESC;
>
>
> On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>>
>> SELECT id, a, b, ..., min(mtime)
>> FROM tab
>> GROUP BY id
>> ORDER BY min(mtime) DESC;
>
>
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Baruch Burstein]

 SELECT id, a, b, ..., mtime
FROM tab
WHERE rowid IN (
SELECT id, min(mtime)
FROM tab
GROUP BY id
)
ORDER BY mtime DESC;

On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:

>
> SELECT id, a, b, ..., min(mtime)
> FROM tab
> GROUP BY id
> ORDER BY min(mtime) DESC;




-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign keys across database boundaries

[Baruch Burstein]

Can foreign keys or triggers be set between ATTACHed database tables? If
so, is there any other type of statment that doesn't work across ATTACH
boundaries?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] last_insert_rowid() with conflict

[Baruch Burstein]

If I have a column that has unique values, and the ON CONFLICT clause is
IGNORE, is there a way to get the rowid of the last insert (on
success) *or*the last conflict (on conflict). I would like to just
keep adding to the
table without worrying if there is a duplicate, but after each insert I
need to know the row of the inserted item (whether it was inserted now or
previously)

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] escaping GLOB pattern

[Baruch Burstein]

Is there a built-in way to escape a GLOB pattern? Will it escape it if I
bind it to a parameter in a prepared function instead of embedding it
directly in the query string?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unicode61 FTS tokenizer

[Baruch Burstein]

Does the new FTS tokenizer `unicode61` depend on ICU?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite Commit C API

[Baruch Burstein]

> On Thu, May 17, 2012 at 7:04 PM, Rajesh Kumar  wrote:
>
 int fsync( int fd ) { return 0; }
>
> fsync will expect an Integer pointer right. But sqlite pointer is of type
> sqlite3*. So how can fsync works on sqlite. What should I pass to fsync???
>
>
You don't need to call fsync(). Sqlite calls it internally to make sure no
corruption occurs. If it is broken on your system, then by extension any
software that depends on it working properly (such as sqlite) will be
broken, too.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Baruch Burstein]

On Wed, May 16, 2012 at 2:47 PM, Jean-Christophe Deschamps  wrote:

>
>  > This FAQ topic doesn't apply here.  We're talking about schema names or
>> keywords, while FAQ#14 is literal escaping a single quote.
>>
>> create table "foo""bar" ( baz );
>>
>> select * from "one""two";
>>
>> select * from sqlite_master;
>>
>> table|foo"bar|foo"bar|9|CREATE TABLE "foo""bar" ( baz )
>>
>
> I knew this particular escape worked, granted, but this isn't documented.
>
> But even then:
>
> SQLite version 3.7.11 2012-03-20 11:35:50
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table [foo [[bar]] ?];
> Error: unrecognized token: "]"
> sqlite> create table `foo ``bar`` ?`;
> Error: near ";": syntax error
> sqlite> create table `foo ``bar ?`;
> Error: near ";": syntax error
>
> So some questions remain: what are the definitive rules?
>

Those all work. You are getting an error because you didn't supply any
column in the table



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escape table and column names

[Baruch Burstein]

On Wed, May 16, 2012 at 2:25 PM, Simon Slavin  wrote:

>
> On 16 May 2012, at 11:56am, Petite Abeille 
> wrote:
>
> > On May 16, 2012, at 12:28 PM,  
> wrote:
> >
> >> Anyone have an answer?
> >
> > http://www.sqlite.org/lang_keywords.html
> >
> >> What about if table name or column name contains that escape character?
> >> Do I need to escape it in another way?
> >
> > http://sqlite.org/faq.html#q14
>
> Doesn't answer the question of what to do if your table name includes a
> double-quote character.
>
> And nor can I, except to say that if I saw such a thing I'd run away.
>
> A quick testing shows it works the same for double-quoted column names,
e.g.

sqlite> create table "q""w" (t);
sqlite> .tables
q"w

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NULL sqlite3_stmt in function calls

[Baruch Burstein]

Are all API functions that accept an sqlite3_stmt* safe (=NO-OPs) to call
with a NULL pointer? I mean things like bind, column, etc.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_column_count vs. sqlite3_data_count

[Baruch Burstein]

sqlite3_column_count
sqlite3_data_count

What is the difference between the two?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finalizing all statements before closing connection

[Baruch Burstein]

On Tue, May 15, 2012 at 10:34 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:

> On Tue, May 15, 2012 at 10:17:41PM +0300, Baruch Burstein scratched on
> the wall:
> > Which of the following should I be running right before calling
> > sqlite3_close?
> >
> > while(stmt = sqlite3_next_stmt(db, stmt))
> > sqlite3_finalize(stmt);
> >
> > while(stmt = sqlite3_next_stmt(db, NULL))
> > sqlite3_finalize(stmt);
> >
> > I am not sure which will have the desired effect.
>
>   The second, since "stmt" will be an invalid pointer by the time
>  the loop comes around and hits sqlite3_next_stmt() again.
>
>  Ideally, however, your application should manage its own statements
>  correctly.  This type of brute-force clean-up can act as a safety net,
>  but it can also leave dangling pointers elsewhere in the code.
>  Something in your code must know about those statements... and if
>  not, then you're leaking memory, which is just as bad.
>

I am aware of this. However, I am working on a  C++ wrapper for sqlite. It
is the wrapper's user's responsibility to make sure no statement objects
still exist before the database object gets destroyed. This is just a
precaution.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiler warnings with 3.7.12

[Baruch Burstein]

I just compiled the the sqlite3 shell from the 3.7.12 amalgamation
download. I got the following warnings:

shell.c:71:0: warning: "popen" redefined [enabled by default]
In file included from shell.c:33:0:
[mingw
path]\bin\../lib/gcc/x86_64-w64-mingw32/4.7.0/../../../../x86_64-w64-mingw32/include/stdio.h:450:0:
note: this is the location of the previous definition
shell.c:72:0: warning: "pclose" redefined [enabled by default]
In file included from shell.c:33:0:
[mingw
path]\bin\../lib/gcc/x86_64-w64-mingw32/4.7.0/../../../../x86_64-w64-mingw32/include/stdio.h:451:0:
note: this is the location of the previous definition

I am using the MinGW_w64 compiler. Version 4.7.0 from RubenVB's stable
build, both running on and targeting x64 machines.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] proposal for api

[Baruch Burstein]

Assuming that sqlite knows how many rows are in a result set without having
to sqlite3_step over each row, can there be an api like:

sqlite3_in64 sqlite3_rows(sqlite3_stmt *stmt)

which would run the query (if sqlite3_step has not been called on this stmt
object yet), and return the number of rows in the result set?

Or is my assumption that sqlite has this information wrong?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

[Baruch Burstein]

I rebind them on every iteration.

On Mon, May 14, 2012 at 6:46 PM, Pavel Ivanov <paiva...@gmail.com> wrote:

> > One the data is commited surely it doesn't need to be retained, does it?
>
> If you called sqlite3_reset() on a statement and then didn't call
> sqlite3_clear_bindings() then all bindings will surely be needed on
> the next statement execution in some subsequent transaction.
>
>
> Pavel
>
>
> On Mon, May 14, 2012 at 11:35 AM, Black, Michael (IS)
> <michael.bla...@ngc.com> wrote:
> > If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?
> >
> >
> >
> > One the data is commited surely it doesn't need to be retained, does it?
> >
> >
> >
> > Michael D. Black
> >
> > Senior Scientist
> >
> > Advanced Analytics Directorate
> >
> > Advanced GEOINT Solutions Operating Unit
> >
> > Northrop Grumman Information Systems
> >
> > 
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Baruch Burstein [bmburst...@gmail.com]
> > Sent: Monday, May 14, 2012 10:20 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] a couple of questions
> >
> > On Mon, May 14, 2012 at 5:45 PM, Richard Hipp <d...@sqlite.org> wrote:
> >
> >> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein <bmburst...@gmail.com
> >> >wrote:
> >>
> >> >
> >> > 2. How "static" does data have to be to be bound with SQLITE_STATIC?
> If
> >> it
> >> > won't change until the call to sqlite3_step, is that enough? How about
> >> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> >> > enough, I assume that if it doesn't change before the
> sqlite3_finalize,
> >> it
> >> > must be safe to use SQLITE_STATIC?
> >> >
> >>
> >> SQLITE_STATIC means that the content must not change until after SQLite
> has
> >> read it for the last time.  You are guaranteed to be safe if you hold
> the
> >> content unchanged until sqlite3_finalize().  You can probably get away
> with
> >> changing the content sooner than that, but it depends on your
> >> circumstances.  Why push your luck?
> >>
> >
> > For speed. I will be binding - stepping (it is an update statement) -
> > resetting in a tight loop, possibly up to about a million times.
> >
> > --
> > Programming today is a race between software engineers striving to build
> > bigger and better idiot-proof programs, and the Universe trying to
> produce
> > bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

[Baruch Burstein]

On Mon, May 14, 2012 at 5:45 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein <bmburst...@gmail.com
> >wrote:
>
> >
> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
> it
> > won't change until the call to sqlite3_step, is that enough? How about
> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> > enough, I assume that if it doesn't change before the sqlite3_finalize,
> it
> > must be safe to use SQLITE_STATIC?
> >
>
> SQLITE_STATIC means that the content must not change until after SQLite has
> read it for the last time.  You are guaranteed to be safe if you hold the
> content unchanged until sqlite3_finalize().  You can probably get away with
> changing the content sooner than that, but it depends on your
> circumstances.  Why push your luck?
>

For speed. I will be binding - stepping (it is an update statement) -
resetting in a tight loop, possibly up to about a million times.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] a couple of questions

[Baruch Burstein]

1. Can a use the expression 'WHERE b IN (SELECT ...)' if b is a blob
column? Does the 'IN' comparison work with blobs?

2. How "static" does data have to be to be bound with SQLITE_STATIC? If it
won't change until the call to sqlite3_step, is that enough? How about
until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
enough, I assume that if it doesn't change before the sqlite3_finalize, it
must be safe to use SQLITE_STATIC?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters

[Baruch Burstein]

Thank you for the quick answer.

On Mon, May 14, 2012 at 4:55 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, May 14, 2012 at 9:49 AM, Baruch Burstein <bmburst...@gmail.com
> >wrote:
>
> > Are text parameters bound with sqlite3_bind_text automatically escaped
> and
> > quoted, just escaped, just quoted, or neither?
> >
>
>
> Both.  And neither.
>
> The content of the parameter is not modified in any way.  It is copied
> directly into the database file, byte for byte.  This has the same effect
> as if the parameter had been both escaped and quoted and inserted into the
> SQL and then parsed, but is much faster since no conversions take place.
>
>
> >
> > --
> > Programming today is a race between software engineers striving to build
> > bigger and better idiot-proof programs, and the Universe trying to
> produce
> > bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] parameters

[Baruch Burstein]

Are text parameters bound with sqlite3_bind_text automatically escaped and
quoted, just escaped, just quoted, or neither?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_finalize

[Baruch Burstein]

If sqlite3_prepare_v2 didn't return SQLITE_OK, do I need to call
sqlite3_finalize on the statement pointer?


-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] index creation for single-use DB

[Baruch Burstein]

I am using an in-memory DB to load data into it, do a few sort / find
duplicates / SELECTs, and then dispose of the DB. It can vary in size from
a few thousand rows to over a million.
Would the time used for creating an index be worth it for just a single
sort and a few SELECTs? If so, would it be faster to do all the INSERTs and
then add the index, or create the index and then do the INSERTs?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] using lemon

[Baruch Burstein]

Can anyone suggest a lexical scanner generator that:
a) Works well with Lemon?
b) Is also thread-safe (like Lemon)?

Does Flex work well with Lemon? (I think it is not thread-safe, but it is
very popular and has plenty of examples around, which is also a plus for me)

I am trying to learn a little about writing tokenizers/parsers, and thought
a */Lemon combo would be better to learn if I don't have any previous
experience anyway than Flex/Bison.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
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

[Baruch Burstein]

It is already wrapped in a transaction.
I seem to remember seeing somewhere that the .import command doesn't
understand escaping, e.g.

"one","two,three"

will get imported as

"one" | "two | three"

(the quotes are part of the data, and the second column was split into two
by the comma)
Just a point to be aware of.

On Tue, May 1, 2012 at 11:22 PM, Black, Michael (IS)  wrote:

> You need to try and do an import from the shell.  GUIs seem to have way
> too many limits.
>
> http://sqlite.org/download.html
>
>
>
> Don't do any indexes up frontdo them afterwords if they'll help your
> queries.  Indexes will slow down your import notably.
>
>
>
> I don't think you're anywhere near the limits of sqlite since it talks
> about terabytes.
>
> http://sqlite.org/limits.html
>
>
>
> Somebody else can answer for sure but wrapping your .import inside a
> transaction may be a good thing.
>
> I don't know if that's done by default.
>
>
>
> Your queries are liable to be pretty slow depending on what you have to do.
>
>
>
>
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of peter korinis [kori...@earthlink.net]
> Sent: Tuesday, May 01, 2012 3:06 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] is SQLite the right tool to analyze a 44GB file
>
> I'm new to SQLite . not a programmer . not a DBA . just an end-user with no
> dev support for a pilot project (single user, no updates, just queries).
>
>
>
> I want to analyze the data contained in a 44GB csv file with 44M rows x 600
> columns (fields all <15 char). Seems like a DBMS will allow me to query it
> in a variety of ways to analyze the data.
>
>
>
> I have the data files and SQLite on my laptop: a 64-bit Win7 Intel
> dual-proc
> with 4GB RAM + 200GB free disk space.
>
> End-user tools like Excel & Access failed due to lack of memory. I
> downloaded SQLite ver.3.6.23.1. I tried to use Firefox Data Manager add-on
> but it would not load the csv files - 'csv worker failed'. So I tried
> Database Master from Nucleon but it failed after loading (it took 100
> minutes) ~57,000 rows with error message = 'database or disk is full". I
> tried to create another table in the same db but could not with same error
> message. The DB size shows as 10,000KB (that looks suspiciously like a size
> setting?).
>
>
>
> From what I've read SQLite can handle this size DB. So it seems that either
> I do not have enough RAM or there are memory/storage (default) limits or
> maybe time-out issues that prevent loading this large file . or the 2 GUI
> tools I tried have size limits. I do have a fast server (16GB, 12 procs,
> 64-bit intel, Win server) and an iMAC available.
>
>
>
> 1.   Is SQLite the wrong tool for this project? (I don't want the
> overkill and admin overhead of a large MySQL or SQL Server, etc.)
>
> 2.   If SQLite will work, are there configuration settings in SQLite or
> Win7 that will permit the load . or is there a better tool for this
> project?
>
>
>
> Thanks much for helping a newbie!
>
>
>
> peterK
>
>
>
> ___
> 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
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
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

[Baruch Burstein]

If none of your fields contain a comma, you can just use the sqlite3
terminal to load a csv file.

On Tue, May 1, 2012 at 11:06 PM, peter korinis wrote:

> I'm new to SQLite . not a programmer . not a DBA . just an end-user with no
> dev support for a pilot project (single user, no updates, just queries).
>
>
>
> I want to analyze the data contained in a 44GB csv file with 44M rows x 600
> columns (fields all <15 char). Seems like a DBMS will allow me to query it
> in a variety of ways to analyze the data.
>
>
>
> I have the data files and SQLite on my laptop: a 64-bit Win7 Intel
> dual-proc
> with 4GB RAM + 200GB free disk space.
>
> End-user tools like Excel & Access failed due to lack of memory. I
> downloaded SQLite ver.3.6.23.1. I tried to use Firefox Data Manager add-on
> but it would not load the csv files - 'csv worker failed'. So I tried
> Database Master from Nucleon but it failed after loading (it took 100
> minutes) ~57,000 rows with error message = 'database or disk is full". I
> tried to create another table in the same db but could not with same error
> message. The DB size shows as 10,000KB (that looks suspiciously like a size
> setting?).
>
>
>
> From what I've read SQLite can handle this size DB. So it seems that either
> I do not have enough RAM or there are memory/storage (default) limits or
> maybe time-out issues that prevent loading this large file . or the 2 GUI
> tools I tried have size limits. I do have a fast server (16GB, 12 procs,
> 64-bit intel, Win server) and an iMAC available.
>
>
>
> 1.   Is SQLite the wrong tool for this project? (I don't want the
> overkill and admin overhead of a large MySQL or SQL Server, etc.)
>
> 2.   If SQLite will work, are there configuration settings in SQLite or
> Win7 that will permit the load . or is there a better tool for this
> project?
>
>
>
> Thanks much for helping a newbie!
>
>
>
> peterK
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit precompiled command-line shell binary for Windows 7

[Baruch Burstein]

I haven't tested it (I only have a 32-bit system), but here you go. Just
change the extension to .exe

On Mon, Apr 30, 2012 at 4:28 PM, Udi Karni  wrote:

> Dear Sqlite development team,
>
> I'd like to add a vote for requesting a 64-bit precompiled command-line
> shell binary (sqlite3.exe) for Windows 7 - to be added to the list of files
> downloadable from the page  .
>
> It would be very helpful for those of us who are not versed with compiling
> C++ code.
>
> I have seen a tremendous performance boost from the use of "memory
> temp_store" and "memory database". The 64-bit version would allow them to
> go beyond 2GB.
>
> Is that simple? Not so simple? If you could elaborate - it would be much
> appreciated.
>
> Thank you !
>
> Udi
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] table aliases in update

[Baruch Burstein]

Does sqlite not support table aliases in update statements? Is there a way
to work around this to get the affect of

update table1 t1

set col1 = col1 * 2

where col1 <= (select avg(col1)

 from table1
 where col2=t1.col2);

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 command line shell bug?

[Baruch Burstein]

I just noticed that the dot-commands are tested by the length of the input,
not of the correct term. For example, '.e' will match '.exit' (it would
also match '.explain', except it matches '.exit' first). Is this intended
behavior? And if so, why should '.e' match '.exit' any more than '.explain'?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Small addition to shell.c

[Baruch Burstein]

I find I was often trying to exit the shell while at the continuePrompt.
This usually happens because I accidentally entered 'exit' without the
period first, but sometimes for other reasons (I started to enter a SQL
command and realized I didn't need to, or whatever). I have added a small
fix to my own shell to check for '.exit' at the beginning of every line,
even if nSql>0 (line 2517). I would suggest this as a permanent addition,
as it is slightly inconvenient to (about 70% of the time) end up doing:

sqlite> exit(think: oops! I meant...)
   ...> .exit(think: argh! All right! Now I remember that I need only 2
more lines to get out of here!)
   ...> ;
Error: near "exit": syntax error
sqlite> .exit

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite read-only code

[Baruch Burstein]

Is there any way to compile sqlite without any support for writing to the
database? Basically just support SELECT? We are trying to use it for
reading files in our system, but every byte we can squeeze out of the lib
would help.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient usage of sqlite

[Baruch Burstein]

On Mon, Jan 2, 2012 at 6:49 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 2 Jan 2012, at 2:46pm, Baruch Burstein wrote:
>
> > It is my first game, as I am just learning game programming. It is a
> > sokoban clone.
>
> If it's your first anything, you may be worrying about the wrong thing.
>  Get your game working first, any old how.  Worry about libraries and file
> sizes later.  All the tiny details like that will join up and sap your
> strength if you worry about them at this stage.
>
> Good luck with it.
>
> Simon.
>
> Thank you for the advice. I finished the game and am now looking for ways
to improve what I have.


-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient usage of sqlite

[Baruch Burstein]

On Mon, Jan 2, 2012 at 2:20 PM, Ivan Shmakov <oneing...@gmail.com> wrote:

> >>>>> Baruch Burstein writes:
>
>  > Which brought me to wonder if storing 5-10 tables with some of them
>  > having <500 bytes of data may be very inefficient.
>
> Which kind of game data takes so little space, I wonder?
>

It is my first game, as I am just learning game programming. It is a
sokoban clone.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient usage of sqlite

[Baruch Burstein]

On Sun, Jan 1, 2012 at 8:12 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 1 Jan 2012, at 5:27pm, Baruch Burstein wrote:
>
> > I need a file format to hold a bunch of resources for my program. I
> thought
> > of using SQLite. However, I am debating two formats. The
> > more convenient one for me would put every few resources in
> > a separate table. However, this would result in small tables. Am I right
> > that this is very inefficient in SQLite? The other option would be to
> put a
> > bunch of unrelated resources in one table. Is this more efficient?
>
> First, don't worry about inefficient.  What you need to worry about is
> not-efficient-enough.  For instance, your app taking 1/10th of a second
> slower is not a problem, but if your app becomes too slow to be fun to use,
> that's a problem.
>
> By inefficient I meant space-wise, not time-wise.



> A reason to split resources up into many tables would be that each
> resource has different columns and you need to do cross-column searches.
>  Is this what you have ?
>
> If instead you just have different types of resource, just make another
> column in your table and put the thing you'd expected to be the table name
> in that column.
>
> My resources are a bunch of sound and image files, level data files,
script files and other game data stuff. Instead of distributing my game
with about 20-30 small (some very small) files, I thought I would roll all
the files into some kind of archive. I started to roll my own format, when
it occurred to me that sqlite may be well suited for this. Which brought me
to wonder if storing 5-10 tables with some of them having <500 bytes of
data may be very inefficient. I don't want to substitute a 20K file for my
<10K of files. I know this is not a lot, but it still bothers me, like what
when I have a game with 500M of files? (you never know, it may happen!). No
searching is needed (except once for the key to load a resource)


-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Efficient usage of sqlite

[Baruch Burstein]

I need a file format to hold a bunch of resources for my program. I thought
of using SQLite. However, I am debating two formats. The
more convenient one for me would put every few resources in
a separate table. However, this would result in small tables. Am I right
that this is very inefficient in SQLite? The other option would be to put a
bunch of unrelated resources in one table. Is this more efficient?


-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for SELECT

[Baruch Burstein]

I will when I get the chance, but I am trying to get a list of things to
try to improve my SELECT speeds. If it is one SELECT, but returning +-1
rows, it probably won't make a difference, right?

On Tue, Nov 22, 2011 at 11:41 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 11/22/2011 04:34 PM, Baruch Burstein wrote:
>
>> Do transactions speed up SELECT statements?
>>
>>
> They can a bit. If you put 10 SELECT statements in a transaction
> SQLite only has to lock and unlock the database file once. If
> you run them outside of a transaction the db is locked and unlocked
> 10 times.
>
> Best to experiment to find out if this is significant for your
> app.
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transactions for SELECT

[Baruch Burstein]

Do transactions speed up SELECT statements?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How is the amalgamation produced?

[Baruch Burstein]

I found what seem to be two options for producing an amalgamation:
1. "./configure" and "make sqlite3.c" (something like that, it is not in
front of me at the moment, but I have done this and know it works)
2. There is a Tcl script in the tool directory mksqlite3c.tcl that seems to
do this too (I didn't test this)

How is the official amalgamation produced?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C API docs

[Baruch Burstein]

How are the C API documents auto-generated? Which tool is used?
I see that they are all in the comments in the code, but couldn't find a
tool in the source that is used to extract them and make the links.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with binding parameters to LIKE

[Baruch Burstein]

I have done something similar and it worked for me, but there is an issue
with indexes you should take into account, as discussed here:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html
.
Out of curiosity (since this query and it's field names seem very similar to
one I am using), what are you using this for?

On Sun, Oct 23, 2011 at 5:53 AM, Navaneeth.K.N wrote:

> Hello,
>
> I am trying to use parameters in a LIKE query. I have the following
> code which uses Sqlite C/C++ API.
>
> const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
> ORDER BY freq DESC LIMIT 10;";
>
> int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
> if ( rc != SQLITE_OK )
> return false;
>
> sqlite3_bind_text ( stmt, 1, data , -1, NULL );
>
> Unfortunaltly, this won't work. Sqlite is executing the statement
> successfully, but I am not getting the expected result. When I execute
> the same statement after removing parameters it works perfectly.
> Something like,
>
> const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
> ORDER BY freq DESC LIMIT 10;";
>
> It looks like concatentation with parameters is not working for some
> reason. To debug the issue, I hooked up sqlite3_trace and
> sqlite3_profile and printed the SQL being executed. Unfortunatly,
> these routines won't give the SQL with values bound to it.
>
> I am running out of ideas and any help would be great to address the
> problem.
>
> Thanks
> --
> -n
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Code Patch

[Baruch Burstein]

If I have a (very simple 2 line) code patch, where should I be sending it?
Or should I just post it here as straight text?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite code checkout

[Baruch Burstein]

Thank you.
And what would the URL be for a specific tag?

On Thu, Sep 22, 2011 at 3:17 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Sep 22, 2011 at 8:05 AM, Baruch Burstein <bmburst...@gmail.com
> >wrote:
>
> > How can I get a clean copy of the latest SQLite trunk in a script? Fossil
> > seems to only have an option for a "checkout", with all the additional
> > files
> > involved. I want just a clean copy of the code, like the sqlite-src-*.zip
> > file on the download page.
> >
>
>
>   wget
> http://www.sqlite.org/src/tarball/sqlite-latest-trunk.tar.gz?uuid=trunk
>
>
>
> >
> > p.s. Bonus points if I can get just the files needed for building the
> > amalgamation, without art/contrib/ext/test/doc/etc.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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