Re: [sqlite] Index rownum

On 8 Jan 2015, at 1:43am, Max Vasilyev wrote: > I guessed that insertion and deletion could be an issue. We need to > re-number keys (change a lot of Btree nodes) on each operation. Or at least > on REINDEX command (I say not strictly, just as idea). If you need to manually

[sqlite] Uncached performance

Hello! Imagine I am trying to introduce a local key-value SQLite database for caching some data retrieved from a remote server. Key is a character string, value is a BLOB (and for 50% keys is just NULL). I will specify the details later. My database weights a little less than 2 Gbs and contains

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

Max Vasilyev wrote: > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > and want to use WHERE, but what if 'title' is not unique? If the ORDER BY columns are not unique, you cannot know which rows to display on which page. You must be able to uniquely identify rows. > - This is considered

Re: [sqlite] Uncached performance

On 8 Jan 2015, at 10:04am, Максим Гумеров wrote: > When I put it on HDD and try to make 1 queries (extracting values for > 1 different keys) with some additional processing of extracted values, > it takes about 4 seconds on my PC on any run except the first, with

Re: [sqlite] Uncached performance

>My database weights a little less than 2 Gbs and contains 130'000 keys. >When I put it on HDD and try to make 1 queries (extracting values for >1 different keys) with some additional processing of extracted >values, it takes about 4 seconds on my PC on any run except the first, >with

Re: [sqlite] Uncached performance

Thanks for your responces! SS> This suggests that you are filling up a cache, especially if your BLOBs are large. In terms of overall time, 1 queries in 2 seconds is 5000 queries a second. If you are using a rotating hard disk then this is not unexpected, given that your disk probably

[sqlite] Should .dump preserve foreign_keys pragma?

Hello. What follows puzzles me. Either there's something I don't understand, or something is wrong. dhcp-179(niall)7: sqlite3 SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a

Re: [sqlite] Should .dump preserve foreign_keys pragma?

On 8 Jan 2015, at 1:38pm, Niall O'Reilly wrote: > I'ld have expected the foreign_keys pragma setting to have been > preserved. That makes sense in terms of how a sensible user would expect SQLite to behave. But unfortunately it's not what SQLite does. See section 2

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

Hi Clemens, 2015-01-08 13:34 GMT+03:00 Clemens Ladisch : > > > http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names > > and yes, we can use title+rowid as lasttitle. But... it looks too complex > > to be 'best

Re: [sqlite] Uncached performance

On Thu, 8 Jan 2015 15:04:28 +0500, ?? ??? wrote: > CREATE TABLE global ( > [key] VARCHAR (1024), > value BLOB, > level INTEGER NOT NULL, > original_name VARCHAR (1024), > id INTEGER PRIMARY KEY AUTOINCREMENT, > parent_id REFERENCES global (id) > ); The order of

Re: [sqlite] Should .dump preserve foreign_keys pragma?

At Thu, 8 Jan 2015 13:46:37 +, Simon Slavin wrote: > > > On 8 Jan 2015, at 1:38pm, Niall O'Reilly wrote: > > > I'ld have expected the foreign_keys pragma setting to have been > > preserved. > > That makes sense in terms of how a sensible user would expect SQLite >

Re: [sqlite] Should .dump preserve foreign_keys pragma?

On Thu, Jan 8, 2015 at 9:05 AM, Niall O'Reilly wrote: > At Thu, 8 Jan 2015 13:46:37 +, > Simon Slavin wrote: > > > > > > On 8 Jan 2015, at 1:38pm, Niall O'Reilly wrote: > > > > > I'ld have expected the foreign_keys pragma setting to have been > >

[sqlite] Support for millisecond

Can SQLite support millisecond precision in date time data? I looking at doc I think so, but it's not clear. Regards, Lance Shipman Product Engineer Esri Redlands, CA USA ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Support for millisecond

> On Jan 8, 2015, at 7:21 PM, Lance Shipman wrote: > > Can SQLite support millisecond precision in date time data? I looking at doc > I think so, but it's not clear. There is no 'date time’ data type in SQLite. Feel free to store your time data as either text or number. To

Re: [sqlite] Support for millisecond

On Thu, Jan 8, 2015 at 7:28 PM, Petite Abeille wrote: > > On Jan 8, 2015, at 7:21 PM, Lance Shipman wrote: > > > > Can SQLite support millisecond precision in date time data? I looking at > doc I think so, but it's not clear. > > There is no 'date

Re: [sqlite] Uncached performance

> SS> Is there a difference between a key being present in your database with > NULL value, and the key not being present ? > Surely there is. But, actually, those 1 queries are made only to keys > with a non-NULL value (usually less than 1 Kb, and maybe 20% are about 16 > kb). *Probably* not

Re: [sqlite] Should .dump preserve foreign_keys pragma?

