[sqlite] VFS xFullPathname cannot access URI parameters

[Chris Warner]

In 3.31.1, attempting to access URI Parameters via the sqlite3_uri_* functions 
inside a VFS xFullPathname function returns junk.  This was not an issue in 
3.30.1 or 3.31.0.

The internals of those functions use the new databaseName() function, which 
attempts to find the database name from the path passed info sqlite3_open_v2(). 
 According to the function documentation:

> This only works if the filename passed in was obtained from the Pager.

However, the xFullPathname function is called before the Pager has opened the 
file, the parameter passed to sqlite3_uri_* functions aren't prefixed by the 4 
null bytes, thus the databaseName() call steps backward too far.

Minimal reproducer:

--

#include 
#include 

int test_full_path_name(sqlite3_vfs* vfs, const char* zName, int nOut, char* 
zOut)
{
  auto test_param = sqlite3_uri_parameter(zName, "test_param");
  if (!test_param)
  {
    return SQLITE_ERROR;
  }

  return SQLITE_OK;
}

int main()
{
  static sqlite3_vfs uzip_vfs = {
      3,
      0,
      sqlite3_vfs_find(nullptr)->mxPathname,
      nullptr,
      "test-vfs",
      nullptr,
      sqlite3_vfs_find(nullptr)->xOpen,
      sqlite3_vfs_find(nullptr)->xDelete,
      sqlite3_vfs_find(nullptr)->xAccess,
      test_full_path_name,
      sqlite3_vfs_find(nullptr)->xDlOpen,
      sqlite3_vfs_find(nullptr)->xDlError,
      sqlite3_vfs_find(nullptr)->xDlSym,
      sqlite3_vfs_find(nullptr)->xDlClose,
      sqlite3_vfs_find(nullptr)->xRandomness,
      sqlite3_vfs_find(nullptr)->xSleep,
      sqlite3_vfs_find(nullptr)->xCurrentTime,
      sqlite3_vfs_find(nullptr)->xGetLastError,
      sqlite3_vfs_find(nullptr)->xCurrentTimeInt64,
      sqlite3_vfs_find(nullptr)->xSetSystemCall,
      sqlite3_vfs_find(nullptr)->xGetSystemCall,
      sqlite3_vfs_find(nullptr)->xNextSystemCall};

  int rc = sqlite3_vfs_register(_vfs, 0);
  if (rc != SQLITE_OK)
  {
    std::cout << "ERROR (" << rc << "): vfs registration failed." << std::endl;
  }

  std::string open_path{"file:E:/Temp/test.sqlite?test_param=hello"};

  sqlite3* db{nullptr};
  rc = sqlite3_open_v2(open_path.c_str(), , SQLITE_OPEN_READONLY | 
SQLITE_OPEN_URI, "test-vfs");
  if (rc != SQLITE_OK)
  {
    std::cout << "ERROR (" << rc << "): opening the database failed." << 
std::endl;
  }

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

Re: [sqlite] log() in sqlite3

[Chris Brody]

The log function does seem to be supported by extension-functions.c which
is available from here: https://www.sqlite.org/contrib

On Fri, Jan 31, 2020 at 1:30 PM David Raymond 
wrote:

> The core functions of the library are fairly unlikely to get expanded upon
> at this point, so it's up to the application using the SQLite library to
> expand on them.
>
> You can load an extension which has those functions. Or the C API gives
> you means to add your own functions.
>
> For Python, the built in sqlite3 module has the create_function() method
> for a connection which will register a function.
>
> https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function
>
> Once you register the function you can use it right in your sql text like
> any other function,
> "update tbl set field2 = log(field1);"
>
> And you don't need to do the more tedious:
> a) Get original data with statement 1
> b) Do calculations in the outside program
> c) Use the result in statement 2
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Peng Yu
> Sent: Friday, January 31, 2020 12:42 PM
> To: SQLite mailing list 
> Subject: [sqlite] log() in sqlite3
>
> Hi,
>
> I see that many math functions (like log()) are not in sqlite3. It
> seems that SQL standard doesn't have them.
>
> https://www.sqlite.org/lang_corefunc.html
>
> But since sqlite3 contains non-standard functions anyway. Would it be
> considered to add those functions?
>
> Given the current version of sqlite3, is the only choice of computing
> log() to get the data into another language (e.g., python) and compute
> the log over there? Thanks.
>
> --
> Regards,
> Peng
> ___
> 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] "Standard SQL" ?

[Chris Brody]

A few resources I found from https://www.google.com/search?q=sql+standard :

   - https://en.wikipedia.org/wiki/SQL
   -
   
https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/#gref
   - https://dev.to/0xcrypto/who-owns-the-sql-standard-76m
   - https://modern-sql.com/standard
   - https://docs.oracle.com/cd/B28359_01/server.111/b28286/intro002.htm

It seems to me like the last ANSI standard was published in 2016.

It sound to me like implementors would generally implement a ``common SQL
subset'' or ``common subset of the ANSI SQL standard''.

My (US) $0.02 worth.



On Thu, Jan 30, 2020 at 2:20 PM Simon Slavin  wrote:

> I would appreciate your help.  Reading a technical article today, I came
> across a casual reference to "Standard SQL" as if it was a well-known
> thing.  This worried me since I've never heard the term and I'm meant to
> know about such things.
>
> It doesn't seem to refer to the official standard for SQL, which is huge
> and contains a plethora of features implemented once or never.  The author
> seemed to think it was a sort of 'core SQL' – features identically
> implemented by all, or most, of the well-known SQL engines.
>
> The one possibility I can think of is SQL:1999.  This is the first version
> which has features marked as 'mandatory' or 'optional'.  A full
> implementation of all mandatory features could, I suppose, be called
> "Standard SQL", but I've never heard of that term being used for that.
>
> Have any of you been using this term for a meaning other than "Fully
> conforming to SQL:2019 (or whatever version you think current) ?  Do you
> have documentation somewhere ?  Or are my suspicions correct and there's no
> such thing ?
> ___
> 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] A crash bug in sqlite

[Chris Brody]

> Yes.  I discovered the same thing independently.  The previous fix was
> subtly wrong.  Please try the latest trunk version.

Will there be a patch release, or should we just wait for the next minor?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deterministic random sampling via SELECT

[Chris Peachment]

In the very old days before computers were common, a random number
table appeared at the back of many statistical texts. This was used
to select a series of random numbers which would then be used as
look-up indices into some other data set.

You could do the same:

  1. generate a list of pseudo-random numbers, using a pre-defined
 seed value, over the range 1 .. count(*) of records in table,

  2. use that list as record id values to select the desired subset
 of the data in the table.

This would be done in two separate operations, possibly with a
storage of the generated numbers in a separate table which could
be used in the query of the main data.

Since it is a pseudo-random number series, you can repeat it as
often as needed using the same seed value.

Chris


On Thu, 7 Nov 2019, at 15:15, Merijn Verstraaten wrote:
> 
> > On 7 Nov 2019, at 19:16, David Raymond  wrote:
> > 
> > Along those lines SQLite includes the reverse_unordered_selects pragma
> > https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects
> > which will flip the order it sends rows in queries that don't explicitly 
> > specify an ordering. It's there to assist you in finding spots in your code 
> > where you might be relying on implicit ordering when you really shouldn't 
> > be.
> 
> Like the rest of this threads, this is just pointing out why the things 
> in my initial email don't work, but I already knew that. Which is why I 
> asked for help to see if there is a way to do what I want that *does* 
> work. I don't care particularly about the details of "can I control the 
> order the condition is evaluated", it's just that all reasonable ways 
> to sample large streams that I know would require a deterministic order.
> 
> If someone has a different/better idea on how to return just a random 
> sample from a query in a repeatable way, I'm all ears.
> 
> So far the only suggestion was "use some non-deterministic random 
> sampling method and store the result", but since my samples are large 
> and I have lots of them, this would balloon my storage by >100x and I 
> don't have the available storage to make that work.
> 
> - Merijn
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> Attachments:
> * signature.asc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Chris Green]

Philippe RIO <51...@protonmail.ch> wrote:
> A short question : how could I know if I am reading the last record with
> sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> function for that case which returns SQLITE_DONE? A function which is one
> record in advance from sqlite3_step.
> 
What do you mean by "last record"?

-- 
Chris Green
·

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

Re: [sqlite] DBMS Normalization Query

[Chris Locke]

Short answer.  Duplicate data in a database is bad.  Take school
departments.  For each teacher, do you put "History", "Chemistry",
"Biology" for their department?  What happens if someone enters "Bioolgy"
instead?  So for your point #1, "as soon as duplicate data is possible".
Maybe not for surnames or firstnames, but certainly for address counties,
departments, car types, etc, etc.  As soon as there is a definite list for
some data.
#2, #3.  Wikipedia is your friend here.

On Thu, Oct 3, 2019 at 2:47 PM shivambhatele 
wrote:

> Hello All,
>
> I am looking to explore more about normalization in DBMS. I am confused
> about some points. Can anyone tell me about these points? I have to google
> it to read about all these points and I have found some blogs like this
> normalization in DBMS    but
> still, I am not cleared some points
>
> 1. When is the process of normalization used?
> 2. Boyce and Codd Normal Form
> 3. 1NF, 2NF, and 3NF
>
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] 3.29.0 .recover command

[Chris Locke]

>  I got foreign key constraint failures

I don't know why one would work and one would fail, but usually, this
occurs when you insert a record which has foreign keys to another table,
but that table hasn't been imported yet.  The workaround is usually to
ensure all the 'lookup' tables are done first, so when the main record is
inserted, the required record exists, or to turn off foreign key checks,
and only put them into the database once all the imports have completed.


On Tue, Aug 6, 2019 at 11:27 AM Olivier Mascia  wrote:

> On one database instance, a .dump command gives me (among many other
> lines) things like:
>
> INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
> while the output of .recover command gives me things this way:
>
> INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1,
> 11237795927160, 11868 );
>
> I'm wondering why these differences in the way to construct the
> instructions to rebuild a sound database instance. What are the (probably
> rightful) motivations?
>
> 1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping
> wanted, why not double quotes instead of single quotes?
> 2) Why do the insert statement prefer to name and repeat, ad nausea, the
> column names on each insert when, apparently, the shortcut syntax
> capitalizing on the known column order in the schema might seem much less
> verbose?
>
> On the real DB I quickly tested .recover on (with no reason, I have
> nothing to recover, just testing the feature) I had an issue while
> rebuilding a new DB from the script made by .recover. I got foreign key
> constraint failures (which I have not yet traced exactly).
>
> sqlite> .once system.sql
> sqlite> .recover
>
> sqlite3 recover.db
> sqlite> .read system.sql
> Error: near line 14658: FOREIGN KEY constraint failed
> Error: near line 14659: FOREIGN KEY constraint failed
> Error: near line 14660: FOREIGN KEY constraint failed
> sqlite> .q
>
> While doing the same kind of work around .dump worked nicely:
>
> sqlite> .once systemd.sql
> sqlite> .dump
>
> sqlite3 dump.db
> sqlite> .read systemd.sql
> sqlite> .q
>
> The source test db passes successfully those tests:
>
> sqlite> pragma integrity_check;
> integrity_check
> ok
> sqlite> pragma foreign_key_check;
> sqlite> .dbconfig
>enable_fkey on
> enable_trigger on
> fts3_tokenizer off
> load_extension on
>   no_ckpt_on_close off
>enable_qpsg off
>trigger_eqp off
> reset_database off
>  defensive off
>writable_schema off
> legacy_alter_table off
>dqs_dml off
>dqs_ddl off
>
> Again, I have no recovery to attempt for now. I was just exercising the
> .recover feature for learning, using a db I'm not suspecting of anything.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
>
> ___
> 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] Proposal: SQLite on DNA

[Chris Brody]

> > May I humbly suggest that the development team look into porting to a new 
> > platform:
>
> > 

+1 (+100)

> As in Viral File System?

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

Re: [sqlite] Quirks of SQLite. Was: Version 3.29.0

[Chris Locke]

Typos \ suggested amendments to quirks.html

Section 2
"When ever comparing SQLite to other SQL database engines"
When ever should be one word. "Whenever comparing SQLite to other SQL
database engines"

"An application interact with the database engine"
should be, "An application *interacts* with the database engine"

"using function calls, not be sending messages to a separate process"
should be, "using function calls, not *by* sending messages to a separate
process"

Section 3.2
"SQLite as no DATETIME datatype"
should be, "SQLite *has* no DATETIME datatype"

Section 4
"there where already countless millions of databases"
should be, "there *were* already countless millions of databases"


Thanks,
Chris


On Thu, Jul 11, 2019 at 3:22 PM Richard Hipp  wrote:

> On 7/11/19, David Raymond  wrote:
> > I don't see [quirks.html]
> > anywhere on https://sqlite.org/docs.html , maybe add it to the "Overview
> > Documents" section?
>
> The quirks.html document is now linked in the Overview Documents section.
>
> https://www.sqlite.org/quirks.html
>
> EVERYONE:  If you have personally experienced some unusual or
> unexpected feature of SQLite that you think should be added to
> "quirks.html", please follow-up to this thread, or send me private
> email, so that I can consider adding it.  Thanks.
>
> --
> 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] round function inconsistent

[Chris Locke]

>  Yours is clearly incorrect

lol.  "Your software gives a different result to the one I expect,
therefore its wrong."

You are aware that your first example (3.255) probably isn't being stored
internally as a single.
Just because computers work outside your understanding doesn't make them
'incorrect'.

On Fri, May 24, 2019 at 2:15 PM Thomas Kurz  wrote:

> Sorry, but even Excel (which usually isn't very good at decimal math)
> gives correct results:
>
> ROUND(3.255;2) --> 3.26
> ROUND(3.254999;2) --> 3.25
>
> Yours is clearly incorrect.
>
>
> - Original Message -
> From: Richard Hipp 
> To: SQLite mailing list 
> Sent: Friday, May 24, 2019, 14:44:52
> Subject: [sqlite] round function inconsistent
>
> On 5/24/19, Jose Isaias Cabrera  wrote:
>
> > Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> > exist?  Thanks.
>
>
> Consider these two queries:
>
>SELECT round(3.255,2);
>SELECT round(3.2548,2);
>
> Do you expect them to give different answers?
>
> If so, do you realize that 3.255 and 3.48 are in fact the
> exact same floating point number?  That number in (unambiguous) hex
> notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
> the round() function to return different answers for two cases where
> it is given bit-for-bit identical inputs?  How does it know which
> answer to give?
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Series of statements results in a malformed database disk image

[Chris Locke]

Edit: HOWEVER, just ran an integrity check, and that did fail.
"wrong # of entries in index sqlite_autoindex_t1_1"



On Thu, May 9, 2019 at 3:52 PM Chris Locke  wrote:

> Are you using a new database when you create your table, or using an
> existing database?
> Are you writing your database locally?
> What operating system / sqlite version are you using?
>
> The above test works for me...
>
> > Execution finished without errors.
>
> > Result: 1 rows returned in 62ms
>
> > At line 4:
>
> > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
>
>
>
> On Thu, May 9, 2019 at 3:47 PM Manuel Rigger 
> wrote:
>
>> Hi,
>>
>> I discovered a sequence of statements that results in a malformed database
>> disk image:
>>
>> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
>> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
>> UPDATE t1 SET c0 = NULL;
>> UPDATE OR REPLACE t1 SET c1 = 1;
>> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>>
>> The last statement returns the following:
>> |1.0
>> Error: near line 5: database disk image is malformed
>>
>> Unlike some of my previous test cases, this actually looks like something
>> that could happen in practice, or what do you think?
>>
>> Best,
>> Manuel
>> ___
>> 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] Series of statements results in a malformed database disk image

[Chris Locke]

Are you using a new database when you create your table, or using an
existing database?
Are you writing your database locally?
What operating system / sqlite version are you using?

The above test works for me...

> Execution finished without errors.

> Result: 1 rows returned in 62ms

> At line 4:

> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);




On Thu, May 9, 2019 at 3:47 PM Manuel Rigger 
wrote:

> Hi,
>
> I discovered a sequence of statements that results in a malformed database
> disk image:
>
> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
> UPDATE t1 SET c0 = NULL;
> UPDATE OR REPLACE t1 SET c1 = 1;
> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
> The last statement returns the following:
> |1.0
> Error: near line 5: database disk image is malformed
>
> Unlike some of my previous test cases, this actually looks like something
> that could happen in practice, or what do you think?
>
> Best,
> Manuel
> ___
> 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] Unexpected or wrong result and no warning/error, bug?

[Chris Locke]

> create table t(s varchar(5));

Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This doesn't help your issue directly, but does highlight that you've not
read the SQLite documentation, and aren't creating tables properly.


On Fri, Apr 12, 2019 at 5:06 PM Shawn Wagner 
wrote:

> From the documentation (https://www.sqlite.org/lang_update.html)
>
> If a single column-name appears more than once in the list of assignment
> expressions, all but the rightmost occurrence is ignored.
>
>
> On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou  wrote:
>
> > create table t(s varchar(5));
> >
> > insert into t values('US'),('USA');
> >
> > update t set s = replace(s, 'USA', '___'),
> >  s = replace(s,'US','USA'),
> >  s = replace(s,'___','USA');
> >
> > select * from t;
> >
> > -- Expected answer:
> > -- USA
> > -- USA
> > --
> > -- MySQL gets it right
> > -- Postgres prints error about setting the same column multiple times
> > -- SQLite3 (latest and older) no changes or wrong result but no
> > error/warning
> > ___
> > 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] Remove row to insert new one on a full database

