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

[Wamiduku]

On Oct 11, 1:59 am, "Joe Mistachkin"  wrote:
> Wamiduku wrote:
>
> I suppose the setup could uninstall the VC++
> runtime; however, it's typically considered "bad form" to do so (since
> it's officially a "shared" component).

I agree that the uninstall had better leave it there.

> > One solution to this dependency problem would be to add the VC redist
> > dlls to any portable SQLite app you make, but the redist is 19 dlls
> > and 11MB, so that's not very neat.
>
> I agree, several people have expressed concern about this issue both on
> this mailing list and on the project ticket tracker.

Not a problem though, since you can use the static SQLite DLL, which I
didn't know existed.

> > Rebuilding Sys.Data.SQLite with statically linked C++ libs, so that it
> > becomes standalone, would be a better solution. What would I need to
> > change in the VS2010 project in order to do that?
>
> Nothing.  There are already "Static" projects in the solutions.  Simply
> [also] build the projects named "SQLite.Interop.Static." in one of
> the solutions.
>
> The resulting binaries will be present in the:
>
>         \bin\\\Static

Holy hexadecimals, I feel like a moron for wasting all that time with
virtual machines and looking through Thinapp logs to track changes,
when the statically linked Sys.Data.SQLite DLL was just a rebuild
away! Gotta laugh at that ;-).

There's a case that's probably rare, but could be good to be aware of
anyway; Since the name and version is the same for both the static and
non static Sys.Data.SQLite DLL, if there's a Sys.Data.SQLite in the
GAC, .NET will load it and ignore any Sys.Data.SQLite in the app
directory.

So, if any sloppily written app installs a non static Sys.Data.SQLite
in the GAC, but doesn't install the VC runtime, any app with a static
SQLite in the app directory will stop working.

Maybe making the static Sys.Data.SQLite the default version could be
an idea to consider? Not being dependent on a previous VC runtime
installation would emphasize the self-contained and configuration-less
nature of SQLite.

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

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

[Joe Mistachkin]

Wamiduku wrote:
> 
> I found it! By using the trial version of VMWare Thinapp and installing
> +uninstalling Data.SQLite, you can see what the uninstallation left
> behind, and it is the Visual C++ 2010 Redistributable Package.
> 

Yeah, that would do it.  I suppose the setup could uninstall the VC++
runtime; however, it's typically considered "bad form" to do so (since
it's officially a "shared" component).

> 
> One solution to this dependency problem would be to add the VC redist
> dlls to any portable SQLite app you make, but the redist is 19 dlls
> and 11MB, so that's not very neat.
> 

I agree, several people have expressed concern about this issue both on
this mailing list and on the project ticket tracker.

> 
> Rebuilding Sys.Data.SQLite with statically linked C++ libs, so that it
> becomes standalone, would be a better solution. What would I need to
> change in the VS2010 project in order to do that?
> 

Nothing.  There are already "Static" projects in the solutions.  Simply
[also] build the projects named "SQLite.Interop.Static." in one of
the solutions.

The resulting binaries will be present in the:

\bin\\\Static

directory, where year is 2008 or 2010, platform is Win32 or x64, and
config is Release or Debug.

--
Joe Mistachkin

___
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 database table Oct. 10, 2011 13:53:01 GMT

[Frank Chang]

 Igor Tandetnik,
 
>>> Explain the problem you are trying to solve, *not* your proposed solution. 
>>> <<<

 
  What we are trying to achieve is to to find the minimum row id for each 
unique Field Name in BLobLastNameTest where many rows can have the same 
FIELDNAME but distinct BLOBS(Vertices Column).
 
   Once we know  the first/minimum row id  of each unique fieldname group, 
we would like to write a sqlite UPDATE Statement to accumulate all the BLOB 
vertices of all the rows with that unique fieldname into the first(i.e 
MIN(ROWID))  row's BLOB(Vertices)column  for  each unique fieldname group.
 
The reason we can't use select FieldName, rowid from BlobLastNameTest is 
that it would slow our C++ Windows/LINUx/Solaris UNIX worker threads so much 
that it wouldn;t be worth multithreading this whole process. We were hoping 
that the SQLITE query processor and the appropriate indexes could accomplish 
these previous 2 steps in a few minutes for a reasonable number of 
BLOBLASTNAMETEST rows. Thank you for all of your help. 
  
