Re: [sqlite] Concrete example of corruption

On 12/5/2013 20:31, Stephen Chrzanowski wrote: @Warren> the package/sandbox idea won't work due to certain constraints the OS puts on the file. Quoting the Apple docs[1] Doug pointed to: "...you can access the document's contents using any appropriate file-system routines." I'd like to see

Re: [sqlite] Concrete example of corruption

On 6 Dec 2013, at 3:31am, Stephen Chrzanowski wrote: > It was mentioned in another thread, of this exact subject (I think this is > the third thread?) that the package/sandbox idea won't work due to certain > constraints the OS puts on the file. On one hand, you can't keep

Re: [sqlite] Concrete example of corruption

@Warren> It was mentioned in another thread, of this exact subject (I think this is the third thread?) that the package/sandbox idea won't work due to certain constraints the OS puts on the file. I don't recall what the reason was, exactly, as I've never used a Mac for any kind of considerable

Re: [sqlite] Concrete example of corruption

On Dec 5, 2013, at 8:55 PM, Warren Young wrote: > On 12/5/2013 17:00, Scott Robison wrote: >> Might there be a way to implement a custom VFS for Mac to deal with this? > > Wouldn't it be a lot simpler to just put the DB file into a Mac package (i.e. > directory) so the

[sqlite] JOIN failure in sqlite 3.8.1

Hi, I noticed that some of my views were not working with sqlite 3.8.1. I managed to isolate the problem in the simple test case below. Then I discovered that the 3.8.2 pre-release seems to do it right. I suspect it was fixed along with https://www.sqlite.org/src/tktview?name=c620261b5b but I

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

On 12/5/2013 8:39 PM, Hayden Livingston wrote: If I have my data structured like Col1 "FooId", "Value1", "Value2", "Value3" I can do group by (Col1) and show this data on a graph. Imagine Col1 is the timestamp field, I could split it into hour groups and show rates of

Re: [sqlite] Concrete example of corruption

On 12/5/2013 17:00, Scott Robison wrote: Might there be a way to implement a custom VFS for Mac to deal with this? Wouldn't it be a lot simpler to just put the DB file into a Mac package (i.e. directory) so the associated WAL and whatever other files get created in the package, too?

Re: [sqlite] Concrete example of corruption

I wasn't trying to suggest it be added to an official VFS shipped with SQLite. Just pondering if there might be a way for people who want or need such functionality to integrate it cleanly via the VFS mechanism. On Thu, Dec 5, 2013 at 5:24 PM, Simon Slavin wrote: > > On 6

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

If I have my data structured like Col1 "FooId", "Value1", "Value2", "Value3" I can do group by (Col1) and show this data on a graph. Imagine Col1 is the timestamp field, I could split it into hour groups and show rates of somethings. Like orders per hour rate. On Thu, Dec 5, 2013

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

On 12/5/2013 14:45, Klaas V wrote: Warren wrote 4 dec 2013: | There are tiny corners of the programming world (FP) where this is not the case, but then you get into | questions of purity, and databases are about as far from side-effect-free as you can get. That's a wee bit exaggerated,

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

On 12/5/2013 7:16 PM, Hayden Livingston wrote: I suppose I should have stated my goal even further. I'm putting them in sql so that I can query it row by row (i.e. operationId by operationId), as opposed to SELECT all operationIds, then foreach operation id select all rows where operationid =

Re: [sqlite] Concrete example of corruption

On 6 Dec 2013, at 12:00am, Scott Robison wrote: > Might there be a way to implement a custom VFS for Mac to deal with this? One problem is that to be able to call fsevents you have to link in a huge amount of the standard Mac support some of which isn't accessible

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

I suppose I should have stated my goal even further. I'm putting them in sql so that I can query it row by row (i.e. operationId by operationId), as opposed to SELECT all operationIds, then foreach operation id select all rows where operationid = ... On Thu, Dec 5, 2013 at 1:44 PM, Igor

Re: [sqlite] Concrete example of corruption

