Re: [sqlite] column types and constraints

On Fri, 29 Jun 2018, David Burgess wrote: "This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply

Re: [sqlite] column types and constraints

"This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you

Re: [sqlite] unique values from a subset of data based on two fields

with status_one as ( select * from names where status = 1 ), one_names as ( select distinct name from status_one ) select min(id), status, name from names where status = 0 and name not in one_names group by status, name union all select * from status_one; -Original Message-

Re: [sqlite] column types and constraints

On Fri, 29 Jun 2018, danap wrote: Unless your trying to create a generic user interface. I have spent the last month trying to solve affinity with the columns. The only way it seems to me to guarantee to solve the issue is to test every retrieved column value and test its affinity. Affinity

Re: [sqlite] column types and constraints

On 29 Jun 2018, at 17:46, Bob Friesenhahn wrote: > On Fri, 29 Jun 2018, danap wrote: >> >> Unless your trying to create a generic user interface. >> >> I have spent the last month trying to solve affinity with the columns. >> The only way it seems to me to guarantee to solve the issue is to test

Re: [sqlite] unique values from a subset of data based on two fields

Thank You David - I was just starting to play with CTEs Paul www.sandersonforensics.com SQLite Forensics Book On 29 June 2018 at 17:45, David Raymond wrote: > with status_one as ( > select * > from names > where status = 1 > ), > one_names

Re: [sqlite] column types and constraints

On Fri, 29 Jun 2018, R Smith wrote: Type-constraining here would merely protect the programmer against him/herself. I know this is not entirely without merit, but a feature I would happily forego when weighed against even just one of the points made in the previous paragraph. The 'lite' in

Re: [sqlite] column types and constraints

On 6/29/18, Bob Friesenhahn wrote: > > Without adding all the necessary safe-guards to ensure that only valid > data goes into the database, sqlite puts the using application at risk > (security and stability) with its wishy-washy ways. > Can you provide an example of a security of stability

Re: [sqlite] column types and constraints

On Jun 29, 2018, at 11:46 AM, Richard Hipp wrote: > > On 6/29/18, Bob Friesenhahn wrote: >> >> Without adding all the necessary safe-guards to ensure that only valid >> data goes into the database, sqlite puts the using application at risk >> (security and stability) with its wishy-washy ways.

Re: [sqlite] column types and constraints

On 2018/06/29 6:25 PM, Bob Friesenhahn wrote: On Fri, 29 Jun 2018, David Burgess wrote: "This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values//... This is an important feature of SQLite.  In hindsight, an excellent decision. It

Re: [sqlite] column types and constraints

SQLite returns what has been saved in the database, the application should control what it writes to the database. If the database can be edited by an untrusted entity, the application should treat the content of the database as untrusted. Even if SQLite enforced data types, someone who can edit

Re: [sqlite] column types and constraints

On Fri, 29 Jun 2018, Richard Hipp wrote: On 6/29/18, Bob Friesenhahn wrote: Without adding all the necessary safe-guards to ensure that only valid data goes into the database, sqlite puts the using application at risk (security and stability) with its wishy-washy ways. Can you provide an

Re: [sqlite] unique values from a subset of data based on two fields

Thanks Ryan As often is the case the the actual problem is more complex than my example - sometimes we over simplify to, well, simplify - but you have both given me some ideas and I'll go away and play. Paul Paul www.sandersonforensics.com SQLite Forensics Book

Re: [sqlite] unique values from a subset of data based on two fields

The solution from David works perfectly, just want to point out some CTE things since you mention getting into it. 1st - A nice thing about CTE is that, in the case of a non-recursive CTE (like this one), it can well be replaced by a simple sub-query, however, the CTE can be referenced more

Re: [sqlite] column types and constraints

> At least, enough-so in my mind to defend David's assessment of "an > excellent decision". Sorry, I cannot follow the point. a) Why would I put strings in an integer column? If I need to do so, I have a concept error in my database schema. b) I think that noone wants type affinity to be

Re: [sqlite] column types and constraints

On 2018/06/29 9:00 PM, Warren Young wrote: The following is an answer to your challenge, not a feature request// 4. The application uses the value as an index into an array. If the application passes int* to sqlite3_column_int() to avoid compiler complaints, they’ll get a negative

Re: [sqlite] column types and constraints

On 29 Jun 2018, at 9:23pm, Thomas Kurz wrote: > a) Why would I put strings in an integer column? If I need to do so, I have a > concept error in my database schema. > > b) I think that noone wants type affinity to be actually removed. I'd just > like an option to make SQLite behave like any

Re: [sqlite] column types and constraints

> Indeed, but that option does exist, it's called CHECK constraints You're clearly right, but from my point of view, it's redundant to say COLUMN xy INTEGER CHECK type=integer, because "COLUMN INTEGER" already implies that the column is integer. And, btw, as CHECK already exists, it shouldn't

Re: [sqlite] column types and constraints

pragma application_written_by=coder; -vs the default, which is- pragma application_written_by=programmer; ;-) All of the issues raised are "application" problems, not database problems. Clearly if you retrieved a value from the database and want to use it as an index you have to do bounds

Re: [sqlite] unique values from a subset of data based on two fields

>I want a query that returns all of the records with status = 1 and >unique records, based on name, where the status =0 and the name is >not in the list status=1 Translation into SQL using English to SQL Translator, using the most direct translation on the "problem statement" above directly

Re: [sqlite] column types and constraints

On 2018/06/29 10:23 PM, Thomas Kurz wrote: b) I think that noone wants type affinity to be actually removed. I'd just like an option to make SQLite behave like any other DBMS and - respect the data type declared for a column - reject column definitions with invalid types (e.g. "STRING")

Re: [sqlite] column types and constraints

On 2018/06/30 12:05 AM, Thomas Kurz wrote: Indeed, but that option does exist, it's called CHECK constraints You're clearly right, but from my point of view, it's redundant to say COLUMN xy INTEGER CHECK type=integer, because "COLUMN INTEGER" already implies that the column is integer. And,

Re: [sqlite] column types and constraints

On Jun 29, 2018, at 4:36 PM, Keith Medcalf wrote: > > All of the issues raised are "application" problems, not database problems. Computers are here to aid humans, not the other way around. > Clearly if you retrieved a value from the database and want to use it as an > index you have to do

Re: [sqlite] column types and constraints

But there is no API which will permit you to retrieve "unsigned" integers from SQLite3. Nor is there an API which will let you "send" an unsigned integer to SQLite3 for storage in the database. All the sqlite3_column and sqlite3_bind interfaces deal only with signed integers. If you are using

Re: [sqlite] Problem with sqlite3 .import command

On 28 Jun 2018, at 9:21am, Константин Краснов wrote: > However, a table with name " my-test-table" was created in the database and > is empty > > sqlite> .schema > CREATE TABLE [my-test-table]( > "col1" TEXT, > "col2" TEXT > ); Dear Konstantin, The above is not showing a table with the name

Re: [sqlite] Precompiled Binaries for Windows

Hi Simon. I solve my problem. I use MSVS 2017 My steps: Download sqlite-amalgamation-324.zip icu4c-57_1-Win32-msvc10.zip Extract archives into directories named by archive names. Open "Developer Command Prompt for VS 2017", go to sqlite-amalgamation-324 and execute next commands

Re: [sqlite] Precompiled Binaries for Windows

On 29 Jun 2018, at 4:33pm, Ruslan Yakauleu wrote: > I solve my problem. I use MSVS 2017 Ruslan, very good. Thank you for posting your solution. It may help someone else. Happy programming. Simon. ___ sqlite-users mailing list

[sqlite] unique values from a subset of data based on two fields

I have a table Create table names (id int, status int, name text) 1, 1, 'paul' 2, 1,'helen' 3, 0, 'steve' 4, 0, 'steve' 5, 0, 'pete' 6, 0, 'paul' I want a query that returns all of the records with status = 1 and unique records, based on name, where the status =0 and the name is not in

Re: [sqlite] unique values from a subset of data based on two fields

Select DISTINCT name,id,status from names where status = 1 *Michele Pradella* /R Software Engineer / michele.prade...@selea.com Office: +39 0375 889091

[sqlite] Precompiled Binaries for Windows

Hi! Please can anybody describe how can I reproduce this build from sources? https://www.sqlite.org/download.html - Precompiled Binaries for Windows -- sqlite-dll-win32-x86-324.zip (444.18 KiB) 32-bit DLL (x86) for SQLite version 3.24.0 I need this build but with a small change: ICU

[sqlite] Problem with sqlite3 .import command

Then i try to import following .csv file col1;col2 1;1 2;2 3;3 with .import command into a table with name what contents "-" sign sqlite> .open test.db sqlite> drop table if exists [my-test-table]; sqlite> .mode csv sqlite> .separator ; sqlite> .import 'my-test-table.csv' [my-test-table] And

Re: [sqlite] Precompiled Binaries for Windows

On 28 Jun 2018, at 10:00am, Ruslan Yakauleu wrote: > I need this build but with a small change: ICU builtin support required. > But in some cases (searched in web) I can't build actual sources. In other > cases DLL or DEF is wrong and can't be used properly in MSVS projects. You told us MSVS.