Re: [sqlite] System.Data.SQLite won't work untill I install+uninstallit.

[Joe Mistachkin]

Wamiduku wrote:
> 
> Here are the steps to reproduce the problem:
> 1 - Start with a clean Windows machine (I've tried with both XP SP3
> and Windows 7 SP1) with nothing installed (I used virtual machines for
> testing).
> 2 - Install .NET 4.
> 3 - Download sqlite-netFx40-binary-bundle-Win32-2010-1.0.76.0.
> 4 - Run the included test.exe and click "Run". You'll get the error
> message
> "System.Configuration.ConfigurationErrorsException: Failed to find or
> load the registered .Net Framework Data Provider.
>at System.Data.Common.DbProviderFactories.GetFactory(DataRow
> providerRow)
>at test.TestCasesDialog.runButton_Click(Object sender, EventArgs e)
> in c:\dev\sqlite\dotnet\test\TestCasesDialog.cs:line 104"
> 

I'm not able to reproduce this locally.  As long as the "test.exe",
"test.exe.config", and "System.Data.SQLite.dll" files are in the same
directory, the tests should be able to complete.

>
> 5 - Download and run the sqlite-netFx40-setup-bundle-
> x86-2010-1.0.76.0.exe installation. Uncheck the two options to install
> to the image cache and GAC.
> 6 - After the installation, go to Control Panel and uninstall
> System.Data.SQLite.
> 7 - Run test.exe again. Now, it will work.
> 

If the "test.exe.config" and "System.Data.SQLite.dll" files are still
present along side it, yes it will work.

> 
> So, there is something that the installation does, which the uninstall
> doesn't undo, that you have to do in order to get System.Data.SQLite
> working. The question is what, and how can you do it without having to
> run the installation?
> 

The setup attempts to undo everything that it does, including the GAC and
NGen steps (if they were selected during installation).

--
Joe Mistachkin

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

[sqlite] System.Data.SQLite won't work untill I install+uninstall it.

[Wamiduku]

I've tried to create an install-free app (portable), but I can't get
System.Data.SQLite to work unless I install it. Once installed, my app
works and it keeps working even after I uninstall System.Data.SQLite.

The same is true for the tests (test.exe and testlinq.exe) included in
sqlite-netFx40-binary-bundle-x64-2010-1.0.76.0 and sqlite-netFx40-
binary-bundle-Win32-2010-1.0.76.0.

Here are the steps to reproduce the problem:
1 - Start with a clean Windows machine (I've tried with both XP SP3
and Windows 7 SP1) with nothing installed (I used virtual machines for
testing).
2 - Install .NET 4.
3 - Download sqlite-netFx40-binary-bundle-Win32-2010-1.0.76.0.
4 - Run the included test.exe and click "Run". You'll get the error
message
"System.Configuration.ConfigurationErrorsException: Failed to find or
load the registered .Net Framework Data Provider.
   at System.Data.Common.DbProviderFactories.GetFactory(DataRow
providerRow)
   at test.TestCasesDialog.runButton_Click(Object sender, EventArgs e)
in c:\dev\sqlite\dotnet\test\TestCasesDialog.cs:line 104"

5 - Download and run the sqlite-netFx40-setup-bundle-
x86-2010-1.0.76.0.exe installation. Uncheck the two options to install
to the image cache and GAC.
6 - After the installation, go to Control Panel and uninstall
System.Data.SQLite.
7 - Run test.exe again. Now, it will work.

So, there is something that the installation does, which the uninstall
doesn't undo, that you have to do in order to get System.Data.SQLite
working. The question is what, and how can you do it without having to
run the installation?

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

Re: [sqlite] Error Message near ".": syntax error

[Simon Slavin]

On 10 Oct 2011, at 2:06am, James Brison wrote:

> Very embarrassing ... I had a typo after doing a copy & paste.
> 
> Thanks All!

No problem.  We'd rather have a problem we can solve than one we can't.

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

Re: [sqlite] Error Message near ".": syntax error

[James Brison]

Very embarrassing ... I had a typo after doing a copy & paste.

Thanks All!





From: Simon Slavin 
To: James Brison ; General Discussion of SQLite Database 

Sent: Saturday, October 8, 2011 7:06 PM
Subject: Re: [sqlite]  Error Message near ".": syntax error


On 9 Oct 2011, at 2:28am, James Brison wrote:

> I am receiving the following error message when running a query against a 
> sqlite db:
> 
> Error Message near ".": syntax error
> 
> What does this mean? and how do I debug it?  I'm new to sqlite and don't 
> understand the 'near'.  Is it saying that I have syntax error somewhere 
> involving '.'?

In your SQL command you have a '.'.  Either that dot, or very soon after it, is 
a character that SQLite doesn't know what to do with.  If you can't figure it 
out, post the command and we'll tell you.

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

Re: [sqlite] Is it possible this optimize query on a very large database table

[Frank Chang]

Florian Weimer,
 
>>A better query needs support for DISTINCT ON, which SQLite lacks
>>AFAIK. But speed will obviously be limited because you cannot avoid
>>traversing the index for the whole table.
 
   I read your email and found it to be very interesting. PostgresSQL supports 
