3

I have made a custom report generator for our database (Oracle Berkeley DB engine).
Now it's time for me to add more flexibility and I am in dilemma. Do a partial or a fundamental redesign?

  1. Lets assume that I have plenty of time.
  2. I can only read the database, I don't have the right to modify it.

Inspired from Query Anything with SQLite article, I would like to let SQLite engine to do the dirty work (grouping, filtering, etc).

Have you tried it? Any examples? What about performance issues?

Shog9
  • 156,901
  • 35
  • 231
  • 235
Nick Dandoulakis
  • 42,588
  • 16
  • 104
  • 136
  • So you want to create a virtual sqlite table 'filled' with data from your Oracle Berkeley DB? Very ambitious! – tuinstoel Jun 03 '09 at 19:21
  • If I am not wrong, I don't have to fill tables just use *intermidiate* values. Actually thats why I ask before I try it. I am not sure how to tackle it :) – Nick Dandoulakis Jun 03 '09 at 19:27
  • The virtual table will act like a view on the Oracle Berkeley DB? Is that what you want to achieve? – tuinstoel Jun 03 '09 at 19:30
  • Not like a view. Somehow, SQLite will use my callbacks instead of the built-in *table walk* routines, while querying a virtual table. – Nick Dandoulakis Jun 03 '09 at 19:44

1 Answers1

1

It works fine for what I am using it :-) However I don't use it together with another database, just standalone. There's a list of Well-known Users of SQlite on their website.

You need to tell us more about your usecase to make any speculations about performance, but I'd rather make a POC and measure performance Long-held, incorrect programming assumptions

There is a nice quickstart article on the sqlite site.

Here's the C/C++ API Reference.

I assume you should be able to create a temporary SQLite table by initially querying your other DB and inserting the data into the temporary SQLite table. Then you can use different querys on that temporary table to do your grouping, filtering, etc.

Community
  • 1
  • 1
lothar
  • 19,853
  • 5
  • 45
  • 59
  • I'm familiar with SQLite3 :) In the database are stored raw data/tables. I want to fetch data through sqlite and queries. – Nick Dandoulakis Jun 03 '09 at 19:35
  • @tydoc OK your "Have you tried it?" made me think you don't know SQLite (yet) ;-) – lothar Jun 03 '09 at 19:56
  • "I assume you should be able to create...". I have thought that already, and it is exactly what I want to avoid :) It's a nice solution but I don't want to create extra tables because the database can have thousands of records, ie OrderLines. – Nick Dandoulakis Jun 03 '09 at 20:16
  • @tydok Maybe you need to clarify what you exactly want to do. If you issue a query to your db that e.g. returns 10000 rows, then you can query your other DB only once and store the result set in a temp SQLite table and then use further (SQLite) queries to sort/filter these results. And from your question I assumed that's exactly what you planned to do. – lothar Jun 03 '09 at 21:17