SQL help on formatting

[blueb]

I'm thinking of gathering 'stand-alone' procedures into a MariaDB table.
Much like the 'Templates' that PureBasic uses. (I would overload this system. I have over 5000 lines in this file already)

Problem is - how to do it so that I can find it in the DB and paste the procedure directly into the PureBasic IDE and preserve the layout...
possibilities...
a) - as TEXT (this won't keep the formatting)
b) - as BLOB (I've heard that you can 'drop' characters occasionally, e.g. 'special' characters)
c) Base64Encoder() / Base64Decoder()

I've read that you might try: Base64Encoder() first and save this string as TEXT into MariaDB
then use the Base64Decoder() command first and then paste this into the IDE.

Is any of this making sense?

[miso]

You don't have to put the procedure code into the database itself, you can put it outside as plain text. (in a zip or folder)
Your database would contain only the desired search parameters and flags, short description and a reference to the filename you can load.

Simplest on loading, you can load it directly into the clipboard as a big string, so you can ctrl+v or right click paste into the ide. Will keep formatting.

[mk-soft]

A) Text is in format UTF
- So no formatting should be lost
B) BLOB are bin data
- Would be bad if data were changed there

Limits: https://www.sqlite.org/limits.html

The PureBasic History saves the code in a blob

[spikey]

Personally I'd be thinking about SQLite for this, not a server based database, it's not an intensive application and you can backup/transport an SQLite database more easily than a server based one. (Unless of course you're planning a shared/networked snippets database in which case server based might be better.)

The snippets tool in Notepad++ does exactly this and white space is preserved properly internally without a need to transcode.

The whole point about a database is that it should faithfully store and represent the data put into it. If this doesn't happen then there's probably something wrong with the database configuration not the server. In the case of special characters I'd guess that the affected databases were configured to use ASCII as storage and someone tried to store a multi-byte data item in the table which would cause trouble.

If you're still untrusting PureBasic provides EscapeString() and UnescapeString() which will handle white-space.

[Nituvious]

Stored procedures is absolutely the way to go when designing a database application with user input.
TEXT and BLOB have performance issues when tables are very large and Base64 is just TEXT or BLOB anyway and doesn't achieve anything but add in two extra steps for each query.

With stored procedures you're sort of getting in a way prepared statements for free. I suggest downloading MySQL Workbench and playing around with them if you're unfamiliar, they're essentially SQL but with extra syntax with some peculiar details similar to GPUs for some reason.

[blueb]

This project is still in the early stages, so I'm free to explore all avenues.
Thanks for all insights.. it's appreciated.

Yes, SQLite is probably sufficient, as I've never used 'Stored Procedures' in MariaDB.

At this point, I'm still very open to alternate suggestions/methods.