Re: [sqlite] Statistics on integer primary key - Cardinality

[Griggs, Donald]

 

Hi Peter,

Regarding:
  "Selectivity is known -- since it's a primary key, which is unique -- it will 
be 1. Cardinality can vary."


I wonder if you're referring to the definition of *cardinality* as used in 
mathematics
http://en.wikipedia.org/wiki/Cardinality
In mathematics, the cardinality of a set is a measure of the "number of 
elements of the set".


as opposed to the definition common in SQL:
 http://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29
 "In SQL (Structured Query Language), the term cardinality refers to the 
uniqueness of data values contained in a particular column (attribute) of a 
database table. "

Regards,
   Donald

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

Re: [sqlite] Very Slow DB Access After Reboot on Windows

[Griggs, Donald]

Regarding win/dos  COPY command and /b option:

Thanks for correcting me, Michael.  I somehow thought that NUL being the target 
would introduce some sort of "text affinity" but it's good to know the truth 
instead.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very Slow DB Access After Reboot on Windows

[Griggs, Donald]

 

Regarding:
Also...try doing a "copy my.db nul:" to get it cached once before you use 
it.
 
 
Am I right in thinking he may want to include the "/b" (binary) option so that 
the copy doesn't stop at the first nul byte?

  copy /b my.db nul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to invoke successive SQLite script files

[Griggs, Donald]

Regarding: "My question is, what command do I use in script-A to invoke 
script-C, script-B, etc? 
 Is it the same ".read" command?  "

I believe it does work that way.  What did you get when you tried it?


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

Re: [sqlite] Sqlite Insert Speed Optimization

[Griggs, Donald]

 


Griggs, Donald wrote: 

> Is the percentage of the final rowcount really a criterion?  

The answer to that, according to my brief exploration, is somewhere between 
"yes" and "very much yes", depending on various factors.

Thanks, Eric.  I guess I was wondering if the fastest records-per-transaction 
value would depend on the page cache and be more or less independent of the 
total records to be imported. (Indicies omitted.)

So, the records-per-transaction for import to a 20 million row table should be 
twenty times the size for a 1 million row table?

I confess I've got a lot to learn.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Griggs, Donald]

Hi Roberto,

Re:  Please find a test database in the attachment.

I don't think attachments are permitted on the mailing list messages.

If it's only ten rows, perhaps just using the command line utility to perform a

.dump

Command and pasting the text into a new message would do the trick. 

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

Re: [sqlite] Sqlite Insert Speed Optimization

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of John Drescher
Sent: Tuesday, July 13, 2010 12:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite Insert Speed Optimization

> I also wrap my statements (about 500 inserts at a time) with a 
> begin/end transaction.
> After these 500 i take a few seconds to read more data so sqlite 
> should have time to do any housekeeping it might need.
>

Wrap more into a transaction. 500 is too small of a percentage of a million.

John


John,

I was wondering if that's really so.  Wouldn't the marginal speed improvement 
be quite small?  Is the percentage of the final rowcount really a criterion?

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

Re: [sqlite] network access problem

[Griggs, Donald]

I just tried 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Black, Michael (IS)
Sent: Monday, May 24, 2010 2:37 PM



From: sqlite-users-boun...@sqlite.org on behalf of Art
Sent: Mon 5/24/2010 1:28 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] network access problem



Running in sqlite application in Virtual Box, attempt to open a database with 
sqlite from a shared network folder \\Vboxsvr\testdata  however the open16 and 
openv2 (with read only)  both fail --- rc = 14.  File opens file if moved 
locally to hard drive.

using latest version of sqlite3 3.6.23.1 

this seems to be a bug in the open code of sqlite when accessing a file across 
a network.

Thank you,
Art Zerger
azer...@yahoo.com
===

Hi Art,

I just succeeded in opening a db with a UNC path, under Windows XP Pro, using 
the same version (3.6.23.1) with the command-line utility -- but I do NOT use 
virtual box.

Does the command-line utility fail for you under virtual box?   

Might you attempt it without virtualbox?   

Donald

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

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

[Griggs, Donald]

 Regarding:
   select * from table1 where field1='x' or field2='y'


Hi, Ray,

I believe you sent two messages -- the first with "OR" and the second with 
"AND".

Since your subject consistently says "OR" and your last email says "AND" I'll 
assume you meant "AND".


Sqlite using a maximum of one index per table per select.

In order to perform your "OR" select, it must scan every the table.

You may want to create a compound index of both fields.

(Or maybe a "UNION ALL" would be fast?  You can use EXPLAIN QUERY PLAN to 
analyze.)


Hope this helps,
Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE = does not work

[Griggs, Donald]

Would this query help determine if any extraneous characters present?

SELECT * FROM MyTable
 WHERE LENGTH(resourceType) <> 3
 AND resourceType LIKE 'PSM' ;

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

Re: [sqlite] WHERE = does not work

[Griggs, Donald]

Ecforu,

Re: What's the diff?

In sqlite, LIKE without a "%" (percent-sign ) would be a case-insensitive 
search, whereas == would be case-sensitive.


sqlite> select 'cat' like 'CAT';
1

sqlite> select 'cat' == 'CAT';
0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: Direct access of table data

[Griggs, Donald]

 
Briggs,

Re: "I guess I'm just getting greedy since sqlite is so much faster than our 
standard databases ("progress")."

That's a distinct possibility.;-)


Re: "When you run a query for select * from table, does it literally copy the 
contents of the table, or does it just build a structure that points to the 
data in the table?"

When you wrote "points to the data in the table" -- by "table" do you mean 
the data on your disk drive?  If so, then yes, sqlite must read the data into 
RAM, and at least one ram-to-ram copy is implied.   (The data for a row might 
span multiple db pages.) If you're using an sqlite wrapper, that may possibly 
perform a copy as well.   
If you're using multi-megabyte BLOB's then these can be an exception, as 
you may read them incrementally:
http://www.sqlite.org/c3ref/blob_read.html

If, however, you're NOT using large blobs, then considerations such as your 
synchronization level,
http://www.sqlite.org/pragma.html#pragma_synchronous
 indexing, page size, etc. are usually much more important to performance than 
a ram-to-ram copy.

Again, have you measured the time to perform the query in question?  Is it 
worrisome?   If not, don't worry.



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

[sqlite] FW: Direct access of table data

[Griggs, Donald]

From: Nathan Biggs [mailto:nbi...@mycfs.com]
Sent: Wednesday, April 21, 2010 2:43 PM
To: Griggs, Donald
Subject: Re: [sqlite] Direct access of table data

Griggs,

Thanks for the quick reply.  I guess I'm just getting greedy since sqlite is so 
much faster than our standard databases ("progress").
When you run a query for select * from table, does it literally copy the 
contents of the table, or does it just build a structure that points to the 
data in the table?


On 4/21/2010 2:29 PM, Griggs, Donald wrote:

Hello Biggs, this is Griggs,

Re: "Is there a way to read the values of a table directly without building and 
executing a query.  I have a function that has predefined memory..."

No easy way.   The sophistication of database abstraction is the reason you 
presumably chose to use sqlite.


Re: "I thought it might perform much [better] without all .."

If you've measured this and the query truly takes too long, you might try 
(in order):
 1) trying to optimize the query (do you have ideal indexing, for instance?)
 2) restructure your data/program
 3) use a simple file rather than a database


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

Re: [sqlite] Direct access of table data

[Griggs, Donald]

Hello Biggs, this is Griggs,

Re: "Is there a way to read the values of a table directly without building and 
executing a query.  I have a function that has predefined memory..."

No easy way.   The sophistication of database abstraction is the reason you 
presumably chose to use sqlite.


Re: "I thought it might perform much [better] without all ..."

If you've measured this and the query truly takes too long, you might try 
(in order):
 1) trying to optimize the query (do you have ideal indexing, for instance?)
 2) restructure your data/program
 3) use a simple file rather than a database


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

Re: [sqlite] Northwind example database

[Griggs, Donald]

 

On 27 Mar 2010, at 10:46am, GeoffW wrote:

> Just for educational purposes I have been experimenting a little with 
> the Northwind Sqlite database contained on the sqlite official site.
> Download link: http://download.vive.net/Northwind.zip.
> 
> Am I misunderstanding here or are the dates in the wrong format for 
> sqlite within this converted database ?

> Assuming it is wrong and not my understsanding, are there any easy 
> ways to get the dates reversed and corrected to sqlite order and 
> written back out to the database ?


Perhaps this is better performed in the calling language, but the following sql 
should reformat these dates.  Of course, you'd need to substitute and repeat 
for the other fields.


-- Reformat date from, e.g., '1/5/2010 12:00:00 AM'  to  '2010-01-05'

update employees
  set birthdate = replace (birthdate, ' 12:00:00 AM', '');

update employees
  set birthdate = '0' || birthdate
  where substr(birthdate, 2,1) == '/';
  
  update employees
  set birthdate =
   substr(birthdate, 1, 3)
|| '0'
|| substr(birthdate, 4,99)
  where substr(birthdate, 5,1) == '/';
  
-- Date should now be formatted as dd/mm/
-- Now change to -mm-dd

update employees
  set birthdate =
 substr(birthdate, 7,4)
  || '-'
  || substr(birthdate, 1,2)
  || '-'
  || substr(birthdate, 4,2);
   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 error: database or disk is full when commit transaction

[Griggs, Donald]

Tim,

Regarding:  "I guess when do the transaction, the database was duplicated, and 
it caused no free disk space"


Unless you do something such as "vacuum" the database should not actually be 
duplicated.  A  rollback journal file,
though, **is** created.
(details at http://www.sqlite.org/atomiccommit.html and elsewhere)


Others on this list can likely give better advice, but I wondered if:
   1) Perhaps deleting only part of the table in multiple transactions would 
