Best tool to handle SQLite code?
(1) By anonymous on 2021-08-28 11:42:26 [link] [source]
I've been working on a data enrichment project involving a growing dataset over a number of years. In doing so I've written a lot of queries that handle specific elements of data quality checks and enrichment, and I've relied heavily on Sqlitestudio as the front-end in which I do the coding.
Short of moving it all to a Python environment and turning the many code snippets into specific functions to call, is there a tool out there that enables SQL code to be easily categorised and managed?
I'm thinking some kind of repository where one can check code out from to use and then discard when done knowing the master is easily found and accessed via the repository.
All ideas/suggestions welcomed.
(2) By Ryan Smith (cuz) on 2021-08-28 13:02:45 in reply to 1 [source]
That's essentially software-versioning, since you seem to use "SQL" as software code, SV fits exactly with that need.
There's of course the GIT world - best for large numbers of contributors, or my personal choice SVN - best for speed and ease of use for smaller groups (using SQLite as the data store btw), or if you like compactness and great for large projects with fewer contributors, then Fossil (which hosts the SQLite project itself).
These are all pretty simple both to set up and use with insanely large amounts of community users out there, so help is never far away. Probably some of the best maintained pieces of software on the planet by nature of having programmers as their main clientèle. You can setup any of these on a local server, or somewhere on a cloud machine etc.
Quite the no-brainer. Or was there a reason I'm missing why SV won't satisfy your needs?
(3) By anonymous on 2021-08-28 14:01:20 in reply to 1 [link] [source]
If it's code that you simply want to organize and document, then an outliner like CherryTree may do the job. You might dedicate one outline node to each query or script. A node can contain embedded code-blocks, with syntax highlighting and editing. Outside of those blocks, you can document the code in ways that you could not effectively do via in-code comments. Nodes can be tagged, by criteria of your choice, for easy searching. The outline would let you organize your material in other ways that make sense to you. Everything is stored in a single SQLite file. To retrieve code, just copy and paste from within the outliner.
More likely, it's just the code (and perhaps related files), that you accumulate. If it does not change (much), then a folder tree may be enough. But you will likely want to augment this with some additional tools, as described below.
For backup, you may zip up the folder. Windows can even treat a zipfile as if it was a folder, for easy drag and drop. Or use 7zip, to browse, drag, and drop the zipfile.
If the files change a bit, over time, then .wim format may be better than .zip format. It archives the folder contents, like zipfiles do, but preserves older snapshots of the folder tree, too. To create/extend the archive, use WimLib. To find and retrieve older versions, I browse the .wim file using 7zip. Storage is extremely compact, because a file's content is stored only once, no matter how many snapshots it's in.
If it's code that regularly changes over time, and you need to keep a close eye on the changes within each file, then a Version Control system, such as Git, or the SQLite-based Fossil, may be a better bet. Fossil is vastly simpler, and includes its own note-taking, bug-tracking, and documentation system, if those are of interest to you.