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] 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 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

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

[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] 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 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] 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.
> --
> 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

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] 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] 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] 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  wrote:

>
> > On Mar 29, 2018, at 12:06 PM, Mike Clark 
> 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  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  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] 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


Virus-free.
www.avast.com

<#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] 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"  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

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  wrote:

>
>
> On 4 Dec 2017, at 9:31am, Tilak Vijayeta  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"  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  wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith  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 
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 
> 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 
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  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 
> > >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

Re: [sqlite] Comparing rows

[Chris Locke]

> I prefer using the PortableApps SQLite browser for other things as you
can have tabbed SQL queries but it doesn't have the DATETIME data type

Date and Time *Datatype*. *SQLite* does not have a storage *class* set
aside for storing dates and/or times. Instead, the built-in Date And Time
Functions of *SQLite *are capable of storing dates and times as TEXT, REAL,
or INTEGER values: TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").

DB4S (DB Browser for SQLite - the 'real' name for SQLite Browser) will
accept anything as a data type, not necessarily whats in the drop down list.

Drop me an email - I can show you how to create an application in
SharpDevelop using VB.Net if you required.  What you want could be
accomplished with 'naked' SQLite, but a proper 'script' (or application)
would give you much more control, reporting, etc, etc.

PS: DB4S is on v3.10 now, so if you're using the 'portable version', it
needs updating! ;)


Thanks,
Chris


On Tue, Aug 22, 2017 at 9:32 AM, Matthew Halliday 
wrote:

> Hi Clemens - thanks for the reply.
>
> I'm trying to keep them a regular 4  or 6 hours - I'll see what works
> best.  The script runs as a scheduled task.
>
> I used the SQLite Studio to create the table so used the DATETIME data type
> for that, and although I used -mm-dd hh:mm:ss in the script it seems to
> have reverted it to dd/mm/yy hh:mm:ss.   I prefer using the PortableApps
> SQLite browser for other things as you can have tabbed SQL queries but it
> doesn't have the DATETIME data type.  if I use "where date_time =
> date('now','-1 day')"  for example, that seems to work ok.
>
> I have mailing list emails going back a few years to when I used to use
> SQLite a lot but couldn't find anything in them for this.
>
> On Tue, Aug 22, 2017 at 9:20 AM, Clemens Ladisch 
> wrote:
>
> > Matthew Halliday wrote:
> > > I have a simple import table: id, servername, drive, capacity, used_mb,
> > > free_mb, free_pc (%) and a date_time field.
> >
> > What is the format of the values in the date_time field?
> >
> > Is there always a constant offset between two consecutive timestamps?
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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 problem with opening database

[Chris Locke]

This is actually answered on the system.data.sqlite download page.
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Scroll down to the section, "Using Native Library Pre-Loading".
For some reason (?) on Chrome the text is about 30pt, so you shouldn't be
able to miss it.
Essentially, it says to have the DLLs in the right directory, underneath
the application.

\App.exe (optional, managed-only application executable assembly)
\App.dll (optional, managed-only application library assembly)
\System.Data.SQLite.dll (required, managed-only core assembly)
\System.Data.SQLite.Linq.dll (optional, managed-only LINQ assembly)
\System.Data.SQLite.EF6.dll (optional, managed-only EF6 assembly)
\x86\SQLite.Interop.dll (required, x86 native interop assembly)
\x64\SQLite.Interop.dll (required, x64 native interop assembly)

Some SQL command you're firing off requires the use of the DLL, and some of
your clients are set up correctly (or have that DLL on their system) while
others don't.  Its an easy fix.


Thanks,
Chris

On Thu, Jul 6, 2017 at 1:55 PM, Paul Sanderson  wrote:

> Could your 32 bit app be picking up a 64 bit dll. Could you rename the
> dll's and hard code the location into your library?
>
> May not work for your release code but may help you narrow down the issue.
>
> Paul
>
>
> On Wed, 5 Jul 2017 at 18:19, Simon Slavin  wrote:
>
> >
> >
> > On 5 Jul 2017, at 1:41pm, Gregor Pavuna  wrote:
> >
> > > As it seems there's some sort of server problem(Windows 2012 server).
> My
> > guess is server is caching 64bit files and serving them to 32bit
> operating
> > systems. I tested on my test server with laptop (32bit windows 7) and it
> > works fine. Than i went to client and connected laptop there and it
> didn't
> > work with their files.
> >
> > You are keeping your application on a server ?  Or a library ?  Does the
> > problem go away if you keep your application and libraries on the client
> > computer instead ?
> >
> > > I also googled that out, but couldn't find anything on that topic. Any
> > suggestions?
> >
> > Whatever the problem, it seems that it’s related to the Windows
> > application loading system, not SQLite.  So you might find another forum
> is
> > able to help you better than this one.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> --
> 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
> ___
> 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] Could not load file or assembly error

[Chris Locke]

I'd recommend the system.data.sqite.dll wrapper
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

This is a .Net component which works very well with SQLite databases.There
are a plethora of downloads, which can seem daunting.  Depending on your
.Net flavour you're working with (I assume 4.5.1) I recommend the '
*sqlite-netFx451-binary-Win32-2013-1.0.105.2.zip*' download.

If you're new to SQLite, I've written a number of SQLite classes which can
be useful building a DB application... a wrapper around the wrapper, so to
speak. ;)


Thanks,
Chris


On Thu, Jun 22, 2017 at 6:50 PM, Paul J. McMillan, Sr. 
wrote:

> Hi,
>
>
>
> I'm new to SQLite.  I want to use it in my C# application.  I downloaded
> the
> tool SQLite/SQL Server Compact Toolbox.  Under Data Connections, I keep
> getting the error message in red: "Could not load file or assembly
> 'SQLiteScripting".  Does anyone have an idea of why I'm getting this error?
>
>
>
> Thanks
>
> Paul McMillan
>
> ___
> 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 on insert

[Chris Locke]

insert into filters (absid, filter_name, enabled, filter_order) values
(null, 'Untitled filter', 0, ((select max(filter_order) from filters)+1)

On Mon, Jun 19, 2017 at 11:46 AM, Tim Streater  wrote:

> I want to insert a new row in my table, and while doing so setting a
> column to one more than the maximum value of that column, thus:
>
>insert into filters (absid, filter_name, enabled, filter_order) values
> (null, 'Untitled filter', 0, max(filter_order)+1)
>
> However I get "Error: no such column: filter_order”. I had a look at the
> syntax diagram for insert which would seem to permit the above.
>
> I can do this in code anyway so it’s not a show stopper, but what have I
> done incorrectly? My IDE appears to use sqlite 3.14.1.
>
> Thanks.
>
>
> --
> Cheers  --  Tim
> ___
> 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] Catching run-away queries

[Chris Locke]