___
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?

[yary]

> 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?

If you can make the column fit in a 64-bit int and make it unique, you
can use it to create a fast index. The trick is to make it into the
table's rowid- then the index is the structure of the table, and
incurs less overhead than a regular index lookup.

Turning a non-unique date into a unique rowid is not trivial but it
isn't impossible. Store the published date in whatever numeric format
is convenient- MMDD, Julian date, seconds since the epoch, etc...
let's pick MMDD as an example. That fits in 8 digits. A 64 bit int
is 19+ digits, so if you put the date in the leftmost 8 then the other
11 can be a serial number to make a unique key.

Then create your table with an "integer primary key", eg

CREATE TABLE t(published_date_serial INTEGER PRIMARY KEY ASC, y, z);

After populating it, to select all records published between Aug 10
2010 and Sep 3 2010:

select
  published_date_serial/'1000 as published_date, y, z
from t
where published_date_serial between 2010081 and 20100903999

That is a bit of a digression.

The original question mirrors one I have- if I am creating a large
read-only database, and can control over the data I'm importing, will
the data import faster if it is in the order of the integer primary
key? I'll have to benchmark that.
___
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 database table.

[Igor Tandetnik]

Frank Chang  wrote:
> Igor Tandetnik,
>  The fieldname groups in our BlobLastNameTable consist of 
> multiple rows where each pair of columns [FieldName,
>   BLOB[Vertices]] is unique.

How so? You have FieldName declared as PRIMARY KEY. From your original post:

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

PRIMARY KEY means there can only be one row with any given value of FieldName.

If you have since changed your schema, then post the new CREATE TABLE statement 
for BlobLastNameTest table, and any CREATE INDEX statements related to it.

> Therefore, every fieldname group does not just have a single row but instead 
> 1000's or
>  1's rows. So that is why we use a group by/order by and 
> subselect clause to locate the first/minimum row id  row
> in each fieldname group.

Assuming this is true, what's wrong with

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

> Once we know  the first/minimum row id  of each unique fieldname group, we 
> would lke to write a
> sqlite UPDATE Statement to accumulate all the BLOB vertices of all the rows 
> with that unique fieldname into the first(i.e
> MIN(ROWID))  row's BLOB(Vertices)column  for  each unique fieldname group.

What do you mean by "accumulate"? Concatenate? I don't think you can do that 
with SQL alone - you'll have to write some code. Personally, I'd do something 
like this (in pseudocode):

stmt = prepare("select FieldName, rowid, Vertices from BlobLastNameTest order 
by FieldName, rowid")
currentFieldName = "";
firstRowId = -1;
blob = ""
while (stmt.Step) {
  if (currentFieldName != stmt.FieldName) {
commitBlob(firstRowId, blob)
currentFieldName = stmt.FieldName
firstRowId = stmt.rowid
blob = ""
  }
  blob += stmt.Vertices  // whatever you mean by "accumulate", do it here
}
commitBlob(firstRowId, blob)

function commitBlob(rowid, blob) {
  if (rowid > 0) {
execute "update BlobLastNameTest set Vertices = ? where rowid = ?;"
with parameters (blob, rowid)
  }
}

> Then we would like to  discard all the rows  in each
> fieldname group of rows that have an rowid different from the first row

That one's easy:

delete from BlobLastNameTest where rowid !=
(select min(rowid) from BlobLastNameTest t2
 where t2.FieldName = BlobLastNameTest.FieldName);

-- 
Igor Tandetnik

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

[sqlite] Re; Is it possible to optimize this query on a very large database table? Oct 10,2011

[Frank Chang]

 Simon Slavin,  I tried your suggestion about creating the index: 
 
>So do this:
>CREATE INDEX BLNTFieldName ON BlobLastNameTest (FieldName, rowid)

But sqlite complains that  rowid is not a BLobLastNameTest column.
 
 
   So then I tried your repeated selects. Your selects work fine but since we 
are using Multiple concurrent Windows and Linux/pthread worker threads, these 
repeated select resulrts would have to be processed in C++ code which would 
overload each of the cores on the multicore CPU we are using. Instead, we were 
hoping they we write a nice query to let the SQLite query processor do the 
aggregation of BLOB(vertices) without incurring the full  index scan cost. 
Florian Weimer told us about this Posttgres feature DISTINCT ON which would 
allow us to do this. However, SQLITE does not have DISTINCT ON yet, so the 
Postgres thread recommended we use somerthing like to simulate DISTINCT ON: 
select t1.FieldName,t1.rowid from BlobLastNameTest t1
where t1.rowid = (SELECT MIN(rowid) FROM 
BlobLastNameTest 
   where FieldName = 
t1.FIELDNAME) 
order by t1.FieldName;
where  the query plan looks like this:
 
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX 
claramary5(~2709793 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary5 (FieldN
) (~1 rows)
 
Please let us know if there is a faster or more elegant way to this.
 
 
Finally, I tried your suggestion about : SELECT rowid,FieldName FROM 
BlobLastNameTest GROUP BY FieldName ORDER BY rowid but it gives the wrong 
answer where the right answer is the minimum rowid for a particular 
FIELDNAME(i.e.LASTNAME). Your query runs very fast . Is it  possible that you 
could tweak it a litle so that it gives the right minimum ROWiD answer. Thank 
you for all of your help.


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

Re: [sqlite] new user

[Sean Pieper]

I have not used it, but kexi claims to offer many of the features of access:
http://www.kexi-project.org/screenshots.html

someone put together a really nice wiki of various admin tools for sqlite here 
with descriptions of features, licensing, and fees.
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

-sean

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Tim Streater
Sent: Sunday, October 09, 2011 8:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] new user

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

---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Wamiduku]

