date:20131205

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 a database file inside the app's package itself and 
write to it, because an App isn't expected to write to itself (common technique 
for a malware app hiding what it really is: when you run it it downloads the 
malware code from a server, writes that code to itself then restarts itself).  
It would work fine with a read-only database, but this thread is about 
corruption due to writing journal files.

On the second hard, you can make a package anywhere.  Including but not limited 
to the user's Documents folder, and the folder where an application is expected 
to keep its own data files.

On the gripping hand, there's nothing particularly secure about packages.  The 
Finder allows a user to look inside a package easily, and if that user has 
enough privileges they can move one file out of the package and leave the rest 
there.  Keeping a database file inside a package is one additional layer of 
resistance against malicious corruption, but it's a very thin layer.

The conclusion (though not 100% of the reasoning) of the rest of your post I 
agree with: this is not an issue which can or should be dealt with inside 
SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

2013-12-05 Thread Stephen Chrzanowski

@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 amount of
time.  I've never installed a program/package/whatever on a Mac so I don't
know the rules or the full terminology.

@OP>
Being that I'm a Windows Only developer, I've never had to run into this
problem.  If something has its hand on the file, it holds it in place.  I
absolutely make sure 100% that while my applications are running, a file
handle is on the database file itself to ENSURE issues like this just do
not happen.  If someone comes along and changes file and/or directory
permissions while my programs are running, well, they're out to sabotage
their data and I'm not interested in their affairs.  We've already been
over the differences between EXTFS and NTFS differences, OS level handling
of file management, etc. all in another thread.

The issue you're running into is going to be at the application level, not
at the DB level.  SQLite cannot, and will not, be your savior in this
case.  My apologies to Dr Hipp and the rest of the development crew but
file handling in SQLite is extremely stupid, and I feel that this is the
way it SHOULD be.  File handling is NOT SQLites responsibility, but your
applications responsibility in knowing how the OS handles itself.  I think
you're expecting too much out of "preservation of data".  Even with MySQL,
if you start messing around with directory structures, file permissions, or
whatever, you're GOING to kill the software.  If you deny write permissions
to the required directories (Assuming MySQL isn't running as root) you're
going to find yourself in a world of hurt.  Mind you end users may not have
access to MySQL database files as typically those live on another machine,
but the fact remains that even THAT software is going to start going stupid
and potentially get corrupted if someone starts moving files around.

Taking this conceptually down to a USER level, instead of an ADMIN level,
I'd recommend doing any or all of three following things;

1> At application start, remember what path the database lives in by
storing that filename in a string.  Any time the database is to be
accessed, verify that the path and file to the database actually exists
before any kind of SQL action happens.  If the file does not exist in the
path required, close/terminate the program, notify the user, and slap them
on the wrist next time.  If you even want to go so far as to TOUCH a file
to make sure you have write permissions to that path, be my guest.

2> Notify the user, in an email, person to person communication, on a web
page, or whatever the best method is in your case, that if the database is
moved while the application is running, they're responsible for the
corruption as they are not using the program as expected.

3> Use the Backup API and work the database out of memory instead of the
file system.  When the user closes the application, prompt to save, and
save where required.  If the path doesn't exist, open up a "SAVE AS" dialog
and have the user save where they need to via the Backup API again.  If the
database size in question is large, use the backup API and put the database
somewhere else that the user doesn't expect (IE: Random file name in a
temporary directory) and work off that.

You have no other options beyond that.  SQLite has to work under many
different types of operating systems, many of them Linux based using the
EXT type file systems, as well as Windows based using anything from
FAT8/12/16/32/NTFS, as well as others that don't fall into the either
camp.  SQLite has ZERO control over the file system.  It hands off handling
of the file to the operating system.  SQLite tells the operating system "I
need to write this data out" and the OS talks to the controller on your
hard drive, asks for data to be written, the drive physically writes the
data to the drive, the drive tells the OS the result, the OS then tells
SQLite the result.  The drive doesn't talk to your software.

You cannot ever expect to cover every single instance of preventing
corruption under your program.  Your users must follow rules, and although
it is great that you're looking to prevent the corruption, you need to look
further up the chain from disk level access to user interaction.

On a personal note, I still cannot fathom why anyone would WANT to do file
management while working on an active document.  Moving a document while
its being worked on contradicts everything I understand.