[Chris Locke]

Arthur - are you running SQLite in parallel runs?
If you access the database file using the sqlite3 command-line tool, and
try to execute the same SQL commands, do you get the same error ?

SQLite makes a temporary 'journal' file while it's working.  I think that,
on your platform, by default it will be in the same directory as the
database file.  Does your application have enough privileges to create new
files in that directory ?
What version of SQLite are you using?  It might be an old version.
Also, removing rows doesn't necessarily remove space in the database file.
If you're running out of disk space, it could be you need to vacuum your
database file.  How large is the database?  How much disk space do you have
left?


Thanks,
Chris

On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel 
wrote:

> OK, I wasn't clear.
> I'm limited in space so when the DB is full (when sqlite3_exec() returns
> SQLITE_FULL when I try to insert a new row), I remove the oldest row and
> retry to insert the new one.
> The data is always the same. That's why removing one row should be enough
> to insert a new one.
> My problem is that some times I need to remove many rows to add one new
> one.
> This is basically my code:
>
> main()
> {
> sqlite3* db;
> int rc;
> char *err_msg = 0;
> int counter;
> bool full = false;
> int id;
>
> /* --- Create DB --- */
> rc = sqlite3_open("db_file.db", );
> printf("1. rc = %d\n", rc);
>
> rc = sqlite3_exec(db,
>  "CREATE TABLE IF NOT EXISTS data_table"
>  "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
> INTEGER, col3 INTEGER)",
>  0, 0, _msg);
> printf("2. rc = %d\n", rc);
>
> /* --- Limit database size to 50 K --- */
> rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
> printf("3. rc = %d\n", rc);
> rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
> printf("4. rc = %d\n", rc);
> rc = sqlite3_exec(db, "VACUUM", 0, 0, _msg);  // resize file
> printf("5. rc = %d\n", rc);
>
>
> /* --- Fill DB --- */
> for (int i = 0 ; i < 5000 ; i++) {
> counter = 0;
> do {
> rc = sqlite3_exec(db,
>   "INSERT INTO data_table"
>   "(col1, col2, col3) VALUES(1, 2, 3)",
>   0, 0, _msg);
>
> if (rc == SQLITE_FULL) {
> if (!full) {
> printf("%d - DB full\n", id);
> full = true;
> }
> counter++;
> // delete oldest row
> int stat = sqlite3_exec(db,
>  "DELETE FROM data_table WHERE id IN "
>  "(SELECT id FROM data_table ORDER BY id LIMIT
> 1)",
>  0, 0, _msg);
> if (stat != SQLITE_OK) {
> printf("Delete error %d\n", stat);
> }
> } else if (rc == SQLITE_OK) {
> id = sqlite3_last_insert_rowid(db);
> } else /*if (rc != SQLITE_OK)*/ {
> printf("Insert error %d\n", rc);
> }
> } while (rc == SQLITE_FULL);
>
> if (counter > 2) {
> printf("%d - %d rows was removed\n", id, counter);
> }
> }
>
> printf("close -> %d\n", sqlite3_close(db));
> }
>
>
> Following the output:
>
> 1. rc = 0
> 2. rc = 0
> 3. rc = 0
> 4. rc = 0
> 5. rc = 0
> 3959 - DB full
> 3960 - 109 rows was removed
> 4044 - 92 rows was removed
> 4128 - 86 rows was removed
> 4212 - 85 rows was removed
> 4296 - 85 rows was removed
> 4380 - 84 rows was removed
> 4464 - 84 rows was removed
> 4548 - 84 rows was removed
> 4632 - 84 rows was removed
> 4716 - 84 rows was removed
> 4800 - 84 rows was removed
> 4884 - 84 rows was removed
> 4968 - 84 rows was removed
> close -> 0
>
> Thanks
>
>
> On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel 
> wrote:
>
> >
> > Hello
> >
> > When I try to insert new data to a full SQLite database, I need to remove
> > much more than really needed. I'm doing the following:
> >
> > while(1) {
> > do {
> > status = insert_1_row_to_db();
> > if (status == full) {
> > remove_one_row_from_db();
> > }
> > } while (status == full);}
> >
> > The i

Re: [sqlite] Remove row to insert new one on a full database

[Chris Locke]

> When the database is full

What do you mean by a full database?  Do you mean when the operating system
has run out of disk space?
A SQLite database can hold millions of rows, so technically, a database
cannot be 'full'.

It would be easier explaining the full issue and what you consider the
problem, rather than asking for help on a solution which may not be
required.


Thanks,
Chris

On Thu, Apr 4, 2019 at 11:53 AM Arthur Blondel 
wrote:

> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
> ___
> 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] Typo - https://www.sqlite.org/see/doc/trunk/www/index.wiki

[Chris Locke]

On the page https://www.sqlite.org/see/doc/trunk/www/index.wiki in the 'key
links' section, there is a link to 'Files in the lastest release of SEE'.
This should be 'latest' and not 'lastest'.


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

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Chris Brody]

> SQLite is built from (the combined) source as part of the project build.
> [...]

If you want extra safety, I would recommend you consider using
SQLITE_DEFAULT_SYNCHRONOUS=3 which is the equivalent to using PRAGMA
synchronous=EXTRA.

Quick references:
* https://www.sqlite.org/pragma.html#pragma_synchronous
* https://www.sqlite.org/compile.html#default_synchronous

> What is the journal mode?
>
> That is a very interesting question, as a journal file certainly seems
relevant to power fail issues

The most common journal modes are the following:
* default journal mode is to use a rollback journal
* newer journal mode is WAL

Recommended reading: https://www.sqlite.org/wal.html

I think WAL could be safer if you do not use SQLITE_DEFAULT_SYNCHRONOUS=3.

What I don't like about WAL is the need for occasional checkpointing
ref: https://www.sqlite.org/wal.html#automatic_checkpoint

I hope this helps a little.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Number of open connections

[Chris Locke]

Does SQLite keep a count of the number of current open connections to the
database?
On the DB4S mailing list, there is an enquiry (
https://github.com/sqlitebrowser/sqlitebrowser/issues/1798) about
encryption failing due to the database being open.  Was wondering whether a
PRAGMA or function returned the number of open database connections.


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

Re: [sqlite] Number of open connections

[Chris Locke]

Thanks Richard for the reply.  Appreciated.

On Tue, Mar 12, 2019 at 2:49 AM Richard Hipp  wrote:

> On 3/11/19, Chris Locke  wrote:
> > Does SQLite keep a count of the number of current open connections to the
> > database?
>
> No.
>
> SQLite can find out if some other connection has the database open in
> WAL mode, or if some other database has an active transaction, because
> it needs to know those things.  But there is no counter.
>
>
> --
> 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

[sqlite] Number of open connections

[Chris Locke]

Does SQLite keep a count of the number of current open connections to the
database?
On the DB4S mailing list, there is an enquiry (
https://github.com/sqlitebrowser/sqlitebrowser/issues/1798) about
encryption failing due to the database being open.  Was wondering whether a
PRAGMA or function returned the number of open database connections.


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

Re: [sqlite] [EXTERNAL] Integration with las version of SQLite

[Chris Locke]

I fear the OP is referring to DB Browser for SQLite.  This recently
released v3.11.1.

@desarrollo - I would suggest contacting the DB Browser for SQLite
developers at this address:
https://github.com/sqlitebrowser/sqlitebrowser/issues


Thanks,
Chris

On Wed, Mar 6, 2019 at 3:47 PM Hick Gunter  wrote:

> The current version of SQLite itself is 3.27.2, Version 3.11.1 would be
> from 2016 and does not have window functions (introduced in Version 3.25)
>
> Please check which sqlite binding product you are using and see if it has
> a support page. If a query works from the sqlite shell but not from the
> program, it is not a problem with SQLite but with the
> binding/wrapper/whatever you are using.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von desarrollo ribisoft
> Gesendet: Mittwoch, 06. März 2019 16:28
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] Integration with las version of SQLite
>
> Good morning,
>
> I have a project in c# which I integrate with SQLite and I have very good
> performance at all, I've had no troubles, but yesterday I updated to last
> version of SQLite 3.11.1 and began to use the ROW_NUMBER() function, and
> running the query in the interface of DB Lite I had no troubles, but when I
> run that same query in c# I'm getting errors because it doesn´t recognise
> that function, I'd like to know if you know how to update the connection or
> integration between c# and this last version of SQLite, thanks in advanced.
>
> --
> Muchas Gracias
>
> Cordialmente:
> ___
> 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 |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] Safe (atomic) db file snapshot and update

[Chris Locke]

> Multiple processes write to foo.db.

What method of journaling do you use?  WAL?

> Multiple processes read foo.db (no writes at all).

Do they open a connection, read, then close the connection, or do you open
a connection, read, read, read, read, until the process is terminated, THEN
close the connection?  (ie, is the connection open all the time)


Thanks,
Chris

On Wed, Mar 6, 2019 at 11:33 AM Anton Polonskiy 
wrote:

> Scenario 1:
> Multiple processes write to foo.db.
> I want to do some periodic snapshots.
> What is the best way to do this without interrupting/blocking writers?
> sqlite3 foo.db '.backup snapshot.db' ?
>
> Scenario 2:
> Multiple processes read foo.db (no writes at all).
> I need to update foo.db without readers interruption and blocking.
> What is the best way to do this?
> mv foo-new.db foo.db ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling ROLLBACK

[Chris Smith]

No


On Sat, Mar 2, 2019, 18:50 D Burgess  wrote:

> Does ROLLBACK release the transaction lock on the database ?
>
> I checked scripts on  this. Yes.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Cheers,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Site search bug - https://sqlite.org/

[Chris Locke]

The link you quote mentions SQLite 3.24 though... ?


Thanks,
Chris

On Wed, Feb 27, 2019 at 12:44 PM niki  wrote:

> This link demonstrates the problem:
>
> https://sqlite.org/search?s=c=3.24
>
> Best regards,
>
> Niki
>
> ___
> 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] Cannot Modify Table if Part of a View

[Chris Locke]

This issue was found via DB Browser for SQLite, but relates to SQLite, not
DB Browser for SQLite, so please bear with me

If a table is part of a view, then SQLite complains that "Error: error in
view view1: no such table: main.table11"

The link to the full issue is here:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686

Martin has followed the instructions here (
https://www.sqlite.org/lang_altertable.html#otheralter) which raises the
above error.
(link to comment:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686#issuecomment-464136036
)
We are using SQLite 3.27.1 and 3.26 (in two different applications) and (as
per the above GitHub thread) 3.27.1 mentions fixing an ALTER TABLE bug
(item 11) with views.  However, there is no WITH clause and no redundant
UNIQUE clauses involved in our case.

Its worked fine in earlier versions of SQLite, and note a comment about the
improved ALTER TABLE functionality:
"In version 3.25.0, the ALTER TABLE statement has been enhanced to run a
test-parse of the whole schema after it alters the schema, to make sure
that the edits it performed on the schema didn't break anything."

it sounds like this extra sanity check is what is causing the problem
described here which also explains why it worked for older versions of
SQLite.

In this issue (https://www.sqlite.org/src/tktview?name=31c6e64ff9) drh
describes a very similar issue to the one here. He is using a trigger
instead of a view but besides that is is pretty much the same problem.
Again some more explanations from the ticket:

"The DROP TABLE removes the table t1, which leaves a dangling reference to
t1 in the trigger. Then during the ALTER TABLE, the trigger is reparsed,
but the reparse fails due to the dangling reference."

Again, it sounds exactly like the issue we are having here. The second
ticket is still open, so no fix there yet. Last modification was 2018-10-03.

Is there any confirmation or further details on this issue at all?


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

Re: [sqlite] Vacuum into

[Chris Locke]

If you renamed file1.db to file1.bak, opened file1.bak, vacuum into
file1.db, close file1.bak, you have a backup pre-vacuum (just in case...)
and 'streamlines' the process some-what.
Obviously, you'd have to rename the file back again if the vacuum failed
(out of disk space, etc)

Just a thought


Chris

On Fri, Feb 8, 2019 at 4:22 PM David Raymond 
wrote:

> So to make sure I'm understanding it ok, with the new vacuum into command,
> if I'm the only user of a file, then the sequence...
>
> open file1
> vacuum into file2
> close file1
> delete file1
> rename file2 to file1
>
> ...is going to be potentially more than twice as fast as the old...
>
> open file1
> vacuum
>
> ...as it saves the whole re-write of the original file, along with all the
> rollback journal or wal writes that entails. Correct?
>
> (With of course the whole "make sure it actually finished and didn't just
> die" caveats before doing the delete and rename)
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_DBCONFIG_DEFENSIVE documentation looks confusing

[Chris Brody]

I think my sample code should have read as follows:

To activate the "defensive" flag for a database connection:
sqlite3_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1, NULL);

(I got the wrong prefix before, and I discovered that it crashes if I
do not add the NULL argument.)

I hope I got this right, really wish it were better documented.


On Tue, Jan 29, 2019 at 10:17 AM Chris Brody  wrote:
>
> I am very sorry to say that I have found the usage of the
> SQLITE_DBCONFIG_DEFENSIVE option to be somewhat confusing.
>
> From my first reading of https://www.sqlite.org/releaselog/3_26_0.html
> I thought SQLITE_DBCONFIG_DEFENSIVE was a compile-time option. (I was
> proven wrong pretty quickly.)
>
> Then I found the following in
> https://www.sqlite.org/c3ref/c_dbconfig_defensive.html to be
> confusing:
>
> Looking at this entry:
>
> #define SQLITE_DBCONFIG_DEFENSIVE 1010 /* int int* */
>
> seems to indicate to me that I should pass 2 integer values after
> SQLITE_DBCONFIG_DEFENSIVE in the sqlite_db_config() call (which I
> think is wrong). I found it especially confusing since the example
> code for SQLITE_DBCONFIG_RESET_DATABASE shows 2 integers after
> SQLITE_DBCONFIG_RESET_DATABASE.
>
> I think there should be example code for SQLITE_DBCONFIG_DEFENSIVE
> that reads something like this:
>
> To activate the "defensive" flag for a database connection:
> sqlite_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1);
>
> I think it would be ideal if there would be a compile-time flag that
> would tell SQLite to enable the "defensive" flag by default whenever
> the application opens a database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_DBCONFIG_DEFENSIVE documentation looks confusing

[Chris Brody]

I am very sorry to say that I have found the usage of the
SQLITE_DBCONFIG_DEFENSIVE option to be somewhat confusing.

From my first reading of https://www.sqlite.org/releaselog/3_26_0.html
I thought SQLITE_DBCONFIG_DEFENSIVE was a compile-time option. (I was
proven wrong pretty quickly.)

Then I found the following in
https://www.sqlite.org/c3ref/c_dbconfig_defensive.html to be
confusing:

Looking at this entry:

#define SQLITE_DBCONFIG_DEFENSIVE 1010 /* int int* */

seems to indicate to me that I should pass 2 integer values after
SQLITE_DBCONFIG_DEFENSIVE in the sqlite_db_config() call (which I
think is wrong). I found it especially confusing since the example
code for SQLITE_DBCONFIG_RESET_DATABASE shows 2 integers after
SQLITE_DBCONFIG_RESET_DATABASE.

I think there should be example code for SQLITE_DBCONFIG_DEFENSIVE
that reads something like this:

To activate the "defensive" flag for a database connection:
sqlite_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1);

I think it would be ideal if there would be a compile-time flag that
would tell SQLite to enable the "defensive" flag by default whenever
the application opens a database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite slow when lots of tables

[Chris Locke]

Ryan Smith has already covered this scenario.

"And to add to the slew of "Few-tables-many-rows rather than
Many-tables-few-rows" solutions offered, one thing to note:  After a single
table contains a few million rows, INSERTing will become slightly slower,
but the difference will still be measured in milliseconds rather than
seconds. This really is by far the fastest (and correctest) way."

On Tue, Jan 29, 2019 at 11:01 AM  wrote:

> Dear all,
>
> what happens if I put all data in a single table and this table become
> very huge (for example millions of rows)?
>
> Will I have same performace problems?
>
> Thanks.
>
>
> Regards.
>
> >
> > Il 28 gennaio 2019 alle 17.28 Simon Slavin 
> ha scritto:
> >
> > On 28 Jan 2019, at 4:17pm, mzz...@libero.it wrote:
> >
> > > >
> > > when the number of the tables become huge (about 15000/2
> tables) the first DataBase reading query, after Database open, is very slow
> (about 4sec.) while next reading operations are faster.
> > >
> > > How can I speed up?
> > >
> > > >
> > Put all the data in the same table.
> >
> > At the moment, you pick a new table name each time you write another
> set of data to the database. Instead of that, create just one big table,
> and add an extra column to the columns which already exist called
> "dataset". In that you put the string you previously used as the table name.
> >
> > SQL is not designed to have a variable number of tables in a
> database. All the optimization is done assuming that you will have a low
> number of tables, and rarely create or drop tables.
> >
> > Simon.
> >
> > ___
> > 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] SQLite slow when lots of tables

[Chris Locke]

I don't know - that's why I asked.  Thanks for the clarification.


On Mon, Jan 28, 2019 at 10:10 PM Warren Young  wrote:

> On Jan 28, 2019, at 2:44 PM, Chris Locke  wrote:
> >
> >> The table name should not be meaningful to your application; nothing in
> >> your application should conjure up a table name.
> >
> > I can't get my head around this advice.  Is this just for this occasion,
> or
> > for every application?  What if I'm writing a customer address book?  Am
> I
> > allowed a table called 'customers' ?  Thats meaningful to my application.
> > Not sure what your trying to advise here.
>
> He’s saying that if your customer is called Bill The Cat, you should not
> call the table holding that customer’s records “Bill the Cat”.
>
> How else do you end up with 2 tables in a single database, each
> created on the fly based on some unspecified event, as the OP talks about?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite slow when lots of tables

[Chris Locke]

> The table name should not be meaningful to your application; nothing in
> your application should conjure up a table name.

I can't get my head around this advice.  Is this just for this occasion, or
for every application?  What if I'm writing a customer address book?  Am I
allowed a table called 'customers' ?  Thats meaningful to my application.
Not sure what your trying to advise here.

On Mon, Jan 28, 2019 at 6:59 PM James K. Lowden 
wrote:

> On Mon, 28 Jan 2019 16:28:41 +
> Simon Slavin  wrote:
>
> > SQL is not designed to have a variable number of tables in a
> > database.  All the optimization is done assuming that you will have a
> > low number of tables, and rarely create or drop tables.
>
> This.
>
> The table name should not be meaningful to your application; nothing in
> your application should conjure up a table name.  It's an external
> factor your application is configured to use.
>
> If you're generating table names based on application data, you're
> insinuating data in the metadata.  When Simon says "SQL is not
> designed" for that, he's referring to the fact that the schema is
> expected to be relatively stable because it *describes* the data.  The
> system is designed to search for data in the tables, not among their
> names.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite slow when lots of tables

[Chris Locke]

Why do you need to create a new table each time?  Its easier to create a
relational database.  This means create one main table that might (for
example) have a 'tableID' field, which points to just one other table.
This means you only need two tables.  Not 20,000+
Just an idea.  Depends on what you're trying to achieve.

On Mon, Jan 28, 2019 at 4:18 PM  wrote:

> Dear,
>
> I developed an application that need to create 1 table with thousand of
> rows every time when a certain event occours.
>
> This works in a good way, but when the number of the tables become huge
> (about 15000/2 tables) the first DataBase reading query, after Database
> open, is very slow (about 4sec.) while next reading operations are faster.
>
> How can I speed up?
>
> Thanks.
>
>  Davide
> ___
> 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] Building SQLite DLL with Visual Studio 2015

[Chris Locke]

Just curious as to why you wouldn't choose option #2 - as that's what I use
with my VB .NET applications, which work quite well.  Just distribute the
.exe, then the two SQLite DLLs (well, three technically, as there are two
versions of the interop.dll)

Thanks,
Chris

On Mon, Jan 21, 2019 at 4:19 PM Simon Slavin  wrote:

>
>
> On 21 Jan 2019, at 11:16am, J Decker  wrote:
>
> > 4. Statically linked to and compiled with your datalayer code.
>
> What he said.  Also, you should be using the 'Amalgamation' download to do
> this, unless you need some compiler switches which are not supported by
> that source-set.
>
> Simon.
> ___
> 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] Sample Employee database ported to SQLite from MySQL

[Chris Locke]

> Just because something doesn't have to be calculated, means that it has
to be stored as text.

Sorry - forgot a 'doesn't'.
Just because something doesn't have to be calculated, doesn't mean that it
has to be stored as text.

On Thu, Dec 20, 2018 at 3:42 PM Chris Locke 
wrote:

> Just because something doesn't have to be calculated, means that it has to
> be stored as text.
> Its usually recommended to set the column affinity to the type of data
> you're storing.  If you're storing a number (and a model number is a
> numeric number) then it should be stored in a numeric field.  If your model
> number has punctuation, then yes, a text field is required.
> Its up to the application (although some would also argue the database) to
> validate data input, ie, ensure numeric data was inputted into a numeric
> field.
>
> On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden 
> wrote:
>
>> On Wed, 19 Dec 2018 10:55:11 +
>> Chris Locke  wrote:
>>
>> > Fields with '_no' are read as 'number' and so should be a number.
>> > OK, that doesn't always work for 'telephone_no' (they usually start
>> > with a 0
>>
>> Lots of numbers are labels that aren't meant to be calculated on.  Item
>> number, part number, model number, serial number, order number.
>> Anything that needs to be distinguished and isn't worth naming.
>>
>> It's never a good idea to store such numbers as numerical types.
>> There's always  a potential loss of information, be it the leading zero
>> or embedded '-' or multiple '.' characters.  Unless the "number" is a
>> quantity, for compuational purposes it's text.
>>
>> --jkl
>>
>> ___
>> 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] Sample Employee database ported to SQLite from MySQL

[Chris Locke]

Just because something doesn't have to be calculated, means that it has to
be stored as text.
Its usually recommended to set the column affinity to the type of data
you're storing.  If you're storing a number (and a model number is a
numeric number) then it should be stored in a numeric field.  If your model
number has punctuation, then yes, a text field is required.
Its up to the application (although some would also argue the database) to
validate data input, ie, ensure numeric data was inputted into a numeric
field.

On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden 
wrote:

> On Wed, 19 Dec 2018 10:55:11 +
> Chris Locke  wrote:
>
> > Fields with '_no' are read as 'number' and so should be a number.
> > OK, that doesn't always work for 'telephone_no' (they usually start
> > with a 0
>
> Lots of numbers are labels that aren't meant to be calculated on.  Item
> number, part number, model number, serial number, order number.
> Anything that needs to be distinguished and isn't worth naming.
>
> It's never a good idea to store such numbers as numerical types.
> There's always  a potential loss of information, be it the leading zero
> or embedded '-' or multiple '.' characters.  Unless the "number" is a
> quantity, for compuational purposes it's text.
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Chris Locke]

I tend to have a class at the table layer, so essentially have a 'settings'
class (matching the 'settings' table).  My form then (hard coded) grabs the
settings it needs.  I'd store the location as x,y (so 300,900 for example)
and size (so 1000,800 for example).  The class handles grabbing records,
editing as required, and saving back.  I don't tend to 'bind' as such.  No
pun, but I'm more of a basic VB programmer.  So in the form_load, create a
class which points to the settings table.  Either explicitly grab the 'main
form/size' setting (eg, "1000,800") break that down and apply as required,
or grab a bunch of 'main form/*' settings.  I can then pull out the size,
height, state, etc, records from that recordset (ie, keep it to one
database query).
Things get 'messy' when saving back.  Clean, but messy.  So to save the
position, I have to find the explicit 'main form/position' record again
(one query), edit it, and save it back (another query).  Due to the class,
there isn't much code to achieve that.
I use winForms, not wpf, but I assume (ignorantly) that the process is the
same.
Just to take this back on topic, SQLite is fabulous for this type of
usage.  A query takes milliseconds and a database can hold a whole manner
of settings, configurations, etc.  File size is small and efficient too.

Thanks,
Chris


On Thu, Dec 20, 2018 at 9:05 AM Roger Schlueter  wrote:

> On 12/19/2018 23:01, Chris Locke wrote:
> > What application are you using to build your application?  You mentioned
> > Visual Studio, so .NET?  If so, are you using the SQLite library from
> > system.data.sqlite.org?  Are you using c# or vb?
>
> Yes.  .NET via vb using wpf.
> I plan to use the library.  Right now I'm in the contemplation
> stage.  :-)  Once I
> settle on my approach to this issue, I'll start the coding.
>
> >
> > My settings table is a lot simpler.  id, setting and value.  3 columns.
> > Possibly 4, adding a 'code' column. The 'setting' column holds the full
> > setting you want to store, eg, 'main form height', or 'main form
> > windowstate'.  I can have user settings in this via 'chris/main form
> > height'.  I can then store that setting name as a constant in my
> > application, so its accessible via Intellisense.  Doing a series of quick
> > database lookups is relatively cheap.  You can also group the settings if
> > need be, so 'main form/height' and 'main form/windowstate' so you could
> > pull out a group of settings with one database query.
> > Happy to link you to a sample if needed.  A simple (although bloaty!)
> > database class can be used for the mundane database work - reading,
> > creating, editing and deleting records.  I tend to ensure my databases
> have
> > unique rowIds, and use these for the glue for relationships.
>
> Hey, you're ahead of me so let me ask for a few more details.  I
> contemplate bringing
> the data from the db into a class for each window that I can bind
> the values to.  That way
> the only other code necessary is to push altered any values back to
> the db when the window
> closes.  In your setup, then, how do you tell the "main form" that
> the binding is on the
> height property.  Or do you just assign the value in the loaded
> event and keep track of
> changes in your own code?
>
> Thanks a lot for your time; I'm stretching to put this together ...
> which is part of the fun.
>
> > Thanks,
> > Chris
> >
> > On Thu, Dec 20, 2018 at 3:37 AM Roger Schlueter  wrote:
> >
> >> On 12/19/2018 10:02, Jens Alfke wrote:
> >>>> On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> >>>>
> >>>> I am starting work on a prototype application so this might be an
> >> excellent opportunity to use SQLite for my application file format.
> Part
> >> of this would be the saving and restoring of GUI elements such as window
> >> positions and sizes, control states, themes, etc.
> >>> IMHO something like JSON is a good format for such config/preference
> >> data, instead of having a table with a column for every pref. During
> >> development you’ll often be adding new prefs, and it’s a pain to have to
> >> update a CREATE TABLE statement every time you add one. It’s even more
> of a
> >> pain to have to handle a schema change with ALTER TABLE in an app
> upgrade
> >> that adds a new pref. If you use JSON you just have to come up with a
> new
> >> string to use as the key for each pref. It’s also easy to have
> structured
> >> values like arrays or nested objects. (FWIW, his is essentially the way
> >> that Apple OS’s manage app prefs via 

Re: [sqlite] SQLite Application Question

[Chris Locke]

What application are you using to build your application?  You mentioned
Visual Studio, so .NET?  If so, are you using the SQLite library from
system.data.sqlite.org?  Are you using c# or vb?

My settings table is a lot simpler.  id, setting and value.  3 columns.
Possibly 4, adding a 'code' column. The 'setting' column holds the full
setting you want to store, eg, 'main form height', or 'main form
windowstate'.  I can have user settings in this via 'chris/main form
height'.  I can then store that setting name as a constant in my
application, so its accessible via Intellisense.  Doing a series of quick
database lookups is relatively cheap.  You can also group the settings if
need be, so 'main form/height' and 'main form/windowstate' so you could
pull out a group of settings with one database query.
Happy to link you to a sample if needed.  A simple (although bloaty!)
database class can be used for the mundane database work - reading,
creating, editing and deleting records.  I tend to ensure my databases have
unique rowIds, and use these for the glue for relationships.


Thanks,
Chris

On Thu, Dec 20, 2018 at 3:37 AM Roger Schlueter  wrote:

> On 12/19/2018 10:02, Jens Alfke wrote:
> >> On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> >>
> >> I am starting work on a prototype application so this might be an
> excellent opportunity to use SQLite for my application file format.  Part
> of this would be the saving and restoring of GUI elements such as window
> positions and sizes, control states, themes, etc.
> > IMHO something like JSON is a good format for such config/preference
> data, instead of having a table with a column for every pref. During
> development you’ll often be adding new prefs, and it’s a pain to have to
> update a CREATE TABLE statement every time you add one. It’s even more of a
> pain to have to handle a schema change with ALTER TABLE in an app upgrade
> that adds a new pref. If you use JSON you just have to come up with a new
> string to use as the key for each pref. It’s also easy to have structured
> values like arrays or nested objects. (FWIW, his is essentially the way
> that Apple OS’s manage app prefs via the NSUserDefaults class.)
>
> JSON or XML: Two sides of the same coin.  If I wanted to go the
> separate file approach, I'd just use the settings class of Visual
> Studio since all the required plumbing is already in place.
>
> More importantly, as I noted this is a prototype (read: test)
> application so it is a good opportunity for me to get my feet wet
> with SQLite since I'm a n00b with it.
>
> > Of course you can save the JSON in the database file. Just create a
> ‘prefs’ table with one blob column for the JSON.
> >
> > A related solution is to store each named pref as a row in the ‘prefs’
> table, identified by a ‘key’ column.
>
> In fact, this statement makes the concerns you raised in the first
> paragraph moot.  A simple table with four columns:
>
>  1. Window name
>  2. Control name
>  3. Control property
>  4. Property value
>
> covers all the possibilities, no ALTER table necessary.  If I want
> to enable per user values, I'd just add a User column.
>
> In short, the design part is easy IMO.  I'm still hoping to see some
> examples since, surely, I'm not the first person to go this route.
> >
> > —Jens
> > ___
> > 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] Sample Employee database ported to SQLite from MySQL

[Chris Locke]

> Then add foreign key constraints so the relations between the tables
> are explicit...

On the GitHub page for the database, it states that, "RowIds, Foreign keys,
secondary keys, defaults and cascade have not been ported."
Most of the tools to create a 'proper' database...
But otherwise, an interesting concept.

"The data was generated, and as such there are inconsistencies and subtle
problems. Rather than removing them, we decided to leave the contents
untouched, and use these issues as data cleaning exercises."


Chris


On Wed, Dec 19, 2018 at 12:16 PM Shawn Wagner 
wrote:

> I'd start by making the employees table a normal rowid one with an INTEGER
> PRIMARY KEY (*Not* INT) column, and change all those VARCHAR, CHAR and DATE
> column types to TEXT (or NUMERIC for the dates depending on the values they
> hold).
>
> Then add foreign key constraints so the relations between the tables are
> explicit...
>
> On Wed, Dec 19, 2018, 4:06 AM Arun - Siara Logics (cc)  wrote:
>
> > Hi Chris,
> >
> > I don't own the MySQL side of the db, but its easy for me to change
> > anything on Sqlite side.  To me the data looks decent for testing and
> > creating applications for demo or learning.
> >
> > I am giving below the script and I will incorporate any other suggestions
> > you may come up with:
> >
> > CREATE TABLE employees (
> > emp_no  INT NOT NULL,
> > birth_date  DATENOT NULL,
> > first_name  VARCHAR(14) NOT NULL,
> > last_name   VARCHAR(16) NOT NULL,
> > gender  CHAR(1) NOT NULL,
> > hire_date   DATENOT NULL,
> > PRIMARY KEY (emp_no)
> > ) without rowid;
> > CREATE TABLE departments (
> > dept_no CHAR(4) NOT NULL,
> > dept_name   VARCHAR(40) NOT NULL,
> > PRIMARY KEY (dept_no)
> > ) without rowid;
> > CREATE TABLE dept_manager (
> >dept_no  CHAR(4) NOT NULL,
> >emp_no   INT NOT NULL,
> >from_dateDATENOT NULL,
> >to_date  DATENOT NULL,
> >PRIMARY KEY  (emp_no, dept_no)
> > ) without rowid;
> > CREATE TABLE dept_emp (
> > emp_no  INT NOT NULL,
> > dept_no CHAR(4) NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATENOT NULL,
> > PRIMARY KEY (emp_no,dept_no)
> > ) without rowid;
> > CREATE TABLE titles (
> > emp_no  INT NOT NULL,
> > title   VARCHAR(50) NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATE,
> > PRIMARY KEY (emp_no,title, from_date)
> > ) without rowid;
> > CREATE TABLE salaries (
> > emp_no  INT NOT NULL,
> > salary  INT NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATENOT NULL,
> > PRIMARY KEY (emp_no, from_date)
> > ) without rowid;
> > CREATE INDEX emp_first_name on employees (first_name);
> > CREATE INDEX emp_last_name on employees (last_name);
> >
> > Regards
> > Arun
> >
> >   On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke <
> > sql...@chrisjlocke.co.uk> wrote 
> >  > The scheme (for me) is like nails on a chalkboard.  'dept_no' but
> > defined
> >  > as a 'CHAR', then 'emp_no' as an INT.
> >  > Fields with '_no' are read as 'number' and so should be a number.  OK,
> > that
> >  > doesn't always work for 'telephone_no' (they usually start with a 0
> ...
> >  > well, they do in the UK where I am...)
> >  > But I digress..
> >  >
> >  >
> >  > Chris
> >  >
> >  >
> >  > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc)
>  > >
> >  > wrote:
> >  >
> >  > > This project (https://github.com/siara-cc/employee_db) hosts the
> > Sqlite3
> >  > > db file ported from mysql test_db found at
> >  > > https://github.com/datacharmer/test_db. It can be used to test your
> >  > > applications and database servers. To use this project, download
> >  > > employees.db.bz2, unzip and open using sqlite3 command line tool.
> >  > >
> >  > > The original data was created by Fusheng Wang and Carlo Zaniolo at
> > Siemens
> >  > > Corporate Research. The data is in XML format.
> >  > > http://timecenter.cs.aau.dk/software.htm
> >  > >
> >  > > Giuseppe Max

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

[Chris Locke]

The scheme (for me) is like nails on a chalkboard.  'dept_no' but defined
as a 'CHAR', then 'emp_no' as an INT.
Fields with '_no' are read as 'number' and so should be a number.  OK, that
doesn't always work for 'telephone_no' (they usually start with a 0 ...
well, they do in the UK where I am...)
But I digress..


Chris


On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) 
wrote:

> This project (https://github.com/siara-cc/employee_db) hosts the Sqlite3
> db file ported from mysql test_db found at
> https://github.com/datacharmer/test_db. It can be used to test your
> applications and database servers. To use this project, download
> employees.db.bz2, unzip and open using sqlite3 command line tool.
>
> The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens
> Corporate Research. The data is in XML format.
> http://timecenter.cs.aau.dk/software.htm
>
> Giuseppe Maxia made the relational schema and Patrick Crews exported the
> data in relational format.
>
> The database contains about 300,000 employee records with 2.8 million
> salary entries. The export data is 167 MB, which is not huge, but heavy
> enough to be non-trivial for testing.
>
> A picture of the schema can be found at:
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true
>
> Regards
> Arun - Siara Logics (cc)
>
>
> ___
> 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] HELP!

[Chris Locke]

> it is almost guaranteed to corrupt the database file if more than one
connection tries to access it at the same time.

I understand the risks and reasons, but have had numerous databases on our
Windows network accessed by 20+ users throughout the day without issue.


Thanks,
Chris


On Sun, Nov 11, 2018 at 7:12 PM Jay Kreibich  wrote:

>
> > On Nov 11, 2018, at 1:24 AM, Clemens Ladisch  wrote:
> >
> > It's not; SQLite is file based.  The only way to share this would be to
> > make a file share in the company-wide network, i.e., to make the file
> > \\COMPANYSERVER\SomeShare\MyLittleDB.sqlite directly accessible from
> > everywhere.  (This is likely to be inefficient.)
>
> Not just inefficient, it is almost guaranteed to corrupt the database file
> if more than one connection tries to access it at the same time.  There
> isn’t a remote file system out there (in the Windows or Unix world) that
> correctly implements the locking structures SQLite requires.
>
>   -j
>
> ___
> 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] about upsert feature.

[Chris Brody]

I wonder if this should be considered a bug, or if should be better
documented?

On Thu, Nov 8, 2018 at 9:57 AM 畑宏和  wrote:

> Thanks for your reply!
> My script's select-stmt do not include where.
> When I add 'where true', syntax error does not occur.
>
> Thanks!
>
> 2018年11月7日(水) 19:44、Richard Hipp さん（d...@sqlite.org）のメッセージ:
>
> > On 11/6/18, 畑宏和  wrote:
> > > Hi, I have a question.
> > >
> > > We can use upsert after v3.24.0.
> > >
> > > Document  says that
> > > `INSERT INTO table-name (column-name) select-stmt upsert-clause`
> > > But when I try this syntax, it returns syntax error.
> > > I think we can't use upsert-clause with select-stmt.
> > > Is this thought right?
> >
> > Does your select-stmt include a WHERE clause?  It should, even if it
> > is a no-op like "WHERE true".  Try adding the WHERE clause and let us
> > know if that fixes your problem.
> > --
> > 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] Displaying row count

[Chris Locke]

>  that will display a row number when outputting results?

Is this for your schema, or a 'general-could-be-anything' schema?  If your
own, any reason why you don't use the rowid or _rowid_ columns?  They
provide a unique reference for each row in a table.


Thanks,
Chris


On Wed, Oct 31, 2018 at 2:54 PM David Fletcher  wrote:

> Hi all,
>
> Is there a mode in the sqlite shell, or some fancy extension, that will
> display a row
> number when outputting results?  You know, something like this:
>
>  sqlite> .row on
>  sqlite> select * from SomeTable where ... ;
>  1. a|17|93|...
>  2. b|212|104|...
>
> I tend to use the sqlite shell for debugging new queries and seeing a row
> number would save me from always doing a 'select count(*) from (...
> previous select ...)'
> command.
>
> Thanks,
>
> David
> ___
> 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] Regarding CoC

[Chris Locke]

> On the other hand, I am open to suggestions on how to express
> those values in a way that modern twitter-ites can better understand

Probably via selfie, with a duckface, together with your evening meal in
the background.


On Mon, Oct 22, 2018 at 4:30 PM Richard Hipp  wrote:

> On 10/22/18, Chris Brody  wrote:
> >> Looks like that happened this morning.
> >> https://news.ycombinator.com/item?id=18273530
> >
> > I saw it coming, tried to warn you guys in private.
>
> There is indeed a reactionary hate mob forming on twitter.  But most
> of the thoughtful commentators have been supportive, even if they
> disagree with the particulars of our CoC, They total get that we are
> not being exclusive, but rather setting a standard of behavior for
> participation in the SQLite community.
>
> I have tried to make that point clear in the preface to the CoC, that
> we have no intention of enforcing any particular religious system on
> anybody, and that everyone is welcomed to participate in the community
> regardless of ones religious proclivities.  The only requirement is
> that while participating in the SQLite community, your behavior not be
> in direct conflict with time-tested and centuries-old Christian
> ethics.  Nobody has to adhere to a particular creed.  Merely
> demonstrate professional behavior and all is well.
>
> Many detractors appear to have not read the preface, or if they read
> it, they did not understand it.  This might be because I have not
> explained it well.  The preface has been revised, months ago, to
> address prior criticism from the twitter crowd.  I think the current
> preface is definitely an improvement over what was up at first.  But,
> there might be ways of improving it further.  Thoughtful suggestions
> are welcomed.
>
> So the question then arises:  If strict adherence to the Rule of St.
> Benedict is not required, why even have a CoC?
>
> Several reasons:  First, "professional behavior" is ill-defined.  What
> is professional to some might be unprofessional to others.  The Rule
> attempts to clarify what "professional behavior" means.  When I was
> first trying to figure out what CoC to use (under pressure from
> clients) I also considered secular sources, such as Benjamin
> Franklin's 13 virtues (http://www.thirteenvirtues.com/) but ended up
> going with the Instruments of Good Works from St. Benedict's Rule as
> it provide more examples.
>
> Secondly, I view a CoC not so much as a legal code as a statement of
> the values of the core developers.  All current committers to SQLite
> approved the CoC before I published it.  A single dissent would have
> been sufficient for me to change course.  Taking down the current CoC
> would not change our values, it would merely obscure them.  Isn't it
> better to be open and honest about who we are?
>
> Thirdly, having a written CoC is increasingly a business requirement.
> (I published the currrent CoC after two separate business requested
> copies of our company CoC.  They did not say this was a precondition
> for doing business with them, but there was that implication.) There
> has been an implicit code of conduct for SQLite from the beginning,
> and almost everybody has gotten along with it just fine.  Once or
> twice I have had to privately reprove offenders, but those are rare
> exceptions.  Publishing the current CoC back in February is merely
> making explicit what has been implicit from the beginning.  Nothing
> has really changed.  I did not draw attention to the CoC back in
> February because all I really needed then was a hyperlink to send to
> those who were specifically curious.
>
> So then, why not use a more modern CoC?  I looked at that too, but
> found the so-called "modern" CoCs to be vapid.  They are trendy
> feel-good statements that do not really get to the heart of the matter
> in the way the the ancient Rule does.  By way of analogy, I view
> modern CoCs as being like pop music - selling millions of copies today
> and completely forgotten next year.  I prefer something more enduring,
> like Mozart.
>
> One final reason for publishing the current CoC is as a preemptive
> move, to prevent some future customer from imposing on us one of those
> modern CoCs that I so dislike.
>
> In summary: The values expressed by the current CoC have been
> unchanged for decades and will not be changing as we move forward.  If
> some people are uncomfortable with those values, then I am very sorry
> for them, but that does not change the fact.  On the other hand, I am
> open to suggestions on how to express those values in a way that
> modern twitter-ites can better understand, so do not hesitate to speak
> up if you have a plan.
> --
&g

Re: [sqlite] Regarding CoC

[Chris Brody]

I would vote for a major simplification, down to something like "love
thy neighbor", "do unto others as ...", or "don't do unto others as
..."

For reference:
* https://www.simpletoremember.com/jewish/blog/loving-thy-neighbor-judaism/
* https://www.biblegateway.com/passage/?search=Galatians+5%3A14=KJV
On Mon, Oct 22, 2018 at 12:26 PM Simon Slavin  wrote:
>
> On 22 Oct 2018, at 1:19pm, Richard Hipp  wrote:
>
> > Looks like that happened this morning.
> > https://news.ycombinator.com/item?id=18273530
>
> It also hit Reddit, in /r/programming.  Currently 239 comments:
>
> 
>
> Simon.
>
>
> ___
> 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] Regarding CoC

[Chris Brody]

> Looks like that happened this morning.
> https://news.ycombinator.com/item?id=18273530

I saw it coming, tried to warn you guys in private.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Chris Locke]

> For the record, "delete the journal file" is terrible advice

Agreed.  In normal production environment, I wouldn't suggest that.  The
user was testing a database, and in my own developemtn cycle, its common
when developing for a database to be in all manners of chaos states.  It
was purely a 'gotcha' that has caught me out before - a journal file
lingers and locks the system.


On Mon, Oct 15, 2018 at 4:03 AM Rowan Worth  wrote:

> On Sat, 13 Oct 2018 at 00:21, Chris Locke 
> wrote:
>
> > > Database is locked
> >
> > Close your application.  Is there a xxx-journal file in the same
> directory
> > as the database? (where xxx is the name of the database)
> > Try deleting this file.
> >
>
> For the record, "delete the journal file" is terrible advice and a great
> way to corrupt a database. In the case where a program crashes
> mid-transaction, the journal contains information which is crucial for
> recovering to a correct database state. And in non-crash scenarios, the
> journal should be cleaned up¹. So when you can see a journal file it's
> likely that either:
>
> 1. some program is currently using the DB, or
> 2. there was a crash mid-transaction
>
> Either way, deleting the journal is a wrong move.
>
> ¹ unless the DB is configured with PRAGMA journal_mode set to TRUNCATE or
> PERSIST, in which case you've asked for the rollback journal to linger
> around.
>
> -Rowan
> ___
> 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] error message: [FireDAC][Phys][SQLite] Database is locked

[Chris Locke]

> Database is locked

Close your application.  Is there a xxx-journal file in the same directory
as the database? (where xxx is the name of the database)
Try deleting this file.


Thanks,
Chris



On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz  wrote:
>
>> Could the problem arise due to filesystem corruption? Have you tried an
>> fsck?
>>
>>
>> - Original Message -
>> From: R Smith 
>> To: sqlite-users@mailinglists.sqlite.org <
>> sqlite-users@mailinglists.sqlite.org>
>> Sent: Friday, October 12, 2018, 17:35:28
>> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>> locked
>>
>> On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
>> > I have tried that too! - I must admit that right now I am turning to a
>> KISS solution: String-based database with functions and procedures in a
>> class that handles the different jobs you do on a table. After all these
>> old-fashioned DB's are not so picky :-)
>>
>> It's sad to see someone get dismayed with what is usually a flawless
>> working system.
>> It's not the database that's tricky, it's the reason the file is locked.
>> This would be a problem with a string based file too.
>>
>> Reading the above, I'm no longer convinced it's your anti-virus' fault
>> either.
>>
>> Using Delphi FireDAC quite a bit, I can promise you that its SQLite
>> connectivity is working just fine, and while an Antivirus can lock a
>> file for the time it takes to check it, unless your database is
>> Gigabytes in size, that lock should be released within milliseconds,
>> unless found to be unsafe - but then the log should speak of it (which I
>> assume it doesn't).
>>
>> This leaves other possible culprits as Windows Virtualization and
>> possible other programs/processes on your machine locking the DB file.
>> Do you have it open in any DB manager or other CLI or such?
>> What's the actual path of the folder containing the file on your drive?
>>
>>
>> Cheers,
>> Ryan
>>
>> ___
>> 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] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Chris Green]

Darren Duncan  wrote:
> On 2018-10-10 10:51 AM, Chris Green wrote:
> > Warren Young  wrote:
> >> Fossil forum email alerts include the full content of the message.
> 
> That's great!  Especially if the alert email subject includes the forum 
> thread 
> subject.
> 
> That said, I consider it critical that these alert emails can also send my 
> own 
> posts in the forum and not just others.  If they don't send for EVERY post, 
> the 
> emails aren't suitable for reading / backing up a thread in one place.
> 
> > And can you then simply 'reply' from your E-Mail client?  If not then
> > it doesn't really help much.
> 
> Actually it helps a lot.  I think in practice most people using this forum 
> would 
> be reading a lot more than they post.  So you can do your majority action of 
> reading in your email client with the forum alerts.  In the rare situation 
> where 
> you want to reply, then you just switch over to the web forum.

Yes, and there lies the rub, it's a window-swapping, mouse clicking
hassle.  If it was a mailing list I'd simply hit L[ist reply] and that
would be it.

-- 
Chris Green
·

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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Chris Green]

Warren Young  wrote:
> On Oct 10, 2018, at 11:23 AM, Tim Streater  wrote:
> > 
> > On 10 Oct 2018, at 18:10, Warren Young  wrote:
> > 
> >> On Oct 10, 2018, at 10:39 AM, Eric  wrote:
> >>> 
> >>> * mailing lists come to me, I don't have to go and get them
> >> 
> >> So do Fossil email alerts.
> > 
> > So there's an unecessary email I've just received telling me to go to the 
> > forum.
> 
> Fossil forum email alerts include the full content of the message.

And can you then simply 'reply' from your E-Mail client?  If not then
it doesn't really help much.

-- 
Chris Green
·

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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Chris Green]

Gary R. Schmidt  wrote:
> On 10/10/2018 22:20, Petite Abeille wrote:
> > 
> [SNIP]
> > ( Also, fwiw: please keep the mailing list, it's perfectly functional as 
> > is. )
> > 
> Seconded.
> 
> It isn't broken, so please don't try and fix it.
> 
Exactly!  :-)

-- 
Chris Green
·

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

Re: [sqlite] Persistent snapshots and rollbacks

[Chris Brody]

> Yes, but there are some things I don't like about it -- see my earlier
> reply in this thread (to Simon's first message).

Gotta say I could not follow what you said in the earlier reply. It
would be nice if you could explain in some finer detail.

That said, I would personally favor using "standard" SQLite3 if possible.

> Alternatively, I was thinking about this idea:  If it is not possible to
> persist savepoints, then I could simply discard the uncommitted changes
> when the connection is closed, and redo them on the next startup of the
> application.  In my particular situation, this is possible (the
> information needed to redo the changes is available) and may be
> acceptable performance-wise.

Makes sense to me. I just had the following idea that I hope can
satisfy your use case a little more smoothly:

Add some kind of a record state column that you can use to track which
"saveset" you want for each record in the database. (You would
probably use a special "saveset" number to mean that you want to
persist the record unless you decide to explicitly remove it someday.)
Then it should be really easy for you to include or exclude certain
records when selecting based on your "saveset" number.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Chris Brody]

> I did now a quick experiment with the sqlite3 command-line, and it seems
> that savepoints indeed work exactly what I need *except* for being
> non-persistent.  Is there some way or trick I could use to make them (or
> the "current session") persist?

Couldn't  do the trick for you?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Chris Brody]

Savepoints ()?
On Fri, Oct 5, 2018 at 11:40 AM Daniel Kraft  wrote:
>
> Hi!
>
> I need the ability to make multiple changes / commits to my SQLite
> database but keep snapshots of previous states and potentially roll back
> to those states later on.  All of that needs to be persistent, i.e.
> survive closing the database and restarting the process.  After some
> time, I can get rid of old snapshots (my process determines by itself
> when and which snapshots can get discarded, it is not based on some
> fixed TTL or something like that).
>
> Is it possible to do all that with SQLite?
>
> From reading the docs, it seems to me that (persistent) WAL mode
> basically does *exactly that* internally:  Changes are recorded in the
> logs so that previous versions are retained.  Rollbacks would be
> possible by "simply" discarding the WAL entries after the desired
> snapshot.  And discarding of very old snapshots corresponds to
> checkpointing.
>
> However, I'm not sure if all of that functionality is (officially)
> exposed to me as a user.  There are in particular two points where I
> think that my requirements differ from the functionality that WAL mode
> exposes:
>
> 1) Handles to snapshots can be obtained and stored, but they are
> read-only.  It seems to be not possible to tell SQLite to restore the
> WAL to a previous version and then continue modifying from that version.
>  (Which basically means truncating the WAL file at a certain point.)
>
> 2) From what I have seen, checkpointing can only be triggered for the
> full WAL (or whatever is possible with existing readers) and not
> selectively up to a desired point.  Of course I could work around that
> by creating a reader at the point I want to keep.  But then I wonder if
> it is a problem if the WAL can never be *fully* checkpointed (as in my
> requirement).  Would that mean that it keeps on growing forever, or is
> checkpointing able to remove parts from the beginning of the WAL?
>
> Is my understanding here correct?  And is there some way in which I
> could achieve my requirements using WAL mode (or somehow else)?
>
> Thank you very much!
>
> Yours,
> Daniel
>
> --
> https://www.domob.eu/
> OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
> Namecoin: id/domob -> https://nameid.org/?name=domob
> --
> 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
> To go: Arc-Cav-Hea-Kni-Mon-Tou
>
> ___
> 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] A SQL statement reformatter

[Chris Brody]

I found https://github.com/mjibson/sqlfmt through their about page. No
license though, just raised
https://github.com/mjibson/sqlfmt/issues/33.

GitHub fork-me ribbon would also be nice I think.