On Oct 10, 4:01 am, "Joe Mistachkin"  wrote:
> Wamiduku wrote:
> > 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).

I found it! By using the trial version of VMWare Thinapp and installing
+uninstalling Data.SQLite, you can see what the uninstallation left
behind, and it is the Visual C++ 2010 Redistributable Package.

I confirmed this by running textlinq.exe from the unzipped sqlite-
netFx40-
binary-bundle-Win32-2010-1.0.76.0 directory on a clean machine again,
which didn't work, and then installing VC 2010 redist (from
http://www.microsoft.com/download/en/details.aspx?id=), after
which testlinq.exe worked.

One solution to this dependency problem would be to add the VC redist
dlls to any portable SQLite app you make, but the redist is 19 dlls
and 11MB, so that's not very neat.

Rebuilding Sys.Data.SQLite with statically linked C++ libs, so that it
becomes standalone, would be a better solution. What would I need to
change in the VS2010 project in order to do that?

-John
___
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 database table.

[Frank Chang]

Igor Tandetnik, 
  The fieldname groups in our BlobLastNameTable consist of 
multiple rows where each pair of columns [FieldName, BLOB[Vertices]] is unique. 
Therefore, every fieldname group does not just have a single row but instead 
1000's or 1's rows. So that is why we use a group by/order by and subselect 
clause to locate the first/minimum row id  row in each fieldname group. 
   Once we know  the first/minimum row id  of each unique fieldname 
group, we would lke to write a sqlite UPDATE Statement to accumulate all the 
BLOB vertices of all the rows with that unique fieldname into the first(i.e 
MIN(ROWID))  row's BLOB(Vertices)column  for  each unique fieldname group. Then 
we would like to  discard all the rows  in each fieldname group of rows that 
have an rowid different from the first row(i.e MIN(rowid)).
  Because we using a C++ WINDOWS/LINUX/Solaris UNIX multithreaded 
program where each concurrent worker thread has its own sqlite database and 
sqlite table and sqlite index , we would like each concurrence worker thread to 
run as fast as possible on a multicore CPU. We have profiled the worker threads 
and we have found that the sqlite statements are the bottleneck. So, that is 
why we would like our queries to run as fast as possible by avoiding full index 
scans as Florian Weimer pointed out in the post about Postgres' DISTINCT ON 
feature which we are trying to simulate on Sqlite. 
 I hope I have provided you more information. I did try your 
suggestion:   select FieldName, rowid from BlobLastNameTest. However, it 
generates 5.5 million rows of output which would force our C++ Worker threads 
to do the BLOB(Vertices) aggregation. Unfortunately , that would would take too 
long so we were hoping that we could devise a nice query to let the SQLite 
query processor do all the Blob(Vertices) aggregation in a few minutes or less 
. Please let me know if you have any questions or suggestions. Thank you for 
your help.  
-- 
  
 
 
--
I take it back - the behavior of your query is well-defined, for the simple 
reason that FieldName is unique,
so every group only has a single row in it (as someone else has kindly pointed 
out - I missed this detail on the
first reading). For that same reason, the whole GROUP BY and sub-select dance 
is completely pointless.
Your query is just a very elaborate and wasteful way to write

select FieldName, rowid from BlobLastNameTest; 


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

Re: [sqlite] How to design this table?

[Ivan Shmakov]

> Simon Slavin writes:
> On 9 Oct 2011, at 3:57am, 张一帆 wrote:

 >> i have some data like "a and b or c ...",there will be a word 'and'
 >> or 'or' which means the Logical relations between each item.

 > If you have "a and b or c" does that mean

 > (a and b) or cOR
 > a and (b or c)?

 > How does your software know ?

The boolean AND and OR operations are often compared to the
usual arithmetics' × and +.  Therefore, a AND b OR c is akin to
a × b + c, and the precedence rules (borrowed from arithmetics)
will make that (a × b) + c.

And SQLite, among many others, behaves just like that:

$ sqlite3 :memory: 'SELECT 0 AND  1 OR 1' 
1
$ sqlite3 :memory: 'SELECT 0 AND (1 OR 1)' 
0
$ 

I guess that it just follows the standard.

-- 
FSF associate member #7257

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

Re: [sqlite] Multiple statements

[Simon Slavin]

On 10 Oct 2011, at 4:25pm, Tim Streater wrote:

> Looking at the description text for sqlite3_exec (SQLite C interface), I see 
> this text:
> 
> "The sqlite3_exec() interface runs zero or more UTF-8 encoded, 
> semicolon-separate SQL statements passed into its 2nd argument, in the 
> context of the database connection passed in as its 1st argument."
> 
> Now, Simon said that the PHP SQLite3 interface (which I would like to use), 
> is a thin wrapper around the SQLite C interface.

I did say that.  However, in another post I commented that the documentation 
for the PHP SQLite3:: interface explicitly says that it executes one statement. 
 And here it is:

 Can I conclude, then, that such multiple statements would be properly 
> executed if presented via the PHP interface? What would happen if such a 
> string contained more than one SELECT - or if the SELECT is not the first 
> statement?


Don't know.  What happened when you tried it ?
Then you'll know what happens with your installation of your versions of those 
pieces of software.

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

Re: [sqlite] Multiple statements

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/11 08:25, Tim Streater wrote:
> Looking at the description text for sqlite3_exec (SQLite C interface),
> I see this text:

That interface is generally not used.  I believe it dates from the days of
SQLite 2.  It provides all values as strings, and gives all the results in
one go hence consuming a lot of memory.

The interface that is used is:

- - sqlite3_prepare() to get a stmt for the first SQL statement
- - sqlite3_step() to process up to the next row or end of results for that stmt
- - sqlite3_value_int/blob etc to get each column of a result row
- - sqlite3_finalize when done with the stmt

If there was more than one SQL statement then the developer needs to go
back to the prepare step passing in the next statement.  This is what some
developers leave out.

If you look in the SQLite source you'll find that sqlite3_exec is pretty
much a wrapper around the above.

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

iEYEARECAAYFAk6TEEcACgkQmOOfHg372QQ7NQCfVxKnkCPkbenBaxNdtHbcHS70
CssAoJsn1nsc05SXokHukAVgEnPBI3Ic
=PHRl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple statements

[Tim Streater]

Looking at the description text for sqlite3_exec (SQLite C interface), I see 
this text:

"The sqlite3_exec() interface runs zero or more UTF-8 encoded, 
semicolon-separate SQL statements passed into its 2nd argument, in the context 
of the database connection passed in as its 1st argument."

Now, Simon said that the PHP SQLite3 interface (which I would like to use), is 
a thin wrapper around the SQLite C interface. Can I conclude, then, that such 
multiple statements would be properly executed if presented via the PHP 
interface? What would happen if such a string contained more than one SELECT - 
or if the SELECT is not the first statement?

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

Re: [sqlite] SQLITE LIMIT clause

[Richard Hipp]

On Mon, Oct 10, 2011 at 10:27 AM, cricketfan  wrote:

>
> SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC;
>
> Since I have the index on PK1, I believe the rows will be returned in the
> ORDER of PK1. Putting an ORDER BY clause will be a no-op.
> Do you think otherwise?
>

There is no guarantee of this.  Without the ORDER BY clause, SQLite might
return the rows in PK1 order, or it might not.  The answer depends on what
other indices are available, whether or not you have run ANALYZE, the
distribution of values for PK1 in your table, what PRAGMAs you might have
run, and which version of SQLite you are using.

If you do include the ORDER BY clause, SQLite will probably arrange for the
answer to come out in PK1 order by default, and thus avoid doing any sorting
on the result.  ORDER BY is an expensive operation, and so the query
optimizer in SQLite works hard to turn it into a no-op.  But you still need
to include the ORDER BY clause in your query so that SQLite knows that it is
not free to choose a different query plan that outputs PK1 in some other
order.


>
>
> Gabríel "A." Pétursson wrote:
> >
> > Be aware that if you do not specify an ORDER BY clause, the order of the
> > returned rows are undefined. You might not even end up with rows with a
> > primary key even near 100.
> >
> > What you probably want is:
> >SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC;
> >
> > Other than that, those two queries should be near identical in your
> > situation. Mind the gaps.
> >
> > On 10/07/2011 03:24 PM, cricketfan wrote:
> >> Any advice would be greatly appreciated.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32624793.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLITE LIMIT clause

[Igor Tandetnik]

cricketfan  wrote:
> SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC;
> 
> Since I have the index on PK1, I believe the rows will be returned in the
> ORDER of PK1. Putting an ORDER BY clause will be a no-op.

Probably, but that's an implementation detail. If you rely on a particular 
property of a resultset, it's best to request it explicitly, rather than hoping 
that the implementation just happens to tilt your way.
-- 
Igor Tandetnik

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

Re: [sqlite] SQLITE LIMIT clause

[cricketfan]

SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC;

Since I have the index on PK1, I believe the rows will be returned in the
ORDER of PK1. Putting an ORDER BY clause will be a no-op. 
Do you think otherwise?


Gabríel A. Pétursson wrote:
> 
> Be aware that if you do not specify an ORDER BY clause, the order of the 
> returned rows are undefined. You might not even end up with rows with a 
> primary key even near 100.
> 
> What you probably want is:
>SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC;
> 
> Other than that, those two queries should be near identical in your 
> situation. Mind the gaps.
> 
> On 10/07/2011 03:24 PM, cricketfan wrote:
>> Any advice would be greatly appreciated.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32624793.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] How to design this table?

[Igor Tandetnik]

张一帆  wrote:
> i have some data like "a and b or c ...",there will be a word 'and' or
> 'or' which means the Logical relations between each item.So how to design a
> table to store the data in best way?

Best way to achieve which goals? What operations do you need to perform on said 
data?
-- 
Igor Tandetnik

___
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 a query on a very large database table?

[Igor Tandetnik]

Frank Chang  wrote:
> Florian Weimar and  Igor Tadetnik,
> 
> When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1,
> 
> select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r
> owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = 
> t1.FIELDNAME)
> order by 1;

This query doesn't make any sense, no more than the one with GROUP BY did. 
Let's step back for a minute - what exactly are you trying to achieve? In what 
way does this simple query fail to reach your goal, whatever that may be:

select FieldName, rowid from BlobLastNameTest;

Explain the problem you are trying to solve, *not* your proposed solution.
-- 
Igor Tandetnik

___
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

[Igor Tandetnik]

Frank Chang  wrote:
> So, why is my query just working accidently?

I take it back - the behavior of your query is well-defined, for the simple 
reason that FieldName is unique, so every group only has a single row in it (as 
someone else has kindly pointed out - I missed this detail on the first 
reading). For that same reason, the whole GROUP BY and sub-select dance is 
completely pointless. Your query is just a very elaborate and wasteful way to 
write

select FieldName, rowid from BlobLastNameTest;
-- 
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?

[Black, Michael (IS)]

With the relatively new prefix option FTS4 appears to be slightly faster 
now...could be within the error spread though.

I had to upgrade to 3.7.8 -- not sure when that prefix came in but it wasn't in 
3.7.5.

Might be nice if the docs mention in what version something shows up.



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

