from:"Mike King"

Re: [sqlite] Random locking errors using Sqlite.NET

2014-11-04 Thread Mike King

Are you accessing data off the share as well? The Sqlite website is quite
categoric that this is not a recommended mode of operation. Also given the
database locks when a record is written it is entirely possible one user is
locking the table.

Cheers,
Mike

On Tuesday, 4 November 2014, Mike McWhinney  wrote:

> Hello,
>
> I continue to have random errors on SQLite.NET (ADO provider).  I have the
> version 3.8.6 (System.Data.SQLite.dll with version 1.0.94.0).
>
> I am getting random locking errors during the execution of my program. The
> program resides on a network and each client has a mapped share
> with full read/write access to the folder as well as the .db file used by
> SQLite.
>
> In my connection string I have the following:
>
> public static string OMconnectionString = "URI=file:oslermedicine.db;
> Default Timeout=10; Pooling=True; Max Pool Size=100;";
>
>
>
> What might I be doing wrong? I am disposing of all objects (data readers,
> tables, commands) after use.
>
> Thanks
> Mike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Grammar 101

2014-02-05 Thread Mike King

I've followed this thread with some interest and I think you are all
wrong... All discussion should be in English, that's UK English as spoken
by us Brits not the bastardisation of our glorious language that is
American English.

I put it to the list that Colour should be spelt with a U, same for
Catalogue and my personal favourite Programme.

