Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

Stefan Keller wrote: > Thank you, Tom and Dan, for your constructive answers. > > To Pavel: My application reads the column types out in order to pretty > print the values - as mentioned by Tom - but also to generate a dialog > for entering new data (of course combined with INSTEAD OF TRIGGERs). >

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

BareFeetWare wrote: > On 04/05/2010, at 3:14 AM, Stefan Keller wrote: > > >> But in SQLite if a view column comes from a function result or some >> computation, then the column type is NULL...!? It's not taking the >> result-type as mentioned in the manual >>

[sqlite] Implementing a CREATE_FUNCTION function

It's convenient to be able to define new functions in C. But sometimes, it would be *more* convenient to be able to define new functions in SQL. This could be done by registering a CREATE_FUNCTION() function; then you could write something like: SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1,

[sqlite] Problem with conflict resolution in triggers

I'm trying to implement a string pool using views and triggers: CREATE TABLE StringPool ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Val TEXT UNIQUE ); CREATE TABLE T ( KeyTEXT PRIMARY KEY, ValRef INTEGER REFERENCES StringPool(ID) ); CREATE VIEW V

[sqlite] CHECK constraints and type affinity

If I write sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); the constraint is applied AFTER converting N to an integer. sqlite> INSERT INTO T1 VALUES('42'); sqlite> INSERT INTO T1 VALUES('-5'); SQL error: constraint failed But if I write sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) =

[sqlite] String interning using foreign keys: How to write an INSERT trigger?

We have a e-mail program that uses a table like: CREATE TABLE Emails ( MessageID TEXT, FromAddr TEXT, ToAddr TEXT, -- ... ); The database takes up hundreds of megabytes of disk space. In order to avoid the duplication of storing the same addresses thousands of times, I'm planning

Re: [sqlite] Quoting strings for SQLite

Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 04/18/2010 11:03 PM, Dan Bishop wrote: > >> I've written a quoting routine too, in C++. I just truncated strings at >> the first NUL character because I didn't think that SQLite support

Re: [sqlite] Quoting strings for SQLite

Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 04/17/2010 07:12 PM, Dan Bishop wrote: > >> Newlines, backslashes, and double quotes can be included literally. The >> only other character you need to worry about is NUL. >> >

Re: [sqlite] Quoting strings for SQLite

Simon Slavin wrote: > I am using a particular program which needs to be able to mess with an > already-established database. It has to issue UPDATE and INSERT commands > using one string for the entire command: no opportunity for binding. So it > has to assemble commands by concatenation. In

Re: [sqlite] Preserving column size

Kevin M. wrote: > I have a C/C++ application in which I want to store data from a struct into a > table (using SQLite 3.6.23) and later retrieve data from the table and store > it back in the struct. But, I need a general interface for this as there are > many types of structs used. So, what

Re: [sqlite] dynamic typing misunderstanding

Igor Tandetnik wrote: > eternelmangekyosharingan > > wrote: > >> I create the following table: >> sqlite> create table t1(a); >> sqlite> insert into t1 values(123456789.123456789); >> >> I ran the following commands: >> sqlite> select * from t1; >>

Re: [sqlite] graphs and sql

Robert Citek wrote: > Hello all, > > Does anyone have any recommendations for books or other resources that > deal with working with graphs (i.e. vertexes and edges) using sql? > > For example, if I could store a graph in a sqlite database, I'd like > to query the database to know if the graph

Re: [sqlite] Import a other database. Search for the fastes way

Carsten Giesen wrote: > Now my question. > > I work on a way to have a cache DB from the Main Server on the PC of my > client. > In the case the server is down they can go on working. (Like Outlook and > Exchange) > > > > For the first time I have to copy a lot of data from the main server. > >

Re: [sqlite] Import feature requests

Simon Slavin wrote: > On 14 Dec 2009, at 5:13am, Walter Dnes wrote: > > >> The following might be options (compile time, config file, set manually; >> I don't care), but they should be available... >> > > It might be worth writing a separate sqlite3 import facility which just reads > a

Re: [sqlite] Output in currency format

Simon Slavin wrote: > On 13 Nov 2009, at 3:30am, Roger Binns wrote: > > >> Simon Slavin wrote: >> >>> Integers in all languages I'm aware of are not stored as mantissa/exponent, >>> they're stored as bits with complete precision. >>> >> There is one huge exception I found out the

Re: [sqlite] feature proposal - strong but dynamic typing

Darren Duncan wrote: > Roger Binns wrote: > >>> In fact this support might even be easier as it may only require >>> enhancements to >>> the SQL parser, which would generate VM opcodes like for a CHECK >>> constraint, >>> unless further work is done to optimize for the presented cases, or

Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote: >> *if* you know that the number *is* a date. >> > > If the column has a type of timestamp, it should be safe to always > assume that it IS a date. sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL); sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime +

Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote: > Strings have a number of other disadvantages in this case. They take > more computations to compare, they take time to parse when you read > them, and they take longer to build when you insert them. Generally, > storing dates as a number of some sort is ideal. > I do

Re: [sqlite] Late data typing. Am I missing something?

Jay A. Kreibich wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen >> Sent: Wednesday, October 28, 2009 3:11 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Late data typing.

Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote: > SQLite has plenty of date editing routines. Dates are stored in a double > as a Julian date. Well, that's one way of doing it. I store them as strings because I wanted a human-readable format. The downside is that this requires 19 bytes instead of 8. I wish SQLite could

