[sqlite] Substring question

[Pete]

I need to select a substring of a column that starts 1 character after a
colon in the column and continues to the end of the column.  For example, if
the column contained "abc:xyz" I want the select statement to return only
"xyz" (the characters after the colon).  The substr function requires
specific character positions - is there a way to do this?
Pete
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Turn off journal if no transaction

[Eugene Ong]

Hi,

I don't use transactions in my app, is the data safe from failure if I turn off 
the journal?
The insert isn't concurrent and I'm using System.Data.SQLite (.net)

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

Re: [sqlite] 3.7.7, URI filenames, and UNC paths

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/24/2011 08:28 AM, Cheetah wrote:
> The documentation clearly states that using an authority other than
> localhost is not permitted.

This was a deliberate decision when the feature was originally proposed.
The original spec was that the hostname would be completely ignored.  It
will be possible to change the spec in the future to be more lenient but
starting with it lenient and then tightening up could potentially break
existing code.

Anyone implementing an environment hosting SQLite (eg a web browser) would
need to be concerned about the URIs and host names.  Currently they can rest
assured that you can't trick SQLite.  This may be overly cautious but as
pointed out above it can be relaxed without harm.

> However, AFAICT, the standard form of a file:
> URI on windows for a UNC path uses exactly that.

Are you proposing that SQLite somehow work out if the supplied name maps
back to the local machine?  That would be mind numbingly hard!  (Netbios
names, DNS names, host files, etc)

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

iEYEARECAAYFAk4FV78ACgkQmOOfHg372QTnjgCeOLAwfdoWO6zXiqgMo/N677J8
6cMAn3wZGv0nKQ/4+w5/8/+sz5rIdDWl
=FRCH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexes on columns

[logan...@gmail.com]

Ahh..ok, thanks for the response everyone. I really appreciate the help here
:).

On Fri, Jun 24, 2011 at 11:10 AM, Igor Tandetnik wrote:

> On 6/24/2011 1:58 PM, logan...@gmail.com
> wrote:
> > Sorry, but seems like I'm missing something here.
> >
> >  From my understanding it looks like for Integer ID columns that are PK
> > SQLite doesn't generate any indexes. Is this true?
>
> It's true in a narrow technical sense, but it doesn't matter in practice.
>
> In SQLite, data is organized in B-trees. Each table and each index is a
> B-tree. For an index, the key into that B-tree is the set of fields the
> index is built on. For a table, each row has a unique integer
> identifier, usually referred to as RowId, which serves as a key into the
> table's B-tree. Looking up a row in the table by its RowId is as fast as
> looking up an index entry by its key, because it's really the same
> operation.
>
> When you declare a column as INTEGER PRIMARY KEY, SQlite simply makes it
> an alias for an already-existing, always-present RowId column. Again,
> the table itself essentially acts as an index on this column, no
> additional external data structure is necessary.
> --
> Igor Tandetnik
>
> ___
> 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] [Bug]Type mismatch in call to sqlite3MPrintf

[Richard Hipp]

On Fri, Jun 24, 2011 at 2:17 PM, Arushi Aggarwal wrote:

> Hi all,
>
> While using sqlite to test a dynamic type tracking tool for LLVM that we
> are
> developing, we found the following bug in sqlite.
>
> I have sqlite version 3.7.6.3, and in the following call to sqlite3MPrintf
>
>  zStmt = sqlite3MPrintf(db, "CREATE%s INDEX %.*s",
>onError==OE_None ? "" : " UNIQUE",
>pEnd->z - pName->z + 1,
>pName->z);
>

Fixed here:  http://www.sqlite.org/src/ci/90cfeaf7b6


>
> from the function sqlite3CreateIndex, the argument 4th argument (pEnd->z -
> pName->z + 1) is a 64 bit integer on x86_64. However, the format string
> expects an int (in sqlite3VXPrintf).
>
> I have not found a case where the value of the argument is actually greater
> than a 32 bit int. However, in some execution that might be the case.
>
> Thanks,
> Regards,
>
> Arushi Aggarwal
> Graduate Student,
>  Department of Computer Science,
> University of Illinois, Urbana-Champaign.
> ___
> 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] .NET4 is available?

[Massimo Savazzi]

Second problem I've discovered is that with previous library I was able to
create an Entity Data model while with the latest is not possible the server
explorer does not work.

 

