SQLite Forum

Timeline
Login

24 forum posts by user aa2e72e

2020-09-04
19:26 Edit reply: Pre-release Versions of System.Data.SQLite packages? (artifact: c54878e364 user: aa2e72e)

I started researching SpatialLite at the end of July this year and quickly realised that I needed to learn SQLite.

My first recourse is always Google Search; useful as it is, Google Search identifies hundreds of references that are irrelevant in any search. My platform is Windows and have been bitten innumerable times by code snippets that simply do not apply to Windows.

There is ample SQLite documentation; there is very little that specifically simplifies platform considerations and very few (version specific) worked examples that a learner can readily reproduce.

I have used this forum as a means to speed up the learning process; as I see it, during the learning process, there are no stupid questions just stupid answers, sometimes.

I do not have (never have had) a single social media account - I cannot abide them.

CRUD is what I need and ODBC delivers. The point about my questions is simply to make sure that I am not oblivious of any vital considerations (known to other more experienced users of SQLite who might be happy to share their insight).

Us copying and pasting info .... quite surprised to surmise that the ethic in this forum is adversarial ...

19:10 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: b0bebf39f2 user: aa2e72e)

I started researching SpatialLite at the end of July this year and quickly realised that I needed to learn SQLite.

My first recourse is always Google Search; useful as it is, Google Search identifies hundreds of references that are irrelevant in any search. My platform is Windows and have been bitten innumerable times by code snippets that simply do not apply to Windows.

There is ample SQLite documentation; there is very little that specifically simplifies platform considerations and very few (version specific) worked examples that a learner can readily reproduce.

I have used this forum as a means to speed up the learning process; as I see it, during the learning process, there are no stupid questions just stupid answers, sometimes.

I do not have (never have had) a single social media account - I cannot abide them.

CRUD is what I need and ODBC delivers. The point about my questions is simply to make sure that I am not oblivious of any vital considerations (known to other more experienced users of SQLite who might be happy to share their insight).

17:16 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: 9ecd78f40b user: aa2e72e)

I can load extension with an ODBC connection. I've only tried with mod_spatialite and do not have pre-compiled binaries for any other extension to investigate whether I can load several extensions.

When Ryan mentioned Add a User-defined function I wondered whether he was referring to the CLI or the BindFunction in System.Data.SQLite, which works thus:

BindFunction(new SQLiteFunctionAttribute("ceiling", 1, FunctionType.Scalar), (Func<object[], object>)((object[] args) => Math.Ceiling((double)((object[])args[1])[0])), null);

And I was puzzled for I have not seen any reference to this being possible in the CLI.

16:27 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: f63c2335da user: aa2e72e)

I can manage the following with an ODBC connection:

Load an external library for use in SQLite

Open the DB in Read-Only mode

Create a new DB file,

Moreover, I can

Prevent the creation of a new DB file

I am intrigued by Add a User-defined function How is this done in the CLI?

16:07 Reply: Join Question (artifact: 00898dc0a6 user: aa2e72e)
Another way

.mode column
.headers on

SELECT a.Main,
	c.Name AS Name,
	a.BACKUP,
	a.Name AS OtherName
FROM (
	SELECT *
	FROM SUPPORT a
	INNER JOIN NAMES b ON a.BACKUP = b.KEY
	) a
INNER JOIN NAMES c ON a.Main = c.KEY;

Main  Name    Backup  OtherName
----  ------  ------  ---------
10    Bob     72      Sally
106   Amanda  73      Jose
15:44 Delete reply: Join Question (artifact: 1b419abb1e user: aa2e72e)
Deleted
15:39 Delete reply: Join Question (artifact: 1ad5e3d4b0 user: aa2e72e)
Deleted
15:33 Reply: Join Question (artifact: ac6a08bbc8 user: aa2e72e)
Another way:

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT a.KEY,
   ...> a.MAIN,
   ...> a.BACKUP,
   ...> b.NAME
   ...> FROM SUPPORT a
   ...> INNER JOIN NAMES b ON a.BACKUP = b.KEY;
Key  Main  Backup  Name
---  ----  ------  -----
A    10    72      Sally
B    106   73      Jose
sqlite>
15:17 Reply: SQLITE_MAX_ATTACHED; (artifact: 42c78c3575 user: aa2e72e)

I am using the Windows 32-bit pre-compiled binary & had tried what you suggest - it does not include that property.

Any other way of finding out?

14:24 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: f452ad11c7 user: aa2e72e)

If you were to get one of the ODBC-SQLite adapters, you could use System.Data.Odbc to access a SQLite database, in the process losing some of SQLite's capability.

I have not been able to identity any instance of lost capability - can you provide an example, please?

13:32 Post: SQLITE_MAX_ATTACHED; (artifact: e6da03ceb2 user: aa2e72e)

How do I query the value of SQLITE_MAX_ATTACHED from the CLI?

2020-09-03
22:45 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: 47d4f7c75b user: aa2e72e)

Then, why does the cloud (Azure, AWS & the like) exist?

21:24 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: dba979eee2 user: aa2e72e)

I note your point (although I do not like several versions of SQLite co-existing in the same environment) but what I had in mind was using the CLI for exploring/fine-tuning development pathways or options.

Doesn't each release of SQLite (SQLIte,DLL) have its own version of the CLI (SQLite.EXE)?

