ROW_NUMBER support

[ArtemRazin]

Firstly, thank you so much for the great library!

I'm wondering if there is any way to include ROW_NUMBER in a query using sqlite_orm?

[fnc12]

@ArtemRazin hi. Thanks. I appreciate your reaction!

What SQL query exactly you want to run?

[ArtemRazin]

The scheme is the following:

CREATE TABLE `Objects` (
	`Name`	TEXT,
	`Owner`	TEXT
)

The query that I need should return the index of a row whose name starts with some letters:

SELECT RowNum FROM (
    SELECT
        ROW_NUMBER() OVER () RowNum,
        Owner,
        Name
    FROM
        Objects)
WHERE
    Owner LIKE 'M%'
LIMIT 1;

I need it for an application that has a frontend in C#/WinForms with a virtual list view, and a backend written in C++. When a user enters something, the backend should return the index of a row in the virtual list view.

[fnc12]

ok I see. So it is window functions feature which has ROW_NUMBER and OVER keywords. This feature is not supported yet and exists in TODO list. Please subscribe to see when it will be ready. One can subscribe in Twitter https://twitter.com/sqlite_orm and GitHub. Also you can make a PR with this feature if you want and can handle it by yourself

[ArtemRazin]

Also you can make a PR with this feature if you want and can handle it by yourself

I don't mind trying to implement this feature myself. Any ideas on where would be the best place to start?

[fnc12]

first you have to create API which will make the code looking more less like original SQL just like it is done with other APIs in sqlite_orm. To make API you have to check SQLite syntax for WINDOW feature https://www.sqlite.org/windowfunctions.html . Please keep in my the goal of sqlite_orm: API has to give opportunity to users to generate any SQL query without raw string API. So it may be is quite tricky or may be not. Also read contribution guide which tells you how to format the code and how to generate amalgamation. Any additional questions can be posted here

[ArtemRazin]

Please keep in my the goal of sqlite_orm: API has to give opportunity to users to generate any SQL query without raw string API.

Sure, that's why we all love sqlite_orm so much ;)