DISTINCT ON. However sqlite does not support it yet as you point you in your 
post. So, on the PostgresSQL thread  
http://www.postgresonline.com/journal/archives/4-Using-Distinct-ON-to-return-newest-order-for-each-customer.html
 , I found that they recommended simulating DISTINCT ON by using a subselect 
correlated subquery with either MAX(ROWID) OR MIN(ROWID) which is what I tried 
to do in my query.
I was wondering if you knew when sqlite might add the DISTINCT ON clause. 
Also , is there any way which you can suggest where  I can write a query or/and 
index which only traverses the index (CREATE INDEX claramary on 
BLOBLASTNAMETEST(FIELDNAME) for each distinct FIELDNAME/last name? Thank you.   

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

Re: [sqlite] Is it possible to optimize this query on a very large datatabase table

[Frank Chang]

Igor Tandetnik, Here is a comparison of my query plan with your query plan 
on the latest version of sqlite. 
 
sqlite> explain query plan select FieldName, min(rowid) from BlobLastNameTest 
group by FieldName;
0|0|0|SCAN TABLE BlobLastNameTest USING COVERING INDEX claramary (~100 rows)

sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1
 GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTes
t where FieldName = t1.FIELDNAME);
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary (~100
 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary (FieldName=?)
 (~1 rows)

 
Your query plan apparently traverses the claramary index(CREATE INDEX claramary 
ON BlobLastNameTest(FieldName)) on the whole table(which may have side effects 
as the number of rows on my table grows from 2.7 million to 20 million) while 
my query plan also tries to execute correlated subquery which exploits the  
CREATE INDEX claramary ON BlobLastNameTest(FieldName). So, why is my query just 
working accidently? Thank you.
 
 
 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Three small patches for lemon

[Olly Betts]

Attached are three small patches for lemon:

lemon-remove-duplicate-prototype.patch - remove a duplicate prototype
for SetNew().

lemon-two-typos.patch - fix a typo in a message and another in a
comment.

lemon-unused-parameter.patch - remove the errsym parameter to
resolve_conflict() which is no longer used.

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

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?‏

[Simon Slavin]

On 10 Oct 2011, at 12:37am, Frank Chang wrote:

> Simon Slavin, Here is the schema which I used. CREATE TABLE 
> [BlobLastNameTest] ([FieldName] CHAR (25), [Vertices] BLOB )

Okay.  That's not what you posted originally.  Okay so we have

CREATE TABLE BlobLastNameTest (FieldName TEXT PRIMARY KEY, Vertices BLOB)

which means that the primary key is actually rowid, as normal.  You also 
mention that you have

CREATE INDEX sqlite_autoindex_BlobLastNameTest_1 ON BlobLastNameTest (Vertices)

.  That index may be helpful elsewhere but it's not useful for the enquiry 
you're asking about.

You want to know

> obtain the minumum rowid for each unique LastName


I assume 'LastName' is what you call 'FieldName' here.  In that case, the first 
thing you want is an index on FirstName.  So do this:

CREATE INDEX BLNTFieldName ON BlobLastNameTest (FieldName, rowid)

One way to do it would be to do repeated SELECTs.  I your software you keep a 
record of the last FieldName you found.  You can start this variable off as the 
zero-length string ''.  So your first SELECT would be

SELECT rowid,FieldName FROM BlobLastNameTest WHERE FieldName>'' ORDER BY 
FieldName,rowid LIMIT 1

This will give you the entry in the table with the first FieldName.  Suppose 
this FieldName is 'Absolom'. Then your next SELECT would be

SELECT rowid,FieldName FROM BlobLastNameTest WHERE FieldName>'Absolom' ORDER BY 
FieldName,rowid LIMIT 1

And this will give you the entry with the next FieldName and the lowest rowid.  
You just keep doing this until your SELECT returns no results.

Since you have declared an INDEX which is idea for this SELECT, SQLite should 
never need to do any scanning, each SELECT should lead it straight to one 
particular row using that index.

I think you might also be able to do

SELECT rowid,FieldName FROM BlobLastNameTest GROUP BY FieldName ORDER BY rowid

You should test this to see if it works, and if it works sufficiently quickly.

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

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?‏

[Frank Chang]

Simon Slavin, Here is the schema which I used. CREATE TABLE [BlobLastNameTest] 
([FieldName] CHAR (25), [Vertices] BLOB )

With this schema it is possible to have multiple rows with the same 
FieldName. This is intentional since I am writing a Windows and Linux C++ 
multithreaded application whch uses a unique Database, Database table and 
database connection on each of 3 worker threads. I wanted to speed up the 
multithreaded application so I inserted into the table [BlobLastNameTest] 
without a index on FieldName. When the worker threads are finished inserting, I 
run create index on FieldName on each of the different tables on each of the 
database threads.  
 
CREATE INDEX claramary ON BlobLastNameTest(FieldName)
 
 I tried running my query without the claramary index but the query 
just hung. So that is why I created CREATE INDEX claramary ON 
BlobLastNameTest(FieldName) to supplement the rowid index. Thank you.   

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

Re: [sqlite] Can pre-sorted data help?

[Petite Abeille]

On Oct 9, 2011, at 10:46 PM, Black, Michael (IS) wrote:

> create virtual table ftext using fts3(t text);

Try this instead:

create virtual table ftext using fts4(t text, prefix="1")

http://www.sqlite.org/fts3.html#section_6_2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can pre-sorted data help?

[Black, Michael (IS)]

OK...here's the benchmark...First...let's build the shell (I'm using 3.7.5)
cc -o shell  -O -DHAVE_READLINE -DSQLITE_ENABLE_FTS4 
-DSQLITE_ENABLE_FTS3_PARENTHESIS sqlite3.c shell.c -lpthread -ldl -lreadline 
-lncurses

The one thing you can do with the table method is make the left-most character 
an integer which speeds things up a notch.

Let's generate some sql data
#include "sqlite3.h"
int main(int argc,char *argv[])
{
  char buf[4096];
  char sql[8192];
  int i;
  for(i=0;i<4096;++i) {
if ((i+1)%6==0) buf[i]=' ';
else buf[i]='X';
  }
  buf[4095]=0;
  for(i=0;i<100;++i) {
buf[0]='a'+(i%26);
if (argc == 1)
  sprintf(sql,"insert into text values('%s',%d);",buf,buf[0]);
else
  sprintf(sql,"insert into ftext values('%s');",buf);
printf("%s\n",sql);
  }
}

cc -o mydata mydata.c
./mydata >data1.sql
./mydata arg >data2.sql
./shell text1.db
create table text (t text,left integer);
create virtual table ftext using fts3(t text);
begin;
.read data1.sql
commit;
create index left_index on text(left);
select count(*) from text where left=97;
38462
CPU Time: user 0.005999 sys 0.002999
select count(*) from text where left=98;
38462
CPU Time: user 0.007999 sys 0.001000
select count(*) from text where left=99;
38462
CPU Time: user 0.006999 sys 0.00

create virtual table ftext using fts3(t text);
begin;
.read data2.sql
commit;
.timer on
sqlite> select count(*) from ftext where t match 'a*';
38462
CPU Time: user 0.008999 sys 0.00
select count(*) from ftext where t match 'b*';
38462
CPU Time: user 0.007998 sys 0.001000
select count(*) from ftext where t match 'c*';
38462
CPU Time: user 0.008999 sys 0.000999


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Petite Abeille [petite.abei...@gmail.com]
Sent: Sunday, October 09, 2011 10:20 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Can pre-sorted data help?



On Oct 9, 2011, at 5:07 PM, Black, Michael (IS) wrote:

> Your assumption is that it is.

Why are you assuming that I'm assuming? Is that an assumption? 8^)

In any case, looking forward for your benchmark :)

___
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] pzTail parameter to sqlite3_prepare_v2()?