It is correct. On the chance that you happen to have compiled your version of SQLite with Foreign Key enforcement turned on by default instead of off; or, a later versions decides to change the default to on rather than off; when you load a dump file you need to have that foreign key

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

The table you are creating is called a keyset snapshot. That is how all relational databases databases which support scrollable cursors implement them (only navigable databases -- hierarchical or network or network extended for example) support navigation within the database. Relational

Re: [sqlite] Support for millisecond

You mean iso-8601 strings in the database? Yes, you can format the strings however you want (ie with an unlimited seconds precision). However, the internal datetime function only returns seconds (it is merely an alias for strftime using a format specifier that only outputs seconds), and if

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

Sorry for the slow response -- yes, this is great logic. We're just disabling vacuum. Thanks! -david On Mon, Dec 8, 2014 at 6:18 PM, Simon Slavin wrote: > > On 9 Dec 2014, at 1:36am, David Barrett wrote: > > > *Re: Why VACUUM.* We vacuum

Re: [sqlite] Support for millisecond

On Fri, Jan 9, 2015 at 12:15 AM, Keith Medcalf wrote: > > You mean iso-8601 strings in the database? Yes, you can format the > strings however you want (ie with an ... > ... sqlite> select strftime('%Y-%m-%d %H:%M:%f', '2015-02-14 > 13:46:15.3948573647856354765

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

It is legal and well defined in SQLite. See the explain output below. This is because of the well-documented feature of SQLite that columns that are neither GROUPED BY nor aggregated will have a defined value. First off, it is legal and perfectly normal to test for expressions containing

[sqlite] a couple of crashing bugs from a fuzzer

Hey folks, I have been running afl-fuzz against sqlite and bumped in a bunch of bugs that seem to crash the sqlite3 binary (but do not seem to be a security problem, other than in the denial-of-service sense). There are four seemingly distinct patterns, with test cases included inline: --

[sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

I was wondering if anyone could let me know where I am going wrong. I am getting the error... "Error while executing query: no such column: t1.*B.Switch-Tower-Sector" but, the column, t1.[*B.Switch-Tower-Sector], does exist. I get results when I do select[*B.Switch-Tower-Sector] from t1; but an

Re: [sqlite] Uncached performance

Hi, Maksim, Others with more knowledge than I have given great advice regarding placing your blob as the *last* table column, and about looking at cache size and page size. I wondered about three things: 1) Might there be further performance gains by placing the blobs in a separate table?

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

On 8 Jan 2015, at 11:12pm, MikeSnow wrote: > UPDATE t2 > SET [*B.ANT_ORIENTATION] = > (SELECT t2.ANT_ORIENTATION > FROM t2 > WHERE > t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]); You do not mention a specific row of t1, so it doesn't know what value

Re: [sqlite] a couple of crashing bugs from a fuzzer

On 1/8/15, Michal Zalewski wrote: > I have been running afl-fuzz against sqlite and bumped in a bunch of > bugs that seem to crash the sqlite3 binary Fixed here: https://www.sqlite.org/src/info/fe578863313128 Bug report for your trophy case:

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

On 1/8/2015 6:12 PM, MikeSnow wrote: "Error while executing query: no such column: t1.*B.Switch-Tower-Sector" but, the column, t1.[*B.Switch-Tower-Sector], does exist. I get results when I do select[*B.Switch-Tower-Sector] from t1; but an error when I do the UPDATE statement... UPDATE t2 SET

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

On 2015/01/09 01:12, MikeSnow wrote: I was wondering if anyone could let me know where I am going wrong. I am getting the error... "Error while executing query: no such column: t1.*B.Switch-Tower-Sector" but, the column, t1.[*B.Switch-Tower-Sector], does exist. I get results when I do

Re: [sqlite] a couple of crashing bugs from a fuzzer

Richard, On 8 January 2015 at 17:29, Richard Hipp wrote: > On 1/8/15, Michal Zalewski wrote: > >> I have been running afl-fuzz against sqlite and bumped in a bunch of >> bugs that seem to crash the sqlite3 binary > > Fixed here:

Re: [sqlite] Uncached performance

Hello Donald, DG> 1) Might there be further performance gains by placing the blobs in a DG> separate table? DG> E.g. DG> CREATE TABLE myBlobs ( DG> idINTEGER PRIMARY KEY REFERENCES global (id), DG> value BLOB DG> ); DG> Then (if you haven't rebuilt a new

Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?

On 01/08/2015 07:48 AM, Philip Warner wrote: I just saw the SQLite Android Bindings page at http://www.sqlite.org/android/doc/trunk/www/index.wiki but was a little disappointed to read in the details that UNICODE and LOCALIZED are not supported. I'd really like the latest SQLite, and

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

Maybe you mean (assuming there is not more than one record in t2 for a given SSID-CELLID-SECTOR) UPDATE t1 ... -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com] Gesendet: Freitag, 09. Jänner 2015 00:12 An: sqlite-users@sqlite.org Betreff: [sqlite] Error while