Re: [sqlite] transparent compression implementations for SQLite?

2011-12-30 Thread Simon Slavin 

On 31 Dec 2011, at 7:11am, Ivan Shmakov wrote:

> Download the code and prebuilt DLL.

It includes both.  In other words, the vfs_compress.c file you asked for is in 
there.

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

Re: [sqlite] transparent compression implementations for SQLite?

2011-12-30 Thread Ivan Shmakov 
> Simon Slavin writes:
> On 31 Dec 2011, at 4:10am, Ivan Shmakov wrote:

 >> I wonder, if anyone has any experience with [1] (and where could one
 >> get the vfs_compress.c file, BTW?), or any other transparent
 >> compression implementations for SQLite?

 > Links to the code and a pre-build .dll are in the article, about
 > 2/3rds down the page.

That's strange.  For me, it reads like the following, with the
links to the DLL (which is of little use to me), the SQLite's
own download page and the amalgamation itself (both under
http://www.sqlite.org/) and http://zlib.net/.  Nowhere can I see
a link to vfs_compress.c.  (I've tried it both with Lynx and
Iceweasel, and I've also checked the (X)HTML code itself, but to
no avail.)

TIA.

--cut: 
http://blog.ashodnakashian.com/2011/09/sqlite-with-built-in-online-compression/ 
--

Download

The code holds the same copyright claims as Sqlite, namely none. The
code is experimental. Use it at your own risk.

[78]Download the code and prebuilt DLL. This sqlite3.dll is version
3.7.7.1 amalgamation created with the default settings/flags from
the amalgamation created from original sources by the original
configure and make files. The compression code is added and it’s
built using VS2010 Sp1 and statically liked to the runtime
libraries, as such it has no dependencies.

Building

To build the code, first [79]download a recent Sqlite version. The
[80]3.7.7.1 amalgamation is perfect. The [81]latest Zlib must also
be downloaded and built.

Add the Zlib headers to the include path, copy the vfs_compress.c
file next to sqlite sources and build. Next, build sqlite3.c
amalgamation (or the original sources) and link the binaries of
sqlite3, vfs_compress and Zlib to create the executable.

…

References

…
  78. 
http://blog.ashodnakashian.com/wp-content/uploads/2011/09/sqlite3_compress.zip
  79. http://www.sqlite.org/download.html
  80. http://www.sqlite.org/sqlite-amalgamation-3070701.zip
  81. http://zlib.net/
…

--cut: 
http://blog.ashodnakashian.com/2011/09/sqlite-with-built-in-online-compression/ 
--

-- 
FSF associate member #7257

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

Re: [sqlite] transparent compression implementations for SQLite?

2011-12-30 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/12/11 20:10, Ivan Shmakov wrote:
> The problem is that I have a table, each row holding something like 64
> bytes of data (of BLOB and INTEGER types), which don't seem too
> compressible, but these rows themselves tend to be similar to each
> other, so that the table could still be compressed as a whole, or in
> chunks.

SQLite stores integers in a variable length encoding using (almost) the
minimum bytes necessary.  Consequently keeping your numbers as small as
possible will help.

You don't describe what your blobs are, but again encoding them in the
shortest length possible will result in less data and redundancy making
compression less significant.

The page you linked to is a pretty good writeup on the subject.  You can
get good compression using CEROD but that doesn't allow making changes.
Other than that you'll need to put something together yourself.

You can compress your fields individually but then you'll only be able to
equality comparisons (not ordering or partial matches).  Using a
precomputed dictionary can give good results even on short fields.  Here
is an example for English(ish) text:

  https://github.com/antirez/smaz

Other than that you'll either need to work at the filesystem level or the
SQLite page level as the article describes.  Here is another compressor to
consider:

  http://blosc.pytables.org/trac

But I suggest you start at the beginning with an understanding of the
SQLite file format:

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

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

iEYEARECAAYFAk7+lnkACgkQmOOfHg372QRJXgCfUPPq2FE5i6Ve6TiZCa64kzU0
beIAoJMTw49+5Mo3AmNpAHC1qUpZpQKq
=6iDD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transparent compression implementations for SQLite?

2011-12-30 Thread Simon Slavin 

On 31 Dec 2011, at 4:10am, Ivan Shmakov wrote:

>   I wonder, if anyone has any experience with [1] (and where could
>   one get the vfs_compress.c file, BTW?), or any other transparent
>   compression implementations for SQLite?

Links to the code and a pre-build .dll are in the article, about 2/3rds down 
the page.

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

[sqlite] transparent compression implementations for SQLite?

2011-12-30 Thread Ivan Shmakov 
I wonder, if anyone has any experience with [1] (and where could
one get the vfs_compress.c file, BTW?), or any other transparent
compression implementations for SQLite?

The problem is that I have a table, each row holding something
like 64 bytes of data (of BLOB and INTEGER types), which don't
seem too compressible, but these rows themselves tend to be
similar to each other, so that the table could still be
compressed as a whole, or in chunks.

TIA.  And HNY.

[1] 
http://blog.ashodnakashian.com/2011/09/sqlite-with-built-in-online-compression/

-- 
FSF associate member #7257

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

Re: [sqlite] Table insert takes very long time, please help..

2011-12-30 Thread guiz 

Igor and Simon, thank you so much for your help. I followed your instructions
and made impressive improvements. As a beginner, I learn something new.
Thank's again, guys.

Cheers,
Rick
-- 
View this message in context: 
http://old.nabble.com/Table-insert-takes-very-long-time%2C-please-help..-tp33055026p33056783.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] Fw: A question on sqlite processing

2011-12-30 Thread Black, Michael (IS) 
I meant on here:



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



It could even be a note in the description of the amalgamation download.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Friday, December 30, 2011 4:56 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Fw: A question on sqlite processing

On Fri, Dec 30, 2011 at 5:50 PM, Black, Michael (IS)  wrote:

> So can we get a link on the download page for this?
>

There is a "Download" link on the lower menu bar.  (ref:
http://www.sqlite.org/src/artifact/d9be87f1c340285a3?ln)

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

Re: [sqlite] A question on sqlite processing

2011-12-30 Thread Simon Slavin 

On 30 Dec 2011, at 10:50pm, Black, Michael (IS) wrote:

> So can we get a link on the download page for this?  Otherwise how are we 
> supposed to find it?

Click the link right at the top that says 'file'.
Then click the 'd9be87f1c340285a' link.
Then copy-and-paste the code from that page.

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

Re: [sqlite] Fw: A question on sqlite processing

2011-12-30 Thread Richard Hipp 
On Fri, Dec 30, 2011 at 5:50 PM, Black, Michael (IS)  wrote:

> So can we get a link on the download page for this?
>

There is a "Download" link on the lower menu bar.  (ref:
http://www.sqlite.org/src/artifact/d9be87f1c340285a3?ln)

-- 
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] Fw: A question on sqlite processing

2011-12-30 Thread Black, Michael (IS) 
So can we get a link on the download page for this?  Otherwise how are we 
supposed to find it?



Something under "Source Code" so it's obvious?  With a little note for it that 
says "e.g. For Microsoft Visual Studio 64k source code limit on debugger".





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Friday, December 30, 2011 7:41 AM
To: General Discussion of SQLite Database
Cc: u okafor
Subject: EXT :Re: [sqlite] Fw: A question on sqlite processing

On Fri, Dec 30, 2011 at 8:17 AM, Black, Michael (IS)  wrote:

>
> Nobody has done a split on the source file yet to make it < 64k.
>

See http://www.sqlite.org/src/artifact/d9be87f1c340285a3?ln in the SQLite
source tree.  Been there since April of 2011.


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

Re: [sqlite] Table insert takes very long time, please help..

2011-12-30 Thread Igor Tandetnik 
Igor Tandetnik  wrote:
> Your statement is equivalent to this simpler one:
> 
> INSERT INTO a
> SELECT 'Item',
> x.Item,
> strftime('%Y-%m-%d',max(TheDate)),
> 
> strftime('%Y-%m-%d', max(TheDate),
>(select skippedweeks from c
> where Item = x.Item and Frequency = max(y.Frequency)) * 7 || ' days'),

This part could be simplified further (assuming skippedweeks is between 0 and 
1,000,000; adjust to taste):

strftime('%Y-%m-%d', max(TheDate),
(max(Frequency * 100 + skippedweeks) % 100 * 7) || ' days')

This way, index on c(Item, Frequency) is no longer necessary, one on c(Item) 
would suffice.
-- 
Igor Tandetnik

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

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

2011-12-30 Thread Simon Slavin 

On 30 Dec 2011, at 4:40pm, Jay A. Kreibich wrote:

> On Fri, Dec 30, 2011 at 04:10:55PM +0530, Durga D scratched on the wall:
>> Hi all,
>> 
>>I have sqlite table with 71 columns. not null for 70 and one is
>> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
>> columns and unique(70 columns). It's worked fine upto 30 columns not null
>> and unique(70 columns).
>> 
>>  I need 70 columns with unique and not null. How to enable it?
> 
>  Do you mean 70 columns that each have a single-column unique
>  constraint, or a single unique constraint that crosses 70 columns?

I think he means he has 70 columns, each of which have to be unique.  Which, of 
course, means he'll have 70 implicit indexes on that table.  It'll make 
inserting things slow, but I don't why any reason why SQLite shouldn't do it.  
On the other hand, I wouldn't want to make SQLite update 70 columns each time I 
INSERTed a row, and I'd probably rely on my application to do it instead of the 
DBMS.

Durga, here are the limits to the number of things you can have in SQLite:



No mention of a maximum number of indexes.  But frankly I'm suspicious of any 
table which has 70 columns.  You should be able to hold all the columns of a 
table in your head at once, and I lose my place long before 70 columns.

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

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

2011-12-30 Thread Jay A. Kreibich 
On Fri, Dec 30, 2011 at 04:10:55PM +0530, Durga D scratched on the wall:
> Hi all,
> 
> I have sqlite table with 71 columns. not null for 70 and one is
> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
> columns and unique(70 columns). It's worked fine upto 30 columns not null
> and unique(70 columns).
> 
>   I need 70 columns with unique and not null. How to enable it?

  Do you mean 70 columns that each have a single-column unique
  constraint, or a single unique constraint that crosses 70 columns?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table insert takes very long time, please help..

2011-12-30 Thread Igor Tandetnik 
Rick Guizawa  wrote:
> INSERT INTO a
> SELECT 'Item',
> x.Item,
> (SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
> MAX(TheDate) FROM b  WHERE Item = x.Item)),
> strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
> TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b  WHERE
> Item = x.Item,
> ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
> WHERE item = x.Item) AS REAL) * 100) , 4),
> y.frequency,
> (SELECT SUM(Frequency) from c WHERE item = x.Item)
> FROM b x, c y
> WHERE x.Item = y.Item AND
> y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
> BY x.Item;

