Re: [sqlite] [sqlite-dev] Final preparations for the release of System.Data.SQLite v1.0.92.0 have begun...
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
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
On 16 Mar 2014, at 7:16pm, RSmithwrote: > 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
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
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
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
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
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
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