sqlite> begin;
sqlite> .read data2.sql
sqlite> commit;
sqlite> .timer on
sqlite> select count(*) from ftext where t match 'a*';
38462
CPU Time: user 0.004999 sys 0.00
sqlite> select count(*) from ftext where t match 'b*';
38462
CPU Time: user 0.003999 sys 0.000999
sqlite> select count(*) from ftext where t match 'c*';
38462
CPU Time: user 0.004999 sys 0.00
sqlite> select count(*) from ftext where t match 'd*';
38462

Using the table method
sqlite> .timer on
sqlite> select count(*) from text where left=98;
38462
CPU Time: user 0.005999 sys 0.00
sqlite> select count(*) from text where left=99;
38462
CPU Time: user 0.004999 sys 0.00
sqlite> select count(*) from text where left=97;
38462
CPU Time: user 0.004999 sys 0.00
sqlite> select count(*) from text where left=100;
38462
CPU Time: user 0.004999 sys 0.00

Although the database is quite a bit bigger.

Table method -- 4.24GB

FTS3 -- 4.92GB

FTS4 -- 5.67GB





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 3:58 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Can pre-sorted data help?


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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to design this table?

[zyf01234]

> > i have some data like "a and b or c ...",there will be a word 'and' or
> > 'or' which means the Logical relations between each item.

> If you have "a and b or c" does that mean

> (a and b) or c OR
> a and (b or c) ?

> How does your software know ?

> Simon.


just a and b or c...,like this.thank you! i am also think about the solution of 
this case.Do you have any ideas?
___
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 a query on a very large database table?

[Frank Chang]

Florian Weimar and  Igor Tadetnik,
 
 When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1, 
 
select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r
owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME)
order by 1;


 
the explain output seems to have 40% less steps. Does this mean order by 1 
should be faster than group by t1.FIELDNAME as I scale up the number of rows in 
the very large database table BLOBLASTNAMETEST. The explain output is ahown 
below. Thank you. 
 
 
sqlite> explain select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r
owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME)
order by 1;
0|Trace|0|0|0||00|
1|Noop|0|0|0||00|
2|Goto|0|32|0||00|
3|OpenRead|3|108789|0|keyinfo(1,BINARY)|00|
4|Rewind|3|30|1|0|00|
5|IdxRowid|3|1|0||00|
6|Null|0|3|0||00|
7|Integer|1|4|0||00|
8|Null|0|6|0||00|
9|Null|0|5|0||00|
10|OpenRead|4|108789|0|keyinfo(1,BINARY)|00|
11|Column|3|0|7||00|
12|IsNull|7|20|0||00|
13|SeekGe|4|20|7|1|00|
14|IdxGE|4|20|7|1|01|
15|IdxRowid|4|9|0||00|
16|CollSeq|0|0|0|collseq(BINARY)|00|
17|AggStep|0|9|5|min(1)|01|
18|Goto|0|20|0||00|
19|Next|4|14|0||00|
20|Close|4|0|0||00|
21|AggFinal|5|1|0|min(1)|00|
22|SCopy|5|10|0||00|
23|Move|10|3|1||00|
24|IfZero|4|25|-1||00|
25|Ne|3|29|1||6c|
26|Column|3|0|11||00|
27|IdxRowid|3|12|0||00|
28|ResultRow|11|2|0||00|
29|Next|3|5|0||00|
30|Close|3|0|0||00|
31|Halt|0|0|0||00|
32|Transaction|0|0|0||00|
33|VerifyCookie|0|7|0||00|
34|TableLock|0|2|0|BlobLastNameTest|00|
35|Goto|0|3|0||00|
sqlite>
 
 
 
 
 
sqlite> explain select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r
owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME)
GROUP by t1.FieldName;
0|Trace|0|0|0||00|
1|Noop|0|0|0||00|
2|Integer|0|4|0||00|
3|Integer|0|3|0||00|
4|Gosub|6|51|0||00|
5|Goto|0|55|0||00|
6|OpenRead|3|108789|0|keyinfo(1,BINARY)|00|
7|Rewind|3|40|9|0|00|
8|IdxRowid|3|9|0||00|
9|Null|0|11|0||00|
10|Integer|1|12|0||00|
11|Null|0|14|0||00|
12|Null|0|13|0||00|
13|OpenRead|4|108789|0|keyinfo(1,BINARY)|00|
14|Column|3|0|15||00|
15|IsNull|15|23|0||00|
16|SeekGe|4|23|15|1|00|
17|IdxGE|4|23|15|1|01|
18|IdxRowid|4|17|0||00|
19|CollSeq|0|0|0|collseq(BINARY)|00|
20|AggStep|0|17|13|min(1)|01|
21|Goto|0|23|0||00|
22|Next|4|17|0||00|
23|Close|4|0|0||00|
24|AggFinal|13|1|0|min(1)|00|
25|SCopy|13|18|0||00|
26|Move|18|11|1||00|
27|IfZero|12|28|-1||00|
28|Ne|11|39|9||6c|
29|Column|3|0|8||00|
30|Compare|7|8|1|keyinfo(1,BINARY)|00|
31|Jump|32|36|32||00|
32|Move|8|7|1||00|
33|Gosub|5|45|0||00|
34|IfPos|4|54|0||00|
35|Gosub|6|51|0||00|
36|Column|3|0|1||00|
37|IdxRowid|3|2|0||00|
38|Integer|1|3|0||00|
39|Next|3|8|0||00|
40|Close|3|0|0||00|
41|Gosub|5|45|0||00|
42|Goto|0|54|0||00|
43|Integer|1|4|0||00|
44|Return|5|0|0||00|
45|IfPos|3|47|0||00|
46|Return|5|0|0||00|
47|Copy|1|19|0||00|
48|Copy|2|20|0||00|
49|ResultRow|19|2|0||00|
50|Return|5|0|0||00|
51|Null|0|1|0||00|
52|Null|0|2|0||00|
53|Return|6|0|0||00|
54|Halt|0|0|0||00|
55|Transaction|0|0|0||00|
56|VerifyCookie|0|7|0||00|
57|TableLock|0|2|0|BlobLastNameTest|00|
58|Goto|0|6|0||00|
sqlite>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Wamiduku]

On Oct 10, 4:01 am, "Joe Mistachkin"  wrote:
>
> 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.

I've unpacked all of sqlite-netFx40-binary-bundle-
Win32-2010-1.0.76.0.zip to a directory and run it from there, so
everything was in place in the directory.

Did you try this on a clean machine (fresh Windows installation) with
nothing but .NET 4 installed? If you try it on any machine where
System.Data.SQLite has been installed once, you'll not see the error.

> > 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.

I ran the tests from the unpacked sqlite-netFx40-
binary-bundle-Win32-2010-1.0.76.0.zip directory, so the files were
neither affected by the installation, nor the uninstallation.

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

Re: [sqlite] load database into memory java applet

[Stephan Beal]

On Mon, Oct 10, 2011 at 12:57 PM, wrote:

> Connection conn = DriverManager.getConnection("jdbc:sqlite:memory:");
>

try:

jdbc:sqlite::memory:

note the extra colon. i don't know if that will work, but it is at least
semantically correct (whereas the version you post is providing the name
"memory:", which is not correct).

-- 
- 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] load database into memory java applet

[orcun . ertugrul]

Hi all;
I am new to mailing list. My database file on the net (in a http address)
and i wanna load that file from an applet. I tried the below code and it
gives "The filename, directory name or volume label syntax is incorrect"
error.

Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:memory:");
String attachStmt = "ATTACH " +
"'http://localhost:8080/appletOrcun/SertifikaDeposu.svt'" + " AS src";
Statement stt = conn.createStatement();
stt.execute(attachStmt);


___
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 a very large database query by avoiding total index scans

[Frank Chang]

Florence Weimar, Igor Tadetnik, Simon Slavin, 
I ran ANALYZE BLOBLASTNAMETEST in order to get better index statistics. 
Then I modified my query to: select t1.FieldName,t1.rowid from 
BlobLastNameTest t1
 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.
FIELDNAME) group by t1.FieldName . 
It appears that this new query is not doing a full index scan while 
select FieldName, MIN(ROWID) FROM BLOBLASTNAMETEST GROUP BY FIELDNAME is still 
doing a full index scan. The explain query plans are shown below. Thank you.
 
sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1
 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.
FIELDNAME) group by t1.FieldName;
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary5 
(~2709783 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary5 (FieldName=?
) (~1 rows)
sqlite> explain query plan select FieldName, min(rowid) from BlobLastNameTest gr
oup by FieldName;
0|0|0|SCAN TABLE BlobLastNameTest USING COVERING INDEX claramary5 (~5419566 rows
)
sqlite>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users