One minor thing here: technically this should be .isNull() instead of .isEmpty(). This makes it possible to create views with an empty name. It was .isEmpty() in the old code and we're not really consistent here but I thought it might make sense to change this while working on this code anyway 😄

Extra empty line here 😉

The wording seems a bit odd here. Maybe change it to "There is no filter set for this table".

@justinclift What do you think as a native speaker? 😉

Ok, your proposal sounds better for me too 😄 I'll change that but I'd like to hear Justin's opinion.

"There is no filter set for this table" sounds fine to myself as a native English speaker. Someone really into correct English sentence structure might have a problem with it (not sure), but to me it sounds perfectly ok from a "natural" point of view. 😄

This won't work as expected. The filters will then be applied to the original data, not the converted data. E.g. using the Julian day format leads to this SQL:

SELECT datetime(`my_date_column`) AS `my_date_column` WHERE `my_date_column` LIKE '%2017%';

vs this with the old code:

SELECT datetime(`my_date_column`) AS `col1` WHERE `col1` LIKE '%2017%';

In SQLite the results will be different because in the first case the WHERE part is applied first and the datetime function will only be applied on the resulting data set afterwards.

Ok, there's no easy way 😢 I can fall back to my first idea that was not saving the display formats, because I don't know how can it be made to work preserving the original column names. I don't like either adding a prefix like new_ or just _ to all the columns.

But that breaks for tables like these:

CREATE TABLE `test` (
	`field`	INTEGER,
	`field_`	INTEGER
);

On the other hand, the same was true for the old code, just with different column names. So I think we can leave it like this unless you have an idea how to fix this easily 😄

Well, in fact I started adding "_" to all columns and that is the safe approach. But it was so ugly 😄 I think, I'll go back to that. Better ugly than having broken combinations.

I'm surprised. sqlite is still filtering by the wrong column when all fields have been renamed. For your example table:
SELECT _rowid_,upper(field) AS field_,field_ AS field__ FROM main.test WHERE field_ = 'hola' ORDER BY _rowid_ ASC LIMIT 0, 50000;
That filters by field__ (original field_) and not by the new field_. If sqlite is going to always give precedence to the original columns, I don't see a efficient and simple way to solve the problem. As you said, the original implementation had the same problem when there were original columns named col1, col2, etc.

So I'll merge from my last commit 😄

Yeah, I think the only way to do this properly is to apply the display format conversion to the column and to the WHERE clause:

SELECT DATETIME(field) AS field, field_ FROM test WHERE DATETIME(field) LIKE '%2017%';

Yes, that is the standard and safe solution. What I have read is that any WHERE applied to the aliases is actually disallowed by the SQL standard. I've implemented your idea and works well. Do you think the display format would be calculated twice or it would be optimized? In any case I think it's the best solution.

https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause
https://dev.mysql.com/doc/refman/5.5/en/problems-with-alias.html

Looks like it is calculated twice. But whatever, implementing this correctly seems more important to me than the slight performance benefit 😄

CREATE TABLE `test` (
	`field`	INTEGER,
	`field_`	INTEGER
);
EXPLAIN SELECT DATETIME(field) AS field, field_ FROM test WHERE DATETIME(field) LIKE '%2017%';

prints