[sqlite] Why can't SQLite support ALTER TABLE commands that rewrite the table?
Since my last question was not answered but instead another statement was made which I don't understand, I feel the need to reformulate my question to this: Why is it that SQLite can only support ALTER TABLE statements "that can be accomplished without having to rewrite the entire table"? [1] This includes statements to drop single columns out of a table. I understand that doing this might cause a considerable delay during which the database is not available, but the same applies to the VACUUM statement and that is very well supported. Could somebody maybe enlighten me? If the developers deciding what SQLite can do and what not would state that they do not want, for personal reasons they don't want to disclose, to implement that particular feature, that's fine. But if that's the case, I'd be happy to hear that. Also, if there's a technical limitation preventing this, I'd like to understand it. Having no answer at all is a bit frustrating... [1] Richard Hipp, 2012-10-08 22:12 +0200, on this mailing list -- Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't SQLite drop columns?
On 08.10.2012 22:12 CE(S)T, Richard Hipp wrote: > The only ALTER TABLE functions that SQLite supports are those that can be > accomplished without having to rewrite the entire table. Oh, and what about the VACUUM command? That certainly needs to recreate just about everything, and yet it's here. (Thankfully!) -- Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't SQLite drop columns?
On 08.10.2012 22:12 CE(S)T, Richard Hipp wrote: > ALTER TABLE DROP COLUMN requires writing every row of the table. Actually, I wouldn't care what the engine needs to do to get the job done. If it's documented that a certain command requires more time, it would be perfectly fine. SELECT queries also don't always return in a constant time, each of them requires different work to be done. So, to update my question: Why doesn't SQLite support DDL commands that internally need recreating the table or may not return in O(1) time? Of course, when the engine recreates the table, I'd expect it to maintain all foreign key references, if possible. That's probably even more work from the outside as a normal database user. (You know, the engine can trust the things it does itself (at least I hope so), but it doesn't necessarily trust the things a user does.) -- Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unofficial poll
On 23.09.2012 12:37 CE(S)T, Baruch Burstein wrote: > Has anyone ever actually taken advantage of this feature? In what case? No. And I guess I wouldn't need it. If I need that in other systems, I simply use the most generic type, varchar/text or blob. This way, at least it's obvious that text can appear in a column. I don't see any use in letting the data store accept arbitrary data types. After all, some code must read and understand the data again! And code is usually very restricted in what it can do with data. Some values must be added/multiplied/..., others are concatenated. Each algorithm needs certain data types, even if the programming language doesn't enforce them. While you could always use a different data type at your will, you have to live with the consequences of doing so: bugs and crashes. Sometimes even unnoticed. And if the data will not be processed by code, but simply stored for a user to read it, a simple text column will always do. I code C# and PHP a lot. C# has a mostly static type system and it has the great benefit that the compiler can check the entire application before you need to execute every line of it. PHP doesn't have that advantage and I regularly produce and find bugs due to that. But also in PHP, I carefully select the appropriate type (i.e. don't rely on auto-conversion too much) and rely on it being preserved. That allows me to add my own type-checking and find bugs a lot faster. -- Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why can't SQLite drop columns?
Hello, I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike probably every other SQL database system. But every time I come across this, I feel the pain of having to write huge amounts of code to automatically remove single columns in a table. When doing that in code, it's usually working non-interactively on some generic table schema and cannot use hard-coded column names. So I really have to collect all relevant data including foreign keys and all column attributes and then generate the right SQL code to copy everything right except the dropped column. I very much believe that it would save a lot of developers' resources if SQLite supported that directly. After all, the DBMS has all the data it needs in its readily readable data structures. It would possibly be less work for SQLite than for anybody using it. So I am asking: Why does SQLite still not support dropping columns through SQL after all these years? Do the SQLite developers have strong arguments against it, and which? Are there technical limitations (I can't believe that)? Is there some kind of religion behind it? -- Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD
On 02.04.2012 18:38 CE(S)T, Alexey Pechnikov wrote: > Why we can't control this? As example, in Russia the date format is > DD.MM. and is needed the patch > http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a > > I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for > internationalization. These may be used for parsing and formatting dates. So is there an SQLite feature to accept floating point numbers with a decimal comma instead of the English (and programming language standard) point? After all, why doesn't SQLite accept a Russian translation of all those SQL commands? Not even considering the code page issues (see Unicode comments above)... I also agree that a database should just stick to standard representation of data, not to user-specific or local. The time-to-string function is useful for selecting and grouping. You can group by a day of all months, for example. But the best knowledge about interpreting a local date representation is surely still in your application, not in any database system. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite .NET exception with parallel reading connections
On 08.02.2012 00:57 CE(S)T, Simon Slavin wrote: > If most of the time really is spent reading from the file, then it > may not be worth parallelising your code. All your data is on the > same disk and you can read only one sector of the disk at a time. Most of the file should be in the file system cache anyway. And I'm not sure about how efficient my reading code is already. I'm going to look at that, too. But with 2 threads on my Core 2 Duo (no HT) I can see 100% CPU usage instead of just a bit over 50%, and the time it takes is a bit below the middle of the longest single job and all jobs sequentially. So it does seem to help to parallelise it all. (And it's easier for me because reading and plotting is started from the same function which I don't want to split.) > If you can read all your data first in one process, but then > parallelise the graph-drawing elements, then that might take good > advantage of multi-cores and multi-processors. Reading is what takes most of the time for me, plotting is pretty fast. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite .NET exception with parallel reading connections
On 07.02.2012 23:36 CE(S)T, Joe Mistachkin wrote: > That issue was fixed prior to release 1.0.77.0, here: > > http://system.data.sqlite.org/index.html/info/13a3981ec0 > > If possible, I recommend using the latest released version, 1.0.79.0. Thanks, that did help. :-) -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite .NET exception with parallel reading connections
Just one more addition: If I only start a single background thread, everything is fine. If I start two threads, one of them always fails. But it's not always the same one. With two graph painting jobs of very different time duration, sometimes the shorter wins, and sometimes it's the longer one. But the message and the stack trace remain the same. The message occurs at the beginning of the whole processing, and afterwards it still takes some time for the other threads to complete. But those who do complete do their work well. So the error only affects one of the connections, leaving the other connections alone. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite .NET exception with parallel reading connections
Hi, I have written an application that reads values from an SQLite database and uses them for generating graph images. This process takes up to a few seconds (most of it is reading the database) and multiple images shall be generated. So I thought that doing things in parallel might be a good idea. As I don't expect a single SQLite connection to be threadsafe, I am creating a separate connection instance for each background worker thread and close it when the painting is finished. But that gives me a strange exception at the same point every time: > Unclassified.JobException: Error in job: PlotJob ---> > System.ArgumentException: Ein Element mit dem > gleichen Schlüssel wurde bereits hinzugefügt. >bei System.ThrowHelper.ThrowArgumentException(ExceptionResource resource) >bei System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, > Boolean add) >bei System.Data.SQLite.SQLiteConvert.TypeNameToDbType(String Name) >bei System.Data.SQLite.SQLiteDataReader.GetSQLiteType(Int32 i) >bei System.Data.SQLite.SQLiteDataReader.VerifyType(Int32 i, DbType typ) >bei System.Data.SQLite.SQLiteDataReader.GetInt16(Int32 i) >bei LogState.SQLiteLogReader.GetDescription(String codeName) >bei LogState.PlotDataSource.Read(ILogReader logReader, DateTime startTime, > DateTime endTime, Time > Span avgTime) >bei LogState.StatePlotter.Plot() >bei LogState.StatePlotter.Plot(String fileName) >bei LogState.Common.PlotBySpec(String plotSpecFileName, String > outputFileName, Boolean compact, S > ize customSize, Boolean newLogReader) >bei LogState.PlotJob.DoWork() >bei Unclassified.JobList.worker_DoWork(Object sender, DoWorkEventArgs e) >--- Ende der internen Ausnahmestapelüberwachung --- >bei Unclassified.JobList.worker_DoWork(Object sender, DoWorkEventArgs e) >bei System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) >bei System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object > argument) The exception message roughly translates as "An element with the same key has already been added." It seems that 3 of my 4 jobs run fine, but the first of them always fails with this message. The cause seems to be inside the method of reading an Int16 value from a DataReader. This has always worked fine, but it stopped when accessing the database in parallel. I have already tried setting the ReadOnly property to true in the ConnectionStringBuilder but that has not helped. Can anybody explain to me what this exception even means? Is this an error in my code, did I use the SQLite library wrong, or is it a bug in SQLite.NET? I am currently using System.Data.SQLite version 1.0.76.0 on Windows XP x86 with .NET 4.0. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checkpoint time
On 01.12.2011 18:58 CE(S)T, Sreekumar TP wrote: > During the measurement of manual checkpoint times, I see the following > behaviour - > > # of WAL frames checkpointed is over 1000 > size of DB has not changed after checkpoint.(measured using fstat) > Time for the checkpoint if around 500 ms. > > Why is it that eventhough 1000+ frames are checkpointed, the DB size has > not increased ? > All records inserted are new and unique records and insertions start from > empty database. Could it be that the pages were not actually written to the database because there was a reader lock on them? Did any other process access the database (and thus hold any locks) while you did the checkpointing? (Wild guess. I've only learned about WAL and started using it yesterday. I've read the whole WAL documentation page [1] though.) [1] http://sqlite.org/wal.html -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managed only / Mono and Linux support
On 29.11.2011 04:49 CE(S)T, Joe Mistachkin wrote: >> So what should I do about Mono/Linux support for SQLite in C#? >> > > If you have access to a Windows machine with MSBuild and/or Visual Studio, > compiling the managed-only binary is fairly simple. Okay, that works fine. On Linux. I still need the "normal" DLL for Windows I think. Is there a way to use the "managed-only" System.Data.SQLite.dll assembly for both Linux and Windows? That would simplify my deployment process across platforms a bit. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Managed only / Mono and Linux support
On 29.11.2011 04:49 CE(S)T, Joe Mistachkin wrote: > Adding yet another > pre-compiled set of binary packages for Mono would just serve to make things > even more confusing. Maybe some kind of grid-layout or wizard-style overview would be useful to find the right download. I regularly get confused about the descriptions, especially for the separated and mixed-mode assemblies, and sometimes get an archive where the more important half is missing. I do know what files I'm looking for, and what file sizes to expect, but all that isn't advertised on the page. > MSBuild.exe System.Data.SQLite\System.Data.SQLite.[year].csproj > /t:Rebuild /p:Configuration=Release /p:UseInteropDll=false > /p:UseSqliteStandard=true Thank you, that worked surprisingly fast. So, where could I have found that information if not here? -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Managed only / Mono and Linux support
Hi, Today I've been trying to run my .NET application on Mono with SQLite. It is running fine on Windows (.NET 4.0) with MySQL and SQLite, and I'm successfully using it on Linux (Mono 2.10) with MySQL only. But now that I wanted to actually use SQLite on Linux, it failed. The error it gives me is a DllNotFoundException for SQLite.Interop.dll. But from what I've read on the web by now, this isn't the path to go. I think I need some sort of "managed-only" SQLite version that will use the platform's libsqlite (in my Ubuntu, 3.7.7 is installed). Unfortunately I couldn't find any such version on the very long download page. Where can I get it? I stumbled upon the C# port of SQLite. After quite some reading I think it may be equally reliable and similarly fast, but I'm not totally sure about it. Their project website isn't exactly descriptive. Also they don't provide binaries and I'm uncomfortable with compiling 10 MB of SQLite source code and 500 kB of mine. So what should I do about Mono/Linux support for SQLite in C#? -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Wanted: Windows SQLite shell 3.6.x (not the current version!)
On 12.11.2011 16:00 CE(S)T, Yves Goergen wrote: > Could somebody please send me an older version 3.6.x of the SQLite > Windows shell client? Thank you. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
>> + Easy access, no setup (subscription, rules etc.) Oh, and some lists have a list ID header, some have another, some have none and I need to write mail filters by subject. That's pretty much guesswork every time I subscribe to a mailing list. And some lists echo my own mail back to me, some don't, while I need my own echo for proper threading in Thunderbird. Some digest and then people reply to that digest with totally useless thread subject which I will never read because it doesn't have interesting words in it. (Unless I'm following something 100% which I rarely do today.) And some lists have a web configuration interface, some don't and I cannot check or set up anything. Do you understand why I'm really fed up with mailing lists? I mean, if there was at least some standard how to use and access it, it might be easier. Web forums are all different as well, but still more the same than mailing lists. Usability is also about learning, but you cannot learn things that change all the time. Sorry for the spam, but today I really made enough bad experience with mailing lists for a whole month. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
>> + Easy access, no setup (subscription, rules etc.)
Oh and did I mention the complicated confirmation and registration
methods? I just subscribed to a PHP mailing list. At least I tried. I
have no idea what happened. I received at least two confirmation
e-mails, and of course you get no immediate feedback, all mail from the
list is either delayed or my message was ignored. It's no fun reading
two pages of stuff I need to do until I can participate at the list.
("Copy this text and send a mail to that address, or reply, or do
nothing, or wait, or this or that...")
--
Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Wanted: Windows SQLite shell 3.6.x (not the current version!)
Could somebody please send me an older version 3.6.x of the SQLite Windows shell client? It's not available from the website but I'd like to compare it with other instances of that version 3.6 to see whether it returns the same wrong result or whether my query bug is somewhere else. 3.6 gives no result for my query with HAVING, but 3.7 does. I cannot upgrade the older version so I'd just downgrade the other to be able to compare anything. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
Sorry for my late reply, I look into mailing lists only once every few weeks or when I'm posting something. On 27.10.2011 18:29 CE(S)T, Nico Williams wrote: > then setup a forum interface to the mailing list Then please take good care to use a frontend that uses compatible markup formatting (or none at all, which would not be as appealing) as e-mail programmes do. Otherwise you'll end up with stuff like BBCode in e-mails. >> + Easy access, no setup (subscription, rules etc.) > > The same is true of the list. You do not have to subscribe to read > it, and it could be made to allow posts by non-subscribers, to to have > subscribers that don't get copies of the e-mails. Huh? Subscribers don't get e-mails from non-subscribers? Anyway, if you want to participate in a mailing list without subscription, you need a decent web frontend, which a forum is and a mailing list archive is usually not at all. > Anyways, you have to register to post on a forum, and that's not > terribly different from subscribing to a list. You could still use things like OpenID (or some other external authentication, say Facebook, just to name one) to make that process seamless and really easy. >> + Easily searchable archive with modern and usable UI > > Same is true of a mailing list. But mailing lists are even easier to > search because you can use your favorite MUA's search capabilities if > you like *in addition to* your favorite web search engine and your > favorite mail archive web interface (gmane, ...). You can only search so far as your subscription reaches back. For newcomers, this is absolutely useless. But I personally am already used to not using any kind of site search, like in forums, mailing lists or bug trackers. If Google doesn't find it, it's not there, nor is anything similar-written. That seems to be the most effective solution. >> + Post editing and moderation support (if someone messed it up) >> -> Allows to correct thread hijacking or wrong subject > > I don't care. So you don't care about threading? Really? Why do *you* use it then? If somebody hijacks my thread, I always see new posts in that thread although they're not for my problem. That sucks. >> + Solid threading support (not every MUA can do it well) > > Get an MUA that works right then. Tell that users of a cheap free web mailer or Windows Mail (f.k.a. Outlook Express). >> + URL to any content, directly from the primary UI > > The list software could be made to include a URL for every message > posted in that same message (that'd be nice). Cool, that's the first time I hear that's possible. :-) >> + Optional source code syntax highlighting > > And XSS attack vectors? No thanks. XSS is JavaScript. Syntax Highlighting is CSS. Different things. You can do about anything without JavaScript, it just adds a lot of comfort. >> + Less traffic in your mailbox, you only read what you want > > Use filters, or separate accounts for lists, ... That's what *I* do. But how many do it? I imagine there's a lot of people who are interested in SQLite but not in professional (traditional internet wise, not in an enterprise where Outlook is used, which is not a decent MUA in that sense) mail management and processing, which can get just as complex. Just because you know how to do it "right", that doesn't mean everybody does. I have the impression that quite some mailing list defenders are a bit selfish in that regard. >> + No publicly published e-mail address (spam etc.) > > Forums get forum spam. They also tend to come with ads. Tend is something the forum admin can control. And forum spam can be deleted later, mailing list spam (or e-mail spam) not. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] HAVING and aggregate functions
Hi, I have a strange problem with an SQL query. I want to filter records by the result of a grouped column. SQLite doesn't complain but gives no result records in PHP PDO but the very same SQL does work fine in the SQLite shell on Windows. I have version 3.6.22 and an older 3.6 on the PHP side which does not work, and SQLite 3.7.4 on the shell side where it works. A simplified example of my query is like this: select name, count(*) cnt from things group by name having cnt > 5 order by cnt desc What's the problem here? I wanted to try it with an older SQLite shell but I couldn't find any historic downloads on the SQLite website. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
On 23.10.2011 16:05 CE(S)T, Simon Slavin wrote: >>> If the mailing list was replaced by a forum, everybody would go to the >>> forum. >> The failure in this logic is that is not true. I already said I would >> not bother with the forum and I was not the only one. You would, if it was really replaced. Otherwise you wouldn't have a place for discussion anymore. But with many opinions like this, the mailing list should indeed not be replaced by a forum, and then, no forum should be set up. (Personal opinion: Let's do progress somewhere else.) > Part of the attraction of this list is that I don't have to think > "Oh, I want to read a lot of SQLite-related stuff now !". What do you mean? I don't get it. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
On 18.10.2011 16:40 CE(S)T, Simon Slavin wrote: > The way to settle this is easy: leave the mailing list in place. > Create a web forum. If people abandon the mailing list and start > using the web forum instead, it worked. If people stay with the > mailing list, the mailing list is superior. I don't think many would want to be in two places that serve the same purpose. You can't make a forum a success if the old way still remains active. A forum will only be successful if people are there to post. And if nobody posts there, nobody will go there. If the mailing list was replaced by a forum, everybody would go to the forum. But you definitely should ask the mailing list users whether they would prefer moving over to a forum, otherwise many might be angry. I would also very much prefer a web-based forum. The whole concept has many advantages over e-mail lists and only few disadvantages. + Easy access, no setup (subscription, rules etc.) + Easily searchable archive with modern and usable UI + Post editing and moderation support (if someone messed it up) -> Allows to correct thread hijacking or wrong subject + Solid threading support (not every MUA can do it well) + Following single threads, with notification + URL to any content, directly from the primary UI + Optional source code syntax highlighting + Less traffic in your mailbox, you only read what you want + No publicly published e-mail address (spam etc.) o More "generation Facebook"-like (is this an advantage at all?) - Web-based UI may be less efficient for some (married with their MUA; also greatly depends on the forum software being used!) Further reading: http://halr9000.com/article/179 Having said that, unfortunately I don't have the time to set it up and manage it, but I'm far too little into this community. I'm just a reader and rare questioner... But you'd need a few moderators to operate a forum anyway. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote:
> On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby
> <phil.willoug...@strawberrycat.com> wrote:
>> What about:
>>
>> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);
>
> Y’know the urban legend about the folks at the restaurant who design a
> complicated plan for switching the contents of salt and pepper shakers
> which have their hats juxtaposed, and then a passer-by suggests just
> switching the caps?
I don't know that story nor do I understand it. But would that trigger
thing work? That would be something I could set up easily.
It's not that important issue for my anymore. I've simply decided to not
store binary data in the SQLite database in this application anymore but
instead write it to disk. Storing files in the database should only be
of interest here if you cannot write to disk, but when using SQLite you
obviously can do that.
--
Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote: > * You should be using bound parameters on INSERT. If you are not, > change > your code. This will eliminate a whole list of potential problems. I already do that. > * Make sure the binding is done as BLOB and not TEXT. PDO probably has > > its own flags defined for this. This is the part that tells SQLite > whether you are inserting TEXT or BLOB. There is a PDO method to execute a prepared statement with an array of values to be used as parameters. There is no way to specify additional information about how to interpret these values in this method. But there is another method to bind each value separately, and it has another argument to pass some data type. I'd need to change the way I execute my SQL statements to make use of it. I'd expect that SQLite known on its own what data type a column is and respect it. Seems like SQLite is sometimes more type-agnostic than PHP, where I take great care of data types in this special application. For now, I just won't save files to the database with SQLite but instead on disk. I won't get to rewriting the database class anytime soon but I'll look into it then. I'm wondering why I get all the data back but SQLite can't count its characters... And the image I get back from SQLite looks error-free so it probably didn't make a single mistake handling it as text data. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On 06.02.2011 13:52 CE(S)T, Samuel Adam wrote:
> For the original poster: To prevent problems like this, if it will always
> be a BLOB then add a constraint to your table:
>
> CHECK (typeof("Data") IS 'blob')
>
> Your INSERTs will fail if inserting TEXTual data then.
I don't want my INSERT to fail, I want SQLite to see that I'm inserting
binary data. There is only one way to insert data with PHP/PDO. I give
it binary data and it's supposed to do it right.
--
Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
On 06.02.2011 12:01 CE(S)T, Simon Davies wrote:
> length() should return the number of bytes for a blob.
> What does typeof() tell you about the data?
It says "text". Now tested with SQLite 3.7.4, this is the only command
line client I have here.
Here's the statement how the table was created:
> CREATE TABLE "message_revision" (
> "MessageRevisionId" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "MessageId" INTEGER NOT NULL REFERENCES "message" ("MessageId") ON
> DELETE CASCADE,
> "CreatedTime" DATETIME NOT NULL,
> "Author" INTEGER NOT NULL REFERENCES "user" ("UserId"),
> "Subject" VARCHAR(255),
> "Content" MEDIUMTEXT,
> "HtmlContent" MEDIUMTEXT,
> "Summary" VARCHAR(255),
> "ModerationState" TINYINT NOT NULL DEFAULT 0,
> "Draft" BOOLEAN NOT NULL DEFAULT FALSE,
> "ContentType" VARCHAR(255),
> "Data" BLOB);
The column in question is "Data".
I can verify that the entire image file has been stored as it is
entirely returned in a SELECT query and displayed in the web browser.
--
Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LENGTH on a BLOB field stops at NUL byte
Hi, I'm storing small files in a BLOB field in the database. I'd like to determine the size of such a file with an SQL query without fetching the data. I tried using the LENGTH() function on that field but it stops counting at the first NUL byte. A 3.2 KiB GIF image is reported to be only 7 bytes long, that's just the file header length. Is this a bug or should I use another function on binary data? The SQLite version is 3.6.20 from PHP 5.3.1 on Windows XP. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Circular foreign keys
On 03.01.2011 13:15 CE(S)T, Drake Wilson wrote: > Quoth Yves Goergen <nospam.l...@unclassified.de>, on 2011-01-03 13:01:17 > +0100: >> So I have foreign keys from message_revision to message and the other >> way around. This obviously won't work because when defining the table >> message, the table message_revision isn't known yet. > > What do you mean? Creating two tables with circular foreign keys > works fine for me. > In particular, if you never create table B, subsequent operations on A > may fail, but the creation succeeds and allows you to create B later. Oh, I didn't even dare executing such statements... but it really works. Enable foreign key constraints, create a table referencing a non-existent table and everything's just fine... Well, then this topic may be regarded solved. :-) -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] XML functions support?
Hi, I'm wondering whether SQLite supports the XML functions ExtractXML and UpdateXML. I couldn't find them in the manual and not in the web. Is it true that I would need to provide them as user functions? Are there any existing implementations for PHP PDO? -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18.09.2009 21:56 CE(S)T, Simon Slavin wrote: > * Support for multiple concurrent clients/processes Doesn't that already work? You need common file system access, right, but then it should work afaik. What I'd like to see is foreign key integrity enforcement. You can already do it with triggers but it would be way easier if the system did that for me. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite 3.6.16.C#
On 01.08.2009 16:19 CE(S)T, Noah Hart wrote: > This is not a driver, dll, or wrapper. This is a port of the underlying > SQLite software. Hm, yes, but isn't the other C# SQLite assembly also the entire DB engine? I mean, there's no client/server driver; if you can access SQLite files, you already have the entire engine in your hands. And the other one doesn't need any additional files, it's just one .NET assembly. And a huge one. So I cannot imagine that it's just some bindings to a native DLL. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite 3.6.16.C#
On 01.08.2009 02:14 CE(S)T, Noah Hart wrote: > I am pleased to announce that the C# port is done to the point where others > can look at it. > > The project is located at http://code.google.com/p/sqlitecs Excuse me, but what's the difference of this to SQLite ADO.NET at http://sqlite.phxsoftware.com/ ? -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Referential IntegrityRE: Foreign key support
On 12.02.2009 06:23 CE(S)T, Roger Binns wrote: > It is true that triggers can be used to achieve referential integrity. > However you don't have to hand craft them. The front page of the wiki > links to the document explaining it: > > http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > > But the SQLite team has already done all the work for you. The SQLite > source includes a program named 'genfkey' that will create the triggers. Wow, didn't know that. Maybe because my last visit on that page is some years ago. So the next step is probably that the SQLite engine does all that on its own, how would that be? :-) -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Referential IntegrityRE: Foreign key support
On 09.02.2009 19:32 CE(S)T, palmer ristevski wrote: > Just wondering when/if Richard > will make SQLite having referential Integrity a priority. > Is it on the TODO list? Is it #1 or #100 on this TODO list. > Because almost all other common databases have this. > > Once it has this feature I think that it will become even more popular. You can already use referential integrity with SQLite - just in a very complicated way, using numerous handcrafted triggers. How it works is documented somewhere in the Wiki, IIRC. -- Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite.Net
On 20.09.2007 15:06 CE(S)T, Samuel R. Neff wrote: > can you be more specific? Thread links.. http://sqlite.phxsoftware.com/forums/t/731.aspx "SQLiteDataReader.GetValue() not returning DateTime" http://sqlite.phxsoftware.com/forums/t/795.aspx "Cannot retrieve data from a column; inconsistent results" Another bug I reported was indeed replied quickly, but the forum didn't notify me although specified. (I'll now do what the reply says...) So the new forum software has other bugs, too. http://sqlite.phxsoftware.com/forums/t/866.aspx "Something's not cleaned up correctly" -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite.Net
On 19.09.2007 17:54 CE(S)T, Samuel R. Neff wrote: > However, I do use the one Robert suggested heavily > and can attest to the fact that it is extremely well written and works > without errors. Ehm, wait, I know of two bugs that have been reported in their forum and were not even replied to for weeks. The one bug should have been fixed, but isn't. But these errors are not critical and can be worked around in the application code (probably decreasing the performance). Still, this is my recommendation for using SQLite in .NET. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 08.09.2007 21:06 CE(S)T, Joe Wilson wrote: > Are you able to store this file in the database itself as a BLOB? No, its an input file that I'm processing and saving the results in the database. When processing and database import are successful, the file must be deleted so that it won't be imported again in a later run. On 08.09.2007 22:16 CE(S)T, Dwight Ingersoll wrote: > Assuming the data files you are working with are of a manageable size, > either read the file into a variable in your code and then process it if the > file delete succeeds, or import the data file into a work table in SQLite, > and then attempt the file delete. And when deleting the file works fine, but processing does not? Then the file is gone and I need to restore it or something. My application must not break in this time and I need to be able to recreate that file... Storing it in a db table sound like the data is safe at any time, but it's still not the original format. Well, maybe deleting to the recycle bin would be an option I could live with. Or if all else fails, giving the database thing a priority and only inform the user if the file could not be deleted. After all, it's not usual that the file can't be deleted or that a database error would occur in my particular case, but it's an interesting question in general, I believe. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can I simulate a COMMIT?
Hi, in a scenario when multiple operations need to be transactionally synchronised, I have a file that must be deleted when the database records are added successfully, but the database operations must be rolled back, if the file cannot be deleted. I'm currently using a transaction for this on the database side and rolling it back if the file cannot be deleted. But what if the file is gone and then SQLite says it doesn't accept my records? Since we're inside a transaction, integrity checks should be deferred until a COMMIT. Is there a way to tell whether the COMMIT will succeed under the current conditions so that I can safely delete the file? Would that work with nested transactions or are integrity checks also deferred to the most outer transaction? I never used nested transactions so I have no experience with it. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table locked - why?
On 31.08.2007 10:45 CE(S)T, Yves Goergen wrote: > Maybe I should retry it with a plain SQLite console and figure out > whether the bug is in the .NET wrapper (just as the previous one I've > found...). Stay tuned... When I do that from an SQLite console, it works as expected. So I'll head over to the SQLite.NET forum. Sorry for bothering you. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table locked - why?
On 31.08.2007 06:03 CE(S)T, Dan Kennedy wrote: > On Fri, 2007-08-31 at 00:09 +0200, Yves Goergen wrote: >> CREATE TEMPORARY TABLE attached_db.temp_table > > I'm not sure where that table is created - in the temporary > namespace or as part of attached_db. Checking... > > SQLite version 3.4.2 > Enter ".help" for instructions > sqlite> attach 'def' as def; > sqlite> create temporary table def.t1(a, b, c); > SQL error: temporary table name must be unqualified > > Huh. Maybe that's the root of your problem there. Sorry, no. I've now created the temp table without the database name prefix but it doesn't help. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table locked - why?
On 31.08.2007 00:50 CE(S)T, Virgilio Alexandre Fornazin wrote: > Did you closed the cursor opened at 'select *...' ? > Thats probably the reason you have getting a 'table is locked' error. I'm using the .NET wrapper to SQLite that should handle all API internals for me. Even disposing the previous SQLiteCommand object from "INSERT INTO ... SELECT * FROM ..." doesn't help here. Maybe I should retry it with a plain SQLite console and figure out whether the bug is in the .NET wrapper (just as the previous one I've found...). Stay tuned... -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table locked - why?
On 31.08.2007 00:23 CE(S)T, RaghavendraK 70574 wrote: > Pls see if u hv an open sqlite3 terminal.sometimes this can also > cause a prob with begin tx and just kept it open. Really, I don't. Trust me. :) -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table locked - why?
On 30.08.2007 23:00 CE(S)T, [EMAIL PROTECTED] wrote: > Probably another thread or process is reading from the database > and does not want you to drop the table out from under it. Surely not. This database is there for testing and development purposes only and my application is the only one that opens it. It doesn't use multiple threads so there also can't be another thread locking it. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Table locked - why?
On 30.08.2007 22:55 CE(S)T, Ken wrote: > BEGIN TRANSACTION; > CREATE TEMPORARY TABLE attached_db.temp_table (...); > INSERT INTO attached_db.temp_table SELECT * FROM attached_db.table1; > > /* Add a comit */ > COMMIT; Error: cannot commit transaction - SQL statements in progress > DROP TABLE attached_db.table1; -- Error: Table is locked -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Introducing... ManagedSQLite
On 27.06.2007 02:27 CE(S)T, Robert Simpson wrote: > If you'd rather communicate directly with the database and process > per-row as quickly as possible, you can use the > DbCommand/DbDataReader model. Hm, okay, that's what I referred to that I am using. > While some may argue whether or not 592k is "lightweight", it's > definitely not in the heavyweight category either. Sure, I don't have any special problem with it. 7zip compresses it down to 250 kB and that's what often goes over the internet. Disk space and memory bandwidth really aren't a problem in that scale. :) -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Crashes and random wrong results with certain column names
On 27.06.2007 01:35 CE(S)T, [EMAIL PROTECTED] wrote: > http://www.sqlite.org/cvstrac/tktview?tn=2450 Wow, I guess from that page that it's already fixed? One question regarding the issue tracker: Is there a reference of what the severity and priority values mean? Is a lower value more or less severe/important? I can't figure that out from the complete listing. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Introducing... ManagedSQLite
On 26.06.2007 00:24 CE(S)T, WHITE, DANIEL wrote: > The main advantage of mine is that it is lightweight and easy to use > without using ADO.NET. Okay, the other SQLite.NET DLL has 592 kB, that's not necessarily "lightweight". I'd like to be able to include the SQLite library into the main assembly so that I won't need a separate DLL anymore. I'm not sure though how good that works with each solution or whether it's a good idea for other reasons. But I'm quite happy with it without using ADO.NET. In fact, I don't like the idea of copying each and every record into memory and having it sorted and filtered there (which ADO.NET propagates) instead of having that done directly in the source database engine... -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to sort not binary?
On 25.06.2007 00:57 CE(S)T, [EMAIL PROTECTED] wrote: > Yves Goergen <[EMAIL PROTECTED]> wrote: >> Oh, well, I just realised that this will only sort numbers "naturally" >> at the beginning of strings, but not in the middle or at the end. It >> will be a bit more complex to do that. Maybe I find a sort/compare >> algorithm for it. But at least I know now that it's easy to use. :) > Check out the "sortStrCmp()" function in older versions of > SQLite version 2 in the util.c source file. See, for example, > >http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/util.c=1.42 > > I think the sortStrCmp() function does what you are after. Okay, first thank you for pointing me on this. But even with the good documentation about the state machine implemented, this surely is highly optimised C "hack"* code that I probably won't understand so soon. Another problem is that I can't easily translate this into C# because there's no such thing as pointers to strings (character arrays). I'll keep on searching for a function for that. Maybe, given enough time, I'll come up with my own one... Some day... ;-) *) I mean, come on, using negative array indices on pointers... cb = b[-1]; ;-) -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Crashes and random wrong results with certain column names
On 24.06.2007 00:17 CE(S)T, Yves Goergen wrote:
> CREATE TABLE "t a" ("c a", """cb""");
> (...)
And this may take forever to still not finish:
DROP TABLE "t a";
It then worked in a second try.
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Crashes and random wrong results with certain column names
Hi,
I've tested my own SQLite application's identifier quoting capabilities
now and found that the SQLite engine has serious problems with
table/column names containing certain special characters. Just try the
following:
CREATE TABLE "t a" ("c a", """cb""");
INSERT INTO "t a" ("c a", """cb""") VALUES (1, 2);
INSERT INTO "t a" ("c a", """cb""") VALUES (11, 12);
INSERT INTO "t a" ("c a", """cb""") VALUES (21, 22);
SELECT * FROM "t a";
The final select statement will either crash the client (this is true
for the Win32 command line client version 3.4.0, which is simply
terminated (it was more impressive with 3.3.6), and the ADO.NET adaption
System.Data.SQLite) or, if you're "lucky", return random garbage data
for some rows and columns.
Also, when it does not crash, the result set column name for the "cb"
column is simply labelled cb, i.e. without the double quotes. Viewing
the original SQL CREATE TABLE statement from the sqlite_master table or
using PRAGMA table_info("t a") will show the correct column names though.
I could not see any problems yet in my short tests with spaces and
double quotes in column names, but when the double quotes are the first
and last character of a column's name, things start to get crazy.
Is this a bug or am I simply not supposed to use such column names? (I
was reading the formal SQL-92 syntax definition recently and thought,
why not just try it out...)
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: How to sort not binary?
On 22.06.2007 17:48 CE(S)T, Yves Goergen wrote:
> Match m1 = Regex.Match(param1, "^([0-9]+)");
> if (m1.Success)
> {
> Match m2 = Regex.Match(param2, "^([0-9]+)");
> if (m2.Success)
> {
> int cmpNum = int.Parse(m1.Groups[1].Value) -
> int.Parse(m2.Groups[1].Value);
> if (cmpNum != 0) return cmpNum;
> }
> }
> return String.Compare(param1, param2, true);
Oh, well, I just realised that this will only sort numbers "naturally"
at the beginning of strings, but not in the middle or at the end. It
will be a bit more complex to do that. Maybe I find a sort/compare
algorithm for it. But at least I know now that it's easy to use. :)
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: How to sort not binary?
On 12.05.2007 22:57 CE(S)T, Ingo Koch wrote:
> Yves Goergen wrote:
>
>> I guess that doesn't work when I'm accessing the database through the
>> System.Data.SQLite interface in .NET?
>
> Fortunately your guess is wrong. ;-) System.Data.SQLite supports
> user defined collation sequences. See TestCases.cs of the source
> distribution for samples how to implement them.
Thank you for the reply. I managed to try it out now and got it working
really fast! Here's my solution, tightly adapted from TestCases.cs:
///
/// User-defined collating sequence which does natural sorting.
///
[SQLiteFunction(Name = "NATSORT", FuncType = FunctionType.Collation)]
class NaturalSorting : SQLiteFunction
{
public override int Compare(string param1, string param2)
{
Match m1 = Regex.Match(param1, "^([0-9]+)");
if (m1.Success)
{
Match m2 = Regex.Match(param2, "^([0-9]+)");
if (m2.Success)
{
int cmpNum = int.Parse(m1.Groups[1].Value) -
int.Parse(m2.Groups[1].Value);
if (cmpNum != 0) return cmpNum;
}
}
return String.Compare(param1, param2, true);
}
}
Then, without anything else, do a query like:
SELECT * FROM table1 ORDER BY column1 COLLATE NATSORT;
You can even debug the user-defined function from VS 2005. I don't have
the impression that it runs considerably slower with my 5000 records
sorting on 3 solumns (where in many cases the first already decides).
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: How to sort not binary?
On 13.05.2007 17:19 CE(S)T, Chris Wedgwood wrote: > On Sun, May 13, 2007 at 05:07:16PM +0200, Yves Goergen wrote: > >> Ah, now I realised that I'd also like to have that "natural sorting", >> meaning this: >> >> 2 >> 8 >> 9 >> 10 >> 11 >> 23 > > select from from table order by cast( as text); I'm not sure what you wanted to say with this. I removed the first "from" to make it work but it does the same as without the cast. It still sorts strings beginning with "10" before those beginning with "2". -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 12.05.2007 22:57 CE(S)T, Ingo Koch wrote: > Yves Goergen wrote: > >> I guess that doesn't work when I'm accessing the database through the >> System.Data.SQLite interface in .NET? > > Fortunately your guess is wrong. ;-) System.Data.SQLite supports > user defined collation sequences. See TestCases.cs of the source > distribution for samples how to implement them. Ah, now I realised that I'd also like to have that "natural sorting", meaning this: 2 8 9 10 11 23 instead of: 10 11 2 23 8 9 Can this be done with a user-defined collation, too? How does this all impact on the performance? I have over 5.000 rows to sort on 3 columns and would like to do that in near-real-time. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 12.05.2007 17:33 CE(S)T, Igor Tandetnik wrote: > Yves Goergen <[EMAIL PROTECTED]> > wrote: >> I'm trying to get my table sorted the way how for example Windows >> Explorer or other file managers are sorting names. Most of all, >> accented characters should not be listed at the end of the list but >> near their non-accented character instead. I can only see the >> built-in collations BINARY and NOCASE. Is there something else? > > You need to install a custom collation. See sqlite3_create_collation[16] I guess that doesn't work when I'm accessing the database through the System.Data.SQLite interface in .NET? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to sort not binary?
Hi, I'm trying to get my table sorted the way how for example Windows Explorer or other file managers are sorting names. Most of all, accented characters should not be listed at the end of the list but near their non-accented character instead. I can only see the built-in collations BINARY and NOCASE. Is there something else? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Performance of Joins
On 11.04.2007 21:38 CE(S)T, Igor Tandetnik wrote: > No. The other table affects the result of the query, so the join still > has to be performed. I see. I haven't thought of that effect. Thank you for the reply. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Performance of Joins
Hello, I was thinking about what happens when I do an SQL query that names tables and joins between them that aren't used at all. For example this: SELECT m.Sender, m.Subject FROM Message m, MessageTag mt; Does it open and read the table MessageTag at all? Also, what happens in a more complex example where a more explicit join is done but never used: SELECT m.Sender, m.Subject FROM Message m NATURAL JOIN MessageTag mt; Or: SELECT m.Sender, m.Subject FROM Message m LEFT JOIN MessageTag mt USING (MessageId); Does it impact performance when the join appears in the query or will the optimiser remove it? I haven't seen anything about this in the "query optimiser overview" documentation. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Data integrity in extreme situations
Hi, I'm intending to use the SQLite database in a desktop e-mail application. Since most e-mails are quite valuable to me, I'd like to know if SQLite databases can get corrupted during normal use and exceptional situations like a sudden application/OS crash, power failure or access blocking through a virus scanner. Can it happen that under these circumstances all/arbitrary data from the database file can be lost or does that only affect the data currently being written, if any. How does it relate to transactions? I'm considering doing a full database backup each time the application quits but keeping the entire file twice may take much disk space. Maybe using two files, one for current e-mails and one as growing archive (that doesn't change often), would be a possibility. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Apostrophes in strings
Hi everyone,
I found the SQLite DLL for PHP today and just wanted to do some testing
with it. I could rewrite my dump of the MySQL tables and data so that
SQLite would understand it. But there's a fundamental problem left. I
have strings that contain ' characters. But strings are written in ''
characters theirselves. MySQL would want a 'Rock\'n\'Roll' here, so '
get backlash-escaped. But there seems to be a problem with it. I get no
detailled error description, but when I remove all \' it works. Here's
my SQL and result:
---SQL
INSERT INTO tasks VALUES (1, 1, 1091182902, 'Access rules are not
applied to forums\' categories', 'ACL system:\nRules are only applied to
the specified and subsidiary forums. Guests may not be able to see an
actually accessible forum inside a category which they have no view
rights for.\n-> Extend forum rights to directly superior categories in a
way that doesn\'t open all other forums', 'ACL apply', 5, 1, 1,
1091225287, 4, 1, 2, 0, 0, 0, 1, '1.5-dev-20040729', '1.6', 0, 0, '');
---
---PHP code
sqlite_query($db, join(file('bugs.sql')));
---
---PHP output
Warning: sqlite_query(): unrecognized token: ":" in
C:\wwwroot\projects\bugtracker\sql.php on line 4
---
It means the : behind the \' I guess. Removing all \' helps to execute
the query.
So how can I insert ' characters into a table other than by \'?
PS: Subscription to this list has not replied my anything within an
hour, I home I'll get replies to this post. Please CC me to be sure. TIA
--
Yves Goergen <[EMAIL PROTECTED]>
BlackBoard Internet Newsboard System --> blackboard.unclassified.de
Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL)