[James Hartley]

On Sun, Oct 9, 2011 at 10:27 AM, Igor Tandetnik  wrote:

> James Hartley  wrote:
> > From this, I assume that *pzTail will be set to NULL after compiling >
> the stream's last statement?
>
> I suspect it'll point to the terminating NUL character. In any case, it
> should be easy to figure out experimentally.
>

My bad.  I traced my issue down to a pointer which had been dereferenced one
too many times.  Oh, the inhumanity.

Thank you Igor for your quick reply.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Statement failing

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/10/11 11:24, Tim Streater wrote:
> But closer investigation of the SQLite3 interface does not reveal an
> equivalent to the FetchAll method available under PDO.

Note that SQLite itself (the C library) doesn't have it either.  You call
prepare on SQL text and then step which will provide the next result row.
 SQLite *only* calculates the next result row on a call to step, not all
of the result rows.

Any sort of interface that is telling you in advance the number of result
rows is actually making SQLite do all the work of finding all the results.
 It is in no way more memory or CPU efficient.

If you really want a fetchall you can write itself ala this pseudocode:

  results=list()
  while result=next_result():
results.append(result)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6R6NwACgkQmOOfHg372QTjBgCgqTqnHG+8ny7Utu4+6/2hwMHg
BQ8AniRWi3LmFmh0pD8Zbvi1EZp8x6Uq
=lHWO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Statement failing

[Tim Streater]

On 09 Oct 2011 at 17:00, Simon Slavin  wrote: 

> On 9 Oct 2011, at 4:52pm, Tim Streater wrote:
>
>> At present, I'm using PDO and setting it to throw exceptions. So I have a
>> try/catch around all my $dbh->query and in there, log what happened and
>> where, report to the user and then give up. I haven't looked closely at the
>> SQLite3 interface in PHP but it wasn't obvious whether I can use the same
>> exception mechanism or not. I'll have to see how to incorporate this in my
>> app but for now I'm giving up on multiple statements in one call to the
>> interface.
>
> When you move from toy software to professional software, it becomes all about
> the error-handling.

Quite. Which is why a data-aquisition package I wrote some 20 years ago in C 
typically ran for 11 months unattended at a time (only the annual site-wide 
power-outage took it down).