Re: [sqlite] How to input a double num?

liubin liu wrote: > Now I use the sqlite3_mprintf() and the "%f" to get the double num. My code > is below. > > Now there is a num like "212345678901234567890123456.988290112". With the > way of "sqlite3_mprintf()" and "%f", the num is cut to > "2123456789012346000.00". > > > How to

Re: [sqlite] Grammar of "X is Y"

John Crenshaw wrote: > Yeah, I tend to agree that null != null is confusing, But SQL doesn't have NULL != NULL. It has NULL != NULL IS NULL. That makes it even more confusing. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Infinity

I've noticed that I can use IEEE Infinity values in SQLite by writing any literal too big for a double. sqlite> CREATE TABLE foo (x REAL); sqlite> INSERT INTO foo VALUES (9e999); -- +Inf sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets

Re: [sqlite] sparse matrix in scientific computing with sqlite

Michael Chen wrote: > --this is my first version for the purpose of storing sparse numerical > matrix in sql > --please let me know how to fix the bug at the end of the file, and how to > tune the performance > --or any better reference, thanks! > > .explain--

Re: [sqlite] INTERSECT?

P Kishor wrote: > I don't even know how to title this post, and it just might be > something very obvious. Either way, I apologize in advance. Consider > the following -- > > sqlite> SELECT * FROM foo; > a b > -- -- > 1 6 > 2 6 > 2 3 > 3

Re: [sqlite] C code spanish character insert problem

Bible Trivia Extreme wrote: >> Open your spanish.txt file in a hex editor. The letter 'ñ' should be >> encoded as C3 B1. If you see F1 instead, it means your file is in >> >> > ISO-8859-1 or something similar.Thanks Dan, it seems to be F1. So what do I > do exactly? > > Im assuming I need

Re: [sqlite] C code spanish character insert problem

Bible Trivia Extreme wrote: > On Tue, Sep 29, 2009 at 9:48 PM, Simon Slavin > wrote: > > >> On 30 Sep 2009, at 2:25am, Bible Trivia Extreme wrote: >> >> >>> Is there something special I need to do in the C/Sqlite >>> code to make this work properly? >>>

Re: [sqlite] inserting a number as a string and keeping it that way

Sean Moss-Pultz wrote: > Hi List > > Here's basically what I'm doing: > > sqlite> create table test(t text); > sqlite> insert into test values(0123); > sqlite> select * from test; > 123 > > How can I get the string to stay 0123? I've read the docs about > "Column Affinity." But I guess I'm

Re: [sqlite] Tedious CSV import question

C. Mundi wrote: > On 9/25/09, Jay A. Kreibich wrote: > >> ... >> >> CSV is a great quick and dirty format to move data. But it isn't >> "simple" and it isn't nearly as universal as many assume. It works >> great if you're just moving simple numbers and strings that

Re: [sqlite] Tedious CSV import question

C. Mundi wrote: > Hi. I have scanned the list archives and Googled. I may have missed > something, but what I found was not sufficiently general or robust for my > needs. > > Happily, sqlite has a very nice .output mode for CSV. It correctly > double-quotes fields when they contain a space or a

Re: [sqlite] Importing data into SQLite

T wrote: > > You can use the sqlite binary to import data from a CSV file - if you do it > that way you have to make sure that your data fields in the SQLite database > match exactly in order the data in the CSV file. That's been my experience. > The other way is to do it programmatically

Re: [sqlite] SQL Lite date / time functions

Alexey Pechnikov wrote: > Hello! > > On Monday 21 September 2009 19:56:07 Igor Tandetnik wrote: > >>> Are correct selects like as >>> SELECT * from tbl_name where date = julianday('now','start of month'); >>> >> I see no reason why not. Note that the condition will only hold when >>

Re: [sqlite] Viewer for blobs in hex?

Yan Bertrand wrote: > Hi all, > > > > I would like to display the contents of blobs in my table as > hexadecimal. I have not found any easy way of doing this. I tried : > > - wxSQLitePlus, but it does not display blobs contents (or I > could not make it do so) > > -

Re: [sqlite] SQLite Date problem

P Kishor wrote: > On Sun, Sep 20, 2009 at 4:16 AM, Dan Bishop <danbisho...@gmail.com> wrote: > >> Max_wang wrote: >> >>> A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT >>> date(253392451200.0, >>> 'unixepoch');"

Re: [sqlite] SQLite Date problem

Max_wang wrote: > A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, > 'unixepoch');" > The result was "-09-09". > > But now I use SQLite 3.6.18 replace it,this SQL execute result is > "-1413-03-01". > > Is this a Bug? > Apparently so. And the problem first

[sqlite] Convention for column type names?

I understand that SQLite lets you use arbitrary names for column types, and all that's significant is a few substrings like "INT", "CHAR", and "BLOB". But what's the common practice? Do you declare everything as INTEGER, TEXT, BLOB, REAL, or NUMERIC for a one-to-one match with the actual

Re: [sqlite] Booleans in SQLite

Nicolas Williams wrote: > On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote: > >> I'm just curious how difficult it would be to add >> support for booleans in SQLite. This would most likely involve adding a >> new type affinity as well as adding "true" and