Excellent, thanks - I'll pass that on.
Very much appreciated.


Thanks,
Chris

On Thu, May 4, 2017 at 4:52 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 5/4/17, Chris Locke <sql...@chrisjlocke.co.uk> wrote:
> > If sqlite is given a slightly misconfigured SQL statement (eg, incorrect
> > JOIN statements), it could potentially try and retrieve millions of rows.
> > (Question taken from here:
> > https://github.com/sqlitebrowser/sqlitebrowser/issues/1005)
> >
> > Is there any way of hooking into this before the actual records are
> > retrieved, or a way of (cleanly) terminating a query after a period of
> time?
>
> Can you get the application to use the sqlite3_interrupt()
> (https://www.sqlite.org/c3ref/interrupt.html) interface to stop the
> long-running query?
>
> --
> 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] Catching run-away queries

[Chris Locke]

If sqlite is given a slightly misconfigured SQL statement (eg, incorrect
JOIN statements), it could potentially try and retrieve millions of rows.
(Question taken from here:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1005)

Is there any way of hooking into this before the actual records are
retrieved, or a way of (cleanly) terminating a query after a period of time?


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

Re: [sqlite] Couldn't load a sqlite.dll

[Chris Locke]

Obviously the information given is a bit sparse.  I'm assuming from a .dll
you're using Windows?
What steps have you taken, and what is giving this error?


On Thu, May 4, 2017 at 11:54 AM, prabha karan 
wrote:

> Dear All,
>  I got this exception except my system ... Pls help me
> ___
> 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 + Dropbox

[Chris Locke]

Another 'sharing solution' which is just getting off the ground is dbhub.io,
(https://dbhub.io)
This is a 'github for sqlite databases' allowing you to share databases,
have version control, etc.


Thanks,
Chris


On Fri, Apr 7, 2017 at 6:22 PM, Warren Young  wrote:

> On Apr 7, 2017, at 11:04 AM, Simon Slavin  wrote:
> >
> > On 7 Apr 2017, at 5:55pm, Donald Griggs  wrote:
> >
> >> Dropbox claims that it performs difference determination, even on binary
> >> files.
> >
> > Interesting.  That would definitely decrease bandwidth used by large
> SQLite databases.
>
> Dropbox is also smart enough to broadcast changes between machines on a
> LAN sharing the same Dropbox account, so the changes don’t have to go up to
> the cloud and then be sync’d back down to the other clients of that account
> on the same LAN.
>
> None of that solve the core problems you identified which make Dropbox a
> poor choice for sharing a SQLite DB over the Internet.
>
> These projects look like a better way to go:
>
> https://github.com/alixaxel/ArrestDB
> https://github.com/olsonpm/sqlite-to-rest
> https://www.dreamfactory.com/
>
> All were found with a web search for “sqlite rest”.  I haven’t used any of
> them; they just look useful.
>
> One could also just use one of the many client-server DBMSes.
> ___
> 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] Reporting Solutions that work with SQLite / VB 2015?

[Chris Locke]

Have you tried exporting to Excel? They're are even components (relatively
cheap) to convert to PDF so both bases are covered.
Exporting to excel allows columns to be used so figures line up nicely.
Open source applications open excel files.



Thanks,
Chris

On 24 Mar 2017 8:03 p.m., "James K. Lowden" 
wrote:

> On Fri, 24 Mar 2017 19:29:07 + (UTC)
> Craig Bisgeier  wrote:
>
> > I am wondering if anyone can recommend a free or low-cost reporting
> > solution that will work with SQLite and be compatible with Visual
> > Basic 2015 as an embedded or callable application?  This is a project
> > that will never make a lot of money so cost is an object.
>
> Although it uses tools rarely seen in VB environments, I recently wrote
> a utility to produce simple reports.
>
> https://github.com/jklowden/sqlrpt
>
> Numeric columns are aligned on the decimal point and formatted with the
> thousands separator consistent with the locale.  Wide text columns are
> formatted to fit nicely on the page.
>
> Groff is available as a Windows binary.  As a typesetting system,
> it's very different from Crystal Reports.  But it's certainly capable
> of producing anything you're likely to need.
>
> --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] RIGHT JOIN! still not supported?

[Chris Locke]

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have
accessories.

If the requirement is to get a result listing all people irrespective of
whether or not they own a pet and information about any pets they own that
also have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a
derived table that can be left joined to.

On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange  wrote:

> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of theoretical
> or design considerations, but it just never came into my flow of thought
> when writing SQL...
>
> I guess some automated SQL query generators could use it though, because
> they do not have a "flow of thought".
>
> On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
> wrote:
>
> > What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries are
> > more natural to write using the first rather than the second?
> >
> > While I can understand arguments based on simple mirror parity, eg we
> have
> > < so we should have > too, lots of other operations don't have mirror
> > syntax either.
> >
> > -- Darren Duncan
> >
> > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
> >
> >> Seeing how SQLite was created in 2000, it seems like nobody really
> >> needed this feature for the last 17 years enough in order to actually
> >> implement it.
> >>
> >> Last I heard, patches are welcome on this mailing list. Don't keep us
> >> waiting.
> >>
> >> Kind regards,
> >> Daniel
> >>
> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
> >> wrote:
> >>
> >>> i got this
> >>>
> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> >>> Unable to execute statement
> >>>
> >>> still today in 21 ts century?
> >>>
> >>> Lenz McKAY Gerardo (PICCORO)
> >>> http://qgqlochekone.blogspot.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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RIGHT JOIN! still not supported?

[Chris Locke]

Sqlite is public domain, so feel free to add the necessary code, and once
approved, it'll get added to the main code.


Thanks,
Chris

On 20 Mar 2017 8:09 p.m., "PICCORO McKAY Lenz" 
wrote:

> i got this
>
> Query Error: RIGHT and FULL OUTER JOINs are not currently supported
> Unable to execute statement
>
> still today in 21 ts century?
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Chris Locke]

Just add a 'comments' table.  Seems a lot of extra work and 'extra tools'
needed to read the comments, which could potentially be missed.
Add a 'comments' table with a 'comment' field which you can even add dates,
usernames, etc, to.

Thanks,
Chris

On Wed, Mar 15, 2017 at 11:12 AM, Clemens Ladisch 
wrote:

> PICCORO McKAY Lenz wrote:
> > an important feature in a DB its the column field that gives to
> developers
> > metadata info INDEPENDENT of the tecnologies used, due by this way with a
> > simple text editor in generated script developer can read and use minimal
> > info for understanding structure ...
>
> There is no widely accepted standard for comments in SQL, except /* actual
> comments */, and neither is there one for metadata, except as actual data
> in your own metadata table(s).  Adding some non-standard mechanism would
> not allow anything that isn't already possible.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Chris Locke]