Your statement is equivalent to this simpler one:

INSERT INTO a
SELECT 'Item',
x.Item,
strftime('%Y-%m-%d',max(TheDate)),

strftime('%Y-%m-%d', max(TheDate), 
(select skippedweeks from c
 where Item = x.Item and Frequency = max(y.Frequency)) * 7 || ' days'),

ROUND(CAST (max(frequency) AS REAL)/CAST (SUM(Frequency) AS REAL) * 100, 4),
max(frequency),
SUM(Frequency)
FROM b x, c y WHERE x.Item = y.Item
GROUP BY x.Item;

You want indexes on b(Item) and either c(Item) or c(Item, Frequency) (the 
latter in case you have lots of records in c with the same item but different 
frequencies; the former if you have just a few records for each Item).
-- 
Igor Tandetnik

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

Re: [sqlite] Table insert takes very long time, please help..

2011-12-30 Thread Simon Slavin 

On 30 Dec 2011, at 2:53pm, Rick Guizawa wrote:

> Using shell sqlite3.exe to process the insert took a long time to
> complete. Table b has about 5 rows and table c has about 8
> rows of data. How can I speed up my insert?

Run ANALYZE on your database:



Create indexes appropriate to your SELECT commands:



For instance, to speed up this part of your query:

SELECT MAX(Frequency) FROM c WHERE item = x.Item

Make this index:

CREATE INDEX cif ON c (Item,Frequency)

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

Re: [sqlite] Table insert takes very long time, please help..

2011-12-30 Thread Rick Guizawa 
edit: I need to mention that BEGIN and COMMIT were already included in
my insert.

On Fri, Dec 30, 2011 at 10:53 PM, Rick Guizawa  wrote:
> Hi all, I have the following tables:
>
> CREATE TABLE [a] (
> [ItemType] VARCHAR(10)  NOT NULL,
> [Item] VARCHAR(60)  NOT NULL,
> [LastDate] DATE  NOT NULL,
> [NextDate] DATE  NOT NULL,
> [Probability] FLOAT  NOT NULL,
> [Frequency] INTEGER  NOT NULL,
> [TotalFrequency] INTEGER  NOT NULL
> )
>
> CREATE TABLE [b] (
> [ID] INTEGER  NOT NULL,
> [TheDate] DATE  NOT NULL,
> [Item] VARCHAR(35)  NOT NULL
> )
>
>
> CREATE TABLE [c] (
> [ItemType] VARCHAR(10)  NOT NULL,
> [Item] VARCHAR(60)  NOT NULL,
> [SkippedWeeks] INTEGER  NOT NULL,
> [Frequency] INTEGER  NOT NULL
> )
>
>
> My insert is as follow:
>
> INSERT INTO a
> SELECT 'Item',
> x.Item,
> (SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
> MAX(TheDate) FROM b  WHERE Item = x.Item)),
> strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
> TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b  WHERE
> Item = x.Item,
> ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
> WHERE item = x.Item) AS REAL) * 100) , 4),
> y.frequency,
> (SELECT SUM(Frequency) from c WHERE item = x.Item)
> FROM b x, c y
> WHERE x.Item = y.Item AND
> y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
> BY x.Item;
>
> Using shell sqlite3.exe to process the insert took a long time to
> complete. Table b has about 5 rows and table c has about 8
> rows of data. How can I speed up my insert? Please, really appreciate
> any help on this. Thank you.
>
> Cheers,
> Ric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About Joining Multiple Tables

2011-12-30 Thread Simon Slavin 

On 30 Dec 2011, at 11:44am, Sushil Dudhalkar wrote:

> My query returns everything but the corresponding Supplier and customer 
> records. The query is as under. 

You are using double quotes here:

> where Transactions.DocID = "P-1001"

They should be single quotes:

where Transactions.DocID = 'P-1001'

Apart from that, I don't notice anything wrong.  Try doing a lookup which goes 
straight from the PO to the Suppliers table:

SELECT 
PO.Po_ID,
PO.Po_Date,
PO.QuoteNum,
PO.PayTerms,
PO.DeliveryDate,
PO.VAt,
PO.SrvcTax,
PO.Amount,
Suppliers.SName,
Suppliers.Address1,
Suppliers.Address2,
Suppliers.City,
FROM PO
LEFT JOIN Suppliers 
ON PO.SuppID = Suppliers.SID 

and see whether that works and does return the data that would correspond to 
the transaction P-1001.

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

[sqlite] Table insert takes very long time, please help..

2011-12-30 Thread Rick Guizawa 
Hi all, I have the following tables:

CREATE TABLE [a] (
[ItemType] VARCHAR(10)  NOT NULL,
[Item] VARCHAR(60)  NOT NULL,
[LastDate] DATE  NOT NULL,
[NextDate] DATE  NOT NULL,
[Probability] FLOAT  NOT NULL,
[Frequency] INTEGER  NOT NULL,
[TotalFrequency] INTEGER  NOT NULL
)

CREATE TABLE [b] (
[ID] INTEGER  NOT NULL,
[TheDate] DATE  NOT NULL,
[Item] VARCHAR(35)  NOT NULL
)


CREATE TABLE [c] (
[ItemType] VARCHAR(10)  NOT NULL,
[Item] VARCHAR(60)  NOT NULL,
[SkippedWeeks] INTEGER  NOT NULL,
[Frequency] INTEGER  NOT NULL
)


My insert is as follow:

INSERT INTO a
SELECT 'Item',
x.Item,
(SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
MAX(TheDate) FROM b  WHERE Item = x.Item)),
strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b  WHERE
Item = x.Item,
ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
WHERE item = x.Item) AS REAL) * 100) , 4),
y.frequency,
(SELECT SUM(Frequency) from c WHERE item = x.Item)
FROM b x, c y
WHERE x.Item = y.Item AND
y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
BY x.Item;

Using shell sqlite3.exe to process the insert took a long time to
complete. Table b has about 5 rows and table c has about 8
rows of data. How can I speed up my insert? Please, really appreciate
any help on this. Thank you.

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

Re: [sqlite] search

2011-12-30 Thread Aris Setyawan 
Hi Durga,

Another alternative, you can use an xml database. It will fix your
problem easily using xquery, like this:
doc('region')//country/title/text() -> it will show all region you have
doc('region')//village/title/text() -> it will show all village you have

You also can use selection too (where condition).
http://en.wikibooks.org/wiki/XQuery/XPath_examples
http://sedna.org

SQLite with fts is my favorite, but for tree like data structure I
will use xml database.

-aris

On 12/28/11, Durga D  wrote:
> Dear Michael.Black.
>
> It's correct.
>
>I need to design database to store file paths and their info like
> size. I have an idea
>   item(file or folder), level0(imm. parent), level1(grand parent)
> to level160(ancestor), type(file type or folder type).
>
>  primary key: (item, level0 to level160)
>
>  Is it correct approach? This is from server side. Need to store
> millions of records.
>
> Need optimum relationship between folders and files uniquely.
>
> for ex: c:/mydocs/home/a.doc
> c:/mydocs/office/agreement.doc
>
>   insertion of filepaths,deltion of file paths are enough. should be able
> to search by folder wise also.
>
>any ideas?
>
> Thanks in advance.
>
> On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) > wrote:
>
>> I don't know if FTS or a normal table will matter here but just normalize
>> the whole thing.
>>
>>
>>
>> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>>
>> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
>> faster.
>>
>>
>>
>> INSERT INTO virfts4 VALUES(1,'CO','country1');
>>
>> INSERT INTO virfts4 VALUES(1,'ST','state1');
>>
>> INSERT INTO virfts4 VALUES(1,'CI','city1');
>>
>> INSERT INTO virfts4 VALUES(1,'VI','village1');
>>
>> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>>
>>
>>
>> You can store as many levels as you want.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on behalf of Durga D [durga.d...@gmail.com]
>> Sent: Tuesday, December 27, 2011 4:27 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] search
>>
>> Thank you. I agree. It's correct.
>>
>> I already have data base with /country/state/city/village format. Is it
>> possible to do that while virtual table creation time?
>>
>> if yes, how?
>>
>> in case, if I have 250 levels like this ex: file system. how to do this.
>> any idea?
>>
>> Thanks in advance.
>>
>> On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:
>>
>> > 2011/12/27 Durga D :
>> > > select * from virfts4 where residence match '/*'; -- dint work
>> > > how to get counties names from this db by using query?
>> >
>> > Normalize database to 1NF, e.g.
>> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
>> > arrivtime, duration, imagelocation);
>> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
>> > 0730, 1500,'C');
>> >
>> > then use select:
>> > SELECT DISTINCT country FROM virfts4;
>> > --
>> > Kit
>> > ___
>> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fw: A question on sqlite processing

2011-12-30 Thread Richard Hipp 
On Fri, Dec 30, 2011 at 8:17 AM, Black, Michael (IS)  wrote:

>
> Nobody has done a split on the source file yet to make it < 64k.
>

See http://www.sqlite.org/src/artifact/d9be87f1c340285a3?ln in the SQLite
source tree.  Been there since April of 2011.


-- 
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] Fw: A question on sqlite processing

2011-12-30 Thread Black, Michael (IS) 
This was just answered in another chain.  Visual Studio has a 64k limit on 
source code files for the debugger (thanks Microsoft...a 16-bit 
limit?...really!).



If you need to step into it use WinDbg

http://msdn.microsoft.com/en-us/windows/hardware/gg463009



Nobody has done a split on the source file yet to make it < 64k.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of u okafor [uo07...@yahoo.com]
Sent: Thursday, December 29, 2011 3:24 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Fw: A question on sqlite processing

Dear sir,
I have built a project solution in Visual Studio environment (version 10) using 
a free
amalgamation source down from SQLite website and I am debugging. My first
question is - when I invoke sqlite3_complete() out of the shell.c source file, 
control
passes into the body of sqlite3PagerSetPageSize(Pager *pPager, ..) function, a
method defined in sqlite3.c file (rather than going to a function defined in 
sqlite3.c
file as sqlite3_complete()). please explain this phenomenon and the reason 
behind
it . . . it's a similar occurrence also with sqlite3_config.

It is consistent and obviously with a reason - only I don't know what that 
reason is,
please help

Yours truly,
uzo okafor


- Forwarded Message -
From: Richard Hipp 
To: u okafor 
Sent: Wednesday, December 28, 2011 8:45 PM
Subject: Re: A question on sqlite processing





On Wed, Dec 28, 2011 at 7:26 PM, u okafor  wrote:

Dear sir,
>I am a research student who is currently using freely downloaded software from 
>SQLite.
>I have a few questions and would appreciate if you can call me back @ (973) 
>332 0997
>My questions are so simple, I promise . .
>uzo okafor
>
>
I am on travel.  Questions to sqlite-users@sqlite.org will get answered quickly.



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

[sqlite] Fw: A question on sqlite processing

2011-12-30 Thread u okafor 
Dear sir,
I have built a project solution in Visual Studio environment (version 10) using 
a free
amalgamation source down from SQLite website and I am debugging. My first 
question is - when I invoke sqlite3_complete() out of the shell.c source file, 
control 
passes into the body of sqlite3PagerSetPageSize(Pager *pPager, ..) function, a
method defined in sqlite3.c file (rather than going to a function defined in 
sqlite3.c 
file as sqlite3_complete()). please explain this phenomenon and the reason 
behind
it . . . it's a similar occurrence also with sqlite3_config. 
 
It is consistent and obviously with a reason - only I don't know what that 
reason is,
please help
 
Yours truly,
uzo okafor 
 

- Forwarded Message -
From: Richard Hipp 
To: u okafor  
Sent: Wednesday, December 28, 2011 8:45 PM
Subject: Re: A question on sqlite processing





On Wed, Dec 28, 2011 at 7:26 PM, u okafor  wrote:

Dear sir,
>I am a research student who is currently using freely downloaded software from 
>SQLite.
>I have a few questions and would appreciate if you can call me back @ (973) 
>332 0997
>My questions are so simple, I promise . .
>uzo okafor
>
>
I am on travel.  Questions to sqlite-users@sqlite.org will get answered quickly.
 


-- 
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] Is it bug? or need to enable any pragma for not null and unique while creating table

2011-12-30 Thread Richard Hipp 
On Fri, Dec 30, 2011 at 5:40 AM, Durga D  wrote:

> Hi all,
>
>I have sqlite table with 71 columns. not null for 70 and one is
> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
> columns and unique(70 columns). It's worked fine upto 30 columns not null
> and unique(70 columns).
>

What makes you think SQLite 3.7.9 doesn't support more than 70 NOT NULL or
UNIQUE columns?  I just tried it with 100 NOT NULL and UNIQUE columns and
it works fine for me.



>
>  I need 70 columns with unique and not null. How to enable it?
>
> Thanks in advance,
> Durga.
> ___
> 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

[sqlite] About Joining Multiple Tables

2011-12-30 Thread Sushil Dudhalkar 

Hi! Need Help.

I have these 5 tables

1. PO (Purchase Order)
2. Transactions
3. Items
4. Suppliers
5. Customers

To print my purchase order i Need the Below data

Transactions.DocID,
Transactions.Quantity,
Transactions.Rate,
Items.ItemName,
Items.Unit,
PO.Po_ID,
PO.Po_Date,
PO.QuoteNum,
PO.PayTerms,
PO.DeliveryDate,
PO.VAt,
PO.SrvcTax,
PO.Amount,
Suppliers.SName,
Suppliers.Address1,
Suppliers.Address2,
Suppliers.City,
Customers.CName, 
Customers.Address1,
Customers.Address2,
Customers.City 

My query returns everything but the corresponding Supplier and customer 
records. The query is as under. 

SELECT 
Transactions.DocID,
Transactions.Quantity,
Transactions.Rate,
Items.ItemName,
Items.Unit,
PO.Po_ID,
PO.Po_Date,
PO.QuoteNum,
PO.PayTerms,
PO.DeliveryDate,
PO.VAt,
PO.SrvcTax,
PO.Amount,
Suppliers.SName,
Suppliers.Address1,
Suppliers.Address2,
Suppliers.City,
Customers.CName, 
Customers.Address1,
Customers.Address2,
Customers.City 
FROM Transactions INNER JOIN Items 
ON Transactions.ItemID = items.ItemID 
LEFT JOIN PO 
ON Transactions.DocID = PO.Po_ID 
LEFT JOIN Suppliers 
ON PO.SuppID = Suppliers.SID 
LEFT JOIN Customers 
ON PO.CustID = Customers.CustID 
where Transactions.DocID = "P-1001"

I am a newbie and would be obliged if anyone can guide me. Using SQLite3.

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

[sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

2011-12-30 Thread Durga D 
Hi all,

I have sqlite table with 71 columns. not null for 70 and one is
surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
columns and unique(70 columns). It's worked fine upto 30 columns not null
and unique(70 columns).

  I need 70 columns with unique and not null. How to enable it?

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