From: Massimo Savazzi [mailto:massimo.sava...@gmail.com] 
Sent: Friday, 24 June, 2011 21:29
To: 'sh...@sqlite.org'; sqlite-users@sqlite.org
Subject: Re: [sqlite] .NET4 is available?

 

Shane,

Thank you very much for your email.

 

I've unistalled the old .NET4 libraries and installed the new ones.

I've updated project references and DLLs distributed with the app.

 

Can you help me as when I try the new app I get this error:

 

 

See the end of this message for details on invoking 

just-in-time (JIT) debugging instead of this dialog box.

 

** Exception Text **

System.BadImageFormatException: An attempt was made to load a program with
an incorrect format. (Exception from HRESULT: 0x8007000B)

   at System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
utf8Filename, Int32 flags, IntPtr& db)

   at System.Data.SQLite.SQLite3.Open(String strFilename,
SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) in
c:\work\SQLite\dotnet\System.Data.SQLite\SQLite3.cs:line 131

   at System.Data.SQLite.SQLiteConnection.Open() in
c:\work\SQLite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line 834

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)

   at e2EPGManager.frmEPGManager.UpdateCategories() in E:\My
Projects\Enigma2 EPG Manager - Xeon 2\Enigma2 EPG Manager\Enigma2 EPG
Manager\frmEnigma2EPGManager.vb:line 44

   at e2EPGManager.frmEPGManager.frmEPGManager_Load(Object sender, EventArgs
e) in E:\My Projects\Enigma2 EPG Manager - Xeon 2\Enigma2 EPG
Manager\Enigma2 EPG Manager\frmEnigma2EPGManager.vb:line 76

   at System.EventHandler.Invoke(Object sender, EventArgs e)

   at System.Windows.Forms.Form.OnLoad(EventArgs e)

   at System.Windows.Forms.Form.OnCreateControl()

   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)

   at System.Windows.Forms.Control.CreateControl()

   at System.Windows.Forms.Control.WmShowWindow(Message& m)

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

   at System.Windows.Forms.Form.WmShowWindow(Message& m)

   at System.Windows.Forms.Form.WndProc(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)

 

 

** Loaded Assemblies **

mscorlib

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.235 (RTMGDR.030319-2300)

CodeBase:
file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
 



Enigma2 EPG Manager

Assembly Version: 1.0.0.0

Win32 Version: 1.0.0.0

CodeBase:
file:///E:/My%20Projects/Enigma2%20EPG%20Manager%20-%20Xeon%202/Enigma2%20EP
G%20Manager/Enigma2%20EPG%20Manager/bin/Release/Enigma2%20EPG%20Manager.exe
 



Microsoft.VisualBasic

Assembly Version: 10.0.0.0

Win32 Version: 10.0.30319.1 built by: RTMRel

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.
0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll
 



System

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.232 built by: RTMGDR

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a
5c561934e089/System.dll
 



System.Core

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.225 built by: RTMGDR

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0_
_b77a5c561934e089/System.Core.dll
 



System.Windows.Forms

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.235 built by: RTMGDR

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0
_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
 



System.Drawing

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.1 built by: RTMRel

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0
.0__b03f5f7f11d50a3a/System.Drawing.dll
 



System.Runtime.Remoting

Assembly Version: 4.0.0.0

Win32 

Re: [sqlite] .NET4 is available?

[Massimo Savazzi]

Shane,

Thank you very much for your email.

 

I've unistalled the old .NET4 libraries and installed the new ones.

I've updated project references and DLLs distributed with the app.

 

Can you help me as when I try the new app I get this error:

 

 

See the end of this message for details on invoking 

just-in-time (JIT) debugging instead of this dialog box.

 

** Exception Text **

System.BadImageFormatException: An attempt was made to load a program with
an incorrect format. (Exception from HRESULT: 0x8007000B)

   at System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
utf8Filename, Int32 flags, IntPtr& db)

   at System.Data.SQLite.SQLite3.Open(String strFilename,
SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) in
c:\work\SQLite\dotnet\System.Data.SQLite\SQLite3.cs:line 131

   at System.Data.SQLite.SQLiteConnection.Open() in
c:\work\SQLite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line 834

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)

   at e2EPGManager.frmEPGManager.UpdateCategories() in E:\My
