Re: [sqlite] Pragma to flag unknown pragma?

[R Smith]

On 2016/11/23 2:08 AM, Scott Hess wrote:

https://www.sqlite.org/pragma.html has:
"No error messages are generated if an unknown pragma is issued.
Unknown pragmas are simply ignored. This means if there is a typo in a
pragma statement the library does not inform the user of the fact."

I just lost some time due to this, even though I was fully aware of
it.  My code wasn't working, so I instrumented to report errors, and
gradually dug things deeper and deeper.  It wasn't until I was
verifying statements line-by-line against sqlite3 in a terminal window
that I saw that I was setting journal_mod rather than journal_mode!

I realize that pragma don't have the compatibility guarantees that
other syntax has.  But that means I actually _would_ want my code to
start barfing if a PRAGMA stops being supported.  Say I'm issuing
"PRAGMA secure_delete=ON" - presumably it's for a reason, and I'm
going to want to revisit that reason if it's no longer supported.


The problem is more the other way round - Backward compatibility is hard 
when you introduce a new pragma, and a previous version of SQLite 
"barfs" suddenly when it encounters that statement. Recent posts here 
re-emphasize the frequency with which old versions are still used out 
there


Silently ignoring an unrecognized pragma is a great way to be able to 
introduce new functionality without worrying that the old will break.


That said - we have been lobbying a long time for a "Strict-Mode" in 
SQLite (though that would probably hurt the "lite-ness" of it), but this 
is another example where such a mode would prove very useful.


Also - there are many tools that will syntax-check and/or use 
code-highlighting for your SQL for SQLite (SQLitespeed found here: 
www.sqlc.rifin.co.za or sqliteexpert.com being two of them and others 
regularly pop up here) which would high-light such problems. If you do 
not wish to check ALL your statements, at least try those that break on 
one of these systems before starting a big dig.


Good luck!
Ryan

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

Re: [sqlite] Table name syntax

[James K. Lowden]

On Mon, 21 Nov 2016 20:46:45 +
David Raymond  wrote:

> insert into main.foo
> select db1.foo.*
> from db1.foo left outer join db2.bar
> on db1.foo.pk = db2.bar.pk
> where db2.bar.pk is null;

Just by the way, your query could be cast as 

insert into main.foo
select * 
from db1.foo where not exists (
select 1 from db2.bar
on db1.foo.pk = pk 
);

Although DRH's points regarding "select *" are well founded, there are
good uses for it, and yours is one.  In this case, the tables "main"
and "foo" are supposed to have the same columns.  "select *" propagates
that symmetry if foo is altered.  Whether or not that's actually
desirable depends on how "main" is defined, on its meaning in the mind
fo the designer.  

IMO the criticism of "select *" is sometimes overwrought.  An
application that uses columns by name -- as it should -- will not fail
in the face of extra columns.  The efficiency gain could be small or
large, depending.  It's a good guideline, but doesn't deserve
veneration in all circumstances.  

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

Re: [sqlite] CREATE TABLE fails

[Igor Korot]

Hi, guys,
Parents with the comma before "PRIMARY" fixed it.

Thx.


On Tue, Nov 22, 2016 at 9:39 AM, Niall O'Reilly  wrote:
> On 22 Nov 2016, at 14:35, David Raymond wrote:
>
>> It's needed. The arrow coming out of [column-def] (visually) goes past
>> [table-constraint] first, with the option to loop down to a comma on its way
>> to a [table-constraint]
>
>
>   Thanks.  My gut told me one thing, my eyes another.  Eyes were wrong.
>
>
>   Best regards,
>   Niall O'Reilly
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl bindings doc update request

[Rolf Ade]

I certainly apologzize, to rise this a third time; I will stop to do it
again.

Still, I think the documentation of the busy method of the Tcl interface
to sqlite

http://sqlite.org/tclsqlite.html#busy

lacks the information, that the callback procedure will be called with
one argument.