* If I were working in a garage on a car, I'd start work on it in a certain
physical place, be it on a lift, or on the ground, or at least in a certain
bay.  If I need to move it to another bay, I get the car into a condition
that I can safely move it, do the move, then resume work when ready.  If I
turn my

Re: [sqlite] Concrete example of corruption

2013-12-05 Thread Doug Currie


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 associated WAL and whatever other files get created in the 
> package, too?

Yes, I was wondering the same thing...

https://developer.apple.com/library/mac/documentation/CoreFoundation/Conceptual/CFBundles/DocumentPackages/DocumentPackages.html

e


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] JOIN failure in sqlite 3.8.1

2013-12-05 Thread Mark Brand

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 don't know for sure if this boils down to the same problem.

In this case, the problem seems to be triggered by the combination of a LEFT 
JOIN to an empty set together with a JOIN on the primary key column of a 
table, and can be strangely avoided with a CAST.

CREATE TABLE T (i INTEGER PRIMARY KEY);

INSERT INTO T (i) VALUES (1968);

--This join fails to return any rows in sqlite 3.8.1.
--Succeeds in 3.7.17.
--Succeeds in 3.8.2 2013-11-27 pre-release
SELECT A.i, typeof(T.i) t_type, typeof(A.i) a_type
FROM (SELECT 1968 i) A
LEFT JOIN (SELECT 1968 i WHERE 0<>0) B
ON B.i=A.i
JOIN  T
ON T.i = A.i
;

--But a CAST() makes the join succeed, even though both types are already 
integer.
SELECT A.i, typeof(T.i) t_type, typeof(A.i) a_type
FROM (SELECT 1968 i) A
LEFT JOIN (SELECT 1968 i WHERE 0<>0) B
ON B.i=A.i
JOIN  T
ON CAST(T.i AS INTEGER)=A.i
;

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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
somethings. Like orders per hour rate.


select ComponentId, OperationId, Value
from MyTable
where ColumnName = 'Col1';

would give you all orders together with their timestamps. Is that what 
you wanted?

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

2013-12-05 Thread Warren Young


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?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

2013-12-05 Thread Scott Robison

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 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 from some
> of the low-level programs (not Apps) that SQLite is used in.  In fact the
> amount of overhead involved is more than the whole of SQLite. like trying
> to get SQLite to support Unicode sorting.  It would be very much a rarely
> used tail wagging the dog.
>
> Also, the problem this solves really isn't one that concerns the vast
> majority of Mac programmers.  If you can't trust your users not to move
> data files out of place you can't trust them not to overwrite the contents
> of a database of journal file.  And the vast majority of Mac users never
> find the appropriate folder (easily locatable inside a hidden folder as
>
> /Users//Library/Application Support//
>
> ) anyway.  It's not like these files are right there in the user's
> documents folder where users are actually likely to notice them and mess
> with them.
>
> My conclusion is that those programmers who want this can do it inside
> their app, outside the code which handles the database itself.  Which means
> that the code will be useful for more than SQLite database files, and gives
> programmers more flexibility when deciding when to start monitoring files,
> and what to do when a database file or a journal file is moved.
>
> Simon.
> ___
> 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] 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 at 4:29 PM, Igor Tandetnik  wrote:

> 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 = ...
>>
>
> Query for what, exactly? Show an example of the kind of data you want to
> get out of that database.
>
> --
> Igor Tandetnik
>
> ___
> 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] SQLite version 3.8.2 running 2.5x faster for some queries.

2013-12-05 Thread Warren Young


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,


To prove my point, I decided to divide the SQLite commands[1] into those 
that modify the DB and those that do not:


Has side effects Limited side effects   No side effects
~~      ~~~
ALTER TABLE  ATTACH DATABASEANALYZE
CREATE TRIGGER   CREATE TABLE   CREATE INDEX
DELETE   CREATE VIEWDROP INDEX
DETACH DATABASE[2]   CREATE VIRTUAL TABLE   EXPLAIN
DROP TABLE   DROP TRIGGER[3]PRAGMA
DROP VIEW   REINDEX
INSERT  SELECT
REPLACE VACUUM
UPDATE

Commands in the first column clearly have side effects: they can affect 
the results of another process's SELECT queries.


The second column is for commands that are unlikely to affect another 
process's queries, but they do alter the user-visible DB schema, so it's 
hard to argue that they're side-effect-free.