But closer investigation of the SQLite3 interface does not reveal an equivalent 
to the FetchAll method available under PDO. The salient point here is that I 
need to know the number of rows in the result set before I start processing it. 
So I guess I'm sticking with PDO. :-)

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

Re: [sqlite] pzTail parameter to sqlite3_prepare_v2()?

[Igor Tandetnik]

James Hartley  wrote:
> The documentation speaks that multiple SQL statements can be passed to
> sqlite3_prepare_v2() which will only compile the first statement of the
> stream.  Upon return, pzTail points to the first character of the next SQL
> statement which has not been compiled.  From this, I assume that *pzTail
> will be set to NULL after compiling the stream's last statement?

I suspect it'll point to the terminating NUL character. In any case, it should 
be easy to figure out experimentally.

> Also when processing multiple SQL statements, does sqlite3_reset() need to
> be called before calling sqlite_prepare_v2() again using the last value of
> pzTail as the next SQL statement to compile?

No. The two sqlite_prepare_v2() calls and the two statement handles they 
produce are completely independent. It doesn't matter at all that the original 
strings from which they were compiled just happened to reside in the same 
char[] array.
-- 
Igor Tandetnik

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

[sqlite] pzTail parameter to sqlite3_prepare_v2()?

[James Hartley]

I am needing some clarification.

The documentation speaks that multiple SQL statements can be passed to
sqlite3_prepare_v2() which will only compile the first statement of the
stream.  Upon return, pzTail points to the first character of the next SQL
statement which has not been compiled.  From this, I assume that *pzTail
will be set to NULL after compiling the stream's last statement?

Also when processing multiple SQL statements, does sqlite3_reset() need to
be called before calling sqlite_prepare_v2() again using the last value of
pzTail as the next SQL statement to compile?

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

Re: [sqlite] Statement failing

[Simon Slavin]

On 9 Oct 2011, at 4:52pm, Tim Streater wrote:

> At present, I'm using PDO and setting it to throw exceptions. So I have a 
> try/catch around all my $dbh->query and in there, log what happened and 
> where, report to the user and then give up. I haven't looked closely at the 
> SQLite3 interface in PHP but it wasn't obvious whether I can use the same 
> exception mechanism or not. I'll have to see how to incorporate this in my 
> app but for now I'm giving up on multiple statements in one call to the 
> interface.

When you move from toy software to professional software, it becomes all about 
the error-handling.

I like the try/catch interface that I'm using with the SQLite3 API, but I'm not 
married to it, and other styles of programming will benefit more from other 
styles of error-trapping.

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

Re: [sqlite] Statement failing

[Tim Streater]

On 09 Oct 2011 at 02:02, Simon Slavin  wrote: 

> On 9 Oct 2011, at 1:39am, Tim Streater wrote:
>
>> On 08 Oct 2011 at 23:32, Simon Slavin  wrote:
>>
>>> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.
>>
>> By the way, is one to be preferred over the other?
>
> That is a great question, and I'd love to see answers from PHP programmers.
>
> My contribution: the thing the PDO API has going for it is that it makes
> changing from one SQL engine to another simple.  If you develop on a
> stand-alone computer for a big multi-server network, this is an advantage.

That won't apply in my case.

> On the other hand, the biggest advantage of using the SQLite3 API is that it's
> such a thin wrapper around the SQLite C API.  For someone who already knows
> SQLite it's very easy to pick up.  But that's only an advantage for
> experienced SQLite users, or those who want to be.

Having it be a thin wrapper suits me.

> I have had trouble using the PDO API in situations that demand proper
> error-handling.  If you have a situation where something either works or
> doesn't you're fine.  If you have to understand exactly what error you got, in
> order to handle several different situations, you pretty-much have to simulate
> all your error conditions and see what happens to write your program.  The
> advantage of the SQLite3 interface here is that it perfectly reflects the
> documentation for the SQLite3 C API, so you can probably figure out what to do
> just by reading the SQLite C documentation.

At present, I'm using PDO and setting it to throw exceptions. So I have a 
try/catch around all my $dbh->query and in there, log what happened and where, 
report to the user and then give up. I haven't looked closely at the SQLite3 
interface in PHP but it wasn't obvious whether I can use the same exception 
mechanism or not. I'll have to see how to incorporate this in my app but for 
now I'm giving up on multiple statements in one call to the interface.

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

Re: [sqlite] Can pre-sorted data help?

[Simon Slavin]

On 9 Oct 2011, at 3:49pm, Mohit Sindhwani wrote:

> Maybe, I'm worrying about the wrong things :)

I think perhaps you are.  First, you are trying to reproduce SQLite's indexing 
mechanism with one that isn't even as good.  Secondly you are worrying about 
indexing columns, instead of creating indexes suitable for the SELECTs and 
UPDATEs you do.

So first worry about getting your data in tables.  Work out your rows and 
columns.
... so far, no wasted space

Then worry about what SELECTs and UPDATEs you want to do.
... so far, still no wasted space

