Re: [sqlite] column types and constraints

I see no unsigned integer support anywhere ... https://www.sqlite.org/datatype3.html And there are no API functions dealing with unsigned integers, only standard 2-s complement signed integers. https://www.sqlite.org/c3ref/funclist.html Declaring something an "unsigned integer" is the same

Re: [sqlite] column types and constraints

On Jun 29, 2018, at 10:17 PM, Keith Medcalf wrote: > > So what you really want is (a) to be able to error-out on SQL operations that > do not use proper affinity words (ie, only accept INTEGER, DOUBLE, TEXT and > BLOB), and; (b) to crash or return an error instead of converting the data >

Re: [sqlite] column types and constraints

Hi, ALL, I am not sure what are we talking about here and is discussed. Just like on the paper the hard drive is storing the characters. It is for us (humans, developers) to interpret those characters as a TEXT, numeric value (be it INTEGER or FLOAT/DOUBLE) or some binary data. As long as the

Re: [sqlite] column types and constraints

On 30 Jun 2018, at 8:58pm, Wout Mertens wrote: > Hmm, very odd. I'll need to check what happened again. Pay a lot of attention to the type you supplied when you created the table. The post you're responding to has this line: > sqlite> create table x(x primary key); This does not supply a

Re: [sqlite] column types and constraints

In your case, yes. If you do not wish SQLite3 to "convert" to the requested storage type on storage of a value, then do not specify a storage type (or specify a storage type of BLOB). Then whatever you request-to-store will be stored without conversion. SQLite version 3.25.0 2018-06-21

Re: [sqlite] column types and constraints

> when in fact it was the third-party interface wrapper. The examples I provided were all taken from the current sqlite3.exe cli with 3.24.0 library. It is not a third-party issue. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] column types and constraints