Someone should have make this tool 20-30 years ago!
On Fri, Sep 28, 2018 at 10:44 AM Peter da Silva  wrote:
>
> Pity they aren't making the code available. Not sure I want to send company
> SQL to a random site.
>
> On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen  wrote:
>
> > Slick. That line width slider feature is something we don't get using Poor
> > Man's Sql Formatter.
> >
> > Thanks!
> >
> > On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:
> >
> > > For those times when you have to understand a poorly-formatted SQL
> > > statement:
> > >
> > > 
> > >
> > > I seem to prefer 'full' mode.
> > >
> > > Simon.
> > > ___
> > > 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] downloading older versions

[Chris Brody]

From https://www.sqlite.org/chronology.html you should be able to
click on the date, then click on the check-in hash number, then click
the link after "Downloads:" to download a ZIP, TAR, or SQLAR of the
older version. Keep in mind that this gives you a snapshot of the
source tree, you would have to build your own amalgamation.

I wish this would be a little more intuitive.

It would probably be ideal if you could start using the fossil tool.
On Wed, Sep 26, 2018 at 2:29 PM David Raymond  wrote:
>
> I think you've got the right idea, but where are you seeing that there was 
> ever a 3.8.6.1?
> I don't see it anywhere on https://www.sqlite.org/changes.html, and 3.8.5 was 
> in 2014
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Mark Wagner
> Sent: Wednesday, September 26, 2018 2:24 PM
> To: SQLite mailing list
> Subject: [sqlite] downloading older versions
>
> I'm trying to download older versions of sqlite to check the behavior
> of various bugs but I'm having trouble finding them.
>
> For example to get
>
> SQLite version 3.8.6.1 2017-07-21 03:23:38
>
> I have tried:
>
> https://www.sqlite.org/2017/sqlite-tools-linux-x86-3080601.zip
>
> Should that have worked?  Note that I've tried various "year"
> components on the path as well.
>
> -- Mark
> ___
> 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] Error when reading from pre-populated SQLite database in Ionic project

[Chris Brody]

Check the results of cordova plugin ls

A common pitfall is that you have to use cordova-sqlite-ext plugin to get
pre-populated database functionality. Commonly used cordova-sqlite-storage
plugin does not support this feature. Second pitfall is if you have
multiple Cordova sqlite plugins installed.

On Wed, Sep 5, 2018 at 12:25 PM Chris Locke 
wrote:

> When SQLite creates an empty .db file, which directory is it in?  With all
> your tweaking, etc, is the new database always in the same directory?
>
> Thanks,
> Chris
>
> On Wed, Sep 5, 2018 at 3:23 PM Robert Helmick  >
> wrote:
>
> > I'm receiving an error when I try to read from a pre-populated SQLite
> > database: `sqlite3_prepare_v2 failure: no such table 'plant'`
> >
> > From what I understand SQLite looks for the mydb.db file in the /www
> folder
> > by default, then creates an empty database when it doesn't find the
> > pre-populated mydb.db file. This is why it can't find the 'plant' table,
> > because the newly created blank database obviously doesn't contain a
> > 'plant' table. However I can confirm that the database *is* in the /www
> > folder, and that it contains the 'plant' table when I run `sqlite3
> mydb.db`
> > then `.tables` in the terminal.
> >
> > I can't figure out why it's not reading from the pre-populated mydb.db
> > file.
> >
> > Folder structure (from root):
> >
> > /src
> > -/app
> > --/app.component.ts
> > /www
> > -/mydb.db
> >
> > app.component.ts:
> >
> >   constructor(public platform: Platform, private sqlite: SQLite ) {
> > platform.ready().then(() => {
> >   this.getData();
> > });
> >   }
> >
> >   getData() {
> > this.sqlite.create({
> >   name: 'mydb.db',
> >   location: 'default'
> > }).then((db: SQLiteObject) => {
> >   db.executeSql('SELECT * FROM plant ORDER BY id ASC', [])
> >   .then(res => {
> > // Do Stuff
> >   }).catch(e => console.log("FAIL executeSql:", e));
> > })
> >   }
> >
> > I've attempted many fixes that I've found on StackOverflow, like wiping
> the
> > app from my device, starting a new ionic project then copying app and
> > config files over, and setting a direct path in the database location,
> but
> > it still keeps trying to read from the empty database that it creates..
> >
> > Thanks in advance for any help.
> >
> > Robert
> > ___
> > 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] Error when reading from pre-populated SQLite database in Ionic project

[Chris Locke]

When SQLite creates an empty .db file, which directory is it in?  With all
your tweaking, etc, is the new database always in the same directory?

Thanks,
Chris

On Wed, Sep 5, 2018 at 3:23 PM Robert Helmick 
wrote:

> I'm receiving an error when I try to read from a pre-populated SQLite
> database: `sqlite3_prepare_v2 failure: no such table 'plant'`
>
> From what I understand SQLite looks for the mydb.db file in the /www folder
> by default, then creates an empty database when it doesn't find the
> pre-populated mydb.db file. This is why it can't find the 'plant' table,
> because the newly created blank database obviously doesn't contain a
> 'plant' table. However I can confirm that the database *is* in the /www
> folder, and that it contains the 'plant' table when I run `sqlite3 mydb.db`
> then `.tables` in the terminal.
>
> I can't figure out why it's not reading from the pre-populated mydb.db
> file.
>
> Folder structure (from root):
>
> /src
> -/app
> --/app.component.ts
> /www
> -/mydb.db
>
> app.component.ts:
>
>   constructor(public platform: Platform, private sqlite: SQLite ) {
> platform.ready().then(() => {
>   this.getData();
> });
>   }
>
>   getData() {
> this.sqlite.create({
>   name: 'mydb.db',
>   location: 'default'
> }).then((db: SQLiteObject) => {
>   db.executeSql('SELECT * FROM plant ORDER BY id ASC', [])
>   .then(res => {
> // Do Stuff
>   }).catch(e => console.log("FAIL executeSql:", e));
> })
>   }
>
> I've attempted many fixes that I've found on StackOverflow, like wiping the
> app from my device, starting a new ionic project then copying app and
> config files over, and setting a direct path in the database location, but
> it still keeps trying to read from the empty database that it creates..
>
> Thanks in advance for any help.
>
> Robert
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on IBM z/OS Unix

[Chris Brody]

It is not clear to me why your program closes the sqlite3 database at
the end of each C function invocation. AFAIK sqlite3 should not close
any database connection unless your program invokes sqlite3_close. If
something in the "Language Environment" closes and releases internal
resources at the end of each C function call then this would be an
issue with your z/OS environment.

Assuming that the "Language Environment" does not automatically
release resources, I can think of a the following alternative
approaches:

1: C code opens the database and stores the handle in a static variable.
2: C code opens the database and returns the pointer to the assembly
code; assembly code would then include the returned pointer value in
subsequent calls to the C code.
3a: C code opens the database, stores it in a structure on the heap,
and returns the pointer to the structure back to the assembly code
which is then used in subsequent calls to the C code
3b: C code opens the database, stores it in a structure on the heap,
maintains some kind of hash table or other key-value map, and returns
a numerical value to the assembly program which is then used in
subsequent calls to the C code

I suspect it should be pretty straightforward (easy) to search for
documentation on each of the approaches above. I think this could be
similar to interfacing between C and higher-level languages such as
Java, Python, C++, etc.

I would compare alternatives 2 and 3a to how FILE pointers work
between stdlib and C programs, also to how sqlite3 database pointers
work between sqlite3 library and application code in C..

I hope this is helpful to you and other readers on the list. Please do
not hesitate to ask if anything does not sound right or is not clear.

Chris

https://www.linkedin.com/in/chrisbrody/

On Wed, Aug 29, 2018 at 6:26 PM David Jackson  wrote:
>
> Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> looking at a c program to make SQL calls to this.
> Starting with an Assembler routine that runs within z/OS (not Unix), which
> is not LE (Language Environment)enabled, we then call a c routine (numerous
> times) that is LE enabled. That is all working fine and making good SQL
> calls to sqlite. the c program then returns back to the upper assembler
> calling program. The problem is that the c routines is opening, issuing the
> SQL and closing on each invocation.
>
> Now this may be a dumb question, so apologies up front.
> Is there any way that the c program can open the sqlite db initially on the
> first call and keep it open after it returns back to the calling program
> until a final call at which point it will issue the sqlite3_close.
>
> Again - sorry if this was a dumb question.
>
> Thanks
> ___
> 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] PRAGMA case_sensitive_like

[Chris Locke]

Thanks J - not entirely sure how that's helpful - I know how to call the
pragma, but I was enquiring as to whether there was a way of reading a
write-only pragma (which sounds nonsense now that I've written that down!!)

Thanks,
Chris


On Thu, Aug 16, 2018 at 3:16 PM J Decker  wrote:

> https://www.sqlite.org/pragma.html#pragma_pragma_list
>
> A pragma can take either zero or one argument. The argument is may be
> either in parentheses or it may be separated from the pragma name by an
> equal sign. The two syntaxes yield identical results. In many pragmas, the
> argument is a boolean. The boolean can be one of:
>
> *1 yes true on0 no false off*
>
> Keyword arguments can optionally appear in quotes. (Example: 'yes'
> [FALSE].)
> Some pragmas takes a string literal as their argument. When pragma takes a
> keyword argument, it will usually also take a numeric equivalent as well.
> For example, "0" and "no" mean the same thing, as does "1" and "yes". When
> querying the value of a setting, many pragmas return the number rather than
> the keyword.
> looks like , from the docs, `  pragma *case_sensitive_like` should rteturn
> the value; a pragma without a value.
> there's also a pragma pragma_list which (if compiled in) will return all
> available pragmas
>
> On Thu, Aug 16, 2018 at 6:16 AM Chris Locke 
> wrote:
>
> > While the pragma *case_sensitive_like *can be set, there doesn't seem to
> be
> > a way to read it.
> > Is there a reason for it being write only?
> > A hacky workaround is to use the SQL, "select 'x' like 'X'  ", but is
> there
> > a better way?
> > ___
> > 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 case_sensitive_like

[Chris Locke]

While the pragma *case_sensitive_like *can be set, there doesn't seem to be
a way to read it.
Is there a reason for it being write only?
A hacky workaround is to use the SQL, "select 'x' like 'X'  ", but is there
a better way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG REPORT

[Chris Locke]

> I am using a query to check a date field between a range of dates

Can you provide example values of the date in your database?
Are you storing the EXACT date (eg, '2018-02-01 12:21'), or just the date?

> When running this with the  ODBC driver it fails to return all the
appropriate record in the range. I tried the exact same query in a
> DB Browser for Sqlite and it recovers 127 records only.

How many records were returned with the ODBC driver?



On Wed, Aug 15, 2018 at 10:16 AM Mr Max  wrote:

> To whom it may concern,
>
>
>
>
>
> Whilst using an ODBC driver for SQLite acquired from:
>
>
>
> http://www.ch-werner.de/sqliteodbc/
>
>
>
> I came across a potential bug in SQLite.
>
>
>
> I have an application running VB.NET on a Windows 7 32-bit machine and
> have
> installed the sqliteodbc.exe from the website above. I am using a query to
> check a date field between a range of dates, the exact query being:
>
>
>
> SELECT ind.CUSTOMERU, ind.XTRANU, ind.DDATE, SUM(ind.DAMOUNT) as REVENUE,
> MIN(inc.SURNAME) as CNAME
>
> FROM INV_DETAIL ind inner JOIN CUSTOMER inc ON ind.CUSTOMERU=inc.UNIQ
> WHERE
> ind.DDATE BETWEEN '2018-02-01' AND '2018-02-28' AND ind.DTYPE='3'
>
> AND ind.DAMOUNT<0 AND ind.SUBCONTRU<>'666' AND ind.SUBCONTRU<>'555' GROUP
> BY
> ind.CUSTOMERU, ind.XTRANU ORDER BY ind.CUSTOMERU, ind.XTRANU;
>
>
>
>
>
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.
>
> I have run the same query using ODBC and Access (office 2003) and it
> recovers 138 records. Doing a manual filter of the records from the
> INV_DETAIL table I can extract 138 records!!
>
> The records  apparently omitted by SQLite are one with DDATE equal to the
> start date of 2018-02-01. If I make the start date one day earlier the
> SQLite query returns 138 records!!
>
>
>
> I have attached a spreadsheet with the data from the report I am generating
> and with the INV_DETAIL data for the whole month of Feb 2018.
>
>
>
>
>
> Regards
>
> Bob Maxwell
>
>
>
> ___
> 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] Using Chinook

[Chris Locke]

> I am just starting to learn SQLite

Just a nod to check out (if you haven't already) the tutorials on w3schools.
https://www.w3schools.com/sql/default.asp

Not only do they clearly explain various SQL commands, but you can try them
out 'live' on their website using sample databases - nothing to install.


Thanks,
Chris


On Mon, Aug 13, 2018 at 1:15 PM Roger Schlueter  wrote:

> I am just starting to learn SQLite so I am afraid my questions here will
> be quite simple for a (very long?) while compared to those I have been
> reading.  I'm starting by trying to follow the tutorial.
>
> I have SQLite, the GUI, and chinook.db installed on Windows 10 but not
> in the default directories.  In the cmd window I have this:
>
> C:\Program Files\SQLite>sqlite3 e:\"VB Resources"\SQLite\chinook.db
> Error: unable to open database "e:"VB": unable to open database file
>
> It appears that SQLite does not like directory names with embedded
> blanks.  Is that correct?  If so, is there a workaround?
>
> Roger
>
> ___
> 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] Using Chinook

[Chris Locke]

When using directories with spaces, its better (or necessary) to include
the whole directory in double quotes.  Therefore, try entering this:
sqlite3 "e:\VB Resources\SQLite\chinook.db"

No problems with being a novice - everyone was a novice once.

Thanks,
Chris

On Mon, Aug 13, 2018 at 1:15 PM Roger Schlueter  wrote:

> I am just starting to learn SQLite so I am afraid my questions here will
> be quite simple for a (very long?) while compared to those I have been
> reading.  I'm starting by trying to follow the tutorial.
>
> I have SQLite, the GUI, and chinook.db installed on Windows 10 but not
> in the default directories.  In the cmd window I have this:
>
> C:\Program Files\SQLite>sqlite3 e:\"VB Resources"\SQLite\chinook.db
> Error: unable to open database "e:"VB": unable to open database file
>
> It appears that SQLite does not like directory names with embedded
> blanks.  Is that correct?  If so, is there a workaround?
>
> Roger
>
> ___
> 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] Using Chinook

[Chris Locke]

> to include the whole directory

Sorry, I meant the whole filename.

On Mon, Aug 13, 2018 at 1:28 PM Chris Locke 
wrote:

> When using directories with spaces, its better (or necessary) to include
> the whole directory in double quotes.  Therefore, try entering this:
> sqlite3 "e:\VB Resources\SQLite\chinook.db"
>
> No problems with being a novice - everyone was a novice once.
>
> Thanks,
> Chris
>
> On Mon, Aug 13, 2018 at 1:15 PM Roger Schlueter  wrote:
>
>> I am just starting to learn SQLite so I am afraid my questions here will
>> be quite simple for a (very long?) while compared to those I have been
>> reading.  I'm starting by trying to follow the tutorial.
>>
>> I have SQLite, the GUI, and chinook.db installed on Windows 10 but not
>> in the default directories.  In the cmd window I have this:
>>
>> C:\Program Files\SQLite>sqlite3 e:\"VB Resources"\SQLite\chinook.db
>> Error: unable to open database "e:"VB": unable to open database file
>>
>> It appears that SQLite does not like directory names with embedded
>> blanks.  Is that correct?  If so, is there a workaround?
>>
>> Roger
>>
>> ___
>> 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] Very, very slow commits - Possibly solved

[Chris Locke]

I've been following this thread with interest, but this just doesn't make
sense...

>  Logically speaking SQLite shouldn't notice the difference in row order,
but things do slow down,
> even with analyse.

Are you accessing each row via its ID?  Even so, that should still be
indexed.
I thought you were simply adding records into the database - I'm failing to
grasp how this is slowing down in the new database.


Thanks,
Chris



On Tue, Jul 31, 2018 at 3:30 PM Rob Willett 
wrote:

