Lazarus

Programming => Databases => Topic started by: lawman on January 25, 2020, 12:45:41 pm

Title: virtual db
Post by: lawman on January 25, 2020, 12:45:41 pm
is it possible to make a virtual db with sqlite or firebird?

by virtual db I mean, we open multiple db files and work with it as one db, so relationships can be managed between tables across the db files.

thanks
Title: Re: virtual db
Post by: johnmc on January 30, 2020, 12:05:36 pm
Not with SQLITE it is defined as
Quote
A complete database is stored in a single cross-platform disk file.

see: https://www.sqlite.org/features.html (https://www.sqlite.org/features.html)

This can be seen by looking at the TSQL???Connection/TSQLTransaction has only one database name property.
If you were really determined you could have 2 or more database files but the SQLquery would still only point to one or other transaction.

You may need to use mySQL/maria.

John
Title: Re: virtual db
Post by: rvk on January 30, 2020, 12:36:45 pm
is it possible to make a virtual db with sqlite or firebird?
by virtual db I mean, we open multiple db files and work with it as one db, so relationships can be managed between tables across the db files.
Why would you want to do this?

Most databases will keep all tables in one database-file because it can manage the transactions, integrity, etc.
In Firebird you could use external file but the problem is that an external file could not be available and the whole database integrity would go out of the window if there are relationships to each other.

http://www.firebirdfaq.org/faq16/
https://www.wisdomjobs.com/e-university/firebird-tutorial-210/using-external-files-as-tables-7629.html
Title: Re: virtual db
Post by: Zvoni on January 31, 2020, 08:29:27 am
In SQLite, why not just attach the "secondary" Database to the primary? Kind of like federated tables in MySQL.
IIRC, SQLite supports this even at runtime
Title: Re: virtual db
Post by: lawman on January 31, 2020, 06:07:13 pm
How would you do this?   

Possible with Firebird too?

Wanted to keep tables separate for
1.  User
2.  Common

So I can distribute both to each user.

When updating common tables that user won't change I can just send updated common dB to all.
Title: Re: virtual db
Post by: Zvoni on January 31, 2020, 07:48:12 pm
How would you do this?   

Possible with Firebird too?

Wanted to keep tables separate for
1.  User
2.  Common

So I can distribute both to each user.

When updating common tables that user won't change I can just send updated common dB to all.
Sounds like replication to me......
TinyPortal © 2005-2018