Might there be a way to implement a custom VFS for Mac to deal with this? On Thu, Dec 5, 2013 at 3:55 PM, William Garrison <1billgarri...@gmail.com>wrote: > On Thursday, December 5, 2013, L. Wood wrote: > > > A fact of reality: Documents can be moved by the program's users. > > > > The database

Re: [sqlite] Concrete example of corruption

On Thursday, December 5, 2013, L. Wood wrote: > A fact of reality: Documents can be moved by the program's users. > > The database should not be corruptible in this case. At most, I should get > errors from SQLite that I can handle gracefully. > > This is a normal thing. We are simply driving our

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

Warren wrote 4 dec 2013: | There are tiny corners of the programming world (FP) where this is not the case, but then you get into | questions of purity, and databases are about as far from side-effect-free as you can get. That's a wee bit exaggerated, but who am I to start a discussion;

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

On 12/5/2013 4:31 PM, Hayden Livingston wrote: --> End Goal: A SQL Table: FooId,SomeStringValue,SomeIntValue,SomeOtherColumn,WhateverColumnNameItNeedsTo, You do not want a table with an open-ended set of columns - SQL doesn't work that way. You want a fixed schema; let the number of rows be

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

Ok. We have a bunch of different components in our system that write about a particular investment. These components know nothing about each other, except the item they are operating on. Let's take 3 components: ComponentA, ComponentB, ComponentC -- all of these write a "FLAT FILE"! We then parse

Re: [sqlite] Concrete example of corruption

> Simon wrote: > > Doesn't help. Because the corruption can happen > if the journal file is moved after the app has crashed. > And if the app has crashed it doesn't have any of the files > open and can't monitor them being moved. D. Richard Hipp's scenario was not about "our" program crashing.

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

Apologies, my mail is slow today, did not notice this thread had progressed significantly before I posted - please ignore previous. I'm with Igor though, the multi-table layout you now have is even less convenient than the matrix - It's equally dispersed data only now you have to join 3

Re: [sqlite] Encryption

Yes, I did test SQLCipher and it slows down a bit. Now, I would like to go with SEE if its available for the latest version. SQLCipher is available for 3.8.0.2 while 3.8.1 is out. On Thu, Dec 5, 2013 at 9:34 PM, Simon Slavin wrote: > > On 5 Dec 2013, at 3:02pm, Raheel

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

One PIVOT-ing approach is per-item selects when you don't know the subject value - this is an exact version of your question: CREATE TABLE `temptest` ( `ID` INTEGER PRIMARY KEY, `Col1` TEXT, `Col2` TEXT, `Col3` TEXT, `Value` TEXT ); INSERT INTO `temptest` (`Col1`, `Col2`, `Col3`, `Value`)

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

On 12/5/2013 1:43 PM, Hayden Livingston wrote: Yes, are moving our sparse matrix to different tables: Id | RelationalIdentifier | ColA 1aX 2bA Id | RelationalIdentifier | ColB 1aY 2b

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

Thanks, Igor. Yes, are moving our sparse matrix to different tables: Id | RelationalIdentifier | ColA 1aX 2bA Id | RelationalIdentifier | ColB 1aY 2bB

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

On 12/5/2013 1:15 PM, Hayden Livingston wrote: I have a table schema such like ID | Col1 | Col2 | Col3 | Value 1 anull nullX 2 null a nullY 3 null null a Z 4 b nullnull A 5 null b nullB 6

[sqlite] Selecting rows as "groups" -- "pivots"?

I have a table schema such like ID | Col1 | Col2 | Col3 | Value 1 anull nullX 2 null a nullY 3 null null a Z 4 b nullnull A 5 null b nullB 6 null null b C Right now these are in

Re: [sqlite] Concrete example of corruption

On 2013/12/05 16:40, L. Wood wrote: Could you be clear on what issue it is that you want solved, and how your proposal solves it any better than what is currently being done ? L. Wood: We are trying to find ways to avoid the corruption problem that D. Richard Hipp outlined. See his steps

Re: [sqlite] Concrete example of corruption

On 5 Dec 2013, at 2:40pm, L. Wood wrote: > We are trying to find ways to avoid the corruption problem that D. Richard > Hipp outlined. See his steps (1)-(5) in a previous post. Okay. Sorry, but this is not possible using a client/client DBMS and a setup where your users

