Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
Am Do., 27. Dez. 2018 um 02:53 Uhr schrieb Mark Johnson < mj10...@googlemail.com>: > (summery of the last messages that were sent as email) > > >> Please add the list of column names after the view name: > >> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ... > >> I have a note to improve the documentation about this point. > > So would the following be true: > > To insure that a constant, proper column resolvement, VIEWs should be > defined in a similar way as an INSERT command where a sub-set of columns > with values is done: > > INSERT INTO gcp_master > (name, longitude,latitude) > SELECT > name, longitude,latitude > FROM populated_places > WHERE name LIKE "roma,%"; > > > CREATE VIEW gcp_master_view > (name, longitude,latitude) AS > SELECT > name, longitude,latitude > FROM populated_places > WHERE name LIKE "roma,%"; > > > --- Final note: > > When creating a VIEW with a list of defined column names, a COLUMN rename > on the underlining TABLE: > > ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO > "position_x"; > ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO > "position_y"; > Correction: ALTER TABLE "main"."populated_places" RENAME COLUMN "longitude" TO "position_x"; ALTER TABLE "main"."populated_places" RENAME COLUMN "latitude" TO "position_y"; > > will result will be: > - rename of the COLUMN of the TABLE > - rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs > - will NOT rename the COLUMN definition of the VIEW > > The final CREATE command will then look like this: > > CREATE VIEW gcp_master_view > (name, longitude,latitude) AS > SELECT > name, "position_x","position_y" > FROM populated_places > WHERE name LIKE "roma,%"; > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
(summery of the last messages that were sent as email) >> Please add the list of column names after the view name: >> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ... >> I have a note to improve the documentation about this point. So would the following be true: To insure that a constant, proper column resolvement, VIEWs should be defined in a similar way as an INSERT command where a sub-set of columns with values is done: INSERT INTO gcp_master (name, longitude,latitude) SELECT name, longitude,latitude FROM populated_places WHERE name LIKE "roma,%"; CREATE VIEW gcp_master_view (name, longitude,latitude) AS SELECT name, longitude,latitude FROM populated_places WHERE name LIKE "roma,%"; --- Final note: When creating a VIEW with a list of defined column names, a COLUMN rename on the underlining TABLE: ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO "position_x"; ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO "position_y"; will result will be: - rename of the COLUMN of the TABLE - rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs - will NOT rename the COLUMN definition of the VIEW The final CREATE command will then look like this: CREATE VIEW gcp_master_view (name, longitude,latitude) AS SELECT name, "position_x","position_y" FROM populated_places WHERE name LIKE "roma,%"; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson < mj10...@googlemail.com>: > Based on ticket > > https://www.sqlite.org/src/tktview?name=43ddc85a63 > > > > However, the column count is not correct. > In my case 2 columns are missing: which should be 19. > After a fresh look at this today, I realized that this error was caused by a faulty VIEW, where the 2 columns were not defined in the VIEW but being used in the TRIGGER. After correcting the VIEW, the error is different: ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO "admin_level"; The lookupName parameter 'zCol' contains the new column name 'admin_level', but is searching for the old column name admin_type, which is not being found. -I-> lookupName -200a- looking_for[NEW.admin_type] pParse->eTriggerOp[116] pTriggerTab->zName[middle_earth_admin_general] -I-> lookupName -201a- looking_for[NEW.admin_type] op[116 !=TK_DELETE[117]] pTriggerTab->zName[middle_earth_admin_general] -I-> lookupName -202a loop - pCol->zName[admin_type]==zCol[admin_level] iCol[3] Error: near line 14: error in trigger vw_ins_middle_earth_admin_general after rename: no such column: NEW.admin_type > > > Mark Johnson > mj10...@googlemail.com > > > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW
Based on ticket https://www.sqlite.org/src/tktview?name=43ddc85a63 --- During work for spatialite, I have also run into this problem using version 3.25.3. Today I updated to 3.26.0 and saw that changes were made that (possibly) makes it easier to pinpoint the problem. With 3.25.3 lookupName was always being called with pTriggerTab->aCol == NULL -I-> lookupName -200b- looking_for[NEW.id_rowid] pTriggerTab->zName[middle_earth_general] *aCol[(nil)] nCol[0] -I-> lookupName -250b- pTriggerTab->zName[middle_earth_general] zTab[NEW] op[116] aCol->zName[NULL] nCol[0] -I-> lookupName -251b- pTriggerTab->zName[middle_earth_general] zTab[NEW] [op != 117] aCol->zName[NULL] -I-> lookupName -299- sqlite3SelectAddTypeInfo cnt[0] rc[1] error[no such column: NEW.id_rowid] -I-> sqlite3SelectPrep -103- sqlite3ResolveSelectNames rc[1] error[no such column: NEW.id_rowid] With 3.26.0 lookupName is being called 2 times, first with NULL and then again pTriggerTab->aCol != NULL. -I-> lookupName -200b- looking_for[(null).id_rowid] pParse->eTriggerOp[116] pTriggerTab->zName[middle_earth_general] *aCol[(nil)] nCol[-1] -I-> lookupName -200a- looking_for[NEW.id_rowid] pParse->eTriggerOp[116] pTriggerTab->zName[middle_earth_general] aCol->zName[id_rowid] nCol[17] However, the column count is not correct. In my case 2 columns are missing: which should be 19. -I-> lookupName -201a- looking_for[NEW.id_belongs_to] op[116 !=TK_DELETE[117]] pTriggerTab->zName[middle_earth_general] aCol->zName[id_rowid] nCol[17] -I-> lookupName -202a loop - iCol[0] pCol->zName[id_belongs_to]==zCol[id_rowid] -I-> lookupName -202a loop - iCol[1] pCol->zName[id_belongs_to]==zCol[id_admin] -I-> lookupName -202a loop - iCol[2] pCol->zName[id_belongs_to]==zCol[name] -I-> lookupName -202a loop - iCol[3] pCol->zName[id_belongs_to]==zCol[admin_type] * missing id_belongs_to -I-> lookupName -202a loop - iCol[4] pCol->zName[id_belongs_to]==zCol[belongs_to_01] * missing id_belongs_to_02 -I-> lookupName -202a loop - iCol[5] pCol->zName[id_belongs_to]==zCol[belongs_to_02] -I-> lookupName -202a loop - iCol[6] pCol->zName[id_belongs_to]==zCol[valid_since] -I-> lookupName -202a loop - iCol[7] pCol->zName[id_belongs_to]==zCol[valid_until] -I-> lookupName -202a loop - iCol[8] pCol->zName[id_belongs_to]==zCol[map_x] -I-> lookupName -202a loop - iCol[9] pCol->zName[id_belongs_to]==zCol[map_y] -I-> lookupName -202a loop - iCol[10] pCol->zName[id_belongs_to]==zCol[srid] -I-> lookupName -202a loop - iCol[11] pCol->zName[id_belongs_to]==zCol[order_selected] -I-> lookupName -202a loop - iCol[12] pCol->zName[id_belongs_to]==zCol[rule_type] -I-> lookupName -202a loop - iCol[13] pCol->zName[id_belongs_to]==zCol[rule_text] -I-> lookupName -202a loop - iCol[14] pCol->zName[id_belongs_to]==zCol[notes] -I-> lookupName -202a loop - iCol[15] pCol->zName[id_belongs_to]==zCol[text] -I-> lookupName -202a loop - iCol[16] pCol->zName[id_belongs_to]==zCol[eur_point] resulting in the error: error in trigger vw_ins_middle_earth_general: no such column: NEW.id_belongs_to. Mark Johnson mj10...@googlemail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
