I guess this would be the best section to ask this question.
This is going to be a web app on Xojo Cloud. The main tables are in PostgreSQL and for data values for a report a table is maintained that holds one record for each data point. When the report is run however there needs to be only one record representing all these data points (BKeeney Shorts). My idea was to keep a table of metadata regarding each report that logs table and field names, etc., and create a temporary table based on that which was then populated from apropriate SELECT statements. The problem I see would be multiple users running the same report (different data set) and having duplicate table names, although I suppose that could be worked around. But what if these temporary tables were created in the builtin SQLite? If this was desktop app the SQLite tables would be local to each user and no problem. But on a web setup what would happen? Would the SQLite tables be global to the site or local to each session?..meaning that each user would get their own SQLite temporary table. Thanks for any comments!
The scope of your in-memory SQLite database would depend entirely on where you instantiate it.
If you create it for each user session then are getting an instance of an entire SQLite database to process data that PostgreSQL should probably do for you.
Remember on a local machine like one in Xojo Cloud you basically have one pool of memory. If PostgreSQL has to load all of your data into its memory in order to send it via TCP/IP locally to your Xojo Web App then the data is now being stored in memory on the same machine twice. Plus theres a performance penalty for moving data around on TCP/IP (even if marginal on a local machine it exists).
Why not just let PostgreSQL do whatever work you need done itself? Why copy anything to the web application at all? PostgreSQL is capable of temporary tables just the same. Plus PostgreSQL is multi-core aware so it could handle more of these concurrent queries (on a large enough virtual machine) then your Xojo app could (single core aware).
What you are saying is possible but seems over-engineered.
Thanks Phillip. Perhaps being new to Xojo I’m not envisioning properly how things work in this instance. If I have loaded my app to Xojo cloud (to make the program available to a particular customer of say 5-10 users) and each user opens a session from their web browser do they actually have local memory involved with the databases? Sorry for the ignorance here; I’m also new to doing this on the web. I assumed the browser was just a mirror of what was happening on the server and each session was actually created and manipulated on the server; the browser was just a “control panel” so to speak. I suppose PostgreSQL could just tag the functional report record with a session ID to prevent mixing and then I don’t worry about duplicate table names.
William - I’m really struggling to envision what you’re trying to accomplish.
If you have a table of rows that need to be summarized to one row for a report then let Postgres summarize it for you.
If a user makes a choice which means one row is “theirs”, and is used to build subsequent reports in a particular manner, then grab the unique row ID from Postgres and store that in a Session property. You can use that row ID in any subsequent queries to Postgres.
If you created an in-memory SQLite database for each session to cache data from Postgres, then yes, local memory would be involved. I think Phillip assumed in-memory based on one interpretation of what you might be trying to do.
If you created a disk based SQLite database and opened a connection to it in each session then very little local memory would be involved (just the memory for the connection), but you could have collisions. You couldn’t create a “myTable” for each and every user. There could only be one “myTable”.
I suppose you could create a separate SQLite database (unique name) on disk for each session. But now you’re just moving the memory amplification off to disk. Again, this should be solvable without maintaining local database. Work the solution in Postgres.
Thank you Daniel & Phillip. Things are starting to get a little clearer. — big edit here — I was going to explain more design but in thinking about this I see I need to rethink my approach. So thank you guys for making me think a little harder on the problem instead of one particular solution.