Re: [sqlite] Encryption

Hi, The only problem is that I dont have access to SEE by Dr. Richard. So how should I test it ? Does he give test licenses ? Also I am assuming it will always be supported by the latest version of SQLite right ? On Thu, Dec 5, 2013 at 7:09 PM, Simon Slavin wrote: > > On

Re: [sqlite] Concrete example of corruption

> Simon Slavin wrote: > Could you be clear on what issue it is that you want > solved, and how your proposal solves it any better than > what is currently being done ? We are trying to find ways to avoid the corruption problem that D. Richard Hipp outlined. See his steps (1)-(5) in a previous

[sqlite] "Last Modified" file attribute is not updated

I'm using Windows 7, sqlite 3.7.17 compiled with a VS 2010. When writing in a sqlite database configured with SQLITE_CONFIG_MMAP_SIZE, the database file atribute "Last Modified" isnt updated. According to 'MapViewOfFile' documentation [1], "When modifying a file through a mapped view, the

Re: [sqlite] Sqlite with VS 2008

Compile ? >? if your using windows you dont need to Compile SQLITE just use http://www.sqlite.org/2013/sqlite-dll-win32-x86-3080100.zip if you need to Compile the C++ Source just use a few free C++ Compilers available on the internet http://www.bloodshed.net/download.html -- View this

Re: [sqlite] Encryption

On 5 Dec 2013, at 9:15am, Raheel Gupta wrote: > Yes, I agree. But it should not make the inserts and read too slow as well. The key word here is 'too'. If there's only 5% difference in speed between the two systems then it doesn't matter which one you use. So you have

Re: [sqlite] Strange comparison with CAST behavior

I was not aware of such!!! Thanks! LS 2013/12/5 Hick Gunter > CAST supplies an affinity to the operand, which forces the comparison to > be made with affinity. And since the constants transform into each other, > the comparison succeeds. > > explain SELECT CAST(123 AS

Re: [sqlite] Strange comparison with CAST behavior

CAST supplies an affinity to the operand, which forces the comparison to be made with affinity. And since the constants transform into each other, the comparison succeeds. explain SELECT CAST(123 AS INT)='123'; addr opcode p1p2p3p4 p5 comment

Re: [sqlite] Strange comparison with CAST behavior

Luís Simão wrote: > SELECT 123='123'; // 0 > SELECT CAST(123 AS NUMERIC)='123'; // 1 ??? > > How is this possible? Plain 123 or '123' has affinity NONE. CAST(123 AS NUMERIC) has affinity NUMERIC, so the string gets automatically converted for the comparison. See

[sqlite] Strange comparison with CAST behavior

I found this strange behavior in SQLite: While testing whether SQLite performs cast before comparison or just return 0, I entered following: SELECT CAST(123 AS INT)='123'; And for my surprise, it returned 1!!! However, my previous tests checked that numeric/text comparisons always returned

Re: [sqlite] prepared statemnt for column names and sorting preference

NO. ORDER BY is satisfied either by an index or by a separate sorting step, depending on whatever indexes are present or maybe even created on the fly by SQLite. The choice affects the generated SQL byte code (=the prepared statement), so it would have to re-prepare the statement anyway.

[sqlite] prepared statemnt for column names and sorting preference

Hi, select * from emp order by empid desc; //here empid is column name among clolumns. Now, I want to write prepared statement for above query. select * from emp order by ? ?; //I want to substitute column name and sorting preference. is it possible with sqlite? thanks, a

Re: [sqlite] Encryption

Yes, I agree. But it should not make the inserts and read too slow as well. I just want to support 256 Bit AES. On Thu, Dec 5, 2013 at 1:50 PM, Klaas V wrote: > When you'use the word 'perfornance' you might be interested not jonly in > speed, but in strength of

Re: [sqlite] Encryption

When you'use the word 'perfornance' you might be interested not jonly in speed, but in strength of protection, privacy of the employees, your company as a whole and above all your clients. One of the goals of encryption is to avoid e.g. the government(s and the spies they hired to peek into