Rolf Ade  writes:
> I apologzize to rise this again, it may have fallen under the radar for
> some reason. It is minor, too.
>
> The documentation of the busy method of the Tcl interface to sqlite
>
> http://sqlite.org/tclsqlite.html#busy
>
> doesn't tell, that the callback procedure is called with one arg, the
> "number of times that the busy handler has been invoked previously for
> the same locking event" (as the documentation sqlite3_busy_handler() at 
> https://www.sqlite.org/c3ref/busy_handler.html words it).
>
> The documentation also doesn't note, how the timeout and busy methods
> interfere.
>
> The appended script illustrates the current implementation.
>
> rolf
>
> package require sqlite3
>
> sqlite3 one tmp.db
> one eval {
> CREATE TABLE IF NOT EXISTS some(value text);
> INSERT INTO some VALUES('foo');
> BEGIN IMMEDIATE TRANSACTION
> }
>
> proc busyhandler {args} {
> global counter
> puts "args: '$args'"
> incr counter
> if {$counter > 3} {
> return 1
> }
> return 0
> }
>
> sqlite3 two tmp.db
> two busy busyhandler
>
> catch {two eval { DELETE FROM some }} errMsg
> puts $errMsg
> two timeout 500
> catch {two eval { DELETE FROM some }} errMsg
> puts $errMsg
> two busy busyhandler
> catch {two eval { DELETE FROM some }} errMsg
> puts $errMsg
>
>
>
> Rolf Ade  writes:
>> The documentation of the busy method at
>>
>> http://sqlite.org/tclsqlite.html#busy
>>
>> should be more specific, with regards of the arguments of the Tcl
>> callback procedure.
>>
>> The documentation currently reads:
>>
>> The "busy" method, like "timeout", only comes into play when the
>> database is locked. But the "busy" method gives the programmer much
>> more control over what action to take. The "busy" method specifies a
>> callback Tcl procedure that is invoked whenever SQLite tries to open
>> a locked database. This callback can do whatever is desired.
>> Presumably, the callback will do some other useful work for a short
>> while (such as service GUI events) then return so that the lock can
>> be tried again. The callback procedure should return "0" if it wants
>> SQLite to try again to open the database and should return "1" if it
>> wants SQLite to abandon the current operation.
>>
>> This doesn't specify, what arguments the Tcl callback procedure must
>> expect. Turns out, that the proc is called with one argument (the number
>> of how much the busy callback was already called for the current lock
>> situation, it seems). That should be explictly written in the
>> documentation, since it doesn't seem clearly obvious.
>>
>> What must be obvious is, that English is a foreign language to me.
>> Therefor, I'm shy to propose a phrase.
>>
>> Another plea, since I'm already writing: It isn't immediate and without
>> any doubt clear, how the "timeout" and the "busy" methods play together,
>> if both are used. I suspect, the timeout, if given, determines, how long
>> it lasts until the busy callback is called (and that for every round, if
>> the busy callback returned "0") but if it is this way (or not) isn't
>> said somewhere, if I see right.
>>
>> rolf
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Pragma to flag unknown pragma?

[Scott Hess]

https://www.sqlite.org/pragma.html has:
"No error messages are generated if an unknown pragma is issued.
Unknown pragmas are simply ignored. This means if there is a typo in a
pragma statement the library does not inform the user of the fact."

I just lost some time due to this, even though I was fully aware of
it.  My code wasn't working, so I instrumented to report errors, and
gradually dug things deeper and deeper.  It wasn't until I was
verifying statements line-by-line against sqlite3 in a terminal window
that I saw that I was setting journal_mod rather than journal_mode!

I realize that pragma don't have the compatibility guarantees that
other syntax has.  But that means I actually _would_ want my code to
start barfing if a PRAGMA stops being supported.  Say I'm issuing
"PRAGMA secure_delete=ON" - presumably it's for a reason, and I'm
going to want to revisit that reason if it's no longer supported.