Projects\Enigma2 EPG Manager - Xeon 2\Enigma2 EPG Manager\Enigma2 EPG
Manager\frmEnigma2EPGManager.vb:line 44

   at e2EPGManager.frmEPGManager.frmEPGManager_Load(Object sender, EventArgs
e) in E:\My Projects\Enigma2 EPG Manager - Xeon 2\Enigma2 EPG
Manager\Enigma2 EPG Manager\frmEnigma2EPGManager.vb:line 76

   at System.EventHandler.Invoke(Object sender, EventArgs e)

   at System.Windows.Forms.Form.OnLoad(EventArgs e)

   at System.Windows.Forms.Form.OnCreateControl()

   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)

   at System.Windows.Forms.Control.CreateControl()

   at System.Windows.Forms.Control.WmShowWindow(Message& m)

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

   at System.Windows.Forms.Form.WmShowWindow(Message& m)

   at System.Windows.Forms.Form.WndProc(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)

 

 

** Loaded Assemblies **

mscorlib

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.235 (RTMGDR.030319-2300)

CodeBase:
file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll



Enigma2 EPG Manager

Assembly Version: 1.0.0.0

Win32 Version: 1.0.0.0

CodeBase:
file:///E:/My%20Projects/Enigma2%20EPG%20Manager%20-%20Xeon%202/Enigma2%20EP
G%20Manager/Enigma2%20EPG%20Manager/bin/Release/Enigma2%20EPG%20Manager.exe



Microsoft.VisualBasic

Assembly Version: 10.0.0.0

Win32 Version: 10.0.30319.1 built by: RTMRel

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.
0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll



System

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.232 built by: RTMGDR

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a
5c561934e089/System.dll



System.Core

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.225 built by: RTMGDR

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0_
_b77a5c561934e089/System.Core.dll



System.Windows.Forms

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.235 built by: RTMGDR

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0
_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll



System.Drawing

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.1 built by: RTMRel

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0
.0__b03f5f7f11d50a3a/System.Drawing.dll



System.Runtime.Remoting

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)

CodeBase:
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Remoting/v
4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Remoting.dll



System.Configuration

Assembly Version: 4.0.0.0

Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)

CodeBase:

[sqlite] ANN: C#-SQLite 3.7.6.3

[Noah Hart]

C#-SQLite has been updated to release 3.7.6.3 and is now ready for use.

The 6/24/2011 release features:

 * Updated to SQLite version 3.7.6.3
 * Silverlight support
 * Windows 7 Phone

It now runs 58,100 of the tcl testharness tests without errors.

The project is located at http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.7.6.3-tp31922081p31922081.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

[sqlite] [Bug]Type mismatch in call to sqlite3MPrintf

[Arushi Aggarwal]

Hi all,

While using sqlite to test a dynamic type tracking tool for LLVM that we are
developing, we found the following bug in sqlite.

I have sqlite version 3.7.6.3, and in the following call to sqlite3MPrintf

  zStmt = sqlite3MPrintf(db, "CREATE%s INDEX %.*s",
onError==OE_None ? "" : " UNIQUE",
pEnd->z - pName->z + 1,
pName->z);

from the function sqlite3CreateIndex, the argument 4th argument (pEnd->z -
pName->z + 1) is a 64 bit integer on x86_64. However, the format string
expects an int (in sqlite3VXPrintf).

I have not found a case where the value of the argument is actually greater
than a 32 bit int. However, in some execution that might be the case.

Thanks,
Regards,

Arushi Aggarwal
Graduate Student,
 Department of Computer Science,
University of Illinois, Urbana-Champaign.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexes on columns

[Igor Tandetnik]

On 6/24/2011 1:58 PM, logan...@gmail.com 
wrote:
> Sorry, but seems like I'm missing something here.
>
>  From my understanding it looks like for Integer ID columns that are PK
> SQLite doesn't generate any indexes. Is this true?

It's true in a narrow technical sense, but it doesn't matter in practice.

In SQLite, data is organized in B-trees. Each table and each index is a 
B-tree. For an index, the key into that B-tree is the set of fields the 
index is built on. For a table, each row has a unique integer 
identifier, usually referred to as RowId, which serves as a key into the 
table's B-tree. Looking up a row in the table by its RowId is as fast as 
looking up an index entry by its key, because it's really the same 
operation.

