Re: [sqlite] Reset the cursor

018 12:15 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Keith, > >On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf >wrote: >> >> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was >compiled with SQLITE_OMIT_AUTORESET you will get an SQ

Re: [sqlite] Reset the cursor

r not thinking about this immediately. > > >On Tue, Jun 5, 2018 at 6:38 AM, Igor Korot >wrote: >> Hi, Keith, >> >> On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf >wrote: >>> >>> Perhaps. In order for the sqlite3_errcode(db) to have any meaning &g

Re: [sqlite] Reset the cursor

test Loop 1, no reset, reset at 5 ! sqlite3_reset returns 0 Loop 2, After Reset ! Loop 3, No Reset, Got SQLITE_DONE ! sqlite3_reset returns 0 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message

Re: [sqlite] Reset the cursor

Most of them. In particular those that return (as in SELECT) data work either way. Those that set things can only be used as a pragma. Note that the table name is passed differently (in the case of pragma's expecting an identifier). It is an identifier in the case of a pragma statement, and

Re: [sqlite] Size of the SQLite library

On Wednesday, 6 June, 2018 10:24, Bob Friesenhahn wrote: > The build strategy for the Python APSW extension is an > example of unwanted dependency and loss of control. > Building of software from source code should always be > under the complete control of the person who is performing > the b

[sqlite] sqlite3BtreeBeginTrans commit breaks sessions modules ...

sqlite3BtreeBeginTrans() now has an extra parameter. sessions do not like this. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mail

Re: [sqlite] Size of the SQLite library

Have you tried the link at the end of every message? --- 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 Dian

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

Just tell wget --no-check-certificate in the command line. wget does not use a certificate repository and you need to obtain and specify the expected root manually. It will be no less secure than it was before (when using HTTP) except that now it will use Transport encryption. Certificate ch

Re: [sqlite] Reset the cursor

fic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot >Sent: Thursday, 7 June, 2018 20:19 >To: SQLite mailing list >Subject: Re: [sqlite] Reset the cursor > >Hi, Keith, > >On Tue, Jun 5

[sqlite] Possible bug with locking/retying

> I think it's a misuse of Sqlite and not a real bug. He adds code to ensure > only one thread access the database in a multithread application. For the > description, I infer he uses -DSQLITE_THREADSAFE=2 (multithread), where a > -DSQLITE_THREADSAFE=1 (serialized) solve the problem better because

[sqlite] What software is deployed more than SQLite?

OpenSSL and/or libeay and derivatives. On every device which does any type of SSL/TLS/encryption/hash functions from pocket calculators, control systems, phones, mini-computers, mainframes, satellites (as in orbiting the earth), submarines, and on and on ... Another candidate is the original

[sqlite] Multiple instances of the same program accessing the same db file

> Hmm, one for doing my own locking, one against it. As this seems to be > an obvious issue in any network, I wonder why the network developers > have not appropriately addressed this issue. They have. In the early 80's when network filesystems were invented they were incredibly slow. So incre

[sqlite] Multiple instances of the same program accessing the same db file

> SMB keeps its cache on the computer which hosts the file. So if a file is > opened locally the cache is on the only computer concerned. If computer A > opens a file on computer B, the file-system cache is on computer B, where > all file requests pass through it. > > Of course a badly written

[sqlite] Multiple instances of the same program accessing the same db file

> > There is no difference between a file opened over the network and one > opened locally, except that in the network open case the network > filesystem does not maintain consistency between the client view of the > file and the server view of that same file, because doing so takes time > and th

[sqlite] SQLite queries

> > Although I can understand the sarcasm you're sending out, a > > client/server infrastructure would be an interesting task for > > SQLite to do, but to answer the OP, no, SQLite isn't inherently > > designed to be a client/server. > ?And not really that difficult. Embed SQLite into some "serv

[sqlite] emptying tables

Are you running all your delete statements within a single transaction, or a separate transaction for each? > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Zaumseil Ren? > Sent: Tuesday,

[sqlite] REGEXP pcre DLL for Windows

Is there any reason why the default REGEXP is unsuitable? ext/misc/regexp.c in the source distribution ... > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of sonypsx > Sent: Monday, 18 May

[sqlite] AUTOINC vs. UUIDs

All relational database engines store configuration data within the users' database. Many of them just hide it behind varying layers of logically imposed complication. For example, you could simulate SQL Servers' obfuscation by simply changing the name of the primary database alias from "main

[sqlite] AUTOINC vs. UUIDs

These things should be called HUID's (Hopefully Unique ID's). HUIDs violate determinism and are therefore a very bad thing. Of course, many people do not require determinism (more often they think they do not require determinism -- support is after all the problem of some other guy) and havin

