[sqlite] database is locked

Dear SQLiters, There has been a lot of discussion, I remember, on this subject by others. Please forgive me for asking this for a millionth time. I somehow got my database in a locked state. I updated a table yesterday and I am rather sure that no one on our multi-user system is updating it

Re: [sqlite] database is locked

Subject: Re: [sqlite] database is locked On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > Dear SQLiters, > > There has been a lot of discussion, I remember, on this subject by others. > Please forgive me for asking this for a millionth time. > > I somehow got

Re: [sqlite] database is locked

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Wednesday, January 14, 2015 1:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] database is locked On 1/14/15, Roman Fleysher <roman.fl

Re: [sqlite] database is locked

is locked On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > Thank you, Richard. > > You are correct, I made a typo: we have NFS not NTFS and I know they are > buggy. I always use the same node on our compute cluster to minimize > buffering issue. So, are you s

Re: [sqlite] database is locked

[v_mode] ,v_file, (v_ret = v_shared .l_pid)); if (v_ret == -1) printf("%s File:%s, \n",g_mode[v_mode] ,v_file); -Ursprüngliche Nachricht- Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu] Gesendet: Mittwoch, 14. Jänner 2015 18:54 An: General Disc

[sqlite] PhD student

I like that!!! Roman From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Thursday, February 26, 2015 5:33 AM To: General Discussion of SQLite

[sqlite] how to cite SQLite

Dear SQLiters, I am writing a scientific paper to describe our research. To manage data, we use SQLite. I would like to acknowledge SQLite and cite it properly in the paper. Is there a suggested way of doing it? A conference presentation? A paper, a book? In the simplest form I will use URL.

[sqlite] how to cite SQLite

Discussion of SQLite Database Subject: Re: [sqlite] how to cite SQLite On 7/13/15, Roman Fleysher wrote: > Dear SQLiters, > > I am writing a scientific paper to describe our research. To manage data, we > use SQLite. I would like to acknowledge SQLite and cite it properly in

[sqlite] how to cite SQLite

rs-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Monday, July 13, 2015 2:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] how to cite SQLite On 7/13/15, Simon Slavin wrote: > > On 13 Jul 2015, at 4:51pm

[sqlite] Thanks SQLite

I dare to add my thanks here, with a much simpler example. Initially, for me, CTE was another thing to learn. Then I wanted SQLite to compute statistics on a simple two-column table. Not a big deal, I typed the equation and was done. Next day, I needed the same equation to be applied to

[sqlite] select * where is/like ?

Dear SQLiters, I have a table with examID column. When I try to select specific ID I have to use "like" instead of "=". Why is that? Why does not last SQL query produce nothing? SQLite version 3.8.8.3 2015-02-25 13:29:11 sqlite> select examID, typeof(examID) from mainDB.Exam where

[sqlite] select * where is/like ?

ct * where is/like ? On 19 May 2015, at 5:51am, Roman Fleysher wrote: > Why does not last SQL query produce nothing? What is the schema (the CREATE TABLE command) for your Exam table ? Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sq

[sqlite] select * where is/like ?

ral Discussion of SQLite Database Subject: Re: [sqlite] select * where is/like ? On 19 May 2015, at 4:07pm, Roman Fleysher wrote: > CREATE TABLE Exam( > examID TEXT PRIMARY KEY NOT NULL Works fine for me. I tested inserting as text, integer and real in case they did something weir

[sqlite] select * where is/like ?

RE solved the problem. However, manual says "=" and "IS" are identical except when treating "NULL". What is the difference? Roman From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailin

[sqlite] select * where is/like ?

On 19 May 2015, at 4:43pm, Roman Fleysher wrote: > Now I have two questions: > > 1. I created database from scratch using new version of SQLITE and PRAGMA > integrity_check; produces "missing index" as before. Are you telling us that you have a sequence of command

[sqlite] index broken by insert

Dear SQLiters, I do not really know what info to provide for sufficient information. I use SQLite shell only for all create/insert manipulations. This insert below causes PRAGMA integrity_check; to report missing index (what appears to be on every inserted row): SQLite version 3.8.8.3

[sqlite] index broken by insert

_ From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleys...@einstein.yu.edu] Sent: Tuesday, May 19, 2015 12:25 PM To: General Discussion of SQLite Database Subject: [sqlite] index broken by insert Dear SQLiters, I

[sqlite] index broken by insert

-bounces at mailinglists.sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com] Sent: Tuesday, May 19, 2015 1:35 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] index broken by insert Hi, Roman, On Tue, May 19, 2015 at 1:23 PM, Roman Fleysher wrote: > "Confirmation&q

[sqlite] index broken by insert

-- Dear SQLiters, -- Here is schema first, table is below, -- followed by offending statement. -- you can copy and paste the entire body -- my comments are SQL compatible - -- STEP 1 -- -- create gender and handedness tables to fix possible values -- then

[sqlite] index broken by insert

M (GMT-05:00) To: General Discussion of SQLite Database Subject: Re: [sqlite] index broken by insert On 5/19/15, Roman Fleysher wrote: > CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN > INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID, >

[sqlite] index broken by insert

From: Roman Fleysher Sent: Tuesday, May 19, 2015 3:48 PM To: General Discussion of SQLite Database Subject: RE: [sqlite] index broken by insert Dear Richard, Dear Simon, Dear SQLiters, It is such a pleasure to deal with smart people. Pure joy. How quickly Simon figured out the problem

Re: [sqlite] General R*Tree query

Since coordinate system is spherical, how do you tell that RA=23:59 and RA=00:01 are next to each other using usual comparisons? Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy

Re: [sqlite] General R*Tree query

] General R*Tree query On Tue, Dec 17, 2013 at 3:57 PM, Roman Fleysher < roman.fleys...@einstein.yu.edu> wrote: > > Since coordinate system is spherical, how do you tell that RA=23:59 and > RA=00:01 are next to each other using usual comparisons? I don't; usual comparisons won'

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

Others on the list can give you a better advise. Mine: If you do not need id column in OpenProjects, get rid of it and make ProjID the primary key. Otherwise, create index on ProjID. Either way create index on ProjID in OpenJobs. Better, to make ProjID a foreign key in OpenJobs to refer to

Re: [sqlite] INSERT several rows

, April 03, 2014 4:48 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT several rows On 4/3/2014 4:23 PM, Roman Fleysher wrote: > Is ability to insert multiple rows in one go a feature of a newer versions: > > INSERT INTO myTable (designation) VALUES ('LoResFA_only'), ('HiR

Re: [sqlite] INSERT several rows

of Richard Hipp [d...@sqlite.org] Sent: Thursday, April 03, 2014 5:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] INSERT several rows On Thu, Apr 3, 2014 at 4:52 PM, Roman Fleysher < roman.fleys...@einstein.yu.edu> wrote: > OK, thank you, Igor! I presume there is no w

[sqlite] isBad table bad design?

Dear SQLiters, Could some help if this is bad design or my lack of knowledge of SQL: I have a table that keeps track of bad metrics from exams: badMetric(examID, metric, reason) I want to filter-out bad ones and keep only good ones from the user request: request(examID, metric). I thought:

Re: [sqlite] isBad table bad design?

of SQLite Database Subject: Re: [sqlite] isBad table bad design? On Thu, Apr 3, 2014 at 6:32 PM, Roman Fleysher < roman.fleys...@einstein.yu.edu> wrote: > Dear SQLiters, > > Could some help if this is bad design or my lack of knowledge of SQL: > > I have a table that keeps

[sqlite] SQLite and GPFS

Dear SQLiters, I understand that SQLite relies on the underlying filesystem to perform flushes to disk. This is a problem in network file system (NFS) when a disk is mounted on several nodes of a compute cluster and SQLite is ran on them. Essentially, NFS disallows running SQLite concurrently

