Hi,

I found just one thread where somebody asked for SQLite support about two years ago.

And because of this time span I wanna ask again: What about SQLite support?

Asking greetings

Reiner

Hi Reiner,

Sorry. LLBLGen Pro doesn't support SQLite yet and doesn't have plans to. If possible, use Sql CE 3.5 or, if you have time to fun you could start writing the LLBLGen Driver for SQLite.

Hi David,

do U know if there is a special reason about it? Because I know some developer which like and prefer SQLite.

And I for myself plan some small software and looking for a simple RDBMS for that. Access is not possible because it just runs in 32 bit mode but the future will be 64 bit. I don't want a something like MS SQL-Server Express because IMHO it is too oversized.

Sighing greetings

Reiner

I think the main reason is that there were very few requests to support it.

If you decide to do it yourself, then please read the following brief.

Supporting a database has 3 separate tasks:

1) writing a driver. This driver retrieves the meta-data from the schema/catalog connected and creates objects of a catalog/table/field store in-memory. All drivers of llblgen pro work this way, and a licensee has access to the sourcecode of all these drivers, so a lot of examples are available. (removed reference to subversion repository, as it's no longer there. -- Otis)

2) writing a DQE (Dynamic Query Engine). This is a subclass of a generic dynamic query engine and you write routines which produce a solid INSERT, UPDATE, DELETE and SELECT statement (with variants) using input, as well as you write a class which produces a valid Parameter, a valid Alias name etc., also using a subclass of a generic class. The DQE is part of the runtime (so you use it with your own code). All DQEs of all supported databases are available to you in sourcecode form as a licensee (BSD2 licensed, so you can copy whatever you want to your own SQLite DQE. Again, please check the subversion repository for examples of DQEs. Most of the code can be based on DQEs of other databases, as we already support a lot of other databases so it's likely that the way SQLite for example does paging or a SELECT statement is similar to a database we already support (e.g. mysql or postgresql)

3) writing the db specific templates. This is very easy, just copy the db specific templates already supported and alter the db specific parts, which are typically references to ado.net provider namespaces, ado.net db type enum references and if the database supports stored procedures, the stored procedure call code. If I'm not mistaken, SQLite doesn't support procs, so you can typically use access or mysql templates and start from there, this shouldn't take long (maybe an hour)

How long it will take depends on the skill of the developer: a lot of the code is already available so although it might seem a bit much, it's often not that much, and you likely will use a subset of what's necessary, like you only port C# templates and only for .NET 2.0. If you don't use procs, you don't have to retrieve the meta-data for them in the driver for example...

We estimate with a limited database like SQLite, it will take a couple of days to be up and running.

With driver development: if you decide to go for that, we'll help you if you have questions, like 'how to do this, how to do that', and if you want, we can host your code in our svn repository. If you decide to write the driver, we can provide you with the initial step information, like creating a new GUID for the driver, a new driver config file (which is a simple xml file), where to place what in the llblgen pro installation folder etc.

Hi Walaa,

thanks 4 Ur elaborated answer!

I really do not stuck on SQLite, it was just because of the meaning of my colleagues and some articles of German .NET magazines I remembered.

I do not want the users to install a RDBMS. The users shall have nothing to deal with how the data is stored. And if there are tons of DLLs that have to be copied to make my software run, this would no good idea, isn't it. And additionally if there is a DBMS service that will always start when the system is started even if the software is not started, IMHO this is not suiteable for small programs, isn't it?

Pensively greetings

Reiner

Hi Walaa,

Walaa wrote:

svn://www.sd.nl/LLBLGenPro

hm, my Tortoise is not able to connect to this repository.

Is there something I've to consider?

Errorsearching greetings

Reiner

The subversion repository isn't there anymore. We moved servers and didn't add it to our current server. If you don't want users to install a db, consider firebird embedded, msaccess or sqlserver ce (3.5 that is, the older versions are crap)

Hi Otis,

thanks! I'll try it. First my mind was on Access but there is no OLEDB driver for 64 bit. It has to run in 32 bit mode and I don't want to invest in something that possibly later will not longer supported.

I got an answer from Jens Häupel, who is the Platform Strategy Manager over here in Germany, and he refered to the SQL-Server too.

So it seems that I've to cotton up to Firebird.

Thankfull greetings

Reiner

Now I am getting crazy!

First I investigated for a GUI database designe software that allows to create a RDBMS independent design and is able to create a DDL file for different specific RDBMSs.

So after founding the good DeZign I found 'Happy Fish Database Designer' (yes, nice name but it is a Dutch product ), what has to be improfed but is already really good.

The I fixed some bugs in my DB design that Access does not realised but the MS SQL Server 2008, and converted it to Firebird 2... and becomes angry!

