Re: [sqlite] Hints for the query planner

[kyan]

Hello Dr Hipp,

First of all, I apologize for this rather off-topic suggestion knowing that
you may have already implemented the syntax you describe, but there is an
IMHO good reason for it, read ahead.

On Tue, Sep 10, 2013 at 10:26 PM, Richard Hipp  wrote:

> SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE unlikely(cname LIKE '%bach%')
>AND composer.cid=track.cid
>AND album.aid=track.aid;
>

I would prefer that the planner hint is not interleaved inside normal SQL
syntax. Instead I propose a special comment-like syntax instead, as
Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">":

SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE cname LIKE '%bach%'
> /*> unlikely */
>  AND composer.cid=track.cid AND album.aid=track.aid;
>

or:

SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE cname LIKE '%bach%'
> --> unlikely
>AND composer.cid=track.cid
>AND album.aid=track.aid;


If the hint is to be applied to an expression that combines many column
predicates with AND (I am not sure if this actually makes sense):

SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE unlikely(cname LIKE '%bach%'
>AND composer.cid=track.cid)
>AND album.aid=track.aid;
>

then a -normally redundant- pair of parentheses can be used to specify the
scope of the hint:

SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE (cname LIKE '%bach%' AND composer.cid=track.cid) /*> unlikely */
>AND album.aid=track.aid;
>

The SQLite SQL parser will have to look for exactly "/*>" or "-->" without
whitespace between the characters, so it can easily tell a planner hint
from a plain comment with a single character read-ahead. Also, the fact
that hints are "transparent" to the SQL syntax will allow the query parser
to handle them in an "orthogonal" way (e.g. a small separate parser for
hints) to normal SQL parsing, IMO making handling of any future hints
easier to add.

The main reason for this proposal is that the planner hint will be ignored
by default by other SQL parsers without the need to modify them, which in
some cases may not even be possible. For instance it will allow someone to
write SQL that is valid in databases of alternative DB vendors and still
provide planner hints when the DB vendor is SQLite (that is why I replaced
"+" with ">", to avoid conflicts with a hypothetical alternate Oracle query
optimizer) without having to modify the SQL in the application code to
remove the hints. This is a property of the Oracle optimizer hint syntax I
have always appreciated when writing SQL that is to be executed in
databases of alternative DB vendors with the same schema, for applications
where the user chooses the database vendor from a list of supported ones.

For more on Oracle optimizer hints see
http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm.

As for the name of the hint itself I would propose:

--> PROBABLY(True) -- the current default
--> PROBABLY(False)
--> PROBABLY(False, 0.7)
--> PROBABLY(False, 0.6, 0.3)  --re "pedantic detail", the second value if
for True, the remainder for NULL.

Kind regards,

Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[kyan]

On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth  wrote:

> I have a table with the following (simplified) structure
>
> PKeyINTEGER PRIMARY KEY
> NameTEXT
> Sequence INTEGER
>
> The Name/Sequence pair of columns is defined as UNIQUE
>
> I need to insert a new row into a point between two sequence numbers.  For
> example, if the existing rows are:
>
> Name   Sequence
> ---
> Blue   1
> Blue   2
> Blue3
> Blue   4
> Blue   5
>
> ... I might need to insert a new Blue/3.
>
> If the Sequence column needs not be consecutive integers but just
specifies order, consider turning it to a float. Then you can insert a row
between two existing rows with Sequence a and b by using their median (a +
b) / 2 as the new row's Sequence:

Blue   1
Blue   2
--> Blue(2 + 3) / 2 = 2.5
Blue3
Blue   4
Blue   5

and then:

Blue   1
Blue   2
--> Blue2.25
Blue2.5
 Blue3
Blue   4
Blue   5

and so on. This way you avoid having to modify following rows on each
insertion.

--
Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[kyan]

On Fri, Jul 19, 2013 at 3:49 PM, Sqlite Dog  wrote:

> That's interesting. What about pas file with function declarations to
> SQLite API? Should it be modified somehow?
>
>
If you use static dll loading and linking, Instead of declaring API
functions as external 'sqlite3.dll' you declare them as plain external. The
linker will automatically link to their implementation from the object file
sqlite3.obj by name.

If you have dynamic dll loading then you must have declared them as
function prototypes and assign them by hand using GetProcAddress() after
calling LoadLibrary() for SqLite3.dll. In this case the sqlite header API
file will require more modifications. For instance this:

var
  sqlite3_open: function(filename: PUTF8Char; var DB: Pointer): integer;
cdecl;

will have to be changed to this:

