Common index for multiple databases

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] On Behalf Of John R. Sowden
Sent: Thursday, August 02, 2018 1:12 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Simon Slavin-3
In reply to this post by John R. Sowden
On 2 Aug 2018, at 6:11pm, John R. Sowden <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Igor Korot
Hi,

On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
<[hidden email]> 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 <[hidden email]>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

David Raymond
In reply to this post by John R. Sowden
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:[hidden email]] On Behalf Of John R. Sowden
Sent: Thursday, August 02, 2018 2:27 PM
To: [hidden email]
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

John R. Sowden
In reply to this post by Igor Korot
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
> <[hidden email]> 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 <[hidden email]>
>>> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Igor Korot
Hi,


On Thu, Aug 2, 2018 at 1:44 PM, John R. Sowden
<[hidden email]> 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
>> <[hidden email]> 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 <[hidden email]>
>>>> 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
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Keith Medcalf
In reply to this post by John R. Sowden

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-
>[hidden email]] On Behalf Of John R. Sowden
>Sent: Thursday, 2 August, 2018 12:27
>To: [hidden email]
>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
><[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] 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-
>>[hidden email]] On Behalf Of John R. Sowden
>>Sent: Thursday, 2 August, 2018 12:27
>>To: [hidden email]
>>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
>><[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>>users
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Simon Slavin-3
In reply to this post by John R. Sowden
On 2 Aug 2018, at 7:44pm, John R. Sowden <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Jean-Luc Hainaut
In reply to this post by Keith Medcalf
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Ling, Andy
In reply to this post by Simon Slavin-3

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Common index for multiple databases

Hick Gunter
In reply to this post by John R. Sowden
This is what I think you are asking:

- You have a "main office" computer that holds the current information on "everything"
- You have several different categories of users (technicians, accountant, ...) that require different subsets of the data
- Each user has his own computer, that may be disconnected from the "main office", e.g. for "field work"
- When a user's computer is "attached" to the "main office", it needs to be "synchronized".

If this is correct, then you require either a "distributed" DBMS that handles synchronization by itself, or you need to do some programming both inside and outside  of SQLite.

This may be appropriate for you:

- As already stated, SQLite has just 1 file to hold all tables and indexes of the schema. Make this identical for all users. You can always leave the tables empty with just minimal overhead.
- Downloading from "office" to "user" is accomplished by using ATTACH to make the "user" and "office" databases accessible. Just run the appropriate INSERT ... INTO statements. Check the authorizer callback to allow different users to access only the tables/fields that they are allowed to see. Limiting the rows requires an appropriate WHERE clause.
- "Work" done by the user while offline needs to be saved in a worklog table.
- Uploading the "work" of a user would copy the new worklog records into the "office" worklog table, just another INSERT ... INTO, to be processed by a dedicated sync application.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von John R. Sowden
Gesendet: Donnerstag, 02. August 2018 19:12
An: [hidden email]
Betreff: [EXTERNAL] [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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Ling, Andy
In reply to this post by Simon Slavin-3

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Paul Sanderson
In reply to this post by Keith Medcalf
On 2 August 2018 at 20:08, Keith Medcalf <[hidden email]> 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 <https://www.amazon.co.uk/dp/ASIN/1980293074>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

John R. Sowden
In reply to this post by Ling, Andy
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Richard Hipp-3
On 8/3/18, John R. Sowden <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Common index for multiple databases

Keith Medcalf
In reply to this post by John R. Sowden

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-
>[hidden email]] On Behalf Of John R. Sowden
>Sent: Friday, 3 August, 2018 12:52
>To: [hidden email]
>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 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12