>From that, worry about what indexes to create.

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

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

[Simon Slavin]

On 9 Oct 2011, at 4:13pm, Frank Chang wrote:

> CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, 
> [Vertices] BLOB )

This form

CREATE TABLE BlobLastNameTest (FieldName TEXT PRIMARY KEY, Vertices BLOB)

does exactly the same in SQLite.

> index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3|

Is this the same as

CREATE INDEX sqlite_autoindex_BlobLastNameTest_1 ON BlobLastNameTest (Vertices)

?  Now sure where the '3' comes from.

> This table could potentially hold 10 to 40 million rows. We are using the 
> following query to obtain the minumum rowid for each unique LastName:
> 
> sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest 
> t1
> GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM 
> BlobLastNameTes
> t where FieldName = t1.FIELDNAME);

As you guessed, this is hideously inefficient.

But there is a problem.  You have defined your FieldName column as a primary 
key.  This means there cannot be any two rows with the same FieldName.  This 
makes your query pointless.

Do you actually want to have multiple rows with the same FieldName ?  If so let 
SQLite use rowid as the PRIMARY KEY (which is what it does if you don't define 
one yourself).  If not, please clarify your search.

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

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

[Florian Weimer]

* Frank Chang:

> This table could potentially hold 10 to 40 million rows. We are
> using the following query to obtain the minumum rowid for each
> unique LastName:
>  
> sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest 
> t1
>  GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM 
> BlobLastNameTes
> t where FieldName = t1.FIELDNAME);

You could try

  SELECT FieldName, rowid FROM BlobLastNameTest ORDER BY FieldName, rowid;

and perform the aggregation in the application.  Perhaps this is faster.

A better query needs support for DISTINCT ON, which SQLite lacks
AFAIK.  But speed will obviously be limited because you cannot avoid
traversing the index for the whole table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

[Igor Tandetnik]

Frank Chang  wrote:
>  Hi, We are using the following schema :
> CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, 
> [Vertices] BLOB )
> 
> index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3|
> 
> 
> This table could potentially hold 10 to 40 million rows. We are using the 
> following query to obtain the minumum rowid for each
> unique LastName: 
> 
> sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest 
> t1
> GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM 
> BlobLastNameTes
> t where FieldName = t1.FIELDNAME);

This query doesn't do what you seem to think it does. If it works, it's only by 
accident. You probably want something as simple as

select FieldName, min(rowid) from BlobLastNameTest group by FieldName;

-- 
Igor Tandetnik

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

Re: [sqlite] Can pre-sorted data help?

[Petite Abeille]

On Oct 9, 2011, at 5:07 PM, Black, Michael (IS) wrote:

> Your assumption is that it is.

Why are you assuming that I'm assuming? Is that an assumption? 8^)

In any case, looking forward for your benchmark :)

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

Re: [sqlite] new user

[Taleeb Anwar]

No, not me! At least not at this stage -- will go through various options
when (and if) I decide to develop something (right now am just learning for
the sake of knowledge). Anyway thanks for the info!

Thanks & Regards
Taleeb Anwar

*Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*




On Sun, Oct 9, 2011 at 8:40 PM, Tim Streater  wrote:

> On 09 Oct 2011 at 10:40, saeed ahmed  wrote:
>
> > i am a new to sqlite. i want to know how can i make sqlite looking like
> > microsoft access? similar working environment, like making tables,
> queries
> > etc. actually i find it difficult to work in writing commands mode. any
> help?
>
> Have you looked at Navicat for SQLite?
>
> --
> Cheers  --  Tim
>
> ___
> 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

[sqlite] Is is possible to optimize this query on a very large datatbase table?

[Frank Chang]

  Hi, We are using the following schema : 
CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, [Vertices] 
BLOB )

index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3|
 
 
This table could potentially hold 10 to 40 million rows. We are using the 
following query to obtain the minumum rowid for each unique LastName:
 
sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1
 GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTes
t where FieldName = t1.FIELDNAME);
 
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX 
sqlite_autoindex_BlobLastNameTest_1 (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX 
sqlite_autoindex_BlobLastNameTest_1 (FieldName=?) (~1 rows)
 
This query runs in less than 5 seconds on the lastest version of sqlite with 
2,700,000 rows. But what if we have 10 million rows to 40 million rows. Would 
it possible to optimize this query further? Thank you.  
   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] new user

[Tim Streater]

On 09 Oct 2011 at 10:40, saeed ahmed  wrote: 

> i am a new to sqlite. i want to know how can i make sqlite looking like
> microsoft access? similar working environment, like making tables, queries
> etc. actually i find it difficult to work in writing commands mode. any help?

Have you looked at Navicat for SQLite?

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

Re: [sqlite] Can pre-sorted data help?

[Black, Michael (IS)]

If I had the time right now I'd write a benchmark

I find it really hard to believe that for prefix queries that FTS is going to 
be faster than a seperate table.
Your assumption is that it is.  Especially for the first character situation.  
And it's selectivity is one-to-one for the query he posed. A=A, B=B, etc.

Has anybody bothered to benchmark this?