Long live the Queen etc. (I'll get my coat) :)

On 5 February 2014 12:08, Dominique Devienne  wrote:

> On Sat, Feb 1, 2014 at 3:09 PM, Simon Slavin  wrote:
>
> > But over all, English is an acquisitive (unlike German) evolving (unlike
> > French) language.
> >
>
> Hundreds of new words in French dictionaries for 2014 (links to other
> recent years at the bottom):
>
> http://www.linternaute.com/actualite/societe-france/les-nouveaux-mots-du-dictionnaire-2014-chelou-0513.shtml
>
>
> Maybe you should evolve your opinion on French, and also brush up on your
> French to read the article of course. --DD
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Mike King

Why not show the warning on exit only if an in memory database is in use.
Likewise by default open in memory unless a path is specified.

On Monday, 10 February 2014, Richard Hipp  wrote:

> On Mon, Feb 10, 2014 at 12:51 PM, > wrote:
>
> > I second the idea of a kind of "WARNING: All your work will be lost, are
> > you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the
> > application was started by (double-)clicking on it, otherwise the warning
> > will be a nuisance when running test scripts.
> >
>
> I think I know how to detect a double-click launch versus a command-line
> launch on windows.  But I don't know how to do this, or even if it is
> possible to do, on Mac or Linux.  Anybody have any ideas?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question : how to retrieve a byte array

2014-04-29 Thread Mike King

I'm not sure this sounds like a SQLite issue but I think File.ReadAllBytes
and File.WriteAllBytes in the System.IO namespace may do what you want.

Cheers,


On 28 April 2014 22:58, Denis Bezeau  wrote:

> Hi, I am currently working on a video game using sqlite3,  and I am having
> an issue when saving the database to the platform im on. The save function
> for these platform use either a string or a byte array to save. So I need
> to
> be able to get a byte array of the database, and be able to rebuild the
> database with that byte array, or a string.
>
>
>
> So far I have not found anything that gets me the information I need, so I
> did my own Dump function, that dump all the SQL queries needed to rebuild
> the database from scratch, but it is pretty slow.
>
>
>
> I am working in c# under Unity engine.
>
> Thanks for any help I can get.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Warning when querying FTS3 table

2013-01-12 Thread Mike King

Hi All,

I'm using the latest System.Data.SQlite downloaded from the website.
When I query a FTS3 table (called FreeText) using the code below I get
the following warning in the VS output window.

SQLite error (1): no such table: main.FreeText_stat

>From reading the docs I believe FreeText_stat is created if I'm using
FTS4 which I'm not as it is not enabled in the .Net Wrapper.

Am I safe to ignore this warning? The code seems to function fine.

using (SQLiteConnection conn =
DBUtilities.OpenConnection(Path.Combine(dataFolder,
DocFetchConst.DocDataName)))
{
using (SQLiteCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT f.Content FROM FreeText f,
FreeTextData fd WHERE f.rowid = fd.FreeTextId AND fd.DocumentVersionId
= @DocumentVersionId AND fd.Page = @Page";
command.Parameters.Add(new
SQLiteParameter("@DocumentVersionId", docVerData.DocumentVersionId));
command.Parameters.Add(new SQLiteParameter("@Page", page));
return DBUtilities.GetValue(command.ExecuteScalar(), "");
}
}

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

Re: [sqlite] match on single column but with multiple value

2013-02-10 Thread Mike King

Select * from tbl where col1 in ('a', 'b', 'c')



On Sunday, 10 February 2013, e-mail mgbg25171 wrote:

> Sorry if this is a very basic question but I'm just wondering if there's a
> more elegant way of doing this
>
> select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c'
>
> i.e. selecting rows if a particular column has one of SEVERAL values.
>
> Any help much appreciated.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stemming Query

2013-02-12 Thread Mike King

Is there any way to turn stemming on/off for a FTS3 table? I create my FTS3
table as follows:

CREATE VIRTUAL TABLE FreeText USING fts3(Content TEXT NOT NULL,
tokenize=porter)

I'd like to be able to offer the option of whether to use stemming when
searching (much like some other freetext databases do) as stemming is great
for words and text but not so good for invoice or reference numbers etc.

The only way I could think of doing it is by having two freetext tables -
one with stemming and one without but this is less than ideal because of
duplication,

Anyone got any ideas?

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

Re: [sqlite] Stemming Query

2013-02-12 Thread Mike King

Thanks for a quick answer.

On Tuesday, 12 February 2013, Richard Hipp wrote:

> On Tue, Feb 12, 2013 at 5:50 PM, Mike King 
> <making1...@gmail.com<javascript:;>>
> wrote:
>
> > Is there any way to turn stemming on/off for a FTS3 table? I create my
> FTS3
> > table as follows:
> >
> > CREATE VIRTUAL TABLE FreeText USING fts3(Content TEXT NOT NULL,
> > tokenize=porter)
> >
> > I'd like to be able to offer the option of whether to use stemming when
> > searching (much like some other freetext databases do) as stemming is
> great
> > for words and text but not so good for invoice or reference numbers etc.
> >
> > The only way I could think of doing it is by having two freetext tables -
> > one with stemming and one without but this is less than ideal because of
> > duplication,
> >
> > Anyone got any ideas?
> >
>
> The index is over the stemmed words.  So if you change the stemmer (or turn
> if off completely) then you have to recreate the index.
>
> So, yes, the only way to do this is to have two tables, one with stemming
> and the other without.
>
> --
> D. Richard Hipp
> d...@sqlite.org <javascript:;>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org <javascript:;>
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS3 prefix wildcard

2013-02-22 Thread Mike King

Hi All,

A user wants to be able to query my FTS3 database using a preceeding
wildcard. From reading back through the mailing list it looks like SQLite
may not support this (for very understandable reasons) but I cannot find
any mention of this in the docs. Is this limitation?

To get round this I came up with the following, I query the FTS4aux table
first to get a list of words that match and then use this to query the FTS
table as shown below.

select offsets(text) from text where content match (select term from
ft_terms where term like '%oo%' and col = 0)
This seems to work but I appreciate it is not the most efficient thing to
do but does this look ok?

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

Re: [sqlite] Sqlite .net 4.0 provider needed

2013-03-06 Thread Mike King

Use the official System.Data.Sqlite provider.

http://system.data.sqlite.org

Cheers,

Mike



On 6 March 2013 11:44, moumita  wrote:

> Hi,
>
> I want to user sqlite .net 4.0 provider. from where get that one? Please
> help me in this regards.
>
> Thanks,
> Moumita
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Sqlite-net-4-0-provider-needed-tp67476.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite .net 4.0 provider needed

2013-03-06 Thread Mike King

Use the official System.Data.Sqlite provider.
http://system.data.sqlite.org
Cheers,
Mike


On 6 March 2013 11:44, moumita  wrote:

> Hi,
>
> I want to user sqlite .net 4.0 provider. from where get that one? Please
> help me in this regards.
>
> Thanks,
> Moumita
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Sqlite-net-4-0-provider-needed-tp67476.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write store-procedure in SQLite.net

2013-03-25 Thread Mike King

SQLite doesn't have stored procedures. If you are using a .Net language
such as C# then you can create your own custom functions which may do what
you want. See the link below for an example.

http://stackoverflow.com/questions/172735/create-use-user-defined-functions-in-system-data-sqlite



On 25 March 2013 06:05, Moumita Banerjee  wrote:

> Hi,
>
> I am trying to write a store-procedure in SQLite.net , but I am unable to
> do so. Please help me solve this problem.
>
> Thanks,
> Moumita
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible enhancement request.

2012-01-13 Thread Mike King

Hi All,

This is my first post here so please be gentle!

I'd like to make a suggestion for a future enhancement.I'm using the latest
System.Data.Sqlite and C#, I've got a small database with a freetext (FTS3)
table. When I query this table I use the Offsets function to get the
position of the matches in the record. The problem is that the offsets
returned by Offsets are byte offsets, in a future release would it be
possible to have a new function or something similar which returns
character offsets instead? (naturally depending on whether the database is
using UTF8 or 16).

I'm using the information returned by Offsets to highlight areas in a
string. If the string contains multi-byte Unicode characters then the byte
offsets do not match the position in the string. To get round this I
convert the string into a UTF8 byte array and then I can find the correct
byte positions easily and then the selected areas back to a string.

Cheers,

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

[sqlite] GROUP BY question

2012-05-24 Thread Mike King

Is this Select statement valid?  In Oracle, it wouldn't be because
what is the aggregate of A.  Is this behavior defined anywhere?

create table T (A,B);
insert into  T (A,B) values (1,3);
insert into  T (A,B) values (2,3);

select A,B
from T
group by B;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY question

2012-05-24 Thread Mike King

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

[sqlite] System.Data.Sqlite writing chunks to a BLOB

2012-10-17 Thread Mike King

Hi All,

I'm using the latest System.Data.Sqlite with c# and .Net 4.

Is there any method of writing to a BLOB in byte array chunks rather
than in one big lump? (I can see how using SQLiteDataReader GetBytes I
can read a blob back in chunks).

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

Re: [sqlite] System.Data.Sqlite writing chunks to a BLOB

2012-10-18 Thread Mike King

That's great to know but is this supported in system.data.sqlite or is
there any plans to do so?

Cheers

On Thursday, 18 October 2012, Simon Slavin wrote:

>
> On 17 Oct 2012, at 11:59pm, Mike King <making1...@gmail.com <javascript:;>>
> wrote:
>
> > I'm using the latest System.Data.Sqlite with c# and .Net 4.
> >
> > Is there any method of writing to a BLOB in byte array chunks rather
> > than in one big lump? (I can see how using SQLiteDataReader GetBytes I
> > can read a blob back in chunks).
>
> SQLite proves the equivalent routines:
>
> http://www.sqlite.org/c3ref/blob_open.html
> http://www.sqlite.org/c3ref/blob_write.html
> http://www.sqlite.org/c3ref/blob_close.html
>
> I don't know whether System.Data.Sqlite can call them but the underlying
> engine supports what you want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org <javascript:;>
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.Sqlite writing chunks to a BLOB

2012-10-18 Thread Mike King

Thanks - sorry to be a pain but is this on the roadmap for the future?
(For the thing I'm playing with this is the difference between storing
images in the database or storing them in the filesystem).

Cheers,


On 18 October 2012 11:23, Joe Mistachkin <sql...@mistachkin.com> wrote:
>
> Mike King wrote:
>>
>> That's great to know but is this supported in system.data.sqlite
>> or is there any plans to do so?
>>
>
> It's not being planned yet.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.Sqlite writing chunks to a BLOB

2012-10-18 Thread Mike King

At the moment it's difficult to tell but I envisage 3-4gb being the maximum.

Cheers,

Mike

On 18 October 2012 13:17, Joe Mistachkin <sql...@mistachkin.com> wrote:
>
> Mike King wrote:
>>
>> Thanks - sorry to be a pain but is this on the roadmap for the future?
>> (For the thing I'm playing with this is the difference between storing
>> images in the database or storing them in the filesystem).
>>
>
> Out of curiosity, what size images are you dealing with?  Unless they
> are really large, any efficiency gains from using incremental BLOB I/O
> would probably be minimal.
>
> Anyhow, I've created a ticket to track this feature request, here:
>
> http://system.data.sqlite.org/index.html/info/32d482b38b
>
> Now, it's on the roadmap; however, I'm not sure about the timeline of
> getting this implemented.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.Sqlite writing chunks to a BLOB

2012-10-18 Thread Mike King

I'd missed that - The devil is always in the detail (cue Homer Simpson "DOH!")

Thanks again and sorry to be a nuisance


On 18 October 2012 14:35, Richard Hipp <d...@sqlite.org> wrote:
> On Thu, Oct 18, 2012 at 9:03 AM, Mike King <making1...@gmail.com> wrote:
>
>> At the moment it's difficult to tell but I envisage 3-4gb being the
>> maximum.
>>
>
> The maximum BLOB size in SQLite is 1GB.  So you would do well to store your
> images in separate files.
>
>
>>
>> Cheers,
>>
>> Mike
>>
>> On 18 October 2012 13:17, Joe Mistachkin <sql...@mistachkin.com> wrote:
>> >
>> > Mike King wrote:
>> >>
>> >> Thanks - sorry to be a pain but is this on the roadmap for the future?
>> >> (For the thing I'm playing with this is the difference between storing
>> >> images in the database or storing them in the filesystem).
>> >>
>> >
>> > Out of curiosity, what size images are you dealing with?  Unless they
>> > are really large, any efficiency gains from using incremental BLOB I/O
>> > would probably be minimal.
>> >
>> > Anyhow, I've created a ticket to track this feature request, here:
>> >
>> > http://system.data.sqlite.org/index.html/info/32d482b38b
>> >
>> > Now, it's on the roadmap; however, I'm not sure about the timeline of
>> > getting this implemented.
>> >
>> > --
>> > Joe Mistachkin
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

2016-11-13 Thread Mike King

Thanks. In answer to your question a page of results is displayed in a grid
on the screen. The user selects a row. If they then change the sort order I
want to show the page that contains the selected row (and reselect it).
Each row has a unique ID and i need to know the position in the result set
so I can calculate the page.

Cheers

On Sun, 13 Nov 2016 at 23:26, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 11/13/2016 6:17 PM, Mike King wrote:
> > Sorry to reply again so soon. I'm just playing about with your query. The
> > values in Value1 and Value2 are not unique so I don't think your method
> > would work.
>
> Well, in this case, your problem is under-specified. How do you plan to
> assign a number to a row that's part of a group of rows all sharing the
> same Value2?
>
> One possible approach is to use ID to break ties:
>
> select count(*) from Test t1 join Test t2
> where t2.ID = 1 and
>(t1.Value2 < t2.Value2 or (t1.Value2 = t2.Value2 and t1.ID <= t2.ID));
>
> That's equivalent to "order by Value2, ID" in your temporary table
> approach.
> --
> Igor Tandetnik
>
> ___
> 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] How to get row number of an ID in sorted results

