[sqlite] After column add, what should be done to update the schema?

[Andy KU7T]

Hi,

I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite 
Expert, the DDL is not reflected. Even though the column is created. I read 
somewhere that the DDL is really just a copy of the text when teh table was 
created. Is there a way to refresh this?

Thanks,
Andy

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sql update script. check for existing rows before inserting...

[Andy KU7T]

Hi,

I would like to write a script that checks whether certain records already 
exist, and if not, insert them. If they do exist, it should be a no op.

I am trying this:

IF (SELECT COUNT(*) FROM [Antennas]) = 0
BEGIN
  /* Table data [Antennas] Record count: 16 */
  INSERT OR REPLACE INTO [Antennas]([Code], [Antenna], [Bands], [Ports], 
[Offset], [Bidirectional]) VALUES(0, '', '', null, null, '0');
  -- a few of those...
END;

However, I am getting a syntax error near IF. Any ideas where my error is?

Thanks
Andy

Sent from Mail for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Andy KU7T]

I admit I do not fully understand all the arguments. I am running on Windows. 
Are you saying the PRNG on Windows is not good enough to use randomblob(16) in 
Sqlite? All I need is a reasonable assurance that is are unique...

Andy

Sent from my T-Mobile 4G LTE Device
Get Outlook for Android


From: sqlite-users  on behalf of 
Rowan Worth 
Sent: Thursday, February 20, 2020 7:00:20 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Is randomblob(16) a good guid generation across multiple 
computers?

On Fri, 21 Feb 2020 at 03:59, Jens Alfke  wrote:

> > On Feb 20, 2020, at 10:48 AM, Richard Hipp  wrote:
> >
> > That assumption is not correct for SQLite, which does you a
> > cryptographically strong PRNG.  And the SQLite PRNG is seeded from
> > /dev/random on unix.
>
> Not quite; I'm looking at the function unixRandomness() in SQLite 3.28.
> It's seeded from /dev/urandom, which on Linux "will produce lower quality
> output if the entropy pool drains, while /dev/random will prefer to block
> and wait for additional entropy to be collected." (I'm quoting the macOS
> man page, which goes on to say that on macOS it always returns high-quality
> randomness.)
>

There are a lot of myths in this area, but from what I gather /dev/urandom
is totally fine for cryptographic purposes in modern linux, and any
advantages of /dev/random are highly overstated.

https://www.2uo.de/myths-about-urandom/
 -Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Andy KU7T]

Hi,
I added a randomblob(16) to each record of a Sqlite table via a trigger with 
the goal of global uniqueness. Is that the correct approach or would it be 
better to pass Guid from .Net? I am using System.Data.Sqlite. The following 
article got me questioning the usage of randomblob: 
https://devblogs.microsoft.com/oldnewthing/20120523-00/?p=7553

Thanks
Andy

Sent from my T-Mobile 4G LTE Device
Get Outlook for Android
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Andy KU7T]

Thanks for the response. Do you suggest me keeping storing TEXT (and declaring 
the column as TEXT instead of GUID) or storing and declaring BLOB (and remove 
HEX)?

I think both cases would work, but for performance, does it matter?  AS long as 
my index is binary collated, it probably does not, or does it?

Andy

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10


From: sqlite-users  on behalf of 
Radovan Antloga 
Sent: Friday, February 14, 2020 2:25:01 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Searching by Guid index in table (.NET)? I can write and 
read, but not search by the Guid

In SQLite type guid does not exist. Look at this documentation:
https://www.sqlite.org/datatype3.html

Affinity for guid would be BLOB but in trigger you store TEXT.
I do not use .NET but I think you should use different parameter
type. I would use TEXT type.

Regards R.A.


On 14.02.2020 15:09, Andy KU7T wrote:
> Hi,
>
> I am having trouble searching for a Guid. I think some conversion is missing. 
>  I wonder if someone can point me to the error…
>
> Column:
> [ID] GUID(16)
> Index:
> CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC);
> Trigger:
> CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN 
> ([NEW].[ID] IS NULL)
> BEGIN
>UPDATE
>  [DXLOG]
>SET
>  [ID] = HEX (RANDOMBLOB (16))
>WHERE
>  [NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID];
> END;
>
> Inserting a row simply will create a new guid for me. Good. Reading it like 
> this:
>
> SELECT ID FROM [Dxlog] WHERE RowId = @RowID
>
> Where @RowId is the LastInsertedRowId.
> And getting it in code:
>
> Dim rdr2 As SQLiteDataReader = Nothing
> rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", 
> sqlParameters)
> rdr2.Read()
> Me.mvarId = rdr2.Item("ID")
>
> The returned ID is indeed a Guid. In SQLite it looks like this:
>
> 40FD6722384053ED3BA45CD1C5FDB30D
> And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d}
>
> Now, when I am trying to search for it like this, I get nothing:
>
>  sqlParameters = New List(Of SQLiteParameter)()
>  AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id)
>
>  rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE 
> ID = @ID", sqlParameters)
>  rdr2.Read()
>
>
> Do I have to converte the Guid to binary somehow?
>
> Thanks
> Andy
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Andy KU7T]

Hi,

I am having trouble searching for a Guid. I think some conversion is missing.  
I wonder if someone can point me to the error…

Column:
[ID] GUID(16)
Index:
CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC);
Trigger:
CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN 
([NEW].[ID] IS NULL)
BEGIN
  UPDATE
[DXLOG]
  SET
[ID] = HEX (RANDOMBLOB (16))
  WHERE
[NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID];
END;

Inserting a row simply will create a new guid for me. Good. Reading it like 
this:

SELECT ID FROM [Dxlog] WHERE RowId = @RowID

Where @RowId is the LastInsertedRowId.
And getting it in code:

Dim rdr2 As SQLiteDataReader = Nothing
rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", 
sqlParameters)
rdr2.Read()
Me.mvarId = rdr2.Item("ID")

The returned ID is indeed a Guid. In SQLite it looks like this:

40FD6722384053ED3BA45CD1C5FDB30D
And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d}

Now, when I am trying to search for it like this, I get nothing:

sqlParameters = New List(Of SQLiteParameter)()
AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id)

rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID = 
@ID", sqlParameters)
rdr2.Read()


Do I have to converte the Guid to binary somehow?

Thanks
Andy

Sent from Mail for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Andy KU7T]

Ok, your reasoning sounds good.

I am struggling to come up with a SQL statement that runs a few expressions 
conditionally by a book expression. Can you help me get going?

Something like:

IF isnull(version) THEN
-- create table, indexes etc.

ELSE
-- make incremental changes

END IF


I'm getting a syntax error 'near IF'

Thanks
Andy
Sent from my T-Mobile 4G LTE Device
Get Outlook for Android<https://aka.ms/ghei36>

From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Saturday, February 8, 2020 9:42:25 AM
To: SQLite mailing list 
Subject: Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

On 8 Feb 2020, at 3:10pm, Andy KU7T  wrote:

> I'm looking for best practices on when and where to create/initialize your 
> tables when using SqlLite.Net as well as how to properly handle schema 
> changes from one version to another.

I urge you not to use CreateTable, but instead to use SQL commands to make and 
convert tables.  That way anyone who knows SQL will understand what you're 
doing, and you can more easily transfer your code to something other than .net.

In terms of version control, SQLite has space to store a version number which 
is ideal for that:

<https://sqlite.org/pragma.html#pragma_user_version>

You can store whatever integer you like in there and recall it whenever you 
want.  Put a 1 in there for the first version of your program.  If you ever 
need to make schema changes, start using it to keep track of your schema 
versions.

If you would prefer not to use a SQLite-only facility, you might create a 
configuration table, with key and value rows, and have a row of that table 
indicate your schema version.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SqlLite.Net: Tables creation and schema changes strategies

[Andy KU7T]

Hello,

I'm looking for best practices on when and where to create/initialize your 
tables when using SqlLite.Net as well as how to properly handle schema changes 
from one version to another.

It seems I could roll my own using large SQL files or use some library that can 
do the updates for me…
I have found some code on the internet but I do not seem to have the right 
references:


Core.Instance.dbConn = new SQLiteConnection (Core.Instance.DatabaseName, true);

Core.Instance.dbConn.CreateTable ();

Where can I find the CreateTable, and is it re-entrant, and so it can run on 
every launch of my program?  Any help is appreciated.

Thanks
Andy

Sent from Mail for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What does output from integrity check mean and how can I prevent this?

[Andy KU7T]

