Main Area and Open Discussion > General Software Discussion

SQL / SQLite for Beginners - Tools, Tips, Tricks, and Traps

<< < (2/2)

ital2:
I suppose home-made consumer databases have become very exotic, most users probably using Excel-for-everything, nowadays.

But some years ago, there had been some Excel add-on, allowing SQL selects in Excel. I bought, then never used it. SQL in some real-database frontend is so much more powerful, and it's fun to then dissect your non-normalized main table into more than one, "real-database" tables. Should I repeat it here, that the terms of user and of consumer are not synonyms, and that playing around with stuff, instead of playing around with GUIs that incredibly limit you, is so much more fun? Of course, as soon as there is some VBA scripting involved in your interaction with Excel, that tool can become very powerful, but for non-number-crunching, even then I don't see any advantage, compared with smart database use, and I'm positive about the fact that for getting brilliant results, some database interaction is so much easier than to try to get similar results out of Excel, without programming involved.

To be continued, with as much practical value as can be provided to consumerism-averse non-professional users, and even if their number obviously doesn't get into the millions, it's quite surprising nothing of this kind has been available up to now, so google should finally grasp its relevance, no?


EDIT June 13, 2017: Character sets / Code pages / Text formats/encodings
I finally did NOT need my rtf character mapping table (accented or other special character > \'xx, xx being the respective hex value) for characters like ò, ù, etc.: I found that some ANSI-UTF-8 conversions (and probably even back or in other formats, clipboard = UTF-16?) are done automatically, to and from clipboard and sometimes upon importing from .csv to .db - hope for the best but be aware of possible problems. In most use cases, the following - incomplete - observations will be irrelevant then:

My original text lists are identified by my editor as "Western European (Windows)"; this is ISO-1252 (which "is" ANSI, see the Wikipedia article on "Windows-1252" for further precision), but unfortunately, as a non-professional, you're never sure about your charsets since even automatic translations can occur (see below for an example): You hope for the best, but since it worked smoothly in my case, I didn't even pay attention; so, in order to at least half-way correctly describe the workflow here, I've gone back to the separate steps and have tried to identify the way of the encodings: paying attention to them, and in case making needed conversions manually, can probably help when things do not go smoothly.

First step: from my original text-editor-text to clipboard
(EDIT June 17: as you see, here, too, I forgot to check the initial step of export from db browser to clipboard, then writing to the initial text file; see correction below);

then in the rtf editor (I named it above but also said the developer doesn't hasten to eliminate bugs - don't do any sorting within that editor, you'd destroy your file (in the sense of mixing up the records' fields with the ones of other records)! -, so I don't want to name it again and again):

(1) "create new file from clipboard": Special characters are preserved, in the "File Properties" you can read "Text Encoding: UTF-8 without BOM" (BOM is the signature identifying the text encoding, "without BOM" is identical to "without signature"), so the program obviously (see below) has made an automatic conversion upon reading from the clipboard.

(2) If then you change the setting to "ANSI", the displayed text will not change, the special characters continue to be preserved, but the csv editor obviously does a second conversion back to ANSI (see below).

(3) Or you do, in the csv editor, "Import file - from text document", and here, in the dialog, the format is clearly identified as "ANSI"; here again, the special characters are correctly displayed in the resulting csv table, but the csv editor (if you use another one, all this may be totally different of course) has not made a conversion.

In the 3 cases, I saved the csv-editor file in the ".csv" format and reloaded those files back within my text editor: (2) and (3) were identified as "Western European (Windows)" again, as before (which obviously is ANSI), while (1) was now identified as "UTF-8 (without signature)", as it had been in the csv editor, so that csv editor clearly made those conversions (and not wrong guesses or such).

So from the results in the csv editor - of course, this is an additional step which you don't necessarily need in your use cases, but its result shows where we need to pay attention - we have two different start situation for further processing, UTF-8 (1) or ANSI (2) (in this case ISO-1252); in both formats, (West European) special characters are preserved (up to now; and this might not be the case for non-European special characters, especially with ANSI).

Next step, in DB Browser for SQLite/DB Browser 3 (see above): New file, then Create - Cancel (ie don't create table, but see above for the necessary autoincrement ID field which would to be created exactly here), then File - Import - Table from csv file (which isn't greyed-out NOW anymore; THEN table name "main"; this being done for both csv, UTF8 vs ANSI):
- UTF file import > Encoding is UTF-8
- ANSI file import > Encoding is ISO-8859-1 now, according to DB Browser, special characters are preserved (if you change to UTF-8 or "other", there are not, change to UTF-16 there is nothing left); this ISO-8859-1 is a subset of ISO-1252 (which is not offered as a choice in DB Browser; so in the former there are less characters available than in the latter, but it's sufficient for West European special characters).

Then "browse data" in DB Browser: You see the identification "UTF-8" in both cases, at the right-bottom corner of the screen. So an automatic ANSI-to-UTF-8 conversion will be made upon import of the ANSI-csv file by the SQLite frontend if your csv file is then still in ANSI format (see the automatic conversation in the csv editor above but which isn't made in every case: reading from clipboard yes, reading from csv file no (but which then can be made "manually" ie by choosing from a dialog)).

So, in your database frontend, you'll probably get UTF-8 encoding for the database, even from ANSI; at least that's the case in my workflow, when doing the import from an ANSI .csv file to .db format, in DB Browser; other frontends may not do this conversion automatically upon import, so you should check in case or try to get UTF-8 text files to start from.

Then, afterwards, any text processing you'll do is possibly done upon the ANSI format again, though, since when I do the the ^a - ^c in the database and put the clipboard content in my editor, it says "Windows (Western Europe)" again, with no further precision, and when I put the clipboard content in the csv editor, it says "UTF-8" again but we know from the above that the csv editor automatically translates the formats when they come from the clipboard.

Googling "clipboard content ansi or utf-8" brings some finds which seem to indicate that the operating system itself does automatic clipboard conversions (and even to UTF-16, seemingly not needed in Europe), which probably differ from one Windows version to the other, and with which applications you use have to cope with, making right guesses/conversations or wrong ones, so just let's say let's hope for the best but we should be aware of possible problems in different Windows / software / language regions setups; my setup above, with no cyrillic and with Windows XP and Word 2003 worked flawlessly.

As for RTF, the difficulty lies in the existence of the different versions over time and the fact that help available in the web doesn't necessarily apply to the version your target application understands; as said above, most rtf "editors" are really, really bad at this, and had I Word 2016 available instead of Word 2003, even more codes would be correctly understood. Also, it seems that many paragraph formatting codes don't work if you put them into the style definitions (in the header), so that you must repeat them within each concerned paragraph, and bear in mind that rtf is not a markup language like html for exemple, so \par ... \pard does NOT correspond to some <p> ... </p>, to bring the most prominent/easily misleading example.


EDIT June 16: Import from csv AND getting the id column right
As said above, a) "DB Browser for SQLite/SQLiteBrowser 3" is free AND allows for creating a table from a csv file; as said above, b) you would like to create a correct id column, replicating the rowid column, even if you do NOT judge it necessary to have it autoincremental (but which I advise, see above)*.

*: In fact, getting the "id" of the CURRENT record in a frontend grid is very useful but, without an ID column, often not possible. For example, if you are in edit mode (and in fact, the difficulty is not to get into edit mode, but then out of it again...), in SQLite "Expert", two fast "Home" key pressings (ie the second pressing fast following the first one) will bring you to the very first field of the current record, and then a ^c will bring you the record's ID into the clipboard, from where you can put that ID to numerous brilliant uses, for example for replicating the "default" data of the current record for creating a new one (while "not null, default value = x" will only bring you default values identical for the whole table, not for specific GROUPS of records, so there is a big interest in this even for this simple use case).

Now how to combine a) and b), since a) will create the table without any id, and then, it seems to be impossible to add that id column to the existing SQLite table?

Do a) in SQLite Browser since it comes that handy, name the table "main2" or something; even let alone the default column names, or rename them to the names needed later on, but leave the default column formats alone, it's not worth the effort.

THEN, in SQLite Browser or any other frontend of your choice (for example SQLite "Expert", as mentioned above), do SQL commands, don't bother with what your frontend would, or would not or not correctly, do for you, design and re-design-wise; of course, have stored the commands / column names in some text file for easy access, no need to type it all again and again.

Then, your sql command would be, in an example of mine:

create table main (id INTEGER not null primary key autoincrement, b TINYINT not null, c1 VARCHAR not null, c2 VARCHAR not null, c3 VARCHAR, c4 VARCHAR, fn VARCHAR, n VARCHAR, t VARCHAR);

(Edit June 17: here I had missed the default: b TINYINT not null default 3 or for strings: fieldname not null default 'somestring')

remember the table main2 already exists, too, holding the raw data; and then:

insert into main (b, c1, c2, c3, c4, fn, n, t) select b, c1, c2, c3, c4, fn, n, t from main2;

as said just before, you see here that any rename of the default column names in main2 is futile, for the "from" part, you can just use the default names as well, in their natural order, and for the first, the insert, part, you could even use your target column names in a different order than on screen if you pay attention to not mix columns up;

then delete table main2;

then in case, do some other formatting you will have not thought about up to then; this is probably the most simple way to create a correct table for csv/text data; from there on, you then can split up the data into other, linked tables; there again, you will first create the table, by your own sql command, then transfer data from the main table as needed, again from within the sql command line, not by - reliable or unreliable - frontend specifics.

Oh, and the sql "report" generators I spoke of above, are of course Crystal Report and SSRS. The former was quite ubiquitous in its time, even Microsoft had some OEM version of it, they now have SSRS while Crystal Report is now available from (and only for?) SAP. Anyway, my individually-programmable rtf export described above makes me wonder how many corporate users really use some (inflexible at least in direct comparison?) boxed tool, and how many would rather script what they really need, also in the light of the fact that sql alone does not do so much except for getting the raw data in some order, and then you'll need some macroing anyway, so the question is if this incredible effort put into report generators' GUIs, and which limits possible output variants anyway, is justified. As I see it, it's like sql as seen above: doing your own commands is more reliable, more flexible AND less complicated than wading thru those (limiting) GUI's steps.


EDIT June 17, 2017: NO clipboard export from SQLite "Expert" free version (but from SQLite Browser)
Above, I said that that program's free version allowed for selecting all or a "select ..." result, then copy to clipboard (^a, ^c) from which then I processed it further in order to export/print.

I now am unable to do so, but since I had installed a new version some days ago, 4.2.0.765, I re-installed 4.2.0.739 in order to check if the developer had taken away that ^a functionality, but in both versions it just selects the content of the current column. I would have sworn I had done it as described above, but now I don't simply remember anymore, probably I was in error. In fact, I did all the above for just ONE of my databases/tables yet, doing the export once, to an editor file, and there I must then have deleted all the "" to be found in combination with tabs (unneeded because of the tabs as field separators), and my clipboard processing always was done then from retrieving the content of that intermediate text file (programmatically).