Re: [sqlite] Sequential numbers

> Example: > > > CREATE TABLE aaa (i, seqnr); > INSERT INTO "aaa" VALUES(10,NULL); > INSERT INTO "aaa" VALUES(20,NULL); > INSERT INTO "aaa" VALUES(50,NULL); > INSERT INTO "aaa" VALUES(30,NULL); > INSERT INTO "aaa" VALUES(20,NULL); > > UPDATE aaa SET seqnr=(SELECT count() FROM aaa smaller where >

[sqlite] PRAGMA integrity_check

Dear SQLiters, PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints. Does it check other CHECK constraints specified in the column definition? Thank you, Roman

[sqlite] PRAGMA integrity_check

Database Subject: Re: [sqlite] PRAGMA integrity_check On 10 Sep 2015, at 11:06pm, Roman Fleysher wrote: > PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints. > Does it check other CHECK constraints specified in the column definition? I'm 90% sure it does not

[sqlite] PRAGMA integrity_check

:04am, Roman Fleysher wrote: > I wanted to check the behavior and set up a test database. I use (for now) > SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes' > did not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that > expected? Yeah. Tha

[sqlite] PRAGMA integrity_check

of Simon Slavin [slav...@bigfraud.org] Sent: Thursday, September 10, 2015 7:35 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] PRAGMA integrity_check On 11 Sep 2015, at 12:32am, Roman Fleysher wrote: > Meanwhile, I tested if PRAGMA integrity_check checks column constrai

[sqlite] bug in PRAGMA ignore_check_constraints?

Dear SQLiters, I am trying to temporarily disable CHECK constraint given in columns of table definition. As far as I understand, PRAGMA ignore_check_constraints='yes'; should do it. However this example demonstrates that it is not: CREATE TABLE subject( subjectID INT, gender TEXT

[sqlite] PRAGMA integrity_check

Database Subject: Re: [sqlite] PRAGMA integrity_check On 9/10/15, Roman Fleysher wrote: > Dear SQLiters, > > PRAGMA integrity_check is described to check UNIQUE and NOT NULL > constraints. Does it check other CHECK constraints specified in the column > definition? > Apparently it do

[sqlite] bug in PRAGMA ignore_check_constraints?

...@sqlite.org] Sent: Thursday, September 10, 2015 7:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug in PRAGMA ignore_check_constraints? On 9/10/15, Richard Hipp wrote: > On 9/10/15, Roman Fleysher wrote: >> Dear SQLiters, >> >> I am trying to temporarily dis

[sqlite] datetime in CHECK

Dear SQLiters, I am trying to use CHECK constraint is column of a table to enforce datetime format and this works: AcquisitionDateTEXT CHECK (AcquisitionDate IS date(AcquisitionDate)) when I insert '2015-08-10'. But this AcquisitionDateTime TEXT CHECK (AcquisitionDateTime IS

[sqlite] datetime in CHECK

sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of Cecil Westerhof [cldwester...@gmail.com] Sent: Wednesday, May 04, 2016 5:37 PM To: SQLite mailing list Subject: Re: [sqlite] datetime in CHECK 2016-05-04 22:43 GMT+02:00 Roman Fleysher : &g

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

Dear Richard, Dear SQLiters, Thank you, Simon, for sending the link. I would like to offer several comments on the podcast. 1. Why SQLite is popular. Instead of describing how I selected SQLite to solve our DB needs, I will recount story of Sony, its introduction of transistor radio that I

[sqlite] Beginning database question

I am new to database and SQLite too. I found this in archive. What if I would like GUI, where I would go? Thank you, Roman I assume when you say "discrete" you actually mean "bracketed" as there are lots more than 10 heights and weights. I don't know what Excel has to do with this unless

[sqlite] sqlite shell: arrow keys

Dear SQLiters, I am new to SQLite and learning it (and SQL) using shell. It would make life easier if arrow keys on keyboard could be used to scroll through command history and along command for editing. Is there a way to enable this? Thank you, Roman

[sqlite] table with check

Dear SQLiters, I am using sqlite shell, I believe version 3.7.16.2. I created a table with CHECK condition as: CREATE TABLE subject( subjectID INTEGER PRIMARY KEY, handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) ); in hopes to be able to insert only "Right",