> Dear all,
>
> We think we have now found the issue with the slow commits.
>
> We believe this is due to an inherent (and old) defect in our database
> design. We think our original design has an implicit ordering of rows in
> a table, when the table is only increasing this flaw in the design isn't
> apparent.
>
> However when we started deduping the table AND we copied rows from one
> table to another to move things around, we changed the underlying order
> of rows. Sqlite handles the design change BUT the flaw in our design
> becomes apparent as we keep moving the data around and data gets mixed
> up. The database slows down when we create a second table with an
> identical structure to the first table, copy the data into the new
> table, drop the old and then when we rename the old table to the new
> table, things appear to slow down. Logically speaking SQLite shouldn't
> notice the difference in row order, but things do slow down, even with
> analyse.
>
> We think that a better index definition could solve the problem for us,
> a better database design would, but thats a tricky problem.
>
> We're now going back to our 60GB database and start from scratch to see
> if we can create the issue (now we think we know what it is).
>
> Thanks to everybody who contributed ideas, we appreciate the help.
>
> Rob
>
> On 31 Jul 2018, at 15:19, Rob Willett wrote:
>
> > Simon,
> >
> > As an exercise we have just added in COLLATE NOCASE to our integer
> > columns.
> >
> > Whoops! We thought this would make no difference but its added extra
> > 70% to our processing speeds.
> >
> > We've now got to the stage where we can make changes quickly, so we'll
> > back that change out and go back to the integer defn without COLLATE
> > NOCASE.
> >
> > Rob
> >
> > On 31 Jul 2018, at 14:59, Rob Willett wrote:
> >
> >> Simon,
> >>
> >> Apologies for taking so long to get back, we've been building a test
> >> system and its taken a long time.
> >>
> >> We're just getting round to trying your ideas out to see what
> >> difference they make,
> >>
> >> We've created a new table based on your ideas, moved the collate into
> >> the table, analysed the database. We did **not** add COLLATE NOCASE
> >> to the columns which are defined as integers. Would that make a
> >> difference?
> >>
> >> We've found it now takes around 10% longer to do the queries than
> >> before.
> >>
> >> Rob
> >>
> >>
> >>> Please try moving your COLLATE clauses into the table definition.
> >>> e.g. instead of
> >>>
> >>> CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions ("version"
> >>> COLLATE NOCASE ASC, "Disruption_id" COLLATE NOCASE ASC, "location"
> >>> COLLATE NOCASE ASC);
> >>>
> >>> Your table definition should have
> >>>
> >>>  "version" integer NOT NULL COLLATE NOCASE,
> >>>  "Disruption_id" INTEGER NOT NULL COLLATE NOCASE,
> >>> ...
> >>>  "location" integer NOT NULL COLLATE NOCASE,
> >>>
> >>> and the index should be
> >>>
> >>> CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions
> >>> ("version" ASC, "Disruption_id" ASC, "location" ASC);
> >>>
> >>> Once data has been entered, do ANALYZE.  This step may take a long
> >>> time.
> >>>
> >>> Simon.
> >>> ___
> >>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full text serch - Matching all except chosen

[Chris Locke]

If you want anything except "cat" then you can use the less than and
greater than comparison - <> .
select * from table where field <> 'cat'

This equates to "select all records where the value in the field column is
less than and is greater than 'cat'.  SQL allows you to search for less
than and greater than on alphabetic characters - so "select * from table
where field < 'cat' " means "select all the records from the table (called
'table' in my example) where the field (called 'field') has values less
(alphabetically) than 'cat' - , so 'apple', 'banana', and 'cabbage', but
not 'dog' or 'elephant' - they are greater (alphabetically).

Hope this helps.


Chris


On Tue, Jul 31, 2018 at 9:58 AM Luuk  wrote:

> On 31-7-2018 10:52, Luuk wrote:
> > On 31-7-2018 07:25, paul tracy wrote:
> >> Forgive me if this is the wrong way to do this but I'm a newbie.
> >> I am using version 3.24.0 with FTS5
> >> Is there a way to perform a full text search that returns every row
> except records matching a specified query string?
> >> The following does not work because of a syntax error as the syntax
> requires a query string before the NOT operator: … MATCH 'NOT blah';The
> following also does not work because the * operand cannot be used by
> itself: … MATCH '* NOT blah';In desperation I tried the following which
> returned data but a seemingly random set of data: MATCH NOT 'blah';
> >> I wound up using something like this …
> >> SELECT * FROM mytable WHERE id NOT IN (SELECT id from FullTextIndex
> WHERE FullTextIndex MATCH 'blah');
> >> I think this is much slower on large databases than a full FTS-based
> query but maybe I'm wrong and this is as fast as it gets.
> >> Any insight would be greatly appreciated.
> > From: http://www.sqlitetutorial.net/sqlite-full-text-search/
> >
> > For example, to get the documents that match the |learn| phrase but
> > doesn’t match the |FTS5| phrase, you use the |NOT| operator as follows:
> >
> > LECT *
> > FROM posts
> > WHERE posts MATCH 'learn NOT text';
> >
> >
> > But this should work too (untested):
> > SELECT *
> > FROM posts
> > WHERE NOT posts MATCH 'text' AND posts MATCH 'learn';
> >
> According to this docs it should, (so no need to test :-):-))
> https://www.sqlite.org/lang_expr.html
> ___
> 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] Use with Visual Studio

[Chris Locke]

I use system.data.sqlite.dll (taken from here:
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
with no problems in both VS 2017 Professional and VS 2017 Community.

Thanks,
Chris


On Mon, Jul 9, 2018 at 2:47 AM Roger Schlueter  wrote:

> I am considering using the .net version of SQLite but have two questions:
>
>  1. The documentation lists the versions of Visual Studio that are
> supported.  VS2017 is NOT listed.  Is VS2017 supported.
>  2. The documentation states "Due to Visual Studio licensing
> restrictions, the Express Editions can no longer be supported."
> (Yes, in red).  However, Microsoft no longer uses the phrase
> "Express Edition" but rather calls the freebie version "Community".
> Is this just semantics or does the red warning still apply to
> Community?  Also, I am unaware of any "licensing restrictions" on
> the Community editions that would preclude the use of SQLite.  Are
> there such restrictions and, if so, what are they?
>
> Roger
>
> ___
> 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] Mailing list shutting down...

[Chris Brody]

On Wed, Jun 13, 2018 at 3:00 PM Richard Hipp  wrote:
>
> Cross-posted to the fossil-users mailing list since www.fossil-scm.org

+1

> Even so, Discourse does seem like considering.  Does anybody else have
> any experience with Discourse, good or bad?

SQLCipher switched over to Discourse for the discussion forum at:
https://discuss.zetetic.net/c/sqlcipher

Seems to work pretty well for the user community. I really like having
a choice of social login, using Twitter myself.

I cannot argue with you about the "heartburn", looks like a bear to setup.

> Are there any volunteers willing to call me on skype and help set this up?

I have very limited experience with the software stack involved, would
be happy to teach myself in the process in case better qualified help
is not forthcoming.

On Wed, Jun 13, 2018 at 3:03 PM Simon Slavin  wrote:
> [...]
> > 
> > [...]
> > 
>
> Did you get a chance to try these ?

Both sound like nice short-term solutions, seem to admit that the bots
are bound to catch up someday:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailing list shutting down...

[Chris Brody]

On Wed, Jun 13, 2018 at 10:44 AM jungle Boogie  wrote:
> [...]
> http://spamassassin.apache.org/

Maybe just add SpamAssassin to the existing GNU MailMan setup?

http://www.jamesh.id.au/articles/mailman-spamassassin/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible Input Parser Issue Inf How to convert SQL file into database when a column value is Inf?

[Chris Brody]

On Tue, Jun 12, 2018 at 6:40 PM Richard Hipp  wrote:
> [...]
> Maybe use 1e999 and -1e999 instead?

I can confirm on SQLite versions 3.19.2 & 3.24.0:

sqlite> select 1e999;
Inf
sqlite> select -1e999;
-Inf

I wouldn't mind it if SQLite would be a little more symmetrical, i.e.
output of .dump with Inf or -Inf values would be valid input.

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

Re: [sqlite] Database is malformed but no further information

[Chris Brody]

My understanding is that mobile apps are not 100% predictable since
they may be randomly suspended or terminated, at any point of time.
The operating system should give a signal before suspending or
terminating but I would not trust it. Goes for Android, iOS, Windows
Mobile, and others.

To be extra safe I would use custom SQLite3 build with
-DSQLITE_DEFAULT_SYNCHRONOUS=3 build setting as documented in the
following places:
* https://www.sqlite.org/compile.html#extra_durable
* https://www.sqlite.org/compile.html#default_synchronous

Here is some recommended reading:
* https://www.sqlite.org/howtocorrupt.html
* 
http://sqlite.1065341.n5.nabble.com/Is-WAL-mode-more-robust-against-corruption-td99624.html
* 
http://sqlite.1065341.n5.nabble.com/Integrity-Check-Failure-Handling-td70289.html
* http://blog.niklasottosson.com/?p=852
* 
http://www.froebe.net/blog/2015/05/27/error-sqlite-database-is-malformed-solved/

Chris

https://www.linkedin.com/in/chrisbrody/

On Tue, Jun 12, 2018 at 6:21 PM skywind mailing lists
 wrote:
>
> Hi,
>
> when I load my database into sqlite3 and run an integrity check I only get 
> the error message: Error: database disk image is malformed
>
> I do not get any further information. What causes this simple error message? 
> I expected to get some more information what is actually the reason why 
> SQLite3 thinks that it is malformed. Interestingly I can do a dump to a SQL 
> file for the (whole?) database.
>
> I would really like to find out the root cause of this issue because I 
> experience a corrupt database once a while on iOS. This only happens when the 
> app is terminated while running when the iDevice switches off due to low 
> battery issues.
>
> Best regards,
> Hartwig
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite.org website is now HTTPS-only

[Chris Brody]

http://sqlite.org and https://sqlite.org seem to redirect OK to
https://sqlite.org/index.html

http://www.sqlite.org and https://www.sqlite.org seem to redirect OK
to https://www.sqlite.org/index.html

fossil clone https://www.sqlite.org/src sqlite.fossil works for me on
my mac (recent version installed with help from Homebrew)

A couple things you may want to look into fixing before Chrome starts
to flag non-HTTPS sites as insecure:

http://www.sqlite.org/cgi/src redirects to
http://www.sqlite.org/cgi/src/doc/trunk/README.md (no HTTPS)

Instructions in https://sqlite.org/getthecode.html#clone still give
the fossil clone command with non-HTTPS URL (page at
www.sqlite.org/cgi/src/doc/trunk/README.md does give the fossil clone
with HTTPS URL)

And a couple bonus items:

I think it would be better to have most www.sqlite.org links redirect
to "naked" links within https://sqlite.org (no www subdomain). More
concise, less risk that search bots will see some form of duplicate
content.

Consider redirecting https://sqlite.org and http[s?]://www.sqlite.org
to https://sqlite.org with no explicit index.html page, and do not
redirect https://sqlite.org to explicit index.html page.

I sincerely hope you will not undo the HTTPS work so far. Maybe
redirect people to one or more mirrors in case of troubles with HTTPS?

Thanks for your attention to this one!

On Thu, Jun 7, 2018 at 2:31 PM Richard Hipp  wrote:
>
> As an experiment, I have reconfigured the sqlite.org website to
> redirect all HTTP requests over to HTTPS.
>
> Let me know if this causes anybody any unnecessary grief.  It is easy
> enough to undo the setting.
>
> --
> 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] Filename encoding on Unix platforms

[Chris Brody]

On Tue, Jun 5, 2018 at 2:27 PM, Warren Young  wrote:
>
> On Jun 5, 2018, at 11:39 AM, R Smith  wrote:
> >
> > Any idea what might be the sudden spam activator?  I'm using Thunderbird 
> > client and gmail service.  Weird.
>
> Gmail seems to now consider an origin of mailinglists.sqlite.org to be a 
> high-quality signal of spam.
> [...]

I would like to remark that GMail seems to mark emails from
mailinglists.sqlite.org as unencrypted.

Google is definitely not happy with unencrypted websites, suspect they
are not so happy with unencrypted mailing lists either.

Just my (US) $0.02 worth:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Chris Smith]

"You are soo, bloated," said Java.

On Thu, May 31, 2018, 11:58 R Smith  wrote:

>
> On 2018/05/31 5:17 PM, ven...@intouchmi.com wrote:
> > I have to agree with Bob!
> >
> > We have considered SQLITE for our project.  Going over 500Kbytes puts it
> > just beyond the size of our Flash - the current Firmware.
>
> I stand corrected! It seems the embedded systems with still an extremely
> limited memory footprint size may not be as thin on the ground as I
> imagined, and I regret trying to categorize all embedded systems under
> the same ideal - apologies for that.
>
> Towards my point though, both Bob and Vance, would you be especially
> swayed if the marketing slogan had said "under a megabyte" as opposed to
> "under half a megabyte"?  I still feel that this level of embedded
> system is not common, and even where it might be common, I bet that
> slogan is not the catch phrase that got you interested in SQLite (or
> would sway you from choosing it).
>
> It's however clear my view may not be 100% representative, so perhaps
> the  KiB or 0.5 MiB route has its place.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Cheers,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Chris Brody]

On Thu, May 31, 2018 at 11:38 AM, Richard Hipp  wrote:
> [...]
> By using multiple SQLITE_OMIT compile-time options to leave out
> features, I can get the size down to 308,189 bytes using gcc-7 -Os
> -m32.

@Richard can you elaborate some more on how you make this kind of a build?

I wouldn't mind if we drop some more less-used options from the
default build to keep the standard size "less than half a megabyte".
Also -1 for kibibyte/mebibyte wording on my part.

On Thu, May 31, 2018 at 11:57 AM, Christian Schmitz
 wrote:
> [...]
> Maybe your graph should have three lines.

+1 would be nice, not major though (I think)

On Thu, May 31, 2018 at 11:58 AM, R Smith  wrote:
> [...]
> Towards my point though, both Bob and Vance, would you be especially swayed
> if the marketing slogan had said "under a megabyte" as opposed to "under
> half a megabyte"?

I think Vance already gave the answer (negative). Would it be an idea
to have size slogans for both regular and embedded builds?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Resources for learning SQLite

[Chris Locke]

I'm a VB.Net developer (so don't hate me...) but use SQLite quite
extensively at work - works really well (well, obviously...)
If you know SQL Server, then SQLite isn't that miuch different, and the
class wrappers I use are identical, apart from the connection strings,
which I could pass on.  The wrappers are quite basic (no pun intended) so
depends on your requirements and development needs..

Thanks,
Chris



On Thu, Mar 29, 2018 at 6:20 PM, Jay Kreibich <j...@kreibi.ch> wrote:

>
> > On Mar 29, 2018, at 12:06 PM, Mike Clark <cyberherbal...@gmail.com>
> wrote:
> >
> > I suspect there are already threads on this, so apologies for the
> potential
> > duplicate...
> >
> > I'm a long-time C# developer who has used Sql Server for decades, but I'm
> > just getting started with SQLite. Does anyone have any recommendations
> for
> > books or online resources?
> >
> > I'm particularly interested in resources that use C#.
> >
> > I've been working with "Using SQLite" by Jay A. Kreibich, and it's very
> > useful, but if there's something more advanced I'd love to know about it.
>
> Glad it’s useful.  It’s also a bit out of date, as it was published in
> 2010 when the latest version was 3.6.  The core stuff is still valid, but
> there have been a LOT of advancements in the last eight years.
>
>   -j
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Chris Locke]

I see - thanks Paul.  I misunderstood.  Thanks for your detailed
explanation.


Chris

On Wed, Mar 21, 2018 at 9:13 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> Actually it is totally different Chris
>
> >  I read that - but my point was more that some people seem to think that
> an
> > int primary key can be auto incrementing, it can't.
>
> an INT primary key cannot be autoincrementing
>
> An INTEGER primary key and an INTEGER primary key autoincrement work in
> essentially the same way. i.e. if you insert a row and do not specifically
> assign a value to the pk (i.e. you assign NULL) the value assigned will
> usually be one more than last pk used.
>
> if you have an INT primary key and add a new row with no value assigned to
> the PK then null will be stored (all null values are treated as unique in
> SQLite and so as far as the PK is concerned all rows are different).
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (id int primary key, data text);
> sqlite> insert into test (data) values('row 1');
> sqlite> insert into test (data) values('row 2');
> sqlite> select id, data from test;
>   |row 1
>   |row 2
>
> of course the rowid is still there hidden behind the scenes and you can
> access it with
>
> sqlite> select rowid, id, data from test;
> 1|  |row 1
> 2|  |row 2
>
> but if you want to use the rowid as the PK then you should probably use an
> INTEGER pk so it becomes an alias for the rowid in the first place.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 20 March 2018 at 16:44, Chris Locke <sql...@chrisjlocke.co.uk> wrote:
>
> > >  some people seem to think that an int primary key can be auto
> > incrementing, it can't
> >
> > But it works in the same way .... sort of.  Its auto incrementing, with
> the
> > caveat that if the last row is deleted, the previous number will be used
> > again.  Depending on the database schema, this may or may not cause
> issues.
> >
> >
> > Thanks,
> > Chris
> >
> >
> > On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> > sandersonforens...@gmail.com> wrote:
> >
> > >  I read that - but my point was more that some people seem to think
> that
> > an
> > > int primary key can be auto incrementing, it can't.
> > >
> > >
> > > SQLite version 3.18.0 2017-03-28 18:48:43
> > > Enter ".help" for usage hints.
> > > Connected to a transient in-memory database.
> > > Use ".open FILENAME" to reopen on a persistent database.
> > > sqlite> create table test (id integer primary key autoincrement);
> > > sqlite> create table test2 (id int primary key autoincrement);
> > > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > > sqlite>
> > >
> > > Paul
> > > www.sandersonforensics.com
> > > skype: r3scue193
> > > twitter: @sandersonforens
> > > Tel +44 (0)1326 572786
> > > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > > Forensic-Toolkit
> > > -Forensic Toolkit for SQLite
> > > email from a work address for a fully functional demo licence
> > >
> > > On 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:
> > >
> > > >
> > > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > > >
> > > >> Autoincrement can ONLY be used with an integer primary key
> > > >>
> > > >
> > > > I think Peter's shouting is more about the inability to distinguish
> via
> > > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> > both
> > > > of which are of course integer and can be auto-incrementing, but only
> > one
> > > > of which is an alias for rowid.
> > > >
> > > >
> > > >
> > > > ___
> > > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Chris Locke]

>  some people seem to think that an int primary key can be auto
incrementing, it can't

But it works in the same way  sort of.  Its auto incrementing, with the
caveat that if the last row is deleted, the previous number will be used
again.  Depending on the database schema, this may or may not cause issues.