So it seems that I did the export from a "select" result with SQLite Browser and then just forget about that; it works as described, but not from SQLite "Expert" Lite/"Personal". (While it's true that SQLite Browser offers export to csv format, of the database or of the current table, both exports just work for the table(s) in its/their unsorted/unfiltered format, not for "select" results, so they are useless.)

I discovered this error of mine just today when trying to export/print a second table; of course, the developer of SQLite "Expert" ("Personal") is not to blame if he wants to encourage users to buy his program; it's just that at this point in time, there are quite a lot of GUI bugs left thru the different versions (last version 3, then at least 2 for version 4) which are quite annoying, so for buying, now at 100$ plus VAT, one would like to have the (very pretty) GUI to function a little bit smoother before making the investment.

And another error of mine regarding this program: There is a setting "Show edit buttons" which shows edit buttons in every field, and clicking on them will open an edit pane just for that field - see my description above of such an edit pane for all fields combined though when I had missed this one. On the other hand, you'd need such an additional edit field for editing of fields with "long" content only, and the appearance of the unneeded buttons on ANY field is really disturbing, so it would be so much better to amend editing within the field itself for longer texts, or if the used grid doesn't allow for this, to have appear the edit field automatically (upon mouseclick/F2/field activation by arrow only, of course) whenever the text in the field is probably (!) too long in order to be edited smoothly in-line - that functionality would be possible in any such grid component, since it would just imply a "check by cursor home-end: addditional text displayed?" / "current text too long for this field if it was 1-line-only?", since the core problem isn't any additional text then entered, but to identify the insertion point for that additional text (or for text to be deleted).

EDIT some minutes later: I had just succeeded in selecting a "block" in SQLite "Expert" with the mouse (left button down), when the GUI was NOT in edit mode (in order to leave edit mode, I regularly have to change to another table, then go back - I said the GUI is ridden with bugs...), but my try to mouse-select the whole "select" result as a block (left top corner to bottom right one) was not successful, and even mouse-selecting a block then did not function anymore - so probably my initial export, some weeks ago, HAD been from SQLite "Expert" as I had initially thought. While the core seems to be stable, the SQLite "Expert" GUI is both the prettiest one on the market, and quite unbearable (I could list half a dozen other quirks of it).

ital2:
SQLite "Expert"

I had said that editing a value ("cell") was very cumbersome with that tool if the value ("cell content") is broad; I had said there wasn't even an additional edit pane sor the specific cell; than I had corrected, there was such an edit pane. Lately, I even made use of that edit pane, and I had to discover that the aleatoric, automatic word wrap in that edit pane was then, after "save [the edit]", replicated as hard returns within the cell content - so it's there, but it's worthless since it sort of destroys the text it's deemed to edit.

There are lots of updates, but they are offered in a way that you don't retain the previous versions, since it's "download and install?", then going directly into some temporary folder, not into your downloads folder, so if ever the developer wants to do away with some current functionality of the free (or even of the paid) version, that's the way he will probably do it. In plain English: Rather click "No", and download the latest versions manually then if you think you'll need them. Considering there are so many quirks which remain, over a multitude of these minor updates, I'd be interested in knowing WHAT is amended by the latter, I never see any difference.

Navicat

I had mentioned the around-30-p.c. price increase; some days later only, they announced new major versions (number 12 instead of number 11) but I don't see real development in areas where versions 11 fail; this being said without trialling anew, just speaking from their advertisement.

Correct line drawing

I mentioned the fact that in visual representations, the lines between FKs (targets) and their respective sources (PK) aren't drawn correctly - obviously because that would imply quite some more coding; Devart Studio for ... does not seem to be any better than the rest; the only db frontend I discovered so far and which seems to draw the lines correctly, is DBVisualizer (179$ plus VAT); if somebody knows some other competitor which does this right, please name it.

Remember Access? or: DB frontends limit your possibilities

I said above that it's easy to create some little sql commands which you then enter into the command line of the frontend, in order to get any info you want (if you also use sub-queries filtering the data before further questioning it), the only difficulty residing in too much typing, hence my advice to use some little group of macros, with some text input box for the search term(s).

It would be as easy to do after your splitting up your one table into several tables: The sql commands get a little bit more "complicated" (well, not really, and you will find all what you need in the web, neatly explained), and the result will be as neat as before: one single grid containing the results, in the frontend.

Where frontends totally fail is when you need to create new records or update existing ones, records spread over several tables (well, technically it's datasets split up into several records, of course): Here, you would have to write quite extensive "macros" yourself since you would not only need one inputbox and the "surrounding" sql commands, but you'd need a whole "FORM", all created from your macro tool, and then distributing the data into the form, for the part you want to see there, and then reading the data from the form you will have entered or changed there, and distributing it into the respective records in the respective tables: That would be lots of scripting, individually for any such little "application", like a ToDo set, for example (and not considering the need for a calendar and such things).

But IF you script all this, you will NOT need a db frontend anymore (and all the less so you'll want to have your macro/script enter the commands and the data by the command line of the frontend then), but you will only need a "grid", in order to display the data, since you'll do all the assembly work anyway. (NB: Some frontends may have some "API" which allows for entering commands / data programmatically instead of putting text into a command line, but they are overkill as a grid anyway, not presenting any added value, all to the contrary.)

In other words, Do-it-yourself with some db frontend is only possible as long as you limit yourself to one single table, as in a spreadsheet, and which is shockingly bad db behavior: You'll endlessly repeat the same data again and again (that's why in my descriptions above I have used 1-to-2-char codes instead of English words: in order to at least limit the typing chore), with all the craziness which comes with that - it's right that in a book list, such "category" repetition, over-and-over, may be acceptable, while in some ToDo database, it becomes VERY cumbersome to repeat all the multiple categories again and again, for every new ToDo; I've said it above, with a correct ID for each (1-table-only-) record, you can identify the current record and easily have a macro write a simili-clone of that current record, ie a clone with the probably-identical values (cell contents) pre-filled in the new record, in case ready to be overwritten manually by you, within the frontend grid ("in-line editing"), and with the fields left empty which you will (probably) want to fill out yourself, so you CAN simplify your typing work a bit IF you start, for every new record, from a quite similar one: So your macro 1 would be: Display the records of the kind of which I want to create a new one; and your macro 2 would be: Make a reasonable almost-clone of the current record (which the frontend automatically displays then); in-between, you'd probably do some arrow-up-or-down, and after macro 2, you'd do the necessary adjustments/additions.

But it becomes very evident, by this, that db frontends (just like Excel does) retain you within a flat-table db state which is not only incredibly limiting but which in practice doesn't present any advantage over a smart text editor like KEdit for example - which has come down to 99 plus VAT from 129 plus VAT, btw. Again, as soon as you have a grid and the necessary scripting know-how, and are willing to do the necessary work, I do NOT see in which way ANY db frontend would help you in ANY way, in their current state, and that's where that (otherwise awful) Access, from Microsoft, comes into play, since they say it's easy to get the necessary forms, necessary for multiple-table management* (remember: db frontends are only "good" for multiple-table-SELECTS (and when you enter all the necessary commands yourself anyway, so why not do that with a grid, too?).

* = "management" not being querying databases for data sets here, but creating new datasets and updating existing ones; as for the "management" in the sense of structure-altering commands, we've seen above that you're well advised to NOT rely on the frontend for such things either (Navicat and "Exper" mixing columns up, in different scenarios), but to write those "management" commands in sql yourself, too. Thus, if you permit the low pun, db frontends fall FLAT, in quite some ways.

ital2:
SQLite frontends:

SQLabs SQLite Manager is really unbearable in Windows. Above and in the Trial thread, I related my adventures with its previous (on my XP system), I now tried to trial its current version (on my W10 system). First of all, unchanged is the trial crippling: among others, no more than 20 (or was it 25?) records, not only for search results, but also for the data as such in the grid. Then, it crashed immediately. It seems to be a Mac program, originally, and aside from their crazy trial conditions which they inforce admirably well, they don't seem to be able or willing to treat Windows users the way we are accustomed to be treated: A trial should run for its expected duration, not crash within the very first minute that is.
.
They have got the prettiest site of all of the SQLite frontends if you ask me, and their grid isn't ugly either, thus my continuous interest in that program in spite of the crippled trial (and it seems to offer a lot for just 50 bucks, according to their site's explanations), but now I'm really finished with it. Mac developers who try to make some additional sells by translating their application to Windows should probably peer less on the money and more on Windows' specificities I'd guess in general; cf. Scrivener - I don't know Scrivener but by hearsay, but what interests me here and now about it is the fact that the Windows version seems to be programmed by completely different people (who should have access to the original source code after all), and that it has been considerably lacking behind the current Mac version, and that's it's to be feared this disdain for Windows users (or call it whatever you like to call it: what about "incompetence"?) will continue to be preserved.

SQLite "Expert" comes with almost-daily updates recently, so that's what you'd call a frenzy update rate if there is any. Unfortunately, the latest output I installed from this diligent developer crashed on my system immediately, so that's what you'd call an ever-extended beta, right? (Cannot prove it since the update file went straight to my temp folder, as described above, and CCleaner takes care of those. Btw, in the lastest installments of both W10 and FF, Click&Clean doesn't seem to work correctly (or anyway) anymore, not correctly triggering CCleaner either it seems when you want to have do it at least that, so I'm currently looking out for an alternative over there, too...), so I now have to re-install some ancient version which I took the precaution to manually save some weeks ago, in order to continue to use that pretty but currently a little bit sludgy frontend for the time being; alternatively, I could check if there's even a newest-new 3-digits-after-the-dottie update? (Well, DB Browser for SQLite's rock-solid whilst being really ugly...) Gee!


