Re: [sqlite] Quick way to determine optimal page size?
On 2 Aug 2019, at 8:55pm, Jen Pollock wrote: > Compressing the backups would likely save you a lot more space, and I > suspect it wouldn't be affected that much by page size; presumably empty > space at the ends of pages will compress very well. It might be an interesting exersize to compare the sizes of these files: A) size of .sqlite file B) use the shell tool to dump SQL commands as .sql C) compress (A) using some standard compression tool D) compress (B) using the same tool Results could differ depending on the proportion of the data which is numeric. It's the sort of thing I'm sure I would have tried years ago but I don't remember what results I got. And I no longer have access to big SQLite databases. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to determine optimal page size?
Compressing the backups would likely save you a lot more space, and I
suspect it wouldn't be affected that much by page size; presumably empty
space at the ends of pages will compress very well.
Jen Pollock
On Thu, Aug 01, 2019 at 12:48:27AM +0300, Tony Papadimitriou wrote:
> Tens of databases (from a few MBs to almost GB), so it's good to keep them
> at their minimum size (for disk and backup savings).
>
> I often save several megabytes by going to the 'right' size, eg., just today
> I went from ~110MB down to ~80MB in one of them ('vacuum'ed before and after
> so it's just the page size making this difference). Sometimes, very small
> page sizes give best results, sometimes the other way around.
>
> Some databases do well in the same page size as new data is added, but for
> some others you need to recalculate as their content changes.
> Still, you can't know in advance which ones can do better unless you
> actually try it. And, that's the main problem.
> I have to try with ~100 DBs to get a significant benefit in just a few of
> them (about 5-10), until next time.
>
> Anyway, I thought I'd ask.
>
> -Original Message- From: David Raymond
> Sent: Wednesday, July 31, 2019 10:48 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Quick way to determine optimal page size?
>
> Not that I'm aware of no. How much of a difference are you seeing for your
> database size depending on the page size you try?
>
> -Original Message-
> From: sqlite-users On Behalf
> Of Tony Papadimitriou
> Sent: Wednesday, July 31, 2019 3:29 PM
> To: General Discussion of SQLite Database
>
> Subject: [sqlite] Quick way to determine optimal page size?
>
> Instead of brute force “pragma page_size=xxx; vacuum;” for each page size
> and each database to determine which one produces the smallest file, is
> there some quicker way?
>
> Thanks.
>
> ___
> 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
Re: [sqlite] Large database backup
Thanks for your help! Especially the comment about cache size helped a lot. When I reduced the cache size to about 100 pages I actually get it do the writing incrementally in multiple sqlite3_backup_step() calls. With bit more finetuning I think it will be ok. I also had unrelated problem of using same database handle for backup and some other things at the same time which understandably caused additional blocking. After these changes it is now performing very well. Thanks! Toni Tammisalo ttamm...@iki.fi ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
I totally agree with your answer. But this wasn't really the question. > You have hacked around this security feature I beg you to try to look at my "hacks" with a fresh eye. The service they provide is a genuine one: be able to run raw SQL requests, and also to be notified when one has committed changes in the results of another. I suppose you know that most high-level libraries in GUI platforms embed such database observation features. This is part of the expected tool belt these days. It happens that a security feature has been rerouted for another purpose. This other purpose sheds a new light on authorizers. In GRDB, statements are always "authorized": applications want to manage *their* database, so there is no point restricting access to the database. There is no need for the security side of SQLite authorizer. There is need for the statement inspection features provided by SQLite authorizers (what will be read/written). And prevention of the truncate optimization. Now that I hope I have better explained where I talk from, I hope you will read again my previous question. Thanks in advance, Gwendal Roué ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
On 8/2/19, Gwendal Roué wrote: > > Do you think this can still be seen as a misuse of the library? > Forcing a reprepare after an authorizer change is a security feature. It helps to prevent people from adding an authorizer, but then mistakenly using a statement that was prepared before the authorizer was added, thinking that the authorizer prevented that statement from leaking sensitive information or harming the database, when it does not. You have hacked around this security feature. As long as you are careful to never use a prepared statement that was created using a lax authorizer, then you will be fine. But if you mess up, and accidentally use a prepared statement with an incorrect authorizer, and that statement leaks information or allows unauthorized changes to the database, then no tears. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
For the record, I have successfully refactored my code so that authorizer is set only once, and the issue has disappeared. Yet, this authorizer not always returns the same value for the same input. Precisely speaking, it always return SQLITE_OK, but when asked if it should allow deletion. In this case, it may return SQLITE_IGNORE or SQLITE_OK during compilation (prepare), and SQLITE_IGNORE or SQLITE_OK during execution (step). All four combinations happen, depending on whether this is a DELETE statement with truncate optimization enabled or disabled, or a DROP statement. Do you think this can still be seen as a misuse of the library? Some insights would be appreciated. Thanks in advance On Thursday, August 1, 2019, Richard Hipp wrote: > On 8/1/19, Gwendal Roué wrote: > > > > 1. set authorizer > > 2. compile statement > > 3. reset authorizer > > 4. step > > 5. set authorizer (and do something else) > > 6. step -> SQLITE_ABORT_ROLLBACK > > Please test to see if changing the "0" to a "1" on the line of code > shown below fixes the problem, and report back. > > https://www.sqlite.org/src/artifact/0fac710388?ln=81 > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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
Re: [sqlite] mode insert dumps
> Le 2 août 2019 à 10:04, Luca Ferrari a écrit : > > I've got a lot of database files that I would like to dump using > INSERT statements. > unluckily, .mode insert does not work for me, or I'm not able to > understand how it works. > > sqlite> select mude_anno, mude_numero from catdaemo; > INSERT INTO table VALUES(2019,1161); Besides the other answers focused on using .mode insert more precisely, the .dump command might prove useful too. — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia https://www.integral.be ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] mode insert dumps
You need to .mode insert SELECT * FROM ; Repeat for all your tables. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Luca Ferrari Gesendet: Freitag, 02. August 2019 10:04 An: SQLite Betreff: [EXTERNAL] [sqlite] mode insert dumps Hi, I've got a lot of database files that I would like to dump using INSERT statements. unluckily, .mode insert does not work for me, or I'm not able to understand how it works. sqlite> select mude_anno, mude_numero from catdaemo; INSERT INTO table VALUES(2019,1161); My questions are: 1) why is the insert operating against "table" instead of the real table I was selecting from (catdaemo)? 2) is there a way to force the INSER to have also the columns I selected listed? I would like something like: INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161); The reason is that I want to exclude automatic keys from my inserts because I'm going to pump those inserts into another database with automatic keys. Thanks, Luca ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] mode insert dumps
On 2/8/62 15:04, Luca Ferrari wrote: Hi, I've got a lot of database files that I would like to dump using INSERT statements. unluckily, .mode insert does not work for me, or I'm not able to understand how it works. sqlite> select mude_anno, mude_numero from catdaemo; INSERT INTO table VALUES(2019,1161); My questions are: 1) why is the insert operating against "table" instead of the real table I was selecting from (catdaemo)? You can add a table name to the ".mode insert": sqlite3> .mode insert catdaemo 2) is there a way to force the INSER to have also the columns I selected listed? I would like something like: INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161); Try doing: sqlite3> .headers on along with the ".mode insert" command. Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] mode insert dumps
Hi, I've got a lot of database files that I would like to dump using INSERT statements. unluckily, .mode insert does not work for me, or I'm not able to understand how it works. sqlite> select mude_anno, mude_numero from catdaemo; INSERT INTO table VALUES(2019,1161); My questions are: 1) why is the insert operating against "table" instead of the real table I was selecting from (catdaemo)? 2) is there a way to force the INSER to have also the columns I selected listed? I would like something like: INSERT INTO catadaemo( mude_anno, mude_numero ) VALUES(2019,1161); The reason is that I want to exclude automatic keys from my inserts because I'm going to pump those inserts into another database with automatic keys. Thanks, Luca ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
