[sqlite] SQLite database becomes corrupt on iOS
On Aug 14, 2015, at 10:13 AM, Simon Slavin wrote: > On 14 Aug 2015, at 5:16pm, Random Coder wrote: >> I've run into other issues >> that lead me to believe the OS is caching file writes until the app >> exits in some situations regardless of various sync calls, but I never >> did have time to track down if I was just fooling myself, or if the OS >> was indeed doing things to "help" me out. > > The hardware in iDevices varies (obviously) between the many devices which > have been produced over the years. But I believe that you're right in that > it's not possible for the OS to tell when data has /really/ been written to > permanent storage. Such writing takes a lot of power and it makes sense that > a device would want to do it infrequently. If the device runs out of power normally, your writes are guaranteed. However, your app will get no notice. It's equivalent to getting jetsamed during low-memory conditions. That said, it's possible to corrupt a database by forcing the device to power off (by holding the power and home buttons) while SQLite's writes are in flight. This is equivalent to the conditions of a kernel panic. Using the system-provided SQLite helps, the unix VFS is modified to be (somewhat) more robust on Apple's platforms.
[sqlite] System.Data.SQLite version 1.0.98.0 released
> > System.Data.SQLite version 1.0.98.0 (with SQLite 3.8.11.1) is now available > on the System.Data.SQLite website: > Great news, thanks Joe!! Regards Steffen
[sqlite] ATTACH DATABASE statement speed
On Wed, Aug 19, 2015 at 11:44 AM, Simon Slavin wrote: > Hmm. Would it be possible to format an external drive in ZFS and try the > operations on files stored on that ? As you might have guessed from the timezone I am not at home atm, so I do not have spare external disks. However, I do have an expendable 16BG usb stick so I tried on that. First I formatted it using zfs and I did the Pragma quick_check; I killed it after 40 minutes. Secondly I formatted it using ext4 and the Pragma quick_check; it finished in about 25 minutes. Now, I am trying again with zfs. But I think it already shows something is indeed wrong.
[sqlite] ATTACH DATABASE statement speed
I see. Thanks nameless person known as sqlite-mail (npkasm for short), what you say makes sense. However it does not explain why the pragma checks are so slow. Anyhow, npkasm, I will keep in mind for the future. Good point indeed. On Wed, Aug 19, 2015 at 3:59 PM, sqlite-mail wrote: > Hello ! > > The problem with foreign keys most of the time is not the the referenced > table/field (normally primary key that do no need extra index) but the > dependent table/field when they do not have a proper index, any time you > update/delete a record on the referenced table a linear scan is performed on > all dependent tables and that can be a lot time consuming depending on the > number of records on then. > > I've got this problem on a heavily foreign key constrained database and it > took me a bit to realize that ! > > Cheers ! >> @nameless person known as sqlite-mail, >> Yes, I do have foreign keys. But each relate to a primary key; there >> are no explicit indexes on this primary keys, but they should not be >> needed because primary keys are indexed automatically. >> Or are they? >> >> >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting row number in a sorted list.
>
> SELECT count(sub.Name) + 1 AS Rank, a.Name
> FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name <
> a.Name
> WHERE a.Name LIKE 'P%'
> GROUP BY a.Name
> ORDER BY a.Name
> LIMIT 1
>
>
> -- Rank | Name
> -- | --
> -- 4 | PQRS
I should add that the group by is just there to make the COUNT() behave
as expected, if you need the rank even in duplicates when there are
multiples of the same name (not that that would make any sense
realistically, but let's imagine there exists such a use-case) then
simply add a temp table with a column with RANDOM() appended to the name
and use that for the order.
Same example as before but with duplicate names:
create table NameTable(Name TEXT);
insert into NameTable VALUES
('PTN'),
('ABCD'),
('CDE'),
('ABCD'),
('PQRS'),
('ABCD'),
('ABCD'),
('AXN');
CREATE TEMPORARY TABLE tmpTable AS SELECT Name, Name||random() AS UName
FROM NameTable;
SELECT * FROM tmpTable;
-- Name | UName
-- -- | --
-- PTN| PTN670595216556973252
-- ABCD | ABCD3088193799719600707
-- CDE| CDE2011182050635024217
-- ABCD | ABCD-6134681665725239567
-- PQRS | PQRS1314027443609404785
-- ABCD | ABCD4099207489085812545
-- ABCD | ABCD-7190663061184182030
-- AXN| AXN9089277539697356029
SELECT count(sub.UName) + 1 AS Rank, a.Name
FROM tmpTable AS a LEFT OUTER JOIN tmpTable AS sub ON sub.UName < a.UName
GROUP BY a.UName
ORDER BY a.UName;
-- Rank | Name
-- | --
-- 1 | ABCD
-- 2 | ABCD
-- 3 | ABCD
-- 4 | ABCD
-- 5 | AXN
-- 6 | CDE
-- 7 | PQRS
-- 8 | PTN
SELECT count(sub.UName) + 1 AS Rank, a.Name
FROM tmpTable AS a LEFT OUTER JOIN tmpTable AS sub ON sub.UName < a.UName
WHERE a.Name LIKE 'P%'
GROUP BY a.UName
ORDER BY a.UName
LIMIT 1;
-- Rank | Name
-- | --
-- 7 | PQRS
Also: Add temp Index on the temp table if it is large and clean up the
table afterward, of course.
NOTE: A query asking for the rank of 'ABCD' or even LIKE 'A%' will
ALWAYS return 1 because the first 'ABCD' is at position 1, even though
there are 'ABCD' names ranked at 2, 3 and 4. (Which is why this is
non-sensical, but there it is no less!).
[sqlite] Getting row number in a sorted list.
On 2015-08-19 03:02 PM, John McKown wrote:
> On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin wrote:
>
>> 1|ABCD
>> 2|CDE
>> 4|AXN
>> 5|AXN2
>> 6|PQRS2
>> sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ'
>> limit 1;
>> 6
>>
>>
>> Hum, that probably isn't what the OP wanted. I would guess in this latter
>> table, he would want "5" because that is the _relative_ row number you
>> would see by counting if you did a simple "SELECT Name FROM NameTable;"
>>
>> The basic problem is that the question is "improper". In general, there is
>> no "relative row number" for the rows listed by a "SELECT" command. Oh,
>> there is when you look at it. But the order of the rows returned where then
>> is no ORDER BY clause is not guaranteed. And, even then, the order of
>> individual rows which have the same ordering with the ORDER BY are not
>> guaranteed. What I mean is, if you do SELECT A,B,C FROM TABLE ORDER BY A, B
>> ; and there are two or more rows with equal A & B values, then the order of
>> the C values is not guaranteed.
>>
>> So, what to do? Well, it would be possible to do something like:
>>
>> sqlite> drop table if exists row_order;
>> sqlite> create temporary table row_order AS select * from NameTable;
>> sqlite> select rowid from row_order where Name between 'P' and 'PZZZ'
>> limit 1;
>> 5
>> sqlite> select rowid, Name from row_order;
>> 1|ABCD
>> 2|CDE
>> 3|AXN
>> 4|AXN2
>> 5|PQRS2
>> sqlite> select rowid, Name from NameTable;
>> 1|ABCD
>> 2|CDE
>> 4|AXN
>> 5|AXN2
>> 6|PQRS2
>> sqlite>
>>
>> This may answer the OP's question. But it only works for SQLite. And I am
>> not sure that it is guaranteed to work on all past and future versions of
>> SQLite. It depends on the non-standard ROWID facility in SQLite. Perhaps
>> Dr. Hipp can address this last issue.
I agree the OP's question is not well-formed. We do not know if he needs
the row id in order to effect an update perhaps, or if he needs the rank
in terms how-many-eth a row appears in an ordered list.
If it is simply the row id, then Simon's solution will work perfectly,
and if it is the rank he would rather want, your solution would work on
SQLite - but there is also a more universal SQL based approach that
would work on any DB by simply linking a table to itself and counting
the instances lower down the order, like this quick script demonstrates:
(Ref: I was first shown this method by James Lowden as explained on
schemamania.org)
create table NameTable(Name TEXT);
insert into NameTable VALUES
('PTN'),
('ABCD'),
('PZZZ'),
('CDE'),
('PQRS'),
('AXN');
SELECT rowid, Name FROM NameTable;
-- rowid| Name
-- | --
-- 1 | PTN
-- 2 | ABCD
-- 3 | PZZZ
-- 4 | CDE
-- 5 | PQRS
-- 6 | AXN
-- Rank the names in Alphabetical order:
--
SELECT count(sub.Name) + 1 AS Rank, a.Name
FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name < a.Name
GROUP BY a.Name
ORDER BY a.Name;
-- Rank | Name
-- | --
-- 1 | ABCD
-- 2 | AXN
-- 3 | CDE
-- 4 | PQRS
-- 5 | PTN
-- 6 | PZZZ
-- And now to only get the specific item's rank, simply include
-- a WHERE clause and LIMIT clause:
--
SELECT count(sub.Name) + 1 AS Rank, a.Name
FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name < a.Name
WHERE a.Name LIKE 'P%'
GROUP BY a.Name
ORDER BY a.Name
LIMIT 1
-- Rank | Name
-- | --
-- 4 | PQRS
-- 2015-08-19 15:32:33.424 | [Success]Script Success.
[sqlite] Getting row number in a sorted list.
On 19 Aug 2015, at 2:00pm, R.Smith wrote: > Seriously though, if that column is not COLLATE NOCASE declared, 'PZZZ' will > fail. Either ensure your column has COLLATE NOCASE or perhaps simply choosing > the highest (non UTF-8) character such as: > > WHERE name BETWEEN 'P' AND 'P~' > > will suffice. Both good points about what I wrote. In fact the OP should do both. Simon.
[sqlite] Getting row number in a sorted list.
Simon Slavin wrote: > SELECT rowid FROM NameTable > WHERE name BETWEEN 'P' AND 'P' > > This will execute faster if you have an index on 'name' in NameTable. > > [Yes I know 'P' is lazy. Until you find someone with that name > (presumably Polish) with that name bite me.] If the column has text affinity, and if there is an index declared as COLLATE NOCASE, this query will run fast (without case sensitivity) with LIKE: ... WHERE name LIKE 'P%' If the column has text affinity, and if there is a normal (case sensitive) index, this query will run fast with GLOB: ... WHERE name GLOB 'P*' Regards, Clemens
[sqlite] Getting row number in a sorted list.
On 2015-08-19 02:40 PM, Simon Slavin wrote: > > SELECT rowid FROM NameTable > WHERE name BETWEEN 'P' AND 'P' > ORDER BY name > LIMIT 1 > > This will execute faster if you have an index on 'name' in NameTable. > > [Yes I know 'P' is lazy. Until you find someone with that name > (presumably Polish) with that name bite me.] Reminds me of my great-aunt, Penna Borowitz. I do miss her! Seriously though, if that column is not COLLATE NOCASE declared, 'PZZZ' will fail. Either ensure your column has COLLATE NOCASE or perhaps simply choosing the highest (non UTF-8) character such as: WHERE name BETWEEN 'P' AND 'P~' will suffice. Cheers! Ryan
[sqlite] .NET - Using SQLite in an Universal Windows library without LINQ
Hi Mat, We have been using SQLite in our Windows universal application both for tablet and phone We used SQlitePCL as the wrapper library to write our queries in plain sql . check for more details here http://codifyit.blogspot.in/2015/04/using-sqlite-in-your-windows-store-apps.html cheers, Saurav On Wed, Aug 19, 2015 at 1:16 PM, Mathieu Sicard wrote: > Hi, > > I was previously using System.Data.SQLite in a .NET library, and now want > to port this library to an Universal Windows library. > > I cannot make the System.Data.SQLite work (since I guess it is not > portable), and found only LINQ style SQLite PCL libraries in NuGet ... > > Is there a way that I can use good all fashion queries in an Universal > Windows App in order to have the same or close syntax as in > System.Data.SQLite? > > Something like this : > > SQLiteCommand dbCommand = new SQLiteCommand(dbConnection); > dbCommand.CommandText = dbQuery; > SQLiteDataReader reader = dbCommand.ExecuteReader(); > > Mat. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
On 19 Aug 2015, at 1:44pm, Richard Hipp wrote: > On Unix, unlink() after open is used. > > On Windows, the FILE_FLAG_DELETE_ON_CLOSE flags is used when the > temporary file is opened. I was wrong. Apologies. Simon.
[sqlite] Getting row number in a sorted list.
On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) wrote: > Example, for below entries in NameTable > > Name > > 1. PTN > > 2. ABCD > > 3. CDE > > 4. PQRS > > 5. AXN > > > I want to get the row number of the first name that starts with 'P' in the > sorted list. Here it's going to be row number 4 (PQRS)in the sorted list. > > I need the row number, not the entry itself for my use case. How do I form a > query to achieve this? SELECT rowid FROM NameTable WHERE name BETWEEN 'P' AND 'P' ORDER BY name LIMIT 1 This will execute faster if you have an index on 'name' in NameTable. [Yes I know 'P' is lazy. Until you find someone with that name (presumably Polish) with that name bite me.] Simon.
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
On 19 Aug 2015, at 1:36pm, Keith Medcalf wrote: > Meaning that on a persistent temp storage the files will stay forever (or > until a manually deleted). Then again, on systems such as windows where temp > files are never deleted this is to be expected. Hmm. On every Unix box I've seen /tmp gets cleaned up either on shutdown or on startup. (With the possible exception of 'safestart' or 'safeboot' where implemented.) Are you telling me that Windows doesn't do either ? No wonder the hard disks fill up. Simon.
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
On 18 Aug 2015, at 8:40pm, Sam Roberts wrote: > The docs say you have to close the DB handle to clean them up. I'm > concerned that if a process is SIGKILLed or just exits abruptly that > the temporary DBs will accumulate on disk. > > What mechanism is used to create the temporary files? If the file is > unlinked after open, then process exit is sufficient, but I haven't > been able to trigger data overflow onto disk in my quick testing. The file is not unlinked after open. This can't be done because the files may be closed and reopened in the course of their use. So if your process is forced-quit then the temporary files will continue to exist. Some temporary files have fixed names so they will be replaced the next time SQLite tries to perform the same operation, and deleted when that one finishes. Others will just hang about until the computer is rebooted and will be deleted with other temporary files either on shutdown or on restart. This is the same thing that happens to other files in the temporary file folder. Simon.
[sqlite] Getting row number in a sorted list.
Hi, I have a database with NameTable having name records in it, I need to get the row number of the first record in the sorted list of names for which the search name matches. Example, for below entries in NameTable Name 1. PTN 2. ABCD 3. CDE 4. PQRS 5. AXN I want to get the row number of the first name that starts with 'P' in the sorted list. Here it's going to be row number 4 (PQRS)in the sorted list. I need the row number, not the entry itself for my use case. How do I form a query to achieve this? Thanks, Sabeel
[sqlite] System.Data.SQLite version 1.0.98.0 released
System.Data.SQLite version 1.0.98.0 (with SQLite 3.8.11.1) is now available on the System.Data.SQLite website: https://system.data.sqlite.org/ Further information about this release can be seen at: https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you encounter any problems with this release. -- Joe Mistachkin
[sqlite] ATTACH DATABASE statement speed
> > is there any target date when the preRelease branch gets over to a actual > release? > > Is a really hard show stopper for our development at the moment. We checked > everything for compatibility before merge your current trunk to Visual > Studio 2015 and we forget about the SQLite design tool. :( > Sorry! Wrong thread...
[sqlite] System.Data.SQLite 1.0.98.0 release
Hi, is there any target date when the preRelease branch gets over to a actual release? Is a really hard show stopper for our development at the moment. We checked everything for compatibility before merge your current trunk to Visual Studio 2015 and we forget about the SQLite design tool. :( Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
Hi, is there any target date when the preRelease branch gets over to a actual release? Is a really hard show stopper for our development at the moment. We checked everything for compatibility before merge your current trunk to Visual Studio 2015 and we forget about the SQLite design tool. :( Regards Steffen Mangold
[sqlite] SQLite database in a Windows CE6.0 mobile device
Hi I am trying to run a SQLite database in a CE6.0 device (Psion Omnii XT15). It runs the demo from sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.97.0.zip, which creates a db from testce.exe, but on running my VB.net app it produces an error message - File or assembly name Microsoft.VisualBasic Version=8.0.0.0 ... not found. How do I get around the Microsoft.VisualBasic problem? What resources does testce.exe need? Or may I see the testce source code? Thank you. Jonathan Trahair
[sqlite] ATTACH DATABASE statement speed
@Simon I tried zpool scrub on both my disks and it returned nothing, I also tried executing stress[1] on the disk and no error appeared in the log or in stress itself. However, coping the sqlite db on an external disk connected via usb3 and formatted with Ntfs actually does the pragma quick_check in little more than 20 seconds and pragma integrity_check in 5 minutes. So I think it is not an hardware problem, but Zfs messes up somewhat. Is there any known disagreement between Zfs and sqlite? In fact I have this feeling that my system is working fine in everything a part of sqlite. I read in the man that recordsize could be relevant. Otherwise I have to reinstall the system. But it is of course time consuming. [1] http://people.seas.harvard.edu/~apw/stress/ @nameless person known as sqlite-mail, Yes, I do have foreign keys. But each relate to a primary key; there are no explicit indexes on this primary keys, but they should not be needed because primary keys are indexed automatically. Or are they? @Marcus Grimm, It seems have no effect actually in my disk. The test goes fast for a while and slow down after. On Tue, Aug 18, 2015 at 9:12 PM, Marcus Grimm wrote: > Just another guess: > Have you tried to increase the page chache drastically ? > I can remeber that "PRAGMA quick_check" is pretty slow > for bigger DBs without an increased page cache. > Maybe something like: > PRAGMA cache_size=50; > PRAGMA quick_check; > > Marcus > > > Am 18.08.2015 um 12:38 schrieb Paolo Bolzoni: >> >> It really seems something strange happens at filesystem level. >> >> This is a simple copy of slightly less than 1gb. It needs 9 seconds >> including sync. >> % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date >> Tue Aug 18 19:22:23 JST 2015 >> sending incremental file list >> italy-latest.osm.pbf >> 946,976,283 100% 123.88MB/s0:00:07 (xfr#1, to-chk=0/1) >> Tue Aug 18 19:22:32 JST 2015 >> >> >> However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks >> normal for a while. >> (I hope gmail don't mess up with the formatting...) >> >> 60, 90, 80 MB/s is kinda expected: >> 08/18/2015 07:27:38 PM >> Device: rrqm/s wrqm/s r/s w/srMB/swMB/s >> avgrq-sz avgqu-sz await r_await w_await svctm %util >> encplate >>0.00 0.00 820.00 13.0062.11 0.26 >> 153.34 1.872.271.14 73.46 1.20 99.80 >>0.00 0.00 1214.500.0094.58 0.00 >> 159.49 0.960.780.780.00 0.78 95.20 >>0.00 0.00 1008.50 22.0078.09 0.41 >> 155.99 1.501.460.96 24.16 0.93 95.80 >> >> but after some seconds it drops terribly to less than 10MB/s >> 08/18/2015 07:29:04 PM >> Device: rrqm/s wrqm/s r/s w/srMB/swMB/s >> avgrq-sz avgqu-sz await r_await w_await svctm %util >> encplate >>0.00 0.00 124.003.50 9.88 0.12 >> 160.72 1.67 12.99 11.21 76.14 7.65 97.50 >>0.00 0.00 69.00 18.00 5.68 0.29 >> 140.55 1.81 20.92 14.15 46.86 11.38 99.00 >>0.00 0.00 86.000.00 7.05 0.00 >> 167.91 1.04 12.03 12.030.00 11.24 96.70 >> >> And so, going to 10MB per second it can easily require few hours... >> >> >> I am out of ideas, but thanks for all the support. >> >> >> >> On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin >> wrote: >>> >>> >>> On 18 Aug 2015, at 7:30am, Paolo Bolzoni >>> wrote: >>> Any other idea of what can I try? Perhaps my filesystem is misconfigured? >>> >>> >>> The long time you quote is not standard for SQLite and I don't think >>> anyone can help you solve it by knowing picky details of SQLite. I'm even >>> surprised that it changed with your -O0 compilation since this suggests >>> features of your compiler I didn't know about. >>> >>> It's possible one of the developer team can help but they're reading this >>> and can pitch in if they think so. >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
On 19 Aug 2015, at 10:46am, Paolo Bolzoni wrote: > As you might have guessed from the timezone I am not at home atm, so I > do not have spare external disks. > However, I do have an expendable 16BG usb stick so I tried on that. > > First I formatted it using zfs and I did the Pragma quick_check; I > killed it after 40 minutes. > Secondly I formatted it using ext4 and the Pragma quick_check; it > finished in about 25 minutes. > > Now, I am trying again with zfs. But I think it already shows > something is indeed wrong. Just by itself, the above information is significant to the SQLite team. Perhaps when you have had a chance to confirm it a new thread can be started called 'SQLite database on ZFS is very slow'. This should pull any ZFS experts out of the woodwork. Unfortunately I am not one of them. Simon.
[sqlite] .NET - Using SQLite in an Universal Windows library without LINQ
Hi, I was previously using System.Data.SQLite in a .NET library, and now want to port this library to an Universal Windows library. I cannot make the System.Data.SQLite work (since I guess it is not portable), and found only LINQ style SQLite PCL libraries in NuGet ... Is there a way that I can use good all fashion queries in an Universal Windows App in order to have the same or close syntax as in System.Data.SQLite? Something like this : SQLiteCommand dbCommand = new SQLiteCommand(dbConnection); dbCommand.CommandText = dbQuery; SQLiteDataReader reader = dbCommand.ExecuteReader(); Mat.
[sqlite] pragma temp_store_directory is deprecated, what is the alternative?
Wouldn't be easier to simply add a parameter to sqlite3_initialize()? E.g., a char const pointer to the tmp directory? That, if null, defaults to something reasonable as James mentioned? Maybe I am oversensitive, but I found strange I have to use setenv to setup a command line option about "where to put tmp files" in my program... On Tue, Aug 18, 2015 at 11:02 PM, James K. Lowden wrote: > On Sat, 15 Aug 2015 01:17:28 +0100 > Simon Slavin wrote: > >> > BTW, Posix is almost silent on the question. It says TMPDIR will >> > define the location of a temporary store, but not how. >> >> I'm okay if the documentation simply says something like ... >> >> For Darwin (Mac), it's always /tmp/ >> For Linux, see the TMPDIR environment variable >> For Windows see [whatever it is] > > Agreed, although IIUC it could be simpler than that, see next. > >> However I suspect things may be more complicated than that. For >> instance, does .NET respect the OS's choice of temporary directory no >> matter which OS it's running under ? I have no idea. > > What I'm suggesting is that there is no "OS's choice", really. > > There are a few functions in the C standard library, e.g. tmpfile(3), > that may consult the environment. The variable's name varies by > implementation. Some implementations, notably GNU's (if the > documentation is correct), do not consult the environment. > > I would guess .NET is written atop the Win32 API and uses > GetTempFileName or somesuch. That uses GetTempPath, whose return value > is affected by TMP and TEMP. > (https://msdn.microsoft.com/en-us/library/windows/desktop/aa364992 > (v=vs.85).aspx). > > GetTempPath and tmpnam(3) on Windows both honor TMP, but the fallback > policies differ. So it's not really a question of what the OS's choice > is, because the *OS* offers no "temporary file" function. It's really a > question of which library function is called, and how that function is > implemented. > > But none of that matters unless those functions are used. An > application -- or library, as in SQLite's case -- need not use them, > which in any case aren't all that helpful. AIUI SQLite does *not* use > those functions, but rather has its own way to determine where temporary > files go. In that case the rule could be quite simple and > OS-independent. For instance, > > 1. Use "SQLITE_TMPDIR" if defined > 2. Use current working directory otherwise > > where the value is set by sqlite3_initialize and cannot be changed > thereafter. > > --jkl > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ATTACH DATABASE statement speed
Hello ! The problem with foreign keys most of the time is not the the referenced table/field (normally primary key that do no need extra index) but the dependent table/field when they do not have a proper index, any time you update/delete a record on the referenced table a linear scan is performed on all dependent tables and that can be a lot time consuming depending on the number of records on then. ? I've got this problem on a heavily foreign key constrained database and it took me a bit to realize that ! Cheers ! > @nameless person known as sqlite-mail, > Yes, I do have foreign keys. But each relate to a primary key; there > are no explicit indexes on this primary keys, but they should not be > needed because primary keys are indexed automatically. > Or are they? > > >
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
On 8/18/15, Sam Roberts wrote: > What mechanism is used to create the temporary files? On Unix, unlink() after open is used. On Windows, the FILE_FLAG_DELETE_ON_CLOSE flags is used when the temporary file is opened. -- D. Richard Hipp drh at sqlite.org
[sqlite] Getting row number in a sorted list.
On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin wrote:
>
> On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) <
> santhrat at visteon.com> wrote:
>
> > Example, for below entries in NameTable
> >
> > Name
> >
> > 1.
> ??
> PTN
> >
> > 2. ABCD
> >
> > 3. CDE
> >
> > 4. PQRS
> >
> > 5. AXN
> >
> >
> > I want to get the row number of the first name that starts with 'P' in
> the sorted list. Here it's going to be row number 4 (PQRS)in the sorted
> list.
> >
> > I need the row number, not the entry itself for my use case. How do I
> form a query to achieve this?
>
> SELECT rowid FROM NameTable
> WHERE name BETWEEN 'P' AND 'P'
> ORDER BY name
> LIMIT 1
>
> This will execute faster if you have an index on 'name' in NameTable.
>
> [Yes I know 'P' is lazy. Until you find someone with that name
> (presumably Polish) with that name bite me.]
>
> Simon.
>
?Interesting. What happens if somebody did: CREATE TABLE NameTable (Name
text PRIMARY KEY) WITHOUT ROWID ??
?Also, what about the following:
sqlite> create table NameTable (Name TEXT);
sqlite> insert into NameTable(Name) Values('ABCD');
sqlite> insert into NameTable(Name) Values('CDE');
sqlite> insert into NameTable(Name) Values('PQRS');
sqlite> insert into NameTable(Name) Values('AXN');
sqlite> SELECT rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ'
limit 1;
3
sqlite> insert into NameTable(Name) Values('AXN2');
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
5|AXN2
sqlite> insert into NameTable(Name) Values('PQRS2');
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
5|AXN2
6|PQRS2
sqlite> delete NameTable where Name='PQRS';
Error: near "NameTable": syntax error
sqlite> delete from NameTable where Name='PQRS';
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
4|AXN
5|AXN2
6|PQRS2
sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ'
limit 1;
6
Hum, that probably isn't what the OP wanted. I would guess in this latter
table, he would want "5" because that is the _relative_ row number you
would see by counting if you did a simple "SELECT Name FROM NameTable;"
The basic problem is that the question is "improper". In general, there is
no "relative row number" for the rows listed by a "SELECT" command. Oh,
there is when you look at it. But the order of the rows returned where then
is no ORDER BY clause is not guaranteed. And, even then, the order of
individual rows which have the same ordering with the ORDER BY are not
guaranteed. What I mean is, if you do SELECT A,B,C FROM TABLE ORDER BY A, B
; and there are two or more rows with equal A & B values, then the order of
the C values is not guaranteed.
So, what to do? Well, it would be possible to do something like:
sqlite> drop table if exists row_order;
sqlite> create temporary table row_order AS select * from NameTable;
sqlite> select rowid from row_order where Name between 'P' and 'PZZZ'
limit 1;
5
sqlite> select rowid, Name from row_order;
1|ABCD
2|CDE
3|AXN
4|AXN2
5|PQRS2
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
4|AXN
5|AXN2
6|PQRS2
sqlite>
This may answer the OP's question. But it only works for SQLite. And I am
not sure that it is guaranteed to work on all past and future versions of
SQLite. It depends on the non-standard ROWID facility in SQLite. Perhaps
Dr. Hipp can address this last issue.
?
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
> On 19 Aug 2015, at 1:36pm, Keith Medcalf wrote: > > Meaning that on a persistent temp storage the files will stay forever > (or until a manually deleted). Then again, on systems such as windows > where temp files are never deleted this is to be expected. > Hmm. On every Unix box I've seen /tmp gets cleaned up either on shutdown > or on startup. (With the possible exception of 'safestart' or 'safeboot' > where implemented.) > Are you telling me that Windows doesn't do either ? No wonder the hard > disks fill up. Hehehehe. It is a plot to sell new computers every year or so (and Operating Systems). This is a long standing issue with Windows since about Windows 1.0 -- though back then TEMP files were stored in the TEMP file directory under DOS, this is no longer the case. With each new version of Windows and Microsoft Smegma, the number of temporary storage locations that require manual cleaning multiply like oversexed rabbits in a dark room. Even just *finding* all the temp directories (and keeping track of them) is a full time maintenance job. It is far easier and less time consuming to just throw the computer away every year or so and buy a new one -- which is the design goal and what most consumers do. Others just re-format and re-install every six months to a year. A very few hunt down the temp directories (Windows 10 with one user account has 43 of them on the boot (C:) drive alone at current count and I do not know if I have found all of them yet -- it has only been RTM a couple of weeks) and wipe up the slobbering drool on a regular basis.
[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?
> Some temporary files have fixed names so they will be replaced the next > time SQLite tries to perform the same operation, and deleted when that one > finishes. Others will just hang about until the computer is rebooted and > will be deleted with other temporary files either on shutdown or on > restart. This is the same thing that happens to other files in the > temporary file folder. Meaning that on a persistent temp storage the files will stay forever (or until a manually deleted). Then again, on systems such as windows where temp files are never deleted this is to be expected.
[sqlite] System.Data.SQLite 1.0.98.0 release
If everything goes as planned, it should be out later today (US Pacific Time). Sent from my iPhone > On Aug 19, 2015, at 4:37 AM, Steffen Mangold powerdoo.com> wrote: > > Hi, > > is there any target date when the preRelease branch gets over to a actual > release? > > Is a really hard show stopper for our development at the moment. We checked > everything for compatibility before merge your current trunk to Visual Studio > 2015 and we forget about the SQLite design tool. :( > > Regards > Steffen Mangold > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] ATTACH DATABASE statement speed
On 19 Aug 2015, at 3:27am, Paolo Bolzoni wrote: > coping the sqlite db on an external disk connected via usb3 > and formatted with Ntfs actually does the pragma quick_check in little > more than 20 seconds and pragma integrity_check in 5 minutes. Those times are completely typical for SQLite. Those are the sort of times I'd expect to see. > So I think it is not an hardware problem, but Zfs messes up somewhat. > Is there any known disagreement between Zfs and sqlite? Over the years SQLite has revealed bugs in various versions of ZFS. However, I'm not aware of any problems with up-to-date versions of ZFS. > In fact I have > this feeling that my system is working fine in everything a part of > sqlite. > I read in the man that recordsize could be relevant. You might be able to improve your times by 50% with judicious choices of tweaks. But your original times strongly suggest disk problems of some kind. The current theory seems to be a ZFS formatting fault, but I know nothing about ZFS in real use so I don't know what to do about checking that. Hmm. Would it be possible to format an external drive in ZFS and try the operations on files stored on that ? Simon.
[sqlite] pragma temp_store_directory is deprecated, what is the alternative?
On 19 Aug 2015, at 1:28am, Paolo Bolzoni wrote: > Wouldn't be easier to simply add a parameter to sqlite3_initialize()? > E.g., a char const pointer to the tmp directory? That, if null, > defaults to something reasonable as James mentioned? The correct place for temp files varies from computer to computer, depending on whether the admin wants them on the boot drive or not, on whether the admin feels that every user should have their own temp directory, and other stuff. The programmer isn't meant to know this stuff. So it's best to let the admin set an environment variable and get the information from there rather than have the programmer try to pick a directory on a computer they've never used. Simon.