Re: [sqlite] table with check

peof(handedness)='null'". Peter - Original Message > From: Roman Fleysher <roman.fleys...@einstein.yu.edu> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Fri, May 17, 2013 12:19:21 PM > Subject: [sqlite] table with check >

[sqlite] autoincrement and primary key

Dear SQLiters, 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

Re: [sqlite] autoincrement and primary key

[i...@tandetnik.org] Sent: Monday, May 20, 2013 4:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] autoincrement and primary key On 5/20/2013 4:17 PM, Roman Fleysher wrote: > I would like to use INTEGER PRIMARY KEY, but I would like to disable its > implicit AUTOINCREMENT featur

Re: [sqlite] Serialize an in-memory database

Read section on URI Filemanes, particularly mode for memory databases: http://www.sqlite.org/c3ref/open.html DB Connection in backup API does not have to point to a file, it can point to in-memory database if URIs are enabled. (I learned it from someone else on the list, i use SQLite for

Re: [sqlite] Feature request: add support for COMMENT statement

Dear SQLiters, I can not add solutions, since I am a physicist designing database for the first time, but I would like to add questions... Object-relational mapping (ORM) is a new and interesting concept for me that I learned. I will read about it more. However, I do not understand why new

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

Bravo Alex !! From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Alex Bowden [a...@designlifecycle.com] Sent: Monday, July 01, 2013 12:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is there a way

[sqlite] can column name come from a table?

Dear SQLiters, I think the answer to my question is "NO", but may be I missed something... Can column name come from a table, i.e. from another select? Example: SELECT (SELECT columnName FROM columnNameTable WHERE condition how to select limit 1) FROM table which has that columnName; Or this

Re: [sqlite] can column name come from a table?

name come from a table? On 7/29/2013 8:32 PM, Roman Fleysher wrote: > I think the answer to my question is "NO", but may be I missed something... > > Can column name come from a table, i.e. from another select? Example: > > SELECT (SELECT columnName FROM columnNameTable WHER

Re: [sqlite] BETWEEN and explicit collation assignment

Dear Igor, I was reading with great interest your debate with Simon. I have come to respect your opinion by reading your comments. I am not a moderator, but trying to imply that many SQLite users are on your side reduced your image in my head. I left USSR (where such arguments were plenty) and

Re: [sqlite] BETWEEN and explicit collation assignment

collation assignment On 8/22/2013 4:06 PM, Roman Fleysher wrote: > Logically, I agree with Simon, collate modifier in table definition describes > how indices should be built, nothing more. But for what purpose are indexes built? Isn't it for the purpose of being used to speed up queries? W

Re: [sqlite] BETWEEN and explicit collation assignment

rs@sqlite.org Subject: Re: [sqlite] BETWEEN and explicit collation assignment On 8/22/2013 6:59 PM, Roman Fleysher wrote: > First, I do not expect any changes to SQLite to be made as a result of this > discussion. You might not, but Simon seems to: "It's too late to contrafit this int

Re: [sqlite] BETWEEN and explicit collation assignment

22, 2013 9:37 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] BETWEEN and explicit collation assignment On 8/22/2013 9:01 PM, Roman Fleysher wrote: >> create table t(x text collate nocase); >> insert into t values ('A'); >> select count(*) from t where x = 'a'; > >>

Re: [sqlite] BETWEEN and explicit collation assignment

You are correct, James, comparison depends on types. However, lets say we have suitcase and we want to test if it is bigger than the allowed limit, i.e. we have a biggest allowed suitcase to compare against. How do we answer: is this one bigger than the standard? Well, if this is a check-in

Re: [sqlite] BETWEEN and explicit collation assignment

> What is relevant is not determined by the type of the object, but by > the task at hand, in other words by the comparator. No. There is only one meaning of "equal". Two things are either equal or not. How to compare them is determined by only their type. You are correct, James, types are

Re: [sqlite] Hints for the query planner

In Bayesian statistics there is a term "prior", prior probability. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Tuesday, September 10, 2013 3:26 PM To: General Discussion of

Re: [sqlite] Using an sqlite db as a mutex?

What about doing this via NFS? I presume no guarantee? From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Thursday, September 26, 2013 11:31 AM To: sqlite-users@sqlite.org

Re: [sqlite] replace "\n" with nothing

: [sqlite] replace "\n" with nothing replace(columname, char(10), ''); Sometimes, depending on your OS's interpretation of '\n', it might actually be char(13)+char(10) or such (that's hex 0x0D and 0x0A). Get the HEX() from such a line to be sure. On 2016/07/05 9:00 PM, Roman Fley

[sqlite] replace "\n" with nothing

Dear SQLiters, I made a mistake and inserted a new line char, "\n" in the middle of a text. I now would like to replace it with nothing. Something like: replace(columnName, '\n','') But this will interpret "\n" literally, as two symbols. How do I do it? Thank you, Roman

Re: [sqlite] 3.17.0 does not read updated DB

My mistake: I do not update DB. I rename (unix mv) the DB. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleys...@einstein.yu.edu] Sent: Thursday, February 16, 2017 12:33 AM To: General

[sqlite] 3.17.0 does not read updated DB

Dear SQLiters, Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up updated content of a DB file? I updated DB while it was attached in sqlite3 and SELECT did not produce new results. Instead it printed the same output as before DB file was updated. Update was performed

Re: [sqlite] SQLite binary with Math Functions for OS-X?

Can't you count how many rows there are and then sort by the variable of interest, limiting output to half the count, all within SQL? Roman Sent from my T-Mobile 4G LTE Device Original message From: Ronald Gombach Date: 11/21/16 7:12 AM (GMT-05:00) To:

Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > Yes, Richard, this is exactly what I mean. > Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c extension as a prototype from which to develop yours. -- D. Richard Hipp d..

[sqlite] extension to run bash

Dear SQLites, I am using exclusively sqlite3 shell for all the processing and may need ability to run bash commands and assign result to a column. For example: UPDATE result SET nRows =` wc -l fileNames` ; Here I used `` as would be in bash for command substitution. This would run wc command

Re: [sqlite] extension to run bash

] extension to run bash On 1/11/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > Dear SQLites, > > I am using exclusively sqlite3 shell for all the processing and may need > ability to run bash commands and assign result to a column. For example: > > UPDATE result SET

Re: [sqlite] extension to run bash

I am not even sure myself this is the right path. I have table with file names and need operations to be performed on columns (i.e. on files). Results, numeric or new file names, are to be recorded in a column. I see two ways: From bash script, make list of rows, run commands, load results

Re: [sqlite] Developing a SQLite3 DB remotely

I do not have big experience in the area, but have some. I think that light weight use is not the right thing to ask. I have seen NFS delays of 20 seconds: file was created on one machine and showed up on another after 20 seconds. This depends on how heavy OTHER things are, not how heavy

Re: [sqlite] Developing a SQLite3 DB remotely

No. I was not aware of these tools. Are any of them good? Maintained? I am mostly using sqlite3 shell from bash scripts. Do you know if some of them are suitable replacements? Is this off the topic of the original question? Thank you, Roman From:

[sqlite] command shell .timeout

Dear SQLiters, I am using sqlit3 command shell. It has ".timeout" command. What is the difference between: .timeout MS PRAGMA busy_timeout = milliseconds; I am getting "database is locked" when accessing the same file from multiple concurrent shells and trying to set timeouts to avoid this.

Re: [sqlite] command shell .timeout

/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > Dear SQLiters, > > I am using sqlit3 command shell. It has ".timeout" command. What is the > difference between: > > .timeout MS > PRAGMA busy_timeout = milliseconds; They accomplish the same thing.

[sqlite] transfer records with foreign key

Dear SQLiters, I have two tables linked by a foreign key, linkID. I need to transfer content of these two tables into two corresponding tables in another database preserving the link. However, the second database already has records and numeric value of linkID can not be preserved. Nor its

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

Dear Richard, Dear SQLiters, This is not clear to me as well. If I have two databases, db1 and db2. Both have table t. db1.t and bd2.t. I want to create a TEMPORARY trigger that upon insert in db1.t does something with db2.t. Because: TEMP triggers are not subject to the same-database rule.

Re: [sqlite] rowid as foreign key

lite-users-boun...@mailinglists.sqlite.org] on behalf of Gwendal Roué [gwendal.r...@gmail.com] Sent: Monday, July 24, 2017 3:17 PM To: SQLite mailing list Subject: Re: [sqlite] rowid as foreign key > Le 24 juil. 2017 à 20:58, Roman Fleysher <roman.fleys...@einstein.yu.edu> a > écr

[sqlite] rowid as foreign key

Dear SQLiters, Is it possible to link two tables using rowid, the implicit column? I tried and it did not work, so I presume the answer to my question is "no". Thank you, Roman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] rowid as foreign key

dump/load operations. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher >Sent: Monday, 24 July, 20

[sqlite] WITH inside trigger in 3.16.2

Dear SQLiters, I am trying to create a trigger with body: WITH ... DELETE FROM ... and it does not seem to work (Error: near "DELETE": syntax error). But I can execute the body itself without errors. Does it mean that WITH clause is not supported within trigger? I use SQLite version 3.16.22.

Re: [sqlite] WITH inside trigger in 3.16.2

My apology, I can not read. http://sqlite.org/lang_createtrigger.html clearly states that CTE is not supported in triggers. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleys

Re: [sqlite] SAVEPOINT with multiple databases

commit/rollback, or savepoint release/rollback will never leave you with a different set of attached databases than before that statement. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher Sent: Wednesday, May 17

Re: [sqlite] SAVEPOINT with multiple databases

On 16 May 2017, at 10:09pm, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > I think I came to a point where I need to learn SAVEPOINTs. > > I am trying to understand documentation if creation and release of save > points covers all presently attached databases, that

[sqlite] SAVEPOINT with multiple databases

Dear SQLiters, I think I came to a point where I need to learn SAVEPOINTs. I am trying to understand documentation if creation and release of save points covers all presently attached databases, that is those before save point is created? Is attaching a database just a command that will sit on

Re: [sqlite] PRAGMA optimize; == no busy handler?

Dear SQLiters, Vacuuming seems to belong to a different thread, but let me say that it is not always warranted. Vacuuming may change/reassign ROWIDs. If you have two databases (backup and production?) that used to be linked via such a key, it will break. Roman

Re: [sqlite] XOR operator

The point is that terminology is chosen for a reason and can not be dismissed. "Flexibly typed" means it is typed. It means SQLite knows how many bytes: without knowing it would not be able to establish equality "IS". Flexibly means columns can contain values of mixed types, but each value

Re: [sqlite] Energy consumption of SQLite queries

Dear Ali, A couple of comments. Indeed lots of energy is transferred into heat, but not all. Therefore, using temperature (after calibrating specific heat coefficient of the device ) is not a good method. Some energy is radiated as visible and invisible light and hard to catch it all. Some as

Re: [sqlite] probably recursive?

Thank you Cezary and others who commented. For some reason, I did not receive email from Cezary, only comments on it. I was under impression that RECURSIVE can not be used in sub-query. I see that it can. But, most importantly, could you elaborate more on how it works. I agree it is n-to-n

Re: [sqlite] 3.24 draft - upsert

Why does SQLite have to follow what PostgreSQL does? I thought SQLite is the leader. Roman Sent from my T-Mobile 4G LTE Device Original message From: Richard Hipp Date: 5/9/18 5:48 AM (GMT-05:00) To: SQLite mailing list

[sqlite] clocks in SQLite

Dear SQLIters, I use datetime('now') to record when a job gets started and stopped. As soon as a job stops, new is started. Thus stop of the previous should be within a second of the start of the next, as you see in the first few lines. But then the clock jumps. Job 1847 ends at 16:44:11 and

Re: [sqlite] clocks in SQLite

Thank you for pointing the 24 hours. I did not notice the day change. Now, I have no idea how this can happen. I will investigate more. Roman From: Graham Holden [sql...@aldurslair.com] Sent: Tuesday, May 15, 2018 3:39 PM To: Roman Fleysher Cc: General

Re: [sqlite] probably recursive?

to me, but it should be easier because you only need to find a single number (which you can then plug into a delete statement). If my statement about the square is not obvious to prove in your head I can try write a proof for that but I'm not much good at proofs. > On 2 May 2018, at 7:27 am, Roma

Re: [sqlite] probably recursive?

Pairs (x,y) do not repeat. Actual x and y are positive integers, but I do not see how being positive can be relevant. Integer is important for sorting/comparison. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of

Re: [sqlite] probably recursive?

-05:00) To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] probably recursive? Ah my bad, I misunderstood the initial condition. nX is a function of X. My statements were only true if nX=X. Well, sorry about the noise. > On 2 May 2018, at 8:20 am,

[sqlite] probably recursive?

Dear SQLiters, I have trouble solving this problem, maybe it is impossible? I have a table with two columns x and y, both integers. Imagine they are coordinates on X-Y plane, dots. I need to find all x's that have more than nX dots, and all y's that have more than nY dots. Both conditions must

Re: [sqlite] probably recursive?

ty sets - but this would be painfully slow next to a simple software algorithm that prunes/resolves a 2-dimensional array - exponentially worse so for larger grid sizes. On 2018/05/01 2:45 AM, Roman Fleysher wrote: > Dear SQLiters, > > I have trouble solving this problem, maybe it is impo

Re: [sqlite] probably recursive?

2018, at 5:34pm, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > With recursive route, I am thinking I need to build deleteList(x,y). Rather than actually delete rows, if you can, insert a new column in the table of all points. It starts with every row set to TRUE. When you dec

Re: [sqlite] primary key in another column

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 6:10 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 6:03 PM, Roman Fleysher wrote: >

Re: [sqlite] primary key in another column

it in the select query that is getting the data? On 1/26/18 6:03 PM, Roman Fleysher wrote: > My implementation of "for Each row" requires all columns to be populated. It > is a dumb thing: > > forEachRow commandToBeExecuted itsArgumentsWhichReferToColumns > > The files are ima

Re: [sqlite] primary key in another column

that don't need such a view, create a simple pass through view). On 1/26/18 6:30 PM, Roman Fleysher wrote: > No, I can not compute inside forEachRow. ForEachRow is now universal, can be > applied to any table. If I modify SELECT inside it to fit specific purpose, > forEachRow will use uni

Re: [sqlite] primary key in another column

); ... Is that a right solution? Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleys...@einstein.yu.edu] Sent: Friday, January 26, 2018 4:43 PM To: General Discussion of SQLite Database Subject: [sqlite

Re: [sqlite] primary key in another column

f Igor Tandetnik [i...@tandetnik.org] Sent: Friday, January 26, 2018 5:56 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] primary key in another column On 1/26/2018 5:47 PM, Roman Fleysher wrote: > I will use this table as a manager. There will be multiple columns holding > various

  1   2   >