[sqlite] format for column names
Hi all,
I've just joined this mail list.
I've read through the syntax page and other sources as to how to create a table
using SQLite, and it's all working fine. But I can't find any specifications
for the format of a column name. Does the spec permit spaces in the name? It
seems to work OK when creating a table if I enclose the column name in quotes,
eg:
CREATE TABLE People('Name First', 'Name Last')
And getting the list of column names works fine. But select statements don't
seem to work if the column names have spaces.
So, are spaces allowed or not? Is there a specification somewhere that shows
what characters, length etc are allowed in column names (and table names)?
Thanks,
Tom
T
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] count(gid) takes too long
Sripathi Raj wrote: Main question: Using DBD-SQLite, select count(gid) from es_src_media_info takes 130 secs. What gives? Repeatably 0.3 seconds or less here with apsw and python2.4 on Windows XP on a dual Athlon1600 with ~4 year old disks. Same sort of times in the sqlite command line shell. Obviously I don't have real data so I made some up - my database is about 43MB. What are you running on? Do the times change if you use the sqlite shell? Are your discs fragged? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] count(gid) takes too long
Sripathi Raj wrote: Hi, I have a table with 500,000 records. The following is the schema of that table: CREATE TABLE ES_SRC_MEDIA_INFO (GID INTEGER PRIMARY KEY AUTOINCREMENT, MEDIAPATH VARCHAR(256) NOT NULL UNIQUE, BYTES_USED LONG, BYTES_ON_DISK LONG, MTIME LONG, CTIME LONG, TYPE VARCHAR(20), CATEGORY VARCHAR(20), TIMESTAMP LONG, JOBID VARCHAR(30) Main question: Using DBD-SQLite, select count(gid) from es_src_media_info takes 130 secs. What gives? I added an unique index on GID and tried it select count(gid) from es_src_media_info where gid >= 1. It took around 90 seconds this time. Is there any way to speed this up other than adding a trigger? Small question: Does sqlite take the column types and length into consideration while creating the table? Thanks, Raj With or without an index Sqlite has to access every row to get a count. If you want a rapidly accessed count keep a running total. Sqlite ignores the text field sizes and makes them all of type TEXT. In stores integers as 65 bit signed integers and other numbers as 64 bit floating point. Read up on Sqlite "manifest typing" to learn more on how it stores data according to actual type rather than declared type. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling sqlite3 with mingw32
Matias Torres <[EMAIL PROTECTED]> wrote: > Hello everyone i need help on compiling sqlite3 from source in linux > with mingw. That is exactly how I build the windows libraries and executables that appear on the SQLite website. What you do is this: * Ignore the configure script. * Make a copy of the Makefile.linux-gcc as just "Makefile". * Edit "Makefile" so that it specified i386-mingw32msvc-gcc (or whatever your compiler is called) as the C compiler. There are comments in the Makefile that make it clear how to do this. * Make similar changes for the linker, archiver, etc. * type "make". If you really feel like you have to use the configure script, the just configure for a linux build. Then type: make target_source The "target_source" target will do all of the preprocessing steps and put a bunch of source files in a subdirectory named "tsrc". Change into that subdirectory. Remove one or two source files that you do not need (like shell.c for the command-line client or tclsqlite.c for the TCL interface) and type: for i in *.c; do i386-mingw-gcc $i; done Maybe add some other options to gcc if you need them. If you want to build a DLL, there is a script that does that in the source tree. See mkdll.sh. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Compiling sqlite3 with mingw32
Hello everyone i need help on compiling sqlite3 from source in linux with mingw. I tried to find the headers to compile my app but i wasn't able to find them, so i'm trying to compile them. I set some enviroment variables : # cross.env PREFIX=/opt/cross-tools TARGET=mingw32 export CC="i586-mingw32msvc-gcc -mms-bitfields" export CXX="i586-mingw32msvc-g++ -mms-bitfields" export CFLAGS="-O2 -march=i586 -mms-bitfields" export CXXFLAGS="-O2 -march=i586 -mms-bitfields" export PKG_CONFIG_PATH=$PREFIX/$TARGET/lib/pkgconfig export PATH=$PREFIX/$TARGET/bin:/bin:/usr/bin export LD_LIBRARY_PATH=$PREFIX/$TARGET/lib export LDFLAGS=-L$PREFIX/$TARGET/lib export OBJDUMP=$PREFIX/$TARGET/bin/mingw32-objdump export HOST_CC=/usr/bin/gcc then i try ./configure --target mingw32 --prefix=/../.. --host=mingw32 but it says unable to find a compiler for building build tools. Does anybody know what can i do? please? :'( Thanks in advance. Matias - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
As you can tell from this forum, locking and synchronization is the area where there is least intuitive understanding among users and is the most consistent source of problems. There must be a deep psychological reason. I don't think it's deep really, just the most complex part to understand. That and it's a nightmare to debug because of the time dimension. In my last project it took me three tries to get the locking issues really resolved. Did you know on SQL server that if you don't have a clustered index it can promote row level locking to page locking? I learned all about it the hard way! Ack! -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Jay Sprenkle wrote: On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote: Thankyou. The Firefox people have merely removed their dependance upon an unreliable resource, cross OS file locking. A prudent design choice. If they come up with an elegant distributed lock protocol it would be worth propagating universally in the light of the success of Firefox and its consequent broad distribution. I see that it has already grabbed perhaps 30% of browser users. I agree as long as they don't replace it with something that is less robust. IMHO something that's broken as simply as running two instances doesn't seem robust or elegant. I hope I'm wrong about it though - To unsubscribe, send email to [EMAIL PROTECTED] - As long as I can remember poorly conceived and implemented file locking mechanisms have been a nosebleed in IT. If you were ever involved in porting to multiple OS's you quickly got burned. On that basis I hope the Firefox people develop something elegant, but wouldn't bet on it. As you can tell from this forum, locking and synchronization is the area where there is least intuitive understanding among users and is the most consistent source of problems. There must be a deep psychological reason. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote: Thankyou. The Firefox people have merely removed their dependance upon an unreliable resource, cross OS file locking. A prudent design choice. If they come up with an elegant distributed lock protocol it would be worth propagating universally in the light of the success of Firefox and its consequent broad distribution. I see that it has already grabbed perhaps 30% of browser users. I agree as long as they don't replace it with something that is less robust. IMHO something that's broken as simply as running two instances doesn't seem robust or elegant. I hope I'm wrong about it though - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: two questions about formatting output
If I'm going to do that, it seems easier to just add a .width line to my .sqliterc file that specifies a larger width for about 10 or so columns. Still not perfect though. On 8/29/06, Fred Williams <[EMAIL PROTECTED]> wrote: From experience. Most "free form" output routines I have used utilizes the content of the first row to establish the width of "columns." (i.e. Reflected in the result per your "Administrative Assistant") If you can "throw away" your first row of output, you could insert a first row in your table with "X"'s in all positions out to your maximum column widths. Export the data and delete the first line. Fred > -Original Message- > From: John Salerno [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 29, 2006 11:39 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Re: two questions about formatting output > > > Alright, one final note, because now I'm really confused. Here was my > original row data: > > Secretary, Programmer, Programmer II > > It displayed like this: > > title > -- > Secretary > Programmer > Programmer > > I changed Secretary to Administrative Assistant and now it > shows like this: > > title > -- > Administrative Assistant > Programmer > Programmer II > > So why didn't it expand for Programmer II, but it does expand for AA? > > Thanks, > John > > > > > > > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > > Just as a follow-up, I noticed that one of my columns *did* > expand to > > fit a longer name, yet another column didn't. Could it be > because the > > one that expanded contained a string of a single word, while the > > column that didn't expand contained multiple words, i.e. 'Programmer > > II'? > > > > Thanks. > > > > > > > > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > > > Is it possible to change the width of columns in column > mode in some > > > general way? What I mean is, I know I can do .width X X X > etc., but > > > that requires knowing how many columns you have. Is there > some way to > > > set it so that the column will simply expand to fit the > information? > > > > > > Second, I created a row entry like this: > > > > > > salary double(6, 2) > > > > > > and then entered a number such as 45000.00, but the > output shows it as 45000.0 > > > > > > Maybe this comes from ignorance of how SQL works in general, but > > > shouldn't it show the second 0, since I typed it that way and I > > > declared the field to have 2 decimal places? > > > > > > Thanks, > > > John > > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Thankyou. The Firefox people have merely removed their dependance upon an unreliable resource, cross OS file locking. A prudent design choice. If they come up with an elegant distributed lock protocol it would be worth propagating universally in the light of the success of Firefox and its consequent broad distribution. I see that it has already grabbed perhaps 30% of browser users. [EMAIL PROTECTED] wrote: "Jay Sprenkle" <[EMAIL PROTECTED]> wrote: If you run two instances of firefox you trash your own database. No, you didn't read what I said. Firefox implements their own locking mechanism, so two instances of firefox will play nicely together. The problem is when some other application, that does not follow firefox's locking protocol, tries to access the database while firefox is running. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: two questions about formatting output
On 8/29/06, Martin Jenkins <[EMAIL PROTECTED]> wrote: John Salerno wrote: > So why didn't it expand for Programmer II, but it does expand for AA? AIUI, the default column width is the greater of 10 and the width of the first line of output. Ah, thanks! I had even read about this, but I misunderstood it to mean something else. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: two questions about formatting output
>From experience. Most "free form" output routines I have used utilizes the content of the first row to establish the width of "columns." (i.e. Reflected in the result per your "Administrative Assistant") If you can "throw away" your first row of output, you could insert a first row in your table with "X"'s in all positions out to your maximum column widths. Export the data and delete the first line. Fred > -Original Message- > From: John Salerno [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 29, 2006 11:39 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Re: two questions about formatting output > > > Alright, one final note, because now I'm really confused. Here was my > original row data: > > Secretary, Programmer, Programmer II > > It displayed like this: > > title > -- > Secretary > Programmer > Programmer > > I changed Secretary to Administrative Assistant and now it > shows like this: > > title > -- > Administrative Assistant > Programmer > Programmer II > > So why didn't it expand for Programmer II, but it does expand for AA? > > Thanks, > John > > > > > > > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > > Just as a follow-up, I noticed that one of my columns *did* > expand to > > fit a longer name, yet another column didn't. Could it be > because the > > one that expanded contained a string of a single word, while the > > column that didn't expand contained multiple words, i.e. 'Programmer > > II'? > > > > Thanks. > > > > > > > > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > > > Is it possible to change the width of columns in column > mode in some > > > general way? What I mean is, I know I can do .width X X X > etc., but > > > that requires knowing how many columns you have. Is there > some way to > > > set it so that the column will simply expand to fit the > information? > > > > > > Second, I created a row entry like this: > > > > > > salary double(6, 2) > > > > > > and then entered a number such as 45000.00, but the > output shows it as 45000.0 > > > > > > Maybe this comes from ignorance of how SQL works in general, but > > > shouldn't it show the second 0, since I typed it that way and I > > > declared the field to have 2 decimal places? > > > > > > Thanks, > > > John > > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: two questions about formatting output
John Salerno wrote:
So why didn't it expand for Programmer II, but it does expand for AA?
AIUI, the default column width is the greater of 10 and the width of the
first line of output.
sqlite> .mo co
sqlite> .he on
sqlite> create table t(t TEXT UNIQUE);
sqlite> insert into t(t) values("");
sqlite> insert into t(t) values("");
sqlite> insert into t(t) values("");
sqlite> select * from t;
t
--
bb
cc
sqlite> select * from t order by t desc;
t
sqlite>
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: two questions about formatting output
But if you do .mode column (and .header on, if that matters), it outputs:
a
Secretary
Programmer
Programmer
John
On 8/29/06, P Kishor <[EMAIL PROTECTED]> wrote:
>"C:\Program Files\sqlite3" tmp.db
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> create table t (a text);
sqlite> .s
CREATE TABLE t (a text);
sqlite> insert into t values ('Secretary');
sqlite> insert into t values ('Programmer');
sqlite> insert into t values ('Programmer II');
sqlite> select * from t;
Secretary
Programmer
Programmer II
sqlite>
On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote:
> Alright, one final note, because now I'm really confused. Here was my
> original row data:
>
> Secretary, Programmer, Programmer II
>
> It displayed like this:
>
> title
> --
> Secretary
> Programmer
> Programmer
>
> I changed Secretary to Administrative Assistant and now it shows like this:
>
> title
> --
> Administrative Assistant
> Programmer
> Programmer II
>
> So why didn't it expand for Programmer II, but it does expand for AA?
>
> Thanks,
> John
>
>
>
>
>
>
> On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote:
> > Just as a follow-up, I noticed that one of my columns *did* expand to
> > fit a longer name, yet another column didn't. Could it be because the
> > one that expanded contained a string of a single word, while the
> > column that didn't expand contained multiple words, i.e. 'Programmer
> > II'?
> >
> > Thanks.
> >
> >
> >
> > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote:
> > > Is it possible to change the width of columns in column mode in some
> > > general way? What I mean is, I know I can do .width X X X etc., but
> > > that requires knowing how many columns you have. Is there some way to
> > > set it so that the column will simply expand to fit the information?
> > >
> > > Second, I created a row entry like this:
> > >
> > > salary double(6, 2)
> > >
> > > and then entered a number such as 45000.00, but the output shows it as
45000.0
> > >
> > > Maybe this comes from ignorance of how SQL works in general, but
> > > shouldn't it show the second 0, since I typed it that way and I
> > > declared the field to have 2 decimal places?
> > >
> > > Thanks,
> > > John
> > >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] two questions about formatting output
I'll definitely move on to using Python to work with SQLite, so I guess this won't be a problem eventually, but for now the command line program is a very easy way to test out things and just use a database on the fly, without having to write the extra code involved in connecting to it via a separate language. On 8/29/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "John Salerno" <[EMAIL PROTECTED]> wrote: > Just as a follow-up, I noticed that one of my columns *did* expand to > fit a longer name, yet another column didn't. Could it be because the > one that expanded contained a string of a single word, while the > column that didn't expand contained multiple words, i.e. 'Programmer > II'? > > Thanks. > > > > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > > Is it possible to change the width of columns in column mode in some > > general way? What I mean is, I know I can do .width X X X etc., but > > that requires knowing how many columns you have. Is there some way to > > set it so that the column will simply expand to fit the information? > > Please do not confuse "SQLite" and the command-line shell. The command-line shell is a small (and stupid) program that uses SQLite to access an SQLite database. The complete source code to the command-line shell is in the file "shell.c". The command-line shell is rather limited in what it can do in terms of output formatting. If you need something more elaborate (which you apparently do) then I would suggest writing a short script in your favorite dynamic language to open the database, extract the information you want, then present it in whatever format seems appropriate. A full-featured language is a much better tool for doing that sort of thing than a simple command-line shell. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: two questions about formatting output
"C:\Program Files\sqlite3" tmp.db
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> create table t (a text);
sqlite> .s
CREATE TABLE t (a text);
sqlite> insert into t values ('Secretary');
sqlite> insert into t values ('Programmer');
sqlite> insert into t values ('Programmer II');
sqlite> select * from t;
Secretary
Programmer
Programmer II
sqlite>
On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote:
Alright, one final note, because now I'm really confused. Here was my
original row data:
Secretary, Programmer, Programmer II
It displayed like this:
title
--
Secretary
Programmer
Programmer
I changed Secretary to Administrative Assistant and now it shows like this:
title
--
Administrative Assistant
Programmer
Programmer II
So why didn't it expand for Programmer II, but it does expand for AA?
Thanks,
John
On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote:
> Just as a follow-up, I noticed that one of my columns *did* expand to
> fit a longer name, yet another column didn't. Could it be because the
> one that expanded contained a string of a single word, while the
> column that didn't expand contained multiple words, i.e. 'Programmer
> II'?
>
> Thanks.
>
>
>
> On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote:
> > Is it possible to change the width of columns in column mode in some
> > general way? What I mean is, I know I can do .width X X X etc., but
> > that requires knowing how many columns you have. Is there some way to
> > set it so that the column will simply expand to fit the information?
> >
> > Second, I created a row entry like this:
> >
> > salary double(6, 2)
> >
> > and then entered a number such as 45000.00, but the output shows it as
45000.0
> >
> > Maybe this comes from ignorance of how SQL works in general, but
> > shouldn't it show the second 0, since I typed it that way and I
> > declared the field to have 2 decimal places?
> >
> > Thanks,
> > John
> >
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] two questions about formatting output
"John Salerno" <[EMAIL PROTECTED]> wrote: > Just as a follow-up, I noticed that one of my columns *did* expand to > fit a longer name, yet another column didn't. Could it be because the > one that expanded contained a string of a single word, while the > column that didn't expand contained multiple words, i.e. 'Programmer > II'? > > Thanks. > > > > On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > > Is it possible to change the width of columns in column mode in some > > general way? What I mean is, I know I can do .width X X X etc., but > > that requires knowing how many columns you have. Is there some way to > > set it so that the column will simply expand to fit the information? > > Please do not confuse "SQLite" and the command-line shell. The command-line shell is a small (and stupid) program that uses SQLite to access an SQLite database. The complete source code to the command-line shell is in the file "shell.c". The command-line shell is rather limited in what it can do in terms of output formatting. If you need something more elaborate (which you apparently do) then I would suggest writing a short script in your favorite dynamic language to open the database, extract the information you want, then present it in whatever format seems appropriate. A full-featured language is a much better tool for doing that sort of thing than a simple command-line shell. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: two questions about formatting output
Alright, one final note, because now I'm really confused. Here was my original row data: Secretary, Programmer, Programmer II It displayed like this: title -- Secretary Programmer Programmer I changed Secretary to Administrative Assistant and now it shows like this: title -- Administrative Assistant Programmer Programmer II So why didn't it expand for Programmer II, but it does expand for AA? Thanks, John On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: Just as a follow-up, I noticed that one of my columns *did* expand to fit a longer name, yet another column didn't. Could it be because the one that expanded contained a string of a single word, while the column that didn't expand contained multiple words, i.e. 'Programmer II'? Thanks. On 8/29/06, John Salerno <[EMAIL PROTECTED]> wrote: > Is it possible to change the width of columns in column mode in some > general way? What I mean is, I know I can do .width X X X etc., but > that requires knowing how many columns you have. Is there some way to > set it so that the column will simply expand to fit the information? > > Second, I created a row entry like this: > > salary double(6, 2) > > and then entered a number such as 45000.00, but the output shows it as 45000.0 > > Maybe this comes from ignorance of how SQL works in general, but > shouldn't it show the second 0, since I typed it that way and I > declared the field to have 2 decimal places? > > Thanks, > John > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] converting a sqlite table to dbf
Damn acronyms anyhow! Thanks. > -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 29, 2006 6:47 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] converting a sqlite table to dbf > > > On 8/29/06, Fred Williams <[EMAIL PROTECTED]> wrote: > > Kind'a like chucking your Pentium 5 and going back to > scratching on the > > cave wall with a rock, Eh? > > > > I'd export the tables to a CVS files using something like > > SQLiteAdmin.exe. Open the CVS files with Excel and save > the resulting > > spreadsheets as .DBF files (My Excel has a choice of DBASE > II through > > DBASE IV.) Or you could use that block buster database, > "Access", to do > > the same. > > I think you mean "CSV" here... > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Ritesh Kapoor wrote: Can you - DRH or someone else provide some more background information on why locking dosen't work on NFS mounted file systems. I just tried to find out what the locking problem was but couldn't find a web page discussing it in any detail. Lots of pages saying there *was* a problem with locking but none describing *what* the problem was. Someone asks for a reliable NFS locking method in a short thread at http://www.exim.org/pipermail/exim-users/Week-of-Mon-19990531/012756.html The gist of that thread is that opening a file with O_EXCL isn't atomic. This problem (and a workaround) is mentioned in Linux's man page for open(2) and apparently this workaround is used by exim and procmail. http://blogs.sun.com/erickustarz/entry/integrated_locking claims there's no problem with NFS locking and mentions the SQLite FAQ as making this claim. Apparently it's an "issue with early implementations rather than with the NFS protocol issue". Stevens said pretty much the same back in 1999 and he's probably about as authoritative as you'll get. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to ".import" an Ascii file
Sorry for the false info Ascii files have 3 character separation, including 2 chr(34 )(quote)=with space between [" "] ; that is my problem! Best, Randall; thanks for your patience. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] converting a sqlite table to dbf
P Kishor a écrit : Thanks, so the answer is there is no straight-forward way of doing this. I will export to CSV, and then rebuild a dbf using Perl. In case folks wonder why I am going back to the abacus, my data start and end as .dbf. I use SQLite in between to do magic tricks with it. On 8/29/06, Fred Williams <[EMAIL PROTECTED]> wrote: Kind'a like chucking your Pentium 5 and going back to scratching on the cave wall with a rock, Eh? I'd export the tables to a CVS files using something like SQLiteAdmin.exe. Open the CVS files with Excel and save the resulting spreadsheets as .DBF files (My Excel has a choice of DBASE II through DBASE IV.) Or you could use that block buster database, "Access", to do the same. Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P > Kishor > Sent: Monday, August 28, 2006 11:23 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] converting a sqlite table to dbf > > > is there any straightforward way for converting a SQLite > table to dbf format? > Hello, I use shapelib (a libary to read and write gis shape files), the attributes files are dbf files, its in c and it easyling bound to sqlite. Best wishes. -- Noël Frankinet Gistek Software SA http://www.gistek.net - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how to ".import" an Ascii file
Hi Randall, Regarding: how to ".import" an Ascii file (separator is " ") Assuming that no translations have occurred in transit, it looks as though you're using a single ascii space as a separator. So, given a file such as: cat dog koala bananna orange kiwi You should be able to import it using the sqlite command utility: Sqlite3 myDatabase Create table myTable (a, b, c); .separator ' ' or .separator " " .import myfile mytable If you are using MS Windows, you may need to double any backslashes needed for the filename, e.g. .import \\myDir\\myFile mytable NOTE that using a single space as a separator does NOT allow you to directly import text aligned in columns with a variable number of spaces as separators, such as: cat dogkoala bananna orange kiwi If you must import a file such as that, you could either: 1) Use some other utility, such as LMOD to help parse the columns into a single-separator format. 2) Set the separator to some unused character (perhaps '|'), import the entire line into a temporary table, then use "substr()" to populate the columns yourself. 3) Write some code linked with sqlite to import your table. [opinions expressed are my own, and not my company's] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] converting a sqlite table to dbf
Thanks, so the answer is there is no straight-forward way of doing this. I will export to CSV, and then rebuild a dbf using Perl. In case folks wonder why I am going back to the abacus, my data start and end as .dbf. I use SQLite in between to do magic tricks with it. On 8/29/06, Fred Williams <[EMAIL PROTECTED]> wrote: Kind'a like chucking your Pentium 5 and going back to scratching on the cave wall with a rock, Eh? I'd export the tables to a CVS files using something like SQLiteAdmin.exe. Open the CVS files with Excel and save the resulting spreadsheets as .DBF files (My Excel has a choice of DBASE II through DBASE IV.) Or you could use that block buster database, "Access", to do the same. Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P > Kishor > Sent: Monday, August 28, 2006 11:23 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] converting a sqlite table to dbf > > > is there any straightforward way for converting a SQLite > table to dbf format? > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to ".import" an Ascii file
On 8/29/06, Randall <[EMAIL PROTECTED]> wrote: Hi, I feel embarrased after searching the archives and syntax; Can anyone pleasae spare the time to teach me how to ".import" an Ascii file (separator is " ") I cannot get the separator to work. Many Thanks, Randall I had the toughest time myself because I didn't think of the .separator command. However, in your case, having a space separate the columns is going to be pretty tricky. You can try something like sqlite3> .separator \s sqlite3> .import else, you might have to first change the space separator to something more definitive. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Kurt Welgehausen <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > Saying NOT NULL on a PRIMARY KEY is redundant, by the way. > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > sqlite> insert into t (k, d) values (null, 'jkl'); > sqlite> select * from t; > k d > -- -- >> <> jkl > > > Am I missing something, or should I write a bug ticket > about a primary key accepting nulls? > To my surprise (perhaps "horror") I find that SQLite has for a very long time allowed NULL values in PRIMARY KEY columns. This is clearly incorrect. But the ability to do this has been in the code for so long that I fear changing it might break many legacy programs. So I have chosen to merely document the behavior for now - with a warning to developers that the behavior might be fixed in the future. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to ".import" an Ascii file
Hi, I feel embarrased after searching the archives and syntax; Can anyone pleasae spare the time to teach me how to ".import" an Ascii file (separator is " ") I cannot get the separator to work. Many Thanks, Randall - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Mario Frasca wrote: Kurt Welgehausen wrote: [...] should I write a bug ticket about a primary key accepting nulls? there is already a ticket for that: 518. I reopened it three days ago. I have right now attached a patch for it. it is quite small and I hope it fits in the current style. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] converting a sqlite table to dbf
On 8/29/06, Fred Williams <[EMAIL PROTECTED]> wrote: Kind'a like chucking your Pentium 5 and going back to scratching on the cave wall with a rock, Eh? I'd export the tables to a CVS files using something like SQLiteAdmin.exe. Open the CVS files with Excel and save the resulting spreadsheets as .DBF files (My Excel has a choice of DBASE II through DBASE IV.) Or you could use that block buster database, "Access", to do the same. I think you mean "CSV" here... - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > > If you run two instances of firefox you trash > your own database. No, you didn't read what I said. Firefox implements their own locking mechanism, so two instances of firefox will play nicely together. The problem is when some other application, that does not follow firefox's locking protocol, tries to access the database while firefox is running. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
On 8/29/06, Ritesh Kapoor <[EMAIL PROTECTED]> wrote: Can you - DRH or someone else provide some more background information on why locking dosen't work on NFS mounted file systems. If its a known issue then is there an SQLite compile time option that would remove locking - i couldn't find one. When I got stuck with this problem I had to remove all locking code in sqlite. I googled and did find some information related to NFS-locking on different mailing lists but all of this information wasn't connected. This seems to be a problem known for a long time now - Why hasn't it been fixed? The operating systems in question don't work correctly. Sqlite can't fix it. It could be documented better or a test put together that would warn you though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote: Jay Sprenkle wrote: > > If it breaks because of something you did, then YOU are the bum. > If it's broken because of the operating system THEY are the bums. > Having someone to blame still doesn't make it work. They are have made the rational decision, based on building a product which runs everywhere. In either case it's a problem. If you run two instances of firefox you trash your own database. Given the choice of "It's broken and I get blamed" or "It's broken and the operating system is at fault" (Which is entirely true) then the choice is clear in my mind. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] memory footprint and leakage_2
hi, i realized i missed some codes in sqlite3GenericRealloc() for the calculating. after modifying my codes, the testing results are: the highest water mark of memory usage is 16684 bytes, and 0 memory leakage. the same configuratiions as described in the early. 16684bytes, is it the right figure? thanks wang
[sqlite] memory footprint and leakage
hi,
i am evaluating sqlite performance for mobile platform now.
i found something discussable about memory footprint and leakage.
the highest water line of memeory usage is 44611bytes (by malloc() ), and
there seems a large memory leakage (34017bytes) in my testing scenario.
'cache_size' is set to the smallest, '1', but i dont know why it still use
so much. for low-profile mobile platforms, such amount seems too much.
can somebody tell me something about this?
-the way i measure the memroy usage
in os_common.h
sqlite3GenericMalloc(int n){
char *p = (char *)malloc(n+8);
...
...
if( p ){
*(int *)p = n;
}
currentMemoryConsumption += n;
if (currentMemoryConsumption > maxMemoryConsuption)
{
maxMemoryConsuption = currentMemoryConsumption;
}
return (void *)p;
}
void sqlite3GenericFree(void *p){
assert(p);
currentMemoryConsumption -= *((int*) ((char*)p - 8));
...
...
}
--testing
scenario--
the testing is done with windows filesystem,
cache_size=1; page_size = 1k (default),
the database being operated on contains 250 enties, no index:
contacts.db size: 7,168 bytes
CREATE TABLE contacts (name text,phone int);
A.H.|1439
Abel|1654
Absalonsgade,|1658
...
...
the queries are:
select number from contacts where rowid = 1
select number from contacts where rowid = 125
select number from contacts where rowid = 64
INSERT INTO contacts values('kim','')
INSERT INTO contacts values('wang','')
update contacts set phone='34' where name = 'kim'
update contacts set phone='71' where name = 'wang'
update contacts set phone='' where name = 'Eskildsgade,'
update contacts set phone='' where name = 'Bustrupgade,'
begin
INSERT INTO contacts VALUES('abc','49')
INSERT INTO contacts VALUES('klaus','41')
update contacts set phone='1657' where name = 'Eskildsgade,'
update contacts set phone='1737' where name = 'Bustrupgade,'
delete from contacts where name = 'abc'
delete from contacts where name = 'klaus'
commit
delete from contacts where name = 'kim'
delete from contacts WHERE name = 'wang'
delete from contacts where name = 'Eskildsgade,'
delete from contacts WHERE name = 'Bustrupgade,'
[sqlite] Regarding connect to sqlite via c program
Hi, My motive is to connect sqlite through c program, can u guide me. I am not aware to do the 3rd step , please explain . Win32 recompile with borland c++ builder 1. Download preprocessed source from ¤http://www.sqlite.org/sqlite_source.zip 2. Unzip the source to a project directory, e.g. C:\sqlite 3. Fire up your borland c++ builder and create a new dll project, plain C without mfc or something like that 4. Save the project as (e.g.) C:\sqlite\sqlite.bpr 5. Add the sqlite source files from c:\sqlite to the project 6. Remove the unit1.* files from your project 7. Even if .def files are not the clean way, it will be easier than rewriting the sourcecode. Create the file sqlite.def with the following content and add it to the project: -- View this message in context: http://www.nabble.com/Regarding-connect-to-sqlite-via-c-program-tf2181919.html#a6034213 Sent from the SQLite forum at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Kurt Welgehausen wrote: [EMAIL PROTECTED] wrote: Saying NOT NULL on a PRIMARY KEY is redundant, by the way. [...] Am I missing something, or should I write a bug ticket about a primary key accepting nulls? there is already a ticket for that: 518. I reopened it three days ago. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] converting a sqlite table to dbf
Kind'a like chucking your Pentium 5 and going back to scratching on the cave wall with a rock, Eh? I'd export the tables to a CVS files using something like SQLiteAdmin.exe. Open the CVS files with Excel and save the resulting spreadsheets as .DBF files (My Excel has a choice of DBASE II through DBASE IV.) Or you could use that block buster database, "Access", to do the same. Fred > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P > Kishor > Sent: Monday, August 28, 2006 11:23 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] converting a sqlite table to dbf > > > is there any straightforward way for converting a SQLite > table to dbf format? > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/ > Open Source Geospatial Foundation https://edu.osgeo.org/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