help.
   2) If your operating system allows it, you might experiment with applying 
transparent compression to the database directory and perhaps more.
   3) If #2 is not feasible, would applying compression yourself to certain 
data fields be worthwhile?

I assume you're working with an embedded device of some sort which would 
explain such severe memory constraints?

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

Re: [sqlite] Fwd: Sqlite User

[Griggs, Donald]

Hello, Alexis,

Regarding:
> how many user at the same time ken use sqlite


I'm afraid the answer is, "it can vary widely depending upon your application 
statistics, your programming, your CPU speed and ram, etc."

I'm writing to you from the sqlite users mailing list.  Perhaps you could write 
back and say something about your level of familiarity with databases (e.g. are 
you a programmer?, what databases have you worked with?, etc.) and a bit about 
your intended application.

A lot of the simplicity of sqlite comes from the fact that no there is no 
server/client and sqlite locks the entire database during transaction writes.  
This simplicity comes at the cost of some amount of concurrency.

You may want to refer to item 5 at:
http://www.sqlite.org/faq.html
As well as
http://www.sqlite.org/whentouse.html
http://www.sqlite.org/lockingv3.html


Sometimes, the answer can only be determined through your own testing, but some 
applications are at one extreme or another, and the list members can give you 
general advice.

Donald

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

[sqlite] "suitable UI" -- RE: What's the problem with my INSERT clause?

[Griggs, Donald]

 

Regarding:
  BTW, can you recommend me suitable UI to work with SQLite?


Maybe you've already tried out some of the GUI tools listed at:
   http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

I'm not sure what your requirements and preferences are, or what operating 
system you're using, or whether you require an open-source solution, but I 
personally use Sqlite3Explorer, among other tools.




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

Re: [sqlite] Fwd: Sqlite files in temp folder

[Griggs, Donald]

Mark,

> Can you offer any advice on how to 
> stop the files being written, and remove the
> programme(s) that are writing these files.

Hello, I'm (simply) a member of the sqlite mailing list.

As you probably know, it is not sqlite itself that is creating these files, but 
some program on the system which incorporates sqlite -- and we have no *direct* 
way of determining what program that is.

I googled briefly and found the following two links which may well be relevant 
regarding the BBC's iPlayer and the service Kservice.exe:

   http://hintofsarcasm.com/2006/01/12/sky-by-broadband
   
http://www.techsupportforum.com/microsoft-support/windows-xp-support/451685-massive-sqlite-temp-files-filling-hard-drive-help.html

If the symptom described in the above links does not fit your situation, 
perhaps I could suggest:

1) Downloading one of the many sqlite GUI front-end programs, and see if either 
the column/table names, or the data values themselves give you some hints as to 
what program may be at fault.

2) If #1 is beyond your capabilities, you might take one of the smaller sqlite 
files, compress it, and email it to me directly, and I will make a quick 
look-see.

Perhaps others on this mailing list have better suggestions.

Hope this helps,
   Donald Griggs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: no such table on .import

[Griggs, Donald]

sqlite> .import C:\HEAD.txt head;
Error: no such table: head;
Any idea why I'm getting "no such table"?

Hi Phil,

The "dot" commands -- such as .import -- don't require a semicolon terminator, 
and the utility is interpreting your trailing semicolon as part of the table 
name.

You may also want to surround 'c:\head.txt' with single quotes, but I think if 
you omit the semicolon you'd finally get ahead with your work.

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

Re: [sqlite] Index creation in a memory database

[Griggs, Donald]

Regarding:  Maybe the question for this issue is why it's not ok this syntax: 
create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos

>From my reading of the CREATE INDEX syntax at:

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

the parentheses are required, not optional.

Are they optional under the sql99 standard?



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

Re: [sqlite] Unique column constrained to 0 .. 2^32?

[Griggs, Donald]

Regarding:  ...is to use an MD5/sha1 or similar checksum of the record and use 
the last 32 bits of that checksum.  It is extremely unlikely for there to be a 
collision ...

Except that the OP wrote:  "...I don't think it works very well for 2^32 
possible values (when there may well be only a couple of hundred unused 
ones)"



I think that, with 500 unused values, you'll have consumed over
   99.8 percent of your values -- and you appear to be describing this 
situation as ordinary.   (Collisions, in this situation, of course, would be in 
the majority.)

Is your application such that, being just a hair's breadth away, it will almost 
certainly then exhaust the rest of your 2**32 values, resulting in "bad things 
happening"?

If there may well be only a couple of hundred unused values, then unless your 
rows and indices are tiny, wouldn't your database be so large that a 2GB table 
of integers will be small in comparison?Anyway, Scott has beat me to a 
suggestion that you could just store the ID of the *deleted* rows while 
monitoring your maximum ID to be sure it remains under 2**32 (i.e., 4294967296).


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

Re: [sqlite] selective result columns

[Griggs, Donald]

I'm not clear.   Were you able to use Simon's syntax link:
   http://sqlite.awardspace.us/syntax/sqlitepg09.htm
To correct your syntax?

SQL works with sets, and will always return the same number of columns, but you 
*can* use sql to force a NULL, an empty string, or a space to be returned for a 
column value.  Does that not meet your need?

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

Re: [sqlite] trim'ming a column

[Griggs, Donald]

> UPDATE LSOpenJobs
>  SET vEmail = TRIM(vEmail);
>
> Given the state of the world economy, I hope that LSOpenJobs is an 
> ever-expanding table.  ;-)

Why do you say this?  Will the table grow because of this statement?  I have 
fixed the client data entry to take care of this in the future, but there are a 
lot of records that I didnot clean previously, so I need to do this, at least 
once. 

=
Jose,

I beg your pardon.   My comment ending with ;-)  was simply a poor joke, 
presuming that LSopenJobs was a list of available jobs -- something I hoped 
would increase during a time of large unemployment in the world.

The sql below should not increase the size of your database.
  UPDATE LSOpenJobs
SET vEmail = TRIM(vEmail);

Wishing you peace and prosperity,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trim'ming a column

[Griggs, Donald]

Regarding: I know that trim(vEmail) will do it, but what would be the command 
to run to trim all of the existing records?

UPDATE LSOpenJobs
  SET vEmail = TRIM(vEmail);

Given the state of the world economy, I hope that LSOpenJobs is an 
ever-expanding table.  ;-)


Regarding: Also, how to I trim specific characters?

If you don't need to keep such characters at ALL, you can use REPLACE with hex 
literals to change unwanted, non-whitespace characters to spaces, then TRIM. 

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

Re: [sqlite] Corrupted SQLITE database recovery

[Griggs, Donald]

Regarding:
I have a small corrupted sqlite 3 database. 
... Are there any tools for a more detailed analisys?


I'm not aware of any, Marko.

You may want to try
   .dump mytable1
   .dump mytable2
   etc
On individual tables to see if some are salvageable.

For failing tables, if you *can* read a certain number of records, you
might try a manual binary search for a ROWID that is readable beyond the
failure area, then
   .output mySalvage.txt
   select * from myBadTable where ROWID >= myROWIDgood;
   .output stdout

I realize that neither of these methods is guaranteed to work, and that
you may already be aware of them.

Your backup may turn out to be your best option.

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

Re: [sqlite] New sqlite bug report: Problem with /* */ commentfollowed by Non-SQL (sqlite-specific) command

[Griggs, Donald]

comment lines in sql begin with --

Regards,
Simon

===

But both types are supported, right?


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

C comments can span any number of lines. C-style comments begin with
"/*" and extend up to and including the next "*/" character pair or
until the end of input, whichever comes first. C-style comments can span
multiple lines.  

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

Re: [sqlite] New sqlite bug report: Problem with /* */ comment followed by Non-SQL (sqlite-specific) command

[Griggs, Donald]

Regarding:



/*
Here comes the comment. Source is reduced to show core of the problem.
*/

.mode column


==
FWIW, I reproduced error on 3.16.19 windows.

Of course, I presume this is not a problem with sqlite itself, but with
the sqlite3 utility program. 

Interesting in that I can make the error vanish by either:
   - Including any sql command or pragma ending in semicolon before the
dot command
   - Enclosing EACH line of initial comments with /*  */  (instead of
surrounding multiple lines of comment with a single /* */ pair.
   - even including an unnecessary semicolon on the end of a comment,
like "-- puppies;" will prevent the error.

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

Re: [sqlite] index optimal?

[Griggs, Donald]

Hi Sylvain,


Regarding: can you describe an output of a "explain"?

==> A very short answer could be that, in the output of   EXPLAIN QUERY
PLAN
   "If you see the name of an index, then that index is used."
   

Regarding:  is there something to tell us the best index to have for a
specified query?

==> Your question might be interpreted as:
   a) Given the indexes defined in my schema, which ones are ideal to
use for a specific query?
Or
   b) How do I create ideal indexes?

For each of these questions, the answer is "Both skill and craft are
sometimes involved, therefore there will often not be an absolute rule."

For question "a" (which to use), sqlite itself tries to determine this,
and often does a very good job.  In some cases, the ANALYZE command
helps sqlite make these decisions.

For question "b" (how to create ideal indices) I am definitely not an
expert, but I think some general guidance might be:
   -- For a given SELECT, sqlite will use, at most, one index per table.
   -- An index on a large table is usually more useful than an index on
a tiny one.
   -- An index on a column with many repeated values (low specificity)
may be less useful.
   -- Compound indexes are sometimes used to good effect, but remember
that they are used from left to right 
   -- Your own testing in your particular database will give the most
authoritative answers.
   -- This list is for sqlite, but I suspect you'll want to seek out
general SQL books and other resources.

Maybe this helps,
   Donald



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

