Re: [sqlite] [sqlite-dev] Final preparations for the release of System.Data.SQLite v1.0.92.0 have begun...

[Joe Mistachkin]

Stefano Ravagni wrote:
>
> In addiction..what change in backward compatybilities ? I think nothing...
> Who use datareader with "while .read do " continue to use in same 
> waybut many others developer, who use HasRows for check if 
> datareader has or had records will be gratified to this new 
> capacity...don't think ??
>  

I have figured out a backwards compatible way to enable this behavioral
change.

It involves modifying the connection string used when creating the
associated connection by modifying [or adding] the Flags connection
string property to include the StickyHasRows value, e.g.:

"Data Source=:memory:;Flags=StickyHasRows;"

Currently, these changes can be seen on the stickyHasRows branch, here:

https://system.data.sqlite.org/index.html/timeline?r=stickyHasRows

Would this satify your requirements?

--
Joe Mistachkin

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

Re: [sqlite] System.Data.SQLite Deployment Mystery

[Joe Mistachkin]

dpybus wrote:
> 
> I have an identical problem. I cannot deploy an app which uses either Net
4.5
> or 4.5.1 with the appropriate sqlite dll. It can be fixed by installing
the
> sqlite package on the target computer.
> 

Generally, there are three types of issues with System.Data.SQLite
deployment:

1.  Attempting to use the native interop assembly (or native library)
without
the necessary Microsoft Visual C++ Runtime Libraries installed.

2.  Attempting to use the 32-bit native interop assembly (or native library)
in a 64-bit process or vice-versa.

3.  Loading the managed-only System.Data.SQLite assembly in such a way that
it
cannot locate its associated native interop assembly (or native
library).
With the introduction [and refinement] of the "native library
pre-loading"
feature, this frequency of this issue has declined significantly.  One
way
to see this type of issue is to install the managed-only
System.Data.SQLite
assembly in the GAC without making the associated native interop
assembly
available somewhere in the PATH.

In this case, I suspect the problem is #1.  Running the free Dependency
Walker
tool (from http://dependencywalker.com/ ) against the native interop
assembly
("SQLite.Interop.dll") on the target machine should reveal if that is the
case.

If that does turn out to be the case, it can normally be solved by
installing
one of the packages from:

https://support.microsoft.com/kb/2019667

--
Joe Mistachkin

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

Re: [sqlite] Execute Error Notification

[Simon Slavin]

On 16 Mar 2014, at 7:16pm, RSmith  wrote:

> My question is though, is there a way to learn somehow whether a rollback or 
> constraint violation happened during the whole execute process which caused a 
> rollback?
> 
> Kind of like saying:  "Yes I know the script executed fully, but was there a 
> mess-up at some point?"

Do something trivial that can't fail sometime during the script.  Like create a 
VIEW called 'succeeded'.  If you committed, the VIEW will be there.

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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi again,

I confess I have difficulties to diggest your code.

>From your experience to reach this success, would you have some hints for
SQLite team that would allow the code to become more easy to read and stay
performant ?

Example :
- a non-performant implementation of 'this' required 'that' complex
workaround,
- a non-availabe syntax for 'this' required 'that' other  complex syntax to
workaround,
- ...

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

[sqlite] Execute Error Notification

[RSmith]

I use quite a few script-type sql lists to achieve some functions in some systems, by which I basically mean I make up a list of SQL 
statements and then just send them to the very convenient sqlite3_exec() function when I'm pretty sure I cannot do it any faster by 
individual steps or speed is no concern...


A typical script might look like this:
-- --
-- Alter Table masterOrderData after Importing

BEGIN TRANSACTION;
DROP TABLE IF EXISTS `TempAlteringTable`;
ALTER TABLE masterOrderData RENAME TO `TempAlteringTable`;

CREATE TABLE `masterOrderData` (
  `PurchaseOrder` TEXT PRIMARY KEY COLLATE NOCASE,
  `MStockCode` TEXT COLLATE NOCASE,
  `MDescription` TEXT,
... other irrelevant columns omitted 
) WITHOUT ROWID;

INSERT OR ROLLBACK INTO `masterOrderData` (PurchaseOrder, MStockCode, 
MDescription, etc.)
  SELECT PurchaseOrder, MStockCode, MDescription, etc. same as above...)