The third column looks longer than it really is.  You can look at it as 
SELECT plus a bunch of "DBA commands."  Those latter commands merely 
affect how well the SQLite DB engine runs, and they're unlikely to be 
used very often once the DB is set up and working well.


If you strip away the setup, DBA, and rarely-used commands from the 
other columns, too, you're still left with SELECT on one side vs about 
half a dozen SQL commands with side effects on the other.  That's what I 
was getting at with my quoted comment above.


We should also consider SQLite's "functions".

Most of the core functions[4] are pure, but there are several impure 
ones: changes(), last_insert_rowid(), random(), randomblob(), and 
total_changes().


The date and time functions[5] are pure, unless you pass 'now', which is 
what all the argument here is about, of course.


The aggregate functions[6] are all pure.

I think my point stands: SQL is awfully impure, from an FP/mathematical 
standpoint.






-

Footnotes:

[1] From https://www.sqlite.org/lang.html but leaving out the keywords 
that aren't independent commands.  I also left out the TRANSACTION and 
SAVEPOINT commands, since they just group other commands.


[2] I put the DETACH and DROP commands in the first column even though 
their corresponding ATTACH and CREATE commands are in the second because 
there is nothing stopping these destructive commands from affecting 
another process's queries.


[3] DROP TRIGGER is interesting: From the perspective of figuring out 
whether it could affect another process through side effects, this 
command actually /stops/ future side effects from occurring, even though 
it modifies the DB file to do so.  Thus, it goes into the second column, 
not the first, where all the other DROP commands are.


[4] https://www.sqlite.org/lang_corefunc.html

[5] https://www.sqlite.org/lang_datefunc.html

[6] https://www.sqlite.org/lang_aggfunc.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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 = ...


Query for what, exactly? Show an example of the kind of data you want to 
get out of that database.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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 from some of 
the low-level programs (not Apps) that SQLite is used in.  In fact the amount 
of overhead involved is more than the whole of SQLite. like trying to get 
SQLite to support Unicode sorting.  It would be very much a rarely used tail 
wagging the dog.

Also, the problem this solves really isn't one that concerns the vast majority 
of Mac programmers.  If you can't trust your users not to move data files out 
of place you can't trust them not to overwrite the contents of a database of 
journal file.  And the vast majority of Mac users never find the appropriate 
folder (easily locatable inside a hidden folder as

/Users//Library/Application Support//

) anyway.  It's not like these files are right there in the user's documents 
folder where users are actually likely to notice them and mess with them.

My conclusion is that those programmers who want this can do it inside their 
app, outside the code which handles the database itself.  Which means that the 
code will be useful for more than SQLite database files, and gives programmers 
more flexibility when deciding when to start monitoring files, and what to do 
when a database file or a journal file is moved.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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 Tandetnik  wrote:

> 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
> open-ended. Something like this:
>
> ComponentId, OperationId, ColumnName, Value
>
> See also http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%
> 80%93value_model
> --
> Igor Tandetnik
>
>
> ___
> 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] Concrete example of corruption

2013-12-05 Thread Scott Robison

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 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 car, or at most not
> > stopping completely at a stop sign - not driving off a cliff.
> >
>
> I agree that on a Mac, this is would not be a surprising event, ie user
> moving/renaming a file at any time.
>
> Instead of trying to cope with this reality solely with SQLite API, you
> might consider using other system services.
>
> For example, your app or document class could use fsevents to watch for
> changes to your document package. When the user moves/renames your doc
> folder, you can respond in some rationale manner.
>
> And don't make it a valid usage in your architecture to allow other
> processes to access your SQLite db file directly--only through your
> document class.
>
> You've highlighted a valid concern. SQLite isn't designed to deal with an
> open db file being relocated. But Mac end users could do exactly this
> without much thought. It's your job to code for this possibility. Use the
> rest of the system to help you do that.
>
> Bill
> ___
> 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] Concrete example of corruption

2013-12-05 Thread William Garrison

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 car, or at most not
> stopping completely at a stop sign - not driving off a cliff.
>

I agree that on a Mac, this is would not be a surprising event, ie user
moving/renaming a file at any time.

Instead of trying to cope with this reality solely with SQLite API, you
might consider using other system services.

For example, your app or document class could use fsevents to watch for
changes to your document package. When the user moves/renames your doc
folder, you can respond in some rationale manner.

