Here: The "like" query for strings as a real alternative for not-too-extensive databases
classic sql selection:
select * from tablename where "x" = 'y' -- display all records where column x has got the value y
"like" syntax:
select * from tablename where "x" like '%y%' -- ditto for the string y anywhere within the value in column x
It's known that simple databases - we're not speaking of full-text search here, which is available in SQLite - are not perfectly suited for text search, the "like" clause is said very slow. This certainly is true for big databases, but for thousands of records on an old xp pc, even "like" will bring the results in less than a second, so it's perfectly adequate.
To give an example, my list of CDs:
ID (SQLite ID number)
Code/Category
Range (bear in mind CDs are physical stuff, so they only do have one place if not copied, no physical CD in more than one physical range, so the "where to find" info is not redundant even though most of the time (only), Category and Range are identical; in pop, categories are by countries and then in case decenniums; in jazz, Category is the instrument of the main artist),
Artist (can be several, for jazz in particular; it's just my classical music CDs that I have got in a different list, the main difference being the fact that the composer column is the main field over there, where pop, jazz, and so on are sorted by artist)
Year (not of a single CD but year of birth of the artist or year of creation of the group)
Titles (which means different CDs, not titles on a given CD, that info being on the sleeve; it's just in certain cases that I put exceptional titles in parentheses here, in order to know where to look for them; also the year of the CD is in parentheses if I took the time to look it up from the sleeve or from elsewhere)
From the above it becomes evident that my data isn't in perfect database format since as said, some records can have several artists, and it's similar for the titles: I put some solo artists together with their respective (main) groups, and then, in the titles, I put some additional info; it's evident I need (and have) links.
Instead of lengthy explanations, I just put an example here, with the query and its immediate result; from this it becomes clear that a simple (and not too big) SQLite database, with systematic "like" instead of "=" query, is also suitable for text data which is in a less-than-perfect form, and where to bring it into such perfect form would not make too much sense, because of too much work, for not really much more effect in practice (left out fields without interest here):
select * from cds where "artists" like '%stefani%' or "titles" like '%stefani%'
1)
[Artist:] No Doubt
[Category:] A8 [this means Pop, USA, Eighties]
[Year:] 87
[Titles:] (Gwen Stefani 1968, Eric Stefani) a) Rock Steady, b) Gwen Stefani: The Sweet Escape (2006), c) Gwen Stefani: Love. Angel. Music. Baby. (2004)
2)
[Artist:] (Gwen Stefani see No Doubt )
0 [default when not applicable]
0 [default when not applicable]
[Titles: empty]
From the above, it becomes evident that it would be very welcome to have some input device (macro tool) which would allow for typing the search string just once and then put it into both query statements. The same macro would also apply to queries for titles, or, for speed reasons, you would apply a second macro, with just one query statement (the string just being searched in titles).
A macro may be easy on the pc, it should also be available for Android and/oder iOs/iPad, I hope. Here again, if you make the slightest typo, most of the time you will just not see the wanted results, without any error dialog, for example if you write "artist" instead of "artists", so doing this by some macro tool is not only a question of convenience, but of avoiding typos which may leave out wanted records.
Also, if I put my links to artists from the "artists" column into the "titles" column (which should be quite easy), a simple query would be sufficient in these searches for an artist also belonging to some group, but it would not cover cases where, as said mainly in jazz, there are main, and "secondary" artists, the "secondary" being just the ones by which the CD in question is not filed. As you can imagine, in jazz, it's not so neat at all, but in my text files, every important artist is either named in the artist or in the titles field, so that with a query over both columns, they will be retrieved reliably.
In other words, in many practical, private or office, use cases of not-too-extensive data collections, "like" is the solution to use a light database instead of a text file: on a modern pc, some 20- or 30,000 records should deliver almost instantaneous results, and that without enormous adaptation work beforehand which could often not be justified in terms of time investment.
P.S.
Tuxman: Any statement is an implicit question, too, for people who'd be able to add significant info/advice.
widgewunner: Thank you!
EDIT:
Just imagine music group names with or without the leading "The": Who; The Who; Who, The? If you all made it neat instead, that and similar ones will be additional problems; ditto for names in an office environment (but where most of the time the databases are very big and thus name search should be made possible by "="):
- prefix before first name
- first name
- prefix before last name
- last name
- suffix after last name
Not speaking of the multiple variants, according to country, too, and then even according to existence/absence/combination of these elements, how these elements are then to be combined differently (!) in the address field and in the salutation.
Edit June 1, 2017Writing from some more experience with transposition from text data to database data:
As said, you'll need a RegEx editor, and you'll need to know how to do RegEx replace with it, and then you'll be able to achieve about 80-90 p.c. of the necessary translation work by automated means. It's also necessary to have a RegEx editor which allows for details, for example for only replacing the very first ":" in each line, instead of further ones, too; this avoids a lot of manual work after visual check.
As for the result in the database, you will want the family name of the very FIRST author/artist/etc. of probably several ones as the START of your author(s)/artist(s) field since you will want, for example for print-outs, to be able to SORT by them ("order by..."); for just FINDING authors/artists/etc., you'll do as explained above ("like" instead of "="). Thus, you will need to do some macroing in order to seperate the first from the last names, which means in practice that you clip by the last space (before the first comma if there is a comma) in the field, except when there is some "di/von/de/of", by this allowing for more than one first (or middle) names.
For example, when there is an institution as the author, this will not work, so you will have to do some visual checking afterwards, for this like for everything else, but here again, it's a very good idea to combine several tools; for example, visual checking is much easier within a csv editor (I use Ron's Editor for that, but do NOT try to SORT with that, it's bug-ridden in this respect but the developer is not interested in de-bugging it; at least he does not react to mentions of those bugs) or even the target database, but the editing should be done by RegEx, within your editor or such (2-screen or big-screen setup): At this stage, you'll use the database just as a visual checking tool but you work on your editor csv file, which means you'll create the same database several times, up to it being almost perfect, then only NOT going back again to other tools for editing.
Before the final version, all your fields will be in varchar format and without size limitations, since all too often, depending on your original text file and/or your editing ("|" or something else as field separators) in order to distribute your data into the respective target fields, SOME contents of for example fields 6 and 7 will be within fields 7 and 8 instead, and since thus things you can quickly rectify in a csv editor (at least in Ron's Editor), you'll do your editing in some systematic way, sometimes even doing changes in the csv editor, then saving it for further editing in the text editor for RegEx, and so on - mixing things up will end up by inadvertantly discarding corrections already done.
Instead of buying a csv editor and/or learning RegEx, you probably can do a lot of things within Microsoft's Excel, but you'll probably get a double-quotes problem which can be avoided otherwise; anyway, you should replace all double-quotes with single-quotes in your original file(s) to begin with, before getting to any editing in the copied version of the original file. Btw, since your editing will probably have unwanted effects sometimes, you should do some sort of versioning (between your visual checks in the csv editor or in Excel or in the database), and of course you should store your original source file in a safe place as they say.
As a general rule: If your editing had unwanted effects on SOME records / datasets / text lines, rectify them manually at once, before doing other automated editings; if they had such unwanted effects on a lot of records, though, go back and devise some other (/ some other RegEx) strategy.
And: Many users don't use text files for data storage but Excel files to begin with; it's evident that they then will get all the advantages of a database (SQL queries, incl. standardized ones and ones for filtering), even out of their flat files (before, or even without ever normalizing them), without all the semi-manual fuss described above, so to store half-way standardized files in Excel instead of in text files seems to be a very good idea: The field format is common to spreadsheets and databases and thus forces you to a much better standardization from start on then the text format does, so you'll get much less "micro-architectural" variants than if you use text files, even if you had thought you had been entered your data in a standardized format in there.
As for "sorting fields", I mean fields FOR sorting here, it's evident you can easily establish a flat tree structure, by (creating, and then sorting by) fields like C1/2/3/4, for Code1..., for categories and possible sub-categories, while the latter can remain empty when not needed.
But as implied above, databases, even "primitive" flat ones (and Excel, for such things), only get "interesting" when you're able to trigger technically complicated sql (or other) selects very easily; in other words, you need 1-key(-combinations') "stored searches", but with variables for the key terms; it's surprising that current database frontends' developers didn't grasp that so that you have to write your own macros for it.
Edit June 2, 2017
Printing ("Reports")I didn't look into the various "Reports" facilities of paid/trial frontends; from previous experience, I would assume most of them are not very flexible so that you must accept what you get; for example, from Navicat, you can buy some extra "Report Generator" so that it's safe to assume the report functionality of the Navicat frontends is very basic; also, there is some famous generic report generator I don't actually remember the name of and which isn't cheap either.
Thus, printing from some database frontend does not seem to be easy, but it is, with some macroing. First, you must identify how you want to get the records; for example, you want the first record separator ("|" or other) of each record to be translated to ": ", the second one to " - ", the third one to a ":" but with a code for printing bold, the fourth one to be translated to a comma and a code for end of printing bold, the fifth one should be a code for newline, etc. - it goes without saying that we're speaking of database data and thus we're speaking of perfect standardization of the records.
I've spoken of this problem before, you need a RegEx implementation which allows for differently treating the first, second, third... occurence of your field separator in each line. In practice, this means you need an implementation which allows for treating each line individually, and then, instead of trying bombastic macro code, you just set up specific RegEx commands for the specific field separators 1, 2, 3..., and you run the first RegEx to the first occurrence of the "|" (or whatever) in each line, then the second RegEx to the first (!) occurrence... and so on: Since each RegEx replaces the first "|" in each line, the next run will automatically replace the next occurrence.
So you must find a RegEx implementation which allows for treating each line individually, but in bulk of course, and which allows for treating just ONE occurrence in each hit region (here, the lines); this will unfortunately exclude some of the available editors and their RegEx Replace implementations, but then, you will want to have the whole macro available for any printing anyway, instead of manually entering the different RegEx commands.
After this, you will have your csv file, exported from the database file, and you will have a text file which originally will have been a copy of said csv file and which is now a plain text file, in case with markup codes for formatting.
Oh, I forgot: You will need titles and sub titles for prettier print-outs, and you also will want to have automated page breaks. You will run an sql select with "group by" (without the further data of course, in our example above without "N", "FN" and "T" and so on), which will give you a list of the groups, and then you put that list, after the necessary formatting, as bulk into your database (insert into ... (field, field...) values (v, v, v,), (v, v, v)...; - you know how it's done): This will give you sort of "empty" records but which are perfect titles and sub titles even for browsing query results on screen (but then without leading blank lines, while for printing, you can easily insert blank lines or page breaks before those titles/sub titles). In the future, you will not create new categories or sub categories without creating the respective "blank" record, the record with just the categories, first, and that's not too difficult to remember, since:
You shouldn't create new records entirely by hand anyway but create a macro which allows for creating a new record as a copy of the current record, and if you systematically do this, this means that you will create new records by first going onto the "title" / "sub-title" = the "dummy" category record, trigger the macro, and then just fill out the real data; this way, it's technically impossible to ever forget to create the dummy, "title", record for some new category.
Oh, and I forgot, how to then automatically insert the codes for leading blank lines or leading page breaks? You will insert an additional column, name it "pc" for "print codes" or whatever, 1-char numeric, default 0, blank line before = 1, page break before = 2, or whatever, OR you could write a macro which afterwards checks the blank content fields, but that would be less flexible since it's not taken that you want a blank line before ANY record where C3 and C4 are blank (lesser group change), and a page break before ANY record where C2, C3 and C4 are blank (and all the rest, of course, since we only consider titles of any level), so an individual setting is probably best (and will "cost" 1 digit per record). (Of course, your macro mentioned above will then also translate the "1" or "2" into the needed formatting code.)
So, you will have got some select result which then is perfectly pre-formatted by your macro but which looks horrible to the eye. What to do with such a thing? You can put it into InDesign or some other dtp tool, but for most use cases, that would be way too sharp an effort. It's probably possible to import it into Microsoft Word or such, but the question remains, how much formatting will be preserved, how much of it will be lost? It would be ideal if there was some pdf printer (tool) which could read some formatting at least, but in fact, I didn't even find any which preserves page brakes (ascii 12 for example), so let alone for bolding the content of some field content...
Thus, you may think of html, which should be perfectly possible, but then, printing from html is not something I had never considered to do. On the other hand, it's very easy to enlarge your macro just that little bit as to add a little rtf header and the closing brace, and running a macro translating all possible special characters (à, ù and so on) into the respective, 4-chars rtf codes, is easy since the web abounds of the respective tables for that task.
Then, you open your newly-created rtf file from within an rtf editor (for example WordTabs, free) and trigger "Print" - that's it, or perhaps it's even possible to send an rtf file directly to a printer, the necessary commands (paper size, margins, and so on) should all be available within the rtf language.
I'm just assuming here, but let me guess that the above work-flow (which is entirely free) is much more flexible than some expensive database "report" programs are, at least as no graphics are involved, and I admit that I didn't bother to look up Tibetian-to-rtf, but for the rest (and by printing from WordTabs or Works), it works fine already... to be refined of course, and when I have time, I'd like to get rid of the additional rtf-editor-for-printing step - all of rtf is available on the web, but it's quite some stuff to wade thru, for direct printing.
Oh, and for standardization, all the tables in my one-table databases are called "main"; helps big with writing heap select-macros.
Edit June 3, 2017To clarify why RegEx here.
I'm not speaking of the classic RegExReplace "somethinga fieldseparator somethingb fieldseparator somethingc" by "somethingb fieldseparator somethingc fieldseparator somethinga", for which RegEx is needed in order to maintain the original somethings to be relocated (you'll work with parentheses for groups and numbered placeholders for them), it's just needed here in order to treat the lines (records) one by one and then applying the "replace once only" (in the specific way of the respective replace of the replace stack) to every LINE. You probably can do this by other means (for example have a look at KEdit, its price has come down from 129 to 99$ recently and which possibly could do it, did not check), but for me, doing it with RegEx replace has proven simplest.
Instead, you could write a one-line classic ReEx replaces "doing" each line/record for all which is needed, then (with the same command line) the next, and so on, but I suppose that will take a little bit more execution time (by the multiple replaces 1-by-1 of the field contents (which are left alone in the strategy described above, and which I prefer for that reason of "minimal invasiveness" then), but it's correct that it may be simpler to maintain specific 1-line replaces, one for each database select-result file (independently of course if this result comes from flat tables or from a more or less normalized database), than doing multi-line replace commands, one line for each field separator in the record. But then, I prefer the latter also for the fact that in a multi-line command, you will get neat comments at each line, whereas in a 1-line RegEx, you would need to write down the respective comments not as near the respective command but for example as a multi-line block below, each line referencing one group in the 1-line replace command, so at the end, the 1-line flavor is not as elegant anymore when you put down comments, and it's evident that even when you do, changes aren't as quick to do as with multi-line since you'll always need to check if your change concerns the right element (and not possibly its left or right neighbor, inadvertently), while in the multi-flavor, with the (right) comment in the same line, it's always evident where you are and do your changes. But that's a discussion of RegEx styling, both ways are perfectly possible.
Oh, and I would like to modify my claim above that the described strategy may be "more" (?) flexible than out-of-the-box "report designers". For example, it's easy with the former to automatically insert a leading blank line whenever the first, or even the second character of some field changes; this would be helpful for example for the field "A" when you set changes of the fields "C1" to "C4" to "leading page break", and would not even take much white space in case you print in more than 1 column - but good specimens of the latter variety should do that too, of course, so let's say the way described here is AS flexible as good "report designers" (very probably) are.
You just run another macro element which retrieves the content of that field in every line/record, compares it with the stored content of the previous one, and if character 1 or 2 isn't the same anymore, it changes the content of field "pc" ("print code") of the line from 0 to 1 (if the one of the previous line has been 0, not 1, 2 or other, before, so this will not affect records after titles/sub titles, or you do it by "changes, but no changes from nothing", titles'/sub title's "N" fields being empty, in our example above); or you simply bolden the content of the "N" field in the output upon such character changes only, instead of boldening it for every record - as you see, the possibilities of revamping core database outputs are seemingly endless, just by running text macros on the csv, in the right order.
As said above, you will want titles/sub-titles on paper, and on screen, and on top of the respective real data records, not below them, so with the above, you will have an order problem:
select * from main order by C1, C2, C3, C4, B, N, FN, T -- I renamed "PC" to "B" for "breaks"
does it wrong,
select * from main order by C1 asc, C2 asc, C3 asc, C4 asc, B desc, N asc, FN asc, T asc
is ugly, so, in order for the real select
select C2, C3, C4, FN, N, T from main order by C1, C2, C3, C4, B, N, FN, T
to work correctly,
you either code line breaks as -1, page breaks as -2, but I don't like negative values when not needed, or you set the "B" column's default value to 3 instead of 0 and set it for line breaks to 2 and for page breaks to 1; this will even leave you the 0 for a possible other code before you'll have to do an "update main set... where...". I concede a default of 3 isn't pretty but then you don't even have to display it (see the last select); for new records, your macro will put it in automatically, and sql lets you sort ("order") by invisible columns.
We're continuing the "refine it" part here: When a field is empty, you will probably want to get another replace string as the one you want for a non-empty field, for example if you have 4 hierarchical categories C1...C4, you want them "C1 - C2 - C3 - C4: ..." but when subcategory C4 is empty, you want it to be "C1 - C2 - C3: ..." instead. How to do it, since RegEx does not offer some "if it's the full string (with variable part here, that's a given), replace it by (the various part and) "a", and if the variable part is missing, replace it by "b"; look-arounds do not help either if you always replace the very first string of the kind of each line, in every iteration, and a non-replace would create chaos, any un-replaced field separator wrongly becoming the first one then.
So in most cases the replace would be done, which would result, in our example, in a "C1 - C2 - C3 - : ...", and later, you simply would replace any " - :" or even " - - :" by a ":". If that's not possible because of possible occurrences of these strings in the regular text, you simply replace all field separators with the wanted strings but in which you maintain the original field separator, by replacing it with a special character which does not occur anywhere. For example, if the field separator is a tab, do the replaces as wanted, but replacing the tabs by an additional "|", too, which gives, in our example, "C1| - C2| - C3| - [C4 being empty]|: ..." = "C1| - C2| - C3| - |: ...", and then, you'll simply delete (always by macro since you'll know beforehand which strings can occur by this) unwanted "|" combinations (here: "| - |"), and in the end, all remaining (single) "|"; Perl is said to be best, so perhaps it can be done in simpler ways in that language. Anyway, you clearly see that you don't need to just use spaces, then delete the multiple ones, but you can do anything you want; let's hope the commercial offerings go into these depths, but then, 2/3 of their development efforts inevitably go into the GUIs.
EDIT June 12, 2017:
Some (useful and/or very important) DetailsOutput: RegEx / Replace
To my surprise, I didn't find any RegEx editor which allows for only replacing the very first occurrence of some given (sub-)string in each line, so I had to do it the opposite way described above, by changing every line in one occasion, the whole line, line after line, and this gave those horrible 1-line, inscrutable RegEx replace code I referred to above. As you know, you must group the sub-strings by (), and in order for then not getting totally lost, I did a comment line, with numbers 1, 2, 3... 25... immediately above the code line, for every ()-group and with the necessary numbers of spaces before and between the numbers, in order to identify every group by its number - since in the replace part, you must refer to those numbers but which are not in the what'-so-be-replace part of the RegEx code - a RegEx flavor where you write the group numbers directly into that "original's" code would be very welcome.
The above applies to the regular, the "B=3" lines (and thus, the "B=3" is checked within this code line and will thus leave alone lines where this condition is not met); you'll do your special RegEx replace code for other lines, for example for B=1 or B=2, for other conditions, and you'll also don't necessarily replace the original line by the final rtf code but for some formatting code, you'll use placeholders (for example little/suspended/superscript 1/2/3/a and such), and you'll do their final replacement later on (incl. extermination of unwanted code combinations, see above): This will greatly enhance the clarity of your code (so you'll not be get lost in it) AND will greatly help you with formatting changes you will want to implement afterwards, be it that you want some else formatting or be it as alternatives for several different output situations.
Also, you must pay attention to a correct, AND to a smart, order in your replace code (which replaces will be made before others; if you do this smartly, you can greatly reduce complications in your code (differentiation of which lines / sub-strings are affected further on: "discard" anything which is easy to "discard" at some stage, then do the "complicated" things, then again take the "discarded" lines and do further replacements there, in case); also, only the core replacements where RegEx is really needed (for groups) are done by RegEx, most other replacements are done with regular replace commands.
In order to not get lost in this bulk of code, read your text body into the clipboard, then have your code work onto the clipboard, and put a stop code after the first line, then the second one, and so on, and have your code display some of your (changed) text body to check if the replacements made up to the stop code are what you will have expected.
If you do the replace according to these lines of advice, it will all work out to your entire satisfaction, as it did for me, and your replace/rtf-formatting code will differ only in some core parts from one database to another; for almost anything, it'll be perfectly "reusable" - in practice, of course, you'll use the same code block for everything, doing just some "if database = a/b/c..." branchings where the code differs.
(You'll do a lot of replacements in order to refine your code, for example you will print/export only the very first words in bold, up to the first comma if there is any, of the content of the field "N", which means the family name of the FIRST author will be in bold, not further authors (in that book example)*, but if the author is some institution with several words in its name, all of these will be boldened; also, if you will have no first name (field "FN" is null), there will be no space (which would be inserted between a first name and a family name): All these details (fine-tuning) you'll realize by refining your code after the code works well in its core functionality, and placeholder characters greatly help with all this.)
*: Since I now have just one author in the "N" (for "name") field, OR the first author is before the very first comma in there (if there is any comma in that field), and the order within each sub-category (or overall, in order to get an ABC authors' list) is by these "first authors", it would be easy to put further authors (ie anything after a comma there) into additional, referring records, but since I do "like" searches anyway, I don't feel the need to do so yet.
Output: RTF
To my surprise, printing an rtf file isn't done easily, like you would send a pdf file to a printer, and I cannot do it do, there's too much special knowledge involved. So you will need some intermediate program in order to open your rtf file, and from which then you'll print the file. Forget the usual rtf editors, I tried some of them, like "Jarte" and others, and they will open your (correct) rtf file indeed, but then you'll lose much of your formatting, in other words, they aren't able to correctly read the rtf code of the file they will have opened.
Don't laugh and say, "then your code will be wrong", since in MS Word, it all works out perfectly, incl. for example printing-out in several columns, with title lines of several indentation levels starting a new section, so that's exactly the application I advise you to use for checking and printing your programmatically-created rtf files: First, Word is of great help to correct and refine your code, by checking what it will give in Word, and then, it's the perfect intermediate tool for printing as long as there is no basic tool available for sending rtf files to printer.
It goes without saying that such "raw-data-transformation" of database data into "pre-formatted target data" not only works with rtf code for printing, but also, with the respective html/css/js or mark-up codes, for web publishing or for further treatment in InDesign or any other (ex "desktop") publishing tool; even links to graphics or other elements, present in the database, could be integrated as needed into the target text body InDesign or similar then will work on. (Btw, the size of your database files will be extremely low, comparatively, since there is no formatting or other "noise" info within the database body, but just will be added programmatically after export into csv/text format; this doesn't apply to BLOBs, of course, but then, you probably will prefer links to files in the file system instead of BLOBs within the database anyway.)
To begin with, you will send the correct sql select statement to your database, in order to get the wanted data (only), in the wanted order; then, your text-processing code will do everything SQL isn't, or is less, able to do.
SQLite: rowid / ID / autoincrement / primary key
As for SQL specifics you must know beforehand, there's the SQLite particularity that by default, it re-uses (!!!!!) row IDs that will have become "free", by deletion of the row/record in question; it's obvious this can create total chaos in your database (while for flat book lists and such, that is not really such a problem in reality, you'll want to make even such a basic thing "future-proof"). In the web, there is also WRONG information upon this phenomenon available, for example
http://www.sqlitetut...qlite-autoincrement/ pretends/implies by omission of clarification that such rowid re-uses will only occur when your SQLite rowids have reached the stunning number of 9,223,372,036,854,775,807: this is definitely wrong. Fact is, most of the time, SQLite uses new rowid numbers, not numbers of deleted rows/records, but there is no guarantee whatsoever for this, in my tries, when I added (single or multiple) new records, then deleted them, then created new ones, rowid numbers of these recently created-and-deleted were re-used; HOW SQLite decided upon re-use vs new number is beyond my knowledge.
Thus you will need some ID column replicating the internal rowid but with the "autoincrement" setting (which then, but then only (?), also works for the internal rowid, or in other words, I don't see a way to apply "autoincrement" directly to rowid, you seem to have to replicate the rowid in some ID column which then, while remaining a replication of the rowid, forces that rowid to autoincrement; there does not seem to be a direct setting.
They all say that for performance reasons, you should avoid autoincrement at all cost, but imagine you do some zettelkasten database, with cross-references to other records, and then SQLite re-uses rowids/IDs of deleted records for new ones?!
Ok, that's not a perfect example since when doing cross-referencing, you should prevent deletion of referenced records to begin with, but this implies technical cross-referencing (cross-references either only in special, referenced fields (foreign keys) or cross-references-in-text with additional such referencing and the overhead that comes with that, and which is not possible within a home-made, hobby application) while in real life, you would probably use non-technical-cross-references, ie not-monitored ones, just by copying the target's ID number into your text within the content fields (hopefully with follow-the-link formatting), so that these "inline" references to other records MUST be absolutely stable, which, if you use SQLite along the lines of their common advice (ie avoiding autoincrement IDs), is NOT the case.
I did not yet try how it works if you create new records programmatically, ie by executing your own sql, only, but in practice, you will probably create many or even almost all your new records from within your frontend, and doing so will create plenty of re-used rowid numbers at least in SQLite Expert, but I think it's not the fault of this frontend, this time. Of course, there is always the solution of avoiding record deletions, discarding records not being wanted anymore into some sort of "archives", by switching a boolean switch in each record (for example column "a" for "active: yes/no", with or without some "cleaning" of them within the "active" table and shifting them into some "archives" table(s) every now and then), but it's probably a good idea to have such "archives" indeed, AND to provide for the possibility to definitively eliminate records, even from these archives, and this would make reappear the problem, and overall, how would you really prevent ANY possible (and potentially problem-creating) (manual) record deletion from a hobby application? In other words, you couldn't use any out-of-the-box sql frontend anymore but would have to write your own, deletion-proof frontend. It's evident that corporate applications would not come with such problems since there, for compliance reasons, it's even necessary that no record will ever be deleted, instead of being moved into some archives, but then, SQLite isn't the right back-end for any group application to begin with. So we're back to "autoincrement". But:
In the different flavors of SQL, it's more or less difficult do database design changes after creating a database, or after creating a column, and in some cases, your only means is to create a new database, with the characteristics you want it to have from start on, and then fill it with the data from your original database (fortunately, this can be done by sql code), and in the case of unique IDs in SQLite, that's exactly your problem here if you will have created your database without creating the autoincremental ID column when creating the database, ie the command
alter table main add ID not null integer primary key autoincrement will NOT work, because of the primary key constraint ( see, among others,
http://www.sqlitetut.../sqlite-primary-key/ ). (So when creating your database and your very first (empty) table in SQLite browser (see above), first create the necessary ID column, THEN only do the "File - Import - From CSV" command in order to create the (basic) database from your csv data, before "formatting" the (other) fields in your frontend.)
SQL: Mix-up of nulls and of empty strings
This problem described above did not come from my using two different frontends, alternatively, for the same database(s), but from my using Ron's Editor in order to quickly and "manually" shift data from unwanted columns into the correct ones, after sloppy RegExes of my original text data (see above); in Ron's Editor you can shift manually the content of many fields into another column very quickly, so this tool comes very handy for putting "far-from-being-normalized" text data into a table (csv) format for spreadsheet/database use. But whenever I imported or re-imported my data from that csv editor into my database (ie whenever I rebuilt the database from the updated csv file), I didn't have nulls but empty strings in empty fields, while, as said above, for newly created records from within the database (frontend) itself, fields left blank on creation correctly got null values.
Thus, csv editing of database stuff should come to an end at some point, then you'll replace any empty strings by nulls, and from then on you will have only non-empty strings, and nulls, to cope with in your database and your sql code. (I largely prefer nulls over empty strings, and be it for the only reason that I can simply leave fields blank when creating new records from within the frontend, without creating by this a mix-up of nulls and empty strings*; you'll do how you like to do it; it's just the mix-up that's really unbearable - or you'll get very complicated code if you want to get correct results.)
*: If you programmatically create new records, you could write your code so as to create empty strings for fields "left blank", but if you create them by typing within the frontend's table view, those fields get set to null if you don't manually set them to '' one by one, and voila, you'll need the code for empty-string-nulls-mixup further on, so clearly avoidance of empty strings in favor of nulls is preferable; your mileage may vary.
SQL: Field names
The ID field can get any other name (but not any other numeric format than INTEGER), but as a general rule, my uppercase field names above are a big nuisance when entering them into the respective sql commands; I'll change them to (equally short) lowercase names, and you should use those from start on (Windows doesn't make any difference here between lower- and uppercase anyway).
