[sqlite] Best page size and cache size in high memory environment

2014-10-06 Thread Paul van Helden
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] Feature Request: Binding Arrays

2013-10-16 Thread Paul van Helden
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 d...@sqlite.org 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

2013-10-16 Thread Paul van Helden
 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


Re: [sqlite] SQLite and integer division 1/2=0

2013-05-13 Thread Paul van Helden
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

2013-05-13 Thread Paul van Helden
 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

2013-05-13 Thread Paul van Helden
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 p...@planetgis.co.zawrote:


 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] SQLite and integer division 1/2=0

2013-05-12 Thread Paul van Helden
On Sun, May 12, 2013 at 1:54 PM, Michael Black mdblac...@yahoo.com 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 slav...@bigfraud.org 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 y...@seiner.com 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] Is there a way to select a precision?

2013-05-06 Thread Paul van Helden


 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?

2013-05-06 Thread Paul van Helden
 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] TRUNCATE TABLE alias for DELETE FROM

2012-10-22 Thread Paul van Helden
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

2012-10-22 Thread Paul van Helden

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

2012-09-23 Thread Paul van Helden
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 bmburst...@gmail.comwrote:

 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


Re: [sqlite] Select rows where a column is not unique

2012-08-06 Thread Paul van Helden
On Mon, Aug 6, 2012 at 2:58 PM, Tilsley, Jerry M.
jmtils...@st-claire.orgwrote:

 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


[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
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

2012-07-03 Thread Paul van Helden
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 yum...@gmail.com 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

2012-07-03 Thread Paul van Helden


   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

2012-07-03 Thread Paul van Helden
On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden p...@planetgis.co.zawrote:


   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

2012-07-03 Thread Paul van Helden
On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS)
michael.bla...@ngc.comwrote:

 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

2012-07-03 Thread Paul van Helden
On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS)
michael.bla...@ngc.comwrote:

 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
columnmynewval for every column in SET to get the actual changes,
something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE insert
complex where clause 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

2012-07-03 Thread Paul van Helden


   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 columnmynewval for every column in SET
  to get the actual changes, something like UPDATE testtable SET col1=?1,
  col2=?2, col3=? WHERE insert 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] sqlite3_bind_blob CHOPS off at first NULL char

2011-04-03 Thread Paul van Helden
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

2011-04-03 Thread Paul van Helden
On Sun, Apr 3, 2011 at 2:46 PM, Lynton Grice lynton.gr...@logosworld.comwrote:

 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

2011-04-03 Thread Paul van Helden
On Sun, Apr 3, 2011 at 3:15 PM, Lynton Grice lynton.gr...@logosworld.comwrote:

  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


Re: [sqlite] Unique index versus unique constraint

2011-03-19 Thread Paul van Helden

 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


[sqlite] Unique index versus unique constraint

2011-03-18 Thread Paul van Helden
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