HOW TO OPEN AND USE SQLITE

For installation problems as in moving to a new computer, see "environment variables old computer dec 31 2020 windows 7"

COMMAND LINE INTERFACE BASIC OVERVIEW HOW TO USE SQLITE: file:///C:/00000PROGRAMMING/SQLITE/SQLITE%20FAQ%20INTRO%20QUICKSTART/cli.html FOR 

open Windows command prompt by typing cmd in start menu search box and hitting enter
will open to c:/users/username
or find sqlite3.exe (in C:\00000PROGRAMMING\SQLITE\sqlite official unzipped) and double click it. In Windows 10 cmd worked well but in Windows 11 it has been downgraded and no longer lets you paste a command longer than 255 characters. I still use it for everything except copying a schema to make changes to it. This routine is described below, and is used frequently for deleting columns or adding/deleting constraints to tables. For this, you need to be able to paste larger commands, so use Windows Terminal tool, which looks like a tabbed command prompt. It has to be resized bigger before you use it. If you use it then resize it, it won't display properly. But unlike cmd, it will accept larger pastings. The next step is to switch to Linux (but Treebard doesn't work in Linux yet.)

EXAMPLE 0a: check sqlite version

    print(sqlite3.sqlite_version)

EXAMPLE 0b: TURN ON/OFF FOREIGN KEYS
    
    FKs not supported till Sqlite version 2?.6.19
    FKs are off by default, do this per each connection:

    conn = sqlite3.connect('c:/treebard/test_fk.db')
    conn.execute('PRAGMA foreign_keys = 1')
    # conn.execute('PRAGMA foreign_keys = 0') # this is default
    cur = conn.cursor()

    # In python if this returns no data then your version has no foreign key support:
    print(cur.execute('PRAGMA foreign_keys'))

    # In shell this returns 0 or 1 for on or off:
    PRAGMA foreign_keys;

EXAMPLE 1: open and close a database:

    C:\Users\LUTHER>sqlite3 c:/sqlite_code/treebard00012.db
    ...
    sqlite> .exit

EXAMPLE 2: find out what tables and views exist

    C:\Users\LUTHER>sqlite3 c:/sqlite_code/treebard00012.db
    ...
    sqlite> .tables
    Citation                        PlacenameChainVWConcat3col
    CurrentEntity                   PlacenameChainVWConcat4col
    CurrentEntityVWPlace            PlacenameChainVWConcat5col
    CurrentEntityVWPlaceBigger      PlacenameChainVWConcat6col
    CurrentEntityVWPlaceDelete      PlacenameChainVWConcat7col
    GenderMadeRelationType          PlacenameChainVWConcat8col
    Name                            PlacenameChainVWConcat9col
    NameConcat                      RelationType
    Person                          Repository
    Place                           Source
    PlacenameChainVW                Title
    PlacenameChainVW2col            citation_SUPERCEDED_20180705
    PlacenameChainVW3col            concatname_SUPERCEDED_20180705
    PlacenameChainVW4col            currentity_superceded_20180705
    PlacenameChainVW5col            name_SUPERCEDED_20180705
    PlacenameChainVW6col            person_SUPERCEDED_20180705
    PlacenameChainVW7col            place_superceded_20180705
    PlacenameChainVW8col            repository_SUPERCEDED_20180705
    PlacenameChainVW9col            source_SUPERCEDED_20180705
    PlacenameChainVWConcat2col      title_SUPERCEDED_20180705

EXAMPLE 3: find out what all is in a table

    sqlite> .mode column
    sqlite> .header on
    sqlite> select * from Place;
    PlaceID     Placename   PlaceFKPlaceID  PlaceNote
    ----------  ----------  --------------  ----------
    1           Earth
    2           reserved
    3           Antarctica  1
    4           Arctic Oce  1
    5           Asia        1
    6           Atlantic O  1
    7           Australia   1
    8           Central Am  1
    9           Europe      1
    10          Indian Oce  1
    11          Middle Eas  1
    12          North Amer  1
    13          Pacific Oc  1
    14          South Amer  1
    15          Philippine  5
    16          Iceland     4
    17          Ireland     9
    18          Russia      5
    19          Costa Rica  8
    20          USA         12
    21          Canary Isl  25
    22          Mindanao    15
    23          Davao del   22
    25          Spain       9
    26          Little Pan
    27          Purok 1A    26
    28          Colorado    20
    29          Greenland   4
    30          Garfield C  28
    31          Glenwood S  30
    32          334 Park D  31
    33          Swimming P  31
    34          City Park   31
    35          At Sea      10
    36          At Sea      4
    37          in the bac  48
    38          Highway 41  30
    39          1313 Mangg  27
    40          upstairs    39
    41          under the   40
    42          in a box    41
    43          in a book   42
    44          on a bookm  43
    45          in a parag  44
    46          in a sente  45
    47          in a word   46
    48          Greyhound   38
    49          Hawaii      20
    50          Gilligan's  13
    51          Africa      1
    52          Maduao

EXAMPLE 4: find out what's in a table row

    sqlite> select * from Place where PlaceID = 10;
    PlaceID     Placename     PlaceFKPlaceID  PlaceNote
    ----------  ------------  --------------  ----------
    10          Indian Ocean  1

EXAMPLE 5: set column width of results table (0 is a default and other integers are character counts)

    sqlite> .width 0 15 0 0
    sqlite> select * from Place where PlaceID = 12;
    PlaceID     Placename        PlaceFKPlaceID  PlaceNote
    ----------  ---------------  --------------  ----------
    12          North America    1

EXAMPLE 6: how to find out how a table was created

    sqlite> .schema Place
    CREATE TABLE Place (PlaceID INTEGER PRIMARY KEY, Placename TEXT, PlaceFKPlaceID
    INTEGER REFERENCES Place(PlaceID), PlaceNote TEXT);

EXAMPLE 7: find out what's in selected columns

    sqlite> select Placename, PlaceFKPlaceID from Place where PlaceID = 15;
    Placename    PlaceFKPlaceID
    -----------  ---------------
    Philippines  5

EXAMPLE 8: create a table

    sqlite> CREATE TABLE Source (SrcTypID INTEGER PRIMARY KEY, SourceType TEXT, Date DATE, SourceNote TEXT);

EXAMPLE 9: find out what columns are in a table

    sqlite> pragma table_info(Place);
    cid         name             type        notnull     dflt_value  pk
    ----------  ---------------  ----------  ----------  ----------  ----------
    0           PlaceID          INTEGER     0                       1
    1           Placename        TEXT        0                       0
    2           PlaceFKPlaceID   INTEGER     0                       0
    3           PlaceNote        TEXT        0                       0

EXAMPLE 10: insert a new row into a table (use NULL where a PK will be placed by sqlite or there's an error)

    sqlite> INSERT INTO SourceType VALUES (NULL, 'Family Tree');
    sqlite> select * from SourceType;
    1|Death Certificate
    2|Marriage License
    3|Census
    4|Burial Record
    5|Military Record
    6|Family Bible
    7|Birth Certificate
    8|Headstone
    9|Family Tree

EXAMPLE 11: copy and recreate a table (instead of dropping it and retyping everything)

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

EXAMPLE 12a: select query inner join on two columns (I couldn't get INNER JOIN syntax to work but this worked):

    SELECT person_display_name, gender, birthdate
    FROM person, current AS ma, current AS pa
    WHERE mother_fk_person = ma.current_fk_person
    OR father_fk_person = pa.current_fk_person
    ORDER by birthdate

EXAMPLE 12b: (this time the INNER JOIN syntax worked)

    SELECT landmark_types, 
            landmark_date, 
            landmark_fk_place, 
            particulars, 
            age, 
            landmark_note 
        FROM landmark 
        INNER JOIN current
        INNER JOIN landmark_type
        ON landmark.landmark_fk_person = current.current_fk_person
        AND landmark_type.landmark_type_id = landmark.landmark_fk_type
        ORDER BY landmark_date

EXAMPLE 13: add column to existing table

    sqlite> ALTER TABLE person ADD COLUMN living BOOLEAN;

EXAMPLE 14: create a foreign key column 2 ways--direct and deferred

CREATE TABLE stuff (
    stuff_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    xyz INTEGER, 
    abc INTEGER, 
    mno INTEGER REFERENCES other_stuff (other_stuff_id) ON UPDATE CASCADE ON DELETE RESTRICT, 
    FOREIGN KEY(abc) REFERENCES more_stuff(more_stuff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (xyz) REFERENCES right_stuff(right_stuff_id) ON DELETE RESTRICT ON UPDATE CASCADE);

EXAMPLE 15: insert multiple rows at once

    list_o_tups = []
    for item in new_list:
        item = (item,)
        list_o_tups.append(item)

    conn = sqlite3.connect('c:/blahblahblah.db')
    cur = conn.cursor()
    cur.executemany('''
        INSERT INTO recent_place (recent_place_id, recent_places) 
        VALUES (NULL, ?)''', 
        list_o_tups)
    conn.commit()
    cur.close()
    conn.close()

EXAMPLE 16: rename table and add primary key constraint
# NOTE: Starting with sqlite 3.25.0 the old way no longer works. The old way was to rename the table, create a new one with the old table's schema, and insert select * from the old table to the new, then drop the old table. Now, the references to the old table will be kept and nothing will work. So you have to do it the new way, which is to make the new table table_new using the old table's schema, insert select * from old to new (adding default/missing values as appropriate), drop the old, and rename the new table to the old table's name. From the internet: https://stackoverflow.com/questions/57253045/sqlite-error-no-such-table-main-table-name-exists

    copy the schema gotten this way:
	.schema table_name
           
    BEGIN TRANSACTION;

    CREATE TABLE table_name_new (...paste the old schema)

	INSERT INTO table_name_new SELECT * FROM table_name;

    DROP table_name;

	ALTER TABLE table_name_new RENAME TO table_name;     

				# old way; don't do this anymore
				ALTER TABLE cities RENAME TO old_cities;
				 
				CREATE TABLE cities (
				   id INTEGER NOT NULL PRIMARY KEY,
				   name TEXT NOT NULL);
				 
				INSERT INTO cities 
				SELECT * FROM old_cities;
				 
				DROP TABLE old_cities;
     
    COMMIT;

EXAMPLE 17: automatically insert default value in case of NULL

    CREATE TABLE book (
        book_id INTEGER PRIMARY KEY,
            book_name TEXT NOT NULL,
            price INTEGER DEFAULT 100);

    "The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes." (https://www.sqlite.org/lang_createtable.html)

EXAMPLE 18: UNIQUE constraint

The following statement creates a new table named contacts with a UNIQUE constraint defined for the email column:

CREATE TABLE contacts(
    contact_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT NOT NULL UNIQUE);

The following example inserts a new row into the contacts table:

    INSERT INTO contacts(first_name,last_name,email)
    VALUES ('John','Doe','john.doe@gmail.com');

EXAMPLE 19: give column of results table an alias heading

sqlite> select claim_types as mumboJumbo from claim_type;
    mumboJumbo
    ----------
    birth
    marriage
    offspring
    death
    burial
    occupation
    imprisonme
    deportatio
    invention
    
EXAMPLE 20: LEFT JOINS need the column with the most stuff in it 
    (or most relevant stuff in it) on the 
    left of the first LEFT JOIN keyword while the columns with possible nulls
    are to the right of that. The order of what's on left of right of ON's
    equal sign doesn't matter.

EXAMPLE 21: Get id of last autoincrement id created...

... for all tables:
    SELECT * from SQLITE_SEQUENCE;
    name|seq
    current|1
    name|65
    name_part|8
    source|5
    role|15
    claims_notes|5
    claims_roles|6
    claims_findings|5
    findings_notes|10
    findings_roles|18
    citation|6
    images_entities|60
    image|44
    date_per_tree|1
    date_test|3
    note|9
    event_type|18
    claim|8
    kin_type|14
    findings_persons|17
    name_type|17
    place|45
    finding|30
    person|20
... for a particular table:
SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'person';
    seq
    20

(this table only exists for AUTOINCREMENT and the purpose of AUTOINCREMENT is to guarantee the uniqueness of an ID through the life of the database, which means deleted PKs will not be re-used. For the better way--since AUTOINCREMENT uses more resources and is slower--see last_insert_rowid (example 39)

EXAMPLE 22: copy_table_from_old_database_to_new_sqlite
	C:\Users\Lutherman>sqlite3 c:/treebard/data/settings/global_config.db
	SQLite version 3.34.0 2020-12-01 16:14:00
	Enter ".help" for usage hints.
	sqlite> .tables
	closing_state  date_global    folder         place
	color_scheme   date_per_user  format
	sqlite> .output table_dump.sql
	sqlite> .dump color_scheme
	sqlite> .dump format
	sqlite> .dump folder

	sqlite> .quit

	C:\Users\Lutherman>sqlite3 c:/treebard/data/sample_tree/sample_tree.tbd
	SQLite version 3.34.0 2020-12-01 16:14:00
	Enter ".help" for usage hints.
	sqlite> .tables
	citation          date_global       findings_persons  note
	claim             date_per_tree     findings_roles    person
	claims_findings   date_per_user     image             place
	claims_notes      date_test         images_entities   role_type
	claims_roles      event_type        kin_type          source
	closing_state     finding           name
	current           findings_notes    name_type

	sqlite> .read table_dump.sql

	sqlite> .tables
	citation          date_global       findings_roles    note
	claim             date_per_tree     folder            person
	claims_findings   date_per_user     format            place
	claims_notes      date_test         image             role_type
	claims_roles      event_type        images_entities   source
	closing_state     finding           kin_type
	color_scheme      findings_notes    name
	current           findings_persons  name_type

EXAMPLE 23: update multiple values in one row simultaneously

	date = user_values[0][1]
	place_id = user_values[1][1]
	particulars = user_values[2][1]
	age = user_values[3][1]

	cur.execute(
		'''
			UPDATE finding 
			SET date = ?,
				particulars = ?,
				age = ?,
				person_id = ?
			WHERE finding_id = ?
		''', 
		(date, particulars, age, current_person, self.finding_id))
	conn.commit()

EXAMPLE 24: sort results table by a column
	SELECT event_type_id, event_types, couple 
	FROM event_type 
	WHERE hidden != 1
	ORDER BY event_types

EXAMPLE 25: query against values in a list when the list is constant

    SELECT * from some_table where some_id in (2,3)

EXAMPLE 26: query against values in a list when the list is a variable

    First Example names the string:
		args = [2, 3]
		sql = "SELECT * from some_table where some_id in ({seq})".format(
			seq = ','.join(['?'] * len(args)))
		cursor.execute(sql, args)

    Second Example just uses the string:
		cur.execute("SELECT notes FROM finding WHERE finding_id IN ({})".format(
			','.join('?' * couple_event_ids[1])), 
		couple_event_ids[0])

EXAMPLE 27: query against list values as well as extra values

	qlen = len(marital_event_types)
	marital_event_types.insert(0, self.current_person)
	marital_event_types.insert(0, self.current_person)
	
	sql = '''
			SELECT findings_persons_id, findings_persons.finding_id, person_id1, kin_type_id1, person_id2, kin_type_id2
			FROM findings_persons
			JOIN finding
				ON finding.finding_id = findings_persons.finding_id
			WHERE (person_id1 = ? OR person_id2 = ?) 
				AND event_type_id in ({})
		'''.format(",".join(["?"] * qlen))
	cur.execute(sql, marital_event_types)

EXAMPLE 28: get unique distinct results, no duplicates

		SELECT DISTINCT finding_id
		FROM findings_roles
		WHERE finding_id in (23, 19, 42, 45, 2)

EXAMPLE 29: count result rows with non-null value in a column

		SELECT COUNT(findings_roles_id)
		FROM findings_roles
		WHERE finding_id = ?

EXAMPLE 30: insert selected columns from one table to another:

		There's no parentheses after SELECT

	INSERT INTO place_new (place_id, places, latitude, longitude, cartesian_coordinates, township, range, section, legal_subdivision) SELECT place_id, places, latitude, longitude, cartesian_coordinates, township, range, section, legal_subdivision FROM place;

EXAMPLE 31: select greatest id from a table:

	SELECT MAX(place_id) FROM place;

EXAMPLE 32: select non-unique values (E.G. finding_id):
	(2 examples)
	SELECT finding_places_id, finding_id 
	FROM finding_places 
	WHERE finding_id IN 
		(SELECT finding_id 
		FROM finding_places 
		GROUP BY finding_id HAVING COUNT(*) > 1);

	SELECT * FROM (SELECT place_id, place_name_id, place_names FROM place_name WHERE place_names IN (SELECT place_names FROM place_name GROUP BY place_names HAVING COUNT(*) > 1)) ORDER BY place_names;

EXAMPLE 33: add database text version to git version control
SEE ALSO EXAMPLE 46 FOR A SIMPLER EXAMPLE

	(the text file created by dump is pure SQL ie all the CREATE TABLE and INSERT etc needed to reconstruct the table from scratch. Don't try to track binary files such as .db with git, I forget why because I repressed that traumatic event)
	https://stackoverflow.com/questions/17830882/whats-the-correct-way-to-deal-with-databases-in-git

	While git (just like most other version control systems) supports tracking binary files like databases, it only does it best for text files. In other words, you should never use version control system to track constantly changing binary database files (unless they are created once and almost never change).

	One popular method to still track databases in git is to track text database dumps. For example, SQLite database could be dumped into *.sql file using sqlite3 utility (subcommand .dump). However, even when using dumps, it is only appropriate to track template databases which do not change very often, and create binary database from such dumps using scripts as part of standard deployment.

	https://www.sqlitetutorial.net/sqlite-dump/
	(above link also explains how to dump specific tables)
	FIRST OPEN THE DATABASE:
		C:\sqlite>sqlite3 c:/path_to_database/db.db
		SQLite version 3.13.0 2016-05-18 10:57:30
		Enter ".help" for usage hints.
		sqlite>

	TO DUMP TO SCREEN:
		sqlite> .output
		sqlite> .dump
		.exit

	TO DUMP TO FILE:
		sqlite> .output c:/path_to_dump/db.sql
		sqlite> .dump
		.exit

	TO DUMP SCHEMA ONLY TO FILE:
		sqlite> .output c:/path_to_database/db_structure.sql
		sqlite> .schema
		.exit

	https://stackoverflow.com/questions/311691/importing-a-sqlite3-dump-back-into-the-database
	TO UNDUMP FROM TEXT TO BINARY DO THIS FROM TERMINAL (not inside sqlite tool):	
		sqlite3 my_database.sqlite < dumpfile.sql

	https://www.sqlite.org/cli.html
	Converting An Entire Database To A Text File

	Use the ".dump" command to convert the entire contents of a database into a single UTF-8 text file. This file can be converted back into a database by piping it back into sqlite3.

	A good way to make an archival copy of a database is this:
		$ sqlite3 ex1 .dump | gzip -c >ex1.dump.gz

	This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:
		$ zcat ex1.dump.gz | sqlite3 ex2

	The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:
		$ createdb ex2
		$ sqlite3 ex1 .dump | psql ex2

EXAMPLE 34: select values from table 2 which are not in table 1:

	ex. 1:

	sqlite> select person_id from person where person_id not in (select person_id from images_elements where person_id is not null) and person_id is not null;
	PERSON_ID
	7
	8
	448
	449
	450
	451
	452
	454
	455
	457
	556
	557
	5581
	5582
	5583
	5584
	5622
	5716
	5717
	5718
	5720
	5721

	ex. 2:
	sqlite> select person_id from finding where event_type_id = 1 and person_id not in (select person_id from person where person_id in (select person_id from finding where person_id is not null and event_type_id = 1) and person_id is not null);
	person_id
	81

EXAMPLE 36: Order Results Table By Multiple Columns
	This example orders by columns from two different tables.

    SELECT person_id, names, name_types, name.name_id, sort_order, used_by
    FROM name
    JOIN name_type
        ON name.name_type_id = name_type.name_type_id
    ORDER BY person_id, hierarchy

EXAMPLE 37: SQLite tries to use old values instead of current ones

	See show_top_pic() in main.py. Has something to do with manually changing a value in db?

EXAMPLE 38: Attaching a second database and using foreign keys across the two DBs in a JOIN:

# FOR EXAMPLES OF other types of queries SEE join_tables_from_different_databases_sqlite

conn = sqlite3.connect('d:/treebard/data/settings/treebard.db')
conn.execute('PRAGMA foreign_keys = 1')
cur = conn.cursor()
att = 'ATTACH DATABASE ? AS sample'
sample = 'd:/treebard/data/sample_tree/sample_tree.tbd'
cur.execute(att, (sample,))

cur.execute(
    '''
        SELECT main.event_type.event_types
        FROM sample.finding
            JOIN main.event_type
                ON main.event_type.event_type_id = sample.finding.event_type_id
        WHERE sample.finding.finding_id < 10
    ''')
results = cur.fetchall()
print(results)
# [('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]

det = "DETACH DATABASE sample"
cur.execute(det)
cur.close()
conn.close()

EXAMPLE 39: last_insert_rowid FOR GETTING LAST INSERTED ID without AUTOINCREMENT
(SEE example 21 for the way that only works with autoincrement but ?doesn't always work?)
(This ex. 39 below still works if there's autoincrement, but this is default and autoincrement is more expensive and slower. Autoincrement is also limiting since it will not re-use deleted primary keys.)

IN THE CONSOLE:
sqlite> .schema test
CREATE TABLE test (id INTEGER PRIMARY KEY, stuff TEXT);

sqlite> INSERT INTO test VALUES (4, 'dog');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
sqlite> SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'test';
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4

IN PYTHON:
cur.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, stuff TEXT)")
conn.commit()

cur.execute("INSERT INTO test VALUES (9, 'test stuff')")
conn.commit()

print(cur.lastrowid)
# output: 9

EXAMPLE 40: clear the screen in the sqlite console with `.shell cls`
 (`.shell args`... allows you to execute CMD commands in the SQLite3 shell)

EXAMPLE 41: get a list of columns in a table

	import sqlite3

	conn = sqlite3.connect("d:/00programming/code_examples_python/sqlite_select_table_names.db")
	cur = conn.cursor()
	cur.execute("create table if not exists table1 (column1 text, column2 integer)")
	conn.commit()
	cur.execute("create table if not exists table2 (column3 text, column4 integer)")
	conn.commit()
	cur.execute("PRAGMA table_info(table1)")
	table1 = cur.fetchall()
	print(table1)    
	cur.execute("PRAGMA table_info(table2)")
	table2 = cur.fetchall()
	print(table2)
	cur.close()
	conn.close()
	tables = [i[1] for i in table2]
	print(tables)

	OUTPUT:
	# cid  name              type     notnull  dflt_value  pk (HEADERS WOULD BE SHOWN IF DONE IN CONSOLE)
	[(0, 'column1', 'text', 0, None, 0), (1, 'column2', 'integer', 0, None, 0)]

	[(0, 'column3', 'text', 0, None, 0), (1, 'column4', 'integer', 0, None, 0)]

	['column3', 'column4']

EXAMPLE 42: Order by ascending is default so ASC can be omitted; to order by descenDing:

	SELECT Name, Price FROM Cars ORDER BY Price DESC;

EXAMPLE 42: Add a UNIQUE constraint to multiple columns

	CREATE TABLE IF NOT EXISTS "links_links_new" (links_links_id INTEGER PRIMARY KEY, person_id INTEGER, place_id INTEGER, nested_place_id INTEGER, place_name_id INTEGER, name_id INTEGER, source_id INTEGER, citation_id INTEGER, finding_id INTEGER, assertion_id INTEGER, project_id INTEGER, to_do_id INTEGER, contact_id INTEGER, image_id INTEGER, note_id INTEGER, repository_id INTEGER, report_id INTEGER, chart_id INTEGER, media_id INTEGER, role_type_id INTEGER, note_topic_order INTEGER, main_image BOOLEAN, locator_id INTEGER REFERENCES locator (locator_id), note_topic TEXT DEFAULT null, FOREIGN KEY (person_id) REFERENCES person (person_id), FOREIGN KEY (place_id) REFERENCES place (place_id), FOREIGN KEY (nested_place_id) REFERENCES nested_place (nested_place_id), FOREIGN KEY (place_name_id) REFERENCES place_name (place_name_id), FOREIGN KEY (name_id) REFERENCES name (name_id), FOREIGN KEY (source_id) REFERENCES source (source_id), FOREIGN KEY (citation_id) REFERENCES citation (citation_id), FOREIGN KEY (finding_id) REFERENCES finding (finding_id), FOREIGN KEY (assertion_id) REFERENCES assertion (assertion_id), FOREIGN KEY (project_id) REFERENCES project (project_id), FOREIGN KEY (to_do_id) REFERENCES to_do (to_do_id), FOREIGN KEY (contact_id) REFERENCES contact (contact_id), FOREIGN KEY (image_id) REFERENCES image (image_id), FOREIGN KEY (note_id) REFERENCES note (note_id), FOREIGN KEY (repository_id) REFERENCES repository (repository_id), FOREIGN KEY (report_id) REFERENCES report (report_id), FOREIGN KEY (chart_id) REFERENCES chart (chart_id), FOREIGN KEY (media_id) REFERENCES media (media_id), UNIQUE (note_id, note_topic, person_id), UNIQUE (note_id, note_topic, place_id), UNIQUE (note_id, note_topic, nested_place_id), UNIQUE (note_id, note_topic, place_name_id), UNIQUE (note_id, note_topic, name_id), UNIQUE (note_id, note_topic, source_id), UNIQUE (note_id, note_topic, citation_id), UNIQUE (note_id, note_topic, finding_id), UNIQUE (note_id, note_topic, assertion_id), UNIQUE (note_id, note_topic, project_id), UNIQUE (note_id, note_topic, to_do_id), UNIQUE (note_id, note_topic, contact_id), UNIQUE (note_id, note_topic, image_id), UNIQUE (note_id, note_topic, repository_id), UNIQUE (note_id, note_topic, report_id), UNIQUE (note_id, note_topic, chart_id), UNIQUE (note_id, note_topic, media_id), UNIQUE (note_id, note_topic, locator_id));

EXAMPLE 43: get case insensitive select results:

	a) a build it into the table column
		CREATE TABLE stuff (topics TEXT COLLATE NOCASE);

	b) or use it in the query:
		SELECT names FROM things WHERE names = "mIsS THinG" COLLATE NOCASE;

EXAMPLE 44: insert a range of blank records with pk only

	data = ((i,) for i in range(1, 203))
	cur.executemany("INSERT INTO family (family_id) VALUES (?)", data)
	conn.commit()

EXAMPLE 45: Join tables in an update query
	UPDATE software
	SET purchprice = c.purchprice
	FROM (SELECT purchprice FROM softwarecost) AS c
	WHERE c.id = software.id;

EXAMPLE 46: Save a new master default blank db schema (all tables) after making changes to one or more table schemas REMEMBER TO DELETE THE OLD default.db and previously exported .sql file first, the second part should be making a .db that doesn't exist. Keep the most recent .sql in data/settings and always do it this way so the most recent .sql is always a valid version of the db schema. Do this every time a change is made and a current export will always be at hand.

THIS IS ALSO how you add a database to a code repo since the .sql is a text file containing all the table schemas, so it doesn't confuse git by being a .db file the way the actual database binary file would do. THIS REPLACES EXAMPLE 33 for the most part which is about the same technique but this explains it simpler.

C:\Users\Lutherman>sqlite3 d:/treebard/data/sample_tree/sample_tree.tbd
SQLite version 3.34.0 2020-12-01 16:14:00
[... make some change[s] in the table schema[s]...]
sqlite> .output d:/treebard/data/settings/unigeds.sql
sqlite> .schema
sqlite> .exit

C:\Users\Lutherman>sqlite3 d:/treebard/data/settings/unigeds.db < d:/treebard/data/settings/unigeds.sql
Error: near line 1: object name reserved for internal use: sqlite_sequence

(I DON'T KNOW what the error is but the import of the schema seemed to work fine.)

EXAMPLE 46: Paste more than 255 characters into SQLite CLI (wasn't needed till 2024):
I learned in early May 2024 that my Windows 11 cmd.exe and Powershell won't allow pasting over 255 chars, probably due to a defect in some Windows update, and since it does work in Terminal, that proves the problem does not originate with SQLite but with Windows.
For the details see my question and answer at https://stackoverflow.com/questions/78436811/cant-paste-over-255-characters-into-sqlite-cli/78439692#78439692
and
https://sqlite.org/forum/forumpost/2f26d56e10bc78554f05c7fa707d2cdb92916d8fac03efcec99f5f1deb4230f4
and
https://sqlite.org/forum/forumpost/abeafebb5cf9c9a6

method 1:
Use Windows' tabbed Terminal cli instead of cmd.exe or Powershell. It works as expected but remember to stretch the window out to the width of the screen before typing anything since Terminal doesn't resize properly. If you do forget and resize after starting, it will still work but the typed input will appear above the sqlite> command prompt instead of where it's supposed to be. 

method 2:
Download checho.exe and put it in the PATH.
output (deleting a column from a table):
C:\Users\Lutherman>sqlite3 d:/treebard/data/sample_tree/sample_tree.tbd
SQLite version 3.45.1 2024-01-30 16:01:20 (UTF-16 console I/O)
sqlite> .schema repositories_links
CREATE TABLE repositories_links (repositories_links_id INTEGER PRIMARY KEY, repository_type_id INTEGER DEFAULT null, source_id INTEGER DEFAULT null, citation_id INTEGER DEFAULT null, repository_id INTEGER DEFAULT null, locator_id INTEGER DEFAULT null, media_id INTEGER DEFAULT null, contact_id INTEGER DEFAULT null, FOREIGN KEY (repository_type_id) REFERENCES repository_type (repository_type_id), FOREIGN KEY (source_id) REFERENCES source (source_id), FOREIGN KEY (citation_id) REFERENCES citation (citation_id), FOREIGN KEY (repository_id) REFERENCES repository (repository_id), FOREIGN KEY (locator_id) REFERENCES locator (locator_id), FOREIGN KEY (media_id) REFERENCES media (media_id), FOREIGN KEY (contact_id) REFERENCES contact (contact_id));
<<COPY ABOVE COMMAND, PASTE IT INTO EDITOR, CHANGE SOMETHING, COPY THE EDITED COMMAND>>
sqlite> begin;
sqlite> .read "|cbecho -c"
sqlite> select * from repositories_links_new;
sqlite> insert into repositories_links_new select repositories_links_id, repository_type_id, source_id, citation_id, repository_id, locator_id, media_id from repositories_links;
sqlite> select * from repositories_links_new;
repositories_links_id  repository_type_id  source_id  citation_id  repository_id  locator_id  media_id
---------------------  ------------------  ---------  -----------  -------------  ----------  --------
1                      5                   3          12           2              1
2                      5                   3                       3              2
3                      5                   3                       4              3
sqlite> drop table repositories_links;
sqlite> alter table repositories_links_new rename to repositories_links;
sqlite> commit;
sqlite> .schema repositories_links
CREATE TABLE IF NOT EXISTS "repositories_links" (repositories_links_id INTEGER PRIMARY KEY, repository_type_id INTEGER DEFAULT null, source_id INTEGER DEFAULT null, citation_id INTEGER DEFAULT null, repository_id INTEGER DEFAULT null, locator_id INTEGER DEFAULT null, media_id INTEGER DEFAULT null, FOREIGN KEY (repository_type_id) REFERENCES repository_type (repository_type_id), FOREIGN KEY (source_id) REFERENCES source (source_id), FOREIGN KEY (citation_id) REFERENCES citation (citation_id), FOREIGN KEY (repository_id) REFERENCES repository (repository_id), FOREIGN KEY (locator_id) REFERENCES locator (locator_id), FOREIGN KEY (media_id) REFERENCES media (media_id));
sqlite>

EXAMPLE 47: SELECT ONLY THE FIRST (OR 2ND OR 3RD...) RESULT THEN STOP LOOKING

SELECT place_name_id, place_id FROM place_name LIMIT 1
SELECT place_name_id, place_id FROM place_name LIMIT 3

EXAMPLE 48: SELECT KEYS THAT DON'T EXIST IN FK TABLE WHICH DO EXIST IN ANOTHER TABLE

sqlite> select person.person_id from person left join event on event.person_id = person.person_id where event.person_id is null;
person_id
---------
185
210
211
212...

EXAMPLE 49: GET LIST OF ALL TABLES USING PYTHON
        cur.execute('PRAGMA table_list')
        table_list = cur.fetchall()
[('main', 'kin_type', 'table', 3, 0, 0), ('main', 'notes_links', 'table', 13, 0, 0), ('main', 'transcription_type', 'table', 2, 0, 0), ('main', 'repository_type', 'table', 2, 0, 0), ('main', 'role_type', 'table', 2, 0, 0), ('main', 'event_type', 'table', 2, 0, 0), ('main', 'source_type', 'table', 2, 0, 0), ('main', 'place_type', 'table', 2, 0, 0), ('main', 'place_name', 'table', 3, 0, 0), ('main', 'main_tbd', 'table', 4, 0, 0), ('main', 'note', 'table', 2, 0, 0), ('main', 'locator_type', 'table', 2, 0, 0), ('main', 'place', 'table', 4, 0, 0), ('main', 'current_tbd', 'table', 6, 0, 0), ('main', 'couple', 'table', 3, 0, 0), ('main', 'repositories_links', 'table', 7, 0, 0), ('main', 'locator', 'table', 11, 0, 0), ('main', 'transcription', 'table', 5, 0, 0), ('main', 'places_types', 'table', 6, 0, 0), ('main', 'media_type', 'table', 2, 0, 0), ('main', 'source', 'table', 5, 0, 0), ('main', 'sqlite_schema', 'table', 5, 0, 0), ('main', 'name_type', 'table', 2, 0, 0), ('main', 'sqlite_sequence', 'table', 2, 0, 0), ('main', 'citation', 'table', 3, 0, 0), ('main', 'person', 'table', 2, 0, 0), ('main', 'nested_place', 'table', 10, 0, 0), ('main', 'traits_tbd', 'table', 14, 0, 0), ('main', 'repository', 'table', 2, 0, 0), ('main', 'event', 'table', 11, 0, 0), ('main', 'media_links', 'table', 12, 0, 0), ('main', 'assertion', 'table', 11, 0, 0), ('main', 'media', 'table', 5, 0, 0), ('main', 'roles_links', 'table', 4, 0, 0), ('main', 'name', 'table', 6, 0, 0), ('temp', 'sqlite_temp_schema', 'table', 5, 0, 0)]

EXAMPLE 50: Changing Output Formats (copied from sqlite.org/cli.html)
   (This portion shows how to get a tuple-like output which is pasteable for executemany inserts or use csv mode which does the same thing)
	(json mode output can be pasted to make a Python dictionary)

The sqlite3 program is able to show the results of a query in 14 different output formats:

    ascii
    box
    csv
    column
    html
    insert
    json
    line
    list
    markdown
    quote
    table
    tabs
    tcl 

	You can use the ".mode" dot command to switch between these output formats. The default output mode is "list". In list mode, each row of a query result is written on one line of output and each column within that row is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

	sqlite> .mode list
	sqlite> select * from tbl1;
	hello!|10
	goodbye|20
	sqlite>

	Type ".mode" with no arguments to show the current mode:

	sqlite> .mode
	current output mode: list
	sqlite>

	Use the ".separator" dot command to change the separator. For example, to change the separator to a comma and a space, you could do this:

	sqlite> .separator ", "
	sqlite> select * from tbl1;
	hello!, 10
	goodbye, 20
	sqlite>