One could perhaps fake pedantic pragma with SQLITE_FCNTL_PRAGMA in a
custom VFS, but that seems a little excessive for something like this.
Something like "PRAGMA pedantic_pragma = on" would be much slicker.

OK, back to the mines,
scott
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When does SQLite open/create files?

[Richard Hipp]

On 11/22/16, Jens Alfke  wrote:
>
> I did not close the database handle, so if SQLite is trying to open the file
> at this moment, it must have closed it earlier on its own.
> Or could it be the WAL file? Does SQLite ever close and reopen, or delete
> and re-create, the WAL?

No.  Did you run "PRAGMA temp_store=MEMORY;"?  This might be a
statement journal opening.

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

Re: [sqlite] creating a table

[R Smith]

On 2016/11/22 6:00 PM, John R. Sowden wrote:
That was a throw back to years ago.  I was trying to protect against 
y2k by making each dbf for 1 calendar year.  Also, these files are 
about 800k in size, so I was worried about storage and search time. 
Storage is not an issue anymore.  I will know about search time after 
learning about sql databases.  Sometimes over the last 45 years I have 
changed the dbf structure.  This way I only change starting at the 
year in question, then change the program to work with the structure 
modification.


SQLite (and indeed all modern SQL engines) can have tables many 
gigabytes (and even terabytes) in size with extremely fast lookups via 
simple indexing. I suggest you only create one file with one table and 
simply add a field to distinguish which year you are logging for 
(considering the size you've mentioned, this would be child's play for 
any DB engine - plus very fast).


It is not uncommon to find people here with terabyte multi-volume DBs 
with billions of rows of data (literally) - so any division of data sets 
is usually superfluous and should be reserved for the extreme case.


We always encourage experimentation with your specific data and 
hardware. Speed should be real fast, and speed differences between a 
table with 1 year's data and 10 years worth of data should be negligible 
(considering your current year dataset totals 800k). Further, the 
advantages of having your data in a single table (or at a minimum, a 
single DB) means you can query relationally between different years and 
get multi-year statistics without resorting to expensive joins and/or 
attaching external DBs.


Good luck!
Ryan

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

Re: [sqlite] When does SQLite open/create files?

[Jens Alfke]

> On Nov 21, 2016, at 12:39 PM, Richard Hipp  wrote:
> 
> On 11/21/16, Jens Alfke  wrote:
>> Does SQLite ever open or create files while a database connection is already
>> open?
> 
> (1) When you run ATTACH.
> 
> (2) The open/create of the original database is deferred until you
> actually need to read or write the database, so the open/create might
> occur later than you expect.
> 
> (3) Some complex queries involve the use of temporary files, unless
> you set PRAGMA temp_store=MEMORY or use equivalent compile-time
> options.

I just ran into a situation that doesn’t seem to fall into any of those 
categories. This is an iPhone app running in the background, after the OS (iOS 
10.1) has locked its file access so it can only use existing file descriptors 
(but can’t open/create/delete files.) The app continues running for over two 
minutes, querying and inserting to the database, then fails:

2016-11-22 13:00:35.229777  Filesystem access lost
...
2016-11-22 13:02:46.361583  Unknown error calling sqlite3_step (14: unable to 
open database file)
2016-11-22 13:02:46.361844  DB Query: INSERT INTO revs (doc_id, revid, parent, 
current, deleted, no_attachments, json, doc_type) VALUES (?, ?, ?, ?, ?, ?, ?, 
?)

I did not close the database handle, so if SQLite is trying to open the file at 
this moment, it must have closed it earlier on its own.
Or could it be the WAL file? Does SQLite ever close and reopen, or delete and 
re-create, the WAL?

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

Re: [sqlite] creating a table

[Simon Slavin]

On 22 Nov 2016, at 5:03pm, John R. Sowden  wrote:

> Thank you, but I am currently in the early learning phase of sql databases 
> and the sql language.  I am starting with Sqlite due to its relative 
> simplicity, and moving on to H2 to integrate it into Libre Office.  It's 
> commands like analyze that I have never heard of and need to become familiar 
> with.  In the mean time I am reading the Sqlite mail list, understanding 
> about 40% of it.  There seems to be a lot of sql tutorials on the web. 
> W3schools seems to be popular, so I am trying it.

W3Schools is an excellent resource for learning stuff and trying to remember 
how to do some little thing.

At this stage in your learning remember each time you encounter information to 
figure out if this is SQL -- which is likely to work in all SQL engines -- or 
SQLite-only.  Each SQL engine has its own weird things which work only in that 
one version of SQL.

For instance the "SELECT" command is for all implementations of SQL, but the 
"ANALYZE" command works only in SQLite, and the "ALTER INDEX" command works 
only in SQL SERVER.

You can learn lots about how to use SQLite from a web site intended for some 
other implementation of SQL.  But you may also learn a couple of things that 
just don't work in SQLite.

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

Re: [sqlite] creating a table

[John R. Sowden]

Thank you, but I am currently in the early learning phase of sql 
databases and the sql language.  I am starting with Sqlite due to its 
relative simplicity, and moving on to H2 to integrate it into Libre 
Office.  It's commands like analyze that I have never heard of and need 
to become familiar with.  In the mean time I am reading the Sqlite mail 
list, understanding about 40% of it.  There seems to be a lot of sql 
tutorials on the web. W3schools seems to be popular, so I am trying it.


John


On 11/22/2016 08:30 AM, Igor Korot wrote:

John,

On Tue, Nov 22, 2016 at 11:00 AM, John R. Sowden
 wrote:

That was a throw back to years ago.  I was trying to protect against y2k by
making each dbf for 1 calendar year.  Also, these files are about 800k in
size, so I was worried about storage and search time. Storage is not an
issue anymore.  I will know about search time after learning about sql
databases.  Sometimes over the last 45 years I have changed the dbf
structure.  This way I only change starting at the year in question, then
change the program to work with the structure modification.

Speed is simple:
Just create a table and then create an index as appropriate.

Shows us what query you want to run and we will help with the index creation.
Then you will need to run ANALYZE against the query and EXPLAIN QUERY PLAN.

Simple. ;-)

Thank you.


John




On 11/22/2016 12:33 AM, Christoph P.U. Kukulies wrote:

Just a thought about your TABLE named "log16":

I'm not at all a database expert but from the idea what a table is, I
would take a more general approach. CREATE a TABLE log with
columns year, logtext, timestamp a la:

CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY)

Just my 2cts.

Christoph


  Am 21.11.2016 um 19:08 schrieb John R. Sowden:

Thank you all for your answers and direction for further information.
Hopefully, I will not bring these subjects up again.  :)

John


On 11/21/2016 09:29 AM, John R. Sowden wrote:

First of all, I come from the dBASE/Foxpro world. There is no
distinction between a table and a database. I understand that with Sqlite a
database includes tables and other items.  The scenario that I do not
understand, is: say I have a log file with about 7 fields totaling about 80
characters per record. How do I name the database and table.  Currently I
say log16 for the year 2016.

Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer.
Neither one allows me to set the length of the text fields in the table
creation process.  How does the Sqlite know how long each record should be,
same with integers.

No help found in the documentation on the Sqlite web site.

John


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


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

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



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

Re: [sqlite] creating a table

[Igor Korot]

John,

On Tue, Nov 22, 2016 at 11:00 AM, John R. Sowden
 wrote:
> That was a throw back to years ago.  I was trying to protect against y2k by
> making each dbf for 1 calendar year.  Also, these files are about 800k in
> size, so I was worried about storage and search time. Storage is not an
> issue anymore.  I will know about search time after learning about sql
> databases.  Sometimes over the last 45 years I have changed the dbf
> structure.  This way I only change starting at the year in question, then
> change the program to work with the structure modification.

