Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • May 27, 2017, 06:57:01 AM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: SQLite, SQL, SQLite Expert  (Read 320 times)

ital2

  • Member
  • Joined in 2017
  • **
  • default avatar
  • Posts: 41
    • View Profile
    • Donate to Member
SQLite, SQL, SQLite Expert
« on: May 01, 2017, 11:04:52 AM »
First, some quite obscure trap for beginners, stackoverflow helped me to find that out.

For many a command, not using quotes (single or double) will work fine, so you will not discover it's faulty syntax.

for example
select * from tablename where somecolumnname = somestring
will work fine as long as there is no column (another column) which is called/homonym to somestring

example:
select * from A where T = V
will work fine and display all records where there is a "V" in column "T"
IF there is not another column which by any chance is called V

IF there is, SQLite will simply do not show the resulting records, no warning, no error message, nothing, so when you've got some other columns and your requests work fine in general, it'll be probably a lot of time before you realize SQLite will not display many wanted query results.

You know ask why would anybody call many columns by single characters, and also many string values by single characters, to begin with? That's right, but I had just a few 1-character columns, incl. "C" for Code, with 1-character codes in it, for several, different todo-categories, just 6 or 8 characters in all, all single ones, and so I wanted to have it neat and without sacrifying space, and so I called that "Code" column "C".

This works fine, except for my code "h" in column "c", since I also had another 1-single column called "h", and without the quotes, SQLite is unable to differentiate column names from values if they are identical, even if you would have thought that the "where c = h", by its syntax, must have told SQLite the first is a column and the second is a value, but no.

Thus, it's absolutely necessary to write in the official syntax

where "columnname" = 'value'
which is double quotes for identifiers and single quotes for strings/literals
(if at least it was the other way round, I could memorize that better...)

in order to avoid lots of problems which, as the above use case has shown, are far from obvious and could have you get incomplete results without even discovering the problem.

Tuxman

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,842
    • View Profile
    • Donate to Member
Re: SQLite, SQL, SQLite Expert
« Reply #1 on: May 01, 2017, 11:06:54 AM »
Was that a question?  :huh:

widgewunner

  • Member
  • Joined in 2009
  • **
  • Posts: 91
    • View Profile
    • Donate to Member
Re: SQLite, SQL, SQLite Expert
« Reply #2 on: May 01, 2017, 11:14:58 AM »
Thanks for the public service announcement! - I am new to SQL and am using sqlite2 in a couple small projects and this info is indeed useful/helpful to me.

Thanks again!

ital2

  • Member
  • Joined in 2017
  • **
  • default avatar
  • Posts: 41
    • View Profile
    • Donate to Member
Re: SQLite, SQL, SQLite Expert, CSV
« Reply #3 on: May 01, 2017, 11:33:45 AM »
Then, a word on SQLite Expert Personal

As I said in the "Navicat Warning" thread, I recommend this free software, it's really pretty. It has got some bugs, but not of the data-destroying kind, just minor window problems and such.

I said that I use it on my pc, but I left out an important info: Since it's the free version of a paid program, it will not import data, so as it is, it would have been unusable for what I'm trying to do, translating some organized text files (and also csv-files from ListPro) into databases / flat database tables (flat to begin with, looking out for doing it perhaps more to the regulations later on if I find the time).

So I use SQLite Browser / DB Browser for SQLite (free, they don't know how to name it, anyway, for XP I use version 3 of it since 4 will not work there): Import from csv-file into table, then save the new database. All other things I then do in SQLite Expert Personal. csv means comma-separated, but obviously a comma as field separator isn't the best alternative, so I use tab-separated, but it's with the .csv suffix anyway.

Since in SQLite Browser / DB Browser for SQLite, I create the database, for example cds.db (from cds.cvs), I can then open my CDs-database within SQLite Expert where I format the columns, and so on.

Now my explanation why I just don't buy SQLite Expert since I really like it a lot. Well, my psychological loss is too big. Last year in summer, I had already looked out, superficially, for SQLite frontends, since my problem of having data in text file, as explained in another thread, is a persisting one, and then I already had had the idea to do something about it.

It was 60$ lifetime, which was an absolute treat, and I promised myself to buy that fine program as soon as I would need it. Now it's 100$ with 1 year of updates, which means instead of costing me 60$ plus VAT, this same program (btw, the price rise came for the "old" version, not even a new major version in-between) would cost me hundreds of $$ over the years. Of course it's all my fault, and I don't blame the developer, but since I lost the lifetime option, now paying a price almost double of that one, for just the current version, plus update costs again and again - it's unacceptable for me at least for the time being where SQLite Expert is a fine SQLite data browser (I like it a lot more than SQLite Maestro, same price, so you see where the hike had its idea from), but where for example there is no graphic designer, let alone that imaginary piece of joy I've just described in the "Navicat Warning" thread, and there are no stored queries yet, which are available - as a graphic designer, but not a good one - in SQLite Maestro, but as said, stored queries in SQLite Maestro cannot be triggered from the tree entries but must then be triggered by an unnecessary intermediate step.

SQLite Expert is in continuous development (got just another minor upgrade), so it's very evident it will get at least some of its missing functionality later on, and with no doubt, people who didn't come too late for the life updates, as I did, will have got an absolute treat. (It's been very unfortunate that the price hike and the dismissal of the lifetime license came both at the same time - together with the almost 50 p.c. rise dollar vs euro, that all combined is not too bad for a developer who's in the euro zone...


EDIT May 25, 2017
You will find more info upon SQLite Expert, also in comparison with some competitors, in my May 25, 2017 add-on in my thread about trial conception ( http://www.donationc...ex.php?topic=43835.0 ).
« Last Edit: May 25, 2017, 05:32:22 AM by ital2 »

ital2

  • Member
  • Joined in 2017
  • **
  • default avatar
  • Posts: 41
    • View Profile
    • Donate to Member
Re: SQLite, SQL, SQLite Expert
« Reply #4 on: May 01, 2017, 12:44:10 PM »
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.
« Last Edit: May 01, 2017, 01:07:34 PM by ital2 »