Re: [sqlite] How to decide which table is the outer table and whichtable is the inner table?

[Griggs, Donald]

 
Re:   How to decide which table is the outer table and whichtable is the
inner table?

Possibly relevant:
http://www.sqlite.org/lang_analyze.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to skip the first field on .import

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle
Sent: Friday, October 30, 2009 10:36 AM
To: sqlite-users
Subject: [sqlite] How to skip the first field on .import

The first field in my table is ID primary integer autoincrement.

=> Do you instead mean "INTEGER PRIMARY KEY AUTOINCREMENT" ?

I read that if it is set to NULL it defaults to the maximum value
possible.  Not a Good Thing(tm).

=> Can you post the url where you read this?   From page 
http://www.sqlite.org/autoinc.html
it says something quite different:

If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly created
row a ROWID that is one larger than the largest ROWID in the table prior
to the insert. If the table is initially empty, then a ROWID of 1 is
used. If the largest ROWID is equal to the largest possible integer
(9223372036854775807) then the database engine starts picking candidate
ROWIDs at random until it finds one that is not previously used. 



How do I let this start out at the default value and auto increment?
My column separator is '|'.

=>  I'm guessing you're using the command-line utility program.
Others may have better answers, but:
  -- If you're restoring a table you've dumped previously, then you
probably want the primary key values to be set explicitly to their
previous values -- i.e., you don't want them to take on new values.
  -- If this is an initial data import, one way to do this is to
".import" to a temporary table withOUT the ID key, then use an sql
command to transfer the data to the permanent table (which DOES use
INTEGER PRIMARY KEY AUTOINCREMENT), e.g.
INSERT INTO myPerm 
  SELECT NULL, * FROM myTemp

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

Re: [sqlite] How to input a double num?

[Griggs, Donald]

Regarding:  How to input the num
"212345678901234567890123456.988290112"?


Purely curious, if it's not confidential -- how are you acquiring
numbers of such precision?  

If these are measurements, I'm just awed by the precision the device is
achieving.  If instead these numbers represent something other than a
measurement, you might want to consider storing them in a different
form.

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

Re: [sqlite] Grammar of "X is Y"

[Griggs, Donald]

Depends on what your definition of "is" is. 



(Sorry, non-English speakers.  This is a tiny joke based in American
political history)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gilles Ganault
Sent: Friday, October 23, 2009 11:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

On Fri, 23 Oct 2009 10:19:17 -0400, "Griggs, Donald"
<donald.gri...@allscripts.com> wrote:
>I'm grateful to Mike Cariotoglou for Sqlite3Explorer
> http://www.singular.gr/sqlite/

Unless I overlooked it, it won't let me copy all the rows into the
clipboard (tried CTRL-A, also tried selecting the first and the last row
followed by CTRL-C, to no avail).

Also...

"V3.01 29/11/2007"

"Access violation at address 006C304F in module 'sqlite3Explorer.exe'.
Read of address 0039."

Too bad :-/ Thanks anyway.
=

Regarding Sqlite3Explorer:  "..it won't let me copy all the rows into
the clipboard ..."

   I should probably have mentioned that RIGHT-CLICK within the output
grid provides an option to "COPY RESULT SET TO CLIPBOARD".
   I just tried it with 9150 rows, and they appeared to paste correctly
into a blank Micro$oft Excel spreadsheet.

For truly *huge* outputs, you may prefer a script to export from
sqlite3.exe and import elsewhere.

Regarding:
  "Access violation "
I cant' say.  I think the author may be on this list, or you can search
out his email address.

Hope this helps,
  Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Griggs, Donald]

Regarding:  "...copy the output of a SELECT into the clipboard so I can
paste it elsewhere."

 Sorry for piling on another "me too" post, but:

I'm grateful to Mike Cariotoglou for Sqlite3Explorer
 http://www.singular.gr/sqlite/

You can copy/paste the results of a select instantly into a program that
accepts tab separators (such as Excel).


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

Re: [sqlite] SQLite server for Windows/Linux?

[Griggs, Donald]

Gilles,

I know almost nothing about them, but the following may possibly be of
interest: 

http://sqlrelay.sourceforge.net/

http://www.it77.de/sqlite/sqlite.htm

http://www.sqlite.org/cvstrac/wiki?p=ClientServer

Donald

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

Re: [sqlite] Like do not use index as previous version

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ge...@iol.it
Sent: Wednesday, October 21, 2009 2:03 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Like do not use index as previous version

Hi all,
it seems that in last versions on sqlite3 LIKE clause stopped to use
indexes; I created a new empty database with SQLIte 3.6.13 and I run
these statements  :

CREATE TABLE TEST
(TEXT_1 text PRIMARY KEY, TEXT_2 text, TEXT_3 text COLLATE NOCASE);
CREATE INDEX TEST_IDX_2 ON TEST (TEXT_2); CREATE INDEX
TEST_IDX_3 ON TEST (TEXT_3);

Running explain query plan on "select * from test where text_3 like
'x';", I have the following result:

0|0|TABLE test WITH
INDEX TEST_IDX_3

And it's what I  expected.

If I execute the some statements 
in SQLite 3.6.16 and 3.6.19, I have this result:

0|0|TABLE test

So It's not 
using the index as in 3.6.13 version. 
The some if I try to use the operator 
GLOB with field text_2: in SQLite 3.6.13 it uses the TEST_IDX_2 index
but it's 
not used in SQLite 3.6.19.

Any suggestions?

==

Regarding suggestions:
   Since "like" and "glob" are intended for use with wildcards and
you're not using wildcards, why not use
 "where text_3 == 'x';"
instead?

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

Re: [sqlite] SQLite DB Structure

[Griggs, Donald]

Regarding:
   
"I  NEED  HELP! I have SQLite3 DB (contacts from iPhone), it 
contains 1 record,  but when I open it with NOTEPAD.EXE, I see more than 1 
record.
I  need  to  repair  all  records  from  it"

С уважением,

I received your db (via private email) and the good news is that the 
PRAGMA INTEGRITY_CHECK
reports that it is a healthy database.

The *bad* news is that I suspect the data you are looking for was deleted

From the database point of view, at bottom are the tables and the number of 
rows in each table.  I can provide you a full dump of these records and schema, 
but I suspect that's not what you are seeking.

I know of no utility to help you seek out data from deleted records whose data 
may nonetheless be still present in the database file.  Using something like 
NOTEPAD may actually be your best bet -- see what cyrilic strings you can find. 
  Also, John's suggestion to contact the application's support dept (Apple?) 
may help.

There's some great-looking documention on the sqlite file structure here:
http://www.sqlite.org/fileformat.html
but it's not for the faint-of-heart (i.e., not so easily digested).

I'm reminded of my old "databank" wristwatch.   When it's battery died, I joked 
that I had to go get a whole new set of friends.  :-(


0   ABGroup
0   ABGroupChanges
0   ABGroupMembers
3   ABMultiValue
0   ABMultiValueEntry
8   ABMultiValueEntryKey
7   ABMultiValueLabel
3   ABPerson
0   ABPersonChanges
2   ABPersonMultiValueDeletes
3   ABPersonSearchKey
0   ABPhoneLastFour
0   ABRecent
3   ABStore
26  FirstSortSectionCount
29  LastSortSectionCount
16  _SqliteDatabaseProperties
2   sqlite_sequence

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

[sqlite] Use of .TIMER within sqlite3 commandline utility: was RE: sqlite-users Digest, Vol 21, Issue 122

[Griggs, Donald]

 

I believe one must enable the .TIMER option when compiling sqlite3.   I
think the pre-compiled versions have this disabled by default (at least
for the Windows binaries).



Donald


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

Re: [sqlite] Sqlite reading all column data on selects.

[Griggs, Donald]

Hello, Adam,

Regarding: "... are there plans to stop sqlite3 from reading in all
column data on specific select queries? i.e I would like sqlite to ONLY
read column data for columns which are specified in the select and where
clauses."

I'm no sqlite expert, and moreover I'm not sure I understand your
question properly.  Are you wanting to prevent sqlite from transferring
data columns from disk when those columns are not needed?

If so, I don't think that's generally feasible.  You might want to look
at sqlite's (and other database system's) architechture.   Sqlite will
instruct the operating system to read *pages* that it needs to complete
your requests.

