Common index for multiple databases

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

Re: Common index for multiple databases

Warren Young
On Aug 3, 2018, at 12:52 PM, John R. Sowden <[hidden email]> 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
[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 3 Aug 2018, at 7:52pm, John R. Sowden <[hidden email]> 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
[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 Warren Young
On Friday, 3 August, 2018 13:50, Warren Young <[hidden email]> 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
[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

Jim Callahan
In reply to this post by Hick Gunter
Back off from the index semantics for a second.

If Gunter Hick has captured at the application level of what you are trying
to do (remote databases),

I think the name of the concept we are looking for is: "Eventual
Consistency".
 https://en.wikipedia.org/wiki/Eventual_consistency

SQL databases (as opposed to Xbase) are built around the concept of ACID
transactions which
implies a centralized database where everything can be reconciled
immediately. The delayed processing
of "Eventual Consistency" is implemented at the application level; for
example there are applications written
for PostgreSQL to make complete replica databases "eventually consistent".
That would be overkill in
your case, but the concept that "eventual consistency" has to be
implemented in the application space
above the core SQL level.

So, at the application level, what you want to do is create subset tables
for a particular purpose.
Good news! SQL is fantastic at creating of subsets of rows and columns of
data.

In a new SQLite database ATTACH the main database and create a query that
describes the subset
of data you need and then wrap that query in a "CREATE TABLE AS query;"
statement. That with create a subset of the data (without indexes). Rebuild
the indexes you
need in the local table (do not attempt to copy indexes!).

The application logic needs to use the subset database to build a
time-stamped transaction
to run against the main database.

Your application needs a module that accepts all the remote time stamped
transactions
and queue them up to feed into the main database. You have to decide how
your
application should handle conflicting transactions (see the "eventual
consistency"
article).
https://en.wikipedia.org/wiki/Eventual_consistency

Then run your consistent application level transaction log against the main
database.

Don't worry about "copying" indexes.  As Dr. Hipp suggests, copying indexes
is a non-starter in the SQL world.
Just copy the data and rebuild your indexes on the subset data.  If you
want to assure you don't
create a duplicate customer number; copy the column of customer numbers to
a separate table;
reindex it and join it to your subset table.

So, in short, you can't copy indexes, but you can copy any subset of data
and re-index that subset.
"Eventual consistency" has to be handled at the application level above the
SQL core (which only
handles "ACID consistency").

HTH

Jim Callahan
Callahan Data Science LLC
Orlando, FL




On Fri, Aug 3, 2018 at 5:41 AM, Hick Gunter <[hidden email]> wrote:

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