And don't make it a valid usage in your architecture to allow other
processes to access your SQLite db file directly--only through your
document class.

You've highlighted a valid concern. SQLite isn't designed to deal with an
open db file being relocated. But Mac end users could do exactly this
without much thought. It's your job to code for this possibility. Use the
rest of the system to help you do that.

Bill
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2013-12-05 Thread Klaas V

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; it's a valid, 
even respectable opinion

and
| Anyway, all this arguing over how SQLite *should* behave seems misguided to 
me.  What 
|   matters is how SQL is specified.  SQLite should follow the spec in areas 
like this.

Not even this me seems. The developers of SQLite can choose not to "obey" 
rules, advices and guidelines of SQL standard at will if one of the three guys, 
one of us users feels it's appropriate and fits their philosophy better or for 
whatever reason even rewrite (some of) the standard and create a new or adapted 
version = skipping, changibg adding things =  let's call it SQL13 (14 etc) or a 
less prozaic name. What about iSQL or SQLNT?  ;-) 

Klaas `Z4us` V

The fun with standards is there are so many to choose from
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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 
open-ended. Something like this:


ComponentId, OperationId, ColumnName, Value

See also 
http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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 each file in parallel line-by-line.

Each component can choose to write whatever data it wants to (in fact they
do, each component is a different team in my organization). But we've all
agreed on an "OperationID", which happens to be a 128-bit unique value.
Each component can write "MULTIPLE" rows of information and I don't know
how many rows it can write, but they will all be of the same schema.

ComponentA can choose to write: FooId, SomeStringValue, SomeIntValue (and
it can write many rows)
ComponentB can choose to write: FooId, SomeOtherColumn (and it can write
many rows)
ComponentC can choose to write: FooId, WhateverColumnNameItNeedsTo (and it
can write many rows)

--> End Goal:

A SQL Table:

FooId,SomeStringValue,SomeIntValue,SomeOtherColumn,WhateverColumnNameItNeedsTo,

FooId2,.

So is what RSmith is suggesting what I really want? I've spent so many
hours on this.I'm actually clueless now as well.

On Thu, Dec 5, 2013 at 11:45 AM, RSmith  wrote:

>
> 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 tables for the result. Any chance you could share with us the
> exact thing you are trying to store and the exact resulting knowledge you
> wish to deduce from the stored data?  Maybe we can come up with more
> helpful suggestions (as opposed to just looking puzzled!).
>
> Cheers,
> Ryan
>
>
> On 2013/12/05 20:54, Igor Tandetnik wrote:
>
>> 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
>>> 2bB
>>>
>>> Id | RelationalIdentifier | ColC
>>> 1aZ
>>> 2bC
>>>
>>
>> Why? Personally, I'd have one table: either
>>
>> Id, RelationalIdentifier, ColA, ColB, ColC
>> 1, a, X, Y, Z
>> 2, b, A, B, C
>>
>> or
>>
>> Id, RelationalIdentifier, Col, Value
>> 1, a, ColA, X
>> 1, a, ColB, Y
>> 1, a, ColC, Z
>> 2, b, ColA, A
>> 2, b, ColB, B
>> 2, b, ColC, C
>>
>> depending on how open-ended the list of columns is. It's also not clear
>> what the purpose of Id and RelationalIdentifier is; they appear duplicative
>> of each other.
>>
>>  How can I adapt your query to this new table schema?
>>>
>>
>> select RelationalIdentifier, ColA, ColB, ColC
>> from TableA join TableB using (RelationalIdentifier) join TableC using
>> (RelationalIdentifier);
>>
>>
> ___
> 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] Concrete example of corruption

2013-12-05 Thread L. Wood

> 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. It was about 
the "other person's" program crashing, leaving a journal file behind that "our" 
program cannot find because it tries to look for the journal file at the old 
path, causing corruption. Hence, my question about opening the journal file 
only once.

> RSmith wrote:
>
> Your previous post simply asked for changes
> without showing any cause to do so.

I can clarify:

My folder

/Users/lwood/Desktop/folder.ext

is a *document* (I have added ".ext" to the name now). My program has a 
document format of such folder packages. The database file is inside this 
package.

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 car, or at most not stopping 
completely at a stop sign - not driving off a cliff.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2013-12-05 Thread RSmith



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 tables for the result. Any chance you could share with us the exact thing you are trying to store and the 
exact resulting knowledge you wish to deduce from the stored data?  Maybe we can come up with more helpful suggestions (as opposed 
to just looking puzzled!).


