[sqlite] Unique index versus unique constraint
Hi All, When I create a table and specify a unique constraint, a unique index is automatically created. This index cannot be dropped, so the only way to get rid of the uniqueness is to recreate the table without the constraint. No problem. When I create a table without the unique constraint, I can add the unique requirement later by creating a unique index (if it works!). Apart from behaving the same for INSERTS, this is not exactly the same because I can simply drop the index to remove the unique requirement. So my problem is, I need to know how the unique requirement was created in the first place in order to get rid of it in the appropriate manner. One solution would be to parse the SQL field in sqlite_master to look for the constraint. (I'd prefer not!). I also don't really want to attempt a DROP INDEX and then fall back to recreating the table since I am generating scripts. Another solution that ocurred to me is to check for "sqlite_autoindex_" in the name field of pragma index_list(tablename). Can I simply assume that unique indexes named sqlite_autoindex_* cannot be dropped? Perhaps if pragma table_info(tablename) had a "unique" column like it has a "notnull" column, but only for unique constraints on single fields. Or if pragma index_list had a "constraint" (or "cantdrop" or something) column that would indicate that the index cannot be dropped. Am I missing something? How do you solve this problem? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique index versus unique constraint
> > Automatic indexes have no "SQL" entry in the SQLite_Master table, so you > can use that to see if you can drop it. eg: > > select Name from SQLite_Master where type = 'index' and SQL is null > > will give you the name of all the automatic indexes, which can't be > dropped. > Thanks Tom, It actually makes sense to never drop any automatic index (!). My question should have been how to reliably determine whether an index is automatic or not. Unless someone can come up with a compelling reason not to test for "sqlite_autoindex_" in pragma index_list, I'd prefer to stick to that. Now I'm wondering if in future a situation could arise where the SQL will be provided for automatic indexes... Eg. to aid in exporting DDL. > > Yes, we definitely need more extensive schema introspection facilities in > SQLite. > A simple "autoindex" field in pragma index_list would be a good start :-) > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Lynton, What is the value of msg.num_bytes_in? Is it fsize? And what do you get when you SELECT Length(raw_stream_in) FROM test ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
On Sun, Apr 3, 2011 at 2:46 PM, Lynton Grice wrote: > char* from SQLite? You say that SELECT treats message as TEXT which is > fine, but then how can I get the FULL payload back into a char* so that > I can write it to a file? > > SELECT doesn't treat the BLOB as text, the command line client (and apparently .output) does. > My "proof of concept" goal is to now get that full binary stream back > 100% and write it to a file > > Read the results from the SELECT with a C program. sqlite3_column_bytes > I am getting this currently with your sample file when I do a full LOOP > back test ;-( I must be doing the SELECT wrong somehow? > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
On Sun, Apr 3, 2011 at 3:15 PM, Lynton Grice wrote: > Thanks, issue solved with the following: > > len = sqlite3_column_bytes(stmt,2); > memcpy(msg->raw_stream_in, sqlite3_column_text(stmt, 2), len); > > sqlite3_column_blob is a better function to use. sqlite3_column_text will add a zero character and if your database uses UTF16 encoding will give you an interesting result. Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: ON CONFLICT parser bug?
Hi, I've used the new UPSERT with success with a complex SELECT, then got to scratch my head for a while on a much simpler query, so assuming this might be a bug: CREATE TABLE T2 (A INTEGER PRIMARY KEY); INSERT INTO T2 VALUES (1); CREATE TABLE T1 (A INTEGER PRIMARY KEY); INSERT INTO T1 VALUES (1); INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING; An SQL error has occurred: near "DO": syntax error sqlite3_prepare_v2 actually stops the statement after DO, so that pzTail points to " NOTHING". More complex SELECT statements (seems anything after the table name, e.g. WHERE or LIMIT will suffice) works: INSERT INTO T1 (A) SELECT A FROM T2 WHERE 1=1 ON CONFLICT(A) DO NOTHING I'm sure others have found this already? Regards, Paul. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: ON CONFLICT parser bug?
> > > > A SELECT in an UPSERT should always contain a WHERE clause. Like this: > >INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING; > > Without the WHERE clause, the ON keyword tricks the parser into > thinking it is processing an "ON" join constraint on the FROM clause. > The WHERE clause resolves the ambiguity. The extra no-op WHERE clause > is omitted from the prepared statement by the query optimizer and > hence does not slow down the execution of the statement. > > This is a known limitation of the UPSERT syntax. I had intended to > document it, but I apparently forgot to do so, or at least I cannot > find where I documented it right this second. It is a messy situation > that comes about due to our use of an LALR(1) parser (parsers with > more lookahead also run slower) and by the need to provide full > backwards compatibility with older versions of SQLite. > > Thanks. Easy to live with! The first time I used UPSERT with a SELECT it was a JOIN ending with USING(). Worked like a charm! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] minor documentation typo
Also in partialindex.html: The WHERE clause may *not* contains subqueries should be contain Very happy to have learned about partial indexes today! On Fri, Mar 25, 2016 at 1:32 AM, Wolfgang Enzinger wrote: > minor documentation typo here: > https://www.sqlite.org/partialindex.html > > 3.0 Queries Using Partial Indexes > [...] > Example: Let the index by > > should be > > Example: Let the index be > > Greetings, Wolfgang > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Performance comparison between SQLite and SQL Server?
The quality and helpfulness of this mailing list makes me wish for a SQHeavy...
[sqlite] Mixing text and numeric values in comparisons
Hi All, CREATE TABLE test (A TEXT); INSERT INTO test VALUES ('1'); SELECT * FROM test WHERE A=1; // returns 1 SELECT * FROM test WHERE A=1.0; // returns nothing How is the second comparison done? I realize that you can have double values that are too large to convert to int64, but as I understand, SQLite does convert doubles to integer values for storage when they are small enough. The problem presented itself when binding integers from an Access table with sqlite3_bind_double because the Access table has the integers in a float field. (Apparently common). I had to change my library to check integers in double fields and then bind with sqlite3_bind_int64 in order to hit a result with WHERE. What is the best way to check for integers? (fabs(val)
[sqlite] Mixing text and numeric values in comparisons
Hi Richard, Thank you for the quick reply. I cannot change the type of the column. The tables are user defined and need to be matched with other user defined tables. The values are always integers but can be in any type of column. So I have to detect integers in order to avoid the .0 How do others on this list deal with this problem? Paul. > > What is the best way to check for integers? (fabs(val) modf(val, &intpart)
[sqlite] Mixing text and numeric values in comparisons
On Fri, Mar 4, 2016 at 4:23 PM, Igor Tandetnik wrote: > On 3/4/2016 9:15 AM, Paul van Helden wrote: > >> So I have to detect integers in order to avoid the .0 >> > > WHERE CAST(A as integer) = 1 > > Not quite going to work either. I should have said the values are _nearly_ always integers, but they can be text (alphanumeric). So the general case is to compare anything to anything where there is a high likelihood of values being integers.
[sqlite] Mixing text and numeric values in comparisons
Hi again, CREATE TABLE test (A TEXT); INSERT INTO test VALUES ('1'); SELECT * FROM test WHERE A=1; -- returns 1 SELECT * FROM test WHERE A=1.0; -- returns nothing because the RHS is converted to '1.0' This would not have been a problem if CAST(1.0 AS TEXT) produced '1' (like FloatToStr does in Delphi) If this seems silly, what I'm doing above is simulating a sqlite3_bind_double if you change 1.0 to ? (Integers stored in float fields will never find a match stored in a text field, without casting) However: CREATE TABLE tleft (Txt TEXT); INSERT INTO tleft VALUES ('1'); CREATE TABLE tright (Flt REAL); INSERT INTO tright VALUES (1.0); SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match! Is it because of the following from https://www.sqlite.org/datatype3.html > (As an internal optimization, small floating point values with no > fractional component and stored in columns with REAL affinity are written > to disk as integers in order to take up less space and are automatically > converted back into floating point as the value is read out. This > optimization is completely invisible at the SQL level and can only be > detected by examining the raw bits of the database file.) > Does the cast of Flt to TEXT use the stored integer value in the comparison? I want my query to do the same! I therefore make my case that SELECT '1'=1.0 should return 1, and not 0 as it does at the moment, i.e. CAST(1.0 AS TEXT) must produce '1' Paul.
[sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index
Hi, I use a lot of indexes on fields that typically contain lots of NULLs, so the WHERE NOT NULL partial indexing seems very useful. However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a normal index, SQLite won't use the index to find Max(IndexedField) if it is a partial index. Is this an optimization opportunity? I understand that other kinds of partial indexes might exclude possible Min or Max values, but a NOT NULL index would be fine for mins, maxes and most other things? Regards, Paul. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index
On Mon, May 8, 2017 at 8:20 PM, Scott Robison wrote: > On Mon, May 8, 2017 at 11:40 AM, Paul van Helden > wrote: > > Hi, > > > > I use a lot of indexes on fields that typically contain lots of NULLs, so > > the WHERE NOT NULL partial indexing seems very useful. > > > > However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. > a > > normal index, SQLite won't use the index to find Max(IndexedField) if it > is > > a partial index. > > > > Is this an optimization opportunity? I understand that other kinds of > > partial indexes might exclude possible Min or Max values, but a NOT NULL > > index would be fine for mins, maxes and most other things? > > This may be an optimization opportunity, but you can easily force the > use of that index by stating WHERE NOT NULL in the select query > itself. For example: > > CREATE TABLE a(b); > CREATE INDEX ab on a(b) where b is not null; > > sqlite> explain query plan select max(b) from a; > 0|0|0|SEARCH TABLE a > sqlite> explain query plan select max(b) from a where b is not null; > 0|0|0|SEARCH TABLE a USING COVERING INDEX ab > > > Thanks Scott, That works and fully solves my problem! Regards, Paul. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best page size and cache size in high memory environment
Hi All, My application uses temporary tables that can become quite big (>300MB), so it makes sense to me to change the page size and cache size with pragma statements. The docs say "The default suggested cache size is 2000 pages" and "The normal configuration for SQLite running on workstations is for atomic write to be disabled, for the maximum page size to be set to 65536, for SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the maximum default page size to be set to 8192." So I set my page size to 8192. That means the suggested cache size is still only 16MB. If I increase my cache size to 20 000 pages I see a lot more that 10x increase in memory use and also very slow connection close, presumably due to deallocation of page memory. Should I increase page size instead? What do you guys do when it is OK for SQLite to chew as much memory as you have? Also, does it make sense to change the page size only for the temp database? "pragma temp.page_size=65536" ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
> > > What do you mean, select precision? The double value you pass to > sqlite3_bind_double() will be used as is. Are you saying you want to round > it first? Then go ahead and do that - I'm not sure what that has to do with > SQLite. > -- > It is an issue with SQLite because the values in NUMBER(10,2) have no effect. Too often I see small values with 15 digits in a table because a double was passed as-is. It is not just about space, it is also about presentation. In engineering we are taught that the number of digits should also tell you the accuracy of the sample, so for example a latitude/longitude obtained from a handheld GPS should be stored with 6 decimal digits (~10cm), the rest is just junk. Since the database does not do this for you, when the programmer knows the accuracy of the sample, he shouldn't be lazy and instead do Round(Longitude*100)/100 before binding. Of course, when the data is presented it should be properly rounded with zeros added at the end or even zeros replacing digits to the left of the decimal (to indicate precision), but my point is you shouldn't store junk digits in the first place. I love it that you don't have to specify TEXT and NUMBER lengths, but would have preferred that SQLite didn't ignore them when specified and that in a NUMBER(p,s) column, the double is stored as an integer internally if p<=18. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that > SQLite will attempt to store (string) values as integers first and floats > second before giving up and storing strings. > This has nothing to do with my reply and I understand how it works. > > You do realize that there are decimal numbers that have infinite binary > expansions? > I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know they are the same in SQLite). > > You are also talking presentation (as in formatting) of numeric values as > opposed to representation (as in storing/retrieving). The former is best > handled in the user interface while the latter is the subject of database > engines. > My point was about not storing binary junk - the part of a number that has no meaning because the accuracy of the inputs is limited. When you have a generic db manager that can show any table or if you are looking at the results of your own SQL statement, it helps to reduce clutter on the screen. The data also compresses better. > > Fatihful reproduction of formatting would be possible using TEXT affinity > and calling sqlite3_bind_text. Performing arithmetic with these "numbers" > would however be tricky, slow and would still not guarantee that calculated > values would conform to the desired formatting. > > Of course, but in most cases we don't need to go this far. My main point is about rounding before binding; my secondary point that scale in a column definition can be desirable to avoid it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and integer division 1/2=0
Hi All, I will probably get little support on this here, but I think it is worthwhile documenting my complaint. SELECT 1/2 returns 0 which to me is a little odd, but I see from this page: http://www.sqlite.org/sqllogictest/wiki?name=Differences+Between+Engines that most other engines do the same. (In my opinion MySQL does it right: 1/2=0.5; 1 div 2=0). So be it, but it can really trip you up in SQLite if you have a table create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC); insert into numtypes values (1, 2, 25.23); insert into numtypes values (1.0, 2, 27.17); insert into numtypes values (1.1, 2, 22.92); select A, typeof(A), B, typeof(B), A/B*C from numtypes; A,typeof(A),B,typeof(B),A/B*C 1,integer,2,integer,0,0 1,integer,2,integer,0,0 1.1,real,2,integer,0.12.606 Yes, I have discovered create table numtypes (A REAL, B REAL, C REAL) solves the problem. It just seems illogical to me that numbers are silently converted to integer and then integer division is done on those. A client of mine just got very bad answers from a simple calculation because some rows had integers and others real. It is not intuitive that a NUMERIC column would mix integer and float division. If you don't know this, as I'm sure most regular users don't, it can really burn you. I don't mind the conversion to integer, but then 1/2 should be 0.5. On my wishlist: PRAGMA INTEGER_DIVISION = off; I would use it all the time. Yes "feature creep" I can hear you type. :-) Best regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
On Sun, May 12, 2013 at 1:54 PM, Michael Black wrote: > PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you > would not have known to turn it on (default would have to be OFF for > backwards compatibility). I will use it on every connection I make in future to avoid future pain. (I have a SQLite management system where my clients can create their own tables and enter their own SQL for custom reports) On Sun, May 12, 2013 at 2:35 PM, Simon Slavin wrote: > > I think it's endemic to computers, the same as the difficulty with > counting in units of 0.1 until you get 1. > > On Sun, May 12, 2013 at 2:59 PM, Yan Seiner wrote: > If you want floats, you have to specify floats. If you want integers, you > have to specify integers. The compiler has no way to know which you want. > > Just get in the habit of always adding a .0 if you want float constants. > > I can live with SELECT 1/2 vs SELECT 1.0/2. The problem is that there is no way to specify a float when you insert into a NUMERIC. 1.0 turns into an integer. Then you do a division on all rows with an SQL select and you get mixed results because some rows have floats and some rows have integers. In C, 1/2=0. In Pascal 1/2=0.5. Oracle/MSSQL/others act like C, MySQL acts like Pascal. This is not my main issue. Consistency throughout a table, is. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
Tim, Simon & Darren, if you read my whole OP you will see that I've discovered this: use REAL instead. My point is that the behaviour of a NUMERIC column is not intuitive and gives mixed results which wouldn't be a problem if the division operator could be modified. My suggestion cannot be too outlandish if MySQL does it "my way". Simon says: "The PRAGMAs allow SQLite to switch between different behaviours when the standard doesn't say what should happen". I would venture to say perhaps the standard wasn't too clear on this, or at the very least the fact that MySQL does it differently means there is a bit of a smudge on this part. Darren says: "declaring NUMERIC types is saying you don't care about the behavior". I do care about behaviour, so I'll change my management system to exclude NUMERIC as an option since I have no use for it then! I cannot expect my clients to know little quirks to this level of detail. I agree with what Darren says about the option of having 2 operators, / and div, that's what MySQL does and it is also a feature of Pascal and other languages. Please don't get me wrong. I haven't used MySQL for new projects in years, so I'm not promoting it in any way. Also, if NUMERIC wasn't so ubiquitous in the SQL world, I wouldn't even have raised the issue. If I am correct in taking away from this discussion "don't use NUMERIC column definitions if you want to do any calculations [with divisions] on them", then we can let it rest now. I'll dream of seeing NUMERIC(p,s) one day that enforces (p,s) (and doesn't do integer division unless s=0 !) :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
> I should have asked you for (1,2,20) as well and we could see whether it > outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC > means FLOAT. > > Of course it does! All the others too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
Actually, to be more accurate, the internal storage may be far from a float (as in IEEE double) but a divide on an integer-looking value will certainly be done with floating point math. On Mon, May 13, 2013 at 6:13 PM, Paul van Helden wrote: > > I should have asked you for (1,2,20) as well and we could see whether it >> outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC >> means FLOAT. >> >> Of course it does! All the others too. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: Binding Arrays
Fantastic! I've been wanting this for a long time. Since which version do we have sqlite3_intarray_x? On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp wrote: > Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: Binding Arrays
> Since version 3.6.21, circa 2009-12-07. Note however that this capability > is not built in. It is an extension that you need to compile and link > separately. > > OK... Herewith my vote to make it standard then, like SQLITE_ENABLE_COLUMN_METADATA was enabled for the precompiled binary at some point. I prefer to stick to the precompiled binaries. Besides, it would make the sqlite3_intarray functions more visible in the documentation, etc. I'm sure I'm not the only one that didn't know about this very useful functionality. It's about time the binary got slightly bigger ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
Hi, Is this correct? Should update triggers not only fire for actual changes? I have a large table with a column which contains all NULL values except for 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, except it fires for every row. Thanks, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
You are right, sorry, just checked. sqlite3_changes returns number of records hit, not changed. Have been using sqlite for 2 years now and was always under the impression this was for actual changes. But which is better behaviour, reporting "row hits" versus real changes? Especially when it comes to triggers? On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy wrote: > Paul van Helden wrote: > > Is this correct? Should update triggers not only fire for actual > changes? I > > have a large table with a column which contains all NULL values except > for > > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, > > except it fires for every row. > > I'm pretty sure that sqlite3_changes() in this case also returns *all* > rows, not > only 4 "really" changed. If you want triggers to only fire for really > changed > rows (and sqlite3_changes() to return only those 4 rows), you should add > WHERE > clause: > UPDATE table SET column=NULL WHERE column IS NOT NULL; > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
> > > The statement "UPDATE table SET column=NULL" updates every row in the > table. The fact that some rows may already have a NULL in that > column is not important. > > Well, it is important to me, the word "change" means before != after :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden wrote: > >> The statement "UPDATE table SET column=NULL" updates every row in the >> table. The fact that some rows may already have a NULL in that >> column is not important. >> >> Well, it is important to me, the word "change" means before != after :-) > Just checked MySQL: UPDATE testtable SET testrow=NULL; Affected rows: 40 UPDATE testtable SET testrow=NULL; Affected rows: 0 That is what I'm familiar with. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS) wrote: > What's better is that it tells you what you asked for...not what you think > you asked for...which it does. > I asked for changes :-) > > You've already been shown the correct solution...a WHERE clause... > > I've done that even before posting here, just thought it odd. > You want sqlite to do a complete record compare, including following > update triggers, on EVERY record it looks at to see if something happened > to change??? > Just the fields in the SET clause. > > Yuck... > > As compare to the WHERE clause which does exactly what you want and runs > oodles faster (in all likelihood). > > I always keep an eye on the "affected rows" to see what my statements have done (whether I used a WHERE or not). Even with a WHERE, I would prefer seeing actual changes :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) wrote: > And Oracle says the opposite: > > Yet they all give the same answer when done with "update testtable set > testrow=null where testrow not null; > > You keep hammering this one, it is obvious, I understand, THANKS! What if the SET and WHERE contain many columns? Now I have to add a "WHERE column<>mynewval" for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE AND col1<>?1 AND col2<>?2 AND col3<>?3. (passing a null parameter to the above won't even work!) > > Connected to: > Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit > Production > With the Partitioning, Oracle Label Security, OLAP, Data Mining, > Oracle Database Vault and Real Application Testing options > SQL> create table testtable(testrow number); > Table created. > SQL> insert into testtable values(NULL); > 1 row created. > SQL> insert into testtable values(NULL); > 1 row created. > SQL> insert into testtable values(NULL); > 1 row created. > SQL> update testtable set testrow=null; > 3 rows updated. > SQL> update testtable set testrow=null; > 3 rows updated. > No surprises there. Oracle has never managed to impress me. > SQL> update testtable set testrow=null where testrow is not null; > > 0 rows updated. > > So rather than holding your breath for Oracle to change I'd recommend you > do it the portable way. > > I'm not waiting for anything. My last question was simple: which is better? Since MySQL does it the "correct way" perhaps we can just think about this for sqlite4? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
> > > Then why do you keep hammering on the idea that SQLite is somehow > incorrect or wrong? > > You've explained what you're trying to do. We've explained there is > a better way to do that, that also happens to provide the correct > answer on all platforms, AND likely runs faster-- especially if any > of those columns has an index on them. > I gave a simple example. I work with generic cases. My application doesn't have all static SQL. A lot is from the user or built dynamically. > > > What if the SET and WHERE contain many columns? > > Then you're asking for a more complex operation. Your SQL gets a bit > more complex as well. > > > Now I have to add a "WHERE column<>mynewval" for every column in SET > > to get the actual changes, something like UPDATE testtable SET col1=?1, > > col2=?2, col3=? WHERE complex where clause > > AND col1<>?1 AND col2<>?2 AND col3<>?3. > > > (passing a null parameter to the above won't even work!) > > Well, no, it won't, because you're using the wrong operator. > > Use "WHERE col1 IS NOT ?1 AND..." and it all works fine. > > OK thanks, so I should always use IS NOT where I always used <>. Oh well (talk about yuck!) > > No surprises there. Oracle has never managed to impress me. > > I know what you mean. That MySQL database they make is difficult to > take seriously. > > Very funny. They didn't make it, they own it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select rows where a column is not unique
On Mon, Aug 6, 2012 at 2:58 PM, Tilsley, Jerry M. wrote: > Guys, > > I'm sure this is a pretty lame question, but my thinking hat is > malfunctioning this morning. How can I select all rows from a table where > a specific column is NOT UNIQUE? Table has three columns (charge_code, > mnemonic, description). > > SELECT * FROM tablename WHERE charge_code IN (SELECT charge_code FROM tablename GROUP BY description HAVING Count(*)>1) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unofficial poll
I am using this feature a lot. My applications log all changes to the database, SQL and parameters. So I have an attached log.db with a field for the SQL and then 32 typeless columns for the parameters. Works like a charm! On Sun, Sep 23, 2012 at 12:37 PM, Baruch Burstein wrote: > I am curious about the usefulness of sqlite's "unique" type handling, and > so would like to know if anyone has ever actually found any practical use > for it/used it in some project? I am referring to the typeless handling, > e.g. storing strings in integer columns etc., not to the non-truncating > system e.g. storing any size number or any length string (which is > obviously very useful in many cases). > Has anyone ever actually taken advantage of this feature? In what case? > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRUNCATE TABLE alias for DELETE FROM
Hi, TRUNCATE TABLE is now in the SQL:2008 standard. http://en.wikipedia.org/wiki/Truncate_(SQL) It would make portability easier if SQLite understood TRUNCATE TABLE to be the same as DELETE FROM without WHERE. Yes? No? Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
> > It would be possible to implement TRUNCATE TABLE on top of that, but > this would be only syntactic sugar. > ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit silly to make an exception for SQLite if the solution is that simple and won't break anything. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
"In-process" describes it best for me. On Wed, Jan 29, 2020 at 8:16 AM Darren Duncan wrote: > The concepts I like the best so far are "in-process" or "integrated" or > something library-themed. -- Darren Duncan > > On 2020-01-27 2:18 p.m., Richard Hipp wrote: > > For many years I have described SQLite as being "serverless", as a way > > to distinguish it from the more traditional client/server design of > > RDBMSes. "Serverless" seemed like the natural term to use, as it > > seems to mean "without a server". > > > > But more recently, "serverless" has become a popular buzz-word that > > means "managed by my hosting provider rather than by me." Many > > readers have internalized this new marketing-driven meaning for > > "serverless" and are hence confused when they see my claim that > > "SQLite is serverless". > > > > How can I fix this? What alternative word can I use in place of > > "serverless" to mean "without a server"? > > > > Note that "in-process" and "embedded" are not adequate substitutes for > > "serverless". An RDBMS might be in-process or embedded but still be > > running a server in a separate thread. In fact, that is how most > > embedded RDBMSes other than SQLite work, if I am not much mistaken. > > > > When I say "serverless" I mean that the application invokes a > > function, that function performs some task on behalf of the > > application, then the function returns, *and that is all*. No threads > > are left over, running in the background to do housekeeping. The > > function does send messages to some other thread or process. The > > function does not have an event loop. The function does not have its > > own stack. The function (with its subfunctions) does all the work > > itself, using the callers stack, then returns control to the caller. > > > > So what do I call this, if I can no longer use the word "serverless" > > without confusing people? > > > > "no-server"? > > "sans-server"? > > "stackless"? > > "non-client/server"? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more efficient JSON encoding: idle musing
> > > I experimented with a number of similar ideas for storing JSON when I > was first designing the JSON components for SQLite. I was never able > to find anything that was as fast or as compact as just storing the > original JSON text. > I've also done a lot of experiments and was surprised at how little a binary encoding saves in space. Also tried with lookups for keys, but the lookup values quickly become close to the size of the keys (if not larger) if keys are mostly shortish. I'd be happy with a JSON5-like ability to have the quotes on keys optional if they contain no spaces and no special characters. Seems to reduce the data size quite significantly. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users