Re: [sqlite] TYPEOF in triggers

[Eric Bohlman]

Witold Czarnecki wrote:
I just tested it on 3.0.8 and - you are right - it works.
Is there any reason to use 2.8 instead of 3.0? I use SQLite via python 
(pysqlite).
3.x uses a different database file format than 2.8, and the APIs are 
sufficiently different that they'd need different Python bindings.  If 
you don't need to use database files produced by tools that are still 
using 2.x and there are Python bindings available for 3.x, then it makes 
sense to use 3.x.

[sqlite] Reset values if insert fails

[Keith Herold]

I have a question about how to reset values if an insert fails.  Using
the following tables, is there any relatively straightforward way to
set LastNameIDInserted to -1 if the UNIQUE constraint on tblNames
fails?

CREATE TABLE tblNames
(
   NameID INTEGER PRIMARY KEY,
   First CHAR(10),
   Last CHAR(10),
   CONSTRAINT uniquepairs UNIQUE
(   
First,
Last
)
);

CREATE TABLE tblNamesLastInserted
(
   LastNameIDInserted INTEGER
) ;

CREATE TRIGGER updateLastInsertedID AFTER INSERT ON tblNames
BEGIN
   UPDATE tblNamesLastInserted
  SET LastNameIDInserted = new.NameID ;
 END ;

INSERT INTO tblNames (First, Last) VALUES ('Keith', 'Herold') ;
-- LastNameIDInserted = 1
INSERT INTO tblNames (First, Last) VALUES('Keith', 'Herold');
-- LastNameIDInserted = -1

I know about last_insert_rowid, but that doesn't tell you whether an
insert succeeded or not, just what the last rowid was.  I tried
modifying the trigger to use a case statement that set
LastNameIDInserted = -1 if change_count() was 0, but that didn't seem
to do anything (and I suppose it's because the trigger never fires,
because the insert fails, and there is no 'AFTER' in this case?).

I suppose a  'BEFORE' trigger could do this for me, but I was
wondering if there was some other way?

I am trying to move some work currently done in C++ into the database,
so that I can rely on the database to do the heavy-lifting.

-- Keith
**
- Ever notice how 'big' isn't, compared to 'small'?

- I'm not a professional; I just get paid to do this.

- Rules for programming:
   1.  Get it working, right?
   2.  Get it working right.

- Things I've learned about multithreaded programming:

123...   PPArrvooottieedcc ttm  ueelvvteeirrtyyhtt
rhheiianndgge  dwi hnpi rctohhg eri aslm omscitanalgt 
 iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd  'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
**

[sqlite] implementing ALTER TABLE .. ADD COLUMN

[Vladimir Vukicevic]

It seems that after ALTER TABLE .. RENAME TO, ADD COLUMN is probably
the most common, probably followed by DROP COLUMN.  I'd like to take a
crack at adding support for ADD COLUMN.  It seems that I can do this
via VDBE -- it seems similar to doing an UPDATE across the entire
table, reading the existing data, adding a null for the new column,
and putting the newly-extended record back into place; with a
schema/num columns update coming either before or after this
operation.

Would this be the right approach to take?

Thanks,
- Vlad

Re: [sqlite] TYPEOF in triggers

[Witold Czarnecki]

2.8.15
I just tested it on 3.0.8 and - you are right - it works.
Is there any reason to use 2.8 instead of 3.0? I use SQLite via python 
(pysqlite).

Best regards,
Witold
- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: 
Sent: Friday, February 11, 2005 12:16 AM
Subject: Re: [sqlite] TYPEOF in triggers


On Thu, 2005-02-10 at 23:59 +0100, Witold Czarnecki wrote:
sqlite> CREATE TABLE test(a NUMERIC);
sqlite> CREATE TRIGGER test1 BEFORE INSERT ON test FOR EACH ROW BEGIN
   ...>   SELECT CASE WHEN (TYPEOF(NEW.a) != 'numeric') THEN RAISE(ABORT, 
'Error!') END;
   ...> END;
sqlite> INSERT INTO test VALUES('aaa');
sqlite> SELECT TYPEOF(a) FROM test;
numeric
sqlite>

Why this trigger doesn't fire?
I tired the above (using copy/paste) and the trigger
did fire.  What version of SQLite are you running?
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] TYPEOF in triggers

[D. Richard Hipp]

