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 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_FILEINTEGRITY_TEST STORAGE_BLOCK
> > CAM_IN_FRAGMENTLOOP_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