And for Regex, here in db output processing or in general, see my https://www.donationcoder.com/forum/index.php?topic=44120.0

ital2:
The following was meant as an Edit to my previous post, unfortunately I got, with multiple tries, "invalid attachment". It's a 5kb .png file called SQLiteExpert.png, from c:\, so it'd be of interest to know what in such a file could be "invalid" for this forum, perhaps screenshots cannot be inserted by edit, but only into new posts, since now, doing a new post for it, it seems to work?


EDIT July 25, 2017

Of course, some days later, there is a new version of SQLite "Expert", it's quite FIVE versions later than about 5 days ago. Probably, the immediate crash will not come now. But I've said it several times, the quite pretty GUI is really bad, functionally. So I think I should share a little screenshot in order to give an impression of what I mean, this is just an example of many (or let's say, several of) such quirks. As always, just a tiny one, so that the size demonstrates, too, that my illustrations are only for conveying my point in question issues. Here, we've got a record NOT numbered 72, but with a 4-digit number, but the whole record had been more or less readable indeed, not editing purposes though, but then, I had clicked on the plus sign above the records, in order to create a new one - which you see in the middle line, with the star in front of it, and you can see that it's perfectly unscrutable - so that's how the developer thinks people should enter the data into a new record, by "in-line" editing, ha ha ha (the multiple "(n" are default nulls):

Navigation

[0] Message Index

[*] Previous page