Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Ability to mix mode PERM and TEMP on different tables. #125

Open
neofuture opened this issue Feb 11, 2019 · 4 comments

Comments

Projects
None yet
2 participants
@neofuture
Copy link

commented Feb 11, 2019

The project I'm working on requires quite a lot or data and it would be ideal to model some tables as temporary and some as permanent, and also be able to merge/save from memory to perm.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Feb 11, 2019

Having mixed modes on the tables wouldn't be very difficult, but I'm not sure how the merge/save feature would work from memory to perm.

Maybe something like this?

// save selected rows to other table
// use "copyTo" instead of "exec"
nSQL("memoryTable").query("select").where([..]).copyTo("permTable").then(() => {
    // copy complete
})

But this would require that the permanent table and memory table are both created beforehand.

This gets tricky if we want to copy from one table with the same name to another table with the same name but on different adapters.

I think it would need to look something like this:

// copy from current "tempTable" location to new adapter using "tempTable" as table name.
nSQL("tempTable").query("clone", {
    id: "some-id",
    mode: "PERM"
}).exec().then(() => {
    // clone complete
});

In the above example, nanoSQL would have to connect to the PERM adapter, create a copy of the tempTable data model on the PERM adapter, copy the rows and indexes over, then disconnect from the PERM adapter to complete the query.

Might end up implementing both of these, they make sense for different use cases.

Let me know what you think.

@neofuture

This comment has been minimized.

Copy link
Author

commented Feb 12, 2019

Creating both tables on both adaptors and then copying would apply more to me.

Ill try and explain what im trying to achieve, the solution im working on loads a fair chunk of data into a json data store object, this is fast and inserting these records is considerably slow (1 minute for 19mb) which is inhibitive for the user, but creating them in a temporary table first then using a worker or timer to clone the table to a permanent table.

so what i want to try and achieve, is load the data once, work with it in memory, and or memory tables, commit these tables to permanent storage, and on next load grab them from permanent storage instead of hitting the server to request the data again, but reloading these tables into memory instead, and then committing any changes to both the object and the memory store.

I know i might sound like a lunatic, but I'm trying a concept for my next app, to make the data management a lot better and more efficient than using webSQL.

As you might be aware Safari are killing webSQL which makes development difficult for our apps, so looking for a better solution.

What you have created is amazing.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Feb 13, 2019

2.1.9 has been released with these among the new features:

  • Added new clone query type to copy tables from one adapter to another.
// clone users table into new adapter
nSQL("users").query("clone", {
    mode: new SyncStorage() // or any nanoSQL adapter
}).exec().then(() => {
    // clone table done
})
  • Added new copyTo query argument. Allows a query result to be streamed directly into another table.
// copy results of upsert
nSQL("posts").query("upsert", {...}).copyTo("postsLog").exec();

// get a section of rows from one table into another
nSQL("users").query("select")
.where(["status", "=", "banned"])
.copyTo("bannedUsers").exec();

// stream analytics
nSQL("orders").query("select", ["COUNT(*) AS totalOrders", "AVG(total) AS avgOrder"])
.where(["orderDate", ">", Date.now() - (24 * 60 * 60 * 1000)])
.copyTo("orderStats").exec();

I'm also working on a plugin that should help with this use case, I'll close this issue once the plugin is finished.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Feb 18, 2019

Oh I almost forgot, you can do this as well:

nSQL().connect({
    id: "my-db",
    mode: "PERM",
    tables: [
        {
            name: "some table",
            mode: "TEMP" // mode specific to this table, works with any adapter.
            model: {...}
        }
    ]
})

There is some metadata stored in the top level adapter about the tables, so make sure at least the top level mode is one that can persist data if any of the tables persist data.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.