Hi,

Using sqlite dbs for my program (official .NET sqlite.interop.dll, v. 
1.0.92.0). Have not had any db integrity issues until today. One user send me a 
db, I ran Sqlite Expert professionals's integrity checker and got this:

*** in database main ***
On tree page 2 cell 14: 2nd reference to page 53
Page 34 is never used
Page 37 is never used
Page 54 is never used


Seems like some references out of whack.

How could this be happening?  Disk failure, power failure, etc?
How can this be detected at run time (can I do the integrity check at run 
time?)?
The tool was able to fix it, is that also something that is available as an api?

Thanks
Andy

[sqlite] Next version of System.Data.Sqlite bug fixes only or new features?

[Andy (KU7T)]

I have been reading there are a few issues with v 95, so I am waiting for
the next. Will there be a version that has only bug fixes or new features as
well? As many of you know, new features usually also create regressions.



Thanks

Andy

[sqlite] Changing Page_size fails sometimes, retry succeeds but is messy. Ideas?

[Andy (KU7T)]

Hi,

 

I determined that I would like to upgrade my page_size from 1k to 4k during
start up time if it is not done yet. At the time I would like to do that, no
other db access is occurring yet. Still, I see failures that often the
page_size returned at the end in the verification step is still the old one.
Doing a simple retry succeeds. Does it somehow take a while for a page_size
change to go into effect or what else can be done to make this work
correctly?  The steps I am executing right now are (in WAL mode):

 

Pragma wal_checkpoint

Vacuum

Pragma journal_mode=Delete

Pragma page_size=4096

Vacuum

Pragma journal_mode=WAL

Pragma page_size (verification step that often returns 1024 even though it
should return 4096)

 

Any ideas what I am doing wrong. Do you have to exclusively lock or wait
somewhere?

 

Thanks

Andy

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When to run ANALYZE

[Andy (KU7T)]

Hi,

 

I added a call to both:

 

ANALYZE

ANALYZE sqlite_master 

 

To my app at timely intervals. We are also running in WAL mode. Do I need to
do a WAL checkpoint (pragma wal_checkpoint) before running the index recalc
or not?

 

Thanks

Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

[Andy (KU7T)]

Hi,

 

I am using System.Data.SQLite, Version=1.0.92.0. I read that increasing the
page_size to 4k on modern OS is a good thing and should speed things up.
However, I have a particular query that takes substantially longer. I tried
to make sure that nothing else is changed, so I am a little puzzled. Can
anyone explain why this is? Query is this, in case this gives  a clue:

 

REPLACE INTO PacketSpots 

(Call, TS, Band, Freq, QSXFreq, Comment, Spotter, Bearing, CountryPrefix,
Sect, ZN, Exchange1, GridSquare, MiscText, Name, NR, Continent, Sunrise,
Sunset, Latitude, Longitude, QualityTag) 

VALUES

(@Call, @TS, @Band, @Freq, @QSXFreq, @Comment, @Spotter, @Bearing,
@CountryPrefix, @Sect, @ZN, @Exchange1, @GridSquare, @MiscText, @Name, @NR,
@Continent, @Sunrise, @Sunset, @Latitude, @Longitude, @QualityTag)

 

SqliteParameters not show.

 

The query used to be sub 1 ms, now it is 28 ms. Since I get quite a few
calls per second, this is a big change.

 

Anyone any ideas or tips?

 

Thanks

Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Increase page_size on existing db with data and WAL

[Andy (KU7T)]

Hi,

 

When I read this: http://sqlite.org/pragma.html#pragma_page_size it seems to
argue that I can never change the page_size on a non-empty db that is
running in WAL mode.  Any tips how I can still do this?

 

Thanks

Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.Sqlite, not using parameterized query, how to encode special characters

[Andy (KU7T)]

Hi,

 

Some of my single row inserts use parameterized queries today to make
encoding easier. For example having embedded single quotes in strings etc.  

 

Now, I would like to take advantage of the multi row inserts as described in
http://stackoverflow.com/questions/16055566/insert-multiple-rows-in-sqlite.
There seems to be a limit of the number of SqliteParameters that I can pass
in, so it seems parameterized queries are not the way to go with multi row
inserts. If I just build the sql string myself, how can I solve encoding
issues as mentioned above?

 

Thanks

Andy

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users