I use Obsidian to write the code for the table creation. Then I have an ancient firefox browser I never upgrade that has an old SQLite plugin that I like and I do the creation execution there.
For maint. I tyically either use the firefox program to run my queries and produce stuff, or I write my own Python program to do it or I use sqlitemanager on occasion. I see it’s now paid for but I had an old free copy.
You write code in Obsidian? Is there some reason you do that other than just “Obsidian is a text editor”?
Yes, I can link to other code snippets. I have a single place to go to to copy said code for later use and I can edit faster there than in anything else I’ve tried. I can also add in other stuff that a pure code editor won’t accept, like drawings of how the data moves between tables or a handwritten note to myself about the code that I was too lazy to add in as text.
I am mostly using it for queries right now but am slowly build up a library of functional bits that can be re-used in many differnet places. Sort of my own personal library of useful stuff I’ve figured out. Plus I can use the language keyword in Obsidian to catch many syntax errors that plain text editors miss.
So if you use the same code in 3 different files, you’ll effectively link all of those to the snippet and you can thus track where you’ve used the code in the future?
You didn’t ask me but…
This is great for SQLite:
https://sqlitebrowser.org/
This is great for Postgres:
https://eggerapps.at/postico/
For anything that really needs a database, I use SQLite with any of the following:
Datasette and Datasette Desktop – best if you just want to explore an SQLite database (or a bunch of CSVs). Read-only off the shelf, but there are plugins for add and update queries. Incredibly useful if you’re going to share the data, eg over an intranet; you can do pretty remarkable things with the template system. I think the developer, Simon Williston, runs a CMS off and a couple websites off it.
Lately, for more tradition DBMS work, I’ve been using Native SQLite Manager, from the App Store. The developer is very responsive, and so far it’s the most Mac-assed of the SQLite managers I’ve tried lately (others including DB Browser for SQLite, which is clunky, and Beekeeper Studio, which is read only.
I was using DBeaver, which is very powerful and incredibly full-featured, but also confusing for someone (like me) who isn’t steeped in decades of DBMS practices; it’s also some kind of universal app – points for cross-platform consistency, none for Mac-assedness.
But for other purposes I’ll keep my data in CSV and use python – either pandas, or sometimes agate, which I find a LOT more intuitive, but which isn’t as widely used.
So does SQL incorporate SQLite or are they different languages? If I learn SQL will I be able to create SQLite databases?
Best you read up on SQLlite.
SQLLite SQLite Home Page is the database stored (usually) on the local file system as a “file” which responds to Structured Query Language (SQL) queries to return results. Same as using SQL to query (usually) remote server databases like Oracle, MySQL, Microsoft SQL Server, … (a long list of server products).
SQL as a technology is decades old, emerging from a long “battle” with other technologies of the day. Today there are lots of products that are higher level and abstract SQL commands. some mentioned above. I like Django. Other are out there. The products are sort of like any programming language that helps convert instructions into code the lower level software can understand.
The short version is that SQLite is a flavor of SQLI. For most practical purposes, if you learn to use SQLite, you’ll be learning SQL.
There are other flavors (Postgres, MySQL, etc.), and serious database experts have their preferences. I’m sure some are a little better at one kind of task etc. But for the most part, you’ll be fine with SQLite unless you’re doing something really specific. Much of what’s on iOS sits on top of SQLite, for example.
SQL is a language for accessing and manipulating data. An sql database (or “a sql database” if you pronounce it “sequel” like the cool kids) is a bunch of tables – think spreadsheet tabs on a workbook. SQL is a way of retrieving or modifying data. It’s surprisingly readable, in its simplest uses. For example:
SELECT
name,
employee_ID,
department,
start_date
FROM
employees
WHERE
start_date > "2021-12-31"
AND
department != "sales"
That’s essentially saying: Use the “employees” table, and give me the records (rows) for people who started this year and aren’t in sales, but only show me fields (columns) of the row." (The lower case field names are arbitrary, and I’ve intentionally picked ones that make a kind of intuitive sense; the upper case parts are commands or other terms with special meaning on SQL.)
I should stress I’m no expert in sql or SQLite (so there may be some mistakes in the above). But that’s kind of the point. It can be very useful even if you only get a little of it. If you’ve ever touched a macro in Microsoft Office, or done much with complete formulas like VLOOKUP in a spreadsheet app, or played with widgets in Scriptable, or even if you haven’t ,… you’ll be fine.
The best way to learn is to have a project that you are interested in, and try to do it. For most people, I think it usually starts with a spreadsheet that gets out of hand or is just too huge to be easy to use in Excel or sheets. Conversely, for a lot of simpler tasks, a spreadsheet may be good enough.
For me, the single biggest challenge of learning SQL was figuring out how to do it. Outside of a few tools (Microsoft Access for one, maybe surprisingly), database managers – the software you usually use to work with a SQLite or other SQL database – are clearly made for programmers, and can be a little bewildering.
Yes and for SQL table definitions I track the foreign keys as well. Plus I put links to the documents about the particulatr application I used the code in sicne I have a whole ecosystem of AnimalTrakker and LambTracker apps that are often re-using code and all talk to their respective databases.
More or less. They share a base but are different.
Biggest issue is lack in SQLite of certain types of joins. But you can always do the reverse and get he data out it just takes more thought.
Biggest advantage is that the entire database is a single file. So it can easily move between various operating systems and mobile devices. It is also local so solves my issues with cloud access for in the field applications.
For example, I use the same AnimalTrakker file on Mac, Windows and Linux desktops and laptops and also on a plethora of various Android devices. I am exploring using the file on iOS and iPadOS as well but haven’t done that part yet although I believe it to be possible. Data portability without conversion is a huge advantage for cross platform work. Local storage is a huge advantage for cases lie mine where you cannot guarantee there will be any interent connectivity or communications at all. Everything must be contained within the device you are using.
It also seems that there are less books available on learning SQLite than SQL.
One minor quibble on the syntax of your sample query.
After spending hours searching for the missing or extra comma I now format all my queries like this
SELECT
id_employeeid -- this is the primary kep for the employee table
, employee_name -- specify what portion of the table you are looking at
, employee_ID
, department -- I would tend to clarify this as well as department might also be a table not a field
, start_date
FROM
employees_table -- redundant I know but then it's obvious what you are looking at
WHERE
start_date > "2021-12-31"
AND department != "sales"
I nearly always bring in the primary key in most queries because it often makes it easier to do further processing on the results.
Here’s an example of one of my table creation statements copied direct out of Obsidian
-- The sire_id and dam_id are pointers into this table
-- id_breeder_id_contactsid is a pointer into the contacts table of the person and location that is the breeder.
CREATE TABLE "animal_table"
("id_animalid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE
, "animal_name" TEXT NOT NULL
, "id_sexid" INTEGER NOT NULL
, "birth_date" TEXT NOT NULL
, "birth_time" TEXT NOT NULL
, "id_birthtypeid" INTEGER NOT NULL
, "birth_weight" REAL NOT NULL
, "birth_weight_id_unitsid" INTEGER NOT NULL
, "id_birthinghistoryid" INTEGER NOT NULL
, "rear_type" INTEGER NOT NULL
, "weaned_date" TEXT NOT NULL
, "death_date" TEXT NOT NULL
, "id_deathreasonid" INTEGER NOT NULL
, "sire_id" INTEGER NOT NULL
, "dam_id" INTEGER NOT NULL
, "inbreeding" REAL NOT NULL
, "id_breeder_id_contactsid" INTEGER NOT NULL
, "id_managementgroupid" INTEGER NOT NULL
, "alert" TEXT NOT NULL
)
Here is a medium complex query. I use table and field names so even the novice understands what I am doing. Yes, it makes for more typing but that also helps future me who may not totally remember why and how I did something.
SELECT
animal_table.id_animalid
,(SELECT
contacts_table.contact_first_name
FROM contacts_table
WHERE
animal_ownership_history_table.to_id_contactsid = contacts_table.id_contactsid) AS owner_first_name
, (SELECT
contacts_table.contact_last_name
FROM contacts_table
WHERE
animal_ownership_history_table.to_id_contactsid = contacts_table.id_contactsid) AS owner_last_name
, (SELECT
contacts_table.contact_company
FROM contacts_table
WHERE
animal_ownership_history_table.to_id_contactsid = contacts_table.id_contactsid) AS Company
, (SELECT
tag_number
FROM animal_id_info_table
WHERE
id_tagtypeid = "2"
AND animal_id_info_table.id_animalid = animal_table.id_animalid
AND (tag_date_off IS NULL OR tag_date_off = '')
)
AS RFID
, (SELECT
tag_number
FROM animal_id_info_table
WHERE
id_tagtypeid = "4"
AND animal_id_info_table.id_animalid = animal_table.id_animalid
AND (tag_date_off IS NULL OR tag_date_off = '')
)
AS Ranch_Tag
,(SELECT
tag_number
FROM animal_id_info_table
WHERE
id_tagtypeid = "10"
AND animal_id_info_table.id_animalid = animal_table.id_animalid
AND (tag_date_off IS NULL OR tag_date_off = '')
)
AS Trich_Tag
, animal_evaluation_table.trait_score11 as Scrotal_Circ_cm
, animal_evaluation_table.trait_score12 as Motility_percent
, animal_evaluation_table.trait_score13 as Morphology_percent
, (SELECT
note_text
FROM animal_note_table
WHERE
animal_note_table.id_animalid = animal_table.id_animalid
AND note_date > "2022-05-10%"
)
AS Comments
, custom_evaluation_traits_table.custom_evaluation_item as Classification
, animal_table.birth_date as Birth_Date
FROM animal_table
INNER JOIN animal_evaluation_table ON animal_evaluation_table.id_animalid = animal_table.id_animalid
INNER JOIN custom_evaluation_traits_table ON custom_evaluation_traits_table.id_custom_evaluationtraitsid = animal_evaluation_table.trait_score16
LEFT JOIN animal_ownership_history_table on animal_ownership_history_table.id_animalid = animal_table.id_animalid
WHERE
--id_contactsid = 1
--AND
animal_evaluation_table.eval_date > "2022-05-10"
ORDER BY
owner_last_name
, Trich_Tag
```SQLite is not SQL. To compare them is a false equivalence.
SQLite is a file storage thing which one uses SQL queries to extract data. SQLite does not implement every single command in the ANSI standard for SQL. It’s smaller and simplified by design.
Have you considered using the Tap Forms app on your Mac?
Tap Forms on the Mac App Store
EDIT TO ADD: Tap Forms is a full-featured front end to an SQLite database file.
I have got Tapforms, but kept hitting problems and limitations. I might give it another look, but will still learn SQL.
The core development team moved away from mySQL when it was acquired by Oracle. MySQL was forked as MariaDB. MariaDB is fully compatible to mySQL but in the FOSS community it is considered the better choice (licensing, commercialization by Oracle,…). MariaDB is also faster than mySQL. In fact, often enoug, when you read mySQL it’s really MariaDB under the hood (Wikipedia). And many Linux distributions moved from mySQL to MariaDB as the default database (Debian, Fedora, CentOS,…).
If Tap Forms does not work for you then by all means try something else. But SQLite works at a much lower level than does Tap Forms. That is, Tap Forms does more for you whereas with SQLite, more has to be done by you.
Yea, I know. Details. Simply add it to the list of SQL Servers. Actually I have MariaDB running on my Mac and Synology NAS so I do know about it. Habit to say “MySQL”.
… implementation details … and politics … 