function sqlite3_open(filename: PUTF8Char; var DB: Pointer): integer;
cdecl; external;

but if the names of the function pointer variables are as in the
amalgamation then every unit that uses the API unit and calls its functions
will not need any modifications. For example the following code:

  Rslt := sqlite3_open(FileName, db);

compiles with both declarations.

HTH.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[kyan]

On Fri, Jul 19, 2013 at 10:21 AM, Sqlite Dog  wrote:

>
> Our database manager is developed using Delphi (Pascal). Thus it is not
> possible to statically link SQLite library, SQLite.dll is used. Is there
> some other way to support SEE in our project?
>

It is possible to compile the SQLite amalgamation with Embarcadero CBuilder
and statically link the object file in a Delphi program using the $L
directive; I have
successfully
done this
with Delphi XE
. So
-although I haven't tried it-
I suppose it is possible to do the same with SEE since it is open-source.

--
Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exact content of a column in a row

[kyan]

On Tue, May 28, 2013 at 3:26 PM, kyan <alfasud...@gmail.com> wrote:

>
> On Tue, May 28, 2013 at 3:06 PM, Dave Wellman <dwell...@ward-analytics.com
> > wrote:
>
>> Is there a way to extract the content of a column/row in its 'stored'
>> format
>> - i.e. the actual bytes?  So that it's 'fairly' easy to read! I have a
>> utility that will look at the hex bytes of any file, but the entire
>> database
>> is (understandably) quite complex.
>>
>>
>>
>> Let me explain the problem that I'm facing and someone might point me in a
>> better direction.
>> 
>>
>>
> Maybe the typeof() function could help?
> 
>

Also, in order to troubleshoot your application(s), script(s) etc. that
modify the database and find the piece of code that inserts a value of the
wrong type you can create two triggers on your table:

CREATE TRIGGER BI_TEST BEFORE INSERT ON test FOR EACH ROW
BEGIN
  SELECT CASE
WHEN (typeof (new.col) <> 'integer') THEN
  RAISE(FAIL, 'Invalid type inserted')
END;
END;

CREATE TRIGGER BU_TEST BEFORE UPDATE OF col ON test FOR EACH ROW
BEGIN
  SELECT CASE
WHEN (typeof (new.col) <> 'integer') THEN
  RAISE(FAIL, 'Invalid type inserted')
END;
END;

You can keep them until you run the app/scripts and locate the problem (you
will get an error at exactly that command) and then drop them or keep them
permanently to ensure that you will never have a value of the wrong type.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exact content of a column in a row

[kyan]

On Tue, May 28, 2013 at 3:06 PM, Dave Wellman
wrote:

> Is there a way to extract the content of a column/row in its 'stored'
> format
> - i.e. the actual bytes?  So that it's 'fairly' easy to read! I have a
> utility that will look at the hex bytes of any file, but the entire
> database
> is (understandably) quite complex.
>
>
>
> Let me explain the problem that I'm facing and someone might point me in a
> better direction.
> 
>
>
Maybe the typeof() function could help?

http://www.sqlite.org/lang_corefunc.html#typeof

For instance:

create table test (col);
insert into test values (1);
insert into test values ('1');
insert into test values (1.0);
select col, typeof(col) as type from test;

col type
1integer
1text
1real

and for a possible data fix:

update test set col = cast(trim(col) as integer) where typeof(col) <>
'integer';
select col, typeof(col) as type from test;

col type
1integer
1integer
1integer

--
Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

[kyan]

On Fri, May 24, 2013 at 4:46 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> It's exhaustive in that it absolutely verifies if the key exists or not.
>  However, it doesn't necessarily do a full database scan.  I assume it uses
> available indexes and does a standard lookup on the key.
>
> So, it still might be fast enough for what you want (though I missed the
> beginning of the thread).
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
>
> On Fri, May 24, 2013 at 7:07 AM, kyan <alfasud...@gmail.com> wrote:
>
> It is exhaustive
>

Thank you both for your answers.

Since I am writing code for an application server that connects to
databases of different database vendors used by other development teams I
have no way of knowing anything about the underlying database, so I will
not take any chances.

--
Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

[kyan]

On Fri, May 24, 2013 at 3:56 PM, Clemens Ladisch  wrote:

> This is why we have transactions.
>

Perhaps but I would like to refrain from arguing on this at this time.
Instead I would like an answer to my original question if possible.

TIA.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

[kyan]

On Fri, May 24, 2013 at 2:47 PM, Clemens Ladisch  wrote:

> Foreign key checks are not intended to prevent user errors but to
> prevent programming errors.  In other words, your program is responsible
> for preventing the user from trying to delete some X that is still
> referenced by some Y (by doing a check before deleting, or, preferrably,
> by structuring the UI so that deleting such a X is not possible).
>

I am sorry but I disagree. If my program was to prevent the FK violation it
would have to do a query; infact it would be the exact same query the
SQLite engine does in order to enforce the FK. This is clearly redundant.

Apart from performance, there is also the issue of concurrency, where the
program does the query , decides it is OK to make the change but in the
meantime somebody else has made another change that invalidates the
program's change, for instance inserted a detail record to the master the
user is trying to delete.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

[kyan]

Hello Dr. Hipp and all,

Due to its implementation of foreign keys and their deferring, SQLite does
not provide any useful information when a foreign key is violated; it gives
a not very helpful "Foreign key constraint failed" message instead. This is
a problem because an application developer cannot produce a proper message
to a user when a FK is violated, for instance "You cannot delete X because
it is connected to Ys" because they don't know and can't somehow deduce X
and Y.

It occurred to me that this may be adequately worked around if foreign keys
are deferred, using the "pragma foreign_key_check" command. The idea is
that when a SQLITE_CONSTRAINT error occurs at commit, the application code
can catch it and use the foreign_key_check pragma to get information about
the FK violation(s) that caused the commit error so that a meaningful user
message can be produced before doing a rollback. But this would impose a
serious performance penalty if the check is "exhaustive" meaning that ALL
records in the database are checked against ALL foreign key constraints.

So my question is, is this pragma exhaustive or is it somehow optimised so
that it does not always perform a full database scan -e.g. by means of some
internal per-transaction FK violation counter or list? Because if it is
optimised and thus fast enough then I suppose I can try to use it for the
purpose I have described.

TIA.

--
Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copying an SQLite database

[kyan]

On Thu, Mar 14, 2013 at 4:28 PM, J Trahair
 wrote:
> There will be times when the program running the SQLite database will copy 
> the database file MySQLiteDatabase.db to another folder, where the copy will 
> be zipped (compressed). What happens if another user (User B) is writing a 
> record to the db at the same moment that User A's program is copying it to 
> the other folder. Glitches, corruption...or perfect integrity?

If the program cannot ensure that there are no open connections to the
database file for the duration of the file copy then you should use
the SQLite backup api to copy the db file instead of a plain file
copy. The result is guaranteed to be consistent. For instance if an
update is made to the db during the backup process then the process
will be restarted transparently.

http://www.sqlite.org/backup.html

HTH
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] per-context resources for user-defined function

[kyan]

On Mon, Mar 11, 2013 at 12:52 PM, James K. Lowden
 wrote:
> I need one data structure
> per context.  I don't see support for that in the SQLite API.  Am I
> expected to maintain my own global associative array?

Not if you use sqlite3_aggregate_context(). See
http://www.sqlite.org/c3ref/aggregate_context.html

HTH

Constantine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to create a foreign key in existing table?

[kyan]

On Sat, Mar 2, 2013 at 6:02 PM, Simon Slavin  wrote:
> Nevertheless, you do now understand that there are no magic undocumented 
> calls in SQLite that people are using to do things like his.  Which is what 
> your question was.

OK, thanks again.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to create a foreign key in existing table?

[kyan]

Thank you for your response Simon.

On Sat, Mar 2, 2013 at 3:51 PM, Simon Slavin  wrote:
> SQLite isn't at its root a DDL.  It's a C API, fully documented here:
>
> 
>
> That's SQLite and that's its full documentation.  As designed, to add SQLite 
> facilities to your programming project you simply include the C source code 
> (.c and .h files) that implements those commands into your project.  That's 
> the 'amalgamation source' you see us talking about on this list.

I am familiar with the C API. I have successfully built the
amalgamation with Embarcadero CBuilder, translated the C API to Object
Pascal and linked the .obj file that CBuilder output to Delphi
programs with success, This way I don't need to distribute the SQLite
dll, the C source is directly linked in Delphi. And I have written a
thin Object Pascal object layer on top of the C API which allows me to
use SQLite in Delphi programs.

> Any DDL you see is someone trying to make some or all of those things 
> accessible as a DDL.  People who make DDLs for SQLite can put as many or as 
> few of these things into their DDL as they like, along with as many things 
> they made up themselves as they like.  There is more than one DDL and for all 
> we know they might all be different to one-another.  If you want to know 
> about a particular DDL, go find the documentation for that DDL.

But... if I understand correctly adding some DDL extensions to SQLite
would mean modifying its SQL parser so that it can parse e.g. the
expression "ALTER TABLE tab DROP COLUMN col" and its engine in order
to be able to execute it. Wouldn't that mean modifying the source? I
would prefer not to do that for various reasons, the main one being
that I would have to learn C (I can read it but never coded with it)
and then make myself very familiar with the code of SQLite itself in
order to have it execute the introduced DDL, which is far beyond my
scope.

On the other hand, if you mean executing the "introduced" DDL in the
context of my own application, read ahead.

> Having said that, the GUI admin tools and DDLs which implement something like 
> adding/removing constraints usually doesn't include new low-level C code to 
> fiddle with the database like the source code does.  They normally does it by 
> using various API calls in a way that looks like you're doing just one call.  
> For instance, to add a new constraint to an existing table ...
>
> ALTER TABLE myTable ADD CONSTRAINT (capacity > 0)   <-- imaginary command for 
> discussion only
>
> they do this:
>
> 1. Get the old table definition as a text string.
> 2. Add the constraint to the definition.
> 3. Create a new table with this new definition.
> 4. Copy all the data in the old table into the new table.
> 5. Drop the old table.
> 6. Rename the new table with the old table's name.
>
> You can do all the above yourself using just SQLite commands which are 
> already in the API.  All they've done is specify one command in the DDL which 
> does them all for just one call.
>
> (There are complications which mean that you cannot do exactly the above and 
> expect everything to work perfectly.  I'm using it just as a demonstration.)

I am already doing that but it is very difficult to do with FKs. Even
if you disable FK enforcement for the duration of the above sequence
of actions one may end up with invalid FKs, for instance using renamed
or dropped columns. Even worse the consequences will not be evident
until the first time the FK is enforced as part of a DML statement,
where you get a pretty generic error (constraint failed). This means
that the database schema is in an inconsistent state.

So in order to do a structural change properly one would have to:

-Disable FK enforcement to avoid losing detail records when the table
is dropped.
-Find FKs that are affected by the coming change.
-Perform the change doing the sequence you illustrated.
-Repeat the sequence for any tables linked with FKs to fix or drop
them since it is impossible to alter a table's FK without recreating
the table.
-Reenable FKs.

Ok, it can be done but it is tedious at best, hence my original
question. I thought that some tool writer had a better solution they
wouldn't mind sharing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to create a foreign key in existing table?

[kyan]

On Tue, Jan 1, 2013 at 8:01 PM, Peter Haworth  wrote:
> There are plenty of third party tools out there that will take care of
> adding/removing constraints to existing tables and a whole lot of other
> functions that aren't available in SQLite's DDL, while accounting for all
> their secondary effects.

About these functions that are not available in DDL, are they part of
SQLite source? Are they part of the interface? They don't seem to be
documented anywhere. Maybe they are available only for third party
tool developers? How can someone get documentation for them?

Thank you in advance.

Constantine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to find table exists or not

[kyan]

On Thu, Dec 6, 2012 at 6:49 PM, Staffan Tylen  wrote:
> SELECT CASE WHEN EXISTS (...) END
>
> On Thu, Dec 6, 2012 at 5:47 PM, Durga D  wrote:
>
>> I have situation in which I want to read particular record if table exists.
>> Based on that record information, I have to execute some logic on other
>> tables of the same database. If record doesn't exist I need to create new
>> table.


SELECT * FROM ATable WHERE EXISTS (SELECT * FROM sqlite_master where
type = 'table' and name = '')

Can also make it correlated if the name of the table whose existence
you are checking depends on the value of a column in each record of
ATable:

SELECT * FROM ATable WHERE EXISTS (SELECT * FROM sqlite_master where
type = 'table' and name = ATable.TableName)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite converts all unicode characters into ANSI

[kyan]

On Sat, Nov 17, 2012 at 3:29 PM, ZikO  wrote:
> I have a slight problem with sqlite and its text encoding. I read from
> documents that sqlite handles UTF-8 by using a command PRAGMA encoding =
> "UTF-8"; etc. My database is to store Polish text. The database is going to
> be used with Qt later on. I have a script with two commands: CREATE TABLE
> ... and INSERT INTO ... This file is encoded in UTF-8. However, when I build
> and fill database via a command *sqlite3 myname.db < the_file.sql*, I create
> both database and the table but all specific characters such as ą, ć, ź, Ż
> etc. are automatically converted into a, c, z, Z etc. I thought it would be
> a problem with the command line. So I downloaded SQLite Manager 2009 and
> when I copy / pasted the whole script to execute it in SQLite Manager, I
> noticed the effect is exactly the same. Characters are automatically
> converted during copy / pasting. Is the SQLite limitted to use only with
> ANSI characters?

I've used SQLite with Greek characters with success.

Just a wild guess: are your scripts saved as ANSI text without a BOM?
If so, you could try saving them as unicode or UTF-8 with a BOM and
try again. Use Notepad's save as command to do this. Also, make sure
that any tool you use to execute them is unicode-aware.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to measure query time in sqlite at runtime?

[kyan]

On Sat, Nov 17, 2012 at 1:26 AM, 杨苏立 Yang Su Li  wrote:
> I want to measure the execution time of each sql statement in sqlite.

Use sqlite3_profile() to register a profiler callback for a database
connection that will be called for every SQL statement you execute on
that connection.

http://www.sqlite.org/c3ref/profile.html

HTH.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger logic with INSERT OR REPLACE

[kyan]

On Wed, Nov 14, 2012 at 6:01 PM, Simon Slavin  wrote:
>  It's also worth knowing about
>
> UPDATE OR REPLACE
>
> which is similarly not MERGE, but has more MERGE-like behaviour.  Though what 
> it does I had to figure out myself since the documentation on the 'UPDATE' 
> page doesn't describe it.

>From what I've seen UPDATE OR REPLACE will *delete* existing records
if a PK or unique constraint is violated:

insert into master values (1, '1');
insert into master values (2, '2');
insert into detail values (1, 1, '1');
insert into detail values (2, 2, '2');

update or replace master set id = 1, descr = 'a' where id = 2

select * from master;
1   'a'

select * from detail;
2   1   '2'

In this case detail record 1 - 1 - '1' seems to have been
cascade-deleted and 2 - 2 - '2' cascade-updated.

Still nothing to do with MERGE behaviour IMHO. There doesn't seem to
be a way to perform "UPSERT"s in SQLite. An UPSERT modifies an
existing record or inserts one if a matching one is not found. It
never deletes one.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger logic with INSERT OR REPLACE

[kyan]

> No.  INSERT OR REPLACE does an INSERT, possibly after doing a DELETE.  It 
> never does an UPDATE.

http://www.sqlite.org/lang_conflict.html

Similarly any detail rows that are linked with foreign keys to the
affected row will be handled according to the FK's ON DELETE clause.
For instance if the FK is ON DELETE CASCADE detail records will be
deleted even if the affected row's key's value is not changed by the
INSERT or REPLACE statement.

create table master (id int primary key, descr text);
create table detail (id int, parentid int, descr text, foreign key
(parentid) references master on delete cascade);
insert into master values (1, '1');
insert into detail values (1, 1, '1');
insert or replace into master values (1, '2');
select * from detail;
> 0 records

I mention this because it was a cause of grief for me, not having paid
proper attention to the behaviour of INSERT OR REPLACE.

PS: Because of this the INSERT OR REPLACE statement cannot be
considered logically equivalent to MSSQL or ORACLE's MERGE statement
(http://en.wikipedia.org/wiki/Merge_%28SQL%29)  which IMHO should be
added to SQLite at some point.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data type information for derived columns

[kyan]

On Wed, Oct 10, 2012 at 8:21 PM, jkp487-sql...@yahoo.com
 wrote:
> New to SQLite. Is there a way to get column data type information for
> derived columns in a query or view? For example, if I have something like
> this:
>
>
> select Customer.LastName || Customer.FirstName as Fullname
>
>
> then no data type is returned for that column. I’m using Delphi to access
> the database and Delphi’s field objects throw a fit if they don’t have a data
> type. Thanks much for your help.

I've had a similar problem with aggregate functions like SUM().
sqlite3_column_declType() returns an empty string for columns of a
statement that are the result of an expression but
sqlite3_column_type() should return the correct SqLite datatype (one
of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB) once the
statement has been prepared. Which API call does the Delphi unit that
implements SqLite connectivity uses to determine which TField
descendant to use for the column? From my experience it should use the
former first to get the "declared" datatype but should fall back to
the latter if the declared type is an empty string.

HTH
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detecting when a table is created

[kyan]

> Is there any possibility to be notified when a table is created (when a
> "CREATE TABLE XXX" is executed)?

You could try installing a profile callback using sqlite3_profile()
(see http://www.sqlite.org/c3ref/profile.html). Of course this
callback will be called for any kind of SQL or DDL statement that gets
executed in your database so you will have to distinguish CREATE TABLE
statements by parsing the SQL command text in the profiler callback's
second argument.

HTH

Constantine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users