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 ><jsow...@americansentry.net> 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
