http://permalink.gmane.org/gmane.comp.db.sqlite.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.sqlite.general/74661 <pre> Hi I'd be grateful for a little advice on SQL syntax, as supported by sqlite. I have a table containing, typically, around 50,000 records. Within the table one particular field will contain many occurrences of the same string value. I can produce a de-duplicated list of the values from that field using "SELECT DISTINCT", but it would be useful to have an occurrence count against each value so that, for example, the list aaaaaabbbccccccccc would produce the output aaa, 2bbb, 1ccc, 3 I know there are ways of achieving this by joining multiple views but, for my own education, I'd like to know if there is an elegant way of achieving this in a single query. Thanks Nige </pre> Nigel Verity 2012-05-25T22:22:06 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74660 <pre> Thanks for the report. This issue is being worked on here: https://system.data.sqlite.org/index.html/info/3aa50d8413 -- Joe Mistachkin </pre> Joe Mistachkin 2012-05-25T22:20:06 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74659 <pre>Just picking a random post to make my reply to. I truly wish I could get access to an external sqlite library to load at runtime. I use e devlopement language that has the sqlite library built into it so I am at the mercy of the language provider as to what is provided. Right now, they are using sqlite 3.7.4 which looks to be at least 7 releases out of date and lacks several features including fts4aux and rtree virtual tables. TO make matters worse, they don;t expose whatever function it is in sqlite that would allow me to load extensions to sqlite from external libraries. So while I think that in most circumstances it makes sense to compile the sqlite library into your application for all the reasons mentioned, I wish that was not the case in my circumstances. Pete lcSQL Software &lt;http://www.lcsql.com&gt; On Fri, May 25, 2012 at 9:00 AM, &lt;sqlite-users-request-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org&gt; wrote: </pre> Peter Haworth 2012-05-25T20:57:16 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74658 <pre>For those who missed the notice about Richard's visit the first time around: https://plus.google.com/104981852298046595282/posts/JhpKW3zXScj On Fri, May 25, 2012 at 9:45 PM, Richard Hipp &lt;drh-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org&gt; wrote: Adding persistent user preferences on top of Fossil's JSON API would be (A) really easy and (B) allow the preferences to be of near-arbitrary complexity. e.g. it could be used to periodically store drafts of a wysiwyg editing session (in addition to any user-specific repo-level config options). Alternately/additionally, "session" support (storage valid as long as the login token) would be really easy to add. &lt;snip&gt; Those who "register" with me will get details on when/where, once those are finalized (the Where is decided, the exact When not so much). But those visiting this first time around will always be able to say, "we saw him before he was famous!" ;) Happy Hacking! </pre> Stephan Beal 2012-05-25T19:59:24 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74657 <pre> My desire is to have a Fossil code-sprint on July 3. Come and bring your laptop and be prepared to hack. I'll have contributor license agreements&lt;http://www.fossil-scm.org/fossil/doc/trunk/www/copyright-release.html&gt;on hand for you to sign if you have not done so already, I will be handing out passwords for push privileges on the core Fossil repository, and I will providing guidance and training to anybody who is interested in how to get started in hacking Fossil. Ideas for things to implement at the sprint include: * WYSIWYG wiki editing * Web-based content file editing * User preferences stored in a cookie * Automatically adjust side-by-side font sizes to fit the screen * Full text search * Better labeling of attachments * Fix the private branch mechanism to make it easy to publish a private branch * Automated self-test The above list will likely grow. So if you are planning to be in the Munich area on July 3 please stop by Contact Stephen Beal for information on the venue. If this code sprint w</pre> Richard Hipp 2012-05-25T19:45:01 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74656 <pre>On Fri, 25 May 2012 14:56:24 -0400, Frank Chang &lt;frank_chang91-PkbjNfxxIARBDgjK7y7TUQ&lt; at &gt;public.gmane.org&gt; wrote: Poisonous link, don't click. Frank, please perform a rigorous virus check on your machine(s), you may have been infected by a bot. </pre> Kees Nuyt 2012-05-25T19:44:34 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74655 <pre> How to reproduce: obtain any SQLite database with 2 or more tables in it (I used "CREATE TABLE a (x INTEGER PRIMARY KEY); CREATE TABLE b (y INTEGER PRIMARY KEY)"). Compile and run the following C# code: SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder(); connBuilder.DataSource = &lt;Path to the database&gt;; SQLiteConnection conn = new SQLiteConnection(connBuilder.ConnectionString); DataSet dataSet = new DataSet(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(&lt; at &gt;"SELECT * FROM a; SELECT * FROM b;", conn); dataAdapter.FillSchema(dataSet, SchemaType.Source); The last line mysteriously throws NullReferenceException — apparently, something went wrong deep inside System.SQLite.Data assembly, but I don't have its source code at the hands. I would appreciate if someone pointed me at a _nice_ workaround other than "Don't use FillSchema()". _______________________________________________ sqlite-users mailing list sqlite-users&lt; at &gt;sqlite.org http://sqlite.org:8080/cgi-bin/mailman/list</pre> z Kemtauros 2012-05-25T19:21:48 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74654 <pre> On 25 May 2012, at 7:38pm, Udi Karni &lt;ukarni-Re5JQEeQqe8AvxtiuMwx3w&lt; at &gt;public.gmane.org&gt; wrote: Just for testing purposes, change MIN(DATE) to DATE and check the timings. Simon. </pre> Simon Slavin 2012-05-25T19:10:20 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74653 <pre> http://swatim.sw.ohost.de/gertycamp3.html </pre> Frank Chang 2012-05-25T18:56:24 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74652 <pre>I am running the following query - CREATE TABLE XYZ AS SELECT ID, MIN (DATE) FROM SOURCE-TABLE WHERE CRITERIA GROUP BY ID ; SOURCE-TABLE has 600 million rows, 2 million meet the WHERE criteria, and get grouped to 100,000 distinct IDs. There are no indexes as there is too much variety in the WHERE clauses and index creation on this table takes many hours. Temp_store=memory. When executed - the source table is read very slowly, and runtime is 1/2 hour. I split this SQL into 2 steps - the first just extracting - the second just grouping. The extract read the table extremely fast and finished in 3 minutes. The GROUP BY ran in a few seconds. So a 10X improvement - which is pretty incredible speed. Why such a large disparity? Why wouldn't the single-step SQL read fast - write the accepted values to TEMP - then sort and group - same as the 2-step? Any way to influence the SQlite SQL processor to chose a more optimal single-step approach? Thanks ! </pre> Udi Karni 2012-05-25T18:38:54 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74651 <pre> On a 64-bit machine managed executables run as 64-bit assemblies (and thus can only load 64-bit DLLs) unless the 32-bit flag is set in the assembly. You can set this flag via Visual Studio's "Platform Target" option or CorFlags.exe. I also seem to recall if a 32-bit assembly launches a managed assembly, it'll be run as a 32-bit process. This is why I use a custom SQLite DLL. I compile and ship both 32 bit and 64 bit versions and load the proper DLL at runtime depending on how my process has been run. Sadly, my solution has too many other custom features to be something I can publish. </pre> Random Coder 2012-05-25T18:22:05 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74650 <pre> Except that 64-bit executable cannot load 32-bit DLL and vice versa. I'm not sure how this rule is applied to managed executables and DLLs loading unmanaged DLLs. Pavel On Fri, May 25, 2012 at 12:14 PM, Adam DeVita &lt;adevita-nFjhRH3k8BJWk0Htik3J/w&lt; at &gt;public.gmane.org&gt; wrote: </pre> Pavel Ivanov 2012-05-25T16:40:59 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74649 <pre>Under normal circumstances, same directory as the exe is part of a default path of places Windows looks. I just ran my 32 bit code on a windows 7 64 bit machine. It worked ok ( It is just a count records in this table and display it on a test label. ). For device drivers, the 32 &amp; 64 bit difference can be incompatible. For other libraries, DLL functions are just like exe ones. The 64 bit machine should execute the 32 bit code. I'm testing with the .NET 3.5 SP1 32 bit code. Adam On Fri, May 25, 2012 at 11:47 AM, Peter Walburn &lt; Peter.Walburn-pm10qGyYuvuzR313v1King&lt; at &gt;public.gmane.org&gt; wrote: </pre> Adam DeVita 2012-05-25T16:14:09 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74648 <pre>We were just having a discussion on a different thread on the relative merits of static versus dynamic linking. Notice that if the application where statically linked (if that were possible in C#) then all the grief that Peter is having would have never come up. The application would just work, and everybody would be happy. On Fri, May 25, 2012 at 10:56 AM, Peter Walburn &lt; Peter.Walburn-pm10qGyYuvuzR313v1King&lt; at &gt;public.gmane.org&gt; wrote: </pre> Richard Hipp 2012-05-25T15:56:41 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74647 <pre>I've just been trying some other things and it seems that the SQLite.Interop.dll file that I have copied to the 32-bit operating system is the 64-bit version. I think it will work if I make sure that the 32-bit versions of the DLLs are included in my install program. Will the 32-bit versions work on 64-bit operating systems too? Peter Walburn Software Engineer E-mail: peter.walburn-pm10qGyYuvuzR313v1King&lt; at &gt;public.gmane.org Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com -----Original Message----- From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org [mailto:sqlite-users-bounces&lt; at &gt;sqlite.org] On Behalf Of Adam DeVita Sent: 25 May 2012 16:37 To: General Discussion of SQLite Database Subject: Re: [sqlite] FW: Problem with SQLite when deployed. Simple thing first: Did you ensure that the SQLite.Interop.dll is in the path that the PC is searching? regards, Adam On Fri, May 25, 2012 at 1</pre> Peter Walburn 2012-05-25T15:47:57 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74646 <pre>Adam SQLite.Interop.dll is in the same directory as the application being run. But it probably isn't in the PC's search path. Peter Walburn Software Engineer E-mail: peter.walburn-pm10qGyYuvuzR313v1King&lt; at &gt;public.gmane.org Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com -----Original Message----- From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org [mailto:sqlite-users-bounces&lt; at &gt;sqlite.org] On Behalf Of Adam DeVita Sent: 25 May 2012 16:37 To: General Discussion of SQLite Database Subject: Re: [sqlite] FW: Problem with SQLite when deployed. Simple thing first: Did you ensure that the SQLite.Interop.dll is in the path that the PC is searching? regards, Adam On Fri, May 25, 2012 at 10:57 AM, Peter Walburn &lt; Peter.Walburn&lt; at &gt;omega-data.com&gt; wrote: -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ________________________________________</pre> Peter Walburn 2012-05-25T15:38:42 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74645 <pre>Simple thing first: Did you ensure that the SQLite.Interop.dll is in the path that the PC is searching? regards, Adam On Fri, May 25, 2012 at 10:57 AM, Peter Walburn &lt; Peter.Walburn-pm10qGyYuvuzR313v1King&lt; at &gt;public.gmane.org&gt; wrote: </pre> Adam DeVita 2012-05-25T15:36:38 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74644 <pre>Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: "The message's content type wa</pre> Peter Walburn 2012-05-25T14:57:37 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74643 <pre>Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: "The message's content type wa</pre> Peter Walburn 2012-05-25T14:56:11 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74642 <pre>Am 25.05.2012 14:27, schrieb Pavel Ivanov: Well, it reduces the work for the Linux maintainers and some app developers are notoriously slow to fix security bugs upstream. Guess it really depends on the target audience. For some library or other smaller app that is to be distributed with some popular Linux distro or to another environment where someone else is responsible for keeping the environment sane, it might make sense to do dynamic linking to a system library. If the other person that manages the environment fails, your app fails. If the other person does not update the libs you are stuck and can only file bugs and wait. But if you ship a major system, you really really want to keep your environment under control. More work, but saves you from insane bugs due to broken system libs and weird hacks around various system lib issues. If some lib is broken, just patch it and ship a fixed version. We typically run 'ldd' on all our libs and programs and match the external dependencies to an allowed list. </pre> Michael Schlenker 2012-05-25T13:26:29 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74641 <pre>Hi Michael, Kreith, Jonas, Thanks for your response. I just tried to launch the binary manually, and it worked very fast. I didn't figure out by myself that i should've tried to launch the application outside of Visual Studio (I was thinking that in "Release" mode VS doesn't slow down the execution by debugging instruments). Sorry for raising a dust with all this. 2012/5/25 Black, Michael (IS) &lt;Michael.Black2-itBhTGHPdi0&lt; at &gt;public.gmane.org&gt;: </pre> Nick 2012-05-25T13:09:13 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general