Speed is simple:
Just create a table and then create an index as appropriate.

Shows us what query you want to run and we will help with the index creation.
Then you will need to run ANALYZE against the query and EXPLAIN QUERY PLAN.

Simple. ;-)

Thank you.

>
> John
>
>
>
>
> On 11/22/2016 12:33 AM, Christoph P.U. Kukulies wrote:
>>
>> Just a thought about your TABLE named "log16":
>>
>> I'm not at all a database expert but from the idea what a table is, I
>> would take a more general approach. CREATE a TABLE log with
>> columns year, logtext, timestamp a la:
>>
>> CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY)
>>
>> Just my 2cts.
>>
>> Christoph
>>
>>
>>  Am 21.11.2016 um 19:08 schrieb John R. Sowden:
>>>
>>> Thank you all for your answers and direction for further information.
>>> Hopefully, I will not bring these subjects up again.  :)
>>>
>>> John
>>>
>>>
>>> On 11/21/2016 09:29 AM, John R. Sowden wrote:

 First of all, I come from the dBASE/Foxpro world. There is no
 distinction between a table and a database. I understand that with Sqlite a
 database includes tables and other items.  The scenario that I do not
 understand, is: say I have a log file with about 7 fields totaling about 80
 characters per record. How do I name the database and table.  Currently I
 say log16 for the year 2016.

 Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer.
 Neither one allows me to set the length of the text fields in the table
 creation process.  How does the Sqlite know how long each record should be,
 same with integers.

 No help found in the documentation on the Sqlite web site.

 John

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

Re: [sqlite] creating a table

[John R. Sowden]

That was a throw back to years ago.  I was trying to protect against y2k 
by making each dbf for 1 calendar year.  Also, these files are about 
800k in size, so I was worried about storage and search time. Storage is 
not an issue anymore.  I will know about search time after learning 
about sql databases.  Sometimes over the last 45 years I have changed 
the dbf structure.  This way I only change starting at the year in 
question, then change the program to work with the structure modification.


John



On 11/22/2016 12:33 AM, Christoph P.U. Kukulies wrote:

Just a thought about your TABLE named "log16":

I'm not at all a database expert but from the idea what a table is, I 
would take a more general approach. CREATE a TABLE log with

columns year, logtext, timestamp a la:

CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY)

Just my 2cts.

Christoph


 Am 21.11.2016 um 19:08 schrieb John R. Sowden:
Thank you all for your answers and direction for further information. 
Hopefully, I will not bring these subjects up again.  :)


John


On 11/21/2016 09:29 AM, John R. Sowden wrote:
First of all, I come from the dBASE/Foxpro world. There is no 
distinction between a table and a database. I understand that with 
Sqlite a database includes tables and other items.  The scenario 
that I do not understand, is: say I have a log file with about 7 
fields totaling about 80 characters per record. How do I name the 
database and table.  Currently I say log16 for the year 2016.


Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 
computer.  Neither one allows me to set the length of the text 
fields in the table creation process.  How does the Sqlite know how 
long each record should be, same with integers.


No help found in the documentation on the Sqlite web site.

John



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



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

Re: [sqlite] compilation warnings sqlite3

[Richard Hipp]

On 11/22/16, Christoph P.U. Kukulies  wrote:
> Am 22.11.2016 um 16:39 schrieb Richard Hipp:
>> On 11/22/16, Christoph P.U. Kukulies  wrote:
>>> Am 22.11.2016 um 16:27 schrieb Christoph P.U. Kukulies:
 To whom it may concern: while compiling sqlite3 under
 FreeBSD-11.0-RELEASE
 a compiler warning rushed over the screen:
>>> Sorry, this message got prematurely dismissed:
>> The line numbers in the warnings would be more helpful to us if you
>> told us which version of SQLite you are compiling against.
>>
>>> te3.Tpo -c sqlite/sqlite3.c -o sqlite/libsqlite_static_la-sqlite3.o
>>> sqlite/sqlite3.c:55862:12: warning: unused variable 'pBlock'
>>> [-Wunused-variable]
>>> sqlite3 *pBlock = 0;
>>>  ^
>
> Not sure cause it scrolled off screen but it may be: sqlite3-3.15.1_1
> (or sqlite3-3.7.17_1).

The "sqlite3 *pBlock = 0;" statement occurs on line 61402 in 3.15.1
and on line 52203 in version 3.7.17.  So you are apparently not
compile either of those.  The statement is line 55860 in SQLite 3.8.9
(2015-04-11, 1.5 years old, 23 subsequent releases).  That line number
is only 2 off from your warning message, suggesting that version 3.8.9
might be what you are compiling.

The "_1" suffix on your version numbers, and the fact that the line
numbers do not match any official SQLite releases, hint that the
FreeBSD people are making their on custom modifications to SQLite,
rather than using the tested and released versions.  If so, then you
should probably report the warnings to them, since for all we know the
warnings may be caused by their modifications.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compilation warnings sqlite3

[Christoph P.U. Kukulies]

Am 22.11.2016 um 16:39 schrieb Richard Hipp:

On 11/22/16, Christoph P.U. Kukulies  wrote:

Am 22.11.2016 um 16:27 schrieb Christoph P.U. Kukulies:

To whom it may concern: while compiling sqlite3 under
FreeBSD-11.0-RELEASE
a compiler warning rushed over the screen:

Sorry, this message got prematurely dismissed:

The line numbers in the warnings would be more helpful to us if you
told us which version of SQLite you are compiling against.


te3.Tpo -c sqlite/sqlite3.c -o sqlite/libsqlite_static_la-sqlite3.o
sqlite/sqlite3.c:55862:12: warning: unused variable 'pBlock'
[-Wunused-variable]
sqlite3 *pBlock = 0;
 ^
sqlite/sqlite3.c:60268:11: warning: unused variable 'key'
[-Wunused-variable]
u32 key = get4byte([i]->aData[8]);
^
sqlite/sqlite3.c:8954:26: warning: unused variable 'sqlite3one'
[-Wunused-const-variable]
SQLITE_PRIVATE const int sqlite3one = 1;
   ^

If it's of any interest for the developers to keep their code clean, I'm
just mentioning it here.

--
Christoph


Not sure cause it scrolled off screen but it may be: sqlite3-3.15.1_1
(or sqlite3-3.7.17_1).

--
Christoph

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

Re: [sqlite] compilation warnings sqlite3

[Richard Hipp]

On 11/22/16, Christoph P.U. Kukulies  wrote:
> Am 22.11.2016 um 16:27 schrieb Christoph P.U. Kukulies:
>> To whom it may concern: while compiling sqlite3 under
>> FreeBSD-11.0-RELEASE
>> a compiler warning rushed over the screen:
> Sorry, this message got prematurely dismissed:

The line numbers in the warnings would be more helpful to us if you
told us which version of SQLite you are compiling against.

>
> te3.Tpo -c sqlite/sqlite3.c -o sqlite/libsqlite_static_la-sqlite3.o
> sqlite/sqlite3.c:55862:12: warning: unused variable 'pBlock'
> [-Wunused-variable]
>sqlite3 *pBlock = 0;
> ^
> sqlite/sqlite3.c:60268:11: warning: unused variable 'key'
> [-Wunused-variable]
>u32 key = get4byte([i]->aData[8]);
>^
> sqlite/sqlite3.c:8954:26: warning: unused variable 'sqlite3one'
> [-Wunused-const-variable]
> SQLITE_PRIVATE const int sqlite3one = 1;
>   ^
>
> If it's of any interest for the developers to keep their code clean, I'm
> just mentioning it here.
>
> --
> Christoph
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] compilation warnings sqlite3

[Christoph P.U. Kukulies]