From a newbie's point of view, how is this better (if doing it in 'hard
coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key,
value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the
example given was just an example, but whats the advantage of parametized
queries?

Sorry if diverting the topic somewhat


Thanks,
Chris

I

On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson 
wrote:

> To answer my own question:  this works:
>
> using (SQLiteCommand command = m_conn.CreateCommand())
> {
> command.CommandType = CommandType.Text;
> command.CommandText = "INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES (?, ?, ?)";
> SQLiteParameter param;
> param = new SQLiteParameter();
> param.Value = 2;
> command.Parameters.Add(param);
> param = new SQLiteParameter();
> param.Value = 234.56;
> command.Parameters.Add(param);
> param = new SQLiteParameter();
> param.Value = DateTime.Now;
> command.Parameters.Add(param);
> rowsAffected = command.ExecuteNonQuery();
> }
>
> RobR
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rob Richardson
> Sent: Monday, March 13, 2017 2:23 PM
> To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> sqlite.org)
> Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
> Hello again.
>
> Since my attempt to find the official answer for myself has hit a snag,
> I'll just ask here.
>
> The examples I've seen for parameterized queries used with the
> SQLiteCommand class have shown named parameters, and the names usually
> begin with an "@" character.  Is that character required for named
> parameters?  Is that the correct leading character?  Is it required to
> include that leading character in the name given to the SQLiteParameter
> object?
>
> I'm used to using the System.Data.ODBC classes, which do not support named
> parameters, but they do support unnamed parameters, represented by question
> marks.  The order in which the parameters are attached to the command
> object determines the association between the parameter object and the
> query parameter.  Unnamed parameters would be easier for me to work with
> than named ones.  Does SQlite.Net support unnamed parameters?
>
> Thank you.
>
> RobR
>
>
> ___
> 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] confused getting started

[Chris Locke]

NT- I write a lot of vb.net programs that use sqlite databases, so will be
happy to run though a beginners guide.
It would be painless to convert from vb.net to c#

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

Re: [sqlite] SQLite3.dll for x64

[Chris Locke]

Or user error.

"Thanks. Also found out where I was going wrong. While creating the .lib
file, weshould be using the following command: lib /def:sqlite3.def
/machine:X64 /out:sqlite3.lib I was skipping the /machine:X64 option
before."

On Mon, Mar 6, 2017 at 10:48 AM, Anick Saha  wrote:

> Hi,
>
> Please look into this issue:
> http://stackoverflow.com/questions/42623284/sqlite3-dll-for-x64
>
> My guess would be that the download link might be directing to the wrong
> set of files.
>
> Thanks,
> Anick
> ___
> 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] Retrieve INTEGER PRIMARY KEY

[Chris Locke]

Why do you say 'there is no equivalence' ?
Have you read the link I posted in the reply to your question nearly 3 days
ago?

Last_insert_rowid()

https://www.sqlite.org/c3ref/last_insert_rowid.html

select @@identity and 'select last_insert_rowid()' perform the same action
- retrieving the last unique row reference.  It was the answer to your
query.  The link provides further research.


On Mon, Feb 6, 2017 at 1:19 PM, Clyde Eisenbeis <cte...@gmail.com> wrote:

> In this case, there is only one record added ... no one else has
> access to this database.
>
> In the past, I have locked a record, so no one else can access that
> record while it is being modified.  Is locking an option in SQLite?
>
> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>
> On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <h...@scigames.at> wrote:
> > But only if you can guarantee that your statement inserts exactly one
> record and that nothing is executed on your connection between the insert
> and the call.
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Chris Locke
> > Gesendet: Freitag, 03. Februar 2017 15:41
> > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> > Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
> >
> > Last_insert_rowid()
> >
> > https://www.sqlite.org/c3ref/last_insert_rowid.html
> >
> > On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <cte...@gmail.com>
> wrote:
> >
> >> For OLE DB SQL, I have retrieved the primary key:
> >>
> >> -
> >>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
> >> System.Data.OleDb.OleDbConnection(stConnectString))
> >>   {
> >> using (System.Data.OleDb.OleDbCommand oledbCmd =
> >> oledbConnect.CreateCommand())
> >> {
> >>   ...
> >>   oledbCmd.ExecuteNonQuery();
> >>   //Retrieve the ID
> >>   oledbCmd.CommandText = "Select @@Identity";
> >>   int iKeyID = (int)oledbCmd.ExecuteScalar();
> >>   stKeyID = iKeyID.ToString();
> >> -
> >>
> >> What is the correct nomenclature for SQLite?
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> >  Gunter Hick
> > Software Engineer
> > Scientific Games International GmbH
> > FN 157284 a, HG Wien
> > Klitschgasse 2-4, A-1130 Vienna, Austria
> > Tel: +43 1 80100 0
> > E-Mail: h...@scigames.at
> >
> > This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> 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] Retrieve INTEGER PRIMARY KEY

[Chris Locke]

Last_insert_rowid()

https://www.sqlite.org/c3ref/last_insert_rowid.html

On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis  wrote:

> For OLE DB SQL, I have retrieved the primary key:
>
> -
>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
> System.Data.OleDb.OleDbConnection(stConnectString))
>   {
> using (System.Data.OleDb.OleDbCommand oledbCmd =
> oledbConnect.CreateCommand())
> {
>   ...
>   oledbCmd.ExecuteNonQuery();
>   //Retrieve the ID
>   oledbCmd.CommandText = "Select @@Identity";
>   int iKeyID = (int)oledbCmd.ExecuteScalar();
>   stKeyID = iKeyID.ToString();
> -
>
> What is the correct nomenclature for SQLite?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem

[Chris Locke]

Ken,

That went to the mailing list ... to which you are also a recipient of
;)


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

Re: [sqlite] Datatype for prices (1,500)

[Chris Locke]

PHP will easily display a value with trailing zeros - you don't add '00'
programmatically.

eg:   $number = number_format(1234, 2, '.', '');

On Thu, Dec 1, 2016 at 8:08 AM, Werner Kleiner 
wrote:

> As I can see storing prices is a topic with different ways and
> different solutions.
>
> The advice to store prices in Cent or Integer:
> Yes you can do: but how will you sore hundredth cents amounts or tenth
> cent prices?
> I have prices like 0,0020 or 0,0008 Euro
>
> I think I have to manipulate the prices for viewing in the app with PHP.
> Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4)
> stores a price 1.500 from a textfield exact so.
> If you want to show the price again in the app, there is nothing to do.
> But switching to SQLite the price is viewed as 1.5 (and stored)
> I know this is no error of SQLite. But I have now to differ between
> Sqlite and MySQL and have to optimize the SELECT and adding 00
> programmatically to view correct if using Sqlite.
>
> My original post was if there is a way for both DBs with same
> behavior, but it seems not.
>
> Thanks to all for help.
>
> Werner
>
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Chris Locke]

