1

I am looking for a functionality in which i can keep some data in the memory tables for frequent usage and the less frequent data in normal tables (on disk). This can be achieved in sqlite using the attach command, but problem is that if i have to query the two tables (in memory table and normal table) together, i have to do a union. This is a work around (i want to avoid union) but i want to know if sqlite has any built in mechanism for this purpose? Also, it would be great to know if sqlite is the best candidate for such kind of work in the embedded world?? Or are there any commercial embedded databases available that can work well in such cases. Looking forward for the answers.

Thanking you in anticipation Nadeem

Nadeem
  • 75
  • 2
  • 11

1 Answers1

1

You can create a temporary view along with the temporary and non-temporary table:

create table bla1(a int);
create temp table bla2(b int);
create temp view bla3 as select * from bla1 union select * from bla2;

Then you can select from both tables at once:

sqlite> insert into bla1 values (1);
sqlite> insert into bla2 values (2);
sqlite> select * from bla3;
1
2
  • Thanks for the reply, but view will end up doing the union behind the scenes. My application environment is very resource constrained, so i cannot afford doing heavy operations with the queries. – Nadeem Jun 27 '11 at 16:11
  • AFAICT, even if you had this native table with a mix of permanent and volatile records, the DBMS would still suffer the overhead of maintaining such tables. To me sqlite is the best DB system for embedded systems; are you aware that it is the most used DB system? (by most used I mean the one that is deployed the most in the field). –  Jun 28 '11 at 15:31
  • Yeah, i agree with what you are saying. – Nadeem Jun 28 '11 at 16:00