Thanks,
Chris


On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

>  I read that - but my point was more that some people seem to think that an
> int primary key can be auto incrementing, it can't.
>
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (id integer primary key autoincrement);
> sqlite> create table test2 (id int primary key autoincrement);
> Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> sqlite>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:
>
> >
> > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> >
> >> Autoincrement can ONLY be used with an integer primary key
> >>
> >
> > I think Peter's shouting is more about the inability to distinguish via
> > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY, both
> > of which are of course integer and can be auto-incrementing, but only one
> > of which is an alias for rowid.
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Chris Locke]

0, across approx 20 databases, ranging from small 3 table schemas, to a
couple of ERP systems using 120+ tables.

Thanks,
Chris


On Fri, Mar 16, 2018 at 4:09 PM, R Smith <ryansmit...@gmail.com> wrote:

> Across 8 production systems and about 120 SQLite DBs for us - Not a single
> AUTOINCREMENT - so   0 .
>
> I have to confess though, there are less critical places where we use the
> ability of SQLite to insert and automatically incremented INT primary keys
> (so Non-AUTOINCREMENT keys), in case that is relevant to the knowledge you
> seek.
>
>
>
> On 2018/03/16 5:37 PM, Richard Hipp wrote:
>
>> This is a survey, the results of which will help us to make SQLite faster.
>>
>> How many tables in your schema(s) use AUTOINCREMENT?
>>
>> I just need a single integer, the count of uses of the AUTOINCREMENT
>> in your overall schema.  You might compute this using:
>>
>> sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>>
>> Private email to me is fine.  Thanks for participating in this survey!
>>
>
> ___
> 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] BF Interpreter

[Chris Locke]

"Thats the beauty of it.  It doesn't *do* anything."   ;)



On Thu, Mar 1, 2018 at 6:55 AM, Gary Briggs  wrote:

> Thanks to the help the other day with the strange concatentation result.
>
> I was referring to a BF interpreter I was working on, in pure SQLite SQL.
> Well, here it is, working.
>
> Hopefully no-one finds this useful,
> Gary
>
> WITH RECURSIVE
>   program AS
>  (SELECT '++[>+++>++>+++>+-]>++.>+.+++..+
> ++.>++.<<+++.>.+++.--..>+.>.' AS p,
> '' AS input, 3 AS width
> ),
>   jumpdepth AS
>  (SELECT 0 AS idx, 0 AS jumpdepth, '' AS jumplist, NULL as jumpback,
> NULL AS direction, p || '0' AS p, width FROM program
>   UNION ALL
> SELECT idx+1, CASE SUBSTR(p, idx+1, 1)
> WHEN '[' THEN jumpdepth+1
> WHEN ']' THEN jumpdepth-1
> ELSE jumpdepth END,
> CASE SUBSTR(p, idx+1, 1)
> WHEN '[' THEN SUBSTR('000' || (idx+1), -width) ||
> jumplist
> WHEN ']' THEN SUBSTR(jumplist,width+1)
> ELSE jumplist END,
> CASE SUBSTR(p, idx+1, 1)
> WHEN ']' THEN CAST(SUBSTR(jumplist,1,width) AS INTEGER)
> ELSE NULL END,
> CASE SUBSTR(p, idx+1, 1)
> WHEN '[' THEN 'L'
> WHEN ']' THEN 'R'
> ELSE NULL END,
> p, width
>   FROM jumpdepth
>   WHERE LENGTH(p)>=idx),
>   jumptable(a,b,dir) AS
>   (SELECT idx,jumpback,'L' FROM jumpdepth WHERE jumpback IS NOT NULL
>   UNION ALL
>SELECT jumpback,idx+1,'R' FROM jumpdepth WHERE jumpback IS NOT
> NULL),
>   bf(ep, p, width, defaulttapeentry, ip, dp, instruction, output, input,
> tape) AS
>(SELECT 0, p, width, SUBSTR('000', -width), 1, 1, '', '',
> input, SUBSTR('00', -width)
>FROM program
> UNION ALL
> SELECT ep+1, p, width, defaulttapeentry, CASE WHEN jumptable.b IS
> NOT NULL AND
> ((dir='R' AND CAST(SUBSTR(tape, width*(dp-1)+1, width) AS
> INTEGER)=0)
> OR
>  (dir='L' AND CAST(SUBSTR(tape, width*(dp-1)+1, width) AS
> INTEGER)!=0)) THEN jumptable.b
>   ELSE ip+1 END,
> CASE SUBSTR(p, ip, 1)
>WHEN '>' THEN dp+1
>WHEN '<' THEN MAX(dp-1,1)
>ELSE dp END,
> SUBSTR(p, ip, 1),
> CASE WHEN SUBSTR(p, ip, 1)='.' THEN (output || CHAR(SUBSTR(tape,
> (dp-1)*width+1, width))) ELSE output END,
> CASE WHEN SUBSTR(p, ip, 1)=',' THEN SUBSTR(input, 2) ELSE input
> END,
> CASE SUBSTR(p, ip, 1)
> WHEN '<' THEN CASE WHEN dp=1 THEN defaulttapeentry || tape
> ELSE tape END
> WHEN '>' THEN CASE WHEN dp*width=LENGTH(tape) THEN tape ||
> defaulttapeentry ELSE tape END
> WHEN '+' THEN SUBSTR(tape,1,width*(dp-1)) ||
> SUBSTR('000' || (CAST(SUBSTR(tape,width*(dp-1)+1,width) AS
> INTEGER)+1), -width) || SUBSTR(tape,width*dp+1)
> WHEN '-' THEN SUBSTR(tape,1,width*(dp-1)) ||
> SUBSTR('000' || (CAST(SUBSTR(tape,width*(dp-1)+1,width) AS
> INTEGER)-1), -width) || SUBSTR(tape,width*dp+1)
> WHEN ',' THEN SUBSTR(tape,1,width*(dp-1)) ||
> SUBSTR('000' || (UNICODE(SUBSTR(input,1,1))), -width) ||
> SUBSTR(tape,width*(dp+1))
> ELSE tape END
>   FROM bf LEFT JOIN jumptable ON jumptable.a=ip WHERE LENGTH(p) >= ip)
> SELECT output FROM bf ORDER BY ep DESC LIMIT 1;
>
> ___
> 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] More sqlite header questions

[Chris Brody]

On Tue, Feb 13, 2018 at 2:03 PM, Simon Slavin  wrote:
> [...]
> There are two possibilities:
>
> A) The SQLite API was used correctly, including being allowed to close all 
> files it opened.
> B) Any other situation.
>
> If (A) happened, you can predict things about the database header and you can 
> rely on SQLite documentation about the format of files it uses.  Otherwise 
> you can't.

Thanks Simon for the quick response. Can you clarify the following:
- Does this imply that a SQLite database may be left in some kind of
unrecoverable, corrupted, or otherwise invalid state in case an
application would terminate without calling sqlite3_close() on all
open database connections?
- If yes, what can a programmer do to protect the data in case an
application is abruptly terminated for any reason?
- Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
mitigate this kind of possible corruption?

I think this is especially important for mobile apps which may be
terminated without notice, especially when using hybrid app frameworks
such as Cordova/PhoneGap.

> However, the SQLite library goes through heroic measures
> [...]

I am sure that this was at the cost of many heroic programmer hours.

> The SQLite header is less than 100 bytes long.  It all fits within one sector 
> / page of a storage device i.e. the entire header is written in one 
> operation.  If you ever discover an inconsistent header there's a bug in 
> SQLite.  The contents of the header are listed in
>
> 

Thanks for the clarification.

>> - Any recommended explanations or resources to understand how it may
>> be possible to obtain the correct sqlite database information (such as
>> WAL or other journal mode, actual database size, number of pages,
>> page/cache size, etc.)?
>
> PRAGMAs are available for retrieving all this information.  See
>
> 
> 
> 
>
> The size of the database is page_count * page_size.  Other PRAGMAs on the 
> same page provide other information which might be covered in your "etc.".

Makes sense ... assuming that the database is not corrupted beyond the
heroic repair mechanism:)

> If you have any other questions, please do not hesitate to ask them here.

Will do. Looking forward to the requested clarification. Thanks for
the answers so far.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] More sqlite header questions

[Chris Brody]

Thanks to DRH for the quick answer. So I would infer that the sqlite
file header would normally be in a consistent state, and only be in an
inconsistent or otherwise incorrect state in the following cases:
1. modification (write) is in progress (until the sqlite3 code has a
chance to finish the modification)
2. application crashed or otherwise terminated without finishing the
modification (until the application or some other sqlite program opens
the database again)
3. rogue code writes data to the file descriptor in use by sqlite
4. other causes described in http://www.sqlite.org/howtocorrupt.html
such as rogue code, rogue process, rogue script, OS bug, dishonest
hardware, etc.

My understanding is that iOS applications with shared databases can be
especially sensitive to sqlite headers as discussed in:
- https://developer.apple.com/library/content/technotes/tn2408/_index.html
- note referenced by SQLCipher project, not sure if I can understand
it 100%
-  https://github.com/sqlcipher/sqlcipher/issues/255#issuecomment-355063368
- discovery that iOS checks header of shared SQLite databases, with
special handling of sqlite databases in WAL mode

I still have the following questions:
- Am I correct to say "that the sqlite file header would normally be
in a consistent state"?
- Am I missing anything or otherwise mistaken here?
- How likely would the header continue to indicate that the database
is an sqlite database in case 1 or 2 above?
- How likely would the header continue to indicate whether the
database is in WAL or any other journal mode in case 1 or 2 above?
- Any recommended explanations or resources that explain how iOS
handles shared sqlite databases (in more detail, with simpler terms)?
- Any recommended explanations or resources to understand how and when
sqlite header may be in inconsistent or otherwise incorrect state?
- Any recommended explanations or resources to understand how it may
be possible to obtain the correct sqlite database information (such as
WAL or other journal mode, actual database size, number of pages,
page/cache size, etc.)?

On Tue, Feb 13, 2018 at 9:10 AM, Richard Hipp <d...@sqlite.org> wrote:
>
> On 2/13/18, Chris Brody <chris.br...@gmail.com> wrote:
> > I was wondering what would happen if there would be an application crash,
> > system crash, or power failure while SQLite is updating the file header?
> >
> > Did I miss an explanation somewhere?
>
> The content is replicated either in the rollback-journal or in the
> write-head log (depending on whether or not you are in WAL mode) and
> will be recovered automatically when the database is first opened
> after power has been restored.  See
> https://www.sqlite.org/atomiccommit.html for further information.
> --
> 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

[sqlite] Crash when writing header

[Chris Brody]

I was wondering what would happen if there would be an application crash,
system crash, or power failure while SQLite is updating the file header?

Did I miss an explanation somewhere?

--
From: David Raymond 
Date: Tue, Feb 6, 2018 at 12:04 PM
Subject: Re: [sqlite] Header corruption
To: SQLite mailing list 


Things stored in the first 25 bytes include page size, WAL status, and the
file change counter. So at least part of the header there gets changed with
every committed write transaction.

http://www.sqlite.org/fileformat2.html


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Deon Brewis
Sent: Tuesday, February 06, 2018 11:57 AM
To: SQLite mailing list
Subject: [sqlite] Header corruption

I’m trying to track down SQLITE corruptions that seems to corrupt our
databases in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and
reading and writing to the database fine, and then suddenly we start
getting a SQLITE_NOTADB errors. The first error we got was on trying to
execute a ‘BEGIN TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format
3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@
...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ; 
.‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ;
žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ; 
..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ;
.dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it
should be “SQLi” is a bit too much of a coincidence to ignore or write off
as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem
fine. It’s just these first 25  bytes that gets corrupted. If I restore
Offsets 0 to Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header
string?

- Deon

___
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] Easiest way to get day of week as a string (not a number)?

[Chris Green]

R Smith <ryansmit...@gmail.com> wrote:
> The reason day-names are not part of the standard set of date-time 
> functions is that they are not standard and certainly not international.
> 
> For you it is "Mon, Tue, Wed, Thu, Fri, Sat Sun"...
> 
> But for me it might be:
> 
> "Lun, Mar, Mer, Jeu, Ven, Sam, Dim" or sometimes simply "Lu, Ma, Me, Je, 
> Ve, Sa, Di" etc.
> 
But systems have language variables which tell which set to use.

The C strftime function has just about every form of day and month
name you can imagine and presumably that works with the locale setting
to give them to you in your own language.

-- 
Chris Green
·

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

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[Chris Green]

x <tam118...@hotmail.com> wrote:
> Don’t think you’ll get it any less ugly than
> 
> substr('SunMonTueWedThuFriSat',strftime('%w',Date)*3+1,3);
> 
Yes, thanks, that's where I had got to!  :-)

-- 
Chris Green
·

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

[sqlite] Easiest way to get day of week as a string (not a number)?

[Chris Green]

I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
way of doing this in a sqlite select?

I guess I can do something (horrible?) with the numeric day of week
and substr() but is there not an easier way?

-- 
Chris Green
·

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

Re: [sqlite] Possible bug when adding "on delete cascade" via DB Browser for SQLite

[Chris Locke]

DB Browser for SQLite is a third party product which just uses SQLite.  Any
support issues should be directed to their gitHub support page.
https://github.com/sqlitebrowser/sqlitebrowser/issues

To confirm whether its an issue in DB Browser for SQLite or SQLite itself,
you can 'reproduce' the issue using the SQLite command line tool.  If that
too fails, then the issue should be confirmed here where it will get
properly investigated.

I know DB Browser for SQLite is still in heavy development, and it does
have a number of little quirks like this. ;)


Thanks,
Chris

<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 13, 2018 at 6:02 PM, Magnus Andersson <
magnus.anders...@dexicon.se> wrote:

> Today I used DB Browser for SQLite, version 3.10.1 on a windows 7 machine,
> and encountered what seems to be a bug, either in the SQLite browser or
> SQLite as such.
>
>
>
> If this is not the correct mailing list to post this in I apologize, just
> let me know.
>
>
>
> I had a table with foreign keys, and added "on delete cascade" via the
> function "Modify table". The "create table" script then ended up like this.
>
>
>
> (Strange one that caused wrong cascaded deletes)
>
> CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
> `speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
> KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
> KEY(`speechInLanguageID`), FOREIGN KEY(`speechID`) REFERENCES
> `tblSpeech`(`speechID`) ON DELETE CASCADE )
>
>
>
> I then tested the cascade action, and everything in the table was deleted,
> not just those rows that should have been deleted.
>
>
>
> I then went back to a backup, did the whole thing again, and then
> everything
> worked, but I noticed that the "create table" script was a little
> different.
>
>
>
> (Corrrect one that worked)
>
> CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
> `speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
> KEY(`speechID`) REFERENCES `tblSpeech`(`speechID`) on delete cascade,
> FOREIGN KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
> KEY(`speechInLanguageID`) )
>
>
>
>
>
> As you see (as far as I can see) everything is the same in both, except for
> the order in which they appear (if you paste both scripts on two lines
> after
> each other in something like notepad without word wrap, you can see it more
> clearly).
>
>
>
> Magnus
>
>
>
> ___
> 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] Is WAL mode more robust against corruption?

[Chris Brody]

On Thu, Dec 28, 2017 at 3:59 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 28 Dec 2017, at 8:10pm, Chris Brody <chris.br...@gmail.com> wrote:
>
>> [...]
>> But I wondered if WAL may be more robust against possible sqlite
>> corruption, in theory or in practice.

After sending I found a couple threads that *seem* to confirm that WAL
mode may be more reliable against possible corruption:
* http://sqlite.1065341.n5.nabble.com/WAL-mode-is-reliable-td41264.html
* https://marc.info/?l=sqlite-users=132052237302135=2

>> Any comments?
>
> WAL mode makes SQLite neither more nor less liable to corruption.  You should 
> not be seeing corruption in SQLite no matter what mode it’s in.

Thanks Simon for the response. I just found it today, unfortunately
directed to my spam folder. Makes sense, assuming there is nothing
wrong according to the "how to corrupt" checklist.

> If you want a PRAGMA to strengthen against corruption, try "PRAGMA 
> synchronous = FULL":
>
> <https://www.sqlite.org/pragma.html#pragma_synchronous>

I will guess you meant to try "PRAGMA synchronous = EXTRA" (seems to
be FULL by default). Maybe a good idea in a hybrid mobile application
environment.

> If you are seeing corruption, you might want to see this checklist.
>
> <https://www.sqlite.org/howtocorrupt.html>

Right. The challenge for me is that my users are JavaScript
developers, often with very limited native platform experience.

> Given that you’re running on a phone/tablet, emphasis is on handling 
> suspension (backgrounding) and termination of the app properly.  There are 
> people here experienced with using SQLite on iPhone and Android who can 
> criticise your approach.
>
> If nothing obvious occurs to you, please post some details: which platform, 
> which circumstances, are you doing multi-thread or multi-process, do you 
> check result codes for your API calls, are you getting error results ?
>
> Simon.

In general I would expect that the app may be suspended or terminated
with no advance notice due to the hybrid environment they run in. I
think the Cordova/PhoneGap framework should give some form of
notification but I would not trust this mechanism to be 100% reliable.
I also want the apps to be robust against possible crashes, memory
issues for example. Right now I am thinking it would be safest to use
"PRAGMA synchronous = EXTRA" at the beginning, feedback would be
highly appreciated.