That being said, you *can* sometimes improve performance by locating
less-used columns later in your table definition (i.e., "to the right
of" the more-used columns).  Even better, you can place large blob-ish
fields into separate tables, along with an indexing identifier, in the
hope that these pages will be read only when required.

What problem are you attempting to solve?

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

Re: [sqlite] Index usage

[Griggs, Donald]

Matthew,

Regarding: "There's no way to optimize your query to be fast in both
situations."

I do *not* know if this would be of any help, but the newest 3.1.18
sqlite release which includes the SQLITE_ENABLE_STAT2 feature may
possibly be of interest:  (and excuse me if you've mentioned this
already)

http://sqlite.org/compile.html#enable_stat2
SQLITE_ENABLE_STAT2

This option adds additional logic to the ANALYZE command and to the
query planner that can help SQLite to chose a better query plan under
certain situations. The ANALYZE command is enhanced to collect a
10-sample histogram of the data in each index and store that histogram
in the sqlite_stat2 table. The query planner will then use the histogram
data to help it estimate how many rows will be selected by a range
constraint in a WHERE clause.  

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

Re: [sqlite] replace extra carriage returns?

[Griggs, Donald]

Hi Matt,

Regarding:
"Is there a more comprehensive function list other than
http://www.sqlite.org/lang_corefunc.html;

Is there an sqlite-supported function that's not listed there, or are
you saying you want more functions?
If the latter, sqlite struggles to keep the "lite" on, but you can
define your own functions as desired.


Regarding:  "I just want to remove multiple carriage returns from a text
typed field."

I think you'll want to use 
  SELECT replace(field1, x'0d0a0d0a', x'0d0a');

Note that I'm assuming you already *have* the multiple cr/lf's in your
text field.   If you're importing data with the commandline utility, it
allows very flexible definition of *field* separators, but I don't think
you can redefine the *record* separator.  So to eliminate empty lines on
imported data, you could still do something like:
   -- Create a table with only one long field.  
   -- Use something very unlikely as field separator
   CREATE TABLE raw(line);
   .separator '#$%'
   .import 'myRawFile.txt'  raw
   -- delete any truly empty lines and output my clean data for later
re-import.
   DELETE FROM RAW WHERE STRLEN(line) < 1;
   .output 'myCleanFile.txt' 
   SELECT * FROM raw;
   .output stdout

   OR -- you could use a tiny program in awk, sed, perl, etc.

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

Re: [sqlite] create a view and add a column

[Griggs, Donald]

Regarding:
  "...I need to generate a view and add a ID column to that view. Is
this possible?"

I may well not be understanding your question, Tim.  If so, you may want
to provide more information, such as what software you're using to speak
to the database, do you work with SQL, etc.

If you *are* working with sql, a VIEW is pretty much shorthand for a
SELECT query.
You create a view by simply prepending "CREATE VIEW myViewName AS" in
front of your desired SELECT query.
  http://www.sqlite.org/lang_createview.html
E.g.
   CREATE VIEW myViewName AS SELECT (Name, Address) FROM PeopleTable;

To add a column to a view, the column must be in one of the tables in
the SELECT, or otherwise calculated by the select.  You drop the
existing VIEW and create the new one.
E.g.
  DROP VIEW myViewName;
  CREATE VIEW myViewName AS SELECT (Name, Address, ID) from
PeopleTable;




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

Re: [sqlite] How long should Insert take?

[Griggs, Donald]

Hi Rod,

Regarding: *Insert rate*: 7/sec.

You'll want to wrap your INSERT loop in a transaction, i.e., BEGIN /
COMMIT.

Without a transaction, each INSERT has to wait for multiple disk
revolutions.

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

If you have a huge number of inserts, you may want to get fancy and
start a new transaction every several thousand.

I think you'll find a marked difference.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filipe Madureira

Is there a way to get a list of attached databases?
Either by SQL or by function call?
==

Greetings, Filipe,

PRAGMA database_list;
  http://sqlite.org/pragma.html#schema
Or, if you're using the commandline utility:
  .databases

You may want to read the entire section on PRAGMAs as there are many
useful ones.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse string comparison for searches

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Friday, August 21, 2009 2:33 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Reverse string comparison for searches

Hello everyone,
Currently, in my database I'm storing thousands of
strings that are formatted in such a way where they share similar
prefixes (i.e. TXT_KEY_FOO, TXT_KEY_BAR).  Sadly, this format cannot be
changed so I was wondering if it would be possible and perhaps faster to
have SQLite perform reverse string comparisons for looking up specific
strings.  Has anyone done this?  Is it practical or worth doing?  These
lookups are performed very frequently so any amount of time that can be
shaved off will be noticed.
-Shaun
=
Hi Shaun,

If you only need to check for exact matches, would adding a column for
an integer hash of the strings be useful?  Of course you'd want to index
it.   You'd want to filter out potential hash collisions with something
like:
   WHERE hash_search = hash_db
   AND   string_search = string_db


 

 

___
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

Re: [sqlite] A problem with versions of SQLite

[Griggs, Donald]

Regarding:  "1. Provide a pre-compiled 2,1 (for Windows), or ..."

Hi Rod,

IN A SEPARATE PRIVATE EMAIL TO YOU  I've attached a windows executable
command line utility for version 2.13.


Conversion is easy.   Open the database using the version 2 executable
and use commands like ".output ascii.sql" followed by .dump and .quit

Then fire up a current version 3 executable and ".read ascii.sql"


Hope this helps,
   Donald
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: Porting to another Operating system.

[Griggs, Donald]

 Re: "I can't see how you get to that page."

I found the link from page:
http://www.sqlite.org/features.html
And perhaps it's linked from others as well.


Re: "I would expect to reorganise the physical database on a regular
basis..."

Once again, I'd look at the VACUUM command, though you may find you
don't really need to run it very often.
http://www.sqlite.org/lang_vacuum.html

You may also be interested in the ANALYZE command, which can provide
hints to sqlite's query optimizer.
http://www.sqlite.org/lang_analyze.html
Once you're up and running with your database app, running analyze just
once may be enough.

You might want to do some experiments to see if running either of these
frequently has a measurable benefit to your application.


Hope this helps,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: Porting to another Operating system.

[Griggs, Donald]

Hi, CityDev,

Regarding:
  All you have to do is copy  

That's handy - I didn't realise that. However I suggest it's good
practice to dump and reload in these kinds of situations. I don't yet
know how SQLite works but I suspect a reload will get the physical data
into a better shape and clear out deleted items etc. Do you know where's
there's documentation on this?

--

Dr. Hipp is the originator of sqlite, so it's hard to find advice more
authoritative than his.   For removing deleted items (which would be
reused as needed anyway), you may want to look at the VACUUM command --
it also rebuilds the db structures. 

If you still need an external ascii dump, there is a
command-line-interface utility documented at:
   http://www.sqlite.org/sqlite.html
The ".dump" command gives you sql which can rebuild a database (via the
".read" command). 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Db design question (so. like a tree)

[Griggs, Donald]

Regarding:
   I could start the id initially with 10 to allocate 

That WOULD allow for a bunch of bull.;-) 

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

Re: [sqlite] SQlite performance on multi process env

[Griggs, Donald]

Subject: Re: [sqlite] SQlite performance on multi process env

Hello, Zhrahman,

Regarding:   "... kindly suggest how to properly have the database
shared in memory among n number of processes. So they can execute select
operatins(read only no update on teh
database) effeciently."


If the large number of processes are unavoidable, you might want to
consider:
   1) Writing a server layer that would be the only process to open the
DB file and service the requests for the other processes.
   2) Modifying such a layer already written by others.
   3) Using a database designed for lots of simultaneous clients, such
