Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[James K. Lowden]

On Tue, 11 Feb 2014 16:49:50 -0500
Stephen Chrzanowski  wrote:

> I don't like the idea of letting the software decide what should be
> done based on a configuration file.  

Hmm, isn't it the other way around?  Does the user tell the software
what to do via a configuration file?  

~/.sqliterc already exists.  I merely suggested using it, instead of a
compiled-in constant should the user so desire, to determine the
location of the default database.  

> may not even have ${HOME}

I just don't see how that matters to a sqlite3 user working at or
near the command line.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[Tim Streater]

On 11 Feb 2014 at 21:49, Stephen Chrzanowski  wrote:

> I don't like the idea of letting the software decide what should be done
> based on a configuration file.

The .sqliterc file already exists, so you're too late.

> Linux and Windows both can isolate processes from each other, be
> completely independent, and not worry about pesky configuration files.
> Yeah most linux distros do have a /home directory, but some 'root only'
> devices don't, or may not even have ${HOME} defined as per James suggested.

Who is going to be running a CLI program on such devices?

> If you're going to go as far as throwing parameters at
> sqlite3.exe/sqlite3.so/sqlite3.whatever to indicate where a configuration file
> is, you might as well start including the database you want to write to.

AIUI, the suggestion is for a change to the CLI program only. And it already 
knows where the .sqliterc configuration file is to be found, at least on OS X. 
I imagine it'll be the same place for linux, I've no idea about Windows.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[Stephen Chrzanowski]

Lets not throw honey at the problem when a bear is around.  Some of the
things I've seen in this thread just makes it sound like the kitchen sink
should be included in this application.

I don't like the idea of letting the software decide what should be done
based on a configuration file.  Linux and Windows both can isolate
processes from each other, be completely independent, and not worry about
pesky configuration files.  Yeah most linux distros do have a /home
directory, but some 'root only' devices don't, or may not even have ${HOME}
defined as per James suggested.  If you're going to go as far as throwing
parameters at sqlite3.exe/sqlite3.so/sqlite3.whatever to indicate where a
configuration file is, you might as well start including the database you
want to write to.  With this, you're throwing additional code on a fully
multiplatform compatible application that has to be aware of checks and
balances against every OS this CAN be run on.  Overkill when a simple note
to the user will do.

I'm all for the notification, sans color/colours/fonts.  If needed, throw a
few asterisks before and after a warning indicating that pending a load,
anything done is written to an in-memory database, and be done with it.

I'm not for over-complicating a simple thing that the user needs to be
notified of.  Maybe lose the colors if necessary for some terminals, but
all in all, I think what Richard posted earlier is sufficient for the cause.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[Nico Williams]

On Mon, Feb 10, 2014 at 10:30 PM, Simon Slavin  wrote:
> On 11 Feb 2014, at 4:06am, James K. Lowden  wrote:
>
>> ${HOME}/.sqlite/db would be my choice.
>
> Since the objective is not to let a naive user unexpectedly lose the data, it 
> might seem a bad idea to put the file in a directory which is hidden from 
> naive users.

I don't get this.  Why should the user know where it lives when they
can just start the shell and there's the data?

I like James' idea, even though it will be somewhat surprising.

The behavior could be made to be opt-in and then have /etc/skeleton on
*nix distros include the opt-in (or not).  As to Windows... I can see
that double-click detection there might be nice.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] on insert new row, increment date with a month

[Gert Van Assche]

This is what I came up with -- it seems to work but I don't know how safe
it is:

CREATE TRIGGER [AddMonthToAPSGEN]
AFTER INSERT
ON [APS_Generale]
BEGIN
 UPDATE APS_Generale SET nDate = date((SELECT nDate FROM APS_Generale
WHERE rowid=(SELECT max(rowid)-1 FROM APS_Generale)),'start of month','+1
month')
 WHERE nDate IS NULL;
END

thanks all for your help,

Gert


2014-02-11 19:12 GMT+01:00 Petite Abeille :

>
> On Feb 11, 2014, at 7:07 PM, Gert Van Assche  wrote:
>
> > Does anyone know if it is possible for a date field to be automatically
> > incremented with a month when a new record is created?
>
> If you are looking for something wacky, triggers are where to look:
>
> http://www.sqlite.org/lang_createtrigger.html
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Gert Van Assche
*skype: gertva*
*phone: +32 498 84 44 75*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] on insert new row, increment date with a month

[RSmith]

On 2014/02/11 20:07, Gert Van Assche wrote:

All,

Does anyone know if it is possible for a date field to be automatically
incremented with a month when a new record is created?

If the last row contains "2013-01-01" in the DateField then the DateField
of the new row should automaticllay be "2013-02-01".