The table I described takes up 1 byte per record for the text...so you can fit 
more in cache and memory.

FTS is mainly for searching the entire recordI think that's where it 
shinesbeing able to prefix query is just a natural feature...not 
necessarily the most optimal way of doing it.

And sorting data can help speed as it reduces fragmentation of the data that 
has to be retrieved so caching can work better.


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Petite Abeille [petite.abei...@gmail.com]
Sent: Sunday, October 09, 2011 9:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Can pre-sorted data help?



On Oct 9, 2011, at 1:03 PM, Black, Michael (IS) wrote:

> For your example create a separate table with just the first letter and build 
> an index on it.

This is most likely pointless as the selectivity of such index is going to be 
very low.

Plus not point in reinventing a square wheel, instead let FTS handle this :)

FTS4 supports prefix queries:

http://www.sqlite.org/fts3.html#section_6_2

___
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] Can pre-sorted data help?

[Mohit Sindhwani]

Thanks Petite!

On 9/10/2011 10:58 PM, Petite Abeille wrote:

On Oct 9, 2011, at 4:49 PM, Mohit Sindhwani wrote:


Maybe, if we had a column called 'published_date' and we did a query for data 
within a date range.. the fastest way to get the information back would be to 
have an index on that column.  Suppose we sorted all the data by date - would 
there be a way to use that information so that we don't have to create the 
index?

Not really. Alternatively, you could partition your data into different tables 
and then simulate partition pruning at query time. This is not supported 
directly by SQLite, but is straightforward enough to simulate.

http://www.orafaq.com/tuningguide/partition%20prune.html


I'll take a look.


Maybe, I'm worrying about the wrong things :)

Maybe.


Thanks for indulging me, nonetheless :)

Best Regards,
Mohit.
9/10/2011 | 11:04 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] new user

[Taleeb Anwar]

No I'm not from Pakistan; but that should not be a reason for not helping
you out...And no I'm not an expert (not even near one).

OK. Let me try to explain again. To use sqlite like we all use
access/sql-server, we need to have some tool (management studio or IDE or
whatever you call it). If you have no idea and are just a beginner (like me)
then go ahead and download sqlite expert (there are other options - but I'm
using it and it is good enough to at least learn the concepts) from
http://www.sqliteexpert.com/download.html. You can download the personal
edition.

Install and run it and you will find a friendly interface (and sqlite will
look worth using). Now you just need to "click" to create tables and add
columns, indexes etc.

Now comes the next question, why do you want to use sqlite. Do you want to
use it with java, .net, C++ or just the database, or what..?
Taleeb bin Waquar

*Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*




On Sun, Oct 9, 2011 at 7:45 PM, saeed ahmed wrote:

> brother it seems you are from pakistan like me.help me if you are wxpert
> user.i want to use sqlite but i dont know where to start?
>
> 2011/10/9 Taleeb Anwar 
>
> > If you don't want to work using command line - then you will have to
> > download some management tools. Visit
> > http://www.sqlite.org/cvstrac/wiki?p=ManagementTools to see a list of
> > tools.
> >
> > Personally I'm using sqlite expert. It has got its own advantages and
> > limitations (but anyway I've not used sqlite much).
> >
> > Hope this helps.
> >
> > Thanks & Regards
> > Taleeb (bin Waquar) Anwar
> >
> > *Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*
> >
> >
> >
> >
> > On Sun, Oct 9, 2011 at 3:10 PM, saeed ahmed  > >wrote:
> >
> > > i am a new to sqlite.i want to know how can i make sqlite looking like
> > > microsoft access?simmilar working environment,likemaking tables,queries
> > > etc.actually i find it difficult to work in writing commands mode.any
> > help?
> > > ___
> > > 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
> >
> ___
> 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] Can pre-sorted data help?

[Petite Abeille]

On Oct 9, 2011, at 4:49 PM, Mohit Sindhwani wrote:

> Maybe, if we had a column called 'published_date' and we did a query for data 
> within a date range.. the fastest way to get the information back would be to 
> have an index on that column.  Suppose we sorted all the data by date - would 
> there be a way to use that information so that we don't have to create the 
> index?

Not really. Alternatively, you could partition your data into different tables 
and then simulate partition pruning at query time. This is not supported 
directly by SQLite, but is straightforward enough to simulate.

http://www.orafaq.com/tuningguide/partition%20prune.html

> Maybe, I'm worrying about the wrong things :)

Maybe.

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

Re: [sqlite] new user

[Taleeb Anwar]

When I said advantages and limitations -- I meant philosophically and not
literally. In today's technology there is always scope for improvement,
irrespective of how great the product is.

I must also "repeat" that I've not used sqlite much. Just read about it
while browsing about .NET windows forms and decided to see and use it (and
thus am using the personal edition of "expert").

Hope this clarifies my stand...:-)
Taleeb Anwar

*Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*




On Sun, Oct 9, 2011 at 6:47 PM, Jean-Christophe Deschamps
wrote:

> Hi Taleeb,
>
> Just curious, what are the limitations in SQLite Expert you've found?
>
> I'm a long-term user of Expert and Bogdan (Expert's author) become close to
> a friend to me.  I'd like to see his product even more useful as I found it
> fairly complete and, above all, Bogdan is very responsive to
> issues/questions/request from users.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Hi Michael and Petite,

Thanks for the replies.


On 9/10/2011 10:08 PM, Petite Abeille wrote:

On Oct 9, 2011, at 1:03 PM, Black, Michael (IS) wrote:


For your example create a separate table with just the first letter and build 
an index on it.

This is most likely pointless as the selectivity of such index is going to be 
very low.


I agree about FTS... we use that too... I'm actually just looking to see 
how we can reduce the indexes that we have.  In a discussion someone 
mentioned 'would it help if we sorted all our data before inserting into 
a table?'  (given that we have a read-only database) and we got the 
feeling that we would probably need to create an index anyway - there is 
probably no way to tell SQLite something like 'the data in this table is 
sorted by the field that we are querying'..


Maybe, if we had a column called 'published_date' and we did a query for 
data within a date range.. the fastest way to get the information back 
would be to have an index on that column.  Suppose we sorted all the 
data by date - would there be a way to use that information so that we 
don't have to create the index?


Maybe, I'm worrying about the wrong things :)

Best Regards,
Mohit.
9/10/2011 | 10:49 PM.


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

Re: [sqlite] Can pre-sorted data help?

[Petite Abeille]

On Oct 9, 2011, at 12:15 PM, Mohit Sindhwani wrote:

> create table titles (id integer primary key, title text, ...);
> could we sort the records by title and use that in some way to restrict the 
> search space when searching titles starting with a specific letter?

You might be better off moving your 'title' column to a FTS virtual table. 

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

Re: [sqlite] new user

[saeed ahmed]

brother it seems you are from pakistan like me.help me if you are wxpert
user.i want to use sqlite but i dont know where to start?

2011/10/9 Taleeb Anwar 

> If you don't want to work using command line - then you will have to
> download some management tools. Visit
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools to see a list of
> tools.
>
> Personally I'm using sqlite expert. It has got its own advantages and
> limitations (but anyway I've not used sqlite much).
>
> Hope this helps.
>
> Thanks & Regards
> Taleeb (bin Waquar) Anwar
>
> *Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*
>
>
>
>
> On Sun, Oct 9, 2011 at 3:10 PM, saeed ahmed  >wrote:
>
> > i am a new to sqlite.i want to know how can i make sqlite looking like
> > microsoft access?simmilar working environment,likemaking tables,queries
> > etc.actually i find it difficult to work in writing commands mode.any
> help?
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can pre-sorted data help?

[Petite Abeille]

On Oct 9, 2011, at 1:03 PM, Black, Michael (IS) wrote:

> For your example create a separate table with just the first letter and build 
> an index on it.

This is most likely pointless as the selectivity of such index is going to be 
very low.

Plus not point in reinventing a square wheel, instead let FTS handle this :)

FTS4 supports prefix queries:

http://www.sqlite.org/fts3.html#section_6_2

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

Re: [sqlite] new user

[Taleeb Anwar]

If you don't want to work using command line - then you will have to
download some management tools. Visit
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools to see a list of tools.

Personally I'm using sqlite expert. It has got its own advantages and
limitations (but anyway I've not used sqlite much).

Hope this helps.

Thanks & Regards
Taleeb (bin Waquar) Anwar

*Ghurbat men Hon Agar Hum, Rehta Hai Dil "Chicken" men!!*




On Sun, Oct 9, 2011 at 3:10 PM, saeed ahmed wrote:

> i am a new to sqlite.i want to know how can i make sqlite looking like
> microsoft access?simmilar working environment,likemaking tables,queries
> etc.actually i find it difficult to work in writing commands mode.any help?
> ___
> 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] Can pre-sorted data help?

[Black, Michael (IS)]

For your example create a separate table with just the first letter and build 
an index on it.

create table text(t string);
create table first(textid int, first_char char);
create trigger makefirst after insert on text
begin
insert into first values(new.rowid,substr(new.t,1,1));
end;
insert into text('a_one');
insert into text('b_two');
explain query plan select * from text where rowid in (select textid from first 
where first_char='a');
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE text USING INTEGER PRIMARY KEY 
(rowid=?) (~25 rows)
0 0  0 EXECUTE LIST SUBQUERY 1
1 0  0 SEARCH TABLE first USING INDEX first_index (l=?) 
(~10 rows)
select * from text where rowid in (select textid from first where 
first_char='a');
t

a_one
select * from text where rowid in (select textid from first where 
first_char='b');
t

b_two

Note that this doesn't have a complete trigger package for updates and deletes 
but you are describing a static set where you don't need them.

Without this you would end up doing this:
sqlite> create index textindex on text(t);   this is actually useless but 
we'll show it anyways just to prove it doesn't work
sqlite> explain query plan select * from text where substr(t,1,1)='a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE text (~50 rows)
sqlite> explain query plan select * from text where t like 'a%';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE text (~50 rows)


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Mohit Sindhwani [m...@onghu.com]
Sent: Sunday, October 09, 2011 5:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Can pre-sorted data help?