2016-11-13 Thread Mike King

I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text
and Value2 - Text). After doing an order by in a select query I'd like to
know the row number that contains a particular ID.

(The real world use is this: I have an application which displays paged
lists of results. If you change the sort order I'd like the application to
go to the page that contains the current selected ID. To do this I need to
know what is the position in the sorted list of the ID).

So, after some experimentation, I'm using a temporary table to hold the
ordered IDs and then getting the rowid of the row with the ID I want. (in
this example the list is sorted by Value2 and the selected ID=1):

create temporary table TempIDs as select ID from Test order by Value2;
select rowid from TempIDs where ID = 1;
drop Table TempIDs;

I know SQL light doesn't support rownum (like Oracle) but is there any way
I could simplify this using a CTE so I don't have to create the temp table?
All I really want is the number of the row with the ID in the sorted list.

Cheers,

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

Re: [sqlite] How to get row number of an ID in sorted results

2016-11-13 Thread Mike King

Sorry to reply again so soon. I'm just playing about with your query. The
values in Value1 and Value2 are not unique so I don't think your method
would work.

Cheers,


On 13 November 2016 at 19:47, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 11/13/2016 12:29 PM, Mike King wrote:
>
>> So, after some experimentation, I'm using a temporary table to hold the
>> ordered IDs and then getting the rowid of the row with the ID I want. (in
>> this example the list is sorted by Value2 and the selected ID=1):
>>
>> create temporary table TempIDs as select ID from Test order by Value2;
>> select rowid from TempIDs where ID = 1;
>> drop Table TempIDs;
>>
>
> select count(*) from Test
> where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1);
>
> --
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] How to get row number of an ID in sorted results

2016-11-13 Thread Mike King

Wow, that's clever and obvious :) Taking this a stage further, the problem
is that in the real app the user enters a query so there'll be a where
clause to content with as well as the sort order. So, I think I'll use a
CTE to build a list of the selected data and then use your query to select
a record based on the sort criteria.

Thanks for your help

On 13 November 2016 at 19:47, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 11/13/2016 12:29 PM, Mike King wrote:
>
>> So, after some experimentation, I'm using a temporary table to hold the
>> ordered IDs and then getting the rowid of the row with the ID I want. (in
>> this example the list is sorted by Value2 and the selected ID=1):
>>
>> create temporary table TempIDs as select ID from Test order by Value2;
>> select rowid from TempIDs where ID = 1;
>> drop Table TempIDs;
>>
>
> select count(*) from Test
> where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1);
>
> --
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] How to get row number of an ID in sorted results

2016-11-13 Thread Mike King

Yes spot on.I did come up with a hybrid prototype which used a binary chop
to work out what to read from the database and offset / limit to read the
pages. This was slower than the temp table.

To muddy the waters a bit thecquery is keyed in by the user so to calculate
the page in code I'd have to effectively do the same query in code surely?

Thanks again

Mike

On Sun, 13 Nov 2016 at 18:55, R Smith <rsm...@rsweb.co.za> wrote:

>
>
> On 2016/11/13 7:29 PM, Mike King wrote:
> > I have a table (test) with 3 columns (ID - auto incrementing, Value1 -
> Text
> > and Value2 - Text). After doing an order by in a select query I'd like to
> > know the row number that contains a particular ID.
> >
> > (The real world use is this: I have an application which displays paged
> > lists of results. If you change the sort order I'd like the application
> to
> > go to the page that contains the current selected ID. To do this I need
> to
> > know what is the position in the sorted list of the ID).
> >
> > So, after some experimentation, I'm using a temporary table to hold the
> > ordered IDs and then getting the rowid of the row with the ID I want. (in
> > this example the list is sorted by Value2 and the selected ID=1):
> >
> > create temporary table TempIDs as select ID from Test order by Value2;
> > select rowid from TempIDs where ID = 1;
> > drop Table TempIDs;
> >
> > I know SQL light doesn't support rownum (like Oracle) but is there any
> way
> > I could simplify this using a CTE so I don't have to create the temp
> table?
> > All I really want is the number of the row with the ID in the sorted
> list.
>
> So if I understand you correct - You use the temporary table with yet
> again a rowid of it's own and populating it using an ordered select from
> your main table, then using this table's rowid to find the list position
> of the item so that you can check its position in the select to know
> where to scroll to so that the first ID shown is the ID that was last
> navigated to. Right?
>
> If so, the best way of doing all this is in your own code. It is some
> magnitudes faster than what you do here. If for some reason you can't do
> it in code, then your temp table is the best solution because nothing
> else can give you row numbers (without some really slow self-joins) and
> there is no guarantees made by any part of SQLite (or SQL in general)
> that a query will pop out a specific row order unless dictated by an
> ORDER BY clause and that happens only AFTER the query rows are produced
> in whatever arbitrary order (so AFTER any row-numbering-scheme could
> have been queried-in).
>
> Perhaps one piece of advice I can offer is that setting the temp-table
> schemata to be created IN MEMORY rather than on disk might speed things
> up a lot (but if you need transactional or ACID integrity maintained you
> will need to switch it to disk again for normal querying, or use a
> different read-only connection with it set to MEMORY for this).
>
>
> Hope that helps.
> Ryan
>
>
>
> ___
> 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] Incremental BLOB IO

2017-03-14 Thread Mike King

Hi,

I'm trying to understand incremental BLOB IO using the latest
System.Data.Sqlite and C#.

I've got some test code working where I can execute a query and using a
data reader get a SQLiteBlob object and read the blob back. However, I'm
not clear as to how I can use incremental IO if I'm doing an Insert of a
new record.

I appreciate it's a really cheeky thing to ask, but does anybody have an
example of this?

Cheers,

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

Re: [sqlite] Many ML emails going to GMail's SPAM

2017-11-21 Thread Mike King

In the UK we’ve still got a threaded conferencing service called CIX must
be over 30 years old now (it was based on BIX / Cosy). The joke is it’s the
UKs oldest online social network :)

Another vote for a threaded forum here. I do try and keep up with the ML
but if it was threaded it would be a lot easier.

Cheers,

Mike

On Tue, 21 Nov 2017 at 17:20, Paul Sanderson 
wrote:

> What about some sort of poll.
>
> Mail lists might work but the additonal functionality offered by a forum (I
> am a member of many) makes them my choice.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic
> 
> Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 21 November 2017 at 16:43, Martin Raiber  wrote:
>
> > On 21.11.2017 17:30 John McKown wrote:
> > > On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> > > william.dr...@l3t.com> wrote:
> > >
> > >>> I really need to come up with an alternative to the mailing list.
> > >>> Perhaps some kind of forum system.  Suggestions are welcomed.
> > >>> --
> > >>> D. Richard Hipp
> > >>> d...@sqlite.org
> > >> Please, not a forum. The email list is instant, dynamic, and
> > convenient. I
> > >> don't think checking into a forum to stay current with the brisk
> > activity
> > >> here is very practical or appealing.
> > > I completely agree. The problem with a forum is mainly that it is not
> > _a_
> > > forum. It is a forum per list. Which means I spend way too much time
> > > "polling" 8 to 10 web "forums" during the day just to see if anybody
> has
> > > said anything of interest.
> >
> > I am using Discourse as community forum and I cannot really see any
> > downside to that except for the increased server requirements.
> > Individuals who want to use it like a mailing list still can do that
> > (enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
> > list:
> https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298
> >
> > ___
> > 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

Re: [sqlite] Use with Visual Studio

2018-07-09 Thread Mike King

Same here. Again, I download it from the main system.data site. No problems
at all.

Cheers

On Mon, 9 Jul 2018 at 06:57, Chris Locke  wrote:

> I use system.data.sqlite.dll (taken from here:
> https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
> with no problems in both VS 2017 Professional and VS 2017 Community.
>
> Thanks,
> Chris
>
>
> On Mon, Jul 9, 2018 at 2:47 AM Roger Schlueter  wrote:
>
> > I am considering using the .net version of SQLite but have two questions:
> >
> >  1. The documentation lists the versions of Visual Studio that are
> > supported.  VS2017 is NOT listed.  Is VS2017 supported.
> >  2. The documentation states "Due to Visual Studio licensing
> > restrictions, the Express Editions can no longer be supported."
> > (Yes, in red).  However, Microsoft no longer uses the phrase
> > "Express Edition" but rather calls the freebie version "Community".
> > Is this just semantics or does the red warning still apply to
> > Community?  Also, I am unaware of any "licensing restrictions" on
> > the Community editions that would preclude the use of SQLite.  Are
> > there such restrictions and, if so, what are they?
> >
> > Roger
> >
> > ___
> > 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

Re: [sqlite] BF Interpreter

2018-03-01 Thread Mike King

Wow. That’s impressive

On Thu, 1 Mar 2018 at 09:49, Chris Locke  wrote:

> "Thats the beauty of it.  It doesn't *do* anything."   ;)
>
>
>
> On Thu, Mar 1, 2018 at 6:55 AM, Gary Briggs  wrote:
>
> > Thanks to the help the other day with the strange concatentation result.
> >
> > I was referring to a BF interpreter I was working on, in pure SQLite SQL.
> > Well, here it is, working.
> >
> > Hopefully no-one finds this useful,
> > Gary
> >
> > WITH RECURSIVE
> >   program AS
> >  (SELECT '++[>+++>++>+++>+-]>++.>+.+++..+
> > ++.>++.<<+++.>.+++.--..>+.>.' AS p,
> > '' AS input, 3 AS width
> > ),
> >   jumpdepth AS
> >  (SELECT 0 AS idx, 0 AS jumpdepth, '' AS jumplist, NULL as jumpback,
> > NULL AS direction, p || '0' AS p, width FROM program
> >   UNION ALL
> > SELECT idx+1, CASE SUBSTR(p, idx+1, 1)
> > WHEN '[' THEN jumpdepth+1
> > WHEN ']' THEN jumpdepth-1
> > ELSE jumpdepth END,
> > CASE SUBSTR(p, idx+1, 1)
> > WHEN '[' THEN SUBSTR('000' || (idx+1), -width) ||
> > jumplist
> > WHEN ']' THEN SUBSTR(jumplist,width+1)
> > ELSE jumplist END,
> > CASE SUBSTR(p, idx+1, 1)
> > WHEN ']' THEN CAST(SUBSTR(jumplist,1,width) AS INTEGER)
> > ELSE NULL END,
> > CASE SUBSTR(p, idx+1, 1)
> > WHEN '[' THEN 'L'
> > WHEN ']' THEN 'R'
> > ELSE NULL END,
> > p, width
> >   FROM jumpdepth
> >   WHERE LENGTH(p)>=idx),
> >   jumptable(a,b,dir) AS
> >   (SELECT idx,jumpback,'L' FROM jumpdepth WHERE jumpback IS NOT NULL
> >   UNION ALL
> >SELECT jumpback,idx+1,'R' FROM jumpdepth WHERE jumpback IS NOT
> > NULL),
> >   bf(ep, p, width, defaulttapeentry, ip, dp, instruction, output, input,
> > tape) AS
> >(SELECT 0, p, width, SUBSTR('000', -width), 1, 1, '', '',
> > input, SUBSTR('00', -width)
> >FROM program
> > UNION ALL
> > SELECT ep+1, p, width, defaulttapeentry, CASE WHEN jumptable.b IS
> > NOT NULL AND
> > ((dir='R' AND CAST(SUBSTR(tape, width*(dp-1)+1, width) AS
> > INTEGER)=0)
> > OR
> >  (dir='L' AND CAST(SUBSTR(tape, width*(dp-1)+1, width) AS
> > INTEGER)!=0)) THEN jumptable.b
> >   ELSE ip+1 END,
> > CASE SUBSTR(p, ip, 1)
> >WHEN '>' THEN dp+1
> >WHEN '<' THEN MAX(dp-1,1)
> >ELSE dp END,
> > SUBSTR(p, ip, 1),
> > CASE WHEN SUBSTR(p, ip, 1)='.' THEN (output || CHAR(SUBSTR(tape,
> > (dp-1)*width+1, width))) ELSE output END,
> > CASE WHEN SUBSTR(p, ip, 1)=',' THEN SUBSTR(input, 2) ELSE input
> > END,
> > CASE SUBSTR(p, ip, 1)
> > WHEN '<' THEN CASE WHEN dp=1 THEN defaulttapeentry ||
> tape
> > ELSE tape END
> > WHEN '>' THEN CASE WHEN dp*width=LENGTH(tape) THEN tape
> ||
> > defaulttapeentry ELSE tape END
> > WHEN '+' THEN SUBSTR(tape,1,width*(dp-1)) ||
> > SUBSTR('000' || (CAST(SUBSTR(tape,width*(dp-1)+1,width) AS
> > INTEGER)+1), -width) || SUBSTR(tape,width*dp+1)
> > WHEN '-' THEN SUBSTR(tape,1,width*(dp-1)) ||
> > SUBSTR('000' || (CAST(SUBSTR(tape,width*(dp-1)+1,width) AS
> > INTEGER)-1), -width) || SUBSTR(tape,width*dp+1)
> > WHEN ',' THEN SUBSTR(tape,1,width*(dp-1)) ||
> > SUBSTR('000' || (UNICODE(SUBSTR(input,1,1))), -width) ||
> > SUBSTR(tape,width*(dp+1))
> > ELSE tape END
> >   FROM bf LEFT JOIN jumptable ON jumptable.a=ip WHERE LENGTH(p) >= ip)
> > SELECT output FROM bf ORDER BY ep DESC LIMIT 1;
> >
> > ___
> > 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

Re: [sqlite] Regarding CoC

2018-10-25 Thread Mike King

I’m more a Mr Creosote kind of guy. Wafer thin mint anybody? :)