Updating a value is easy, a trigger can do that. Increasing a date by a month 
is very easy.
Finding the last added date, not so easy...
It is of course possible with a query, when making some assumptions, but I would suggest keeping it somewhere else. However, in the 
interest of expedience, I will assume that the date that should be incremented is the very last added date, I will also assume the 
date field is a Key (or Index if you will) and called nDate, and I will assume the the primary Index is called ID and it is 
autoincremented and lastly assume the table is called Table1 - if these assumptions are all true, you could possibly do it like this:


CREATE TRIGGER IF NOT EXISTS Trig_Table1_setLastDate
  AFTER INSERT ON Table1 FOR EACH ROW
BEGIN
  UPDATE Table1 SET nDate = (SELECT date(C.nDate,'+1 month') FROM `Table1` AS C 
 ORDER BY C.ID DESC  LIMIT 1) WHERE ID = NEW.ID;
END;

Note that if the ID is not auto-incremented, it might end up being sorted wrongly and the highest ID might not be the very last 
added ID. You can sort by date too if the last added date is definitely the highest date, etc. The DESC makes sure the highest is 
sorted first, and the limit makes sure we pick only the 1st highest.  The C alias makes sure the SQL engine does not get confused 
with the sub-query table.  The Trigger makes sure that whatever new line is added that the new ID's record get the update.


Pretty straight forward _IF_ all the assumptions hold true - else you may need 
some more tricks to do it.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] on insert new row, increment date with a month

[Petite Abeille]

On Feb 11, 2014, at 7:07 PM, Gert Van Assche  wrote:

> Does anyone know if it is possible for a date field to be automatically
> incremented with a month when a new record is created?

If you are looking for something wacky, triggers are where to look:

http://www.sqlite.org/lang_createtrigger.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] on insert new row, increment date with a month

[Gert Van Assche]

All,

Does anyone know if it is possible for a date field to be automatically
incremented with a month when a new record is created?

If the last row contains "2013-01-01" in the DateField then the DateField
of the new row should automaticllay be "2013-02-01".

Thanks

gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error: "File is encrypted or is not a database"

[Iksanov Husain]

- We encrypted our database with common System.Data.SQLite method -
public void ChangePassword(string newPassword) -
in SQLiteConnection .NET class.
- No, there were no changes in the system.
- We tried SQLite shell tool. Does it open an encrypted database? Honestly, we 
found no info about it. Anyway, it seems like ".open" command (with no password 
settings) works correctly, but any query returns "file is encrypted or is not a 
database" error.
- And we tried to open this DB on different computers. Bad luck.
- One more attempt in SQLiteManager - a Firefox plugin:
"Error in opening file data - either the file is encrypted or corrupt
Exception Name: NS_ERROR_FILE_CORRUPTED
Exception Message: Component returned failure code: 0x8052000b 
(NS_ERROR_FILE_CORRUPTED) [mozIStorageService.openUnsharedDatabase]"
 

Пятница, 7 февраля 2014, 15:35 UTC от Simon Slavin :
On 7 Feb 2014, at 10:49am, Iksanov Husain  wrote:
> We have an encrypted SQLite database with a password which is set 
> programmatically in .NET interface.
Can you tell us which encryption system you used to encrypt your database ?
Did you change anything in the last day or two: updated operating system, or 
new DLL ?
If you put a copy of the SQLite shell tool on that computer, will it open the 
file ?
If you move the database to a different computer can you open it ?
Simon.

-- 
Хусаин Иксанов
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free Page Data usage

[Hick Gunter]

You may enjoy reading up on Btrees here:

http://en.wikipedia.org/wiki/B-tree

-Ursprüngliche Nachricht-
Von: Raheel Gupta [mailto:raheel...@gmail.com]
Gesendet: Montag, 10. Februar 2014 10:49
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Free Page Data usage

>> Note that choosing a page size smaller than the typical row size
>> means
that the bottom level of the BTree degrades to 1 row per node.
What do you mean by this ? How is a smaller page bad for the database ?


On Mon, Feb 10, 2014 at 2:43 PM, Hick Gunter  wrote:

> With a record size of a little over 4K (note that the on-disk space
> requirement of a integer+4k Blob row is not always 4k+8) and a page
> size of 2K you are storing 1 row in 3 pages (close to 50% overhead).
> Deleting a record will give you 3 pages of free space, which will be
> reused quickly; some of it for the higher levels of the B-Tree. Note
> that choosing a page size smaller than the typical row size means that
> the bottom level of the BTree degrades to 1 row per node.
>
> Changing to 4k or 8k will increase overhead to near 100% (as you now
> need
> 2 Pages of 4k or one page of 8k for each row).
>
> 16k pages ( 3 rows/page) reduce this to 25.00% while deleting 5
> adjacent rows is guaranteed to free up 1 page.
> 32k pages ( 7 rows/page) reduce this to 12.50% but requires 13
> adjacent deletes for 1 guaranteed free page.
> 64k pages (15 rows/page) reduce this to  6.25% but requires 29
> adjacent deletes for 1 guaranteed free page.
>
> You can choose the source of fragmentation: loosing close to 1 row per
> page (better in bigger pages) or having ununsed space due to
> nonadjacent deletes (better in smaller pages)
>
> -Ursprüngliche Nachricht-
> Von: Raheel Gupta [mailto:raheel...@gmail.com]
> Gesendet: Montag, 10. Februar 2014 07:14
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Free Page Data usage
>
> Hi,
>
> I tried the same database I had and used a 2KB page size.
> It works much faster and also the pages are reused immediattly to the
> extent of 95%.
>
> If only the number of pages could be increased somehow. Does anyone
> think its practical to make the pageNo from a 32 bit int to a 64 bit
> Unsigned Integer.
>
> I do understand that VACUUM is not a good option for me.
>
>
>
> On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin  wrote:
>
> >
> > On 9 Feb 2014, at 10:45am, RSmith  wrote:
> >
> > > On 2014/02/09 12:06, Raheel Gupta wrote:
> > >> Sir, I have only one auto increment primary key.
> > >> Since the new rows will always have a higher number will the
> > >> pages which have some free space by deleting rows with lower
> > >> numbered keys
> > never
> > >> be reused ? e.g. If row with ROWID "1" was deleted and freed,
> > >> will it
> > not
> > >> be used to store the NEW row which will be assigned ROWID 10001 ?
> > >
> > > Yes. That is the point of AutoIncrement, every new Key will always
> > > be
> > higher than any previous key ever used, and always exactly one
> > higher than the highest ever previously used key. As such, it cannot
> > be re-used within pages that are half filled from deletion (except
> > maybe the last page), and I believe pages that go completely empty
> > may be re-used without the need to vacuum etc. (need someone to confirm 
> > this).
> >
> > You are correct, depending on this PRAGMA:
> >
> > 
> >
> > auto_vacuum = NONE
> >
> > A page which has all its data deleted is added to the 'free pages'
> > list and eventually reused.
> >
> > auto_vacuum = FULL
> >
> > A page which has all its data deleted is replaced by the last page
> > of the file.  The file is then truncated to release the space of the
> > last page for use in other files.
> >
> > auto_vacuum = INCREMENTAL
> >
> > A page which has all its data deleted is replaced by the last used
> > page of the file.  When you issue "PRAGMA incremental_vacuum(N)" the
> > file is truncated to release unused pages at the end for use in
> > other
> files.
> >
> > As in previous discussion, all this is about reclaiming space at the
> > page
> > level: releasing entire pages of space.  It has nothing to do with
> > reclaiming space within a page.  And also as in previous discussion,
> > the fastest of these is "auto_vacuum = NONE".  Copying one page to
> > another, releasing filespace and claiming it back again are slow and
> > require much reading and writing.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
> 
>  Gunter Hick
> Software Engineer
> 

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

[Dominique Devienne]

On Mon, Feb 10, 2014 at 9:23 PM, Richard Hipp  wrote:

> (1) Reword the banner to make it more terse and to try to avoid
> "banner fatigue".
>

Thanks.


> (2) If opened with no command-line arguments (and hence on an in-memory
> database) output a warning in the banner.
>
>(a)  On unix the warning is bold.
>(b)  On windows the warning is bold and red.
>(c)  The warning suggests the use of ".open FILENAME"
>

That's very terminal specific and doesn't play nice with color blindness,
nor if someone had a DOS window with red background (someone out there has
a good reason for that).

There's a reason IMHO some Unix commands have an explicit opt-in --color
switch.

So unless you add such a --color switch to sqlite3 itself, I'd stick with
normal text, as ever, and as default.

If sqlite3 could live this long w/o errors written out in red, it surely
can also survive w/o bold and/or red warning text.


> (3) Added the ".save" command as an alias for ".backup".  But there is no
> mention of this in the banner.  (Trying to keep the banner succinct.)
>
> There is no warning prior to close because (in my experience) most users
> will do a hard kill of some kind (Ctrl-D or closing the console window) in
> which case no warning is possible.


I for one often start sqlite3 at the command line w/o arguments, for
experimentation, and .quit properly, not using a hard kill, and would
rather not have such an exit warning indeed.

In fact, maybe sqlite3 should gain a -q (--quiet) switch to avoid any
banner or training-wheels warning, given the new trend on colorful warnings?

My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[Stephan Beal]

On Tue, Feb 11, 2014 at 5:06 AM, James K. Lowden
wrote:

> > (1) Detect double-click launch by looking at argc and argv.
>
> Why make this a special case?  If no database name is provided,
> the behavior should be the same regardless of how launched or what OS.
> Easier to explain; easier to understand.
>

And also a potential security regression for those who have grown to (or
have scripts which) expect their ultra-secret data to be discarded by
default. Privacy advocates just love that type of thing: "if you enter the
following SQL into an sqlite3 console, then save your credit card number
there, it will be silently saved in your home directory where other users
can potentially read it."

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users