[sqlite] AUTOINC vs. UUIDs

HUIDs have the birthday problem. How many people do you need to have in a group before two of them will have the same birthday? HUIDs can only be called LUID's (Locally Unique Identifiers) if you go to the trouble of ensuring uniqueness *before* using one. Validating Global uniqueness is, qu

[sqlite] AUTOINC vs. UUIDs

On the other hand, perhaps both tablets implement the same PRNG with the same seed. You will then have the same HUID's generated on both and have the exact same problem. Addressing the problem using Hope and Pray is not a very robust solution. It would be much better to solve the problem usi

[sqlite] AUTOINC vs. UUIDs

Fossil does not use UUID's. Artifact IDs used by fossil are the SHA-1 hash of the file contents, and the checkin IDs are the SHA-1 hash of the check-in manifest contents. They are *NOT* random but rather, are 100% deterministic -- that is if you run the sha-1 hash over the same input data you

[sqlite] RSQLite.extfuns.dll -- Looking for Linux binary extension

It looks like it is designed to "compile right in". You just need to append it to the amalgamation source file, then you just need to pass an appropriate function name in the define SQLITE3_EXTRA_INIT so that whenever a database connection is opened the function is called to perform "extra ini

[sqlite] Under what circumstances can a table be locked when the database is first opened?

1) Something else has the database open and locked. 2) You are using Shared Cache 3) Something forgot to finalize a select 4) The database is stored on a non-locally-attached filesystem 5) An issue in the version of SQLite you are using (and you did not say which version you are using) 6)