Thanks Richard and Petite,



On 9/10/2011 1:05 AM, Richard Hipp wrote:
> On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani  wrote:
>
>> Hi All,
>>
>> I have been trying to see how we can make one of our databases more space
>> efficient.
>
> Have you tried increasing the page size?  You seem to currently be using
> 1024-byte pages.  Try increasing that to 4096 or 8192.  You might get both a
> performance increase and a database size reduction.
>
>  PRAGMA page_size=8192; VACUUM;
>
> The VACUUM might take a little while on your database,.


Richard: The page size is indeed changed to 8KB in one of the final
steps before we pack it up with CEROD - and it does help with the
database size!

Petite: thanks for pointing that out - we'll drop the index on the
primary key!

Both these suggestions will help.

I'm still trying to see if we make use of the fact that some data can be
sorted... I can't find the best example to illustrate the need for it..
Let me see if I can find something that explains it better.  As an
example, if we had something like
create table titles (id integer primary key, title text, ...);
could we sort the records by title and use that in some way to restrict
the search space when searching titles starting with a specific letter?

Thanks,
Mohit.
9/10/2011 | 6:13 PM.




___
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] Error 14 - cannot open Database

[Ian Hardingham]

Thanks for the reply Stephan.

It transpires that the problem was not enough free hard drive space to 
create the journal file.  Would be good if this was reported more verbosely.


Thanks again,
Ian

On 09/10/2011 11:09, Stephan Beal wrote:

On Sun, Oct 9, 2011 at 11:22 AM, Ian Hardingham  wrote:


Woke up this morning to find my server unable to open our database file.
  Is there anything I can do to diagnose or repair it?  We have backups but
it would be good if it were possible to repair this one.


Some things to try:

Can you open the file with the command-line sqlite3 client? Is the file
still there? Are the file permissions still correct (readable by your
server's user id)?



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

Re: [sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Thanks Richard and Petite,



On 9/10/2011 1:05 AM, Richard Hipp wrote:

On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani  wrote:


Hi All,

I have been trying to see how we can make one of our databases more space
efficient.


Have you tried increasing the page size?  You seem to currently be using
1024-byte pages.  Try increasing that to 4096 or 8192.  You might get both a
performance increase and a database size reduction.

 PRAGMA page_size=8192; VACUUM;

The VACUUM might take a little while on your database,.



Richard: The page size is indeed changed to 8KB in one of the final 
steps before we pack it up with CEROD - and it does help with the 
database size!


Petite: thanks for pointing that out - we'll drop the index on the 
primary key!


Both these suggestions will help.

I'm still trying to see if we make use of the fact that some data can be 
sorted... I can't find the best example to illustrate the need for it..  
Let me see if I can find something that explains it better.  As an 
example, if we had something like

create table titles (id integer primary key, title text, ...);
could we sort the records by title and use that in some way to restrict 
the search space when searching titles starting with a specific letter?


Thanks,
Mohit.
9/10/2011 | 6:13 PM.




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

Re: [sqlite] Error 14 - cannot open Database

[Stephan Beal]

On Sun, Oct 9, 2011 at 11:22 AM, Ian Hardingham  wrote:

> Woke up this morning to find my server unable to open our database file.
>  Is there anything I can do to diagnose or repair it?  We have backups but
> it would be good if it were possible to repair this one.
>

Some things to try:

Can you open the file with the command-line sqlite3 client? Is the file
still there? Are the file permissions still correct (readable by your
server's user id)?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Statement failing

[Stephan Beal]

On Sun, Oct 9, 2011 at 3:02 AM, Simon Slavin  wrote:

> That is a great question, and I'd love to see answers from PHP programmers.
>

Personally, i prefer PDO. i like it's simplified model (unification of query
and result set) and find it easy to work with. i like it so much that i
wrote a C library to mimic it:

http://whiki.wanderinghorse.net/wikis/cpdo/

I have had trouble using the PDO API in situations that demand proper
> error-handling.  If you have a situation where something either works or
> doesn't you're fine.  If you have to understand exactly what error you got,
> in order to handle several different situations, you pretty-much have to
> simulate all your error conditions and see what happens to write your
> program.


True enough, but most PHP apps, i would guess, don't reach anywhere near
that level of complexity. i've never personally had a situation where i
needed to know exactly what broken - a generic PDO exception has always
sufficed for me.



>  The advantage of the SQLite3 interface here is that it perfectly reflects
> the documentation for the SQLite3 C API, so you can probably figure out what
> to do just by reading the SQLite C documentation.
>

Amen.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] new user

[saeed ahmed]

i am a new to sqlite.i want to know how can i make sqlite looking like
microsoft access?simmilar working environment,likemaking tables,queries
etc.actually i find it difficult to work in writing commands mode.any help?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error 14 - cannot open Database

[Ian Hardingham]

Hey guys.

Woke up this morning to find my server unable to open our database 
file.  Is there anything I can do to diagnose or repair it?  We have 
backups but it would be good if it were possible to repair this one.


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