FROM `TempAlteringTable`;

DROP TABLE `TempAlteringTable`;
COMMIT;
-- --

Basically that master table gets created sometime before this script from an import via another DB and with automatic columns as 
just Text columns, so I run the script to change the columns to have better definitions and Primary Key etc. etc..  It all works 
perfectly.


Sometimes though, the imported table might have duplicate PurchaseOrder No's or such, some or other reason the Insert fails and 
rolls back, which it does perfectly.


However, since I just push the whole script to the sqlite3_exec() function, it replies with SQLITE_OK always... even if a rollback 
occured, which is probably correct since it did what I asked it to do, and it did it correctly... My question is though, is there a 
way to learn somehow whether a rollback or constraint violation happened during the whole execute process which caused a rollback?


Kind of like saying:  "Yes I know the script executed fully, but was there a mess-up 
at some point?"

I'm being lazy here and do not want to check the tables after each script, so 
hoping there is a way...

Thank you kindly,
Ryan


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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[E.Pasma]

Op 16 mrt 2014, om 16:45 heeft big stone het volgende geschreven:


Hi Edzard,

I just reproduced your test.

Indeed :
- you probably blew-up everything running SQL sudoku on this planet :
  . 'hardest1' in under 2 seconds on my machine,
  . 'eastermonster1' in 43ms.
- with Norvig's method and available SQLite syntax.

Each of these feats is jaw-dropping.


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


Thanks a lot,

just some nuance, (being Python adept myself) here is a summary of my  
timings. From left to right:  the time in seconds for Norvig's Python  
script and my SQL script in two versions, without and with ordering  
the digits by most used.


sudoku  python  sql sql+zsort

easy1   .067.068.069

sqlite1 .082.095.197

hard1   445.900..085

hola1   .058.075.084

eastermonster1  .259.762.079

hardest1.2822.066   3.990

As the difference is in milliseonds I changed the Python script to  
include the printing of the output in the timing. Below is this version


## Solve Every Sudoku Puzzle

## See http://norvig.com/sudoku.html

## Throughout this program we have:
##   r is a row,e.g. 'A'
##   c is a column, e.g. '3'
##   s is a square, e.g. 'A3'
##   d is a digit,  e.g. '9'
##   u is a unit,   e.g. ['A1','B1','C1','D1','E1','F1','G1','H1','I1']
##   grid is a grid,e.g. 81 non-blank chars, e.g. starting with '. 
18...7...
##   values is a dict of possible values, e.g. {'A1':'12349',  
'A2':'8', ...}


def cross(A, B):
"Cross product of elements in A and elements in B."
return [a+b for a in A for b in B]

digits   = '123456789'
rows = 'ABCDEFGHI'
cols = digits
squares  = cross(rows, cols)
unitlist = ([cross(rows, c) for c in cols] +
[cross(r, cols) for r in rows] +
[cross(rs, cs) for rs in ('ABC','DEF','GHI') for cs in  
('123','456','789')])

units = dict((s, [u for u in unitlist if s in u])
 for s in squares)
peers = dict((s, set(sum(units[s],[]))-set([s]))
 for s in squares)

 Unit Tests 

def test():
"A set of tests that must pass."
assert len(squares) == 81
assert len(unitlist) == 27
assert all(len(units[s]) == 3 for s in squares)
assert all(len(peers[s]) == 20 for s in squares)
assert units['C2'] == [['A2', 'B2', 'C2', 'D2', 'E2', 'F2', 'G2',  
'H2', 'I2'],
   ['C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7',  
'C8', 'C9'],
   ['A1', 'A2', 'A3', 'B1', 'B2', 'B3', 'C1',  
'C2', 'C3']]
assert peers['C2'] == set(['A2', 'B2', 'D2', 'E2', 'F2', 'G2',  
'H2', 'I2',
   'C1', 'C3', 'C4', 'C5', 'C6', 'C7',  
'C8', 'C9',

   'A1', 'A3', 'B1', 'B3'])
print 'All tests pass.'

 Parse a Grid 

def parse_grid(grid):
"""Convert grid to a dict of possible values, {square: digits}, or
return False if a contradiction is detected."""
## To start, every square can be any digit; then assign values  
from the grid.

