Re: [sqlite] Can this be done in one query?

[Darren Duncan]

Kai Peters wrote:

I do have an audit table with this structure:

AuditID
ChangeDate
RowID   (foreign/primary key in TableName)
ActionType   
TableName


and I want to query for records pertaining to a certain table name within a 
given changedate range.
I do, however, only want to receive the last (ChangeDate) record in cases where more than one record 
per rowid exist.


Any help appreciated,


You will need to use a subquery to do what you want, because you want to do a 
join on the results of a group by.  This is one example of syntax:


  select * from audtbl where (RowID, ChangeDate) in
(select RowID, max(ChangeDate) as ChangeDate from audtbl group by RowID);

-- Darren Duncan

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

Re: [sqlite] Can this be done in one query?

[Igor Tandetnik]

Kai Peters  wrote:
> and I want to query for records pertaining to a certain table name within a 
> given changedate range.
> I do, however, only want to receive the last (ChangeDate) record in cases 
> where more than one record
> per rowid exist.

A very similar problem was discussed extensively just the other day. See this 
thread:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg72319.html

-- 
Igor Tandetnik

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

Re: [sqlite] Can this be done in one query?

[Simon Slavin]

On 9 Sep 2012, at 5:19am, Kai Peters  wrote:

> I do have an audit table with this structure:
> 
> AuditID
> ChangeDate
> RowID   (foreign/primary key in TableName)
> ActionType   
> TableName
> 
> and I want to query for records pertaining to a certain table name within a 
> given changedate range.
> I do, however, only want to receive the last (ChangeDate) record in cases 
> where more than one record 
> per rowid exist.

SELECT * FROM auditLines WHERE TableName = 'this' AND ChangeDate BETWEEN 
'20120810' AND '20120812' ORDER BY ChangeDate DESC LIMIT 1

Adjust the above for whatever format you're storing your dates in.
For fast searching add an index as follows

CREATE INDEX TCAuditLines ON (TableName,ChangeDate)

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

[sqlite] Can this be done in one query?

[Kai Peters]

Hi all,

I do have an audit table with this structure:

AuditID
ChangeDate
RowID   (foreign/primary key in TableName)
ActionType   
TableName

and I want to query for records pertaining to a certain table name within a 
given changedate range.
I do, however, only want to receive the last (ChangeDate) record in cases where 
more than one record 
per rowid exist.

Any help appreciated,

Thanks,
Kai



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

Re: [sqlite] System.Data.SQLite and .NET 4.5

[Joe Mistachkin]

Nicolas Rivera wrote:
>
> Does System.Data.SQLite support .NET 4.5?   
>

It has not been tested with the .NET Framework 4.5; however, I am not of any
reason(s) why it will not work.  I do know that it will not work in WinRT,
due to the complete lack of ADO.NET in that environment.

--
Joe Mistachkin

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

[sqlite] System.Data.SQLite and .NET 4.5

[Nicolas Rivera]

Hi,

Does System.Data.SQLite support .NET 4.5?  The downloadable binaries only come 
in 3.5 and 4.0 flavors (although it is possible for assemblies to support 
multiple .NET versions).

Thanks,

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

[sqlite] Disks lie

[Simon Slavin]

One topic that comes up here from time to time is the difficulty of ensuring 
ACIDity and resilience under power failure, even if your code is 100% correct, 
because the OS lies to your app and hardware lies to the OS.  It's difficult 
explaining this stuff because a programmer /has/ to rely on OS services 
somewhere along the line -- the only alternative is to design your own hardware 
and OS from scratch.

So here's a short ACM article by Marshall KcKusick called _Disks from the 
Perspective of a File System_, but the subtitle is better: _Disks lie_.



The first half of the article is about these subjects, ending with the nemesis 
of ACID: out-of-order writing.  The second half of the article, about sector 
sizes, is less relevant to what we discuss here.

As always with this subject, the article has many small details missed out.  To 
cover this in full detail would take a book -- a book which wouldn't be worth 
reading because it would just explain the problems, not help to solve them.  So 
don't believe everything he writes to be a full explanation, just like most of 
the posts to this list don't explain every single aspect of the problem.  It's 
enough to get you thinking about the right issues.

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

Re: [sqlite] Foreign Key

[Simon Davies]

On 7 September 2012 21:28, Dennis Klein
 wrote:
> Hallo,
>
> I am Using the latest Version of the precompiled shell Application from the 
> Download section, and the C/C++ Sources in Version 3.7.11
>
> Now i created my tables, using Primary Key and Foreign Key Constraints. Then 
> I tested it, and inserted test data, with a foreign key which doesn't exist 
> as primary key in the referenced table. This worked. I also added valid data, 
> and tested the "ON UPDATE" and "ON DELETE" constraints. So if i delete a 
> entryset in the referenced table, the foreign key isn't set null.
>
> Is there a bug?

Did you do
pragma foreign_keys=on;
?

http://www.sqlite.org/foreignkeys.html#fk_enable

They are off by default.

>
> Bye Dennis Klein

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

[sqlite] Foreign Key

[Dennis Klein]

Hallo,

I am Using the latest Version of the precompiled shell Application from the 
Download section, and the C/C++ Sources in Version 3.7.11

Now i created my tables, using Primary Key and Foreign Key Constraints. Then I 
tested it, and inserted test data, with a foreign key which doesn't exist as 
primary key in the referenced table. This worked. I also added valid data, and 
tested the "ON UPDATE" and "ON DELETE" constraints. So if i delete a entryset 
in the referenced table, the foreign key isn't set null.

Is there a bug? 

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