SQLite database from Postgres

[David_Schlam]

I have been enjoying using an in-memory SQLite database which has amazing speed but also allows for some complex data searches that using an array or dictionary cannot really do.

Currently, the database is a remote Postgres database being accessed in a Xojo desktop app.

What I am thinking about is loading up a large database record from the Postgres database, keeping it in RAM using a SQLite database, and then recalling the data from RAM when needed to be displayed.

My desktop app has a Tab Panel with a number of tabs each with a lot of data to display. Currently, on Open, all the data is loaded (for every field in every tab panel). I was thinking of only loading what is displayed and then when you move from tab panel to tab panel, the fields displayed pull the data from the in-memory SQLite database.

Some of the data is accounting which is checked for accuracy when the record in loaded. I’m sure this would be faster running this in RAM as well.

Unfortunately, the postgres database records are inherited from a previous system designed to have maybe 200 columns for a record which I can’t change.

Is there some shortcut to create a duplicate table in SQLite? I notice that Navicat has a command to duplicate a table (“structure” or “structure and data”) so I am wondering if there is maybe an option that would save me some time and reduce the errors of manually creating the duplicate table in SQLite.

[Jean-Yves_Pochez]

I ended up making my own app to transfert data and structures between postgres and sqlite.

[Kem_Tekinay]

PostgreSQL has great introspection functions that you can leverage to craft a CREATE TABLE statement.

Example:

SELECT 
  column_name,
  data_type
FROM information_schema.columns
WHERE 
  table_schema = 'public'
  AND table_name = 'my_table'

That will give you the column definition that you can use to create your local table.

Note: Column types may not be a direct equivalent in SQLite, so you’ll have to do some conversion.

information_schema makes all kinds of great information available.

[David_Schlam]

Thanks. I will try that.

[David_Schlam]

This looks cool. Does it create SQL that I can copy and paste into Xojo? If so, do you offer licenses for the app?

[Beatrix_Willius]

Jean-Yves likes to tease us with this app.

[Jean-Yves_Pochez]

I would really like to see these features in xojo.
but as it is one of the last items of the xojo roadmap, I don’t expect it before 10 years …

[Jean-Yves_Pochez]

@David_Schlam

This looks cool. Does it create SQL that I can copy and paste into Xojo? If so, do you offer licenses for the app?

sorry only for my developments needs, not ready to be sold to the world at all…

[Jean-Yves_Pochez]

@David_Schlam

Does it create SQL that I can copy and paste into Xojo?

better than that, it creates direct xojo code that you can paste in a method to select your query.

Dim sqlcode as String
sqlcode = "SELECT LastName,FirstName,BirthDate,HireDate,Address"
sqlcode.AppendText " FROM Employee "
Dim rs as RecordSet = mDatabase.SQLSelect( sqlcode)

[Eric_Williams]

Here’s a great discussion that should help. The goal is to generate a CREATE TABLE command in Postgres that you can turn around and use in SQLLite:

How to generate the "create table" sql statement for an existing table in postgreSQL

[David_Schlam]

Thank you. I will definitely check that out.

[David_Schlam]

You are quite the tease.

[Joost_Rongen1 (Joost Rongen)]

I found a faster way buy just buying Navicat. Does it all, and probably more.

[David_Schlam]

I was looking for a way in Navicat. Where is that? I see - Structure Synchronization - but I am just looking for the SQL to create the columns.

[Maximilian_Tyrtania]

@David_Schlam

What I am thinking about is loading up a large database record from the Postgres database, keeping it in RAM using a SQLite database, and then recalling the data from RAM when needed to be displayed.

Not sure why you would want to set up a SQLite db just for that purpose. Can’t you just load the data into a dictionary and keep that around?

[Jean-Yves_Pochez]

Navicat doesn’t generate native xojo code AFAIK…
it also doesn’t generate xojo controlitems with the right table/field values you can paste in xojo IDE.
but I agree with you buying navicat is a faster way !

[Joost_Rongen1 (Joost Rongen)]

Hi @Jean-Yves_Pochez - I never have SQL in my Xojo code since it get’s hard to read and debug. Generating code seems to be handy, but it often ends in repeating the same code within your project and when it comes to maintenance you could have a lot of work.
But anyway, I have respect for you building your own solutions making you live as a developer easier.

[Joost_Rongen1 (Joost Rongen)]

@Maximilian_Tyrtania

Not sure why you would want to set up a SQLite db just for that purpose. Can’t you just load the data into a dictionary and keep that around?

This is my approach too, works great and fast.