Support other databases such as SQLite about elmah HOT 17 CLOSED

I'm working on this. Should we be referencing the SQLite.NET .dll directly or 
use the
abstract factories exposed by .NET to make the class generic?

It should be safe to add the reference directly. If someone does not use the 
SqliteErrorLog (?) implementation then the JIT compiler will never hit it and 
resolve the reference to System.Data.SQLite.dll. IOW, the absence of 
System.Data.SQLite.dll in production for someone not using it will not prevent 
the 
rest of ELMAH from working.

So how are you going to implement this? Are you using Subsonic or NHibernate? 
The
reason I suggest one or the other is that by using those, we'll get MySQL 
support for
"free" along with MySQL.

Afaik SubSonic doesn't support SQLite yet, does it?

Well I haven't tried it personally, but this person wrote a provider for it.

http://codefornothing.wordpress.com/2007/07/19/sqlite-data-provider-for-subsonic
-part-2/

I think we should avoid adding too many dependencies on other projects like 
SubSonic 
or NHibernate at the moment because that may shy people away from actaully 
using 
ELMAH (if it means pulling/pushing in a lot of things). It's also worth asking 
if  
we need to involve these frameworks for the three simple methods required of an 
ErrorLog implementation? Simplicity and lightweightedness are appealing 
features for 
users that are important to keep in mind. Once there are enough ErrorLog 
implementations floating that work on top of various DB stores, one can always 
consolidate later via NHibernate or SubSonic, but a SqliteErrorLog will right 
now 
serve another better purpose than just trying to reach out to all kinds of 
stores. 
It provides a nice solution between the too-simple XmlFileErrorLog and the more-
complex SqlErrorLog. With a SqliteErrorLog, we get best of both worlds. No need 
to 
set up the database (as with SqlErrorLog) yet you get a single file to manage 
(versus many with XmlFileErrorLog) that contains structured data and which can 
be 
queried with SQL when needed (back to SqlErrorLog).

I took a look at SQLite SQL syntax, and I think it will be less straightforward 
than
I thought implementing it, but I will work on that. In particular, no store
procedures, and I'm not very good at dealing with SQL injection and stuff like 
that. 
I was thinking about a Db4oErrorLog too, that would be very easy instead. What 
do you
think? Another good compromise between xml and SQL Server.

I took a crack at it once. To give you a jump start, here's what I had come up 
with 
for the table creation script:

CREATE TABLE IF NOT EXISTS Error
(
    ErrorId     UNIQUEIDENTIFIER NOT NULL,
    Application TEXT NOT NULL,
    Host        TEXT NOT NULL,
    Type        TEXT NOT NULL,
    Source      TEXT NOT NULL,
    Message     TEXT NOT NULL,
    User        TEXT NOT NULL,
    StatusCode  INTEGER NOT NULL,
    TimeUtc     TEXT NOT NULL,
    Sequence    INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    AllXml      TEXT NOT NULL
)

You will still need to setup an index over the ErrorId column.

As for injection attacks, the most important measure is to user parameterized 
SQL. 
It's okay if SQLite does not support stored procedures. This is what I had come 
up 
with for the insertion script:

INSERT INTO Error (
   ErrorId, Application, Host, 
   Type, Source, Message, User, StatusCode, TimeUtc, AllXml)
VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

There is no support for named parameters so those question marks serve as place 
holders and are keyed by their position. To supply values, all you need to do 
is 
call SQLiteParameterCollection.AddWithValue in the same sequence as the listed 
place 
holders. For the parameter name, you can just supply a null value.

Hope this helps.

Thanks, I had already come up with the table definition but didn't know about
position of parameters.

azizatif. Agreed. I was thinking we could build a SubsonicErrorLog or a
NHibernateErrorLog. That way the dependency on those libraries only applies to
individual who really want to use those error logs.

Perhaps in an ElmahContrib library. ;)

The good thing about JIT is that if you don't hit a type, its dependencies will 
never be involved at runtime so they can be completely absent. So one could 
still 
have references to Subsonic/NHibernate in development and ship with a 
SubsonicErrorLog/NHibernateErrorLog yet the users who never use those 
implementations won't need Subsonic and NHibernate assemblies deployed in 
production. This technological benefit aside, the concern I was trying to bring 
up 
was whether bringing in other frameworks would help keep the project focus 
sharp for 
getting a v1 out the door. Meanwhile, there's a trials section in the repository
(http://elmah.googlecode.com/svn/trials/) for the dev team to experiment with 
ideas 
and which can be merged in later. At any point, the trunk can be branched there.

As for ElmahContrib, I think it's a great idea, but rather than discuss it here 
in 
the issue tracker, it may be something more appropriate to address in the 
discussion 
groups.

I'm done with this, now it needs some testing.

Usage is:

<connectionStrings>
 <add name="Elmah" connectionString="data source=C:\Elmah.db"/>
</connectionStrings>

<errorLog type="Elmah.SQLiteErrorLog, Elmah" connectionStringName="Elmah"/>

The database file is automatically created if it doesn't exist.

And here's the patch. Note that I've added a folder name lib to contain the 
SQLite
assembly but it looks like subversion doesn't include new folders into the 
patch, so
you'll have to add it yourself and add a reference to it into the project. I've 
been
using version 1.0.44 of System.Data.SQLite assembly.

This is great work, Simone. I just applied your patch, took the solution for an 
initial spin (worked as noted) and committed your contribution. It is available 
in 
the trunk as of revision 121. Perhaps it's time to move this issue out to its 
own 
thread and close it there? Further problems or enhancements with the 
SQLiteErrorLog 
implementation should also be reported as distinct issue now that it is part of 
the 
trunk.

Great news Atif. Again, note that I didn't have a chance to test it much yet, 
so it
might contain some major bugs - I didn't look much into SQLite syntax since I 
never
worked with it before.
Sure, feel free to move this on its own thread.

Closing this issue as fixed since at least one other database support has been 
added, which is SQLite (the one hinted in the original issue subject). Going 
forward, issues with the SQLiteErrorLog should be opened as new issues.