Cosmos Db

[Jochem-agro]

Hello, this is more of a feature request

I've stumbled upon this packages through the datatables.net site and saw it mostly supports relational Databases.

Any chance this gets support for Cosmos Db? It could leverages the OData functionality to create complex filters?

[AllanJard]

Its not something I'm currently planning I'm afraid. Your libraries are specifically designed for SQL relational databases. If there is a way for Cosmos to work with SQL, then yes, it would be possible I'd think, but I'm not familiar with Cosmos myself.

[Jochem-agro]

Hey Allen

First of all thanks for the quick response. There is a query like syntax for cosmos db. As far as I'm aware its not a full fletched implemenation of the SQL language but alot of thing are supported.

Maybe some more context: I've currently created a POC for the company I work at with datatables.net and editor and so far its working like a charm (although there were some bumps on the road, but most things were resolved by really digging in to the docs).

I've (ab)used the searchbuilder functionality for creating the search pills.

Now we would like to be also able to query cosmos Db with it. If we would fork this repo and try and create our own implementation could you point me out to some pitfalls we might encounter? Or would you ba willing to look at the implementation in case we get stuck? Or would you rather say that creating a own implementation is madness and we shouldnt try?

Thanks in advance

[AllanJard]

The SQL interface in the link looks promising. If there is an ADO.NET data connector for it (I expect there will be) then the way to do it would be to add it in to the Database.cs interface (1 and 2), and then create a new database driver here.

The only part that might cause you problems is getting the next insert id, every database has its own way of doing it and that needs to be taken into account in the driver.

[Jochem-agro]

Hey Allen

These were exact the locations I thought i would need to extend. I've been digging into your code since yesterday (im in deep please help xD). So its nice seeing it being reinforced by the things you are saying.

The next insert id is for the case you want to use the editor for inserting the data right? I would like to narrow the use case to reading first, since it all starts there.

Cosmos make use of id and/or partition key for identifying documents (or tables, whats in a name). Both can be application created values so we could just boil it down to have the correct values passed to the editor?

[AllanJard]

If you don't need insert right away, then yes, ignore the new primary key value. I'd focus on the select to start with - just get it populating data into the table. Are you getting any errors when doing that?

[Jochem-agro]

Hey Allan

We are back on the grind. The implementation is getting along but im afraid I would have to make a few changes in the editor as well. Im currently getting the count for the datatable. The SQL for azure cosmos goes like this:

select count(1) from [table]

now the table part is easy because that just the ReadTable method but in current implementation you take:

select count(pKey[0]) from table.

so for the count being able to work I have set primary key as 1

would you think it is a good idea to just introduce and if statement over there which checks the type of the database and then performs "hard coded" count

like so :

[AllanJard]

No - any database specific code should be inside the drivers. Could you just set the pkey to be 1 in your controller setup?