On Thu, 25 Oct 2018 at 12:08, Petite Abeille 
wrote:

>
>
> > On Oct 25, 2018, at 12:59 PM, Mike King  wrote:
> >
> > The beer is bloody good and very strong.
>
> "Beer is proof that God loves us and wants us to be happy."
> — Benjamin Franklin, allegedly
>
> https://quoteinvestigator.com/2016/06/24/beer-wine/
>
> > Also, I’m happy to report I didn’t go up in flames when I went there and
> purchased a case :)
>
> Where is the Rabbit of Caerbannog when needed?
>
> https://en.wikipedia.org/wiki/Rabbit_of_Caerbannog
> ___
> 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

Re: [sqlite] Regarding CoC

2018-10-25 Thread Mike King

I’m a good atheist but I love the CoC. Not bothered by the religious bits
but I get the sentiment. I guess it appeals to my British sense of irony
and odd sense of humour :)

As for the Trappist beer our local Monastery (St Bernards, Whitwick) has
just started the first trappist brewery in the uk. The beer is bloody good
and very strong. Also, I’m happy to report I didn’t go up in flames when I
went there and purchased a case :)

Cheers (hic!)

Mike

On Thu, 25 Oct 2018 at 11:33, Petite Abeille 
wrote:

>
>
> > On Oct 25, 2018, at 8:33 AM, Philip Warner  wrote:
> >
> >> The second exhortation tells us that that's not enough, and we also
> have a duty to maximise pleasure.
> >
> > lol, good point. Quite the opposite of monkish orders.
>
> And yet…
>
> https://en.wikipedia.org/wiki/Trappist_beer
>
> Let SQLite be the Trappist beer of software :D
>
> ___
> 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

Re: [sqlite] Help!

2018-11-06 Thread Mike King

Hi,

Surely it’s just a case of attaching the database file as an attachment to
the e-mail.

Cheers

On Tue, 6 Nov 2018 at 19:52, am...@juno.com  wrote:

> November 6, 2018 Dear Good People: I would be most appreciative if any of
> you how know how to take a database in database (not structure) form--and
> copy and paste it into an e-mail, please explain in extremely explicit
> detail. Thanks much in advance.  Respectfully yours, Alex Stavis
> 
> Judge Judy Steps Down After 23 Years Over This Controversy
> glancence-hality.com
> http://thirdpartyoffers.juno.com/TGL3131/5be1f0f4d2d2270f42c4est02vuc
> ___
> 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

Re: [sqlite] Typo

2018-12-25 Thread Mike King

Surely I speak for all of when I say I hope you have a fantastic Christmas
Richard and thanks for everything you do. Do you ever stop though? It’s
Christmas Day so surely you deserve a mince pie and a fine single malt :)

Cheers

On Tue, 25 Dec 2018 at 11:18, Richard Hipp  wrote:

> Thanks.  Fixed here: https://www.sqlite.org/src/info/a1f50d57ce76f6c0
>
> On 12/25/18, gwenn  wrote:
> > Hi,
> > There is a typo here:
> > https://sqlite.org/session/sqlite3changeset_op.html
> >> If pbIncorrect is not NULL, then *pbIndirect is set to true
> > Should be pbIndirect instead of pbIncorrect.
> > Regards.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] Typo

2018-12-25 Thread Mike King

Sadly by the time you get here it will be over for another year. Still
seasons greetings from the UK.

On Tue, 25 Dec 2018 at 13:45, Richard Hipp  wrote:

> On 12/25/18, Mike King  wrote:
> > It’s
> > Christmas Day so surely you deserve a mince pie and a fine single malt :)
>
> Sounds great!  Send me your address and I'll be right over!  ;-)
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] SQlite.NET.chm

2019-01-09 Thread Mike King

From memory a file is marked as blocked by a hidden file stream attached to
the file. That’s why using cp removed the block as I guess it doesn’t
understand file streams.

Cheers

On Wed, 9 Jan 2019 at 21:49, Simon Slavin  wrote:

> On 9 Jan 2019, at 9:33pm, Don V Nielsen  wrote:
>
> > Is the chm file on a server? I remember this problem from years ago...I
>
> It's a long time ago, but I seem to remember being told that if I wanted
> to distribute a .chm file I should Zip it first.
>
> Simon.
> ___
> 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

Re: [sqlite] library interfering with input function when running in python console

2019-03-22 Thread Mike King

I think this is a problem with the Python SQLite wrapper you are using not
SQLite itself. Maybe a better place to ask would be on their mailing list.
Also, consider giving a bit more detail as to why it fails as this may help
them diagnose the issue.

Cheers

Mike

On Fri, 22 Mar 2019 at 15:10, Anthony-William Thibault <
awthiba...@hotmail.com> wrote:

> Hello there!
>
> Consider the following program
>
> Import sqlite3
> x = Input("Enter your name”)
> print(“Hello, ” + x)
>
> When you run the code directly with python (double click the .py file or
> choose open with python) it won’t work
>
> However, the following code will work
>
> x = Input("Enter your name”)
> print(“Hello, ” + x)
> Import sqlite3
>
> I tried running it using Anaconda and it worked, so it’s only when running
> it with the default Python compiler that I encountered this issue.
>
> I tried on 2 different computers and the problem was still occurring
>
> I’m using Python 3.7.2 and 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

Re: [sqlite] SQLite4 version

2019-05-29 Thread Mike King

Would it make sense for DRH to rename V4 to something else? For the
uninitiated it’s not always apparent that V3 is what you should be using.

Cheers

On Wed, 29 May 2019 at 14:03, Warren Young  wrote:

> On May 29, 2019, at 6:16 AM, Simon Slavin  wrote:
> >
> > There may one day be a release of SQLite4
>
> It doesn’t look like it: https://sqlite.org/src4/info/c0b7f14c0976ed5e
> ___
> 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

Re: [sqlite] RFE: allow parameters in PRAGMA statements

2019-06-11 Thread Mike King

I’ve got a similar issue. I user user_version to store a .net version
object I’ve serialised as an int. I convert them to/from text to get them
in and out using the pragma. It would be a nice to have for pragmas to
support parameters.

Cheers

On Tue, 11 Jun 2019 at 14:43, Wout Mertens  wrote:

> Hi,
>
> I am using the user_version pragma for implementing an event-handling
> database. I'd like to prepare the statement to update it, e.g. `PRAGMA
> user_version = ?`.
>
> However, sqlite3 won't let me do that, so I just run the text query every
> time with the number embedded.
>
> Not a huge problem, more of a surprise. Would be nice if it worked.
>
> Wout.
> ___
> 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

Re: [sqlite] Entity Framework Core support

2019-11-17 Thread Mike King

"The EF team is now focused on the EF Core 3.1 release, which is planned
for later this year, and on making sure that the documentation for EF Core
3.0 is complete." *1

"It needs to be clearly stated somewhere that EF 6.3 is meant only as a
tool for migrating from .NET Framework, and that EF Core is the version
that should be used." *2

"EF6 continues to be a supported product, and will continue to see bug
fixes and minor improvements." *3
"EF Core offers new features that won't be implemented in EF6 (such as
alternate
keys <https://docs.microsoft.com/en-us/ef/core/modeling/alternate-keys>, batch
updates
<https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-1.0#relational-batching-of-statements>,
and mixed client/database evaluation in LINQ queries
<https://docs.microsoft.com/en-us/ef/core/querying/client-eval>. But
because it's a new code base, it also lacks some features that EF6 has." *3

EF6 is going to be legacy, the best practice is now to use EFCore for new
projects. Is this something which you should be considering putting in your
roadmap?

Thanks,

Mike

sources:

*1
https://devblogs.microsoft.com/dotnet/announcing-ef-core-3-0-and-ef-6-3-general-availability/
*2 https://github.com/aspnet/EntityFramework6/issues/834
*3 https://docs.microsoft.com/en-us/ef/efcore-and-ef6/

On Sun, 17 Nov 2019 at 23:08, Joe Mistachkin  wrote:

>
> Mike King wrote:
> >
> > Sorry to chase an answer but is there any plans to support Entity
> Framework
> > Core in the roadmap? As I understand it the current version of EF6 is
> > probably going to be the last and it's likely that MS is going to
> recommend
> > people move over to Core.
> >
>
> Given that Entity Framework 6.3.0 was released quite recently (with .NET
> Core
> support), it seems that it should be around for a while longer.
>
> Currently, there are no specific plans to support Entity Framework Core in
> System.Data.SQLite.
>
> --
> Joe Mistachkin
>
> ___
> 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

Re: [sqlite] Entity Framework Core support

2019-11-17 Thread Mike King

Hi,

Sorry to chase an answer but is there any plans to support Entity Framework
Core in the roadmap? As I understand it the current version of EF6 is
probably going to be the last and it's likely that MS is going to recommend
people move over to Core.

Cheers,

Mike

On Fri, 15 Nov 2019 at 00:02, Mike King  wrote:

> Hi All,
>
> I can see System.Data.Sqlite supports Entity Framework 6. Are there any
> plans to support Entity Framework Core?
>
> Apologies if this is a basic question!
>
> Cheers,
>
> Mike
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Entity Framework Core support

2019-11-14 Thread Mike King

Hi All,

I can see System.Data.Sqlite supports Entity Framework 6. Are there any
plans to support Entity Framework Core?

Apologies if this is a basic question!

Cheers,

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

Re: [sqlite] Securing user supplied SQL statements in a single process

2019-12-12 Thread Mike King

I had to do something similar. I’m the end I decided on a simple subset of
SQL and then wrote a parser using a regex as the tokeniser. The output was
SQL. By doing it this way I could validate field names and make sure all
values were correctly formatted and escaped.

Cheers

On Thu, 12 Dec 2019 at 13:01, Richard Hipp  wrote:

> On 12/12/19, test user  wrote:
> > Hello,
> >
> > How can I secure user supplied SQL statements in a single process?
>
> See https://www.sqlite.org/security.html for an introduction.
>
> Other suggestions:
>
> (1) Run the process that is evaluating user-supplied SQL in a sandbox,
> where it can do no harm if it does malfunction.
>
> (2) Limit the SQL to verified users.  Do not allow SQL from anonymous
> robots on the internet.
>
> (3) Restrict the scope of SQL using the authorizer, if possible.
>
> (4) Always use the latest available version of SQLite with all the
> latest patches.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] Securing user supplied SQL statements in a single process

2019-12-12 Thread Mike King

It was a very simple cutdown SQL (think just a where clause) and the regex
was a multiline affair which picked out tokens using named groups. I then
had a function that skipped whitespace tokens and returned the next token
(group name) and the value (parsed and validated for dates and numeric
values, strings were escaped).

Secondly, my code is designed to run on many databases not just SQLite and
I don’t have access at the level you describe from a .Net driver. So using
something specific to one manufacturer is not a good idea nor is writing
custom code for each database.

All this was possible and I just added my experiences to show it is
possible with a little thought.

Cheers

On Thu, 12 Dec 2019 at 19:29, Warren Young  wrote:

> On Dec 12, 2019, at 6:08 AM, Mike King  wrote:
> >
> > ...I decided on a simple subset of
> > SQL and then wrote a parser using a regex as the tokeniser.
>
> First, [SQL is not a regular language][1], so it probably cannot be
> completely parsed by regexes.  Not by a single regex without surrounding
> logic, anyway.  There’s probably valid SQL that will pass your regex but
> give unwanted behavior.
>
> Second, you’re reinventing SQLite’s own authorizer, which runs based on
> the output of SQLite’s own well-tested SQL parsing engine.  (Which uses a
> proper parser, not a regex.)
>
> Security is not an area where you want to roll your own logic.  Use
> someone else’s well-tested solution whenever you can.
>
>
> [1]: https://stackoverflow.com/a/33415289/142454
> ___
> 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

Re: [sqlite] Securing user supplied SQL statements in a single process

2019-12-12 Thread Mike King

Now, just for giggles here's the regex. It's for a simple SQL like Where
clause query language. Hopefully it makes sense :) According to my work
colleagues I am considered weird for enjoying writing a regex and they're
certainly more fun than waiting for the election result ;) (This was for a
personal project)

Cheers,

Mike

// setup the regex for tokenising the query.
//
// i.   \G means match at the start of where the previous match finished
//
// ii.  We used named groups (?<> with names that match the TokenTypes
enum so we can tell which command has been matched
//  (except for Whitespace which when we find it we move onto the next
token)
//
// iii. The token this returns removes any separators e.g. [] for fields,
"" for string, ## for datetime. (also in a string "" indicates a double
quote)
//
// iv.  The keywords below are in descending size order so that tokens such
as NotIn don't get confused with Not or In etc. (EndOfString is the shortest
//  as it is the end of string so it's empty).
//
// v.   As well as supporting the operators <, >, >=, <=, =, <> we also
support the alternatives GT, LT, GE, LE, EQ, NE, == and !=
string pattern = $@"\G(
(?$)|
(?\s+)|
(\#(?[^\#]*)\#)|
(\[(?[^\]]*)\])|

(?({Regex.Escape(CultureInfo.CurrentCulture.NumberFormat.PositiveSign)}|{Regex.Escape(CultureInfo.CurrentCulture.NumberFormat.NegativeSign)})?\d*{Regex.Escape(CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator)}?\d+)|
(""(?([^""]|"""")*)"")|
(?Not\s*Like)|
(?Between)|
(?Not\s*In)|
(?Empty)|
(?{Regex.Escape(bool.FalseString)})|
(?Like)|
(?{Regex.Escape(bool.TrueString)})|
(?And)|
(?Not)|
(?Yes)|
(?EQ)|
(?==)|
(?GT)|
(?\>=)|
(?GE)|
(?In)|
(?LT)|
(?\<=)|
(?LE)|
(?\<\>)|
(?NE)|
(?!=)|
(?No)|
(?Or)|
(?Y)|
(?N)|
(?{Regex.Escape(bool.TrueString.Substring(0,
1))})|
(?{Regex.Escape(bool.FalseString.Substring(0,
1))})|
(?\))|
(?,)|
    (?=)|
    (?\>)|
(?\<)|
(?\())";

On Thu, 12 Dec 2019 at 20:06, Mike King  wrote:

> It was a very simple cutdown SQL (think just a where clause) and the regex
> was a multiline affair which picked out tokens using named groups. I then
> had a function that skipped whitespace tokens and returned the next token
> (group name) and the value (parsed and validated for dates and numeric
> values, strings were escaped).
>
> Secondly, my code is designed to run on many databases not just SQLite and
> I don’t have access at the level you describe from a .Net driver. So using
> something specific to one manufacturer is not a good idea nor is writing
> custom code for each database.
>
> All this was possible and I just added my experiences to show it is
> possible with a little thought.
>
> Cheers
>
>
>
> On Thu, 12 Dec 2019 at 19:29, Warren Young  wrote:
>
>> On Dec 12, 2019, at 6:08 AM, Mike King  wrote:
>> >
>> > ...I decided on a simple subset of
>> > SQL and then wrote a parser using a regex as the tokeniser.
>>
>> First, [SQL is not a regular language][1], so it probably cannot be
>> completely parsed by regexes.  Not by a single regex without surrounding
>> logic, anyway.  There’s probably valid SQL that will pass your regex but
>> give unwanted behavior.
>>
>> Second, you’re reinventing SQLite’s own authorizer, which runs based on
>> the output of SQLite’s own well-tested SQL parsing engine.  (Which uses a
>> proper parser, not a regex.)
>>
>> Security is not an area where you want to roll your own logic.  Use
>> someone else’s well-tested solution whenever you can.
>>
>>
>> [1]: https://stackoverflow.com/a/33415289/142454
>> ___
>> 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

Re: [sqlite] Entity Framework Core support

2019-11-18 Thread Mike King

I've done some reading and kind of side stepped the issue a bit by adding a
reference to System.Data.SQLite and use that connection object to connect
to the database and pass the connection to the MS SQLite driver. I can now
password encrypt databases and (from my very brief tests) it looks like I
can do the basic insert / updates I wanted to.

For future reference here's the OnConfiguring override in my DbContext.
I've commented out the password code for the moment so I can edit the
database using a tool that doesn't support encryption.

 protected override void OnConfiguring(DbContextOptionsBuilder
optionsBuilder)
{
SQLiteConnection conn = new SQLiteConnection($"Data
Source={_fileName}");
//conn.Open();
//SQLiteCommand command = conn.CreateCommand();
//command.CommandText = "SELECT QUOTE(@Password);";
//command.Parameters.AddWithValue("@Password", _password);
//string quotedPassword = (string)command.ExecuteScalar();
//command.CommandText = $"PRAGMA {(!File.Exists(_fileName) ?
"rekey" : "key")} = {quotedPassword};"; // rekey sets / changes password
//command.Parameters.Clear();
//command.ExecuteNonQuery();
optionsBuilder.UseSqlite(conn);
}
}

Cheers,

Mike

On Mon, 18 Nov 2019 at 01:24, Joe Mistachkin  wrote:

>
> Mike King wrote:
> >
> > "It needs to be clearly stated somewhere that EF 6.3 is meant only as a
> > tool for migrating from .NET Framework, and that EF Core is the version
> > that should be used." *2
> >
>
> Given the phrasing here, it sounds like this guidance is relatively new
> and/or unofficial at this point?
>
> >
> > <https://docs.microsoft.com/en-us/ef/core/querying/client-eval>. But
> > because it's a new code base, it also lacks some features that EF6 has."
> *3
> >
>
> To clarify, if it lacks features that EF6 has, it sounds a lot less like
> a porting effort and a lot more like a rewriting effort.  That does not
> necessarily mean it won't get done (at some point); however, that will
> certainly impact it's relative priority on the roadmap.
>
> --
> Joe Mistachkin
>
> ___
> 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] Hex Password with System.Data.Sqlite (.Net Core)

2020-01-02 Thread Mike King

I'm porting some code from .Net 4.8 to .Net Core 3.1 using the latest
System.Data.Sqlite. How do I change / set a database password if my
password is a byte array? (It looks like I can use Pragma Key= if my
password is text).

Many Thanks, Happy New Year and apologies if this has been asked before (I
searched the mailing list archive but couldn't find an answer).

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

[sqlite] Question about passwords in System.Data.Sqlite

2020-01-02 Thread Mike King

Hi,

This is my third attempt to send the following message to the list and each
time it gets rejected as suspected administrivia! (not sure what that is -
I guess it's a US English word but it's certainly not an English one).

I'm porting some code from .Net 4.8 to .Net Core 3.1 using the latest
System.Data.Sqlite. How do I change / set a database password if my
password is a byte array? It looks like I can use Pragma Key= if my
password is text but I use hex passwords.

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

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-02 Thread Mike King

I'll be honest I'm not sure. I'm using the whatever encryption comes with
the nuget package for Core 3.1.

Should I be using a specific encryption extension? When I used the .Net
Framework SQLite lib I always used the encryption that came with it.

Cheers

On Thu, 2 Jan 2020 at 22:57, Joe Mistachkin  wrote:

>
> Which encryption extension are you using?
>
> Sent from my iPhone
>
> > On Jan 2, 2020, at 5:48 PM, Mike King  wrote:
> >
> > Hi,
> >
> > This is my third attempt to send the following message to the list and
> each
> > time it gets rejected as suspected administrivia! (not sure what that is
> -
> > I guess it's a US English word but it's certainly not an English one).
> >
> > I'm porting some code from .Net 4.8 to .Net Core 3.1 using the latest
> > System.Data.Sqlite. How do I change / set a database password if my
> > password is a byte array? It looks like I can use Pragma Key= if my
> > password is text but I use hex passwords.
> >
> > Thanks
> > ___
> > 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

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-02 Thread Mike King

Ok thanks. As this is a hobby project I don't have any cash for commercial
extensions.

Cheers

On Fri, 3 Jan 2020 at 00:18, Joe Mistachkin  wrote:

>
> Mike King wrote:
> >
> > Should I be using a specific encryption extension? When I used the .Net
> > Framework SQLite lib I always used the encryption that came with it.
> >
>
> The CryptoAPI-based encryption included with System.Data.SQLite is a
> legacy feature, has known issues, and is officially unsupported.  It is
> being retained only for the purpose of backward compatibility with legacy
> applications that make use of it.  It has not been ported to .NET Core,
> nor has it been tested there.
>
> You might want to look into the commercial SEE extension, which does work
> with System.Data.SQLite on .NET Core for all supported platforms.
>
> --
> Joe Mistachkin
>
> ___
> 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

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-02 Thread Mike King

I tend to use my hobby code as an excuse to play with and learn the stuff
that I don’t use in the daily grind. It also helps me keep up to date
(hence .Net core 3.1). So, to answer your question encryption is important
:)

Cheers

Mike

On Fri, 3 Jan 2020 at 01:27, Simon Slavin  wrote:

> On 3 Jan 2020, at 12:44am, Mike King  wrote:
>
> > Ok thanks. As this is a hobby project I don't have any cash for
> commercial
> > extensions.
>
> So the question becomes whether you actually need encryption in your hobby
> project, or you were just using encryption because you started off with an
> encrypted database.
>
> There are free encryption extensions for SQLite.  But the world doesn't
> seem to have standardised on any one of them.  Which suggests that there's
> not one of them much better than the others.
> ___
> 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

Re: [sqlite] Question about passwords in System.Data.Sqlite

2020-01-03 Thread Mike King

This is the subject:

Hex Password with System.Data.Sqlite (.Net Core)

Very to the point I’d say :)

Cheers


On Fri, 3 Jan 2020 at 23:10, Warren Young  wrote:

> On Jan 2, 2020, at 3:47 PM, Mike King  wrote:
> >
> > ...suspected administrivia! (not sure what that is -
> > I guess it's a US English word but it's certainly not an English one).
>
> It’s not defined in any of the mainstream dictionaries I have on my phone
> — three of them, because I’m a word nerd — but it’s only a matter of time
> before it’s added.
>
> It’s appeared five times in the Corpus of Contemporary American English
> since 2001, and that’s a lagging indicator of usage in the wider culture:
>
> https://www.english-corpora.org/coca/
>
> The term was commonly used at the high water mark of Usenet, the mid
> 1990s.  There are 156 hits on faqs.org, which serves the old Usenet FAQs.
>
> There’s a crowdsourced definition here, which is correct by my
> understanding of the term:
>
> https://www.wordnik.com/words/administrivia
>
> …but to drag this back on topic for the mailing list, the definition that
> matters is this one:
>
>
> https://mailman.readthedocs.io/en/latest/src/mailman/rules/docs/administrivia.html
>
> You’d have to tell us the subject you sent twice before for us to tell you
> which GNU Mailman administrivia rule you got caught by.
> ___
> 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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

2020-03-13 Thread Mike King

As an occasional poster I like the idea of a forum as for me it's easier to
dip in and follow threads (after lots of years of using System.Data.Sqlite
I'm reluctantly moving over to using the Microsoft drivers because it
supports EF Core).

For the look and feel, it's plain but it's functional. I'm reminded of the
quote from Deng Xiaoping  "No matter if it is a white cat or a black cat;
as long as it can catch mice, it is a good cat."

Cheers,

Mike

On Thu, 12 Mar 2020 at 20:18, Richard Hipp  wrote:

> I have set up an on-line forum as a replacement for this mailing list:
>
> https://sqlite.org/forum
> https://www.sqlite.org/forum/forumpost/a6a27d79ac
>
> Please consider subscribing to the new Forum.  The intent is that the
> forum will eventually replace this mailing list.
>
> The Forum is powered by Fossil.  It has been in active use in the
> Fossil community for a couple of years, and has worked well.  See the
> second link above for more information.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

52 matches

Mail list logo