Re: [sqlite] autoincrement and primary key

I would like to use INTEGER PRIMARY KEY, but I would like to disable its implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the column, I would like uniqueness to be enforced, but if NULL is supplied, I would like the operation to fail instead of advancing key to a new

Re: [sqlite] Create Table oddity

For me this inserts the text 'AppID' ... Sorry my bad, I tried a dummy table but made the type integer not char. So it was displaying 0 but hold text indeed. It's too late here for me to post anything, must be age! ___ sqlite-users mailing list

Re: [sqlite] Create Table oddity

While Keith is closer to the right explanation than I was, there are a couple of points: default does not need a constant: a function call is valid as well, e.g. date() The syntax: CREATE TABLE tApplicationPaths ( AppID INTEGER PRIMARY KEY, ApplicationName CHAR, ApplicationMonitorPath

Re: [sqlite] Create Table oddity

I've been talking with Bogdan about a change to SQLite Expert and ran across something I did NOT expect. I have this schema: CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1,

Re: [sqlite] How people test db failures

At 07:31 03/05/2013, you wrote: Problem is, how do I prove to the customer/user that it is working as expected no matter what? There are two important points in your question. The second (!) is "prove". The first is "as expected". Let me expand on why. For a system (irrespective of its

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

Hi Max, The link is dead. I'd like to know more about this so can you provide us with a live link? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: user-defined functions

... so I think you must be using a really old copy. That's correct: I downloaded this source years ago. And yes, argc for SQLite functions has a different semantics from main(). Thanks for the heads up. -- j...@antichoc.net

Re: [sqlite] ANN: user-defined functions

At 15:46 25/03/2013, you wrote: ´¯¯¯ The sqrt() function takes only one argument, at least. It checks assert( argc==2 ); at line 503 AFAIK. This library also contains several string functions, but it is meant as a general extension library, not a math specific library. I have my

Re: [sqlite] ANN: user-defined functions

At some point in time I needed a few math functions and tried to use the contributed maths extension. I found a couple of mistakes, mostly sqrt() requiring two arguments, and something else I can't remember right now. That by itself may indicate that the math functions never went into full

Re: [sqlite] Sqlite crashes on FTS query

At 23:12 23/03/2013, you wrote: ´¯¯¯ Here's how to reproduce it: - Get the latest sqlite shell for windows: http://www.sqlite.org/2013/sqlite-shell-win32-x86-3071600.zip - Download and extract this database: https://dl.dropbox.com/u/242199/db.zip - Run this query: SELECT rowid FROM ad_text

Re: [sqlite] Using SQLite on a networkshare

Raymond, So I checked some server/client solutions from http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork. But as there wasn't a solution that supported my script language (Autoit) I ended up writing my own server/client solution. This worked for me, but I still have some questions regarding

Re: [sqlite] How to verify referential integrity of SQLite database

Jay A. Kreibich wrote: > I can also see situations when someone might want to run one > set or the other set of checks. Breaking it out, so that these > checks are done by a different PRAGMA (integrity_check_v2 ?) seems > like a wise idea. Indeed; with a separate PRAGMA

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

Let's see. 100.000 licenses @ $20 = $2.000.000 Cayman S = $65.000 www.gizmag.com/porsche-cayman/25260/ => 30 cars. Kim Dotcom! :-) What are those $20 suppose to pay for? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

Ok, but where are the large number of tools that would do that? Features: - very fast, very basic spreadsheet (not based on Excel or Libre/OpenOffice) - saves data in SQLite - very easy to create new tables + columns - lets the user edit rows/columns as easily as in Excel - data can be sorted

Re: [sqlite] sqllite db - remote access on a shared host

Hi, I there any application/program paid or free that will allow me to connect to a remote shared host sqlite DB. A program which will allow me to make changes to the db design and do backups. I have very little control on the shared host. I am hoping for something that will let me connect

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

Also it suggests that transaction was began as read-only (with a select statement) and then there was attempt to transform it to a writing transaction (with insert, update or delete statement) when there was another writing transaction in progress waiting for this transaction to finish. and

Re: [sqlite] ICU extension not working as expected

I've downloaded and compiled icu.c according to the instructions in the included README (though I had to add -fPIC to the compiler options). Now, when searching a table, I'm not getting the kind of diacritic-insensitive behavior I was expecting: sqlite> .load lib/libSQLiteICU.so sqlite>

Re: [sqlite] Escape table and column names

At 13:55 16/05/2012, you wrote: ´¯¯¯ Those all work. You are getting an error because you didn't supply any column in the table `--- Oops, that's what happens when you try doing too many things at the same time. Nonetheless, SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for

Re: [sqlite] Escape table and column names

> This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. create table "foo""bar" ( baz ); select * from "one""two"; select * from sqlite_master; table|foo"bar|foo"bar|9|CREATE TABLE "foo""bar" ( baz ) I knew this

Re: [sqlite] Escape table and column names

> What about if table name or column name contains that escape character? > Do I need to escape it in another way? http://sqlite.org/faq.html#q14 This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. Given that

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Hi Simon, My test cases weren't intended to supply data, they're crash tests. I have faith that Mike's software correctly interprets syntactically correct cases. I was interested in making sure it didn't crash, hang or spew on syntactic errors. You're right. JcD

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Not sure I buy your test cases. I don't either, but for diverging reasons. Fields containg double quotes must be in double quotes. So only one of your examples is valid first,""second"",third I'll have to fix that one. No, it's invalid. Apply to " the same rules that apply to ' for

Re: [sqlite] Selecting every nth row efficiently

This is a good suggestion. A drawback is that the interval can't be too small otherwise there is a risk that a sample would be missed. So I will get more samples than I need. In you example, if there is a sample every second more or less, I would usually get 3-4 samples every hour instead of

Re: [sqlite] Selecting every nth row efficiently

Hit Send inadvertandly, sorry. My rowid isn't increasing with something predictable, so I can't do something like WHERE rowid % n = 0. I can use WHERE random() % n = 0 giving me sort of what I want (better than row % n, but I still need something better). If your sampling is essentially

Re: [sqlite] Selecting every nth row efficiently

I have a big database with timestamps and sensor readings, which I access with SELECT to have gnuplot draw graphs. However, sometimes I have readings every minute and want to plot several years of data, and feeding everything to gnuplot is overkill. In these cases it would be sufficient to

Re: [sqlite] Hashing 2 SQLite db files with the same data

But if data was added exactly in the same way/order shouldn't the counters all count to the same end result if the process was repeated at a later time on a another machine? Maybe, maybe not. Since the file format specifies meaningful fields only (my guess) it's quite possible that the

Re: [sqlite] Hashing 2 SQLite db files with the same data

Do you feel that the platform - Hardware / OS / some other factor could influence the way SQLite performed its sequence? Instead of trying to compare the hashes of DB files themselves, you appear to want a strict comparison of sets in the contents of the DBs. For instance, changing the

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

At 23:44 02/04/2012, you wrote: I wonder whether it respects languages. These don't, but u8_strcoll et al supposedly do, based on LC_COLLATE locale category. Herein lies the problem: if you build an index using these functions while running under locale A, then try to run queries against

Re: [sqlite] NaN in, 0.0 out?

Stephan, > Occasionally the sensor has a fault or the values are invalid (like > negative temperatures). To mark them once and for all as invalid, the > faulty values are replaced (in a C environment) by NaNs. Then the data are > inserted into the database with a C porgram, in a loop with the

Re: [sqlite] The number of parameters passed to user functions

Hi Max, Is there a way to know the number of parameters passed for user function with undefined argument count (nArg = -1 in the Sqlite3_create_function call). I could not find a function specific to this task and thought that I at least can check sqlite3_values array members for zero for my

Re: [sqlite] Unique indexes apparently not working

CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON 'user_identity' (shortName ASC, ucgID ASC); As a general rule you should reserve single quotes to string litterals. Either leave schema names alone (no whitespace, not keyword) or use [my pretty table], "my favorite table", `my

Re: [sqlite] Cyrillic support

You just need to convert it to UTF-8 before insert, then convert it back when you pull the data. UTF-16->UTF8 - insert Select - UTF8->UTF16 There are 16-bit SQLite APIs for that, no conversion needed actually. But of course it all depends of the wrapper you use, if any.

Re: [sqlite] Cyrillic support

I am using the newest version of SQLite. How do I enable support for Cyrillic letters? Whenever I enter something in the DB with Cyrillic letters it gets saved like this ?? This would happen if your Unicode Cyrillic text gets "converted" (read "emasculated") to ANSI using a non-cyrillic

Re: [sqlite] Constraint error messages

Me too. Either as a new standard way of working, or as something which can be turned on and off with a PRAGMA. I accept that SQLite is meant to be fast, but having SQLite spit out which check was violated will result in my app running faster and more dependably than when I build the same

Re: [sqlite] Incompatible versions of SQLite on same system

At 01:08 17/01/2012, you wrote: ´¯¯¯ Richard, Simon, and you are seeing eye-to-eye on this. Earlier in the thread (since trimmed, of course), it was stated that SQLite was designed to be tiny so that each app could include the entire system without draining resources significantly (this would

Re: [sqlite] finding pairwise similarity

where `similarity` is a measure of overlap of `t` between each pair of `c` and is calculated as the number of `t` common between a given pair divided by the total number of unique `t` in the pair. Just like Igor, I take "unique `t` in pair" to mean "distinct `t` in pair". I want the

Re: [sqlite] SQL help

At 18:09 22/12/2011, you wrote: DELETE FROM t WHERE rowid != (SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date LIMIT 1); Are you sure that date will collate correctly, as entries appear to be in dd/mm/ format? Dates in non_ISO format are always a pain. IMHO if dates are

Re: [sqlite] SQLITE_BUSY

So I make up for it by using an 'exec' which executes only the first command, and by hashing the command so that tampering with it would make it fail the hash. AFAIK sqlite3_exec will hapilly prepare and run more than one statements in a row. I use it all the time. But there's no reason

Re: [sqlite] SQLITE_BUSY

And it's convenient to be able to do the prepare during startup. I've just never had to worry about this situation. It means you have to do special handling for the first _step, but it's a reasonably compact way of handling the potential problem. I can't use prepared statements in

Re: [sqlite] SQLITE_BUSY

Hi Simon, Ah, so it's the first _step which does the locking, rather than the _prepare. That makes sense to me. If I had a serious application using, say, a hundred of different prepared queries, I'd rather batch-prepare them all up front then use them when needed without asking question.

Re: [sqlite] SQLITE_BUSY

If your statement is a SELECT returning 50 rows, and you've already read 20 of them by the time you get the _BUSY, do you get the first 20 again after doing the _reset ? I don't believe we can get _BUSY in the middle of a SELECT. Am I wrong?

Re: [sqlite] can you select series with SQL?

> so the consecutive numbers, going down from 14? Sorry I interpreted the question in a dumb way. Igor is certainly right (as usual). ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] can you select series with SQL?

ID 1 2 3 4 11 12 13 14 If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? Sure: select id from yourtable where id <= 14 limit 4 order by id desc;

Re: [sqlite] SQLITE3 Runs out of memory.

Loop for 61153 records start rowIndex at 0 sqlite3_prepare_v2 ("SELECT id, blobdata FROM Table30KB Where id = rowIndex") sqlite3_step() sqlite3_finialize() End Make that sqlite3_prepare_v2 ("SELECT id, blobdata FROM Table30KB") Do sqlite3_step() // handle errors somehow (busy, ...)

Re: [sqlite] Export Blob data from sqlite database to html

At 10:02 16/11/2011, you wrote: Basically my problem is exporting the database so the time stamps appear in hex the data blobs appear in either text (ascii) or as a jpeg image depending on the data in type field. I have tried numerous sqlite browsers without success. Looks like a job for

Re: [sqlite] GROUP BY With ASC/DESC

SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price > '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? Looks like a documentation bug. Indeed the

Re: [sqlite] WAL and local UNC filenames

Hi Pavel, I can't answer this question. Maybe it's something like all operations are successful but memory mapping is not actually shared between processes. So every process sees its own copy and assumes that no other process works with database, thus database can be corrupted in many possible

Re: [sqlite] WAL and local UNC filenames

Pavel, This is not a local file. Even if you use your hostname as netname file is still retrieved through network stack. And I guess SAMBA doesn't work well with memory mapped files (in addition to all problems with locking). True but why did I get no error? Since MMF don't cope with network

Re: [sqlite] WAL and local UNC filenames

Does your path begin with 'file:' ? No, simply \\netname\share\path\test.db ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL and local UNC filenames

Dear List, If a DB in WAL mode is opened using a UNC path refering to a _local_ drive, weird things happen until the DB gets corrupted but AFAIK no error code is thrown. Windows doesn't seem to open the memory mapped file if it has a UNC path. Can someone confirm? I don't know if this

Re: [sqlite] sql server management studio like tool for SQLite

At 21:46 05/11/2011, you wrote: In my opinion spending $59 for the Pro version is a **no-brainer** for many reasons. Seconded! Time saved in few days pays back the ticket. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] concurrent writes and reads to /from DB

Is there a good practice /setting for such high rate of concurrent writes with reads ? I understand that multiple readers with single writer at any time t is the model which sqlite has with various locks/states (unlocked, pending , shared , reserved , exclusive). "with" is a bit unclear.

Re: [sqlite] SQLite Expert

On 30 Oct 2011, at 7:50pm, Abair Heart wrote: > Searching the archives implies, that "sqlite expert" hasn't been asked > about. > > Has anyone tried it yet? Is the program safe? time-saving? Give me the $59 and a Windows computer and I'll check it out. Simon. As a long-term fan user of

Re: [sqlite] Error messages from user-defined functions calling sqlite3_result_error() requires sqlite3_finalize?

Fixed here: http://www.sqlite.org/src/info/8f88cc4e61 If you use sqlite3_prepare_v2() to prepare the statement, the custom error message will now be available after sqlite3_step() returns. Many thanks Dan. ___ sqlite-users mailing list

Re: [sqlite] Error messages from user-defined functions calling sqlite3_result_error() requires sqlite3_finalize?

Funny coincidence in time, I just posted on the same subject while your post was being received! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errcode() and sqlite3_errmsg()

Dear list, It seems that after sqlite3_prepare_v2() and sqlite3_step() we have that sqlite3_errcode() returns meaningful result right away when some function invokes sqlite3_result_error() during sqlite3_step(). OK so far. The issue is with sqlite3_errmsg(). If a function post an ad-hoc

Re: [sqlite] Trouble about ordering records by name (or any varchar column)

In my SQLite 3 Database, I have some records with Turkish characters such as "í", "Ü", "İ" etc. When I select my values with SELECT * FROM TABLE ORDER BY COLUMN_NAME query, the records that begin with these characters are coming at the end. Bare bone SQLite only collates correctly on

Re: [sqlite] "is not null" and index

Yoav Apter wrote: > I have the following table: > > CREATE TABLE x ('col1', 'col2', 'col3') > Create col1index on x ('col1') > > When I run this query: "select * from x where col1 is null" I see the index on x is used. > When I run this query: "select * from x where col1

Re: [sqlite] DOMAIN new error code

That is the default text for the error code, but your extension should provide different text. That's an half-backed solution. I know of no application which test error codes AND THEN test error message _content_ before selecting a way to deal with the error. Most of the times, the

[sqlite] DOMAIN new error code

I have the need to use some math extension functions. While testing them I found it very difficult to select an informative error code in case of a domain error, like sqrt(-1). Would it be sensible to create a new generic error code in some next release for extension functions to report such

Re: [sqlite] Time and date functions

select * from TABLE where DATE between date('now','-7 days','Weekday 0') and date('now','-7 days','Weekday 6'); Let me try this: select * from TABLE where DATE between date('now','-6 days','Weekday 0') and date('now','Weekday 6'); or compare week numbers (%W specification) [slower]

Re: [sqlite] Please provide a precompiled Win64 binary

However, the precompiled Windows binary is currently only available as a 32-bit library. I'd like to request that a precompiled Win64 Windows binary be added to the list of standard build outputs and downloads available from the sqlite.org site. This will be consistent with the basic Win32

Re: [sqlite] UPDATE of field deletes record

> the test code can be seen at > http://www.autoitscript.com/trac/autoit/ticket/2012 Are you sure it is gone? I'm not familiar with this API, but you appear to be finalizing the query BEFORE you extract rows from it. In the native API, that's not valid, making the code report no

Re: [sqlite] UPDATE of field deletes record

with an existing record, and a fieldname that is in a column that has a UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET fieldname="_escape("get calculator batteries")&" WHERE fieldname=sqlite_escape("get hp50g calc cells")&";" this code actually deletes the record. it should not. I

Re: [sqlite] Encoding and Collation

> It's very easy to replace the SQLite functions with user-defined ones, > so if someone wants to go the easy way (partial support for just the > common western scripts) it's easy. And already done by many, if you > search the mailing list. It's exactly what I'm looking for. It could be my

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

That is exactly the problem: such limits are app-specific logic, and that 3rd-party db manager cannot know about app-specific logic. No, that is a metadata which belongs to the DB. Forcing this pragma to always apply would in fact hinder some cases, e.g. creating a backup copy of a table

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

BTW this pragma should be applicable to a list of tables or * for all tables. I realize I didn't state I was talking about recursive_triggers pragma. Sorry for not having made this clear in the previous post. -- j...@antichoc.net

Re: [sqlite] Max page count not being remembered across close/open in SQLite 3.7.6.3

> Most PRAGMAs, including others which modify engine behaviour like recursive_triggers, are not stored in the database but have to be restated every time you open the database file. > Ok, thanks, If that is the case for this PRAGMA I did not realise. Similar to the page_count PRAGMA I would

Re: [sqlite] Custom Collation comparing only firt character?

>Cool - can you please post a link to the sources? Sure, here you are: http://dl.dropbox.com/u/26433628/unifuzz.zip The .c source contains all details (and all the bugs). -- j...@antichoc.net ___ sqlite-users mailing list

Re: [sqlite] Custom Collation comparing only firt character?

Roberto, >Though I cannot use DLLs since it is an iPhone iOS (MacOSX) >operational system. I made it DLL by default at build time since it fits my needs. You can still compile the extension (or part of it) as a standard .o obj and statically link it into your application. >I was hoping for

Re: [sqlite] Add/modify a table in the existing Sqlite database in client side.

>I am using C# windows application(.Net2010) with sqlite, and i need >to change the database table. >Can you pls let me know, how to add or modify a new column/ > table in >the existing Sqlite database that is in my client desktop machine. >I want the existing database to have its data,

Re: [sqlite] SQLite + unicode

>I try this function. Do you have Visual Studio. i show you my example. > >NOCaut wrote: > > > > > > char * unicode_to_1251(wchar_t *unicode_string) Why are you converting Unicode to 1251? This is a lossy conversion in the general case. Work with Unicode strings end-to-end, using the UTF

Re: [sqlite] null handling import

Mattew, > Obviously I am not as well versed in sqlite as you are. I hate to > hate myself, but am still continuing with reading somewhat ancient > files into a somewhat cohorrent database. > >Thanking you for your time and trouble, > > Matthew > >p.s. shame about the parsing of comma

Re: [sqlite] Strange result using JOIN

>I understand that but why the same query, with the same data, return two >different result using different SQLite-based Win32 applications ? SQL is essentially based on set theory. Sets don't have intrinsic ordering and may be enumerated in whatever order. What you think are distinct

Re: [sqlite] Hidding records from the application

>I want to make things clear. there are some rules in my system, such >as : process whose name is proc_host can see all the records, and >process whose name is proc_client1 can see all the records except >rowid 1. First, keep away of relying on rowid values since they may change if you don't

Re: [sqlite] How to search for multiple values in a column including null without repeating column name ?

Michael, >Are you saying that "1" below doesn't show up in the first query result? > >sqlite> create table t(i int,name string); >sqlite> insert into t values(1,NULL); >sqlite> insert into t values(2,'two'); >sqlite> select * from t where i in (1,2,3,null); >i|name >1| >2|two >sqlite> select *

Re: [sqlite] Continuous exclusive lock

> > If you _need_ exclusiveaccess all along, then start app, "begin > > exclusive", do your stuf, "commit" and exit. > > > >The 'problem' is that the application can run for the whole day. Granted. And the 'problem' is ??? > > What I don't get is you later say it's a single-user, single-app > >

Re: [sqlite] Continuous exclusive lock

Cecil, >Do I understand it correctly that after a commit the database is writeable >again for others? Yes. > In that case it is maybe better to do a: > *PRAGMA locking_mode = EXCLUSIVE; >*followed by an update of the database. As long as the application is >running, I want to be sure that

Re: [sqlite] Continuous exclusive lock

Cecil, >Is good enough for me. My only problem is that between reading the >data and >writing the changes, I want to be sure that no one has changed the data. >For me that is enough. For this, a simple "Begin immediate;" ... "Commit;" embrassing you read-modify-write block will do. Doesn't

Re: [sqlite] Query with UNION on large table

Hi Rense, >Thanks for this idea. In fact, the purpose of my original query is >exactly to reduce the database. The 800 mln rows were exported from >another source, and I was hoping to be able to use sqlite to manage >this massive amount of data (e.g., removing redundant information) >before I

Re: [sqlite] Query with UNION on large table

Rense, >As for the ranges of n1 and n1: they are both roughly between 6 >and 1200 . > >Here are the results of EXPLAIN QUERY PLAN SELECT n1, n2 FROM table1 >Where n1 < n2 INTERSECT SELECT n2, n1 FROM table1 Where n2 < n1; > >1|0|0|SCAN TABLE table1 (~437976176 rows) >2|0|0|SCAN TABLE

Re: [sqlite] unexpected effect of view nesting on type conversion

>I've run into a phenomenon I don't understand where view nesting affects >types. Give me a try: Form what I understand, views don't have their own types, so default affinity applies. 12.0 gets converted to 12 as an integer in v2 when the value gets picked from v1. Please someone correct me

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

Kevin, >I have confirmed that the DELETE does indeed delete the record, but >only after Django's delete callback has completed. If what you say is >true, that DELETEs are proceed immediately, then the only other >explanation is that Django, upon sending of the post_delete signal, >has not

Re: [sqlite] Implementing busy-waiting on SQLite

Hi Jay, > > Invoke sqlite3_busy_timeout() with a safe timeout for every connection > > to the DB. > > Use IMMEDIATE transactions everywhere. > > > > That's all you have to do. > > Well, yes and no. You still have to deal with the case of > SQLITE_BUSY being returned due to deadlocks.

Re: [sqlite] Implementing busy-waiting on SQLite

>As was being discussed yesterday, I have four processes accessing the >same database file. When they perform an sqlite action, I wish them to >block if the DB is not available. SQLite does not block if it finds the >db busy or locked, it returns an error code. You can have SQLite do all this

Re: [sqlite] sqlitebrowser - anyone compiled a recent one?

>Thanks. I was prepared to receive alternate suggestions :) >I wanted to have hands on and control over a browser with which >can follow sqlite versions quicker. > >Sqlitebrowsers tend to lag behind the Sqlite development. Then give SQLite Expert a try! http://www.sqliteexpert.com/

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

>Sure, if you're just computing average() then you'll not get any NaNs. NaNs, NO (if we don't have NaNs in the set) but issues, YES. It all depends. No one knows. You don't even know what you're computing exactly. SQL interpret avg() as "take this data as a set, sum up these numerical

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

Nico, >There is one thing that makes numerical support in RDBMSes important: >aggregate functions. > >Aggregate functions are critical because they allow one to do much >analysis at the data source, instead of having to transport it >elsewhere for analysis. I agree with you, totally. Read me

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

Allow me to add a humble bit to what Jay just posted. SQLite, as well as most other RDBMS around, allow you to perform FP calculations in SQL statements. I assume no-one imagines an extended FP fine-grain support of hundreds of computation options and status reporting be part of SQL or

Re: [sqlite] Proper way to escape table name and column name

>What is the official way to escape table name that contains a space >and column name that contain a spaces? You can use square brakets or double-quotes: [This is a long name for a small table] "This is a long name for a small table as well" ___

Re: [sqlite] What is so slow on dropping tables?

>Quoth Roger Binns , on 2011-06-01 00:21:44 -0700: > > On 05/31/2011 12:18 PM, Jan Hudec wrote: > > > - Is there any way to speed it up? > > > > Another way (somewhat hinted at by Nico) is that you can create > these tables > > in separate databases and use attach to

Re: [sqlite] Unlocking the database

>I understand the need for integrity when locking a database, but in >this case I knew that the problem was caused by a (in all honesty, my) >bug. I tried rebooting the machine and it did not unlock the table. Yeah, sometimes Windows will keep a lock on a file beyond reboot, which is

Re: [sqlite] Is it possible to optimize the read performanceof a C++ app using sqlite pragma journal_mode = wal & pragmawal_checkpoint?

> Simon Slavin, Thank you for your suggestion. Our deduper prototoype > uses fuzzy matching methods such as the Levenshtein Distance to > detect duplicates. We have found that these fuzzy matching methods > are best implemented in C++ for processing time requirements. > We would

Re: [sqlite] [BUG] Silent change in intergeral overflow handling in 3.7.6

Richard, At 13:07 25/05/2011, you wrote: >It turns out that the "expected" behavior does not happen in modern C >compilers. Overflow of signed integers is undefined behavior in C. So if >you have a signed integer overflow, it might wrap the result (the >"expected" >result) or it might

Re: [sqlite] date field with default current date

> I resolved with: >CREATE TRIGGER insert_nameTable after insert on >name_tabl begin >update set ww = datetime('now','localtime') >where rowid = new.rowid; end" You don't have to fire a trigger for such default: CREATE TABLE "test" ( "mydate" CHAR DEFAULT

Re: [sqlite] Possibly Alias Bug

>I have problems with column aliasing using AS when I select >rows with the same string: > >.headers ON >CREATE TABLE TEST1 (name TEXT,value TEXT); >INSERT INTO TEST1 VALUES ("name1","value1"); >SELECT value AS name1 FROM TEST1 WHERE name="name1"; // gives no result >SELECT value AS name1_ FROM

Re: [sqlite] (no subject)

>Ah, there is a way to write it so that you can have at most one of >those constraints where only one makes sense, and not require a >specific order of constraints, but it'd require listing all the >possible orderings, which would be impractical. > >So if one wanted to enforce that there's at

[sqlite] (no subject)

Anoher (silly) question about what SQLite considers valid input, again out of mere curiosity. A statement like: CREATE TABLE a (a CHAR COLLATE NOCASE COLLATE BINARY, b INTEGER DEFAULT 1 DEFAULT 2); doesn't cause any error: SQLite applies only the last constraint of each type, namely COLLATE

[sqlite] Usefulness of FKs to multiple tables

Let me ask this by mere curiosity. SQLite will accept and process the following: CREATE TABLE x (a CHAR PRIMARY KEY); CREATE TABLE y (a CHAR PRIMARY KEY); CREATE TABLE z (a CHAR REFERENCES x(a) REFERENCES y(a)); I didn't check if the last FK is even valid normative SQL and that isn't the heart

Re: [sqlite] SQL Statement Help(selecting days).

> > Since we use recursive triggers, set recursive_triggers pragma > > beforehand if not yet done. > >Cunning. A bit of a Rube Goldberg apparatus though, no? Huh? Still way more flexible than having to modify C source of a vtable module, should you have to adapt anything. Yeah, it's kind of

<    1   2   3   4   5   >