Re: [sqlite] alter table, other alter category, fails in presence of trigger on 3.25.2
Unrelated, but are you sure that you want the albums "id int primary key" and did not happen to misspell "integer" so that the declaration should be "id integer primary key". In the former case, id is an integer that just happens to be unique (ie, "id int primary key" is the same as "id integer unique") and not an explicitly named alias for the rowid (which requires correct spelling of the phrase "integer primary key"). Second unrelated, do you not want an affinity for the album_id column in rates? Should not you have declared it as "album_id integer references albums(id) on delete cascade"? Third unrelated, do not forget to create an index on the foreign key (as in "CREATE INDEX idxRates_album_id on rates (album_id)" for example). Fourth unrelated, do you want the title and comment_text to be case sensitive or should they have COLLATE NOCASE? As to the issue with the updated table rename, you can either use a version of sqlite3 that does not have the alter table rename updates, or for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid using the new "change the table names in triggers etc" features added in 3.25.0 so that you can continue to use the old method of just "substituting tables". --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Thierry Henrio >Sent: Wednesday, 3 October, 2018 16:43 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] alter table, other alter category, fails in >presence of trigger on 3.25.2 > >Hello, > >I want to report a bug, I checked >https://www.sqlite.org/src/rptview?rn=8 >and did not find the same? > >Let schema be: > >sqlite> .schema >CREATE TABLE albums (id int primary key, title text, score int); >CREATE TABLE rates (album_id references albums(id) on delete cascade, >comment text, score int); >CREATE TRIGGER test after insert on rates begin update albums set >score=new.score where id=new.album_id; end; > >And I want to make albums.title not null. >This falls into >https://www.sqlite.org/lang_altertable.html#otheralter. > >sqlite> BEGIN; >sqlite> CREATE TABLE new_albums (id int primary key, title text not >null, >score int); >sqlite> INSERT INTO new_albums (id, title, score) SELECT id, title, >score >FROM albums; >sqlite> DROP TABLE albums; >sqlite> ALTER TABLE new_albums RENAME TO albums; >Error: error in trigger test: no such table: main.albums > >Expected behavior is last alter is ok. > >This is in 3.25.2 > >sqlite> select sqlite_version(); >3.25.2 > >Cheers, Thierry >___ >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] alter table, other alter category, fails in presence of trigger on 3.25.2
Hello, I want to report a bug, I checked https://www.sqlite.org/src/rptview?rn=8 and did not find the same? Let schema be: sqlite> .schema CREATE TABLE albums (id int primary key, title text, score int); CREATE TABLE rates (album_id references albums(id) on delete cascade, comment text, score int); CREATE TRIGGER test after insert on rates begin update albums set score=new.score where id=new.album_id; end; And I want to make albums.title not null. This falls into https://www.sqlite.org/lang_altertable.html#otheralter. sqlite> BEGIN; sqlite> CREATE TABLE new_albums (id int primary key, title text not null, score int); sqlite> INSERT INTO new_albums (id, title, score) SELECT id, title, score FROM albums; sqlite> DROP TABLE albums; sqlite> ALTER TABLE new_albums RENAME TO albums; Error: error in trigger test: no such table: main.albums Expected behavior is last alter is ok. This is in 3.25.2 sqlite> select sqlite_version(); 3.25.2 Cheers, Thierry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DB To DB Transfer Time
> Why aren't you measuring the time spent only in the SQLite writing thread? > > That would eliminate the overhead from the read-thread(s) and the queue in > the middle, > measuring only work by SQLite, instead of including the *waiting* for work > in the queue. > > I wrote a similarly "piped" ETL not too long ago, and I output overall > "wall" time of course, > but also time spent reading, time spent writing, but important times when > reader/writer threads > were "stalled", because the pipe is full/empty (respectively). --DD Hello, Too the question, because it is unnecessary coding, time wasted. If I have an idea already of the goal and with the timing overall can determine where to focus the effort it is more efficient use of my time. The monitoring of the pipe, one coding action, already gives me an idea of when read/writer are stalled. So no need in to have timing for those. Yesterday I put the threads on equal footing and derived the following result which are close to my goal. 50,000 rows queried from a networked MariaDB, fields (integer, real, text, blob). SQLite - 114.838 seconds H2 - 115.868 seconds Derby - 136.984 seconds HSQL - 1291.808 seconds Mind you that the plugin needs to use any query to any of the supported databases, MariaDB, Oracle, PostgreSQL, etc. and derive a comparable schema table from the query to create a file/memory database. Looks like SQLite or H2 are the most likely candidates at this time. All of this is a lot of variables that effect timing. Focusing on only the data transfer timing, above, or writes to the sink db is only part of the timing, though probably the place to derive the most benefit. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DB To DB Transfer Time
On Tue, Oct 2, 2018 at 6:56 PM dmp wrote: > >> On Sep 25, 2018, at 11:14 AM, dmp > wrote: > The timing given takes place at the beginning of the transfer > process and stops when completed, threads join. > Why aren't you measuring the time spent only in the SQLite writing thread? That would eliminate the overhead from the read-thread(s) and the queue in the middle, measuring only work by SQLite, instead of including the *waiting* for work in the queue. I wrote a similarly "piped" ETL not too long ago, and I output overall "wall" time of course, but also time spent reading, time spent writing, but important times when reader/writer threads were "stalled", because the pipe is full/empty (respectively). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
