>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 >>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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