values = dict((s, digits) for s in squares)
for s,d in grid_values(grid).items():
if d in digits and not assign(values, s, d):
return False ## (Fail if we can't assign d to square s.)
return values

def grid_values(grid):
"Convert grid into a dict of {square: char} with '0' or '.' for  
empties."

chars = [c for c in grid if c in digits or c in '0.']
assert len(chars) == 81
return dict(zip(squares, chars))

 Constraint Propagation 

def assign(values, s, d):
"""Eliminate all the other values (except d) from values[s] and  
propagate.
Return values, except return False if a contradiction is  
detected."""

other_values = values[s].replace(d, '')
if all(eliminate(values, s, d2) for d2 in other_values):
return values
else:
return False

def eliminate(values, s, d):
"""Eliminate d from values[s]; propagate when values or places <=  
2.
Return values, except return False if a contradiction is  
detected."""

if d not in values[s]:
return values ## Already eliminated
values[s] = values[s].replace(d,'')
## (1) If a square s is reduced to one value d2, then eliminate  
d2 from the peers.

if len(values[s]) == 0:
return False ## Contradiction: removed last value
elif len(values[s]) == 1:
d2 = values[s]
if not all(eliminate(values, s2, d2) for s2 in peers[s]):
return False
## (2) If a unit u is reduced to only one place for a value d,  
then put it there.

for u in units[s]:
dplaces = [s for s in u if d in values[s]]
if len(dplaces) == 0:
return False ## Contradiction: no place for this value
elif 

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi Edzard,

I just reproduced your test.

Indeed :
- you probably blew-up everything running SQL sudoku on this planet :
   . 'hardest1' in under 2 seconds on my machine,
   . 'eastermonster1' in 43ms.
- with Norvig's method and available SQLite syntax.

Each of these feats is jaw-dropping.


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

Re: [sqlite] System.Data.SQLite Deployment Mystery

[dpybus]

I have an identical problem. I cannot deploy an app which uses either Net 4.5
or 4.5.1 with the appropriate sqlite dll. It can be fixed by installing the
sqlite package on the target computer.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/System-Data-SQLite-Deployment-Mystery-tp71752p74566.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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[E.Pasma]

Hello

The SQLite version is 3.8.4 by now and this is stil about the sudoku  
solving. I studied Norvigs algorithm (Python) http://norvig.com/sudoku.html 
 . It is possible to achieve this method in SQL.


A very important difference between Norvigs programme and the SQLite  
example is however that Norvig only reporst the first solution  
(assuming the sudoku has just one solution). Changing my SQLIte code  
by adding LIMIT 1 immediately makes it much more competitive.
 To achieve Norvigs algorithm I need to choose the cell with least  
remaining choices. The Python program maintains a list of remaining  
choices for each cell. In my SQL, using bitmaps, the number of choices  
is expressed as:


(w10 or w11) +
(w20 or w21) +
(w30 or w31) +
(w40 or w41) +
(w50 or w51) +
(w60 or w61) +
(w70 or w71) +
(w80 or w81) +
(w90 or w91)

The complete programme is below. In my tests it needs 0.2 seconds for  
the SQLite example from http://www.sqlite.org/lang_with.html#sudoku


A novelty (comapred to Norvigs algorithm) is the ordering in which  
candidate digist are generated. I pick the digit that occurs most as  
first. That is intuitivelythe the way that I solve sudoku's by hand.  
This extra ordering (by zfixed desc) makes the time for the SQLIte  
example sudoku worse. But ..


the ;hard1' example from Norvigs code is solved in a fraction of a  
second. (by the way this example is ambigu).


Also your (Bigstone's) eastermonster  
('1 
... 
2.9.4 
...5...6...7...5.9.3...7...85..4.7.6...3...9.8...2.1')  
is solved within a second.


Finally here is the code (but it is not suited for educational purpose)

Regards, Edzard

create temporary table i ( --
i integer primary key, -- sudoku cell (1..81)
name char, -- A1..H8
word0, -- only bit i is set, i = 1..54
word1, -- only bit i-54 is set, i = 55..81
peers0, peers1) -- bitmap of neighbour cells
;
insert into i
select  i,
char(65+y)||(x+1) as name,
case when iword=0 then 1