On Thu, 2005-02-10 at 23:59 +0100, Witold Czarnecki wrote:
> sqlite> CREATE TABLE test(a NUMERIC);
> sqlite> CREATE TRIGGER test1 BEFORE INSERT ON test FOR EACH ROW BEGIN
>...>   SELECT CASE WHEN (TYPEOF(NEW.a) != 'numeric') THEN RAISE(ABORT, 
> 'Error!') END;
>...> END;
> sqlite> INSERT INTO test VALUES('aaa');
> sqlite> SELECT TYPEOF(a) FROM test;
> numeric
> sqlite>
> 
> Why this trigger doesn't fire?
> 

I tired the above (using copy/paste) and the trigger
did fire.  What version of SQLite are you running?
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] TYPEOF in triggers

[Witold Czarnecki]

Can you help?:

sqlite> CREATE TABLE test(a NUMERIC);
sqlite> CREATE TRIGGER test1 BEFORE INSERT ON test FOR EACH ROW BEGIN
   ...>   SELECT CASE WHEN (TYPEOF(NEW.a) != 'numeric') THEN RAISE(ABORT, 
'Error!') END;
   ...> END;
sqlite> INSERT INTO test VALUES('aaa');
sqlite> SELECT TYPEOF(a) FROM test;
numeric
sqlite>

Why this trigger doesn't fire?

Best regards,
Witold

Re: [sqlite] exact copy of an existing Table

[Chris Schirlinger]

> SQLiters:
> what would be the most efficient method for creating an exact copy of an
> existing table with all the columns and data of the existing table?

You could just do this from commandline in SQL like so:

CREATE TABLE newTable AS SELECT * FROM oldTable;

That makes a new tables, same schema with the same data (in the same 
database)

No indexes however

[sqlite] exact copy of an existing Table

[Uriel_Carrasquilla]

SQLiters:
what would be the most efficient method for creating an exact copy of an
existing table with all the columns and data of the existing table?

Regards,

Uriel_Carrasquilla

Re: [sqlite] would someone check my SQL..

[Witold Czarnecki]

You may try:
SELECT NULL FROM sqlite_master WHERE tbl_name = 'table';
- Original Message - 
From: "Asko Kauppi" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 10, 2005 5:08 PM
Subject: Re: [sqlite] would someone check my SQL..


Is it true that in SQLite one has no way to check (at CREATE TABLE 
statement itself) whether the table already is there?  I got this from 
a person more accustomed to MySQL:

  >in mysql, the CREATE TABLE command has a 'IF NOT EXISTS' option, but 
as i
  >said, it's a non-standard extension.
  >
  >you could do a "SELECT * FROM table LIMIT 1" and check only if 
there's an
  >error to verify the existence of the table.

Sure, I can do that but.. shouldn't there be a less elaborate way?
-ak

Re: [sqlite] would someone check my SQL..

[Asko Kauppi]

Is it true that in SQLite one has no way to check (at CREATE TABLE 
statement itself) whether the table already is there?  I got this from 
a person more accustomed to MySQL:

  >in mysql, the CREATE TABLE command has a 'IF NOT EXISTS' option, but 
as i
  >said, it's a non-standard extension.
  >
  >you could do a "SELECT * FROM table LIMIT 1" and check only if 
there's an
  >error to verify the existence of the table.

Sure, I can do that but.. shouldn't there be a less elaborate way?
-ak

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[Chris Schirlinger]

> standard VCL stuff so there's a little overhead. But surely not as dramatic as
> suggested. But there's one condition. Use transactions! Without them, you'll
> never get a good performance.

Perhaps not dramatic no, but I was getting to the point where I am 
setting DB page sizes to match OS Cluster sizes to get any drop of 
speed out

Maybe what I was saying could have been better phrased, I wasn't 
attacking components directly, but really pointing out that for EVERY 
drop in speed, you could do well looking at sending well phrased SQL 
directly to the DB and accessing the returned pointers yourself or 
with a basic wrapper

Components are definitely with their uses, though I am biased for the 
argument that if you don't REALLY know how it works at least at a 
reasonable low level, you may not be getting the most you can

And transactions BOY HARDY there is something, never thought to 
mention them though since I felt it was kinda a "given" :)

And as you said, its fun :)

[sqlite] Does the list in tokenize.c represent the entire set of keywords?

[mswarm]