Hmm, very odd. I'll need to check what happened again. I use node-sqlite3, but it also doesn't seem to convert ( https://github.com/mapbox/node-sqlite3/blob/master/src/statement.cc#L274) On Sat, Jun 30, 2018, 9:44 PM Keith Medcalf wrote: > > When you declare a column with no affinity (that is

Re: [sqlite] column types and constraints

When you declare a column with no affinity (that is with blob or none affinity), the data is stored precisely and exactly as presented with no conversions performed by SQLite3. You give it a character string, it stores a character string. You give it an integer, it stores an integer. You

Re: [sqlite] column types and constraints

> H2, HSQL Both are Java crap. SQLite is unfortunately the only embedded DBMS that gets along with a single file for both the library and the database files. (At least it's the only I know after scaning various systems.) ___ sqlite-users mailing list

[sqlite] FTS4 content table

Hi according to the documentation - 6.2.2. External Content FTS4 Tables An "external content" FTS4 table is similar to a contentless table, except that if evaluation of a query requires the value of a column other than docid, FTS4 attempts to retrieve that value from a table (or view, or virtual

Re: [sqlite] column types and constraints

story time: I was storing rows with text id's and never bothered setting a type, it was "id PRIMARY KEY" and I always assumed that it gave me back what I stored in it via the nodejs binding. One day I was storing a string of numbers which happened to fit in a 64 bit int, and so sqlite stored

Re: [sqlite] column types and constraints

> Bob wrote: > Affinity is only a hint and not an assurance of anything. > I solve the problem by bloating the schema with checks like this: > > foo integer default 1234 > check (typeof(foo) == 'integer'), > > This enforces that someone can't put "Hello world" where an integer > belongs.

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

On 2018/06/30 3:12 PM, Luuk wrote: Ok ,my highway to hell start here (regargind the use of SQL) In SQLite3 you are allowed to do this: SELECT a,b,c FROM t1 GROUP BY a The values of 'b' and 'c' will be taken from a 'random' row... But if we rewrite this in SQL, i am getting something like

Re: [sqlite] column types and constraints

> And yes, you might think (like me) that this is silly and they should > just fix it, warning people about the new way for a good few versions, > and then just roll it out. But, that would mean there will exist schemas Hmmm... but what kind of compatibility issues would you see if SQLite

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

On 30-6-2018 15:45, Luuk wrote: > > In SQLite3 you are allowed to do this: > SELECT a,b,c > FROM t1 > GROUP BY a > > The values of 'b' and 'c' will be taken from a 'random' row... > > But if we rewrite this in SQL, i am getting something like this: > SELECT >a, >(SELECT MIN(b) FROM T1

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

If you want the row with the minimum B, and the row with a minimum C, then the union of two queries would seem to be appropriate. Gerry Snyder On Sat, Jun 30, 2018, 6:45 AM Luuk wrote: > > > On 30-6-2018 15:39, Abroży Nieprzełoży wrote: > >> SELECT > >>a, > >>(SELECT MIN(b) FROM T1

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

>>> SELECT >>>a, >>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C >>> FROM t1 t >>> GROUP BY a >> Why not >> select a, min(b) as b, min(c) as c from t1 group by a; >> ? > > It still does not quarantee that the valuse show for b and c are

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

On 30-6-2018 15:39, Abroży Nieprzełoży wrote: >> SELECT >>a, >>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C >> FROM t1 t >> GROUP BY a > Why not > select a, min(b) as b, min(c) as c from t1 group by a; > ? It still does not quarantee that

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

> SELECT >a, >(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C > FROM t1 t > GROUP BY a Why not select a, min(b) as b, min(c) as c from t1 group by a; ? 2018-06-30 15:12 GMT+02:00, Luuk : > > On 30-6-2018 14:55, Keith Medcalf wrote: >> Note

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

On 30-6-2018 14:55, Keith Medcalf wrote: > Note that this is SQLite3 specific (and specific to Sybase of the era where > Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft > re-writes of SQL Server up to about 2000). Technically you cannot do a query > of the form: > >

Re: [sqlite] Determine collation associated with sort

>I think it's explaned in the docs here: >https://sqlite.org/datatype3.html#collation Thanks Luuk. That’s what I was looking for. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Determine collation associated with sort

SQLite will not select the collation based on the index -- it is exactly the opposite -- the collation requested is used to find an appropriate index. So if you do an order by that needs BINARY collation, and the only index available is a NOCASE collation index, that index cannot be used

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

Note that this is SQLite3 specific (and specific to Sybase of the era where Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft re-writes of SQL Server up to about 2000). Technically you cannot do a query of the form: SELECT c1, c2 FROM t1 GROUP BY c2; because each

Re: [sqlite] Determine collation associated with sort

On 30-6-2018 14:05, x wrote: > Suppose I have a select such as > > ‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’ > > I want to know the collation associated with the sort. I know a COLLATE > condition could be attached to the order by BUT suppose there’s none. How

Re: [sqlite] column types and constraints

> Le 30 juin 2018 à 13:30, R Smith a écrit : > > PS: Anyone here that takes web servers and the like through upgrades to > different Apache, or PHP versions, or different MySQL/MariaDB versions will > know the effort of re-engineering your work from years ago to fit the new > upgrade. SQLite

[sqlite] Determine collation associated with sort

Suppose I have a select such as ‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’ I want to know the collation associated with the sort. I know a COLLATE condition could be attached to the order by BUT suppose there’s none. How would I determine the collation

Re: [sqlite] column types and constraints

On 2018/06/30 9:04 AM, Thomas Kurz wrote: COLUMN xy VARCHAR(50) COLUMN xy MEDIUMTEXT COLUMN xy BIT COLUMN xy DECIMAL(5,2) Should those raise errors? Because to SQLite those are the exact same gibberish as: I would appreciate if SQLite raised an error each time the declaration mismatches the

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

Easier and pretty obvious :) Thanks Keith Paul www.sandersonforensics.com SQLite Forensics Book On 29 June 2018 at 23:20, Keith Medcalf wrote: > >I want a query that returns all of the records with status = 1 and > >unique records, based on name,

Re: [sqlite] column types and constraints

> Le 30 juin 2018 à 09:04, Thomas Kurz a écrit : > > CREATE TABLE a (col1 STRING); > INSERT INTO a (col1) VALUES ("3.0"); > SELECT * from a; > ---> 3// this should never happen!! SQLite type affinity rules clearly do not recognise STRING as TEXT: it does so only when the type contains the

Re: [sqlite] column types and constraints

> Le 30 juin 2018 à 09:04, Thomas Kurz a écrit : > >> a) COLUMN xy VARCHAR(50) >> b) COLUMN xy MEDIUMTEXT >> c) COLUMN xy BIT >> d) COLUMN xy DECIMAL(5,2) >> Should those raise errors? Because to SQLite those are the exact same >> gibberish as: > > I would appreciate if SQLite raised an error

Re: [sqlite] column types and constraints

On 30 Jun 2018, at 8:35am, Keith Medcalf wrote: > You "put" a ieee754 floating point double. Keith, Are you referring to this ? On 30 Jun 2018, at 8:04am, Thomas Kurz wrote: > CREATE TABLE a (col1 STRING); > INSERT INTO a (col1) VALUES ("3.0"); > SELECT * from a; > ---> 3// this should

Re: [sqlite] column types and constraints

> You "put" a ieee754 floating point double. No I don't, I put a string ;-) I am not complaining that I can put anything anywhere. But the data type that I provide should be kept. When providing a string, it should be stored as such to have the possibility to get back the original value. When

Re: [sqlite] column types and constraints

You "put" a ieee754 floating point double. If you retrieved an ieee754 floating point double, you would get back that which you put! The fact that internally SQLite3 stored it as a 3 (integer, token, string, whatever) is irrelevant. You "gets" what you "puts", as long as what you "putted"

Re: [sqlite] column types and constraints

> I don't disagree, but this means we lose sight of the important point >that, if you distill the problem to just "INTEGER", then sure, it looks > silly, and sure, you can fix it with internal auto-CHECKing, but in > SQLite the type affinity INTEGER stands father to LONGINT, MEDIUMINT, Ok, I