as Postgres or MySql.  (I believe the succinct advice has been something
like, "Think of sqlite as a replacement for fwrite, not as a replacement
for Oracle.")
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check the healthy of database and the indexesofthe tables

[Griggs, Donald]

Joanne,

I don't want to imply that you *have* to take my suggested course of
action (and probably MOST of the folks on this list know better sqlite
than I), but, nonetheless, I'm still wondering about the results of my
suggestions.   Maybe if you could either give your results for each, or
a short reason why you don't think it's a good thing to try -- or even
just say you *choose* not to try it --- by providing the list with this
information it might greatly shorten your resolution time and the number
of emails.   As it stands, I don't even know if you've read them all.

Specifically,
1-- See if the problem can be reproduced using the command-line utility.
2-- Does adding "peerid" to your second select run without error?
3-- Do the suggestions for removing the index and rebuilding it (AFTER
saving your current database) sound reasonable?   Might a VACUUM be good
as well?

A new one:
   4-- If above does not resolve the difficulty, I would try a .dump
command on each of my tables.  

Hope this is some help,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check the healthy of database and the indexes ofthe tables

[Griggs, Donald]

Hello Joanne,

Regarding:  "Is there any command to check if the index or database in good 
condition."

That's why I listed the pragma below in my email of last night and repeat email 
earlier.

You're in luck if only the index is corrupted of course.

SAVE a copy of your current database (very important).

Try dropping the index and rebuilding it.
Even if ok at that point, you might want to then run a VACUUM.

==

Joanne,

I couldn't say, but if I were you I'd probably

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?   Does "Explain query plan" show that 
it is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

Regards,
  Donald
   

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Tuesday, May 05, 2009 4:02 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to check the healthy of database and the indexes ofthe 
tables

Hi All,
I had the database and one of the index is not good condition. Every time I use 
the index by select ... group by .. the result only return few rows and the 
message print  out that "database disk image is malformed". Is there any 
command to check if the index or database in good condition.
Thanks,
JP


  
___
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

Re: [sqlite] SQL error: database disk image is malformed

[Griggs, Donald]

Hi Joanne,

It's possible you'll have to resort to your most recent backup.   But before 
doing that, I wasn't sure from your reply that you saw the other points I 
listed. 

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?  Does "Explain query plan" show that it 
is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

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

Re: [sqlite] SQL error: database disk image is malformed

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Monday, May 04, 2009 2:51 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL error: database disk image is malformed

Hi All,
I ran the following sql statement:
    select blobid, fbid from sig group by peerid; return about 10 rows
        22
   ...
   33
return about 10 rows and I got the error message:
 SQL error: database disk image is malformed

but when I ran the following sql statement:
    select blobid, fbid from sig;
I didn't see any error message. So why the first sql statement has problem but 
not the second.
Thanks
JP
==
==

Joanne,

I couldn't say, but if I were you I'd probably

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?   Does "Explain query plan" show that 
it is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

Regards,
  Donald
  
___
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

Re: [sqlite] How to exit from sqlite shell?

[Griggs, Donald]

I find it easy to confuse myself if I have either an unclosed quote, an
unclosed "/*" comment,  or an unterminated command (semicolon).Below
is an example of two simultaneously.   


SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 'runaway quoted string
   ...> .quit
   ...> '
   ...> .quit
   ...> ;
SQL error: no such column: runaway quoted string
.quit
.quit
sqlite> .quit 

[it exits]

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

Re: [sqlite] How to create a table from a text file via c/c++ interface?

[Griggs, Donald]

Hi, Feng,

I don't think that "load data from" is part of the sql standard -- I may
be wrong.

At any rate, the source code to the command line utility is freely
available.

When you wrote that "Using ' insert into values' ... is very time
comsuming" did you mean time consuming to write the program, or
time-consuming to run?  If it is taking a very long time to RUN compare
to the sqlite3 ".import" command, then be sure you're surrounding the
import (or at least every 1000 lines or so) in a TRANSACTION.  Expect a
large increase in speed.

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

Perhaps this helps,
   Donald Griggs

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of knightfeng
Sent: Sunday, May 03, 2009 10:41 PM
To: sqlite-users
Subject: [sqlite] How to create a table from a text file via c/c++
interface?

Hi,
   
   '.import' can be used to create a table from text file in the command
line version , but how can I do that using C/C++ interface? Using '
insert into values' by reading a text file line by line is very time
comsuming and it seems that there is no such SQL command "LOAD DATA
FROM" supported by MySql in sqlite.  

Many thanks.

Zhixing Feng




2009-05-04 



knightfeng
___
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

Re: [sqlite] Difference between all the joins

[Griggs, Donald]

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrey Fedorov
Sent: Tuesday, April 21, 2009 1:27 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Difference between all the joins

Hi all,
According to the join-op syntax [1], SQLite has 13 distinct join
statements:

,
JOIN
LEFT JOIN
OUTER JOIN
LEFT OUTER JOIN
INNER JOIN
CROSS JOIN
NATURAL JOIN
NATURAL LEFT JOIN
NATURAL OUTER JOIN
NATURAL LEFT OUTER JOIN
NATURAL INNER JOIN
NATURAL CROSS JOIN

Are they all unique? Which are equivalent?

- Andrey

1. http://sqlite.org/syntaxdiagrams.html#join-op
___
___
___

Hello, Andrey,

No, many are synonyms included (I think) as part of the SQL standards or
to ease conversion from various other SQL dialects.

E.g. an "unadorned" JOIN is identical to an INNER JOIN.   Inner joins
have no "left" or "right" so a LEFT JOIN can be used as a synonym for a
LEFT OUTER JOIN.  Furthermore, LEFT is a default for OUTER JOINS, so
OUTER JOIN == LEFT JOIN == LEFT OUTER JOIN.

This article looks interesting:  http://en.wikipedia.org/wiki/Join_(SQL)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Appending Text to a Column

[Griggs, Donald]

Text concatenation is done with 
  ||
Two vertical bars (virgules).

update tblEntry set Tags = Tags || ' new text' where RowID = 13
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange sqlite_busy deadlock behavior

[Griggs, Donald]

Dave, 

Regarding: "I understand what a deadlock is, and I know it's not
technically a deadlock. This is why I stated the title as deadlock
behavior."

My apologies, Dave. I thought there might actually have been a
difference in definition that was adding to your problem.   After your
reply, though, I see that I had *not* actually seen your original post
as I thought I had.

Worse, I don't have the knowledge to help you much with your actual
problem (interpret stack trace, etc.) other than to say that others have
had success with multi-threaded apps though I understand they can be
vexing.

Am I right that you've determined for certain that you finalized and
committed on one thread yet still receive the BUSY for some time when
polling from the other thread?   

Hoping you and those more knowledgeable on this list than I  will work
out a fix shortly,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange sqlite_busy deadlock behavior

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Brown
Sent: Wednesday, April 08, 2009 1:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Strange sqlite_busy deadlock behavior

I tried the BEGIN EXCLUSIVE method, but now the problem is that thread-A
is in the middle of a query doing sqlite3_step() to get results, and
thread-B tries a "begin exclusive" and gets back SQLITE_BUSY  in the
deadlock situation :)

I guess I am forced to use your 2nd method??



Hi Dave,

A *deadlock* would mean that neither process will ever proceed.
http://en.wikipedia.org/wiki/Deadlock

In your situation, Thread-B would simply wait until thread-A finishes
--- either via some sort of inter-thread communication, or via polling
on the part of thread-B. 
Thread-B experiences a delay, but no deadlock occurs.

Presumably, thread-A is designed to "step lively" and not dally
unnecessarily.

Of course, the 2nd method is great if you have no concerns about
isolation.
  ("PRAGMA read_uncommitted=ON")

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

Re: [sqlite] Possible bug

[Griggs, Donald]

 Hi, Arthur,

This mailing list, like many, will automatically strip attachments.  Posting 
onto your own webspace is a great way, as you have discovered, to reference 
files.

I'm writing just to say that I *did* reproduce a problem.

I'm using Windows XP SP3.

I narrowed the problem to the sql below.  The fault occurs on the insert 
command.

Using the windows binary for 3.6.11, I have no problem, but 3.6.12 results in a 
fault.

Changing the "DEFAULT  (datetime( 'now', 'localtime' )  )" to a simple constant 
such as 'puppies' avoids the fault.

However, doing a simple:
SELECT datetime( 'now', 'localtime' );
does *not* cause a fault.

Regards,  
   Donald

===
.echo on

-- registrierten Nicks mit zugehörigkeit zu Accounts
CREATE TABLE IF NOT EXISTS nickList (
nickID   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
ownerID  INTEGER NOT NULL,
nickName VARCHAR NOT NULL UNIQUE,
registeredAt VARCHAR NOT NULL  DEFAULT  (datetime( 'now', 'localtime' ) 
 )
);



INSERT 
INTO nickList 
 ( nickID, ownerID, nickName ) 
VALUES
( -1, -1, 'Unknown2');


select * from nickList;
.quit
==
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] export table to csv

[Griggs, Donald]

Hi Richard,

The sqlite3 utility allows a *single* dot-command on the command line
itself.
You can have an unlimited number of commands in a separate text file,
though.

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

So, using Windows syntax, you can have something like:

===
Set MyTempFile=%temp%\Mytemp.tmp

echo .separator ,>%myTempFile%
Echo .output thisisit.csv >>%myTempFile%
Echo select * from iotemplate;>>%myTempFile% 
Echo .output stdout   >>%myTempFile%

Sqlite3.exe ioimport.db3 ".read %myTempFile%"

Del  %mytempfile% >nul


If you're using linux/unix, you can redirect input to the your shell
script and avoid the temporary file.

Hope this helps,
Donald 


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Nero
Sent: Monday, March 30, 2009 11:12 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] export table to csv

All,

I can successfully import a databese via command line with:

sqlite3.exe -separator , ioimport.db3 ".import Temp.csv iofromexcel"

Now i am trying to export a table in the db with:

sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv
"select
* from iotemplate;" .output stdout

This does not work and I have tried all solutions possible. Can someone
lead me in the right direction on how to do this via command line

Thanks in advance!
___
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

Re: [sqlite] creating unique data takes many hours, help

[Griggs, Donald]

Hi Michael,


Two thoughts -- and I hope others will chime in if I'm off-base here:


1) Build just one index after the import:

>From page:
http://www.sqlite.org/lang_createtable.html
"The UNIQUE constraint causes an unique index to be created on the
specified columns."

I think that if you then create your own index, it will be redundant,
and, with millions of records, time-consuming.

However, you may well want to KEEP your own index (and add the UNIQUE
constrait to it), and *remove* the UNIQUE constraint on the table column
definition.   I hear that creating the index after the data has been
imported is faster (and less fragmented) than creating an implicit one
on-the-fly during import.



2) Hashing

If Vinnie [thev...@yahoo.com] was correct in guessing your goal (to
determine if a given string has been examined before), and if
performance remains a problem, I wondered if the design might benefit
from using hashing to "pre-qualify" a string and/or substitute for a
direct match.

A short hash (perhaps a 64-bit integer?) could be stored in a separate
table or even a separate database from the strings themselves.  If the
hash of a new string does not match any hash in the database, then you
know there is no need to search for the string itself.  If the entire
hash index can be kept in RAM, you might get a big benefit.

You could decide to take this a step further by using a longer hash as a
proxy for the string itself.  The hash would need to be long enough to
make collisions extremely unlikely, yet not so long as to negate the
value of using a proxy for the original strings.  In practice, you'd
probably want to compute a single long hash for each string, then use
the first X bytes as the "pre-qualifying" hash, and the remainder as the
"confirming hash." If your average string length is short, then using
*two* hashes may not be much of a gain. 

(Of course, since this is not a cryptographic application, you don't
need to worry about whether your chosen hash algorithm is "secure" or
not.)

Regards,
   Donald



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Vinnie
Sent: Sunday, March 29, 2009 6:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] creating unique data takes many hours, help


Michael:

While my answer isn't strictly limited to sqlite, the performance nut in
me can't help myself. You have a a table with only one column, a string.
I could be wrong here but it seems like you just want to keep a list of
values that you have already tried. After you insert a bunch of strings
into the table you want to be able to quickly look up a string to see if
it exists, so that you can tell if you already probed that sequence
(taking a guess here).

If my guess is right, and the only thing you are doing is looking up
sorted single-column elements, you probably can get away with your own
quick disk-based binary tree implementation and avoid sqlite for this
particular circumstance altogether. The result would be several orders
of magnitude faster, even after you have followed the suggestions others
have given.




> Hi,
> 
> I am new with sqlite, and I create a program that reads several mllion

> records and puts them into a sqlite db using.
> 
> The table has one column ONLY indexed and unique, but it takes many 
> hours.
> 
> Is it necessary to pre-allocate the space, or is the anything that I 
> can do to reduce the time it takes.
> 
> this is how I create the db, table and index.

___
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

Re: [sqlite] Documentation on SQLite indirectBLOB?

[Griggs, Donald]

Hi Mark,

There's a good reason for your difficulty -- Sqlite uses dynamic typing:
http://www.sqlite.org/datatype3.html

