Link to my database in in the original post. https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
Tomasz Jerzykowski W dniu 2019-03-15 14:46:19 użytkownik R Smith <ryansmit...@gmail.com> napisał: > For a start, do not use single quotes for table names. In SQL, DB object > identifiers can either be unquoted or contained in double-quotes. SQLite > specifically even allows backticks or square brackets for compatibility, > but nobody likes single quotes. > > I.e. change this: > > drop table 'fs_params_20291_27910'; > > To this: > > drop table "fs_params_20291_27910"; > > > Another thing SQL cares about is the case you use in names, but again SQLite > allows referring to a table without needing to match the schema case. (Just > putting this out there to satisfy my internal pedantry). > > Lastly, you probably have a trigger on one of these tables you intend to > rename, but the trigger has internally code referring to another table (or > some such) causing the error. Could you please send the full schema of your > DB? > > using the sqlite3 CLI will output it on issuing: .fullschema > > Or post a DB on a download site somewhere if it isn't sensitive. > > > Thanks, > Ryan > > On 2019/03/15 3:29 PM, tjerzyko wrote: > > I'm having corruption problem with a certain database file. You can > > download it here: > > https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing > > It was created with SQLite 3.8.7 or older version (I cannot say for sure > > now) > > I open it with sqlite3.exe and run the following script: > > > > begin; > > alter table fs_params rename to fs_params_20291_27910; > > CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT); > > drop table 'fs_params_20291_27910'; > > alter table file_locks rename to file_locks_27446_24559; > > CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME > > TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID)); > > drop table 'file_locks_27446_24559'; > > CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID); > > alter table cam_in_file rename to cam_in_file_22705_10035; > > end; > > > > It throws the following error: > > Error: near line 9: error in trigger T_FILE_BDELETE: no such table: > > main.file_locks_27446_24559 > > > > Probably my script is incorrect, but another thing worries me more. I open > > the database again and: > > > > e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat > > SQLite version 3.27.2 2019-02-25 16:06:06 > > Enter ".help" for usage hints. > > sqlite> .tables > > AUDIO_IN_FILE FS_ATTRIBUTES REC_RECORDING_MODE > > AUDIO_IN_FRAGMENT FS_PARAMS REC_WORKING_MODE > > CAM_IN_FILE INTEGRITY_TEST STORAGE_BLOCK > > CAM_IN_FRAGMENT LOOP_FRAGMENT T_SCHEDULE > > FILE REC_LATEST_CONNECTION > > FILE_LOCKS REC_LIST_TIMESTAMP > > sqlite> select * from CAM_IN_FILE; > > Error: no such table: CAM_IN_FILE > > sqlite> > > > > The table exists, but not quite. The database seems to be corrupted. > > > > When I tried the same procedure on database created with newer SQLite > > version, there were no such problems. > > > > Tomasz Jerzykowski > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