Being a compulsive reinventor of wheels, I've made my SQL code editor
smart enough to highlight the offending line of bad SQL--not a major leap
in technology, but fun nonetheless. Now I'd like to color-code the SQL.

I found this list of keywords in tokenize.c. A little search-and-replace--
replacing TK_ with ', etc.--and it makes a nice set for validating keywords.


TK_ABORT,  TK_AFTER,  TK_ALL,TK_AND,TK_AS, 
TK_ASC,TK_ATTACH, TK_BEFORE, TK_BEGIN,  TK_BETWEEN,
TK_BY, TK_CASCADE,TK_CASE,   TK_CHECK,  TK_COLLATE,
TK_COMMIT, TK_CONFLICT,   TK_CONSTRAINT, TK_CREATE, TK_JOIN_KW,
TK_DATABASE,   TK_DEFAULT,TK_DEFERRABLE, TK_DEFERRED,   TK_DELETE, 
TK_DESC,   TK_DETACH, TK_DISTINCT,   TK_DROP,   TK_EACH,   
TK_ELSE,   TK_END,TK_EXCEPT, TK_EXCLUSIVE,  TK_EXPLAIN,
TK_FAIL,   TK_FOR,TK_FOREIGN,TK_FROM,   TK_JOIN_KW,
TK_GLOB,   TK_GROUP,  TK_HAVING, TK_IGNORE, TK_IMMEDIATE,  
TK_IN, TK_INDEX,  TK_INITIALLY,  TK_JOIN_KW,TK_INSERT, 
TK_INSTEAD,TK_INTERSECT,  TK_INTO,   TK_IS, TK_ISNULL, 
TK_JOIN,   TK_KEY,TK_JOIN_KW,TK_LIKE,   TK_LIMIT,  
TK_MATCH,  TK_JOIN_KW,TK_NOT,TK_NOTNULL,TK_NULL,   
TK_OF, TK_OFFSET, TK_ON, TK_OR, TK_ORDER,  
TK_JOIN_KW,TK_PRAGMA, TK_PRIMARY,TK_RAISE,  TK_REFERENCES, 
TK_REPLACE,TK_RESTRICT,   TK_JOIN_KW,TK_ROLLBACK,   TK_ROW,
TK_SELECT, TK_SET,TK_STATEMENT,  TK_TABLE,  TK_TEMP,   
TK_TEMP,   TK_THEN,   TK_TRANSACTION,TK_TRIGGER,TK_UNION,  
TK_UNIQUE, TK_UPDATE, TK_USING,  TK_VACUUM, TK_VALUES, 
TK_VIEW,   TK_WHEN,   TK_WHERE,  
 
Two questions: Am I safe in assuming this represents the entire set of keywords 
in SQLite?

And why does TK_JOIN_KW, appear in there so many times?

For my purposes, I may eventually run a bunch of SQL code through a word-
frequency counter, and arrange these in frequency-of-use order--or maybe 
put them in a sorted, searchable list--as I suspect that this would be the 
choke point for code editors on really slow machines. 

Are we having fun yet?

Nathan Hawking

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[albert drent]

Quoting Chris Schirlinger <[EMAIL PROTECTED]>:

> > I wouldn't sell the BDE-style components short, as they may be useful
> > for some, if well-written and well-documented, but I like simple.

The (Borland) BDE is a lot of overhead conceirning database access, but it's a
transparent layer allowing to develop applications without knowing which
database is used (within the supported types of course). If that's important:
use the bde (Although SQLite is not supported as far as I know).

There are VCL components around accessing SQLite directly.These components work
directly on the API set of SQLite avoiding a lot of overhead the BDE has.
However they do allow you to access the database is the standard well
documented Borland way. Don't ever use 'locate' on a sql type database. This is
always by far slower than accessing the sql way. Locate requires a resultset you
can walk through up and down, this cannot be done in simple sql.

There are two types of wrappers, one that is not data-aware (like the Tim
Anderson components) and data-aware, like our's. In both cases they use
standard VCL stuff so there's a little overhead. But surely not as dramatic as
suggested. But there's one condition. Use transactions! Without them, you'll
never get a good performance.

Of course anybody must use what he/she likes most. Directly accessing the API is
technical, you need to know lot's about pointers, you need to convert the
datatypes etc. etc. It's more work, but it's more fun too. That's why I created
the VCL part and I can understand why others like to access the API directly
too. But on the speed part...

albert