As far as I know, the term "IndirectBlob" is not defined within sqlite
and is likely a term from the BitPim developers.

Sqlite allows one to use most anything as a type -- "BlobThatAteNewYork"
would work quite nicely.

>From sqlite's point-of-view, if a BLOB contains structure, it's
structure known only to the calling application.

Hope this helps,
  Donald

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MBR
Sent: Wednesday, March 25, 2009 6:09 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Documentation on SQLite indirectBLOB?

The Open Source project BitPim uses SQLite.  I'm trying to examine the
database BitPim creates.  At http://www.sqlite.org/download.html I found
sqlite-3_6_11.zip, which is a pre-compiled Windows command-line SQL
interface.

I ran:

sqlite3 bitpim.db

then typed:

.dump

and saved the output to see what's in the database.  The result is a set
of SQL queries that can be used to recreate the database.  In the output
I found numerous references to columns of type indirectBLOB.  When I
look at the data that gets inserted into those columns, it's pretty
clear that the value in those columns is of the form:

/tableName/,/recordId/,/recordId/,...

But I've been unable to find documentation on the keyword 'indirect' or
'indirectBLOB' anywhere on the web, including
http://www.sqlite.org/docs.html.  Can anyone here tell me where to find
that documentation?

Mark Rosenthal
m...@arlsoft.com 

___
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

Re: [sqlite] Replacement for COPY

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BOB_GOREN
Sent: Tuesday, March 24, 2009 10:49 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Replacement for COPY

Sqlite 2.8.13 supported statements like:

COPY ar FROM xxx.odb USING DELIMITERS |

 

What is the equivalent in sqlite 3.6.11? 

COPY seems to no longer be supported - the same statement that works on
2.8 now generates a syntax error and COPY and DELIMITERS are not in the
list of keywords for 3.6.
==


Hi Bob,

I found this page:  http://www.hwaci.com/sw/sqlite/lang_copy.html
which directs developers to the ".import" command within the sqlite3
command-line utility.

If you must have this capability in your code (versus the standalone
utility) then you might want to look at the source for ".import"

Alternatively, you might want to seek out some good routines in your
language of choice for handling delimited text.  The easy "trick" to
quick imports, as you're probably already aware, will be to combine many
or all of your sql INSERTS within a transaction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problems with shared cache?

[Griggs, Donald]

 

-Original Message-


On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald <donald.gri...@allscripts.com> 
wrote:
>>>>>
>>>>>> However, when I ask the user to send me their deck, I find that:
>>>>>>
>>>>>> sqlite> pragma integrity_check;
>>>>>> integrity_check
>>>>>> ---
>>>>>> ok
>>>>>> sqlite> select id, count(id) from cards group by id having
>>>>>> count(id)
>>>>>>> 1;
>>>>>> sqlite>
>>>>>>
>>>>>> Any ideas?
>
> Obviously, that user is not playing with a full deck.   ;-)
>

=
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Damien Elmes
Sent: Tuesday, March 24, 2009 2:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] problems with shared cache?

Sorry, my application's files are called decks, and I unwittingly used the 
wrong terminology.

Any ideas about the problem?


Damien,

I, if anyone, should apologize -- was just an attempt at humor using an English 
language idiom metaphor for card games.

I'm afraid I don't know enough to help with your shared cache problem (posted 
19 March), sorry.  It's fortunate, I suppose, that your particular application 
didn't really need it.

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

Re: [sqlite] Export the results from a select to excel

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of d...@dommel.be
Sent: Monday, March 23, 2009 11:28 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Export the results from a select to excel

Hello,

what is the fastest way to do this with the C-api?

tx,

Danny
Belgium

===

Hello, Danny from Belgium,

Do you mean fastest to program or fastest to run?   Most any
reasonably-sized spreadsheet shouldn't take all that long to be
exported, regardless of method.

You can simply output your SELECT with tab or comma separators, then
Excel can read up the file.

If you need the spreadsheet to be fancy, with nice formatting and
headers pre-set, then I'll leave it to someone more knowledgeable to
respond.

The fastest method to *develop* would likely be to use the SQLITE3
utility program with a tiny script something like:

Sqlite3  MyDatabase.db 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

Re: [sqlite] problems with shared cache?

[Griggs, Donald]

> However, when I ask the user to send me their deck, I find that:
>
> sqlite> pragma integrity_check;
> integrity_check
> ---
> ok
> sqlite> select id, count(id) from cards group by id having
> count(id)
>> 1;
> sqlite>
>
> Any ideas? 

Obviously, that user is not playing with a full deck.   ;-)


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

Re: [sqlite] how many tables can I have in one database

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
mrobi...@cs.fiu.edu
Sent: Wednesday, March 18, 2009 7:26 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how many tables can I have in one database

Dear colleagues,

I understand that Sqlite can handle databases with 2 teragytes of data
each. I would like to know how many tables can a dabase have.

Thank you

Michael
=

Hi, Michael,

I *think* the answer will be, "As many as you need."  

On the other hand, if you're contemplating a design involving hundreds
of tables, then many would say that the design would likely benefit from
some serious consideration as to normalization.

Approximately how many tables do you envision using?   

There *is* a limit of 64 tables in one JOIN statement, by the way.

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

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

Re: [sqlite] Speed of DROP INDEX

[Griggs, Donald]

 Re: [sqlite] Speed of DROP INDEX

This may not be useful to your situation, but my (not terribly informed)
*guess* is that the reason it takes so long is that the index pages are
spread throughout your 8-millon row database.

If by chance it's feasible to either:
  -- Not create the index until the table is populated, or
  -- Run vacuum at some "non busy" time between the table creation and
the need for index deletion
Then perhaps the delete would be quicker.   A side benefit might be
faster lookups.

Again, even if I'm corrent, this may not be feasible for your
application.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] httpd server ???

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ken
Sent: Tuesday, March 17, 2009 7:31 PM
To: sqlite
Subject: [sqlite] httpd server ???



===

Hi, Ken,

Mr. Hipp's Tiny Web Server is found at:

http://www.hwaci.com/sw/tws/

Quoting from the page:

Embeddable   The core HTTP engine used by TWS is modular and
implemented in just two source files. It is very easy to embed this HTTP
engine inside another application to give that application the ability
to report its status or to be configured dynamically using a web
browser. 


___
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

Re: [sqlite] SQLITE : documentation and search topics

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
REPKA_Maxime_NeufBox
Sent: Monday, March 16, 2009 3:11 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLITE : documentation and search topics

Hello,

A main remark to SQLite developers :
  - Does it exist a Help file .chm for SQLite ?
I am quite new user with Database and SQLite and find very hard to get
information and documentation.
My opinion is that it is totaly NOT intuitive on the web to find
information.
For comparison I am quite new on Autohotkey software (autohokey.com) and
I have no problem to find  information and answer on problem (most of
the time I find the answer without posting a question).

-> Did I miss something on SQLite web ? or does other users think like 
-> me
that some improvment should be done ?

MaxMax14

==
Bonjour, Maxime,

I'm *not* an sqlite developer, but maybe I can provide a portion of an
answer.

-- CHM -- 

There's no .chm file for a couple of reasons.   Mainly, .chm is a
Microsoft application help format for Windows, and Windows is just one
of the many operating systems on which sqlite is run.   Secondly, sqlite
is not really an application (not like, say, Microsoft Access) -- it is
instead an embeddable SQL engine intended for programmers.   That being
said, there *is* also provided a command line tool called "sqlite3"
intended mainly for testing, but which is useful in it's own right.  If
you run it, it will let you know that a consise help screen is available
using the ".help" command.

The full source code (very well commented) is available for both the
sqlite engine and the command line  utility.

There is not to say that other folks have not written end-user
applications using Sqlite, of course, and there are a number of
applications that are GUI database administration tools that may or may
not have good online help included -- you'd want to approach those
developers in that regard.


-- Your current problem --

I take it you likely have something in particular you would like to know
about sqlite?  Since you haven't found it on the website, you may want
to post your particular problem here.


-- Documentation in general --

On the one hand, each person would organize a website differently, and
all will never be pleased.   But on the other hand, sometimes someone
unacquainted with the product can provide some of the most useful
feedback, so you may want to post some specific suggestions you may
have.

I find that it's very helpful to visit the wiki -- and of course you may
submit wiki improvements quite directly.

Hope this may help in some way,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE : Constraint question

[Griggs, Donald]

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wolfgang Enzinger
Sent: Sunday, March 15, 2009 1:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLITE : Constraint question



BTW, is there a document that explains in more detail what operations
the CHECK constraint is capable of?



Hello, Wolfgang,

Were you looking for something beyond the documentation under "CREATE
TABLE"?

 Column constraints:
 http://www.sqlite.org/syntaxdiagrams.html#column-constraint

 Table constraints:
 http://www.sqlite.org/syntaxdiagrams.html#table-constraint

 Conflict clause:
 http://www.sqlite.org/syntaxdiagrams.html#conflict-clause

 Foreign key clause (enforceable with preprocessing to create triggers):
 http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause

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

Re: [sqlite] Slow performance with Sum function

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Trainor, Chris
Sent: Wednesday, March 11, 2009 5:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow performance with Sum function

> Do not be tempted by the incremental vacuum feature.  Incremental 
> vacuum will reduce the database size as content is deleted, but it 
> will not reduce fragmentation.  In fact, incremental vacuum will 
> likely increase fragmentation.  Incremental vacuum is just a variation

> on auto_vacuum.  It is designed for flash memory with zero seek
latency.

> D. Richard Hipp
> d...@hwaci.com

Thanks for the reply, but I am confused again.  Is incremental vacuum
different from the vacuum command?  It seems like vacuum would
defragment the database according to the description here:
http://www.sqlite.org/lang_vacuum.html 

=
No, the auto_vacuum command differs from the regular vacuum command.
The auto_vacuum command does not reduce (and may increase
fragmentation).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions and attached databases

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tito Ciuro
Sent: Wednesday, February 25, 2009 6:36 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Transactions and attached databases

Hello,

If I attach one or more databases and wrap a series of operations which
affect some/all of them, would ROLLBACK or COMMIT treat these operations
atomically?  For example:

Open database 'foo';
Attach database 'bar' as a1;

BEGIN TRANSACTION;
  INSERT INTO main.some_table ... ;
  DELETE FROM main.some_table WHERE ... ;
  INSERT INTO a1.another_table ... ; COMMIT/ROLLBACK TRANSACTION;

Would the insertions and deletions be either committed or rolled back
atomically? Is this supported?

Thanks,

-- Tito


===
===

Greetings, Tito,

Did you see page:
http://www.sqlite.org/lang_attach.html


Transactions involving multiple attached databases are atomic, assuming
that the main database is not ":memory:". 

It then goes on to say:

If the main database is ":memory:" then transactions continue to be
atomic within each individual database file. But if the host computer
crashes in the middle of a COMMIT where two or more database files are
updated, some of those files might get the changes where others might
not. 

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

Re: [sqlite] SQLite caching

[Griggs, Donald]

From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of manohar s
Sent: Monday, February 23, 2009 4:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite caching

Thanks for your quick replies, although restarting my machine, disc
cache is cleared, I am trying to find an utility which could do the job
without requiring a restart.

Regards,
Manohar.S 


Greetings, Manohar,

You can do something like close your sqlite application, copy a large
unrelated file to the nul device, then restart your app -- that should
clear the cache of the sqlite data (if the o.s. uses a "least recently
used" cache instead of "least frequently used" cache -- someone more
knowledgeable may be able to say for a particular O.S.)

However, most any well-performing database is going to depend on a good
RAM cache -- either from the operating system or within it's own code --
or both.

And it's already been mentioned that what you're seeing is not caching
of your RESULTS but instead caching of portions of the database itself.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] number of columns in a table

[Griggs, Donald]

hi,

my question is how to get the number of columns in an existing table.
example if i can retrieve the number of rows with SELECT count(*) FROM
table , is there also some simple query to do the same thing for
counting the number of columns


==

Hello, "r"

You can use sql to query the system table named:  sqlite_master
Also see
   http://www.sqlite.org/pragma.html#schema

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

Re: [sqlite] Hundreds of thousands of INSERTs

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Tuesday, February 17, 2009 4:55 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Hundreds of thousands of INSERTs

Griggs, Donald wrote:
> If you really have to have even more performance, you could cache 
> inserts in RAM and wrap multiple INSERTS with a single transaction 
> when you have a certain number cached (or if a max delay is reached).
>   
shouldn't varying the transaction size have the same effect? moving from
4096 to 8192 didn't show any improvement.

=
Paolo,

My apologies -- you wrote in your original post:
   "one INSERT per row."
Which I mis-remembered as "One INSERT per TRANSACTION."

If you're already inserting 4096 rows per transaction -- then, yes,
you're right -- I wouldn't be surprized that even larger groupings don't
improve performance.

If you think you may be disk-bound rather than CPU-bound, I don't know
if going to a 15,000 RPM disk would be a possibility -- assuming you're
on something slower now -- and that the cost could be justified.

Donald


___
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

Re: [sqlite] Hundreds of thousands of INSERTs

[Griggs, Donald]

> p...@ferret:~/log_analyzer >date ; ./log_parser_ms_sqlite.pl 2 
> vmta2-2009-02-16_1400-success.log.bz2.done.ec.ed ; date Tue Feb 17 
> 15:07:40 CET 2009 Tue Feb 17 15:13:22 CET 2009
>
> it's a 75% shave of application time, amazing!!!
>
> --
>
If you really have to have even more performance, you could cache
inserts in RAM and wrap multiple INSERTS with a single transaction when
you have a certain number cached (or if a max delay is reached).

I noted that you were willing to consider
  PRAGMA synchronous = OFF 
so I assume that you could tolerate a little data loss in case of a
crash.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full Table Read

[Griggs, Donald]

 

Griggs, Donald wrote:
>  > On linux you can do
>   
>> dd if=database.db of=/dev/null bs=1M
>> and after perform "select ..."
>> 
>
> =
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs
> Subject: Re: [sqlite] Full Table Read
>
> We are actually using Windows XP for an OS.  (I know, lots of
> limitations, but that is what we have to use).
>
> =
>
> I think the windows equivalent would be:
>copy /b database.db nul
>
> In either case, we're assuming your database fits into available
> ram-cached virtual space.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
=
Biggs wrote:

Is that just creating an in-memory database then?  I'm not familiar with
a copy to a null location.
=  

Nothing quite that fancy.   It's just attempting to get most or all of
your database into the read-cache of the operating system.   (I probably
was not accurate earlier when I wrote "virtual space" since I don't know
how the read-cache is implemented.)

Your performance, after a successful loading into cache, should be about
that of a ram-based  database -- without the data integrity and
synchronization problems a ramdisk-based database can cause.

You didn't mention any "order by" clause on your "select *".   If you
*do* require a an ordering of the output then an index, of course, could
possibly make a large difference.

Instead of the copy to a nul device, you could also try running a vacuum
command -- since it   can "defragment" a table who's portions are spread
throughout the file.

All this assumes that the time to perform the copy or vacuum is
tolerable in your application.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full Table Read

[Griggs, Donald]

 > On linux you can do
> dd if=database.db of=/dev/null bs=1M
> and after perform "select ..."

=
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs
Subject: Re: [sqlite] Full Table Read

We are actually using Windows XP for an OS.  (I know, lots of
limitations, but that is what we have to use).

=

I think the windows equivalent would be:
   copy /b database.db nul

In either case, we're assuming your database fits into available
ram-cached virtual space.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limits in SQLite

[Griggs, Donald]

Greetings, Ha Le,

When you asked "What are the factors which determine the different limits", I 
suspect I'm not understanding just what you're asking.   

You referenced
  http://www.sqlite.org/limits.html
And one of your two examples was "Maximum Depth Of An Expression Tree"

The webpage reports:
==
Maximum Depth Of An Expression Tree
 ... The depth of expression trees is therefore limited in order to avoid using 
too much stack space. 

The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression tree 
depth. If the value is 0, then no limit is enforced. The current implementation 
has a default value of 1000. 
=

All software running on real machines has limits.   Most of the limits in 
sqlite are so high that if one is approaching them, there's a fairly good 
chance one's design is not going to work well with sqlite anyway.

The tree depth explanation from the webpage describes why the depth is limited, 
plus it lets you increase or decrease the default maximum as your needs 
dictate.  The default of 1000 is quite high.

What further information were you looking for?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fast and simple database merge

[Griggs, Donald]

 Regarding: "...Really cool would be something like:
 cat db1.dat db2.dat db3.dat > final.dat"


I don't think there are any "really cool cat's" available.   ;-)
The format of the database is fairly fancy, though it is documented if
you think you'd be interested in writing a utility against it.

You can use the command line utility with its ".dump" command to output
the schema and data for the databases to be appended, then run these
against the original database to add in the new tables and data.

It will be slower, since the data must be dumped as well as inserted,
but if you don't have any conflicting names in your schemas, maybe this
would save you writing code. (If you can delay adding indicies until
after adding the data you may see some speed improvements.)


Don't know if this helps,
   Donald

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

Re: [sqlite] Index keywords

[Griggs, Donald]

Hi Vance,

Could you maybe give one or two examples on exactly what you need that
you're not finding on the website?   

As far as syntax, it's pretty much all linked from:
  http://www.sqlite.org/lang.html

E.g. for automatically incrementing primary key, see:
   http://www.sqlite.org/lang_createtable.html#rowid
   (if you must have an auto-incrementing key other than an integer
primary key, you will need to implement it yourself via a trigger)

As far as typing, sqlite differs from many other databases:
  http://www.sqlite.org/datatype3.html 

As far as a manual on SQL itself, yes, you would need to look elsewhere.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction behavior with large numbers of inserts

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Wednesday, January 28, 2009 1:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Transaction behavior with large numbers of inserts

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Ian Frosst wrote:
> My question is; am I assessing the situation correctly, and if so, is 
> there anything I can do to avoid this rather nasty situation?  I would

> rather not take the periodic-commit approach, and increasing the page 
> cache size would potentially cause the use of more memory than I can 
> spare.  Disk usage, however, is not a problem; I can use as much as is
necessary.

You can open a second connection and make another database that you do
all your writing to, using as small a page cache as you want.  When done
add the data in your first connection.  Basically do this ("main" is the
name of the first database):

  attach second.db as second
  insert into main.table (select * from second.table)
  detach second

Roger
===

Roger, I am probably missing something obvious here, but I was confused.

The INSERT into main.table (with its associated SELECT) would still be
an implicit transaction and of course would require writing to the
database of the million records.

During the rather long period of time for the INSERT to complete,
wouldn't Ian's original problem still exist (and perhaps be worse
because of the added delays and possible disk arm contention)?

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

Re: [sqlite] help : sqlite database restore

[Griggs, Donald]

Venkat,

Sorry.  I really intended to quote page:
 http://www.sqlite.org/sqlite.html 
Though
 http://www.sqlite.org/quickstart.html
May be helpful as well.

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

Re: [sqlite] help : sqlite database restore

[Griggs, Donald]

On 1/27/09, Griggs, Donald <donald.gri...@allscripts.com> wrote:
>
> Venkat,
>
> If you have a ".db" file it almost certainly *is* an sqlite database.
> A great attribute of sqlite databases is that they are contained in a 
> single file.
>
> If you're looking for a GUI frontend, they are legion, including good 
> free ones such as sqlite3explorer and a plug-in sqliteManager for 
> firefox webbrowser.
>   See http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
>
> Donald
>
=

Hi Donald,

Thanks for the link thats helpful, How do i access the .db file and read
the data...
=

Venkat,

There may be a bit of a language barrier here.  Each of the GUI sqlite
utilities can be used to access and read the data in an sqlite database.
Each has it's own instructions.  Often, one chooses to "open" the
database file from within the GUI.  One then supplies the desired SQL
instructions and asks that they be "executed" by some means.  There are
even ODBC wrappers available so that you may even be able to use some
tool with which you are already familiar.

All of the data, of course, is also available by using the Command Line
Utility provided as part of the official release.  Have you downloaded
the utility and/or a GUI front-end?  Have you had trouble with the
directions provided by them?

If you're comfortable with the command line utility, for instance, have
you had trouble after reading page,
http://www.sqlite.org/quickstart.html ?

If so, you may need to elaborate on "access" and "read the data" since
I'm not as sure as I was earlier that I understand the difficulty you're
having.   

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

Re: [sqlite] help : sqlite database restore

[Griggs, Donald]

Venkat,

If you have a ".db" file it almost certainly *is* an sqlite database.
A great attribute of sqlite databases is that they are contained in a
single file.

If you're looking for a GUI frontend, they are legion, including good
free ones such as sqlite3explorer and a plug-in sqliteManager for
firefox webbrowser.
   See http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

Donald
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of venkat karri
Sent: Tuesday, January 27, 2009 3:25 PM
To: punk...@eidesis.org; General Discussion of SQLite Database
Subject: Re: [sqlite] help : sqlite database restore

This morning I had a meeting with the team and found that we also have a
db file, could you please send me the complete syntax to import or
restore this .db file in sqlite.

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

Re: [sqlite] Is this a bug?

[Griggs, Donald]

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J. R. Westmoreland

Is there a way to get an autoincrement field without being a primary
key?


 Yes, you can create a trigger to do this.  (This also allows you great
flexibility, since you can, for instance, use an increment other than 1,
include a check digit, etc., but it's not "built-in" as the one for
primary key can be.)

Hope this helps,
   Donald

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

Re: [sqlite] Recover deleted records

[Griggs, Donald]

Hi Alex

I can't answer your specific questions, but I presume you've already
studied the file format document (most recently mentioned by Roger
Binn's recent post):

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

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

[sqlite] FW: A list as an SQL paramater

[Griggs, Donald]

 
Regarding:
   "This would definitely work but does incur the overhead of
sqlite3_prepare each time I want to run the query."




Maybe you would be preparing hundreds or thousands of different queries
per minute -- but if by chance you're not -- could anyone be expected to
notice the time for the prepare's?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7

[Griggs, Donald]

Subject: [sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7

Hi,

I consider upgrade sqlite 3.3.4 to sqlite 3.6.7. So, I wonder there is
any change (or problem) of file format.
===

Upgrading from version 2 to version 3 (understandably) required a dump
and restore, but not upgrading from one version 3 to a newer one.

Following is from page: http://sqlite.org/different.html

Stable Cross-Platform Database File

The SQLite file format is cross-platform. A database file written on one
machine can be copied to and used on a different machine with a
different architecture. Big-endian or little-endian, 32-bit or 64-bit
does not matter. All machines use the same file format. Furthermore, the
developers have pledged to keep the file format stable and backwards
compatible, so newer versions of SQLite can read and write older
database files. 

Most other SQL database engines require you to dump and restore the
database when moving from one platform to another and often when
upgrading to a newer version of the software. 


 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Edward J. Yoon
Sent: Tuesday, January 13, 2009 2:45 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7

Hi,

I consider upgrade sqlite 3.3.4 to sqlite 3.6.7. So, I wonder there is
any change (or problem) of file format.

--
Best Regards, Edward J. Yoon @ NHN, corp.
edwardy...@apache.org
http://blog.udanax.org
___
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

Re: [sqlite] Bool Values

[Griggs, Donald]

A design constraint of SQLITE was to avoid adding a bunch of bool
present in other DB's.

(Bad joke, couldn't resist) 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Emil Obermayr
Sent: Friday, January 09, 2009 2:58 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Bool Values

Is there a way to use bool-values to make migration from other DB
easier?

e.g.

select * from address where local = true

like defining true as a contant that represents a numeric 1?
___
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

Re: [sqlite] "meta command" via string via shell?

[Griggs, Donald]

Regarding:
   
  "...but this doesn't work because the .mode tabs isn't separated from
the regular SQL.
  Could someone point out how to accomplish this?"


If I understand correctly, all you need to do is write the desired
commands out to a text file, then either direct stdin to the text file,
or use the '.read" command. 

With Windows, FWIW, I have more luck with .read  , e.g.
Sqlite3 NLSY.db ".read MyFileOfCommands.sql"

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

Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER and .import for very widefile

[Griggs, Donald]

Regarding:
>> I am sure there is a better way to deal with 12K rows by 2500 
>> columns, but I can't figure it out

I wonder if you might want to use *sed* or *awk* or *perl* to preprocess
the data before import.

A "master" table could contain the unique person id, plus the fields
that you intend to index and that you are likely to filter upon most
often.  Other tables could exist for the remaining data, and could be
joined on the person id as needed.

This might:
   -- let you avoid a customized version of sqlite
   -- allow your most-used queries to run faster

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

Re: [sqlite] confusing with how to to this in sqlite

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rachmat Febfauza
Sent: Sunday, December 28, 2008 9:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] confusing with how to to this in sqlite

thanks simon for the explanation.

after holiday, i works on how to optimize my query. actually awal1 table
consist 12000 rows and akhir1 too. how to improve performance?

i added index on table awal1 and akhir1 with following syntax :

create index awal1i1 on awal1(Code,Category,Product,Location,"Begin");
create index akhir1i1 on akhir1(Code,Category,Product,Location,"End");

is this create index syntax right? or i must specify each column with
individual index?? like create index awal1i1 on awal1(Code); create
index awal1i2 on awal1(Product); etc

and i want to know to to improve performance of my query? some hint?

i have one question again, is sqlite suitable for large database file?
coz my apps may grow up to 1 giga database file.

thanks again

=
=

Regarding syntax:
If you don't get an error, the syntax is acceptible.;-)

Sqlite *does* support compound indicies.  However:
   -- You may want to use "EXPLAIN QUERY PLAN" as a prefix to your
SELECT (just running as a test) to ensure than your index is used.
   -- You can quickly experiment with using a simple index on "BEGIN" or
"PRODUCT" instead and measure times.
   -- As you measure times, be aware of possible "caching effects" --
i.e. the first run may be slower than subsequent runs of the a query on
the same tables.
   -- Make sure you see the link on performance, below.
   -- Make sure you include many INCLUDES within a single TRANSACTION
(if appropriate to your application).  This can make a dramatic
difference.
   -- You want to be familiar with the PRAGMA's that can affect
performance.
 http://www.sqlite.org/pragma.html   (but note that some of
these can be used to trade data safety for performance -- make sure
you're making an informed choice)


Regarding:
" is sqlite suitable for large database file? coz my apps may grow
up to 1 giga database file."

Have you read http://www.sqlite.org/whentouse.html 
And http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations ?  If
not, you'll want to.

Many folks successfully run sqlite on multi-gigabyte databases, BUT 
-- in those cases, the simplicity and small footprint of sqlite may
be less compelling,
-- Are there any features in  http://www.sqlite.org/omitted.html
that you will grieve in their absence?  You might go over the detailed
feature lists for postgres, Mysql, etc. with the same question in mind.
-- How much concurant access do you anticipate?
-- Will you control the queries (so as to optimize them and the
indicies) or will the database be subjected frequently to ad hoc queries
(which *might* benefit from a sophisticated query optimizer)?

Hope this helps,
  Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IN expression performance

[Griggs, Donald]

 Regarding:
   Is there a way to use a prepared statement and bind a (variable)
array of integers?

Like in:
SELECT  FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15
... )

Becomes:
SELECT  FROM table WHERE someinteger IN ( ? )


I don't know that you can bind these, but
do I remember correctly that these integers are selected by human users?
If so, I guess the savings in time would be unmeasurably small, right?
Or do your users type the string directly were you aiming to reduce sql
injections?

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

Re: [sqlite] REGEXP

[Griggs, Donald]

Regarding:  "...No regexp() user function is defined by default..."

The reason, if I recall correctly, is that the regexp routines that were
available were GPL'd and would mess with the licensing of sqlite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data dump in sqlite

[Griggs, Donald]

I'm not certain I understand your question, but here goes:

Sqlite3.exe is a command-line interface, so you don't really "browse" at
all.

However, you can specify the path and name of a database on the command
line when you invoke sqlite3, e.g. (In windows):

Sqlite3 "c:\NiceDirectory_Without_spaces\MyDatabase.db" 

The quotes are usually optional, but not when your path contains spaces

ALSO, you may want to see the ATTACH command in the documentation.

Once inside sqlite3, the .databases command should produce SOME output,
even if you haven't opened an existing database.   

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Manisha De Silva
Sent: Tuesday, December 09, 2008 10:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] data dump in sqlite

I downloaded sqlite3.exe

I typed in .databases and it doesn't show any database. Actually I have
sqlite db in C:\Program Files\Jabber Inc\Jabber XCP\var\spool and I want
this to dump the data to a text file. 

I typed in sqlite> .databases

How can I browse to the specific folder name which contains the jsm.db
sqlite db file This message (and any associated files) 
=
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users