"ALTER TABLE ..." looks at other tables
(1) By Gerry Snyder (GSnyder) on 2020-10-11 21:03:18 [link] [source]
My db has a table that I don't use very often that has a custom collation (in Tcl), which I load only when I am going to be using that table. This has not caused any problems until now.
I have been doing some things with the native ALTER TABLE command and it seems that ADD COLUMN is not a problem, but both RENAME TO and RENAME COLUMN fail with errors because of the missing collation in another table.
Is this expected?
I tried creating a tiny example, but the problem did not arise.
The file the situation occurs in passes the INTEGRITY_CHECK, and I use it all the time, but there could be some problem that the check does not see.
My file is 137,312 KB, and I can send it or a link to it if you (drh) care to look into it.
Gerry Snyder
(2) By Richard Hipp (drh) on 2020-10-11 22:00:45 in reply to 1 [source]
Can you send me just the output of the ".schema" command in the CLI?
(3) By Gerry Snyder (GSnyder) on 2020-10-11 22:30:54 in reply to 2 [link] [source]
sqlite> .schema CREATE TABLE blobs(filename text, length integer, time text default current_timestamp, content blob); CREATE TABLE testnocase(a text collate nocase); CREATE TABLE tc(procname text not null on conflict ignore,tcl text,version text,comments text); CREATE TABLE gigo(a text,--comment b int,C real,aa blob,"ggg integer" int, "ggg int" int default 0 not null, abc, xyz, abcxyz, unique(c,aa)); CREATE TABLE norowid(a text, b text primary key) WITHOUT ROWID; CREATE TABLE norowid2(a text, b text, primary key(a,b)) WITHOUT ROWID; CREATE TABLE CenturyClub_countries (country text, visited text, region text); CREATE TABLE CenturyClub_regions (region text); CREATE TABLE animal(type char (100),sound char(100)); CREATE TABLE ot(RegistrationYear,IntroductionYear,Status,Iris_Name,Hybridizer,Ctry,SeedlingNumber,Class,HeightInches,Season,Introducer,Description,Pedigree_Exporter_No_Period); CREATE TABLE cmd_history(cmdtype text,tablename text,created default current_timestamp,command text); CREATE TABLE ancestors_GCS (depth integer, id integer, name_etc text, America_born integer); CREATE TABLE tclcode(procname text not null on conflict ignore unique on conflict ignore,version text,hidden integer default 0,tcl text,comments text); CREATE TABLE ancestors1_GCS (depth integer, id integer, name text, birthdate text, birthplace text, America_born integer); CREATE TABLE OED_index(headword text,position text,length text); CREATE TABLE OED_index_collated(headword text,position text collate compare64,length text); CREATE TABLE OED_index_sorted(headword text,position text collate compare64,length text); CREATE TABLE abc(xyz collation missing); CREATE TABLE def(ghi, jkl); CREATE UNIQUE INDEX cmd_history_index ON cmd_history(cmdtype, tablename, command) WHERE cmdtype != 'patch'; CREATE INDEX OED_index_index ON OED_index(headword); CREATE VIEW procname as select procname from tclcode /* procname(procname) */; CREATE VIEW hidden_views as select command from cmd_history where cmdtype = 'view' and tablename = 'temp' /* hidden_views(command) */; CREATE VIEW dict_view AS SELECT * from OED_index /* dict_view(headword,position,length) */; CREATE VIEW ancestor_depth AS SELECT COUNT(*) FROM ancestors1_GCS GROUP BY depth /* ancestor_depth("COUNT(*)") */; CREATE VIEW ancestor_depth_American AS SELECT depth, COUNT(*) FROM ancestors1_GCS WHERE America_born = 1 GROUP BY depth /* ancestor_depth_American(depth,"COUNT(*)") */; sqlite> alter table gigo rename to ggiiggoo; Error: error in table OED_index_collated: no such collation sequence: compare64
(4) By Dan Kennedy (dan) on 2020-10-12 16:15:41 in reply to 3 [link] [source]
(5) By Gerry Snyder (GSnyder) on 2020-10-12 17:41:47 in reply to 4 [link] [source]
Thank you. It had seemed about a googol times more likely that my s/w was doing something stupid, or that my db had been corrupted (also by my s/w), than that I had found a real problem. I feel relieved. Gerry Snyder