When you declare a column as INTEGER PRIMARY KEY, SQlite simply makes it 
an alias for an already-existing, always-present RowId column. Again, 
the table itself essentially acts as an index on this column, no 
additional external data structure is necessary.
-- 
Igor Tandetnik

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

Re: [sqlite] Indexes on columns

[Simon Slavin]

On 24 Jun 2011, at 6:58pm, logan...@gmail.com wrote:

> From my understanding it looks like for Integer ID columns that are PK
> SQLite doesn't generate any indexes. Is this true?
> 
> If the above is true then I want to create an index to improve the perf of
> my queries that are run against it.

The indexes are generated and SQLite will use them internally whenever it finds 
them convenient.  It's just that they are not given names, so you can find out 
anything about them yourself.

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

Re: [sqlite] Indexes on columns

[Mr. Puneet Kishor]

On Jun 24, 2011, at 1:58 PM, logan...@gmail.com wrote:

> Sorry, but seems like I'm missing something here.
> 
> From my understanding it looks like for Integer ID columns that are PK
> SQLite doesn't generate any indexes. Is this true?
> 

No, what you think is not true. SQLite does generate an index for INTEGER 
PRIMARY KEY columns.

> If the above is true then I want to create an index to improve the perf of
> my queries that are run against it.
> 
> Thanks,
> Hitesh
> 
> On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik  wrote:
> 
>> logan...@gmail.com wrote:
>>> Yes, that's exactly what it is. Here is the definition of one of the
>> table:
>>> 
>>> CREATE TABLE [Attributes] (
>>> [Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
>>> [Name] VARCHAR(50)  NOT NULL
>>> )
>>> 
>>> Will creating explicit index on Id fix this issue?
>> 
>> What issue? Why is having an explicit index, separate from that built into
>> the table itself, important to you? What exactly do you feel is wrong with
>> the way things are now?
>> --
>> Igor Tandetnik
>> 
>> ___
>> 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] Indexes on columns

[logan...@gmail.com]

Sorry, but seems like I'm missing something here.

>From my understanding it looks like for Integer ID columns that are PK
SQLite doesn't generate any indexes. Is this true?

If the above is true then I want to create an index to improve the perf of
my queries that are run against it.

Thanks,
Hitesh

On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik  wrote:

> logan...@gmail.com wrote:
> > Yes, that's exactly what it is. Here is the definition of one of the
> table:
> >
> > CREATE TABLE [Attributes] (
> > [Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
> > [Name] VARCHAR(50)  NOT NULL
> > )
> >
> > Will creating explicit index on Id fix this issue?
>
> What issue? Why is having an explicit index, separate from that built into
> the table itself, important to you? What exactly do you feel is wrong with
> the way things are now?
> --
> Igor Tandetnik
>
> ___
> 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] Query with UNION on large table

[Jean-Christophe Deschamps]

Hi Rense,


>Thanks for this idea. In fact, the purpose of my original query is
>exactly to reduce the database. The 800 mln rows were exported from
>another source, and I was hoping to be able to use sqlite to manage
>this massive amount of data (e.g., removing redundant information)
>before I proceed with my analyses.
>So, would your approach also work if I import the data, rather than 
>insert?

I'm afraid I'm not understanding your question correctly. What's the 
distinction between import (into SQLite) and insert (into SQLite)?

If 10 rows of your raw data looks like e.g.
111, 222(this couple is direct, n1 <= n2)
115, 227   "
143, 333   "
254, 555   "
567, 666   "
410, 189(this couple is reverse, n1 > n2)
333, 143   "
227, 115   "
555, 254   "
666, 567   "

where only (111, 222) and (189, 410) are not mirrored, you could end up 
with only 6 rows since all couple of pairs which also have a mirror 
will collapse into a single row (first value is the flags):
1, 111, 222
2, 189, 410
3, 115, 227
3, 143, 333
3, 254, 555
3, 567, 666

So yes, with a large ratio of mirrored pairs, your DB will shrink and 
more important will hold roughly half the number of rows (making 
queries slightly less than twice faster).

If your 800M rows have, say, 20M of pairs without mirror, your new 
Schema would make that:
20M orphan rows with flags in (1, 2)
(800 - 20) / 2 = 390M rows with flags = 3
All in all, that would be 410M rows.