I recently had this problem. Values stored as real values. Had to check
records in the database to see if any value had changed, and needed
updating. Even though all values in my code were singles, I had bad
rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
database. Aargh.
Storing as integers is the way to go.

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

Re: [sqlite] Encryption

[Chris Locke]

Ulrich- a fantastically detailed post.

On Mon, Nov 14, 2016 at 3:23 PM, Ulrich Telle  wrote:

> Richard,
>
> > Well what I've done is to create an encrypted database with
> > SQLite2009 and then use that in my C# project. I just add the password to
> > the connection string in my app and then it works right away.
>
> According to the information on the SQLite2009 website (
> http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption
> methods:
>
> ** Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and
> SQLite3 ADO.NET Provider (RSA-MS Crypt) **
>
> I don't know SQLite2009 from own experience, but I assume that it allows
> you to choose which enryption method to use, when creating a new database.
> To be compatible with the ADO.NET provider System.Data.SQLite (
> http://system.data.sqlite.org) you obviously have to choose the
> corresponding encryption method in SQLite2009.
>
> > As far as
> > I understand (I'm new to all this) you can also create a database from
> > within your app if it's based on system.data.sqlite.
> > I just can't edit the table columns or add new ones in SQLite2009 once
> > the database is saved or reopened, I can only edit the record data. I
> > just read that once created, you can't (or only very limited) edit the
> > columns of an SQLite database, you have to create a new database with
> > the desired structure and copy the data over.
>
> I have a bit the impression that you mixed up the terms 'database' and
> 'table'. A 'database' can contain several tables, and adding new tables or
> removing existing tables is simple (and should be supported by any SQLite
> administration tool). However, changing the structure of an existing table
> in SQLite is more complicated, since SQLite only supports a limited set of
> operations to modify a table definition. Therefore, if you want to add or
> remove columns from a table definition, you usually have to create a new
> table with a different name, copy the data from the previous table to the
> new one, remove the previous table, and rename the new table to the
> previous name.
>
> > I think that's what DB Browser for SQLite does since you actually can
> > reopen and edit the columns and their parameters etc. with it.
>
> Under the hood DB Browser for SQLite performs the above mentioned steps
> for you.
>
> > It can also create encrypted databases but the encryption scheme it uses
> > is not supported by system.data.sqlite it seems.
>
> Correct. DB Browser for SQLite supports SQLCipher (
> https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.
>
> > But all in all it works fine, I'm just a bit concerned with the RSA
> > encryption in system.data.lite as I've heard it's slow and easy to
> > crack, so I'd prefer something else.
>
> The RSA encryption offered by System.Data.SQLite should not be used, if
> security is a concern for you. You should prefer an AES encryption scheme.
>
> > SQLITE Expert looks interesting but I wonder if it also uses the
> > built-in RSA encryption in system.data.sqlite?
>
> According to the description on the website (http://www.sqliteexpert.com/
> features.html) SQLiteExpert "Supports password protected databases
> (requires third party SQLite library - not included)." That is, you have to
> provide a SQLite3 DLL supporting the encryption scheme of your choice. For
> example,
>
> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build
> the DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows
> binaries are provided
>
> Other SQLite3 management tools that support the wxSQLite3 encryption
> scheme are
>
> - SQLite Maestro (https://www.sqlmaestro.com/en/products/sqlite/maestro/
> about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)
>
> > > Richard Andersen wrote:
> > > In DB Browser for SQlite I can edit the table but I'm not sure if the
> > > SQLCipher encryption used here can be made to work with
> > > System.Data.SQlite, or how to do if it can. Does anyone know anything
> > > about this?
>
> In principle, it should be possible to replace the SQLite encryption
> implementation in System.Data.SQLite by the SQLCipher or wxSQLite3
> implementation, although it might not be trivial. The latter should be
> easier to accomplish, since the wxSQLite3 encryption implementation is
> self-contained, while SQLCipher requires the OpenSSL library as well.
>
> Regards,
>
> Ulrich
> ___
> 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] Encryption

[Chris Locke]

Encryption in system.data.sqlite is legacy encryption, only used within
itself, and not with other applications.

On Fri, Nov 11, 2016 at 6:24 PM, Richard Andersen  wrote:

>
>
> I'm using the ADO.NET version (System.Data.SQlite).
>
> I've created an RSA encrypted database using SQLite2009 and that is
> working fine, but I can't find any tools for editing the table in
> SQLite2009 once it's been created. Is it possible at all?
>
> In DB Browser for SQlite I can edit the table but I'm not sure if the
> SQLCipher encryption used here can be made to work with
> System.Data.SQlite, or how to do if it can. Does anyone know anything
> about this?
>
> thanks,
> Richard
>
> ___
> 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] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Chris Locke]

But be careful, as you can't change all records from 3 to 4 and then 4 to
5, as the 4 to 5 will contain the records you've just moved from 3 to 4
Canofworms.jpg. ;)

Thanks,
Chris

On 15 Oct 2016 5:46 p.m., "Richard Damon"  wrote:

> On 10/15/16 12:15 PM, Simon Slavin wrote:
>
>> On 14 Oct 2016, at 2:29pm, Thom Wharton 
>> wrote:
>>
>> I want to be able to programmatically insert a new record anywhere in
>>> that table. Let's suppose I want to create a new record between the records
>>> whose ID are 2 and 3.  This new record would need to take the ID of 3, and
>>> all subsequent records would need to have their primary keys updated.
>>>
>>> Is there a way to do this automagically (like a specialized INSERT
>>> command?) in Sqlite?
>>>
>> It's a silly thing to want to do since it makes a nonsense of what IDs
>> are for.  I think you need to rethink what you're trying to do by changing
>> existing IDs.
>>
>> However, if you really need to do it ...
>>
>> UPDATE MyTable SET ID = ID + 1 WHERE ID >= 3;
>> INSERT INTO MyTable ...
>>
>> Simon.
>>
>> And, if any other table refers to records in that table via that ID
> field, THEY need to be changed too, all in an 'atomic' transaction.
>
>
> --
> Richard Damon
>
> ___
> 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] Database malformed after 6000 Inserts?

[Chris Locke]

Are you accessing the database across a network, or is local on the server?
Are you using a transaction, or are these individual inserts?
I'm using system.data.sqlite.dll for a file backup program and that happily
rattles through 800,000 inserts (continuously) without issues.
Its worth noting that v103 was recently released, so the v89 you're using
is fairly old.  Thats not the cause of the problem, but it may not be
helping.

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

Re: [sqlite] Convert mysql to sqlite

[Chris Locke]

What OS are you using?  There is a freeware utility here for Windows:
http://sqlite2009pro.azurewebsites.net/


Thanks,
Chris

On Sat, Sep 10, 2016 at 10:24 PM, Scott Doctor 
wrote:

> I have a database with a few tables and about 140MB of data in it that
> exists as a MySQL database. Using MySQL workbench I can export the data,
> but it dumps as a SQL dump with its flavor of sql. I want to convert this
> database into a sqlite database. Nothing fancy in it. No triggers,
> procedures, or foreign keys. Tables are normal structure with a pk field
> and some text and integer fields in each table.
>
> Anyone know of a utility to directly convert from MySQL to sqlite?
>
>
> --
>
> -
> Scott Doctor
> sc...@scottdoctor.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] Query time execution difference between my application and SQLiteBrowser

[Chris Locke]

>First of all, I'll check all the pragmas and stuff, plus the version of
> SQliteBrowser (former DB Browser for SQlite indeed) I'm using, etc.

Just to confirm (as it seems to be overlooked) that SQLite Browser
(actually now called DB Browser for SQLite, rather than formerly...) is a
3rd party tool.
I can post your query to their issues board though ... its still in high
development, and its curious the version number irregularities...
https://github.com/sqlitebrowser/sqlitebrowser/issues


Thanks,
Chris


On Wed, Sep 7, 2016 at 4:04 PM, Simon Slavin  wrote:

>
> On 7 Sep 2016, at 3:48pm, Laura BERGOENS 
> wrote:
>
> > This query takes 100 seconds approx.
>
> Once your tables have some convincing data in (does not need to be final
> data, just something useful to see how the values are distributed), run
> "ANALYZE", just once.  It might speed up later SELECTs.  It might not.  But
> a situation where you have many 'AND' clauses looking at different columns
> is exactly what ANALYZE is most helpful for.
>
> And yes, a 50 kilorow table is not big by SQLite standards.  I have tables
> with a thousand times that that yield answers to SELECT in 5ms.
>
> 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] "Responsive" website revamp at www.sqlite.org

[Chris Locke]

Makes perfect sense.  Thanks.


Chris

On Tue, Sep 6, 2016 at 12:26 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 9/6/16, Chris Locke <ch...@chrisjlocke.co.uk> wrote:
> > When reducing the size of the browser (on Chrome desktop at least) the
> '***
> > DRAFT ***' tag disappears.  Rather than disappears, maybe this should be
> > reduced in size and always be visible?
> > Edit: Noticed its right at the bottom of the page, regardless of size,
> but
> > not at the top.  Is this by design?
>
> By design.  There simply is not space.
>
> Note that when not in draft mode, the "*** DRAFT ***" text becomes the
> tag-line:  "Small. Fast. Reliable.  Choose any three!"  There is no
> way to fit that on a 320-pixel wide screen, together with the logo,
> and make it readable.  It is not essential information so it is
> elided.
>
> > Additionally, on the menu, the 'Download' option is not visible at all.
> >
>
> By design.  There is nothing on the Download page that is useful to a
> mobile device.  So links to that page are omitted on mobile, to save
> precious pixels.
>
> --
> 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] "Responsive" website revamp at www.sqlite.org

[Chris Locke]

When reducing the size of the browser (on Chrome desktop at least) the '***
DRAFT ***' tag disappears.  Rather than disappears, maybe this should be
reduced in size and always be visible?
Edit: Noticed its right at the bottom of the page, regardless of size, but
not at the top.  Is this by design?
Additionally, on the menu, the 'Download' option is not visible at all.


Thanks,
Chris


On Mon, Sep 5, 2016 at 10:56 PM, Delvin 
wrote:

> The site seems to be quite readable - I did notice that the text appears
> larger in landscape mode but it seems to appear quite readable in portrait
> mode (I viewed it with an iPhone 4S and an iPod Touch.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Darren Duncan
> Sent: Monday, 5 September, 2016 16:28
> To: SQLite mailing list 
> Subject: Re: [sqlite] "Responsive" website revamp at www.sqlite.org
>
> On 2016-09-05 1:55 PM, Richard Hipp wrote:
> > Most of the world views the internet on their phone now, I am told,
> > and websites are suppose to be "responsive", meaning that they
> > reformat themselves to be attractive and useful for the majority who
> > view them through a 320x480 pixel soda-straw.  In an effort to conform
> > to this trend, I have made some changes to the *draft* SQLite website
> > (http://sqlite.org/draft) Your feedback on these changes is
> > appreciated.  Please be sure to try out the new design both on a
> > narrow-screen phone and on a traditional desktop browser.  The goal is
> > to provide a more mobile-friendly website without reducing the
> > information content available to desktop users.
>
> Superficially the altered site looks like an improvement.  It uses the
> simple and standard "viewport" declaration to achieve the low-hanging
> fruit.  Text is readable on my phone as with my computer at its default
> size while loading.
> Site still seems navigatable.  I didn't go very far though. -- Darren
> Duncan
>
> ___
> 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] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

Thanks Ryan.  I guess I'm used to MS Access (can I mention those words in
this mailing list?) which recognised the 'table name.field name' convention
on all columns regardless.  It made things 'lazy' I guess.  If a table has
20 fields, then it can be a pain listing out every field required.  A
necessary evil, and 'the right thing', but still a pain...

Thanks,
Chris


On Fri, Jul 22, 2016 at 11:33 AM, R Smith <rsm...@rsweb.co.za> wrote:

>
>
> On 2016/07/21 11:20 PM, Chris Locke wrote:
>
>> I've a table I'm calling recursively.
>>
>> ...
>>
>> I know I can change my SQL statement to be explicit, and select each
>> required field and use AS, but is that the only solution?
>>
>
> It's not so much the "Only" way as it is the "Correct" way. Query planners
> between different engines all pop different values into the column name bit
> returned according to what uses the least CPU cycles (but perhaps still
> indicate the nature of the column) - which is the way we all want it. Once
> you actually NEED the names to be specifically something (as in your case)
> then the SQL standard provides for that by specifying that the Query engine
> *must* return an exact column name where you specify an "AS" clause for a
> selected column.
>
> Hence me saying that this is not so much the /only/ way as it is the
> /correct/ way to ensure your column names are ALWAYS returned exactly as
> you asked for it.
>
> As to your question about it being the Only way? - No - another way would
> be to use a different SQL engine that returns something that you like
> better, or an older version of SQLite when it behaved differently, or put
> your query in a CTE with named columns - but these ways are all silly for
> obvious reasons. Do the "AS" thing - it's how the the rest of us roll... :)
>
> 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

Re: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

Thank you for your prompt reply.  Thats no big issue then and I will code
it explicitly ... didn't want to do that if there was an option / some
other method I was missing.

Thanks,
Chris

On Fri, Jul 22, 2016 at 6:55 AM, Hick Gunter <h...@scigames.at> wrote:

> Short answer: YES.
>
> This question crops up regulary.
>
> The SQL Standard mandates only that column names set with AS be reliably
> returned. Otherwise each implementation is free to choose whatever name it
> deems appropriate, because - by omitting the AS clause - you state that you
> "don't care". The column name may be the unqualified or qualified field
> name or even the text of the expression. It may also change between
> executions of the same query, usually because the "shape" of your data has
> changed enough to make the query planner choose a different sequence of
> joins.
>
> Since your programming environment does seem to care about column names,
> you will have to set them explicitly.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:
> sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Chris Locke
> Gesendet: Donnerstag, 21. Juli 2016 23:20
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with
> column names
>
> I've a table I'm calling recursively.
>
> CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT,
> `surname` TEXT, `departmentId` TEXT, `managerId` TEXT,
> `holidayDaysEntitlement` INTEGER, `holidayDaysTaken` REAL, PRIMARY
> KEY(`id`) )
>
> managerId points to the same table, so my join is simply:
>
> select * from staff
> inner join departments on staff.departmentId=departments.id inner join
> staff as managers on staff.managerId=managers.id
>
> This works fine using DB Browser for SQLite, and adding WHERE clauses like
> '
> managers.id='1' ' etc.  (apologies for wrapping that in quotes...)
>
> Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are
> returned.  Using this statement doesn't work:
> dim s1 as string=sqlReader("managers.firstname").ToString
>
> Retrieving the 12th field, gives me just 'firstname'
>  MsgBox(sqlReader.GetName(11))
>
> I know I can change my SQL statement to be explicit, and select each
> required field and use AS, but is that the only solution?
>
>
> Thanks,
> Chris
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: Your message to sqlite-users awaits moderator approval

[Chris Locke]

Bit of a noob question, but whenever I post to the group, I get the below
email - 'you're not part of the group'.  How does one join the group?  I've
subscribed, so get all the emails... just wondering if there was a second
step, or if all group messages get moderated.

Thanks,
Chris


On Wed, Jul 13, 2016 at 1:57 PM, <
sqlite-users-boun...@mailinglists.sqlite.org> wrote:

> Your mail to 'sqlite-users' with the subject
>
> Re: [sqlite] Possible index corruption
>
> Is being held until the list moderator can review it for approval.
>
> The reason it is being held:
>
> Post by non-member to a members-only list
>
> Either the message will get posted to the list, or you will receive
> notification of the moderator's decision.  If you would like to cancel
> this posting, please visit the following URL:
>
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/confirm/sqlite-users/e9805c75143435e371bcbe62c1c3294eda77ce87
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

I've a table I'm calling recursively.

CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT,
`surname` TEXT, `departmentId` TEXT, `managerId` TEXT,
`holidayDaysEntitlement` INTEGER, `holidayDaysTaken` REAL, PRIMARY
KEY(`id`) )

managerId points to the same table, so my join is simply:

select * from staff
inner join departments on staff.departmentId=departments.id
inner join staff as managers on staff.managerId=managers.id

This works fine using DB Browser for SQLite, and adding WHERE clauses like '
managers.id='1' ' etc.  (apologies for wrapping that in quotes...)

Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are
returned.  Using this statement doesn't work:
dim s1 as string=sqlReader("managers.firstname").ToString

Retrieving the 12th field, gives me just 'firstname'
 MsgBox(sqlReader.GetName(11))

I know I can change my SQL statement to be explicit, and select each
required field and use AS, but is that the only solution?


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

Re: [sqlite] Possible index corruption

[Chris Locke]

*everyone rushes to download the files to have a nose*

On Wed, Jul 13, 2016 at 12:02 PM, Richard Hipp  wrote:

> Off-list reply
>
> On 7/13/16, Miroslav Rajcic  wrote:
> >
> > Note that I had to delete other tables to protect customer info.
>
> You did not seem to enable "PRAGMA secure_delete" before deleting the
> other tables.  Hence, much of the content is still in the file, on the
> freelist.
>
> You might want to take down your two sample database files.
>
> --
> 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] Query question: order by ascending, return the two largest values in ascending order

[Chris Locke]

Whats the benefit of getting a sorted query and then sorting that query
again?

On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski 
wrote:

> Simons + My answer;
>
> select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp
> DESC LIMIT 2) a order by date_time_stamp;
>
> On Mon, Jul 11, 2016 at 7:33 PM, Simon Slavin 
> wrote:
>
> >
> > On 12 Jul 2016, at 12:25am, Keith Christian 
> > wrote:
> >
> > > A table has a column of dates and times that look like this:
> > >
> > > 2015-10-02 07:55:02
> > > 2015-10-02 07:55:02
> > > 2015-10-02 10:00:03
> > > 2015-10-02 10:05:02
> > > 2015-10-02 10:10:02
> > >
> > >
> > > Schema:
> > > CREATE TABLE general ( id integer primary key autoincrement, server
> > > text, date_time_stamp text);
> > >
> > >
> > > Would like to get the latest two dates and times, kept in ascending
> > > order, e.g. the query should return these two values:
> > >
> > > 2015-10-02 10:05:02
> > > 2015-10-02 10:10:02
> >
> > SELECT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2
> >
> > The only difference is that the rows will always be in the reverse order
> > to what you asked for: biggest timestamp first.  But since it's
> consistent
> > that shouldn't be a problem.
> >
> > I recommend you create an index on the date_time_stamp column, since that
> > will make the above query work far faster.
> >
> > 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] Question about C# with SQLite

[Chris Locke]

Possible solution from StackOverflow:
Try setting the Build Action property of the source DLLs to None

On Tue, Jul 5, 2016 at 1:18 PM, Shouwei Li <cas...@gmail.com> wrote:

> Hi, guys,
>
> Thank you very much for your reply.
>
> Just make a conclusion, I followed the solution provided in this link, and
> my program could be run on any PC now.
> https://rashimuddin.wordpress.com/tag/sqlite-interop-dll/
>
> But another issue comes out now, I always get this warning when build the
> whole solution.
>
> 1>C:\Program Files
> (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targets(3506,5):
> warning MSB3178: Assembly 'SQLite\System.Data.SQLite.dll' is incorrectly
> specified as a file.
>
> It not affect the final release program, but I don't know how it comes from
> and how to eliminate it.
>
> Thank you!
>
>
>
> On Tue, Jul 5, 2016 at 9:37 AM, Chris Locke <ch...@chrisjlocke.co.uk>
> wrote:
>
> > You might need something like Microsofts Process Monitor.
> >
> >
> https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255=-2147217396
> >
> > I was getting similar 'red herrings' when deploying my .exe and
> > system.data.sqlite.dll - it was moaning that it couldn't load the DLL.
> > Indeed it couldn't, as it also needed msvcr100.dll, which wasn't on the
> > users machine.  This only came to light when I used the above program and
> > noticed the error produced when it was looking for the file that wasn't
> > there
> >
> >
> > Chris
> >
> > On Tue, Jul 5, 2016 at 4:59 AM, dandl <da...@andl.org> wrote:
> >
> > > Did you mean: SQLite.Intero.dll or SQLite.Interop.dll?
> > >
> > > Did you put it in the right place?
> > >
> > > Also consider the question of 32 vs 64 bit.
> > >
> > > Did you try Stack Overflow? There are lots of hits over there.
> > >
> > > Regards
> > > David M Bennett FACS
> > >
> > > Andl - A New Database Language - andl.org
> > >
> > >
> > >
> > > > -Original Message-
> > > > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:
> > sqlite-users-
> > > > boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> > > > Sent: Tuesday, 5 July 2016 12:57 PM
> > > > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> > > > Subject: Re: [sqlite] Question about C# with SQLite
> > > >
> > > > if it was built with debug mode; probably the debug runtime doesn't
> > exist
> > > > there.  Otherwise it's because the visual studio runtime required
> isn't
> > > > available.
> > > >
> > > > On Mon, Jul 4, 2016 at 6:52 AM, Shouwei Li <cas...@gmail.com> wrote:
> > > >
> > > > > Hi there,
> > > > >
> > > > > I have a project developed with .net 2015 and C#. I use SQLite as
> the
> > > > > server-less database. It works very well in my workstation. But it
> > can
> > > > > not run on other PC. The error indicates:
> > > > >
> > > > > Unable to load DLL "SQLite.Intero.dll": The specified module could
> > not
> > > > > be found.
> > > > >
> > > > > I already attach this dll with my program.
> > > > >
> > > > > I want to ask is there a manual talk about how to deploy a program
> to
> > > > > customer when we use the SQLite database.
> > > > >
> > > > > Thanks for your reading.
> > > > >
> > > > > --
> > > > > Best Regards!
> > > > > Shouwei Li
> > > > > ___
> > > > > 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
> >
>
>
>
> --
> Best Regards!
> Shouwei Li
> ___
> 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 Logo

[Chris Locke]

Blimey - arguments over a feather,  However, I want to argue about your "They
look nothing alike" and I would say to a non-feather expert, they are very
similar.  Same orientation (upwards, pointing to the right), white middle,
a nick on the right.  They do look alike ... very alike.


Chris

On Tue, Jul 5, 2016 at 8:56 PM, R Smith  wrote:

>
>
> On 2016/07/05 9:30 PM, Zsbán Ambrus wrote:
>
>> On Fri, Jun 24, 2016 at 2:03 PM, R.A. Nagy  wrote:
>>
>>> I am putting together a commercial training for SQLite. I would like to
>>> use
>>> the SQLite logo - as seen on the website - on the cover of the materials.
>>>
>>
>> SQLite has a logo...
>>
>> wow.  I didn't notice that before.  I just checked the homepage
>> "http://sqlite.org/;, and it shows a feather in the corner that looks
>> basically the same as the Apache logo.  Is there a reason why they're
>> so similar?  I mean, they're both for software, so it can be confusing
>> for people who (unlike me) actually pay attention to logos.
>>
>
> They look nothing alike - I mean apart from the fact they both display a
> feather. Apache uses a multi-coloured pen feather with visible
> downs-feather base (the soft fluffy bit near the bottom of the branching
> section) to represent the typical feathers that might adorn the head-band
> of an Apache Indian chief. No doubt the connotation was made to symbolise
> its relative light-ness in a World of heavy Web servers.
>
> SQLite shows an outer-wing section feather of a smaller bird (no downs,
> shorter with wider blade) in more or less the same orientation as the
> Apache feather, but with no colours (just white on blue background) to
> symbolise very specifically the lightness of the product (I imagine).
>
> They both contain upright feathers, but that's where the similarity ends,
> for if it was the intent of the SQLite creators to mimic the Apache logo,
> they did a very poor job of it!
>
>
> It's like saying the Miami Dolphins has the same Logo as MySQL... :)
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about C# with SQLite

[Chris Locke]

You might need something like Microsofts Process Monitor.
https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255=-2147217396

I was getting similar 'red herrings' when deploying my .exe and
system.data.sqlite.dll - it was moaning that it couldn't load the DLL.
Indeed it couldn't, as it also needed msvcr100.dll, which wasn't on the
users machine.  This only came to light when I used the above program and
noticed the error produced when it was looking for the file that wasn't
there


Chris

On Tue, Jul 5, 2016 at 4:59 AM, dandl  wrote:

> Did you mean: SQLite.Intero.dll or SQLite.Interop.dll?
>
> Did you put it in the right place?
>
> Also consider the question of 32 vs 64 bit.
>
> Did you try Stack Overflow? There are lots of hits over there.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
>
> > -Original Message-
> > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> > Sent: Tuesday, 5 July 2016 12:57 PM
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] Question about C# with SQLite
> >
> > if it was built with debug mode; probably the debug runtime doesn't exist
> > there.  Otherwise it's because the visual studio runtime required isn't
> > available.
> >
> > On Mon, Jul 4, 2016 at 6:52 AM, Shouwei Li  wrote:
> >
> > > Hi there,
> > >
> > > I have a project developed with .net 2015 and C#. I use SQLite as the
> > > server-less database. It works very well in my workstation. But it can
> > > not run on other PC. The error indicates:
> > >
> > > Unable to load DLL "SQLite.Intero.dll": The specified module could not
> > > be found.
> > >
> > > I already attach this dll with my program.
> > >
> > > I want to ask is there a manual talk about how to deploy a program to
> > > customer when we use the SQLite database.
> > >
> > > Thanks for your reading.
> > >
> > > --
> > > Best Regards!
> > > Shouwei Li
> > > ___
> > > 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] Correct, best, or generally accepted database structure for groups of things

[Chris Locke]

Thanks James.  Points taken on board.  :-)


Chris

On Fri, Jun 17, 2016 at 5:24 PM, James K. Lowden <jklow...@schemamania.org>
wrote:

> On Fri, 17 Jun 2016 07:37:16 +0100
> Chris Locke <ch...@chrisjlocke.co.uk> wrote:
>
> > I fail to see what any of this has to do with sqlite.  I thought this
> > was a mailing list for sqlite?  Seeing queries (no pun intended) on
> > sql statements is very subjective, especially with the limited data
> > provided by the original poster.
>
> A query question frequently exposes design choices, either logical or
> physical.  Both of those can have great effect on the utility and
> performance of the system.  Answering SQL questions helps people use
> SQLite more effectively, and to understand where it differs from other
> DBMSs.
>
> Queries occasionally provoke changes in SQLite itself, either because
> the output was wrong (or unexpected), or because it presented a case
> for optimization.  I have to believe that real queries from users on
> this list serve to inform the developers in how SQLite is used.  (I
> have never seen a homework question on this list.)
>
> > it won't stop there, and as soon as the original poster has another
> > query
>
> No, it won't, because it hasn't.  I've  been hanging out here for 18
> months, and I remember only one annoying trivial-query participant.
> Out of 13,693 messages, that doesn't amount to much.
>
> --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] Correct, best, or generally accepted database structure for groups of things

