Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS
Hi, > It can even compare two fields in the same row and test one against the other > so you can, for example, make sure you don't have any people who are both > male and pregnant. Actually, this restriction could be problematic: https://en.wikipedia.org/wiki/Transgender_pregnancy Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
Hi, Von: Jean-Christophe Deschamps > > > This would means that if ever an SQL statement encounters divide by > > > zero, the application will crash with no way handle the situation > > > gracefully, nor to locate the source of the problem. > > > >Seriously, what are you talking about? Why is there "no way to handle" > >the error, gracefully otherwise? How do you know there would be no way > >to "locate the source of the problem"? > > > >I imagine an error SQLITE_EMATH returned by sqlite4_step. With some > >care, perhaps the expression returning zero could be mentioned in the > >error text. I can't imagine how that would present a problem. > Yes but raising an exception has been mentionned at some point in the > discussion. I was just saying that doing so is pretty different from > returning an error at function-level. An Exception in the SQL sense effectively results in an errorcode returned by an sqlite function. SQLite is implemented in C, there are no exceptions on the language level it could raise. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
Hi, Dave, Von: Dave Wellman > The problem deals with dividing by 0. As far as I can remember, in every > programming language that I have ever used and in all databases that I've > used, if you try and divide by 0 the process will fail with a 'divide by > zero' error. Sqlite doesn't seem to do that, it instead returns NULL. AFAIR, there are environments where division by zero for floating points can lead to a +INF or NaN value without an exception. But you're right insofar as the SQL standard seems to mandate an error in this case. http://postgresql.1045698.n5.nabble.com/Division-by-zero-td1922266.html Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE INDEX Placement
Hi, Von: Drago, William @ MWG - NARDAEAST > Does it matter if I group all of my CREATE INDEX statements at the end of > my .sql file or should they appear right after each CREATE TABLE statement? > Does SQLITE care one way or another? Semantically, it's equivalent (apart from the fact that you get different artifacts when one of the intermediate operation fails for whatever reason). But the performance may be different - if a single table and its indices fit into RAM (os disk caches), but the whole database does not, performing the CREATE INDEX statements right after the corresponding CREATE TABLE statements may be faster, depending on your I/O system, as it can fetch the table contents from RAM during index creation. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLite lock the entire DB for writes, or lock by table?
Hi, Von: Tom > This seems like a really major difference to MySQL. > MySQL with InnoDB allows concurrent writes to different rows in the same > table. > SQLite doesn't even allow concurrent writes to the same DB. > So MySQL has write concurrency, SQLite does not. Guess that's what I'm > learning here. SQLite is an embedded database, which naturally comes with some limitations. Most other embedded SQL databases don't support even as much concurrency as SQLite does. > Maybe I should consider MySQL before considering NoSQL. Out of several years of experience with databases in my former jobs, I strongly suggest that you consider PostgreSQL before considering MySQL. :-) Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hexadecimal integer literals
Hi, Von: Scott Robison > On Wed, Jul 23, 2014 at 9:46 PM, J Decker <d3c...@gmail.com> wrote: > > > Seems like adding hex interpreting is just adding code for the sake of > > adding code. > > Unless the data is coming from some pre written text file, isn't just > > just as easy to format an into into decimal as it is for hex without > > having to add extra characters for the prefix? > > > > One desirable aspect of code is that it is easily understood, that future > modifications / changes be as easy as possible. It is usually much easier to > see that 0x4000 is a single bit set in a 32 bit integer vs seeing the > number 1073741824 and wondering exactly what it means. At least it is to me. > > For that reason, I think adding hex constants to the parser is a good idea. > If I were expressing wishes, I'd suggest adding binary constants (with a 0b > prefix) but that's probably pushing luck. :) > > But no octal! At least not using the C syntax. I like being able to prefix a > decimal integer with 0 and not having it change the meaning (which can't be > done in C). Python 3, Rust and others adopted the syntax 0o123 for octal literals. The IEC 61131 languages use the syntax base#value, so 16#12ab is a hex number, and 8#123 is an octal number. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hexadecimal integer literals
Hi, Eric, Von: Eric Rubin-Smith > > So far no one has raised the idea of using a "big int" layer to implement > proper integer arithmetic past 64 bits. The fact that it hasn't been > mentioned makes me worry that it's a blatantly silly idea for SQLite for some > reason -- but I'm tossing it out there on the off chance that it's useful. Actually, I did: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-July/054285.html > Personally, I would have loved to be able to use 128-bit integer math a few > weeks ago when I was investigating the idea of implementing a longest-prefix > search capability for IPv6 networks using SQLite's R*Tree. > I had to implement all the shifting and such in C, which is fine, but it > would have been pretty sexy to have gotten it all done in SQL. I'm not sure whether such functionalities are a must for SQLite core, but I think that extensions providing such functionalities should be possible. I'm not that much into SQLite internals yet, but I know that PostgreSQL allows extensions to define their own data types with their own parsers and syntax, which is used e. G. by the PostGIS extension. On the other hand, PostgreSQL has built-in "decimal" and "numeric" types which allow up to 131072 digits before and 16383 digits after the decimal point. http://www.postgresql.org/docs/current/static/datatype-numeric.html So one might argue such datatypes could also fit well into SQLite :-) Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hexadecimal integer literals
Hi, Von: Im Auftrag von Dominique Devienne > On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp <d...@sqlite.org> wrote: > > > (6) Do not support hexadecimal integer literals for casts and affinity > > coercions. Only support hex literals in the SQL parser, and throw > > errors for oversized hex literals in that context. > > > > +1. --DD I'm also in favour of (6) for now. This still leaves room for upwards compatible behavior should SQLite (version 4, maybe) or an extension add support for larger integers. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing performance hypothesis of scanning a table with no indexes versus reading same data from a file
Hi, Von: David Canterbrie > I've been tasked with trying to understand how much of a performance hit one > would get if one had to scan a table in its entirety versus reading the same > data stored as a new-line (or some sort like that) from a file. > > The hypothesis I suppose we're trying to understand is that reading > sequentially from SQLite (without indices) should be comparable to reading > from a file that has the same data +/- 1-2% > > My first question is that does sound reasonable, and has someone ever done > such a test? I guess this highly depends on your data format and parser code. The author of http://sebastianraschka.com/Articles/sqlite3_database.html#results claims a factor ~20 speed advantage for SQLite. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building multiple indexes in one scan over a table
Hi, > Von: Fabian Giesen > I'm working on a profiler that uses SQLite to store and index the > results. > > Due to the nature of the task, the workload ends up splitting into > essentially three phases: > > 1. Bulk insertion of relatively large amounts of data (low hundreds > of megabytes to tens of gigabytes); a typical profile generates on > the order of 10MB of sampling data per second. > > 2. Indexing. The first phase normally runs without any indexes on the > sample tables, to maximize insertion throughput. In the second phase > we create the indexes we need to retrieve the samples efficiently. > (We found "batch-indexing" after the fact to generally lead to > shorter overall processing times.) > > 3. Browse the results. Once the samples are indexed, the user can > look around, analyze the results using queries, and so forth. This is > essentially read-only. > > Phase 2 is crucial for quick retrieval, but at the same time it > essentially amounts to wait time for the user, so we'd like to get it > over with as quickly as possible. Now, looking at what happens in > there, I noticed that we often end up creating several indexes on the > same table back-to-back: > >CREATE INDEX samples_by_time ON samples(time); >CREATE INDEX samples_by_name ON samples(name_id); >CREATE INDEX samples_by_type ON samples(type_id); > > Each of these statements ends up scanning over the entire table once. > Since the tables in question are quite large, I would love to have > some way to create multiple indexes in one pass. A cursory > examination of the VDBE code produced for the CREATE INDEX statements > indicates that SQLite's backend should be capable of doing so (though > I'm no expert), but I don't see a way to express what I want in > standard SQL. > > So, is there any way to create multiple indexes for the same table in > one operation in SQLite? If not, how hard would it be to add? If one of your columns is unique, you could declare this column as primary key and use "WITHOUT ROWID". If the column is also of type int, you could use the "INTEGER PRIMARY KEY" optimization which has a similar effect. This saves the time of creating the index altogether, saves a few bytes for the row id column, and the table will be pre-ordered by said value. But be sure you read the caveats in the documentation before proceeding. https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/lang_createtable.html#rowid I'm also not sure how this affects insertion time, but if the value is growing monotoneously, it should not differ too much from the standard auto-generated ROWID behavior which has the same properties. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How good is pragma integrity_check
Hi, Simon, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On 24 Mar 2014, at 5:23am, Roger Binns <rog...@rogerbinns.com> wrote: > > On 21/03/14 15:24, Simon Slavin wrote: > >> Checksums stored with the page index lists, > > > > SQLite already has the ability to carve out data on each page for > > other uses. For example the encryption extension uses this. > > This would be better than nothing, but there is a problem with writing the > checksum in the same operation that writes the page. The two standard > unlikely scenarios for data corruption (cosmic rays and static discharge) > flip bits inside wires lines or inside RAM bits, and it's possible that the > bit that gets flipped will be for the address of the sector being > written/read rather than in the data being written/read. So by storing the > checksum in the page it checks you will get a sector that passes the checksum > test, but has been written to the wrong place in storage and therefore > corrupts the file. Agreed. > So the checksum should be stored away from the page it checks. Not agreed. Another way to solve this problem is to include the file offset or sector number into the checksum - this will also detect "movements" of data to the wrong place, without the need for an atomically writeable "external" storage. > My > understanding of the SQLite file format suggests that the checksum could > usefully be stored with the pointer to the page in the page store (TABLE / > INDEX page list or whatever it's called) since it would be needed at the same > time and the two could be retrieved in one operation. > > > It couldn't be on by default for backwards compatibility reasons. > > (WAL is another example of that.) > > > Agreed. Backward compatibility would be a problem for SQLite3 so it's not > going to happen either way unless the file format is revised and more PRAGMAs > added. Maybe in SQLite4. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ 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
Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > > 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. SharpSVN (https://sharpsvn.open.collab.net/) uses some build trickery to link native libraries in a way that they're kept as "external resource files" along with the assembly. This means that VS and MSBuild copy them along with the main assembly, and it is also installed into the GAC along with the main assembly. The trick seems to be the tag below in the vcxproj file: Advapi32.lib;shell32.lib;Rpcrt4.lib;Mswsock.lib;Crypt32.lib;User32.lib ..\..\imports\release\lib;..\..\imports\release\lib-AnyCPU;..\..\imports\release\bin;%(AdditionalLibraryDirectories) SharpSvn-DB44-20-$(Platform).svnDll;crypt32.dll;mswsock.dll;secur32.dll;user32.dll;ole32.dll;advapi32.dll;%(DelayLoadDLLs) $(TargetDir)SharpSvn-DB44-20-$(Platform).svnDll;$(TargetDir)SharpPlink-$(Platform).svnExe;%(AssemblyLinkResource) true true MachineX86 SharpSvn.snk UseLinkTimeCodeGeneration true It links both a DLL and an exe file that way. (The file endings are changed to reduce confusion of other software.) When installing into the GAC, they both are copied along into the same directory as the SharpSVN Assembly itsself, where they can be found and loaded / executed. Maybe this trick could be used by System.Data.SQLlite as well - however, I'm currently not sure whether it is possible to create such linkage with C#, maybe some postprocessing is necessary. On the other hand, SharpSVN also links a lot of native code directly into the DLL - using C++/CLI instead of C#, this is rather easy. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why would batched write operations NOT be faster than individual ones
Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > At 21:35 03/03/2014, you wrote: > ´¯¯¯ > >RAID3-4-5 was great when disks were expensive, in 80's an 90's. Now > >not. A minimal RAID5 needs 3 disks. A minimal RAID10 4. An enterprise > >disk SAS 15Krpm 146 GB 6G is $350, and a not enterprise grade cheaper > >and bigger. Now RAID1E and RAID10E give more flexibility and variable > >security, from "paranoid" to "i don't care" grades. > `--- > > The point being discussed was not on performance or cost, but on the > imaginary fact that RAID5-6 and variations have the inherent, by-design fatal > flaw that they break down because a parity block can be out of sync with > corresponding data blocks. This is bullshit, period. It is not. Period. > Nothing in RAID5-6 design mandates serialization of writes, by far. Yes. But I've yet to know a setup for harddisks which allows reliable transactional writes spanning several disks. (Kinda two-phase commit for disk writes). Of course, a dedicated hardware controller who issues the write requests to the disks absolutely synchronously lowers the risk by shrinking the time window. But it cannot totally eliminate it, for at least the following reasons: - The platters are usually not completely physically in sync, so the first disk may have written the block while the second disk still needs to wait for another 1/4 rotation for the block to be written. - One of the disks may have internally remapped a bad sector, needing a seek (and thus much longer time) to write the block. In reality, there are usually some more time variation, e. G. due to - Both disks may be connected through the same cable, thus the requests to the disks need to be serialized. - There may be other outstanding requests in the disk internal cache which the disk firmware might reorder in a different way. I admit that the remaining risk may be low, but it is not zero. Period. > It's only when cheap, unreliable hardware is put to work under below par > software that the issue can be a real-world problem. > > So the rant on the design against parity-enabled RAIDs is moot, if not plain > fallacious unless "software RAID without dedicated controller" is clearly > mentionned. I did mention using battery backed power as a way to mitigate the risk. And as far as I know, even the most expensive hardware RAID controllers and disks do not yet support multi-disk transactions, so your reference to not-yet existing hardware is moot. > About SAS disks: they have actual very high reliability and don't lie, > contrary to SATA disks (on both points). > > This is not a war about mine being bigger, but it's better to have facts > stated right. I fully agree there. > All high-end reliable machines and storage subsystems only run > parity-enabled RAID levels and this thechnology isn't going to disappear > tomorrow. I doubt that _all_ those machines exclusively run on parity-enabled RAID levels, but I'm strongly interested in a proof of your "fact" here. I remember reading that PostgreSQL and Oracle recommend using mirroring based levels instead of parity-enabled ones for performance reasons, so I'm really curious to read about how you back up your claim. Best regards Markus Schaber CODESYS® a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why would batched write operations NOT be faster than individual ones
Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On 3 Mar 2014, at 8:18am, Markus Schaber <m.scha...@codesys.com> wrote: > > Another way to bust your data is to rely on RAID 5 or 6 or similar, at > > least if the software does not take special care. > > > > When those mechanisms, updating a block always results in at least 2 > > disk > > writes: The data block and the checksum block. There's a small time > > window where only one of those blocks physically reached the disk. > > Now, when the power fails during said time window, and the third disk > > fails, it's content will be restored using the new data block and the > > old checksum (or vice versa), leaving your data garbled. > > What the heck ? Is this a particular implementation of RAID or a conceptual > problem with how RAID is designed to work ? It sounds like a bug in one > particular model rather than a general problem with how RAID works. It is a conceptual problem of the RAID levels 5 and 6 and similar proprietary mechanisms which are based on parity blocks. RAID setups using only mirroring and striping like the RAID Levels 0, 1, 10 are not affected, and the risk may be lowered by using battery powered RAID controllers. Let's see a simple RAID5 with three disks. The blocks a and b are the two data blocks which are covered by the parity block c. Let's say the database code writes the block b. The RAID layer creates a corresponding write to for the parity block c. As the harddisks are not physically synchronized, there is a small time slot where only one of the blocks b and c has been written, but not the other one. The power fails during that time slot, and during the reboot, the harddisk containing block a fails. During the raid rebuild, the contents of block a are recreated using the blocks b and c - but as only one of those blocks was up to date, and the other contains the old state, this leads to (more or less) complete garbage in block a. So using RAID5, you can risk damaging data which is even unrelated to the data one was actually writing while the machine crashed. Battery powered RAID controllers may lower the risk, as they either hold a copy of the not-yet written blocks in their RAM (or flash) until the power is restored, or they supply power to the harddisks until all the blocks are written. Similar things may happen with other parity / checksum based mechanisms, like RAID 3, 6, or some (nowadays mostly extinct) proprietary solutions. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why would batched write operations NOT be faster than individual ones
Hi, sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On 3 Mar 2014, at 3:41am, romtek <rom...@gmail.com> wrote: > [...] > > Here's a SQLite engineer writing about the same thing: section 3.1 of > > <https://www.sqlite.org/howtocorrupt.html> > > Your disk hardware, its firmware driver, the OS's storage driver, the OS's > file system and the OS file API all get a chance to pretend they're doing > 'sync()' but actually just return 'done it'. And if even one of them lies, > synchronisation appears to happen instantly and your software runs faster. A > virtualising system is another chance to do processing faster by lying about > synchronisation. And unless something crashes or you have a power failure > nobody will ever find out. Another way to bust your data is to rely on RAID 5 or 6 or similar, at least if the software does not take special care. When those mechanisms, updating a block always results in at least 2 disk writes: The data block and the checksum block. There's a small time window where only one of those blocks physically reached the disk. Now, when the power fails during said time window, and the third disk fails, it's content will be restored using the new data block and the old checksum (or vice versa), leaving your data garbled. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite access from ARM ?
Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > Do you know some project to SQLite access from ARM embedded processors ? > I see something for mySQL using MBed plataform (ARM Cortex M3). > Thanks and best regards. As SQLite comes in the form of C source, it is independent of the CPU. If your OS or runtime environment is not yet supported, you can create your own platform abstraction layer. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite clusters?
Hi, Jason, It might be that this is a little bit to big for sqLITE. Maybe a "big iron" database like PostgreSQL or the Greenplum Database will fit your requirements better. Best regards Markus Schaber CODESYS® a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 > -Ursprüngliche Nachricht- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Jason H > Gesendet: Montag, 16. September 2013 23:04 > An: sqlite-users@sqlite.org > Betreff: [sqlite] SQLite clusters? > > I'm transitioning my job from embedded space to Hadoop space. I was > wondering if it is possible to come up with a SQLite cluster > adaptation. > > I will give you a crash course in hadoop. Basically we get a very > large CSV, which is chopped up into 64MB chunks, and distributed to a > number of nodes. The file is actually replicated 2 times for a total > of 3 copies of all chunks on the cluster (no chunk is repeatedly > stored on the same node). Then MapReduce logic is run, and the > results are combined. Instrumental to this is the keys are returned > in sorted order. > > All of this is done in java (70% slower than C, on average, and with > some non-trivial start-up cost). Everyone is clamoring for SQL to be > run on the nodes. Hive attempts to leverage SQL, and is successful to > some degree. But being able to use Full SQL would be a huge > improvement. Akin to Hadoop is HBase > > HBase is similar with Hadoop, but it approaches things in a more > conventional columnar format It a copy of "BigTable" form google.. > Here, the notion of "column families" is important because column > families are files. A row is made up of keys, at leas one column > family. There is an implied join between the key, and each column > family. As the table is viewed though, it is void as a join between > the key and all column families. What denotes a column family (cf) is > not specified, however the idea is to group columns into cfs by usage. > That is cf1 is your most commonly needed data, and cfN is the least > often needed. > > HBase is queried by a specialized API. This API is written to work > over very large datasets, working directly with the data. However not > all uses of HBase need this. The majority of queries are distributed > just because they are over a huge dataset, with a modest amount of > rows returned. Distribution allows for much more paralleled disk > reading. For this case, a SQLite cluster makes perfect sense. > > Mapping all of this to SQLite, I could see a bit of work could go a > long way. Column families can be implemented as separate files, which > are ATTACHed and joined as needed. The most complicated operation is > a join, where we have to coordinate the list of distinct values of > the join to all other notes, for join matching. We then have to move > all of that data to the same node for the join. > > The non-data input is a traditional SQL statement, but we will have > to parse and restructure the statement to join for the needed column > families. Also needed is a way to ship a row to another server for > processing. > > I'm just putting this out there as me thinking out loud. I wonder how > it would turn out. Comments? > ___ > 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] quetion on editing a sqlite database
Hi, Von: von Simon Slavin > On 15 Sep 2013, at 11:30pm, john white <drufus2...@yahoo.com> wrote: > > Oh, there is a location column with this information. I can do them, > > one at a time, but that is going to take a LOT of time. I was hoping > > there would be a way to select them all and then simply do a find and > > replace thing. > > > > An example would be: "C:\TV\" which should now be '/TV/". Even better > > would be to change all the "\" to "/" and then just delete the leading "C:". > > Use the SQLite shell tool for one of your platforms, or perhaps write your > own program to open the databases and execute these two lines. > > UPDATE myTable SET location = replace(location, '\', '/') UPDATE myTable SET > location = replace(location, 'C:', '') > > While you're doing that, you might think hard about what'll happen if you > ever need to use the same database on multiple platforms. While you're > designing the changes it might be worth making two different columns, one for > the location under Windows, and the other for the location under Linux. I'd prefer to use relative pathes with a normalized '/' or '\' within the database, and store the base directory somewhere else (or maybe define the pathes relative to the location of the sqlite database). Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation update request
Hi, Simon, von Simon Slavin > On 4 Sep 2013, at 3:05pm, Markus Schaber <m.scha...@codesys.com> > wrote: > > > Afaics, this applies to partial indices for similar reasons. > > I did not even know partial indices was implemented. Thank you. It's new in 3.8.0 :-) Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation update request
Hi, von Simon Slavin > Could the page > > <http://www.sqlite.org/formatchng.html> > > please be updated to reflect the introduction of WAL mode ? I know this > doesn't change the format of the database file itself, but it does change the > format of files on disk, and it does mean that earlier and later versions of > SQLite won't play nice under some circumstances. Afaics, this applies to partial indices for similar reasons. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
Hi, C M, Im Auftrag von C M > On Sun, Sep 1, 2013 at 4:28 PM, jose isaias cabrera > <cabr...@wrc.xerox.com>wrote: > > "C M" wrote... > > Keeping it simple: > >> > >> I have a Python application that uses SQLite, and I randomly get this > >> error: > >> > >> "SQL logic error or missing database" > >> > >> Is the database in network drive or not in the same machine that is > > running the app? > > > > The database is a file on the same hard drive that the app is on. It's all > on a one laptop. Just to be safe: Can you roule out any antivirus or other security software locking the files temporarily? Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] curious: Why no "shared library" or DLL?
Hi, Im Auftrag von Tiago Rodrigues [...] > It's not that "everyone in Windows programming expects everything to be a > DLL"; this is an overgeneralisation. After all, Java isn't exactly Windows- > only code and we're not dealing with Win32, but with IBM zOS. > However, think a bit on the problem of how you could compile in a native code > object file into a program which runs inside a virtual machine. > > This is a problem with Python, Lua and Ruby, as well, although in Python and > Lua's case we could create a C executable hosting the Python or Lua VM inside > it and expose the SQLite API to it via extension methods. I don't know how > embeddable Ruby is. > > At any rate, one cannot generally embed a JVM or CLR into a C program, so > you're left with one mechanism, which is the very Unix-y > dlopen()/dlclose()/dlsym() interface, or its Windows analogue, > LoadLibrary()/FreeLibrary()/GetProcAddress()... Strictly speaking, this is not true - most JVM and CLR implementations are actually embeddable via a C (or C++) API, for example: http://msdn.microsoft.com/en-us/library/ms404385.aspx http://www.mono-project.com/Embedding_Mono http://docs.oracle.com/javase/1.5.0/docs/guide/jni/spec/invocation.html http://gcc.gnu.org/onlinedocs/gcc-4.0.3/gcj/Invocation.html#Invocation There are other interoperability mechanisms available - e. G. CORBA, or on Windows, one could use COM. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM and large indices: best practice?
Hi, Im Auftrag von uncle.f > Hello all, > > I have a process that runs 24/7 and permanently inserts data into an SQLite > database: > > 1) Create new database at midnight, keep it open for writing for the next > 24 hours > 2) There are no deletions and not even reads during the database creation > 3) I use only bulk inserts wrapped in a transaction (for each minute of > data) that may contain a few thousands of rows in every transaction. > 4) My journal mode is MEMORY. > 5) Once the insertion process is done with I build several indices and close > the database > 6) After that the database file is moved over the network to a storage device > > The database will only be used again for reading and will remain unmodified > forever. > > Each database is fairly large (3-5 GB) and considering it will never be > modified again I would like to take all possible measures to ensure that the > file size / fragmentation / data access times are all as low as possible. > > So my question is about how to ensure most efficient data allocation for such > scenario. I thought of several options: > > 1) VACUUM before creating indices, then create indices, then move database > off to storage > 2) Create indices, then VACUUM, then move off to storage > 3) Create indices, move to storage, VACUUM when already on storage (using > SQLite process running locally on storage device) > > ... or any other sequence of those 3 steps (vacuum, indexing, moving to > storage) Did you consider running ANALYZE, too? That will populate the statistics which the query planner can use to gain optimal index usage. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Path Length Limit on Windows
Hi, Richard, Von Richard Hipp > On Mon, Aug 26, 2013 at 10:39 AM, Markus Schaber <m.scha...@codesys.com>wrote: > > Having a closer look, this will only solve problems with pathes whose > > UTF8-encoding is longer than MAX_PATH bytes, but not with pathes which > > exceed the 260 character limit. > The latest check-in on trunk adds a new windows VFS module called "win32- > longpath" which accepts the full-length 32KB pathnames. Add the string > "win32-longpath" as the 4th argument to sqlite3_open_v2() (on windows only, > of course) and everything should just work after that. That's very good news. Thank you! Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Path Length Limit on Windows
Hi, Richard, I myself wrote: > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > [snip] > > (2) SQLite version 3.8.0 allows for longer windows pathnames up to > >3*MAX_PATH bytes, which is 3x more space that was allowed before. > >This is still not 32K but might be sufficient for your needs. The > >limit can be extended at compile-time. > >[] > > > SQLite version 3.8.0 allows you to recompile with - > > DSQLITE_WIN32_MAX_PATH= with however large of filename length > > limit you desire. > > This sounds like a viable solution. SVN embeds the SQLite amalgamation, IIRC, > so it should be possible to raise the limit. > > I'll relay your suggestion to the SVN developers. Having a closer look, this will only solve problems with pathes whose UTF8-encoding is longer than MAX_PATH bytes, but not with pathes which exceed the 260 character limit. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Path Length Limit on Windows
Hi, Richard, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On Thu, Aug 22, 2013 at 10:38 AM, Markus Schaber <m.scha...@codesys.com>wrote: > > During our internal tests, I recently stumbled across a problem when > > using SVN, which uses SQLite to manage the "wc.db" metadata database > > in the working copy. > > > > SVN itself uses the Apache APR as PAL, which internally uses the > > Windows Unicode APIs with the \\?\ path prefix, allowing path lengths > > of up to 32k characters, so it can deal fine with deep directory > > hierarchies. > > > > Except when using SVN externals within those deep hierarchies. > > Externals are managed via nested working copies coming with their own > > "wc.db", so it's easy to run into the MAX_PATH limit of 260 > > characters. > > > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html > > seems to indicate that SQLite is not using those APIs. On the other > > hand, that post is of 2006. > > > > So my questions are: > > > > Is there any way to successfully open a database at such a long path > > location? > > (1) Move the database file to a new location with a shorter pathname. This won't work in my case, as the path name is dictated by the working copy layout and location - svn expects the wc.db to sit within the ".svn" directory of the working copy. > (2) SQLite version 3.8.0 allows for longer windows pathnames up to 3*MAX_PATH > bytes, which is 3x more space that was allowed before. This is still not 32K > but might be sufficient for your needs. The limit can be extended at > compile-time. >[] > SQLite version 3.8.0 allows you to recompile with - > DSQLITE_WIN32_MAX_PATH= with however large of filename length limit you > desire. This sounds like a viable solution. SVN embeds the SQLite amalgamation, IIRC, so it should be possible to raise the limit. I'll relay your suggestion to the SVN developers. Best regards Markus Schaber -- CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Path Length Limit on Windows
Hi, During our internal tests, I recently stumbled across a problem when using SVN, which uses SQLite to manage the "wc.db" metadata database in the working copy. SVN itself uses the Apache APR as PAL, which internally uses the Windows Unicode APIs with the \\?\ path prefix, allowing path lengths of up to 32k characters, so it can deal fine with deep directory hierarchies. Except when using SVN externals within those deep hierarchies. Externals are managed via nested working copies coming with their own "wc.db", so it's easy to run into the MAX_PATH limit of 260 characters. http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html seems to indicate that SQLite is not using those APIs. On the other hand, that post is of 2006. So my questions are: Is there any way to successfully open a database at such a long path location? Is the SQLite project interested in a fix of this problem? What is the correct way to get such a fix rolled (bug tracker, patches, etc.?) Is it possible to implement a workaround, e. G. via a custom VFS layer (which might be able to delegate everything except file opening to the default one)? PS: I'm already on contact with the SVN Developers, but I think the issue is best fixed in SQLite itself: http://svn.haxx.se/dev/archive-2013-08/0344.shtml Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
