create database view from another table

[Fred Mayer]

I had a problem and solved it; just want to know if there was another more elegant solution than the one I did.   Please let me know if you know of a better option:

The issue:  have a date-time column in my specific SQLITE3 table.  wanted to create Year, Month, Day, Hour, Minute, Seconds and a few others like DATE and TIME.  Not practical to clone the database table, so wanted a 'View' against it.

2) went to my project folder and brought up the MODELS.py, and cloned the table, and added a project_Vtablename table.  (added the "V" in front of the table's name.

3) Went thru the "makemigrations" option, then the "migrate" option.  when it successfully finished, I had both the project_tablename and project_Vtablename tables in the DB.

4) then to insert the extra columns, went to SQLITE3,  developed and debugged a CREATE VIEW project_Vtablename statement, with all the additional columns added (Year, Month, etc).  Of course I deleted the project_Vtablename before creating it as a VIEW.

5) returned to  the VIEWS.py program and copied the IMPORT statement to add the  project_Vtablename , and cloned a function that is successfully reading the original - changed all instances of tablename to Vtablename.  AND it WORKS.

Now, with the Year column, i can now much more easily use and understand my SQL filters when parsing a date-time column.

...so, is there a more elegant way to install a database VIEW than a method something like the above?  If so, please SHARE IT so I and others can take advantage of it.  

[Curtis Maloney]

Hi Mike,

this is not really the correct list for your question - this list is about development _of_ Django, not _with_ Django.

That said:

did you consider adding an index for each of the derived values you wanted? This can act as an efficient way to keep, effectively, a "cache" of values derived from commonly used expressions.

The downside is your queries MUST use the same expression in order for the index to be considered.

The practice of wrapping a VIEW in an unmanaged Django Model (i.e. Meta.managed = False) is a long established and well tested pattern, though your approach to achieving this is novel.

Personally, I would:

- get the SQL Django emits to create the original table using "sqlmigrate"

- alter it to a CREATE VIEW with the additional fields, etc

- clone the Model class definition, add new fields, and set Meta.managed = False, and Meta.db_table to the view name.

- run "makemigrations"

- Add a raw SQL migration using the CREATE VIEW statement.

- run "migrate"

--

Curtis