[sqlite] Intro SQL ?

Well, now I ready for the next step, looking for a intro into SQL... been searching google all night, found hundreds of sites, that start off with a table, but nothing showing, how to create a database, and place it in a directory, or in the same folder, with the sql application. Looking for a

[sqlite] compiling standalone SQLite for Mac users (was Re: Sqlite ( off list ))

Richard, Kindly send the questions to the list, as there are way more knowledgeable SQLite and Mac users on the list than I am... however, I will fumble through an answer below... On Sep 13, 2005, at 6:47 PM, Richard Nagle wrote: Now what command would I use to compile a self standing

Re: [sqlite] Network-based DB performance for Mozilla

On Sep 13, 2005, at 3:19 PM, Brett Wilson wrote: The patch says "improve and support locking on the OSX platform (as well as others)". I see at least some enums in there for MSDOS NFS, etc. Well, looking closer at the code, I'm beginning to think it might very well be a generic solution

Re: [sqlite] Network-based DB performance for Mozilla

The patch says "improve and support locking on the OSX platform (as well as others)". I see at least some enums in there for MSDOS NFS, etc. Can anybody clarify whether this works on other platforms as well? Basically, the question I have about this patch is: if I access the DB from more than

Re: [sqlite] Network-based DB performance for Mozilla

On Sep 13, 2005, at 2:19 PM, D. Richard Hipp wrote: Apple has contributed patches to SQLite that claim to fix this problem. Those patches may one day find their way into the default release. In the meantime, you can find the patches at: I'm not positive, but I think the Apple patches are

Re: [sqlite] Network-based DB performance for Mozilla

On Tue, 2005-09-13 at 14:09 -0700, Brett Wilson wrote: > I'm still concerned about the warnings on the web page about some > networked file systems not supporting locking. There will be multiple > DB connections from the same process. They might even be > multithreaded. Might we have a problem in

Re: [sqlite] Installer Question ?

On Sep 13, 2005, at 4:03 PM, Richard Nagle wrote: .. G4:~/desktop/sqlite-3.2.5/bld rn$ sudo make install Password: tclsh ../tclinstaller.tcl 3.2 /usr/bin/install -c -d /usr/local/lib ./libtool --mode=install /usr/bin/install -c libsqlite3.la /usr/local/lib /usr/bin/install -c

Re: [sqlite] Installer Question ( oops )

Found it... usr/local/bin sqlite3 I guess, I need to build a .bash_profile with path, and add this path. Oh, since I have the build in bld dir of sqlite, is that a compiled version? or just a build version, I can see the installed parts of sqlite, but was wonder, how would one make a .dmg

Re: [sqlite] Network-based DB performance for Mozilla

I'm still concerned about the warnings on the web page about some networked file systems not supporting locking. There will be multiple DB connections from the same process. They might even be multithreaded. Might we have a problem in this case? Brett On 9/13/05, Roger Binns <[EMAIL PROTECTED]>

[sqlite] Installer Question ?

G4:~/desktop/sqlite-3.2.5/bld rn$ ls Makefilecallback.lo func.o main.lo parse.c random.o trigger.o vdbeaux.lo alter.locallback.o hash.lo main.o parse.h

Re: [sqlite] any plans for out-of-row blobs?

- Original Message - From: "Kurt Welgehausen" <[EMAIL PROTECTED]> To: Sent: Tuesday, September 13, 2005 12:46 PM Subject: Re: [sqlite] any plans for out-of-row blobs? There is no way to retrieve part of a blob value in SQLite. From my understanding, most

Re: [sqlite] any plans for out-of-row blobs?

> There is no way to retrieve part of a > blob value in SQLite. > > From my understanding, most databases store > blob values separate from the rest of the row. The common way to handle this in SQLite is to store the blob in a file and store the file name in the db. Regards

[sqlite] any plans for out-of-row blobs?

I need to retrieve parts of the Blob column value. The blob may simply be to large for memory at runtime. There is no way to retrieve part of a blob value in SQLite. From my understanding, most databases store blob values separate from the rest of the row. Would that be a possibility for

Re: [sqlite] Network-based DB performance for Mozilla

How about reading it all into :memory: and attaching the network database with a trigger so that when an update is made to the :memory: database the change is reflected to the network copy. -- Joel Lucsy "The dinosaurs became extinct because they didn't have a space program." -- Larry Niven

Re: [sqlite] Network-based DB performance for Mozilla

>I don't think it will matter what database you use if you're forced to have > >it hosted over a network. > >As far as I know they all rely on the underlying locking mechanism in the > >OS, which is inherently slow over a network. > > > >Can you avoid multiple locking latency hits by just getting

Re: [sqlite] Network-based DB performance for Mozilla

Brett Wilson wrote: Hi everybody, I'm working on replacing a lot of Firefox's storage systems to use sqlite. It has been going well so far except for one issue. The database file is stored in the user's Mozilla profile directory. In companies and Universities using Linux, this directory is

Re: [sqlite] Network-based DB performance for Mozilla

Jay Sprenkle wrote: I'm glad to see someone is working on this since it was a very noticable problem for me. I don't think it will matter what database you use if you're forced to have it hosted over a network. As far as I know they all rely on the underlying locking mechanism in the OS,

Re: [sqlite] Network-based DB performance for Mozilla

This was brought up but seems like a bad idea for several reasons. First, the database could get pretty big. This would really kill start up and shut down times, which is very important to FF users. Second, what would happen if we crash? One possibility is if we think we'll be doing a lot of UI,

Re: [sqlite] how to store date/time in sqlite?

On Sep 13, 2005, at 11:07 AM, Mark Wyszomierski wrote: Moving from a mysql database to sqlite. I had some date/time fields in my mysql database. I would just populate them using the now() function. How could I achieve the same in my new sqlite database? This page might be helpful:

Re: [sqlite] how to store date/time in sqlite?

Ah excellent, thanks Jay, Mark On 9/13/05, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > See the wiki section of the documentation on the web site. There's a page > devoted to this. > > On 9/13/05, Mark Wyszomierski < [EMAIL PROTECTED]> wrote: > > > > Hi all, > > Moving from a mysql

Re: [sqlite] how to store date/time in sqlite?

See the wiki section of the documentation on the web site. There's a page devoted to this. On 9/13/05, Mark Wyszomierski <[EMAIL PROTECTED]> wrote: > > Hi all, > Moving from a mysql database to sqlite. I had some date/time fields in my > mysql database. I would just populate them using the

Re: [sqlite] Network-based DB performance for Mozilla

I'm glad to see someone is working on this since it was a very noticable problem for me. I don't think it will matter what database you use if you're forced to have it hosted over a network. As far as I know they all rely on the underlying locking mechanism in the OS, which is inherently slow

Re: [sqlite] Network-based DB performance for Mozilla

To what extent is the database shared (either intentionally or unintentionally)? Or, put another way, do you have an option to cache data locally? Since you are restricting access to the profile to a single process at a time, your best bet is probably to make a local copy of the DB during

Re: [sqlite] sqlite3_errCode and error handling

I submitted a bug with a similar problem a while back: http://www.sqlite.org/cvstrac/tktview?tn=1366 the bug was closed with this comment by drh: "SQLITE_CORRUPT gets returned when you call sqlite3_reset() or sqlite3_finalize(). This is unfortunate, I know. We should have designed the API

[sqlite] how to store date/time in sqlite?

Hi all, Moving from a mysql database to sqlite. I had some date/time fields in my mysql database. I would just populate them using the now() function. How could I achieve the same in my new sqlite database? Thanks! Mark

[sqlite] test suite failures

Hi All, I just built the latest SQLite from CVS head (3.2.5+) on Windows using MinGW. This is the same build process I normally use with no problems. When I run the test suite I am getting failures on some tests as shown below: 12 errors out of 13455 tests Failures on these tests:

[sqlite] Network-based DB performance for Mozilla

Hi everybody, I'm working on replacing a lot of Firefox's storage systems to use sqlite. It has been going well so far except for one issue. The database file is stored in the user's Mozilla profile directory. In companies and Universities using Linux, this directory is often hosted over the

RE: [sqlite] Pre-allocating disk space to avoid db file fragments

This problem can be remedied by defragging the hard disk from time to time. Copying the whole db file might also do. But I'm thinking if it's possible to prevent this problem from happening, or reduce the chances of getting fragmented? Sqlite can use free pages that were originally occupied by

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

Jay Sprenkle wrote: On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: Actually, you can defrag the database file yourself, if you have admin rights (b/c you need to open a handle to the physical device). I thought he needed an automated solution to include in his code released to

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: > > Actually, you can defrag the database file yourself, if you have admin > rights (b/c you need to open a handle to the physical device). > > I thought he needed an automated solution to include in his code released to users. -- --- The

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

Jay Sprenkle wrote: On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: It depends on lots of things: the OS, the filesystem, the % free space on the file system, other processes that are causing the OS to allocate disk blocks. I have noticed that Windows XP totally sucks at keeping files

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

A small warning with running VACUUM too often. Any predefined statements will fail if they are defined before the VACUUM and used afterwards. I had a daemon which did a VACUUM autonomously. Which occasionally coincided with a user request, and broke it :) Jay Sprenkle wrote: On 9/13/05,

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: > > > > It depends on lots of things: the OS, the filesystem, the % free space > on the file system, other processes that are causing the OS to allocate > disk blocks. I have noticed that Windows XP totally sucks at keeping > files fragment

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

On 9/13/05, Ray Mosley <[EMAIL PROTECTED]> wrote: > > Is it overkill to VACUUM every time the last user terminates? It depends. If your program is very active rearranging database records every time it runs probably not.

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

Jay Sprenkle wrote: On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: Even vacuuming won't defrag the file. Disk space is allocated by the OS and the OS makes no guarantees. Won't Dr. Hipp's method of making a backup copy also defrag the file? i.e. execute begin exclusive to

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

Is it overkill to VACUUM every time the last user terminates? On 9/13/05, Ben Clewett <[EMAIL PROTECTED]> wrote: > > An old COBOL system we had did this. It never allocated less than 64 > blocks of disk space. It did work. > > A lot of modern file systems (eg, EXT2 and EXT3) do this anyway by

Re: [sqlite] Problem with DETACH on 2.8.16

D. Richard Hipp wrote: On Tue, 2005-09-13 at 09:08 +0200, Laurent wrote: I get the error : Assertion failed: xHash!=0, file hash.c, line 299 Abnormal program termination I tried the same sequence of commands and it worked fine for me. Richard, I can confirm Laurent's

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: > > > > Even vacuuming won't defrag the file. Disk space is allocated by the OS > and the OS makes no guarantees. Won't Dr. Hipp's method of making a backup copy also defrag the file? i.e. execute begin exclusive to lock it. copy the file

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

An old COBOL system we had did this. It never allocated less than 64 blocks of disk space. It did work. A lot of modern file systems (eg, EXT2 and EXT3) do this anyway by reserving space after your file for later use. So if you are using a file system with plenty of free space, file

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

Jay Sprenkle wrote: On 9/13/05, GreatNews <[EMAIL PROTECTED]> wrote: Hi D. Richard Hipp, I'm developing a desktop rss reader using your excellent sqlite engine. One issue my users found is that sqlite database can get heavily fragmented over time. I'm wondering if it's a viable

Re: [sqlite] Problem with DETACH on 2.8.16

Hello Richard,I'm sorry, but if I do :1. Create a new directory.2. Download sqlite 2.8.16 (Windows version for sqlite.org).3. Create 2 files script1 and script2 (see attached):4.  Run the commands:  sqlite x1.dbx

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

I think his issue is that the database is changing size too often. He wants it to automatically expand in larger chunks so there is less fragmentation on the disk. Good idea, assuming it's settable via pragma. On 9/13/05, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On 9/13/05, GreatNews <[EMAIL

Re: [sqlite] Pre-allocating disk space to avoid db file fragments

On 9/13/05, GreatNews <[EMAIL PROTECTED]> wrote: > > Hi D. Richard Hipp, > > I'm developing a desktop rss reader using your excellent sqlite engine. > One > issue my users found is that sqlite database can get heavily fragmented > over > time. I'm wondering if it's a viable suggestion that

[sqlite] Pre-allocating disk space to avoid db file fragments

Hi D. Richard Hipp, I'm developing a desktop rss reader using your excellent sqlite engine. One issue my users found is that sqlite database can get heavily fragmented over time. I'm wondering if it's a viable suggestion that sqlite pre-allocates disk space when creating database, and grows the

Re: [sqlite] Problem with DETACH on 2.8.16

On Tue, 2005-09-13 at 09:08 +0200, Laurent wrote: > I get the error : > >Assertion failed: xHash!=0, file hash.c, line 299 >Abnormal program termination > I tried the same sequence of commands and it worked fine for me. -- D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Problem with DETACH on 2.8.16

Hello, I'm using sqlite 2.8.16. I have 2 databases, x1.dbx and x2.dbx, created with the same commands: create table documents (a); create index i on documents(a); Then, if I run : attach 'x1.dbx' as d1; attach 'x2.dbx' as d2; detach d1; I get the error : Assertion failed: