Re: [sqlite] Common index for multiple databases

[Keith Medcalf]

On Friday, 3 August, 2018 13:50, Warren Young  wrote:

>I’d be careful trying to apply your knowledge directly to SQLite.
>dBase comes out of the non-SQL world, so it’s going to have a
>different outlook in many areas.

>If the following is a fair description of how FoxPro for DOS indexes
>work, then I question how well your knowledge transfers to SQLite:

>   https://docs.microsoft.com/sql/odbc/microsoft/index-command

Not really.  This is documentation on the ODBC SQL interface.  What you really 
want is to refer to "native" xBase documentation:

http://www.dbase.com/help/Xbase/IDH_XBASE_INDEX.htm

>Compare:

>https://sqlite.org/lang_createindex.html

>The Venn diagram of these two documentation pages seems to have a
>pretty small region of overlap.

The overlap is actually 100% with only a few xBase specific differences due to 
the primitive nature of the bitty-boxen on which it was developed.

The only real differences are:
  in the definition of "UNIQUE" which in the xBase world does not mean 
"UNIQUE", the xBase equivalent is "DISTINCT"  
  xBase indexes are a single text field only of fixed length

This is so annoying that I had pre-processor macros that allowed you to define 
indexes using the SQL "CREATE INDEX" syntax and translated that into the 
expression format required by xBase in order to ensure that the primitive text 
indexes were generated coherently (which is much easier to do if you automate 
the process) and also to make the LOOKUP and SEEK functions work correctly ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Common index for multiple databases

[Simon Slavin]

On 3 Aug 2018, at 7:52pm, John R. Sowden  wrote:

> My concern in using Sqlite is since the index is embedded into the database 
> file with various tables, if I am running multiple Sqlite database files, how 
> do I use a common index for the different database files.

Okay.  Thanks for that clarification.  From the above, the thing you're calling 
"index" is not the things we're calling "index" and this has led to a lot of 
confusion.

Can you please post an example of "a common index for the different database 
files", or point us to an example or some documentation on it ?  It's not a 
concept we're used to.

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

Re: [sqlite] Common index for multiple databases

[Warren Young]

On Aug 3, 2018, at 12:52 PM, John R. Sowden  wrote:
> 
> I have the xbase type of databases down tight

I’d be careful trying to apply your knowledge directly to SQLite.  dBase comes 
out of the non-SQL world, so it’s going to have a different outlook in many 
areas.

> I understand what an index is.

If the following is a fair description of how FoxPro for DOS indexes work, then 
I question how well your knowledge transfers to SQLite:

   https://docs.microsoft.com/sql/odbc/microsoft/index-command

Compare:

https://sqlite.org/lang_createindex.html

The Venn diagram of these two documentation pages seems to have a pretty small 
region of overlap.

> how do I use a common index for the different database files.

It makes no sense to talk about using an index across multiple SQLite DB files. 
 The index data structure in SQLite has page offsets within that file, which is 
how SQLite uses the index to look up the data the index entry refers to.  The 
same page offset in a different file will contain different data; the other 
file might not even *have* such a page number!

A book’s index cannot be used to look up information in other books for the 
same reason.  If a book’s index says the information you want is on page 42, it 
is not on page 42 in all books, only in *that* book.

> I won't provide technicians with accounts receivable databases, etc.

SQLite will let you put as many tables in a DB file as you want, from one table 
per DB file to all tables in one DB file, or anything in between.

A SQLite-based application can attach to multiple database files using a single 
connection, so that the application that has legitimate need of accounts data 
can attach to that DB file, while other applications can attach to the DB 
file(s) it needs.

https://www.sqlite.org/lang_attach.html

> My thinking is along the line of all mission critical clocks take their 
> accuracy from the US Naval Observatory in Fort Collins, CO, instead of 
> thousands of free running clocks, each with what it thinks is the correct 
> time.

I don’t see how that analogy applies to SQLite.

I think my book analogy is a reasonable high-level approximation to the way 
SQLite indexes work, once created, at a static level.

Once you start modifying data, the analogy breaks down, but then you can start 
to think about how a computerized book indexing program would work, and the 
analogy continues to function.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Keith Medcalf]

Not exactly.  The index is stored in the SAME FILE that contains the table.  
For example in dBase I (or II or III) you might have the following files:

Customer.DBF
CustNo.NDX
CustName.NDX

where the two NDX files index fields from the Customer.DBF file.  In FoxPro you 
can have "compound" indexes so that the two indexes above are stored in one 
file, giving you (for example)

Customer.DBF
Customer.CDF

The indexes in the Customer CDF file are generated from and only can be used 
with the Customer.DBF table (and the indexes must be in-sync with the data in 
the Customer.DBF table).

SQLite3 "requires" that you store the indexes in the same file as contains the 
table data to ensure that they are all updated as a unit and remain consistent, 
so you might have a file called Customer.db

Customer.db

This has nothing to do with indexes on OTHER TABLES/FILES such as you might 
have a file called

Accounts.DBF
AcctCust.NDX
AcctInvN.NDX

in which the AcctCust is an index on the Customer field in the Accounts table 
(and has nothing whatsoever to do with the Customer DBF or Indexes) even though 
it mayhaps contains the same customer numbers as the CustNo.NDX so that you can 
associate the "Customer" record from the Customer DBF with the matching record 
from the "Accounts" record contained in the Accounts DBF.

These might all be stored in a file called

Accounts.db

If you do not "give out" a copy of the Accounts.db then that information cannot 
be accessed.

>index is.  My concern in using Sqlite is since the index is embedded
>into the database file with various tables, if I am running multiple
>Sqlite database files, how do I use a common index for the different
>database files.  I won't provide technicians with accounts receivable
>databases, etc.

You cannot use a "common index" for anything, anywhere, at any time, ever in 
the history of the Universe.  The index "belongs" to the thing it is indexing 
and contains information from only that thing and no other source.  You may 
have multiple tables containing customer numbers, and multiple indexes indexing 
each of those customer numbers for each table containing them, but you cannot 
have a "common index" which indexes two data tables.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden
>Sent: Friday, 3 August, 2018 12:52
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Common index for multiple databases
>
>I have the xbase type of databases down tight, having been using them
>since I bought my copy of dBASE II from George Tate of Ashton-Tate at
>a
>West Coast Computer Faire in 1981.  I have been writing applications
>for
>my alarm company, now through Foxpro 2.6 in DOS.  I understand what
>an
>index is.  My concern in using Sqlite is since the index is embedded
>into the database file with various tables, if I am running multiple
>Sqlite database files, how do I use a common index for the different
>database files.  I won't provide technicians with accounts receivable
>databases, etc.
>
>My thinking is along the line of all mission critical clocks take
>their
>accuracy from the US Naval Observatory in Fort Collins, CO, instead
>of
>thousands of free running clocks, each with what it thinks is the
>correct time.
>
>John
>
>On 08/03/2018 02:48 AM, Ling, Andy wrote:
>>> another point that I did not make clear. The accounting programs
>are not associated with the technical programs, different people,
>different security access. The tech databases and programs are in
>portable computers that go out in the field, but not the accounting,
>etc. There indexes would have to be updated when the computers are
>back at the office.
>> I have a feeling that what the OP is calling an index isn’t really.
>I think what he is talking about is the list of customer IDs.
>> So customer data can get updated “back at the office” and when the
>portable computers get back they need to be updated
>> with the changes.
>>
>> In sqlite terms, an index is a sorted list of the data in one or
>more columns of a table to help speed up access to the data in that
>table.
>> Once defined, the index is automatically updated by sqlite as the
>data in the table is changed.
>>
>> Perhaps we could get some clarification from the OP about what is
>actually wanted.
>>
>> Regards
>>
>> Andy Ling
>>
>>
>>
>>
>>
>>
>*
>*
>> DISCLAIMER:
>> Privileged and/or Confidentia

Re: [sqlite] Common index for multiple databases

[Richard Hipp]

On 8/3/18, John R. Sowden  wrote:
> I have the xbase type of databases down tight, having been using them
> since I bought my copy of dBASE II from George Tate of Ashton-Tate at a
> West Coast Computer Faire in 1981.

Are you using indexes to impose uniqueness constraints across multiple tables?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[John R. Sowden]

I have the xbase type of databases down tight, having been using them 
since I bought my copy of dBASE II from George Tate of Ashton-Tate at a 
West Coast Computer Faire in 1981.  I have been writing applications for 
my alarm company, now through Foxpro 2.6 in DOS.  I understand what an 
index is.  My concern in using Sqlite is since the index is embedded 
into the database file with various tables, if I am running multiple 
Sqlite database files, how do I use a common index for the different 
database files.  I won't provide technicians with accounts receivable 
databases, etc.


My thinking is along the line of all mission critical clocks take their 
accuracy from the US Naval Observatory in Fort Collins, CO, instead of 
thousands of free running clocks, each with what it thinks is the 
correct time.


John

On 08/03/2018 02:48 AM, Ling, Andy wrote:

another point that I did not make clear. The accounting programs are not 
associated with the technical programs, different people, different security 
access. The tech databases and programs are in portable computers that go out 
in the field, but not the accounting, etc. There indexes would have to be 
updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling





**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Common index for multiple databases

[Paul Sanderson]

On 2 August 2018 at 20:08, Keith Medcalf  wrote:

Further to this, you can "emulate" the current structure by creating
multiple databases each containing only the tables needed for that "bit" of
your application.  For example, you can create a customers.db containing
the customers table and all the indexes associated with the customers
table.  You can also create an accounting.db containing all the accounting
tables (but not the customers table) and all the indexes that belong to
those tables.  Lather, Rinse, Repeat.  Create multiple databases and do not
duplicate table names -- that is each table goes in one and only one
database).

You then "open" a :memory: database and "ATTACH" your other database hunks
as required for the application.  You refer to the tables only by table
name and provided that you have not duplicated tables in multiple database
files, then SQLite3 will operate on the appropriate attached database.
Since the indexes associated with a table must be stored in the same
database file as the data table itself, all the indexes will be kept
up-to-date.  You will not be able to have the database enforce referential
integrity across multiple "attached" databases, nor use cross "attachment"
triggers (but you cannot do that now anyway).

---

If you do, for some reason, choose this approach then by default SQLite
limits the number of attached databases to 10, you can adjust this up to a
max of 125. More info below:

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

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Ling, Andy]

> another point that I did not make clear. The accounting programs are not 
> associated with the technical programs, different people, different security 
> access. The tech databases and programs are in portable computers that go out 
> in the field, but not the accounting, etc. There indexes would have to be 
> updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling





**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Ling, Andy]

> another point that I did not make clear. The accounting programs are not 
> associated with the technical programs, different people, different security 
> access. The tech databases and programs are in portable computers that go out 
> in the field, but not the accounting, etc. There indexes would have to be 
> updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling


**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Keith Medcalf]

Well, ok, but it is not really creating an index drawing data from multiple 
tables.  The table definition part "interleaves" data with the same value for 
the same column into a "cluster" of pages, and the creation of the index on the 
cluster is an index of the "common column data".

Creating an index from "multiple databases" would be something like:

create table a(a ...);
create table b(b ...);
create index blah on multisources (a.a, b.b);

Since there is no syncronized motion between the two tables a and b, the index 
is impossible to maintain, except perhaps in a navigational database and then 
only through a linked to owner set, and only for changes to the right-hand 
table in the set.

You can only create an index drawing data from a single database object.  In 
oracle's case, that "single object" is a cluster of tables where "some one 
column" contains linkage data between the two tables.  That is, it is a hybrid 
of a relation and a network extended set index.  That is one table in the 
cluster contains a "linked list" of the other table rows having the same value 
for the cluster column (a LINK TO PRIOR LINK TO OWNER set) and the index is 
created on the column of the first table (the parent or left-hand table) in the 
cluster (set).  That the various rows of the various tables are stored in 
relative proximity to each other (in order to reduce I/O) is a nice 
implementation detail but irrelevant (and it depends on the insertion order of 
the table rows or requires a re-org to get the rows interleaved properly).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jean-Luc Hainaut
>Sent: Thursday, 2 August, 2018 15:04
>To: SQLite mailing list
>Subject: Re: [sqlite] Common index for multiple databases
>
>On 02/08/2018 20:50, Keith Medcalf wrote:
>> In no DBMS known can you index data sourced from multiple tables in
>the same index -- this applies to "Relational" databases and all
>other database models (such as pure hierarchical, network, network
>extended, etc.)  In all DBMS systems the contents of the index must
>be sourced from a single object.
>
>To the best of my (limited) knowledge, Oracle's CLUSTER is the only
>technique allowing an index to reference rows from several tables.
>The
>rows (from source tables) that share the same value of a column are
>collected into a page, if needed complemented by an overflow chain of
>pages. Whether the OP is ready to migrate to Oracle is another story!
>
>J-L Hainaut
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Common index for multiple databases

[Jean-Luc Hainaut]

On 02/08/2018 20:50, Keith Medcalf wrote:

In no DBMS known can you index data sourced from multiple tables in the same index -- 
this applies to "Relational" databases and all other database models (such as 
pure hierarchical, network, network extended, etc.)  In all DBMS systems the contents of 
the index must be sourced from a single object.


To the best of my (limited) knowledge, Oracle's CLUSTER is the only 
technique allowing an index to reference rows from several tables. The 
rows (from source tables) that share the same value of a column are 
collected into a page, if needed complemented by an overflow chain of 
pages. Whether the OP is ready to migrate to Oracle is another story!


J-L Hainaut
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Simon Slavin]

On 2 Aug 2018, at 7:44pm, John R. Sowden  wrote:

> another point that I did not make clear.  The accounting programs are not 
> associated with the technical programs, different people, different security 
> access.  The tech databases and programs are in portable computers that go 
> out in the field, but not the accounting, etc.  There indexes would have to 
> be updated when the computers are back at the office.

That's not how SQLite works.  It might help if you forget how FoxPro works and 
start again.

In SQlite, a database file can hold one or more tables.  If the database file 
holds a table, all the indexes of that table are stored in the same file.  When 
the table is updated, all its indexes are updated immediately, as part of the 
same operation.  It's not something that you have to worry about.  It happens 
automatically.

If you copy a databbase file from one computer to another, since the indexes 
are in the same file as the table, the indexes go with it.

If you write an application that uses a number of tables, it's normal to put 
all those tables in the same database file.  Copy one file, you have your 
entire database.  Back that one file up, you've backed up your entire database.

However, you can have one application access multiple database files at once if 
you need to.  But if you have two tables in different files you cannot use SQL 
abilities like FOREIGN KEYs to ensure referential integrity.  So most people 
don't do that.

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

Re: [sqlite] Common index for multiple databases

[Keith Medcalf]

>SQLite3 stores the table (.DBF) and all the indexes associated with
>that table in a single file called a database.  You can also have
>multiple tables in one database (rather than one table per file) and
>all the indexes associates with all those tables are stored in the
>same database file.  You cannot split the table and it's indexes into
>multiple files.  You can have one table and its indexes stored in a
>single file and have a crapload of those files, however, that defeats
>the entire purpose of having a Relational Database (ACID) and also
>precludes referential integrity enforcement and triggers.

Further to this, you can "emulate" the current structure by creating multiple 
databases each containing only the tables needed for that "bit" of your 
application.  For example, you can create a customers.db containing the 
customers table and all the indexes associated with the customers table.  You 
can also create an accounting.db containing all the accounting tables (but not 
the customers table) and all the indexes that belong to those tables.  Lather, 
Rinse, Repeat.  Create multiple databases and do not duplicate table names -- 
that is each table goes in one and only one database).

You then "open" a :memory: database and "ATTACH" your other database hunks as 
required for the application.  You refer to the tables only by table name and 
provided that you have not duplicated tables in multiple database files, then 
SQLite3 will operate on the appropriate attached database.  Since the indexes 
associated with a table must be stored in the same database file as the data 
table itself, all the indexes will be kept up-to-date.  You will not be able to 
have the database enforce referential integrity across multiple "attached" 
databases, nor use cross "attachment" triggers (but you cannot do that now 
anyway).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Thursday, 2 August, 2018 12:51
>To: SQLite mailing list
>Subject: Re: [sqlite] Common index for multiple databases
>
>
>You misunderstand how dBase databases work.  An index is created on a
>table (.DBF file) and stored in an index file (.NDX).  You can have
>multiple indexes associated with a single .DBF file (which means
>multiple .NDX files).  FoxPro has a non-standard index format that
>permits the multiple indexes associated with one database (.DBF) file
>to be stored in a single index file (.CDX).  In  no case can an index
>reference data that is not stored in the associated database.
>
>SQLite3 stores the table (.DBF) and all the indexes associated with
>that table in a single file called a database.  You can also have
>multiple tables in one database (rather than one table per file) and
>all the indexes associates with all those tables are stored in the
>same database file.  You cannot split the table and it's indexes into
>multiple files.  You can have one table and its indexes stored in a
>single file and have a crapload of those files, however, that defeats
>the entire purpose of having a Relational Database (ACID) and also
>precludes referential integrity enforcement and triggers.
>
>In no DBMS known can you index data sourced from multiple tables in
>the same index -- this applies to "Relational" databases and all
>other database models (such as pure hierarchical, network, network
>extended, etc.)  In all DBMS systems the contents of the index must
>be sourced from a single object.
>
>Of course, dBase-style databases (of which FoxPro is but one
>instance) are "navigational databases", not set-based relational
>databases, so if you are expecting to use SQLite3 as a "navigational"
>database you will likely run into other issues resulting from your
>attempts to "navigate" a "set-based" datastore.
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden
>>Sent: Thursday, 2 August, 2018 12:27
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: Re: [sqlite] Common index for multiple databases
>>
>>I made a mistake.  I should have said table, not database.  My
>>concern
>>is if I have 4 databases each with tables associated with a
>>particular
>>use, like accounting, technical, etc., which may reside on different
>>computers, how do I keep the index in each database file current.  I
>&g

Re: [sqlite] Common index for multiple databases

[Keith Medcalf]

You misunderstand how dBase databases work.  An index is created on a table 
(.DBF file) and stored in an index file (.NDX).  You can have multiple indexes 
associated with a single .DBF file (which means multiple .NDX files).  FoxPro 
has a non-standard index format that permits the multiple indexes associated 
with one database (.DBF) file to be stored in a single index file (.CDX).  In  
no case can an index reference data that is not stored in the associated 
database.

SQLite3 stores the table (.DBF) and all the indexes associated with that table 
in a single file called a database.  You can also have multiple tables in one 
database (rather than one table per file) and all the indexes associates with 
all those tables are stored in the same database file.  You cannot split the 
table and it's indexes into multiple files.  You can have one table and its 
indexes stored in a single file and have a crapload of those files, however, 
that defeats the entire purpose of having a Relational Database (ACID) and also 
precludes referential integrity enforcement and triggers.

In no DBMS known can you index data sourced from multiple tables in the same 
index -- this applies to "Relational" databases and all other database models 
(such as pure hierarchical, network, network extended, etc.)  In all DBMS 
systems the contents of the index must be sourced from a single object.

Of course, dBase-style databases (of which FoxPro is but one instance) are 
"navigational databases", not set-based relational databases, so if you are 
expecting to use SQLite3 as a "navigational" database you will likely run into 
other issues resulting from your attempts to "navigate" a "set-based" datastore.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden
>Sent: Thursday, 2 August, 2018 12:27
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Common index for multiple databases
>
>I made a mistake.  I should have said table, not database.  My
>concern
>is if I have 4 databases each with tables associated with a
>particular
>use, like accounting, technical, etc., which may reside on different
>computers, how do I keep the index in each database file current.  I
>assume that I have an external database with the account number
>field,
>and its index that each database connects to to "refresh" its account
>number index from the external index.  Otherwise if the table with
>the
>accounting index is modified, the tech table and its index would have
>to
>communicate with the master in order to stay current.
>
>I do this now because I have 1 account number index and the various
>foxpro databases (tables) all open that one index when each is used.
>
>John
>
>
>On 08/02/2018 10:31 AM, Simon Slavin wrote:
>> On 2 Aug 2018, at 6:11pm, John R. Sowden
> wrote:
>>
>>> I do not want these databases to all reside in one sqlite file.
>How do I index each database on this customer account number when
>each database and associated index are in separate files?  Is this
>what seems to be referred to as an external file?  I assume that I
>would have to reindex each database each time it is opened, since a
>record could have been edited, etc.
>> You have been misinformed.  In SQLite,
>>
>> A) each table is stored one database file
>> B) each index indexes just one table
>> C) all indexes for a table are stored in the same file as that
>table.
>>
>> An index is updated when its table is updated.  You never need to
>manually reindex unless you changed the table structure or index
>structure.
>>
>> It is normal to keep all tables related to one application in one
>big database file.  So, for example, if you run a library you would
>normally keep tables and indexes for books, borrowers, and current
>loans all in one file.  And therefore all the indexes for those
>tables would be in that file too.  SQLite is designed to handle
>things this way, and does it very efficiently.
>>
>> However, it is possible to keep different tables in different
>database files.  So you might keep books (and all indexes on books)
>in one file, and borrowers and current loans (and all the indexes on
>those tables) in another file.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Common index for multiple databases

[Igor Korot]

Hi,


On Thu, Aug 2, 2018 at 1:44 PM, John R. Sowden
 wrote:
> another point that I did not make clear.  The accounting programs are not
> associated with the technical programs, different people, different security
> access.  The tech databases and programs are in portable computers that go
> out in the field, but not the accounting, etc.  There indexes would have to
> be updated when the computers are back at the office.

Then the solution by David applies.
You will have one database (centralized), which will be updated with
the changes to the local DBs
when they come back to the office.

Thank you.

>
> John
>
>
>
> On 08/02/2018 11:33 AM, Igor Korot wrote:
>>
>> Hi,
>>
>> On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
>>  wrote:
>>>
>>> I made a mistake.  I should have said table, not database.  My concern is
>>> if
>>> I have 4 databases each with tables associated with a particular use,
>>> like
>>> accounting, technical, etc., which may reside on different computers, how
>>> do
>>> I keep the index in each database file current.  I assume that I have an
>>> external database with the account number field, and its index that each
>>> database connects to to "refresh" its account number index from the
>>> external
>>> index.  Otherwise if the table with the accounting index is modified, the
>>> tech table and its index would have to communicate with the master in
>>> order
>>> to stay current.
>>
>> Why do you need 4 databases in the first place?
>> If you client is designed to access all 4 databases then all tables
>> should be in 1 DB file.
>>
>> Thank you.
>>
>>> I do this now because I have 1 account number index and the various
>>> foxpro
>>> databases (tables) all open that one index when each is used.
>>>
>>> John
>>>
>>>
>>> On 08/02/2018 10:31 AM, Simon Slavin wrote:

 On 2 Aug 2018, at 6:11pm, John R. Sowden 
 wrote:

> I do not want these databases to all reside in one sqlite file.  How do
> I
> index each database on this customer account number when each database
> and
> associated index are in separate files?  Is this what seems to be
> referred
> to as an external file?  I assume that I would have to reindex each
> database
> each time it is opened, since a record could have been edited, etc.

 You have been misinformed.  In SQLite,

 A) each table is stored one database file
 B) each index indexes just one table
 C) all indexes for a table are stored in the same file as that table.

 An index is updated when its table is updated.  You never need to
 manually
 reindex unless you changed the table structure or index structure.

 It is normal to keep all tables related to one application in one big
 database file.  So, for example, if you run a library you would normally
 keep tables and indexes for books, borrowers, and current loans all in
 one
 file.  And therefore all the indexes for those tables would be in that
 file
 too.  SQLite is designed to handle things this way, and does it very
 efficiently.

 However, it is possible to keep different tables in different database
 files.  So you might keep books (and all indexes on books) in one file,
 and
 borrowers and current loans (and all the indexes on those tables) in
 another
 file.

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

Re: [sqlite] Common index for multiple databases

[John R. Sowden]

another point that I did not make clear.  The accounting programs are 
not associated with the technical programs, different people, different 
security access.  The tech databases and programs are in portable 
computers that go out in the field, but not the accounting, etc.  There 
indexes would have to be updated when the computers are back at the office.


John


On 08/02/2018 11:33 AM, Igor Korot wrote:

Hi,

On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
 wrote:

I made a mistake.  I should have said table, not database.  My concern is if
I have 4 databases each with tables associated with a particular use, like
accounting, technical, etc., which may reside on different computers, how do
I keep the index in each database file current.  I assume that I have an
external database with the account number field, and its index that each
database connects to to "refresh" its account number index from the external
index.  Otherwise if the table with the accounting index is modified, the
tech table and its index would have to communicate with the master in order
to stay current.

Why do you need 4 databases in the first place?
If you client is designed to access all 4 databases then all tables
should be in 1 DB file.

Thank you.


I do this now because I have 1 account number index and the various foxpro
databases (tables) all open that one index when each is used.

John


On 08/02/2018 10:31 AM, Simon Slavin wrote:

On 2 Aug 2018, at 6:11pm, John R. Sowden 
wrote:


I do not want these databases to all reside in one sqlite file.  How do I
index each database on this customer account number when each database and
associated index are in separate files?  Is this what seems to be referred
to as an external file?  I assume that I would have to reindex each database
each time it is opened, since a record could have been edited, etc.

You have been misinformed.  In SQLite,

A) each table is stored one database file
B) each index indexes just one table
C) all indexes for a table are stored in the same file as that table.

An index is updated when its table is updated.  You never need to manually
reindex unless you changed the table structure or index structure.

It is normal to keep all tables related to one application in one big
database file.  So, for example, if you run a library you would normally
keep tables and indexes for books, borrowers, and current loans all in one
file.  And therefore all the indexes for those tables would be in that file
too.  SQLite is designed to handle things this way, and does it very
efficiently.

However, it is possible to keep different tables in different database
files.  So you might keep books (and all indexes on books) in one file, and
borrowers and current loans (and all the indexes on those tables) in another
file.

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


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

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


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

Re: [sqlite] Common index for multiple databases

[David Raymond]

The "index" is on what's actually in the "table". So the index on each 
database's tables are always up to date.

What you're saying is that each satellite database should have a complete, up 
to date copy of the customer ID "table". The only way to do that would be to 
have a centrally accessible database with that info. Each of the satellite 
databases would connect to and ATTACH that central database to read the 
overarching customer table while they make local edits to their own local 
tables.

The usual concepts of caching and such still apply, but yeah, you'd have 1 
central db with everything that's needed by all satellite db's, and have those 
satellites connect to it as needed.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of John R. Sowden
Sent: Thursday, August 02, 2018 2:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Common index for multiple databases

I made a mistake.  I should have said table, not database.  My concern 
is if I have 4 databases each with tables associated with a particular 
use, like accounting, technical, etc., which may reside on different 
computers, how do I keep the index in each database file current.  I 
assume that I have an external database with the account number field, 
and its index that each database connects to to "refresh" its account 
number index from the external index.  Otherwise if the table with the 
accounting index is modified, the tech table and its index would have to 
communicate with the master in order to stay current.

I do this now because I have 1 account number index and the various 
foxpro databases (tables) all open that one index when each is used.

John


On 08/02/2018 10:31 AM, Simon Slavin wrote:
> On 2 Aug 2018, at 6:11pm, John R. Sowden  wrote:
>
>> I do not want these databases to all reside in one sqlite file.  How do I 
>> index each database on this customer account number when each database and 
>> associated index are in separate files?  Is this what seems to be referred 
>> to as an external file?  I assume that I would have to reindex each database 
>> each time it is opened, since a record could have been edited, etc.
> You have been misinformed.  In SQLite,
>
> A) each table is stored one database file
> B) each index indexes just one table
> C) all indexes for a table are stored in the same file as that table.
>
> An index is updated when its table is updated.  You never need to manually 
> reindex unless you changed the table structure or index structure.
>
> It is normal to keep all tables related to one application in one big 
> database file.  So, for example, if you run a library you would normally keep 
> tables and indexes for books, borrowers, and current loans all in one file.  
> And therefore all the indexes for those tables would be in that file too.  
> SQLite is designed to handle things this way, and does it very efficiently.
>
> However, it is possible to keep different tables in different database files. 
>  So you might keep books (and all indexes on books) in one file, and 
> borrowers and current loans (and all the indexes on those tables) in another 
> file.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Common index for multiple databases

[Igor Korot]

Hi,

On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
 wrote:
> I made a mistake.  I should have said table, not database.  My concern is if
> I have 4 databases each with tables associated with a particular use, like
> accounting, technical, etc., which may reside on different computers, how do
> I keep the index in each database file current.  I assume that I have an
> external database with the account number field, and its index that each
> database connects to to "refresh" its account number index from the external
> index.  Otherwise if the table with the accounting index is modified, the
> tech table and its index would have to communicate with the master in order
> to stay current.

Why do you need 4 databases in the first place?
If you client is designed to access all 4 databases then all tables
should be in 1 DB file.

Thank you.

>
> I do this now because I have 1 account number index and the various foxpro
> databases (tables) all open that one index when each is used.
>
> John
>
>
> On 08/02/2018 10:31 AM, Simon Slavin wrote:
>>
>> On 2 Aug 2018, at 6:11pm, John R. Sowden 
>> wrote:
>>
>>> I do not want these databases to all reside in one sqlite file.  How do I
>>> index each database on this customer account number when each database and
>>> associated index are in separate files?  Is this what seems to be referred
>>> to as an external file?  I assume that I would have to reindex each database
>>> each time it is opened, since a record could have been edited, etc.
>>
>> You have been misinformed.  In SQLite,
>>
>> A) each table is stored one database file
>> B) each index indexes just one table
>> C) all indexes for a table are stored in the same file as that table.
>>
>> An index is updated when its table is updated.  You never need to manually
>> reindex unless you changed the table structure or index structure.
>>
>> It is normal to keep all tables related to one application in one big
>> database file.  So, for example, if you run a library you would normally
>> keep tables and indexes for books, borrowers, and current loans all in one
>> file.  And therefore all the indexes for those tables would be in that file
>> too.  SQLite is designed to handle things this way, and does it very
>> efficiently.
>>
>> However, it is possible to keep different tables in different database
>> files.  So you might keep books (and all indexes on books) in one file, and
>> borrowers and current loans (and all the indexes on those tables) in another
>> file.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[John R. Sowden]

I made a mistake.  I should have said table, not database.  My concern 
is if I have 4 databases each with tables associated with a particular 
use, like accounting, technical, etc., which may reside on different 
computers, how do I keep the index in each database file current.  I 
assume that I have an external database with the account number field, 
and its index that each database connects to to "refresh" its account 
number index from the external index.  Otherwise if the table with the 
accounting index is modified, the tech table and its index would have to 
communicate with the master in order to stay current.


I do this now because I have 1 account number index and the various 
foxpro databases (tables) all open that one index when each is used.


John


On 08/02/2018 10:31 AM, Simon Slavin wrote:

On 2 Aug 2018, at 6:11pm, John R. Sowden  wrote:


I do not want these databases to all reside in one sqlite file.  How do I index 
each database on this customer account number when each database and associated 
index are in separate files?  Is this what seems to be referred to as an 
external file?  I assume that I would have to reindex each database each time 
it is opened, since a record could have been edited, etc.

You have been misinformed.  In SQLite,

A) each table is stored one database file
B) each index indexes just one table
C) all indexes for a table are stored in the same file as that table.

An index is updated when its table is updated.  You never need to manually 
reindex unless you changed the table structure or index structure.

It is normal to keep all tables related to one application in one big database 
file.  So, for example, if you run a library you would normally keep tables and 
indexes for books, borrowers, and current loans all in one file.  And therefore 
all the indexes for those tables would be in that file too.  SQLite is designed 
to handle things this way, and does it very efficiently.

However, it is possible to keep different tables in different database files.  
So you might keep books (and all indexes on books) in one file, and borrowers 
and current loans (and all the indexes on those tables) in another file.

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


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

Re: [sqlite] Common index for multiple databases

[Simon Slavin]

On 2 Aug 2018, at 6:11pm, John R. Sowden  wrote:

> I do not want these databases to all reside in one sqlite file.  How do I 
> index each database on this customer account number when each database and 
> associated index are in separate files?  Is this what seems to be referred to 
> as an external file?  I assume that I would have to reindex each database 
> each time it is opened, since a record could have been edited, etc.

You have been misinformed.  In SQLite,

A) each table is stored one database file
B) each index indexes just one table
C) all indexes for a table are stored in the same file as that table.

An index is updated when its table is updated.  You never need to manually 
reindex unless you changed the table structure or index structure.

It is normal to keep all tables related to one application in one big database 
file.  So, for example, if you run a library you would normally keep tables and 
indexes for books, borrowers, and current loans all in one file.  And therefore 
all the indexes for those tables would be in that file too.  SQLite is designed 
to handle things this way, and does it very efficiently.

However, it is possible to keep different tables in different database files.  
So you might keep books (and all indexes on books) in one file, and borrowers 
and current loans (and all the indexes on those tables) in another file.

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

Re: [sqlite] Common index for multiple databases

[David Raymond]

Each SQLite file will be its own database with its own tables and indexes. So 
for each database you just define an index on the appropriate tables.

I'm not sure why you want multiple files, but it's fine. You can always have 
one master db from which you ATTACH the others as needed. You just can't have 1 
index that spans multiple files, or do foreign key checks between files, etc.

https://www.sqlite.org/lang_attach.html



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of John R. Sowden
Sent: Thursday, August 02, 2018 1:12 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Common index for multiple databases

I have been reviewing sqlite for a couple of years, but still use 
foxpro.  I have a question regarding an index issue.

Currently I have several types of databases (in foxpro, one per file) 
that all point to an index of a common field, a customer account 
number.  The databases are for accounting, technical, general info 
lookup, etc.  \

I do not want these databases to all reside in one sqlite file.  How do 
I index each database on this customer account number when each database 
and associated index are in separate files?  Is this what seems to be 
referred to as an external file?  I assume that I would have to reindex 
each database each time it is opened, since a record could have been 
edited, etc.

tia,

John



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

[sqlite] Common index for multiple databases

[John R. Sowden]

I have been reviewing sqlite for a couple of years, but still use 
foxpro.  I have a question regarding an index issue.


Currently I have several types of databases (in foxpro, one per file) 
that all point to an index of a common field, a customer account 
number.  The databases are for accounting, technical, general info 
lookup, etc.  \


I do not want these databases to all reside in one sqlite file.  How do 
I index each database on this customer account number when each database 
and associated index are in separate files?  Is this what seems to be 
referred to as an external file?  I assume that I would have to reindex 
each database each time it is opened, since a record could have been 
edited, etc.


tia,

John



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