[Chris Locke]

As this is a mailing list, I've not been aware of past history.  I thought
(albeit wrongly) that an SQLite group was about the product sqlite.
Someone posted the other day about a car analogy, so this is like posting
to the Ford Engine Forums, askign what air freshner to put in the car.

I don't mind off-topic posts either, but the generic post about 'how do I
write this sql?' was extremely basic.  Half the group will spin off about
third normal forms...

Ooh yes, I'll be back!  I've a very 'basic' knowledge of SQL, so am picking
up bits from the odd post here and there.  I craft a lot of databases in
vb.net but as my needs are small, sqlite eats this up for breakfast...

I'm tempted to set up a beginners SQL forum though... a 'getting started'
guide together with a 'how the &%$£ do I do this?' section...
I prefer forums to email lists... don't feel so 'spammmy' and noisy...


Thanks,
Chris


On Fri, Jun 17, 2016 at 3:16 PM, Drago, William @ CSG - NARDA-MITEQ <
william.dr...@l-3com.com> wrote:

> > -Original Message-
> > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org] On Behalf Of John McKown
> > Sent: Friday, June 17, 2016 9:35 AM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Correct, best, or generally accepted database
> structure
> > for groups of things
> >
> > On Fri, Jun 17, 2016 at 1:37 AM, Chris Locke <ch...@chrisjlocke.co.uk>
> > wrote:
> >
> > > I fail to see what any of this has to do with sqlite.  I thought this
> > > was a mailing list for sqlite?  Seeing queries (no pun intended) on
> > > sql statements is very subjective, especially with the limited data
> > > provided by the original poster.
> > > Everyone will give helpful advice, but it won't stop there, and as
> > > soon as the original poster has another query (no pun intended) which
> > > would result in a schema change, this would have to be explained, etc.
> > >
> > > A specific group on SQL is required.
> > >
> >
> > ​Perhaps so. But such a group would run into problems because it would
> be a
> > case of "whose SQL?" The four "big" ones that I know of are: SQLite,
> > PostgreSQL, Oracle, and IBM's DB2. The basics are the same, but each has
> > their own peculiarities. ​I don't know what the intent of this forum
> really is. It
> > is only for SQLite related "perculiarities"? Or does it include
> something like
> > the OP's question which is basically "how do I do a SQLite query to get
> this
> > information?" I don't really know. I also monitor the PostgreSQL forums
> and
> > see this "how do I craft an SQL query to ...?" type question quite
> often. What
> > is weird to me, is that someone will post such a question on the _bugs_
> > forums, phrasing it as "I did this SQL query and it didn't do what I
> expected.
> > Please fix your product to make it work." And the reason it didn't work
> was
> > because the SQL query is garbage. Ah, the ever requested "do what I need,
> > not what I said" fix.
> >
>
> I don't know what the actual rules are for this group either. Almost any
> database related topic seems to be tolerated if not enthusiastically
> embraced.
> Chris Locke has only been active here since May of this year (and maybe he
> won't be back now that his problem is solved), so maybe he's unaware of
> some of (off) topics that have made the rounds. In any case maybe he has a
> good point in keeping the mailing list strictly on topic. I personally
> don't mind the occasional detour into other realms, especially on a low
> activity group like this one, and I usually learn a thing or two along the
> way as well, but that is just my opinion.
>
> -Bill
>
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any attachments
> for the presence of viruses as L-3 does not accept any liability associated
> with the transmission of this e-mail. If you have received this
> communication in error, please notify the sender by reply e-mail and
> immediately delete this message and any attachments.
> ___
> 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] Correct, best, or generally accepted database structure for groups of things

[Chris Locke]

I fail to see what any of this has to do with sqlite.  I thought this was a
mailing list for sqlite?  Seeing queries (no pun intended) on sql
statements is very subjective, especially with the limited data provided by
the original poster.
Everyone will give helpful advice, but it won't stop there, and as soon as
the original poster has another query (no pun intended) which would result
in a schema change, this would have to be explained, etc.

A specific group on SQL is required.

Just my thoughts...


Chris

On Thu, Jun 16, 2016 at 10:14 PM, Simon Slavin  wrote:

>
> On 16 Jun 2016, at 9:53pm, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
>
> > Should that function insert its results into a table that looks like the
> one below, or is there a better way?
> >
> > CREATE TABLE Groups (
> > ID INTEGER PRIMARY KEY,
> > AppleID1 INTEGER
> > AppleID2 INTEGER
> > AppleID3 INTEGER
> > AppleID4 INTEGER
> > );
>
> You would definitely want each of the four AppleIDs to have a FOREIGN KEY
> reference to the Apple table.
>
> An alternative to your Groups table would be a Membership table:
>
> CREATE TABLE Members (
> AppleID INTEGER,
> GroupID INTEGER,
> FOREIGN KEY (AppleID) REFERENCES Apples(ID)
> );
>
> It is the responsibility of your software to ensure that every GroupID
> appears exactly four times in Members.
>
> This would allow you to create another table, Groups, which stored things
> like the group's colour and total weight.  And this should be a foreign key
> reference for the Members table too.
>
> 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] Update DataGrid and Save to database

[Chris Locke]

Great analogy.  PS: What colour seat covers should I be using if I have a
Ford?

On Mon, Jun 13, 2016 at 2:11 PM, jumper  wrote:

> Thank you for the advice/information. I just solved the issue about a
> minute ago. How can I stop getting new replies?
>
>
> On 6/13/2016 8:08 AM, R Smith wrote:
>
>>
>>
>> On 2016/06/13 2:48 PM, jumper wrote:
>>
>>> New to SQLite and DataGrids. I need to know how to get an adapter, table
>>> and dataset when app first comes up.
>>> Then when someone updates the DataGrid I need to save the changes to the
>>> database.
>>>
>>> I've been working on this for days and can't do it. Could someone please
>>> post the code for how to do it?
>>> Thank you so much,
>>>
>>
>> Hi John,
>>
>> These questions you ask are about creating user-interfaces for databases
>> and adapters in some GUI systems - none of which is remotely related to the
>> inner workings of the SQLite DB engine or answerable on this forum. It's
>> like you want to know where the gas pedal and steering wheel is in your
>> car, and now contacting the Engine manufacturer to ask. We don't really
>> know.
>>
>> I'm assuming the platforms you use may be MSVC, C++ or Delphi or some
>> other GUI type creator - try their forum or community, or perhaps even a
>> google search for a tutorial stating exactly the tools you use.
>>
>>
>> Good luck!
>> 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