SQLite: how to open existing database in memory

[Peter Haworth]

I assume this is a single user application?  When you say you want to make
changes to the database, do you mean the user makes changes to the data in
the database using your application?  If the disk version never changes
then the next time the user runs your app, whatever changes they made last
time around will be gone - is that OK?

I'm not totally sure what you mean by "look into the database" but assume
you mean using anything other than your application.  There's always going
to be ways to do that using any of the sqlite admin programs out there,
unless you use one of the encryption add-ons for sqlite.

If you want to continue down this path, then conceptually, here's one way
to get the disk based db into an in memory version.

1.  Open the in memory database which will be completely empty.
2.  Use the ATTACH command to open the disk db over the same connection.
Doing that involves assigning a name to identify the db which I'll assume
is "diskdb".  You'll use that in the next step.
3. For each table in the disk db, issue a CREATE TABLE AS command, which
will look something like this:  "CREATE main.TABLE Movies AS SELECT * FROM
diskdb.Movies"
4. Issue the command DETACH DATABASE diskdb

After that all your data will be in your in memory database and you can
access it just as in any other db.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>

On Sat, Oct 17, 2015 at 10:12 AM, Glen Bojsza <gbojsza at gmail.com> wrote:

> Essentially, I want to keep the database in its original form / content.
>
> While the user is running the app I want to be able to make changes to the
> database knowing that the changes are done in memory and will not be
> written to the original database.
>
> I could duplicate the database to a folder on the disk and then later
> remove it but if I can do it just in memory and it automatically cleans out
> after the user quits the app then it would be more convenient.
>
> For example, if I don't the user to be able to look into the database
> (which I believe can be done if it is written to disk) using in memory
> would be the way to go...true?
>
> On Sat, Oct 17, 2015 at 12:55 PM, Peter Haworth <pete at lcsql.com> wrote:
>
> > No, no name is available because you have to use ":memory" as the name to
> > indicate it is an in-memory db.
> >
> > Maybe if you explain what it is you're trying to achieve, we can help
> more.
> >
> > Pete
> > lcSQL Software <http://www.lcsql.com>
> > Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
> > SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
> >
> > On Sat, Oct 17, 2015 at 9:42 AM, Glen Bojsza <gbojsza at gmail.com> wrote:
> >
> > > Thanks for the clarification...this changes the direction I need to
> take.
> > >
> > > In creating an in memory database can you create one with a name?
> > >
> > >
> > > On Sat, Oct 17, 2015 at 12:25 PM, Peter Haworth <pete at lcsql.com>
> wrote:
> > >
> > > > Sorry, I should have looked at your syntax more closely.
> > > >
> > > > You can't open an existing database as an in memory database.  If you
> > > want
> > > > to open an in memory database, the revopendatabase syntax is
> > > > revOpenDatabase("sqlite",":memory:") and it will be a brand new,
> empty
> > > > database with no tables.  The syntax you used would have created a
> disk
> > > > database file named /Users/Dev/Documents/Ratings.db3:memory: with no
> > > tables
> > > > in it, hence the error message you got.
> > > >
> > > > Hope that explains it better.
> > > >
> > > >
> > > >
> > > > Pete
> > > > lcSQL Software <http://www.lcsql.com>
> > > > Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html>
> and
> > > > SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
> > > >
> > > >
> > > _______________________________________________
> > > use-livecode mailing list
> > > use-livecode at lists.runrev.com
> > > Please visit this url to subscribe, unsubscribe and manage your
> > > subscription preferences:
> > > http://lists.runrev.com/mailman/listinfo/use-livecode
> > >
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>