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
