SQLite: how to open existing database in memory

[Glen Bojsza]

Thanks Pete.

I will play with this as it looks to be the way I want to go.

On Sat, Oct 17, 2015 at 2:56 PM, Peter Haworth <pete at lcsql.com> wrote:

> 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
> >
> _______________________________________________
> 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
>