The hybrid JavaScript/native SQLite API component I maintain is
available in the following location:
https://github.com/litehelpers/Cordova-sqlite-storage

This component acts as a bridge between JavaScript and native code on
Android, iOS, and Windows. I am in the middle of some updates and the
documentation really needs cleanup at this point. Right now it is
using one background thread per database on Android, dynamic
background threads on iOS, no form of background threading on Windows.

I already fixed a couple possible causes of corruption on iOS as discussed in:
- https://github.com/litehelpers/Cordova-sqlite-storage/issues/703
- https://github.com/litehelpers/Cordova-sqlite-storage/issues/716

Reports of database corruption have been extremely rare in this
project but I just wanted to check how to make it as safe as possible.

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

[sqlite] Is WAL mode more robust against corruption?

[Chris Brody]

Hello,

I am considering whether or not to recommend the WAL mode for users in the
PhoneGap sqlite plugin that I maintain. The negative I see is the delays
that may result at certain points from the need for database checkpoints.
But I wondered if WAL may be more robust against possible sqlite
corruption, in theory or in practice. Any comments?

Thanks and happy 2018!

Chris

https://www.linkedin.com/in/chrisbrody/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Modify the sqlite database with DB Browser to update a new field

[Chris Locke]

I would suspect this is "DB Browser for SQLite" application. It has an
'execute SQL' tab, so the raw code can be directly entered, or the table
modified through a gui. It's pretty straightforward to use, and had a wiki
on its support pages.
This isn't the place to guide you though, but it has a very active support
group.



Thanks,
Chris

On 24 Dec 2017 7:17 am, "Keith Medcalf" <kmedc...@dessus.com> wrote:

>
> This sounds like a "DB Browser" issue, whatever a "DB Browser" is.  You
> should direct questions about how to enter SQL commands into "DB Browser"
> to the "DB Browser" support channel.
>
> This is especially true since it appears that you have solved the problem
> satisfactorily when using the SQLite3 command line shell.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Miel Loinaz
> >Sent: Saturday, 23 December, 2017 21:34
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Modify the sqlite database with DB Browser to
> >update a new field
> >
> >Hello,
> >I want to modify a sqlite database of a weather station software
> >(wewx.sdb). Date
> >and time are in the form of echo or linux time (field date1). I have
> >added
> >a new field (date2) where I want to display human readable date and
> >time.
> >
> >In Linux console I get it running:
> >
> >UPDATE table1 SET date2 = datetime (date1, 'unixepoch', 'localtime');
> >
> >But I do not know how to modify the sqlite database with DB Browser,
> >I'm a
> >newbie. Where should I enter this code?
> >
> >Thanks and Merry Christmas!
> >___
> >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] Importing Text to Create a Table

[Chris Locke]

What operating system are you using, and what software are you using to do
the import?  What specificially are you trying to import?
If you perform a sequence of 'insert' statements, then that can be time
consuming - its better to incorporate them into one 'transaction' - sqlite
bundles the operations into one big operation.  This is better IO wise - it
performs far less writes to the disk, etc.  But understanding the methods
you're using so far would be helpful.

On Fri, Dec 22, 2017 at 12:22 AM, Lawrence Murphy  wrote:

> I am supporting a website which aims to protect a forest from development.
> The website produces an email for supporters to mail out. A copy of the
> email is sent to our Gmail address and we wish to capture the supporters
> return email address. Google provides an archive of our Gmail account which
> is 458Mbs in size and contains a lot of superfluous data.
>
> I have tried making a table with one column, text 255c in size and doing
> the import but it takes more than overnight and is still running. Is there
> a quicker way to import the data?
>
> Warm Regards,
> Lawrence
>
> Mb: 0408 403 324
> PO Box 263 Cherrybrook NSW 2126
>
> Lose Weight, Gain Health & Prevent Disease
> Find out how here
> http://lwghpd.blogspot.com.au/
> ___
> 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] Missing data table

[Chris B]

Afternoon all,


Just joined the list.  Am a fairly long term user of SQLite Expert Personal (my 
version is 3.5.96.2516 - I upgraded to try version 4 a while back but the 
columns in the Data tab were fixed which is no good for me, I couldn't drag 
them wider so I went back to v3), and while I built my PC from scratch and am 
good with a lot of software, I'm by no means an expert when it comes to SQL or 
databases.


The reason for my post is to help my dad out!  We both maintain our own 
databases (Windows 7) - in the left hand pane of SQLite, you have the database 
name (basestation) and the table name (Aircraft) - I don't think this list 
allows snips or I'd send a screenshot but you know what it looks like.


Twice now in the last two months, the "Aircraft" table has vanished from my 
dad's database effectively showing no data in the data tab on the main window, 
but the file size has remained the same, suggesting the data is in there 
somewhere but without it the database is useless.  Now I know he's obviously 
unwittingly done something, but I'd like to know what he did, how he did it, 
and if there's any way to undo it, should it happen again.  After the first 
loss, I closed the left hand pane so he couldn't accidentally delete the table, 
but it's gone again so something else is amiss.  He uses a laptop with a 
touchpad and I know how temperamental they can be regarding a mouse swipe vs a 
mouse click, especially if you accidentally brush it.


Yes we've backed up the database, but I'd like to prevent this from happening 
in the first place, or at least be able to undo it and get the table back.


If there's anything I've left out please ask, and many thanks for any help with 
this.

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

Re: [sqlite] Error code 14 for the Journal file

[Chris Locke]

The 'scary bit' here is the device not functioning.
> A device attached to the system is not functioning.

Is the database/journal on the same/local PC or on a network?


Chris

On Mon, Dec 4, 2017 at 4:27 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 4 Dec 2017, at 9:31am, Tilak Vijayeta <vijayeta.ti...@gi-de.com> wrote:
>
> > PID:02FB0036 TID:06B60006 SQLite error (14): os_win.c:36317: (31)
> winOpen(\--\TTDB.db3-journal) - A device attached to the system is
> not functioning.
>
> You appear to have a hardware or operating system problem which is causing
> a SQLite error.  SQLite error 14 is "Unable to open the database file".
>
> Is there some sort of protection on the journal file, or on the directory
> it’s in, which is preventing your program from accessing it ?
>
> > PID:02FB0036 TID:06B60006 SQLite error (778): os_win.c:34215: (5)
> winWrite2(\\TTDB.db3-journal) - Access is denied.
>
> "Access is denied" suggests there is some sort of protection or access
> problem.
>
> > 1.A Journal file gets created as a backup on every transaction,
> as in Update or Insert. This file gets deleted after the transaction is
> committed.
>
> This is not normal for SQLite.  Can you tell us the result of
>
> PRAGMA journal_mode
>
> for whatever database file you’re accessing ?
>
> > 2.   I assumed that the journal file goes 'Read Only' sometimes
> because of which Delete operation fails.
>
> That is not something SQLite does.
>
> > 3.   To fix it, every time I come across a SQL Lite error 14, I
> change the read only property of the Journal file.
>
> This does not fit the way SQLite works.  If you are in a situation where
> you have to do this, you have more serious problems which you will have to
> deal with later.  You have something which is preventing SQLite from
> working properly.  Are you running any anti-virus program ?  Can you
> disable it temporarily while you test your program to see if it’s the
> culprit ?
>
> Simon.
> ___
> 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] Article on AUTOINC vs. UUIDs

[Chris Locke]

> if your systems are set up in a sane way, the MAC address alone would
prevent collisions, no?
> And on the same system, are collisions even possible?

Google says "In the case of standard version 1 and 2 UUIDsusing unique MAC
addresses from network cards, collisions can occur only when an
implementation varies from the standards, either inadvertently or
intentionally."

I used to use UUIDs, but when looking at a database using many foreign
keys, it was a debug nightmare looking for a specific key.  After switching
to auto increment fields, its nice when debugging to look for
'templateId=4310' and not
'templateId='8af78580-bb03-4674-92ab-33cef99afdb2'.

On Thu, Nov 30, 2017 at 3:23 PM, Jay Kreibich  wrote:

>
> There are some minor points, but I agree that it basically boils down to
> “serial IDs break security-by-obscurity.”
>
> That’s true, but….
>
>   -j
>
>
>
>
> > On Nov 30, 2017, at 9:00 AM, Keith Medcalf  wrote:
> >
> >
> > Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> is describing as the problems with serial IDs (or using the RowID) are
> simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the
> RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> Buggs Bunny would say "What a maroon!".
>
> >> -Original Message-
> >> From: sqlite-users [mailto:sqlite-users-
> >> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> >> Sent: Thursday, 30 November, 2017 07:16
> >> To: SQLite mailing list
> >> Subject: [sqlite] Article on AUTOINC vs. UUIDs
> >>
> >> Thought some of you might enjoy seeing this article.  I make no
> >> comment on what I think of the reasoning therein.  It’s set in the
> >> PostgreSQL world, but you could make an external function for SQLite
> >> which generates UUIDs.
> >>
> >>  >> increment-is-a-terrible-idea/>
> >>
> >> "Today, I'll talk about why we stopped using serial integers for our
> >> primary keys, and why we're now extensively using Universally Unique
> >> IDs (or UUIDs) almost everywhere."
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Chris Locke]

> Why do I want store ID numbers
> whose values may change? Why not.

Because that's not what the row id column is for. Not strictly. That's why
it's called 'id' - it's an identification field. You can't (shouldn't) be
using it for other means. A database requirement later might need that
column to link to another table. Create the database properly and use the
columns properly.



Thanks,
Chris

On 22 Nov 2017 6:40 am, "Shane Dev" <devshan...@gmail.com> wrote:

Hi Igor,

Homework exercise? No, this is purely a hobby project in my free time. My
goal is see how much logic can moved from application code to the database.

Why do I want store ID numbers whose values may change? Why not. Obviously,
this would be bad idea if the ID column was referenced by other column /
table. In that case, I would have created a different table such as

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, sort integer unique, name text);

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.
Peter Nichvolodov's trigger solution (
https://www.mail-archive.com/sqlite-users@mailinglists.
sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.


On 22 November 2017 at 00:11, Igor Korot <ikoro...@gmail.com> wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke <j...@mooseyard.com> wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith <rsm...@rsweb.co.za> wrote:
> >>>
> >>> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/
happen,
> but is hugely unlikely.
> >>
> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> say you sort rows by date. You’ve already got some entries from 2015 in
> your database, and some from 2017. Someone now inserts 60 entries from
> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> this immediately hits that case.
> >
> > Yes, if you use this method, you do need to renumber them every so
> often.  You assess this when you’re working out (before + after) / 2, and
> you do it using something like the double-UPDATE command someone came up
> with earlier.
> >
> > But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
>
> Homework exercise?
> Stupid requirements?
>
> Thank you.
>
> >
> > Simon.
> > ___
> > 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] Best way to develop a GUI front-end

[Chris Locke]

> For now, I am going to start
> with a windows forms application in vb.net or forms in OpenOffice.

I'd install SharpDevelop (
http://www.icsharpcode.net/opensource/sd/Default.aspx).  Download v4.4 if
you plan on using VB.Net, as the newer v5 doesn't support VB - only C#.
SharpDevelop is a 15 MB download, not a 4.6 GB download of Visual Studio.
I primarily write database apps using SQLite and VB.Net, so have a nice
database class 'wrapper', depending on your database skills... and what you
plan on building.
Happy to blog a beginners guide and whisk you along a SQLite coding journey.

As previously 'warned', it is a Windows only environment, so if you have
Linux friends, they won't be able to view your creations.  However,
building applications is quick and easy but immensely rewarding.  I love my
job.


Thanks,
Chris


On Wed, Nov 15, 2017 at 8:16 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Thank you very much for all your suggestions.  For now, I am going to start
> with a windows forms application in vb.net or forms in OpenOffice.  Tcl/Tk
> is a steeper learning curve, and if someone can point me to some good
> resources that will walk a beginner through the development of a windows
> GUI on that platform, I would appreciate it.  Thank you again.
>
> Balaji Ramanathan
> ___
> 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] BedrockDB interview on Floss Weekly

[Chris Locke]

Thanks Ryan - a handy summary.  Food for thought.


Thanks,
Chris

On Fri, Oct 27, 2017 at 11:57 AM, R Smith <rsm...@rsweb.co.za> wrote:

>
> On 2017/10/27 11:52 AM, Bart Smissaert wrote:
>
>> Is this BedrockDB something that could be used to connect to a server and
>> run SQL and avoid the problems (mainly slowness) that SQLite would
>> have in this situation?
>>
>
> and
>
> Chris Locke wrote:
> My work environment is mainly Windows servers/users.  SQLite 'works' but is
> obviously unsupported (file locking, etc).
> Could BedrockDb help in this area?  Sounds like it works 'locally' but
> 'networkably' (is that a word?!)  Couldn't find any Windows-friendly builds
> or guides.
> Even assuming it could be set up, it also looks like there aren't .Net
> drivers or 'wrappers' for it?
>
>
>
> This is not directly a client-server architecture, but you can achieve the
> same result by having a server with a DB node and a local node that you
> connect to locally, so you simply talk to your local node which in turn
> communicates to the server. The main difference between that and a
> client-server setup is that you experience no latency whatsoever, to your
> app it's as-if the server exists on the local machine (which is technically
> exactly the case), and of course the full dataset exists in two places,
> locally and on the server (at least 2, but it is recommended to have 3
> places), which may be unwanted if local storage is really tight, but then
> SQLite would also not have worked for you.
>
> [snipped]
> Anyway, that's how we do it.
>
> Cheers,
> Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[Chris Locke]

My work environment is mainly Windows servers/users.  SQLite 'works' but is
obviously unsupported (file locking, etc).
Could BedrockDb help in this area?  Sounds like it works 'locally' but
'networkably' (is that a word?!)  Couldn't find any Windows-friendly builds
or guides.
Even assuming it could be set up, it also looks like there aren't .Net
drivers or 'wrappers' for it?


Thanks,
Chris

On Thu, Oct 26, 2017 at 7:15 AM, David Barrett <dbarr...@expensify.com>
wrote:

> I'm glad you liked it!  I'd be happy to answer any questions you have about
> http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> listening!
>
> -david
>
> On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie <jungleboog...@gmail.com>
> wrote:
>
> > Hi All,
> >
> > Pardon the usual interruption of complex sqlite questions...
> >
> > David Barrett was interviewed on Floss Weekly today and gave a rave
> > review of his project, which is based on the wonderful sqlite3
> > database.
> >
> > I'm only 10 minutes into the interview and really love it already!
> > https://twit.tv/shows/floss-weekly/episodes/456
> >
> > Thanks to David for appearing on the show and of course to the Sqlite3
> > team for their amazing efforts to make, and maintain the most widely
> > deployed database engine in the world - maybe even in the galaxy.
> >
> > Thanks,
> > j.b.
> >
> > --
> > ---
> > inum: 883510009027723
> > sip: jungleboo...@sip2sip.info
> > ___
> > 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] Fwd: Problem on Windows 10 machines

[Chris Locke]

I'd suggest running the Microsoft Process Monitor
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon

When your application crashes, this will show the files it tried to access
before the crash.  It might point to a dependancy missing.
Have you 'installed' SQLite on your Win 10 machines?  I use
system.data.sqlite.dll in my applications, and that requires msvcr120.dll.
Without that, I get a weird 'SQLite.Interop.dll module could not be found'
error ... which makes sense, but its not strictly accurate ... its there,
it just can't be loaded.  SQLite requires a couple of extra files to run
properly.  They may not be installed on the Win 10 box.

Ideally, you need a proper stack trace and error log from your application.

> In Windows you get a frowny face "modern icon"

Thats for a full-on Windows 'blue screen', not an application crash.  I
assume this isn't causing a blue-screen, but is just failing.

> Could the problem be that SQLite is installed by MS already on those
machines?

SQLite is a third party product, and would not be pre-installed by
Microsoft.


Thanks,
Chris


On Thu, Sep 7, 2017 at 8:45 AM, Bart Smissaert <bart.smissa...@gmail.com>
wrote:

> Yes, not very helpful. The message is from my VB6 wrapper as is like this:
>
> Method ProcedureX of object _ClassX failed
>
> ClassX is the class in the wrapper ActiveX dll that also has the procedure
> that makes the call to SQLite that causes the problem, in this
> case sqlite3_initialize.
> ProcedureX is another procedure in that same class, but that procedure has
> nil to do with the problem.
> I can take that ProcedureX out and that I will get another procedure
> mentioned in the error message that is again completely unrelated to the
> problem.
>
> So the whole thing is just completely puzzling and I am seriously stuck
> with this.
>
>
> RBS
>
>
>
> On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> >
> > In Windows you get a frowny face "modern icon" (about 5 inches square)
> and
> > "something went wrong, sorry about your luck".
> >
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> >
> > >-Original Message-
> > >From: sqlite-users [mailto:sqlite-users-
> > >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> > >Sent: Wednesday, 6 September, 2017 15:06
> > >To: SQLite mailing list
> > >Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines
> > >
> > >
> > >
> > >On 6 Sep 2017, at 10:03pm, Bart Smissaert <bart.smissa...@gmail.com>
> > >wrote:
> > >
> > >> When my wrapper makes the call to the Sqlite dll my app crashes
> > >
> > >With what error ?  Segmentation fault ?  Privilege violation ?  I
> > >don’t think I’ve seen any crash which doesn’t produce an error report
> > >of some kind, even if we know that there’s no reason for that error
> > >at that point.
> > >
> > >Simon.
> > >___
> > >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