[sqlite] Sample SQL code thats beyond me :(

To re-phrase your problem statement: Join the table Users and Perimeter_Notifications using the common email field and return the results as long as there does not exist a Devices record where Holiday_Mode is 1 for that Users email. Which translates directly to: SELECT * FROM Users, Perimete

[sqlite] Sample SQL code thats beyond me :(

columns of the index are irrelevant for this query's performance. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > Sent: Saturday, 23 May, 2015 10

[sqlite] Update with out without WHERE?

The index on the F1 column is irrelevant -- it might not be used unless it is significantly faster to scan than the table itself. On the other hand it will have to be updated for each row in T1 that is updated where the value of F1 is re-written (not necessarily changed). The WHERE clause lim

[sqlite] Update with out without WHERE?

> But SQLite knows not to UPDATE a row when your update changes no values. This is not possible and would make an update operation incredibly slow. What you mean is that a row which is not updated is not updated. A row which is updated, even if you are re-writing the same value, *IS* an update

[sqlite] Possible Bug

The "data type" is associated with the data value itself. Columns do not have Datatypes -- they have affinities. The column affinity is only used when doing certain and specific operations on real tables. Since you cannot do these operations on a view, the column affinity is meaningless and

[sqlite] Simple Math Question

> On Thursday, 22 October, 2015, at 13:45, Rousselot, Richard A > said: > Doing the following math, why is it that the results are not all returning > "yes"? > > SELECT > (9.2+7.9+0+4.0+2.6+1.3), > case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else > "no" en

[sqlite] Simple Math Question

On Saturday, 7 November, 2015 09:08, James K. Lowden wrote: > On Fri, 06 Nov 2015 22:16:57 -0700 > "Keith Medcalf" wrote: > > I wrote a function called "ulps" which can be used as an extension to > > SQLite3 > Bravo, Keith! > One suggestion, i

[sqlite] combining overlapping tables

insert into combinedTable select * from table1 union select * from table2 union select * from table 3; {set} union {set} returns the distinct result {set} union all {set} returns a results that may have duplicate rows > -Original Message- > From: sqlite-users-bounces at mailinglists.sql

[sqlite] Array or set type or some other indexable data?

Having an "array of values" in a single field violates zero'th normal form and therefore cannot be represented nor manipulated efficiently in a algebraically correctly implemented Relational Database. You would need one with non-relational extensions which handle such non-relational data in th

[sqlite] [AGAIN] SQLite on network share

On Friday, 13 November, 2015 12:55 A. Mannini said: > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.ht > ml#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not e

[sqlite] [AGAIN] SQLite on network share

> Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said

[sqlite] [AGAIN] SQLite on network share

> > Why do you think that is a problem? (the x86_64)? > Yes there isn't a x64 Jet version. Or at least, there is the ACE x64 but > can't be installed side-by-side to Office 32 bit. Ah, I see. Microsoft introduces artificial restrictions "because they can". Just like they could have fixed all

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

Windows 10 1511 includes both a 64-bit and 32-bit version of SQLite called: C:\Windows\System32\winsqlite3.dll C:\Windows\SysWow64\winsqlite3.dll for the 64-bit and 32-bit versions respectively. The version of SQLite contained in these libraries is: 2015-02-25 13:29:11 9d6c1880fb75660bbabd6931

[sqlite] sql programming help

You need to join the execution and resource table to be able to get the results you want. The where clause specifies which rows of the cross-product you want returned. select r.name, e.name, e.release from execution e, resource r where r.status like 'Busy (%)' and substr(r.status,7,5) =

[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

> Any column declared as 'INTEGER PRIMARY KEY' is said to be an alias for > the internal 'rowid' column, and this > really-need-to-know/> > proves > that 'rowid' can change after a 'vacuum' statement is issued. Merely > r

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

On Tuesday, 17 November, 2015 20:29, Richard Hipp said: > On 11/17/15, Yuri wrote: > > Don't keep the bag, keep only one integer ID of the first failed > > constraint. > Therein lays the rub: there is no way to tell which (if any) FK > constraint has failed until you have run the operation to

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

Examining the VDBE code generated from some simulated operations explains how it works with stark clarity. There are two constraint violation counters. One has a "statement" scope (for immediate constraints), and the other a "transaction" scope (for deferred constraints). Basically when the

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wednesday, 18 November, 2015 20:36, Nico Williams said: > On Thu, Nov 19, 2015 at 12:39:41AM +, Simon Slavin wrote: > > On 19 Nov 2015, at 12:26am, Nico Williams wrote: > > > two concurrent scans of the same table should be able to go faster > > > than the same two scans in series. > > >

[sqlite] SQLite Extensions

For anyone who is interested, I have compiled for 32-bit Windows all the extensions that are included in the SQLite3 distribution /ext/misc directory using MinGW (gcc 4.8.1) and have added a few others I have written that add useful functions, and even a few that are taken from other people --

[sqlite] SQLite Extensions

new.rowid; end; create trigger data_hash_update after update of x, y on data begin update data set hash = binmd5(x,y) where rowid = new.rowid; end; insert into data values ('Keith', 'Medcalf', null); insert into data values ('Carl', 'Medcalf', null); select x,

[sqlite] SQLite Extensions

t need to unzip the file to a directory, and plop in the sqlite3.h and sqlite3ext.h and run the appropriate CMD file (you may need to make some changes to the GCC file for the path to MinGW ... > On Fri, Nov 20, 2015 at 12:42 AM, Keith Medcalf > wrote: > > > > For anyone w

[sqlite] regular expression in check constraint?

REGEXP is not builtin. You have to compile it in yourself. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of James Hartley > Sent: Tuesday, 24 November, 2015 15:37 > To: General Discussion

[sqlite] FOREIGN KEY enhancement request

That is incorrect. Whether or not the customer "can_discount" is an attribute of the customer. Whether or not a sale "has_discount" applied is an attribute of the sale. They are not the same attribute, do not mean the same things, and are attributes of different tables. Business logic (im

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Would it not be more efficient to say: select 1 from t1 limit 1; ? > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller > Sent: Thursday, 26 November, 2015 15:03 > To: SQLite m

[sqlite] Warnings for non-deterministic queries?

> Is there a way I could programatically determine that a query is non- > deterministic at query prepare time? What do you mean, non-deterministic? The result is deterministic in all cases. It may be complicated and/or difficult for you to compute, but it is always deterministic. The resu

[sqlite] Warnings for non-deterministic queries?

> Indeed you are correct - pardon me not making it clearer - the point was > to demonstrate using an example that would "feel" deterministic and show > that even in that case the arbitrary-pick should be expected or catered > for. Your example further highlights this (and is probably the more > co

[sqlite] Speed of count distinct rows if unique index on all fields

You are making an error. sqlite> create table x(a, b, c, d); sqlite> create unique index y on x(a,b,c,d); sqlite> insert into x values(1,2,3,null); sqlite> insert into x values(1,2,3,null); sqlite> insert into x values(1,2,3,null); sqlite> select * from x; 1|2|3| 1|2|3| 1|2|3| sqlite> select dist

[sqlite] Speed of count distinct rows if unique index on all fields

No, whether the column contains a null is irrelevant. It is whether the column CAN contain a null. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Bart Smissaert > Sent: Friday, 2 Octob

[sqlite] Sqlite good on Windows XP but very very slow on Windows Seven

Have you checked the options that are set for the Hard Drive Controller and Drives? (Particularly the ones that disable OS and hardware cache flushing). Perhaps Windows 7 drivers are doing an fsync when fync is called. 120 ms per transaction is pretty good for a machine that is working properl

[sqlite] Simple Math Question

Because there are differences. Double precision floating point is only accurate to 14 digits and there is a difference in the 15th decimal place. sqlite> SELECT ...> (9.2+7.9+0+4.0+2.6+1.3) - 25.0, ...> (9.2+7.8+0+3.0+1.3+1.7) - 23.0, ...>

[sqlite] Simple Math Question

> Financial software frequently handles all currency amounts as pence or > cents for the reasons you've just found out. Annoys the heck out of > bankers until you have them work problems by hand and seen just how stupid > computers really are. Usually as hundredths (1/100) of a cent -- that is,

[sqlite] Simple Math Question

You are thinking (and typing) in base 10. Computers use this new-fangled thing called binary -- base 2. The "floating point" type is IEEE 754 double precision binary (base 2) floating point. SQLite does not use decimal floating point (IEEE 854-1987) or General Decimal Arithmetic http://spe

[sqlite] Simple Math Question

sqlite> select ieee754(1.7); ieee754(7656119366529843,-52) or ?00011011001100110011001100110011001100110011001100110011 for the mantissa. Note that it is an infinitely repeating fraction. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-

[sqlite] Simple Math Question

IEEE754 is an extension that is located in the ext/misc of the standard distribution. Of course, before equality testing the numbers must be "normalized" so they have the same exponent ... SQLite version 3.9.1 2015-10-22 18:06:40 Enter ".help" for usage hints. Connected to a transient in-memor

[sqlite] Erros using some compiler options for SQLite 3. and V. Studio 2015

Try changing: > #define SQLITE_OMIT_TCL_VARIABLE > #define SQLITE_OMIT_PROGRESS_CALLBACK > #define SQLITE_OMIT_FOREIGN_KEY > #define SQLITE_OMIT_AUTOVACUUM > #define SQLITE_OMIT_EXPLAIN > #define SQLITE_SECURE_DELETE > #define SQLITE_OMIT_BUILTIN_TEST > #define SQLITE_OMIT_TRACE > #define SQLITE_

[sqlite] Question about Style

You really should be binding the data values to the prepared statement, not injecting user values into the SQL statement. Unless of course you go to the extrodinary lenghts requires to sanitize your inputs. https://xkcd.com/327/ You should be doing sqlite_prepare_v2 on a statement of the for

[sqlite] Third test of json and index expressions, now it works

> > Besides being part of the standard (I assume) > You assume incorrectly. In the classic SQL model, aliases to column names > are assigned after the results have been returned. In other words, > aliases cannot be used in the WHERE clause. Or group by clause ... > Simon. > __

[sqlite] MemoryBarrier compile error on WIndows MinGW gcc 4.8.1

Trunk does not compile with MinGW [gcc version 4.8.1 (GCC)] on Windows. sqlite3x.c: In function 'sqlite3MemoryBarrier': sqlite3x.c:20410:17: error: expected expression before ')' token MemoryBarrier(); /* ** Try to provide a memory barrier operation, needed for initialization only. */ SQLITE_

[sqlite] MemoryBarrier compile error on WIndows MinGW gcc 4.8.1

On Sunday, 6 September, 2015 04:32, Richard Hipp said: > On 9/5/15, Keith Medcalf wrote: > > Trunk does not compile with MinGW [gcc version 4.8.1 (GCC)] on Windows. > Please retry after the latest check-in and let us know whether or not > the problem has been resolved. Con

[sqlite] sqlite3 file as database

> > *.SQL appears to be a common thing for not only Structured Query > > Language, but also "Squish message base lastread pointers" -- > Whatever the heck that is. SQUISH was a database format for storing messages in FidoNet systems. Originally designed by Scott Dudley as part of Maximus, the f

[sqlite] sqlite3 file as database

> At 01:07 15/09/2015, you wrote: > >--- > >SQUISH was a database format for storing messages in FidoNet systems. > >--- > > Geez, I don't even recall my FidoNet node number aka address... Time > must have flown by faster than I thought. Hehehehe. Those were the good old days ... trying to get

[sqlite] I don't understand how to use NOT EXISTS

On Monday, 14 September, 2015 21:07, Nicolas J?ger said: > hi, > I have a table TAGS with idkey and two colums (NAME, COUNT): > id|NAME|COUNT > 53|black metal|3 > 55|occult rock|3 > 61|satanic smurfs|1 > 62|beer|0 > 63|pizza|0 > I want to check if some tag exist by checking if `NAME` is reco

[sqlite] I don't understand how to use NOT EXISTS

> @Simon, > > > >CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 > > > > CASE takes a value. You can't put a SQLite command in there. > > > actually, it's working if you put the command between (). I guess it's > like the `` on linux (eg), > > mplayer `ls *.mp3` > > but maybe,even if

[sqlite] I don't understand how to use NOT EXISTS

> Some initial things. > Data needs to be kept in 1NF (or 1st-Normal-Form) which is relational > theory speak for "Everything without a 1-to-1 relationship with the key > field in a table, should be in another table". Many reasons for this > (if you care to read up on some RT) but the most simpl

[sqlite] disable update's & delete's via triggers?

> You can create TRIGGERs which operate BEFORE, AFTER or INSTEAD OF. So > perhaps just create an INSTEAD OF trigger which does nothing. Perhaps a > SELECT command or a DELETE command with a WHERE clause which is never > satisfied (e.g. rowid < 0). The RowID is an integer. It is perfectly possi

[sqlite] disable update's & delete's via triggers?

insert into y (x) values (5); sqlite> select * from y; -100|1 1|2 2|3 4|5 sqlite> > On 20 Sep 2015, at 11:59pm, Keith Medcalf wrote: > > > The RowID is an integer. It is perfectly possible to have RowID's with > a value less than 0. > > > > sqlite> creat

[sqlite] Fwd: Outdated section of docs?

On Thursday, 24 September, 2015 03:36, Dave McKee said: > Hmmm... actually, looks like Windows 10 has regressed back to only having > one set of DST rules... Windows at Vista and later can have an (unlimited) set of DST rules for each Microsoft Time Zone (Microsoft Time Zone's can only be loo

[sqlite] Feature Suggestions

On Thursday, 24 September, 2015 08:53, Allen said: > Suggestions for SQLite features: > 1. It would be nice to have read-only transactions for use with WAL > databases. When a read-only transaction was started, it would take a > "lock" on the WAL and then not advance further into the WAL until

[sqlite] Feature Suggestions

> > WAL does this already. You just need to BEGIN a transaction when you > want it to BEGIN and COMMIT or ROLLBACK when you are done with it. > > I was under the apparently mistaken impression that starting any > transaction would block writes, even in WAL mode. > > Just to be clear, this would

[sqlite] Odd download file names

did you try "fossil ui"? > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski > Sent: Tuesday, 29 September, 2015 10:58 > To: General Discussion of SQLite Database > Subjec

[sqlite] FOREIGN KEY constraint failed

Are we confusing immediate constraints (checked per statement) with DEFERRED constraints (checked at COMMIT time) again? > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of R Smith > Sent: T

[sqlite] SQLITE_IOERR_ACCESS with 3rd party DLL

Does this mean that you are trying to link the LIB with the OTHER COPY of sqlite into the same application tht is using the DLL, because this sure sounds like what you are doing. In which case the versions are NOT independant processes soince they are both linked to the same process, or to bot

[sqlite] Table alias not accepted with "delete/update" ?

UPDATE __table_metadata SET list_table_id = NULL WHERE list_table_id NOT IN (select distinct id from __tables_metadata); You are correct though, aliases of the table being updated are normally only assigned in the FROM clause, not in the UPDATE clause, though many

[sqlite] Working with booleans

Not to mention that 0 is false and 1 is true. If you use 1 and 0, then you can write commands like: select from where isActive; and have it work as you expect. Whereas if isActive contains 'T' or 'F' then the above will not work and you will have to issue the same select as select from

[sqlite] Checks with dates

create table x ( ds text not null check(date(ds) is not null), de text not null check(date(de) is not null), check(ds < de) ); insert into x values ('2016-04-15', '2016-04-13'); insert into x values ('2016-04-15', '2016-04-17'); insert into x values ('2016-04-15', '2016-04-32'); The constra

[sqlite] Is it possible that dropping a big table takes very long

> Have another problem also. My CPU is about 15%, but the load average is > also about 15. (This is on a Linux system.) This results (sometimes) in a > very sluggish system. Can the load be a SQLite problem, or is it a Java > problem? (When the program is not running, the load average is a lot > lo

[sqlite] Is it possible that dropping a big table takes very long

Perfectly linear. The time waster in creating the records is the index with the completely separate copy of all the data and the native primary key (record number) into a duplicate structure (the index btree). Creating the index and the base table at the same time, while linear as well, is ev

[sqlite] Caveat entry

> The best database performance advice I ever got still applies: think > about how the DBMS will traverse the data, and minimize the work it > will do. It's all about I/O. No number of additional threads will > bring the data into memory sooner. The only way to optimize I/O is to not do it.

[sqlite] UPDATE/open/close blocked while executing SELECT

On Monday, 18 April, 2016 08:47 Olivier Mascia wrote: > Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and > not SQLITE_CONFIG_SERIALIZED), is that it? > Then, if using threads in the application, each thread has its own > connection (or multiple connections) but no thread

[sqlite] Primary key values can be NULL

"INTEGER PRIMARY KEY" declares an alias for the rowid. It is always NOT NULL (it is the record number) and it is always and integer (it is not ducky-typed). Specifying NULL for the rowid causes the generation of the "next" rowid/record number. AUTOINCREMENT modifies "next" to mean "one great

[sqlite] Is it possible that dropping a big table takes very long

For me, source_id = 2016-04-18 15:46:14 eba27d4d17a76884292667d570d542e580ee3e77 Windows 10 1511 Pro, i7-3632QM 2.4Ghz, 16 GB Ram, 1 TB 850 Pro SSD with secure_delete=0 either drop table testuniqueuuid; or the sum of both delete from testuniqueuuid; drop table testuniqueuuid; takes under 2 s

[sqlite] BUG?

On Friday, 22 April, 2016 02:24. Stephan Beal said: > On Thu, Apr 21, 2016 at 4:12 PM, jrhgame wrote: > > SELECT julianday('2016-04-15 12:10:10') ==>2457494.00706 > > SELECT datetime(2457494.00706) ==>2016-04-15 12:10:09 > fwiw, i've done lots and lots of testing with round-trip con

[sqlite] No datasize field - why?

> > What you are seeing there is SQLite just repeating back the type that > the CREATE TABLE command used. It is not the type of data SQLite is > actually storing. > > There is no 'varchar' datatype in SQLite. And it never truncates > strings. > > I think that the best thing for you may be to r

[sqlite] No datasize field - why?

> On Sat, 23 Apr 2016 08:56:14 -0400 > "Keith Medcalf" wrote: > > > Those things that those other DBMSes do are holdovers to maintain > > backwards compatibility with the good old days when dinosaurs ruled > > the earth > As amusing as your rant is,

[sqlite] Working with blob

> I don't know a way to write binary data to a file using the > command-line tool. Maybe there's one out there someone else knows of. The SQL functions readfile and writefile mayhaps? They have to be loaded as an extension (or compiled into) to lib/dll if you want them there.

[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used

> I am able to recreate the exception here. The value of Int64.MaxValue is > 9223372036854775807. The UnixEpoch values are measured in seconds from > the epoch 1970-01-01 00:00:00Z. Adding 9223372036854775807 seconds to the > UnixEpoch would result in a DateTime far beyond the allowed maximum va

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

> > But you already have pandas.read_sql_query. While that function > > isn't really what I'd call simple, the complexity afaict -- dates, > > floats, and chunks -- can be laid at Python's feet. > > I use R rather than python but the problem of dates is significant and > I assume the same proble

[sqlite] IS a SQLite db of small size as good as reliable cache?

> > Any insight into what they were thinking? Back when I used Windows > > daily, it used to annoy me that every morning the machine had to warm > > up again, to revive the state I'd left it in the night before. In > > NetBSD I learned that unused memory is unused, so why not use it? The file c

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

> > There is no impedence mismatch. Simply inadequate wattage by the > person(s) solving the problem. As I said, this problem has been solved > with SQLite and Python for a decade. So I would suggest the problem is > that the wattage was so low, the lights were completely out. > The impedence

[sqlite] Bug: Successfully committed transaction rolled back after power failure

Is this on windows? Any errors in the Eventlogs to the tune "Oooopsie -- accidentally threw away your data instead of writing it to disk"? Windows does this quite commonly under some circumstances. MicroSoft created the bug in NT 4 and has been unable to locate or fix it since -- though ther

[sqlite] Customizing the location of the .sqlite_history

> Jes Slow wrote: > > > Many applications do this by allowing the user to set an environment > > variable to customize the location, altho personally I would prefer > > another way since environment variables are also global. > > Global? Environment variables are per-process, and changeable by

[sqlite] IS a SQLite db of small size as good as reliable cache?

You conclusion about the Windows design goals are correct. Hardware Destroyer (power saving) was invented for the same reason (to maximize the rate of hardware failure through imposition of unnecessary thermal and mechanical stresses on all system components, causing early failure and increasin

[sqlite] json_group_array

count(*) counts the rows of the result set selected count(column) counts the NOT NULL values in the column of the result set selected count(DISTINCT column) counts the number of distinct values (excluding NULLs) in the column of the result set selected count(column IS NULL) is equivalent to

[sqlite] Code Cleanup - Variable Definition - malloc.c - sqlite3DbMallocRawNN

Variable Definitions only supported at start of block before code in older (MSVC) compilers. @@ -22479,15 +22479,15 @@ p = sqlite3Malloc(n); sqlite3MemdebugSetType(p, MEMTYPE_HEAP); return p; } SQLITE_PRIVATE void *sqlite3DbMallocRawNN(sqlite3 *db, u64 n){ + LookasideSlot *pBuf; a

[sqlite] What's the reason for alias names not being visible in subqueries?

select * from parent P, parent_child_1_link L, child_1 C where P.id = L.parent_id and C.id = L.child_id and P.child_type = 1 union select * from parent P, parent_child_2_link L, child_2 C where P.id = L.parent_id and C.id = L.child_id and P.child_type = 2 It is a very straightfo

[sqlite] query Benchmark

If and only If you follow rule 6 and declare the column with a collation sequence that matches the collation in effect for the LIKE operator. So if the column/index is declared with CASE SENSITIVE collation (BINARY, the default), and you use like on that column, and the LIKE is case insensitive

<    3   4   5   6   7   8   9   10   11   12   >