Cheers,
Ryan

On 2013/12/05 20:54, Igor Tandetnik wrote:

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
2bB

Id | RelationalIdentifier | ColC
1aZ
2bC


Why? Personally, I'd have one table: either

Id, RelationalIdentifier, ColA, ColB, ColC
1, a, X, Y, Z
2, b, A, B, C

or

Id, RelationalIdentifier, Col, Value
1, a, ColA, X
1, a, ColB, Y
1, a, ColC, Z
2, b, ColA, A
2, b, ColB, B
2, b, ColC, C

depending on how open-ended the list of columns is. It's also not clear what the purpose of Id and RelationalIdentifier is; they 
appear duplicative of each other.



How can I adapt your query to this new table schema?


select RelationalIdentifier, ColA, ColB, ColC
from TableA join TableB using (RelationalIdentifier) join TableC using 
(RelationalIdentifier);



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

2013-12-05 Thread Raheel Gupta

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 Gupta  wrote:
>
> > The only problem is that I dont have access to SEE by Dr. Richard. So how
> > should I test it ?
>
> Why should you test it ?  Have you tested the solutions you do have access
> to and found that they make your app too slow to use ?
>
> Simon.
> ___
> 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] Selecting rows as "groups" -- "pivots"?

2013-12-05 Thread RSmith


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`) VALUES
('a', NULL, NULL, 'X'),
(NULL, 'a', NULL, 'Y'),
(NULL, NULL, 'a', 'Z'),
('b', NULL, NULL, 'A'),
(NULL, 'b', NULL, 'B'),
(NULL, NULL, 'b', 'C');

So the full table is now this:

IDCol1Col2Col3Value
--------- ---
1   a  X
2a Y
3a Z
4   b  A
5b B
6b C


And using this query:

SELECT (coalesce(T.Col1,T.Col2,T.Col3)) AS A,
(SELECT Value FROM temptest AS X1 WHERE X1.Col1=coalesce(T.Col1,T.Col2,T.Col3)) AS 
"Value",
(SELECT Value FROM temptest AS X1 WHERE X1.Col2=coalesce(T.Col1,T.Col2,T.Col3)) AS 
"Value",
(SELECT Value FROM temptest AS X1 WHERE X1.Col3=coalesce(T.Col1,T.Col2,T.Col3)) AS 
"Value"
FROM temptest AS T GROUP BY A;

Yields these results:

AValueValueValue
a X  Y   Z
b A  B  C


As requested.

You can shorten the SQL by using some "AS" statements etc, and not sure if it will work for whatever big query you have in mind - 
The question seems very unspecific though and the table is weird in any SQL handbook's terms, but this gets the required results.



On 2013/12/05 20:15, 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 null   null  b C

Right now these are in the same table (they may be different tables in the
near future, but I don't think that impacts this discussion)

I want to "PIVOT" this data such that:

A | Value | Value | Value
a X Y  Z
b A B  C

Searching on the internet says this can be done in SQLite using CASE
statements? But what happens I don't know the row id values (i.e. a, b,
etc)?
___
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] 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
2bB

Id | RelationalIdentifier | ColC
1aZ
2bC


Why? Personally, I'd have one table: either

Id, RelationalIdentifier, ColA, ColB, ColC
1, a, X, Y, Z
2, b, A, B, C

or

Id, RelationalIdentifier, Col, Value
1, a, ColA, X
1, a, ColB, Y
1, a, ColC, Z
2, b, ColA, A
2, b, ColB, B
2, b, ColC, C

depending on how open-ended the list of columns is. It's also not clear 
what the purpose of Id and RelationalIdentifier is; they appear 
duplicative of each other.



How can I adapt your query to this new table schema?


select RelationalIdentifier, ColA, ColB, ColC
from TableA join TableB using (RelationalIdentifier) join TableC using 
(RelationalIdentifier);


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Id | RelationalIdentifier | ColC
1aZ
2bC

How can I adapt your query to this new table schema?



On Thu, Dec 5, 2013 at 10:33 AM, Igor Tandetnik  wrote:

> 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 null   null  b C
>>
>> Right now these are in the same table (they may be different tables in the
>> near future, but I don't think that impacts this discussion)
>>
>> I want to "PIVOT" this data such that:
>>
>> A | Value | Value | Value
>> a X Y  Z
>> b A B  C
>>
>
> select coalesce(Col1, Col2, Col3) as A,
>   max(case when Col1 is null then null else Value end) as Value1,
>   max(case when Col2 is null then null else Value end) as Value2,
>   max(case when Col3 is null then null else Value end) as Value3
> from MyTable group by A;
>
> I must say you chose a very inconvenient table schema. What's with the
> sparse matrix? What kind of queries is this good for?
> --
> Igor Tandetnik
>
> ___
> 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] 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 null   null  b C

Right now these are in the same table (they may be different tables in the
near future, but I don't think that impacts this discussion)

I want to "PIVOT" this data such that:

A | Value | Value | Value
a X Y  Z
b A B  C


select coalesce(Col1, Col2, Col3) as A,
  max(case when Col1 is null then null else Value end) as Value1,
  max(case when Col2 is null then null else Value end) as Value2,
  max(case when Col3 is null then null else Value end) as Value3
from MyTable group by A;

I must say you chose a very inconvenient table schema. What's with the 
sparse matrix? What kind of queries is this good for?

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[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 the same table (they may be different tables in the
near future, but I don't think that impacts this discussion)

I want to "PIVOT" this data such that:

A | Value | Value | Value
a X Y  Z
b A B  C

Searching on the internet says this can be done in SQLite using CASE
statements? But what happens I don't know the row id values (i.e. a, b,
etc)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

2013-12-05 Thread RSmith

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 (1)-(5) in a previous post.

Richard did not "outline a corruption problem"... he merely stated a possible way to corrupt the database. In the same way as when I
explain that driving your car over a cliff is a possible way to destroy it - I am being very truthful and it is a very real-world
possibility should your driving sensibilities allow - BUT, I am in no way outlining an "over-the-cliff-driving" problem which
warrants special adaptions to cars or should merit attention by the governments of the world in order to stifle the phenomenon.

Mainly because it isn't a phenomenon - (as others and myself have suggested in previous posts), unless I am mistaken and you have
found it to be a phenomenon - in which case kindly supply the case study / corruption figures, which I am very sure will be taken
seriously.

Your previous post simply asked for changes without showing any cause to do so (which I believe is what Simon suggested above), but
the new post has done nothing different - it's still a request without providing a cause.

(May I add that changing the way in which SQLite access files has a huge impact on backward-compatibility and testing for millions
of systems, which is not to say that it can't or won't be done, but simply that your reason for requiring the change needs to be
specific and non-trivial).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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 can move the database files while the App is running.  
Whatever you can do in your app can always be topped by a user being 
sufficiently clever.

>> When writing that, don't forget that Unix, the underlying
>> OS used for the Mac operating system, allows a file to be
>> moved/renamed/deleted even while an application has it open. 
> 
> Yes, and that is exactly why I asked what I asked, namely whether a mode that 
> opened the journal file only once is a possibility or not. 

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.

Millions of us use SQLite databases on Macs and this has never been a big 
problem.  I suspect you're spending too much time on this eventuality which can 
be triggered only by a malicious user.  And if you have malicious users the 
only defence against them is to set things up so they don't have enough 
privileges to mess with your valuable files.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

2013-12-05 Thread Raheel Gupta

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 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 to write your application using one system, try it out on the
> kind of hardware you expect it to be used on, then figure out if one or
> both systems are 'too slow' for your specific use.
>
> Simon.
> ___
> 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] Concrete example of corruption

2013-12-05 Thread L. Wood

> 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 post.

> When writing that, don't forget that Unix, the underlying
> OS used for the Mac operating system, allows a file to be
> moved/renamed/deleted even while an application has it open. 

Yes, and that is exactly why I asked what I asked, namely whether a mode that 
opened the journal file only once is a possibility or not.  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2013-12-05 Thread Felipe Farinon


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 last modification 
timestamp may not be updated automatically. If required, the caller 
should use SetFileTime to set the timestamp."


I could create a workaround to fix this inside my application, but maybe 
it's sqlite responsibility to call SetFileTime after updating a database 
content.


[1] 
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366761(v=vs.85).aspx 
 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite with VS 2008

2013-12-05 Thread Crashbeta

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 message in context: 
http://sqlite.1065341.n5.nabble.com/Sqlite-with-VS-2008-tp60121p72762.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption