I like SQLite, but be aware that it's got some pretty significant limitations:
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442615.aspx
http://www.ayende.com/Blog/archive/6916.aspx
I'd generally go with SQL Server Compact Edition (if you're running on Windows) or Firebird (cross platform) over SQLite.
Thanks for the comments Jon. I see that SQLite suffers from two possible issues, locking and date time. It is interesting to note that Firebird is supported by ORM's like NHibernate. I suspect but have not investigated that SQL Server CE would be discounted for OSS projects due to it's license.
Right. I think the datetime thing is part of a bigger issue - data types in SQLite are pretty limited and loosely enforced:
http://www.sqlite.org/datatype3.html
I agree about SQL Server CE being out of the running for most open source projects, since it requires a Visual Studio license:
http://www.microsoft.com/sql/editions/compact/howtoobtain.mspx
I think I'd go with SQLite for a simple project and Firebird for anything that may have more advanced needs:
http://www.firebirdsql.org/
I'd like to see VistaDB on this list, too. It's a commercial product, but I think it'd make sense for them to grant licenses to open source projects.
I don't think there are any licensing restrictions on SQL Server CE.
Please see this link: http://msdn2.microsoft.com/en-us/library/ms171938.aspx
It says: "SQL Server Compact Edition is free to download, deploy and distribute."
SQL Server Compact does *not* require a license for Visual Studio. You just need a license for Visual Studio if you're going to use Visual Studio to program against the DB. Have a look at www.microsoft.com/.../compact-redistribute.aspx for redistribution rights.