What kind of developer restricts the constraints name of a DB to 31 characters?

Are they back in DOS times? Shall we limit the names to something unreadable?

Therefore I decided to move to another DB like SQL Server CE 3.5.

But I did not found any hints how to use it, neither in the MSDN nor by Google. Two of my colleagues said that just a fiew files (DLLs) are necessary. I, for myself, found about a week ago a web site that told me that I've to call a special install routine for it.

Does anybody can tell me or can send me a link where is a detailed description for developers how to use (and install) MS SQL Server CE 3.5 for software developers that wanne use this DB for its own software.

Crying greetings

Reiner

I assume you've already seen it - but this page looks like a good place to start...

Matt

Hi Matt,

Yes, I already found this side. Just now I read the document about SQL Server Express and CE.

O.k. I decided to take the CE and if anybody later want's another RDBMS it is possible to generate it for this different RDBMS with the 'Happy Fish Database Designer' I bought from a Dutch software company.

So do I've to select the SQL Server when creating the project in LLBLGen? Because the CE is just one DLL in opposite to the SQL Server service.

Thoughtfull greetings

Reiner

Please start here: SqlServer CE Desktop support

Uhh! It's crazy! After finishing the DB model by finding the last bugs I tried to generate the tables in the SQL Server 2008 Management Studio, connected to a CE DB file, I just get errors.

Its name is SQL Server but the datatypes quite different from that of SQL Server 2005 and 2008.

And my Happy Fish does not know how to convert into the CE types. I already asked Michel van der Pol from Polderij ICT if he can implement it, but even if he can it will take time that I don't have.

Sad greetings

Reiner

CE 3.0/1 is very different to CE 3.5 (200 , CE 3.5 is usable, CE 3.1/0/2.x is crap, so if possible pick CE 3.5. Avoid varchar/char/text and use nvarchar/ntext/nchar.

Hi Otis,

so if I replace all varchar by nvarchar it should work?

Because I though I saw some other data types that are different, or does I am wrong?

Confused greetings

Reiner

Hi Otis,

U r right! I just replaces varchar by nvarchar and it works.

THANKS for this hint!

BTW, as far as I saw with SQL Server CE 3.5 no comments, e.g. for tables and columns, are possible, isn't it?

Happy greetings

Reiner

Hi,

so I am not shure. In the documentation of LLBLGen is to read that first I'd have to create a normal SQL Server project and then the .config has to be changed.

As I understand, first I've to create a similar database in SQL Server 2008, create the LLBLGen project, read and select the entities, create the code and then change the .config in order to access the real CE database, isn't it?

Sorry for my stupid questions but it's my first own project. Mainly I worked in bigger projects for different companies.

Unsure greetings

Reiner

That's right. The idea is to let LLBLgen Pro read the schema and metadata of your database, that's why we need you to create a copy on SQLSERVER 200x use it to initiate the llblgen project then use your config file to point to the real CE database.

Another reason to consider adding SQLite support: Then LLBLGen could add iPhone development templates for use with Novell's MonoTouch/SQLite.

Having just written by hand a database interface layer for my iPhone app, I'm really missing LLBLGen.

scotru wrote:

Another reason to consider adding SQLite support: Then LLBLGen could add iPhone development templates for use with Novell's MonoTouch/SQLite.

Having just written by hand a database interface layer for my iPhone app, I'm really missing LLBLGen.

SQLite doesn't support right-joins. That's a problem, as the joins are sometimes re-arranged because the developer has specified them in the wrong order (e.g. Customer Join Order, and then adding Orderdetails left join Order, which will result in Order right Join Orderdetails, as 'order' is already in the pack). So that's a bit of a hurdle to take. We still consider SQLite for v3 though, as well as other databases. Perhaps not right away at 3.0 RTM, but it won't be hard to add support anymore as we have done a lot of work to make DQE's and drivers use a lot of generic code so writing these is not hard nor a lot of work.

Considering compact frameworks: we are very likely dropping these in v3. So supporting monotouch on iphone, if the main code doesn't compile on monotouch, we won't port it. The same goes for compact framework. It's not totally final yet, but very likely. Among the reasons is the horrid testing environment for these frameworks and the severe limitations in writing code which has to be used on all platforms: 10 to 1 that a handy class or overload is not supported so we've to work around that, which is not worth it in the long run. (compact framework for example isn't updated to .net 4.0 yet either)

-

Thanks for your vote, we'll add it to the list.

Matt

Chombatta wrote:

Bump and one more vote for supporting SQLite in the near future (v3) if at all possible.

I vote for SQlite support in V3 as well.

We got your vote. (democracy in it's perfect sense).

Thanks.

We can't promise anything, but it's on our list of future features to look into.