| Multiverse Feedback: Cardlist | Visual spoiler | Export | Booster | Comments | Search | Recent activity |
| Mechanics | Upcoming releases | Skeleton |
|
CardName: SQLite virtual tables Cost: U Type: Pow/Tgh: / Rules Text: I think that implementing a protocol/API and a open source SQLite extension to access it (using virtual tables) may be helpful for a few purposes: - To make advanced searches using whatever criteria you want to do. (If you are making many queries, you may wish to download the part of the data first, and then do the query on the local copy.) - To export full or partial data (to make a mirror), including large ones. I suggesting including the timestamp of the last change as a column in each table, so that you can do incremental backups, and do not have to redownload everything each time you want to update your mirror. (You can only download public data, or if you are logged in, your own data too even if it is private.) - To be able to import data into Multiverse, including many things that cannot be imported using this import form. (This would only be allowed if you are logged in.) - To make statistics, such as minimum, maximum, average, etc. Flavour Text: Set/Rarity: Multiverse Feedback None
SQLite virtual tables
 I think that implementing a protocol/API and a open source SQLite extension to access it (using virtual tables) may be helpful for a few purposes:
- To make advanced searches using whatever criteria you want to do. (If you are making many queries, you may wish to download the part of the data first, and then do the query on the local copy.) - To export full or partial data (to make a mirror), including large ones. I suggesting including the timestamp of the last change as a column in each table, so that you can do incremental backups, and do not have to redownload everything each time you want to update your mirror. (You can only download public data, or if you are logged in, your own data too even if it is private.) - To be able to import data into Multiverse, including many things that cannot be imported using this import form. (This would only be allowed if you are logged in.) - To make statistics, such as minimum, maximum, average, etc. |
History: [-] Add your comments: |
Note that a SQLite virtual table does not send any SQL code to the server; all SQL code is parsed on the client side, and then a list of constraints (column numbers, comparisons (equal, less, greater, etc), value being compared with) are sent, and the implementation of the virtual table uses them to output a subset of the rows in that table, and then whichever constraints were not understood by the virtual table will be implemented by SQLite itself to further filter and/or sort the results.
Unfortunately, this "list of constraints" that can be used by virtual tables does not currently include a LIMIT clause (you can still put such a clause in your query, but the virtual table will not know about it; it will output all rows, and SQLite will then limit it to the specified number of rows).
I actually am exporting site data already to allow myself more comfortable searches - most commonly by mana cost, which is distinctly more powerful than "Frame".
Last changes kinda seem to be stored already considering what is tracked for "recent activity" and card comments.
The ability to directly access the data would make things easier and probably a lot faster.
So it's not quite the same thing, but do note that each cardset's data is available in JSON or XML formats via the "Export" option in the cardset nav box. The XML does indeed include the last modified date.
I'm potentially interested in allowing people to extract extra statistics or do more powerful filtering. I don't know anything about SQL virtual tables though.
If you can export as JSON, it should be easy to import that file into something like MongoDB and do all your complicated searches right there. Card files are likely unstructured data sets, which makes SQL not the best choice anyway