21:15 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: 368196d0d2 user: aa2e72e)
  1. If High Concurrency were a priority requirement, I would not include SQLite as an option.
  2. As yet, I have done nothing with SQLite across networks but I intend to find out if I can use SQLite databases stored in the cloud. (backups and fail-over issues will be taken care of).

I do not think I understand this

That is like asking about using a power drill versus an extension cord.

(I can't see how power drills and extension cords are substitutes!)

CRUD is basically 90% of what I need; in that respect, the ODBC route delivers.

I can manage the remaining 10% using the CLI (I am still finding my feet regarding the cope of this).

20:37 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: 7fd45baa3a user: aa2e72e)

For any real world application, I would

  1. Not want to be more than two versions behind the current SQLite release.
  2. Want to have an environment for testing the next or work-in-progress version of SQLite.

I have no control over what version of SQLite is available in System.Data.SqLite or Microsoft.Data.Sqlite.

Being in control of what version to use can make a world of difference.

  1. An enhancement such as .read '| currently in the pipeline can save hours of work.
  2. So would an enhancement to ALTER TABLE that permits a column to be dropped.

Using the ODBC connection, I can choose which version of SQLite I use. However some useful features are unavailable:

a. SQLiteConfigDbOpsEnum is unavailable - I will need to construct/build this.

b. SetConfigurationOption is unavailable - I am not sure how I would implement something like

SetConfigurationOption(SQLiteConfigDbOpsEnum.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, true);

c. BindFunction is unavailable; this would be a very valuable feature as it enables any method in a DLL (including ones I write myself) to be bound and used inside SQL statements.

d. EnableExtensions is unavailable (for loading such like mmod_spatialite - but I have a workaround.

The file sqlite3.def simply lists the names of the exported functions. It does not provide the methods' signatures which must be known to be able to build SQLite.Interop.dll I believe.

The choice is not easy!

16:32 Reply: Pre-release Versions of System.Data.SQLite packages? (artifact: 0c60a5e3e3 user: aa2e72e)

I started researching with System.Data.SQLite and then stumbled on Microsoft.Data.SQLite.

I am pondering the following questions:

  1. Why not simply use Microsoft.Data.Odbc? This makes for a smaller footprint application.
  2. What do the SQlite packages offer that are beyond Microsoft.Data.Odbc?

Thoughts?

2020-09-02
15:26 Reply: Dot Command - Square Brackets (artifact: 18a5eda275 user: aa2e72e)

Why does this work without double quotes?

.schema 1980-1982_F

14:56 Post: .dump (artifact: d1efafe329 user: aa2e72e)

The command .dump ?TABLE? Render database content as SQL creates scripts such as

CREATE TABLE IF NOT EXISTS "1980-1982_F" ...

and the SQL for populating the table as

INSERT INTO "1980-1982_F" VALUES(.....)

If I want to revert to the version at the time I created the script, the script will fail when I've added a column to the table because

  • INSERT INTO omits the enumeration of field names i.e. (f1,...,fn)
  • The table is not dropped

Wish:

  • EITHER DROP TABLE IF EXISTS "1980-1982_F" followed by CREATE TABLE "1980-1982_F" ...
  • OR the (enumeration of columns) after INSERT INTO

Perhaps another argument to select DROP before CREATE OR INSERT (... Field enumeration...)?

14:20 Post: Dot Command - Square Brackets (artifact: d400bc618a user: aa2e72e)

My understanding that SQL names that

  1. are SQL keywords OR
  2. contain special character such as space or hypen
need to be wrapped in square brackets. Double quotes are sometimes used instead of square brackets.

The following work:

  1. .schema 1980-1982_F
  2. .schema "1980-1982_F"
but
  • .schema [1980-1982_F]
returns nothing i.e. fails.

Likewise for .dump

Is this inconsistency an -ism?

This statement

.schema 1980-1982_F

ought to return an error since 1980-1982_F contains special characters.

2020-09-01
19:43 Reply: Which one - System.Data.SQLite or Microsoft.Data.SQLite (artifact: a82fa0fbce user: aa2e72e)

Thanks. I've started separate projects with each package and

SELECT SQLITE_VERSION();

confirms your statement relating to the respective version of SQLite in use by each package. Also, I have looked at the comparison.

System.Data.SQLite seems to have a smaller footprint than Microsoft.Data.SQLite.

Is there any difference in the SQL dialect of these packages?

13:54 Reply: UPDATE x AS y: alias invalid in "SET" expr (artifact: 38440e60b6 user: aa2e72e)

The sql statements got joined - it should be

select 1 as id, 100 as value into #tmp;

update a set a.value = 2000 from #tmp a where a.id = 1;

13:51 Reply: UPDATE x AS y: alias invalid in "SET" expr (artifact: 48bddb763d user: aa2e72e)

SQL Server supports it.

select 1 as id, 100 as value into #tmp update a set a.value = 2000 from #tmp a where a.id = 1;

Useful when you update a table using an inner or left join with another table and the new values come from the second table.

08:09 Post: Which one - System.Data.SQLite or Microsoft.Data.SQLite (artifact: 75477b2477 user: aa2e72e)

Which one is preferable/recommended for use with C#?

08:06 Reply: System.Data.SQLite (artifact: 9f1a73a355 user: aa2e72e)

On second thoughts, I am not going to replace the DLLs buried in other applications such as Dell Update, NotePad++ etc.

If I were to do so, I would have taken at least one image of my disks beforehand to ensure that I could restore to an earlier position.