The initial 800M-row DB holds (800M * 2) = 1600M integers, but the new 
DB would contains only (410M * 3) = 1230M integers, 410M of them taking 
only one byte of storage (flags).

If your DB is "build once, insert/update rarely and query mostly" you 
will certainly save much both in space and coffee ;-)

You can obviously apply the same schema to the alien DB as well.

--
j...@antichoc.net  

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

Re: [sqlite] Query with UNION on large table

[Rense Corten]

Jean-Christophe,
Thanks for this idea. In fact, the purpose of my original query is
exactly to reduce the database. The 800 mln rows were exported from
another source, and I was hoping to be able to use sqlite to manage
this massive amount of data (e.g., removing redundant information)
before I proceed with my analyses.
So, would your approach also work if I import the data, rather than insert?

On Fri, Jun 24, 2011 at 5:45 AM, Jean-Christophe Deschamps
 wrote:
> Rense,
>
>>As for the ranges of n1 and n1: they are both roughly between 6
>>and 1200 .
>>
>>Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
>>Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;
>>
>>1|0|0|SCAN TABLE table1 (~437976176 rows)
>>2|0|0|SCAN TABLE table1 (~437976176 rows)
>>0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)
>
> If you can change your schema at all and your application supports
> taking a bit more time for inserts, I believe you can achieve much
> better query times.
>
> I tried the following schema:
> CREATE TABLE "T" (
>   "flags" INTEGER,
>   "n1" INTEGER DEFAULT (random() % 100),
>   "n2" INTEGER DEFAULT (random() % 100),
>   CONSTRAINT "T_order" CHECK(((n1 <= n2) and (flags between 1 and 3;
>
>>In my database, the mirrored pairs vastly outnumber the non-mirrored
>>ones, to the extent that the non-mirrored pairs are actually extremely
>>rare (but relevant).
>
> Given the fact that mirrored pairs dominate, if you store new pairs
>  (N1, N2)
> as
>  (flags, n1, n2)
> with n1 <= n2 and flags bit 0 set if (n1, n2) = (N1, N2)
>               and flags bit 1 set if (n1, n2) = (N2, N1)
> your query becomes:
>   select * from T where flags = 3;
>
> If you build an index on flags, e.g.
>   CREATE INDEX "ixFlags" ON "T" ("flags");
> then it will be used (but it is regularly pointed out that in cases
> where the distribution of values queried doesn't fall in small bins, an
> index may in fact slow things down).
>
> Since your have mostly mirrored pairs, using the schema above will
> decrease the size of your DB (or keep it about the same if you use the
> index) but and --I see that as a big bonus-- essentially half the
> number of rows.
>
> Note that, if it can be of any value to your application, you can even
> have the mirrored pairs sorted if you make the index
>   CREATE INDEX "ixFlags" ON "T" ("flags", "n1");
> Of course this compound index will take some more room, but depending
> on your needs it may prove useful.
>
> The price to pay is having to query first before insert (or have a
> [slow] trigger do the work for you).  Could your application support that?
>
> Sounds to me it could be worth trying on a reduced DB.
>
>
>
> --
> j...@antichoc.net
>
> ___
> 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] 3.7.7, URI filenames, and UNC paths

[Cheetah]

Reading the release notes and documentation for SQLite 3.7.7, I noticed what
I believe may be an oversight / minor problem with the URI filename support
when using SQLite on a Windows platform.

The documentation clearly states that using an authority other than
localhost is not permitted.  However, AFAICT, the standard form of a file:
URI on windows for a UNC path uses exactly that.

For example, the UNC path \\darkstar\home\fred\data.db would map to the
example invalid URI file://darkstar/home/fred/data.db in the documentation.
Windows does accept file://\\darkstar/home/fred/data.db as an equivalent,
but the APIs I've seen to automatically convert a file path to a file: URI
will generate the former format which SQLite does not accept.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] system.data.sqlite x86 vs x64 GAC potential fix!

[Anthony Graham]

I've had numerous issues with the "wrong" version of SQLite being installed
in the GAC.

eg I make my app x86 and use the x86 SQLite dll and have that in my app
directory.

but the .net framework _always_ prefers to load from the GAC so the the x64
system.data.sqlite is installed my app loads it and chokes... (there is no
proper way to disable this behaviour)

Or if I make a x64 app and the user has the x86 dll installed in the gac,
again my app dies.


After ridiculous amounts of goggling I've discovered that the .net framework
does indeed handle this situation if it is aware of the processor
architecture the DLL is made for.

I've just installed the x86 one and discovered that this installs the
managed assembly as MSIL not x86 or AMD64 and adds the interop assembly
location to the environment path. I guess this it the managed only DLL.

So whichever sqlite.interop.dll it finds first gets loaded, regardless of
architecture. resulting in a BadImageFormatException.

The solution to me it seems is to go back to the mixed mode* (all in one)
assemblies and mark them as x64  / AMD64 respectively for installing into
the GAC  thus allowing the assemblies to be correctly selected
automatically.

in this screenshot you can see that, for example System.data.OracleClient
has two versions, one for x86 and one for AMD64, in this situation the
correct one is always loaded. However Sqlite is registered as MSIL
(universal), which is fine for anything that doesn't utilise amd64 or x86
specific instructions, but causes headaches here.

http://tinypic.com/view.php?pic=27x4eo4=7

Could similar be done with SQLite?



- Anthony

*I'm assuming it's not a mixed mode DLL being installed into the GAC
incorrectly as MSIL, that would be really bad...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG?] unexpected effect of view nesting on type conversion

[Pavel Ivanov]

> Just thought I should add that the problem seems to be gone with the
> just released version 3.7.7, but it's not mentioned in the release notes.

It's not in release notes but it's in the timeline. See
http://www.sqlite.org/src/info/91e2e8ba6f and
http://www.sqlite.org/src/info/0b3174e0b1.


Pavel


On Fri, Jun 24, 2011 at 7:15 AM, Mark Brand  wrote:
>
 I've run into a phenomenon I don't understand where view nesting affects
 types.
 Seen in sqlite 3.7.6.3
>>> sqlite-3.6.23 does NOT show this behavior.
>> After further reflection, I am going to go out on a limb and suggest
>> that the behavior described in the OP is a regression. The behavior
>> started somewhere between versions 3.6.23 and 3.7.6.3. It can have
>> serious consequences in the form of calculation errors.
>
>
> Just thought I should add that the problem seems to be gone with the
> just released version 3.7.7, but it's not mentioned in the release notes.
>
> regards,
>
> Mark
> ___
> 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 version 3.7.7 released

[Simon Slavin]

On 24 Jun 2011, at 12:04pm, D. Richard Hipp wrote:

> SQLite version 3.7.7 is now available on the SQLite website:
> 
> http://www.sqlite.org/
> 
> A list of changes is available at
> 
> http://www.sqlite.org/releaselog/3_7_7.html

In section 3.3 of

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

could you add text showing what the default is for each of these parameters ?  
In other words, what value I would pass through for 'vfs=' to get the default 
behaviour, etc..

I know you talk about it a bit in

http://www.sqlite.org/c3ref/open.html#urifilenameexamples

but it's not very clear from that.

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

Re: [sqlite] Query with UNION on large table

[Jean-Christophe Deschamps]

Rense,

>As for the ranges of n1 and n1: they are both roughly between 6
>and 1200 .
>
>Here are the results of  EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1
>Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1;
>
>1|0|0|SCAN TABLE table1 (~437976176 rows)
>2|0|0|SCAN TABLE table1 (~437976176 rows)
>0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (INTERSECT)

If you can change your schema at all and your application supports 
taking a bit more time for inserts, I believe you can achieve much 
better query times.

I tried the following schema:
CREATE TABLE "T" (
   "flags" INTEGER,
   "n1" INTEGER DEFAULT (random() % 100),
   "n2" INTEGER DEFAULT (random() % 100),
   CONSTRAINT "T_order" CHECK(((n1 <= n2) and (flags between 1 and 3;

>In my database, the mirrored pairs vastly outnumber the non-mirrored
>ones, to the extent that the non-mirrored pairs are actually extremely
>rare (but relevant).

Given the fact that mirrored pairs dominate, if you store new pairs
  (N1, N2)
as
  (flags, n1, n2)
with n1 <= n2 and flags bit 0 set if (n1, n2) = (N1, N2)
   and flags bit 1 set if (n1, n2) = (N2, N1)
your query becomes:
   select * from T where flags = 3;

If you build an index on flags, e.g.
   CREATE INDEX "ixFlags" ON "T" ("flags");
then it will be used (but it is regularly pointed out that in cases 
where the distribution of values queried doesn't fall in small bins, an 
index may in fact slow things down).

Since your have mostly mirrored pairs, using the schema above will 
decrease the size of your DB (or keep it about the same if you use the 
index) but and --I see that as a big bonus-- essentially half the 
number of rows.

Note that, if it can be of any value to your application, you can even 
have the mirrored pairs sorted if you make the index
   CREATE INDEX "ixFlags" ON "T" ("flags", "n1");
Of course this compound index will take some more room, but depending 
on your needs it may prove useful.

The price to pay is having to query first before insert (or have a 
[slow] trigger do the work for you).  Could your application support that?

Sounds to me it could be worth trying on a reduced DB.



--
j...@antichoc.net  

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

Re: [sqlite] Indexes on columns

[Igor Tandetnik]

logan...@gmail.com wrote:
> Yes, that's exactly what it is. Here is the definition of one of the table:
> 
> CREATE TABLE [Attributes] (
> [Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
> [Name] VARCHAR(50)  NOT NULL
> )
> 
> Will creating explicit index on Id fix this issue?

What issue? Why is having an explicit index, separate from that built into the 
table itself, important to you? What exactly do you feel is wrong with the way 
things are now?
-- 
Igor Tandetnik

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

Re: [sqlite] [BUG?] unexpected effect of view nesting on type conversion

[Mark Brand]

>>> I've run into a phenomenon I don't understand where view nesting affects
>>> types.
>>> Seen in sqlite 3.7.6.3
>> sqlite-3.6.23 does NOT show this behavior.
> After further reflection, I am going to go out on a limb and suggest
> that the behavior described in the OP is a regression. The behavior
> started somewhere between versions 3.6.23 and 3.7.6.3. It can have
> serious consequences in the form of calculation errors.


Just thought I should add that the problem seems to be gone with the 
just released version 3.7.7, but it's not mentioned in the release notes.

regards,

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

[sqlite] SQLite version 3.7.7 released

[D . Richard Hipp]

SQLite version 3.7.7 is now available on the SQLite website:

 http://www.sqlite.org/

A list of changes is available at

 http://www.sqlite.org/releaselog/3_7_7.html

Further information about this release can be seen at

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

Please post on the SQLite mailing list (sqlite-users@sqlite.org) if you 
encounter any problems with this release.

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



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

Re: [sqlite] Indexes on columns

[logan...@gmail.com]

Yes, that's exactly what it is. Here is the definition of one of the table:

CREATE TABLE [Attributes] (
[Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] VARCHAR(50)  NOT NULL
)

Will creating explicit index on Id fix this issue?

Thanks.

On Thu, Jun 23, 2011 at 11:07 PM, Dan Kennedy  wrote:

> On 06/24/2011 12:26 PM, logan...@gmail.com wrote:
> > Hello,
> >
> > My understanding is that an index is automatically created on any column
> > that is used in the primary key (or a composite index is created if the
> key
> > is composed of different columns). If this is correct then why don't I
> see
> > indexes for those in my table (I'm using SQLite Administrator and Firefox
> > plugin based SQLite manager). I do see indexes for the columns that I
> added
> > a unique constraint upon.
> >
> > Is the above just a GUI error in these tools or an index need to be
> created
> > separately on the columns used in primary keys?
>
> Maybe your tables have "integer primary keys". Those are an exception
> See here:
>
>   http://www.sqlite.org/lang_createtable.html#rowid
>
> ___
> 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] Indexes on columns

[Dan Kennedy]

On 06/24/2011 12:26 PM, logan...@gmail.com wrote:
> Hello,
>
> My understanding is that an index is automatically created on any column
> that is used in the primary key (or a composite index is created if the key
> is composed of different columns). If this is correct then why don't I see
> indexes for those in my table (I'm using SQLite Administrator and Firefox
> plugin based SQLite manager). I do see indexes for the columns that I added
> a unique constraint upon.
>
> Is the above just a GUI error in these tools or an index need to be created
> separately on the columns used in primary keys?

Maybe your tables have "integer primary keys". Those are an exception
See here:

   http://www.sqlite.org/lang_createtable.html#rowid

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