Am 22.11.2016 um 16:27 schrieb Christoph P.U. Kukulies:
To whom it may concern: while compiling sqlite3 under 
FreeBSD-11.0-RELEASE

a compiler warning rushed over the screen:

Sorry, this message got prematurely dismissed:

te3.Tpo -c sqlite/sqlite3.c -o sqlite/libsqlite_static_la-sqlite3.o
sqlite/sqlite3.c:55862:12: warning: unused variable 'pBlock' 
[-Wunused-variable]

  sqlite3 *pBlock = 0;
   ^
sqlite/sqlite3.c:60268:11: warning: unused variable 'key' 
[-Wunused-variable]

  u32 key = get4byte([i]->aData[8]);
  ^
sqlite/sqlite3.c:8954:26: warning: unused variable 'sqlite3one' 
[-Wunused-const-variable]

SQLITE_PRIVATE const int sqlite3one = 1;
 ^

If it's of any interest for the developers to keep their code clean, I'm 
just mentioning it here.


--
Christoph

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

[sqlite] compilation warnings sqlite3

[Christoph P.U. Kukulies]

To whom it may concern: while compiling sqlite3 under FreeBSD-11.0-RELEASE
a compiler warning rushed over the screen:


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

Re: [sqlite] CREATE TABLE fails

[David Raymond]

It's needed. The arrow coming out of [column-def] (visually) goes past 
[table-constraint] first, with the option to loop down to a comma on its way to 
a [table-constraint]


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Niall O'Reilly
Sent: Tuesday, November 22, 2016 9:22 AM
To: SQLite mailing list
Subject: Re: [sqlite] CREATE TABLE fails


   I was going to add "missing comma before PRIMARY" as well, but
   https://www.sqlite.org/lang_createtable.html doesn't seem to indicate
   that a comma is required between a column-def and a table-constraint.

   Best regards,
   Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE TABLE fails

[Niall O'Reilly]

On 22 Nov 2016, at 14:35, David Raymond wrote:

It's needed. The arrow coming out of [column-def] (visually) goes past 
[table-constraint] first, with the option to loop down to a comma on 
its way to a [table-constraint]


  Thanks.  My gut told me one thing, my eyes another.  Eyes were wrong.

  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE TABLE fails

[Niall O'Reilly]

On 22 Nov 2016, at 14:03, Richard Hipp wrote:


On 11/22/16, Igor Korot  wrote:

Hi, ALL,
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
 "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" 
char(129) NOT

NULL
, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, 
"abc_hdr"

char(
254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31),
"abc_case"
smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" 
char(31),

"abc_bm
ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit"
char(31), "a
bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");


Missing comma (,) in between "KEY" and "abc_tnam".


  Or perhaps (if Igor's intent is to use three columns as a compound 
primary
  key) missing parens ['(' ... ')'] around the list of columns after 
KEY ?


  I was going to add "missing comma before PRIMARY" as well, but
  https://www.sqlite.org/lang_createtable.html doesn't seem to indicate
  that a comma is required between a column-def and a table-constraint.

  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE TABLE fails

[Hick Gunter]

Perhaps you mean  ...,"a bc_tag" char(254), PRIMARY KEY ("abc_tnam", 
"abc_ownr", "abc_cnam") );

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Korot
Gesendet: Dienstag, 22. November 2016 14:46
An: Discussion of SQLite Database ; 
General Discussion of SQLite Database 
Betreff: [sqlite] CREATE TABLE fails

Hi, ALL,
SQLite version 3.13.0 2016-05-18 10:57:30 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
 "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT 
NULL , "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" 
char( 254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31), 
"abc_case"
smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31), 
"abc_bm ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit" 
char(31), "a bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");
Error: near ""abc_tnam"": syntax error
sqlite>

Where am I wrong?

Also, just out of curiosity - what symbols are allowed in the table/field name?
Is there any restrictions even for "" syntax?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] CREATE TABLE fails

[Quan Yong Zhai]

CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,

"abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT NULL

, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" char(

254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31), "abc_case"

smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31), "abc_bm

ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit" char(31), "a

bc_tag" char(254), PRIMARY KEY ("abc_tnam", "abc_ownr", "abc_cnam"));



Sent from Mail for Windows 10



From: Igor Korot
Sent: 2016年11月22日 21:46
To: Discussion of SQLite Database; 
General Discussion of SQLite Database
Subject: [sqlite] CREATE TABLE fails



Hi, ALL,
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
 "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT NULL
, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" char(
254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31), "abc_case"
smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31), "abc_bm
ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit" char(31), "a
bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");
Error: near ""abc_tnam"": syntax error
sqlite>

Where am I wrong?

Also, just out of curiosity - what symbols are allowed in the table/field name?
Is there any restrictions even for "" syntax?

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

Re: [sqlite] CREATE TABLE fails

[Richard Hipp]

On 11/22/16, Igor Korot  wrote:
> Hi, ALL,
> SQLite version 3.13.0 2016-05-18 10:57:30
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
>  "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT
> NULL
> , "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr"
> char(
> 254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31),
> "abc_case"
> smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31),
> "abc_bm
> ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit"
> char(31), "a
> bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");

Missing comma (,) in between "KEY" and "abc_tnam".
>
> Also, just out of curiosity - what symbols are allowed in the table/field
> name?
> Is there any restrictions even for "" syntax?

As long as it is within double-quotes, you can use any characters you
want other than U+.  On the other hand, good style suggests
limiting your choice of characters to ASCII alphanumerics and
underscore.

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

[sqlite] CREATE TABLE fails

[Igor Korot]

Hi, ALL,
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
 "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT NULL
, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" char(
254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31), "abc_case"
smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31), "abc_bm
ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit" char(31), "a
bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");
Error: near ""abc_tnam"": syntax error
sqlite>

Where am I wrong?

Also, just out of curiosity - what symbols are allowed in the table/field name?
Is there any restrictions even for "" syntax?

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

Re: [sqlite] Table name syntax

[David Raymond]

Makes sense, thanks. For one-offs and things like the "Copying from one table 
to another" thread that aren't going to be part of a regular running program 
it's easy enough to use the alias version.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Monday, November 21, 2016 4:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] Table name syntax

On 11/21/16, Don V Nielsen  wrote:
>> And since the "*" forms are considered bad style
>
> I have done this, not knowing it is bad style. Can you provide some reasons
> why it is bad?

Years later when somebody does "ALTER TABLE ... ADD COLUMN" your
application will begin doing unnecessary work to extract columns that
or not used (best case) or fail completely when it gets back a
different number of columns from what it expected (worst case).

Or, somebody my use the techniques outlined in
https://www.sqlite.org/lang_altertable.html#otheralter to change the
order of the columns in the table, which would definitely break your
application.

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

Re: [sqlite] creating a table

[Christoph P.U. Kukulies]

Just a thought about your TABLE named "log16":

I'm not at all a database expert but from the idea what a table is, I 
would take a more general approach. CREATE a TABLE log with

columns year, logtext, timestamp a la:

CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY)

Just my 2cts.

Christoph


 Am 21.11.2016 um 19:08 schrieb John R. Sowden:
Thank you all for your answers and direction for further information.  
Hopefully, I will not bring these subjects up again.  :)


John


On 11/21/2016 09:29 AM, John R. Sowden wrote:
First of all, I come from the dBASE/Foxpro world. There is no 
distinction between a table and a database. I understand that with 
Sqlite a database includes tables and other items.  The scenario that 
I do not understand, is: say I have a log file with about 7 fields 
totaling about 80 characters per record.  How do I name the database 
and table.  Currently I say log16 for the year 2016.


Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 
computer.  Neither one allows me to set the length of the text fields 
in the table creation process.  How does the Sqlite know how long 
each record should be, same with integers.


No help found in the documentation on the Sqlite web site.

John



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