[sqlite] Change value on a trigger
In sqlite it's possible to change the value inserted or updated inside a trigger? Something like this: CREATE TABLE foo (a); BEFORE INSERT ON foo BEGIN NEW.a=1; END; Thanks.
[sqlite] CREATE TABLE on conflict ignore?
Hi SQLiters! Can someone tell me the proper syntax for CREATE TABLE and if theres a conflict ignore it. I've tried several versions of the CREATE TABLE query. All I get is an error that the table already exists. Thats what I'm trying to avoid. I've tried using BEGIN ON CONFLICT IGNORE and COMMIT in order to get rid of it. But no luck. Would be grateful for any hints. Cheers Jimmy. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Change value on a trigger
There is an entire section in the SQL language specification dedicated to triggers, if you need further help just come and ask. http://www.sqlite.org/lang.html Regards, PeterP. -Original Message- From: António Vieira (DSI) [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:29 AM To: '[EMAIL PROTECTED]' Subject: [sqlite] Change value on a trigger In sqlite it's possible to change the value inserted or updated inside a trigger? Something like this: CREATE TABLE foo (a); BEFORE INSERT ON foo BEGIN NEW.a=1; END; Thanks. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] CREATE TABLE on conflict ignore?
As far as I can tell there is no conflict cause on the CREATE TABLE statement, however all the table information is stored in a table called "sqlite_master." SELECT count(*) FROM sqlite_master WHERE name = "my_table_name"; That should return "1"; So, if you're a SQL guru you might be able to work that in to a query. I tried to figure out an elegant way to do this but I figured that I would just have to check before in my application. If you think about it logically you would be telling it to IGNORE the conflict anway, and create the table regardless. So wouldn't it be safe to assume that you could just drop the table and recreate it anyway? Aha-! But then you're faced with yet another conflict cause if the table does not infact exist. Regards, PeterP. -Original Message- From: Jimmy Lantz [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 12:10 PM To: [EMAIL PROTECTED] Subject: [sqlite] CREATE TABLE on conflict ignore? Hi SQLiters! Can someone tell me the proper syntax for CREATE TABLE and if theres a conflict ignore it. I've tried several versions of the CREATE TABLE query. All I get is an error that the table already exists. Thats what I'm trying to avoid. I've tried using BEGIN ON CONFLICT IGNORE and COMMIT in order to get rid of it. But no luck. Would be grateful for any hints. Cheers Jimmy. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Difference between Transaction Journal and Checkpoint Journal
Thursday, March 25, 2004, 1:33:03 AM, Rohit wrote: > I was going thru the pager source code ( pager.c ). The comments include > references to "Journal", "Transaction Journal", "Checkpoint Journal". What > is the difference between the three? Am I missing something? There is only one journal file. "Journal" refers to this file or the act of writing to it. The "Transaction Journal" has one (before modification image) copy of each page modified by the transaction. This is used for rollback. The "Checkpoint Journal" has one (before modification image) copy of each page modified by a single sql statement that may be rolled back (e.g., by a trigger) within the transaction. It is a limited form of nested transaction. There is only one checkpoint active at a time, and it is within the one and only active transaction. e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] ENUM semantics
I have a pre-existing sql file that I'd like to import
into SQLite. (eg, via the .read command of the sqlite
command line utility).
this sql file creates a bunch of tables.
Used in many places are column definitions like this:
replay enum('none','public','private') default NULL,
SQLite apprarently does not support the enum keyword?
(It's not listed in the 'Datatypes in SQLite' man page).
or does it?
The error message I get for statments like the above is:
SQL error: near "'none'": syntax error
Hmmm...if ENUM was not supported, I would have expected
the error to be 'SQL error: near "enum"...'
I've tried using double-quotes instead of single-quotes...
same error.
Anyway, how *does* one define the ENUM semantic in SQLite?
Do I have to write a user-defined function? (ugh!)
Tia,
- rich
--
rich coco
[EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Fw: [sqlite] TWS for Windows - link
> It works !!! on Win2000 !!! > > WishList: I would certainly like to see an example database application > using SQLite. (maybe its there, but I couldnt find any obvious code). I > want to scrap IIS & Sambar & Apache efforts for something far more simple, > like TWS. > > > - Original Message - > From: "borivoj" <[EMAIL PROTECTED]> > Subject: [sqlite] TWS for Windows - link > > > You can download windows version on > > http://unicast.org/archives/000508.html, follow the link "installer" > > While it is still not a single exe file, it works, and it is so easy to > > install. I have installed it on a Compact Flash drive to move it easily > > between different machines, one on work and one at home. It is PCMCIA CF > > but I suppose it will work on USB CF as well. Actually quite interesting > > possibility to move web server, database and application that way. I am > > not aware of anything so simple, powerful and useful. > > Borivoj > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] ENUM semantics
At 11:32 AM -0500 3/25/04, rich coco wrote:
Used in many places are column definitions like this:
replay enum('none','public','private') default NULL,
SQLite apprarently does not support the enum keyword?
(It's not listed in the 'Datatypes in SQLite' man page).
or does it?
Hmmm...if ENUM was not supported, I would have expected
the error to be 'SQL error: near "enum"...'
As far as I know, ENUM is not part of standard SQL, but is a
vendor-added extension such as by MySQL.
I believe that the SQL-1999 standard addresses the same problem using
a broader concept called 'domains', which is where you can declare a
pseudo-data-type that starts with a normal one but is more
restrictive. For example, you would declare a domain called 'sex'
which is defined as a character data type that can only contain the
values 'M' and 'F'. That part of domain declarations can look sort
of like 'check' constraints that some databases like Oracle support.
Now, considering that SQLite is typeless and doesn't actually
restrict what you enter into any field, you will have to either use a
foreign key constraint on a new table to restrict the field values to
the 3 you want, or you will have to enforce your constraint in the
application. Either way, you simply declare a varchar field in
SQLite.
-- Darren Duncan
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] ENUM semantics
At 11:22 PM -0500 3/25/04, Rich Coco wrote: darren - thanks for the response. is this an use for a User Defined Function? Or do I misunderstand the intended uses of UDFs. tx, - rich My understanding is that User Defined Functions are something completely separate. User defined functions allow you to do any arbitrary amount of work inside the database engine. While you *could* use UDFs to implement an ENUM, by using one in an INSERT trigger, UDFs are intended for a much broader range of uses. Come to think of it, perhaps you may want to use a trigger on your table rather than a foreign key, although I would have expected a foreign key constraint to be much faster by its simplicity. This is probably going to be a case of 'just do what works best for you'. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Search results
Hi Everyone, This is a tiny bit off subject, so sorry. I have a search page on my web site which when used will search the database for results. I want to be able to rank the results in best match first order. In the database I will have three columns where I will be searching for matches , my question is what type of method do people/systems use to rate a match in the database? An example of a typical search would be . They enter the work Backup. I was thinking of selecting all rows which have backup in the three columns and then counting how many times the word appears in each column. This then would be the ranking of the result. I.e. Result rows Row 1 10 times Row 2 33 times Row 3 23 times These results would appear like this Row 2 Row 3 Row 1 Does anyone have any other idea's on ranking results? If you do please email them to me regards Greg O
Re: [sqlite] ENUM semantics
At 12:05 AM -0500 3/26/04, Rich Coco wrote: I looked into triggers, but the SQLite implementation did not seem to have the flexibility I needed to implement ENUM. btw - i am not a DB guy at all (tho maybe that will change soon!), so my 'DB/SQL intuitio' is still embryonic. By your suggestion to use a 'foreign key', do you mean create, for example, a 1-column table whose entries are the valid ENUM values and associate it to other tables containg column(s) that need the ENUM constraint? tx, - rich Yes, that is what I'm talking about. An ENUM is conceptually the same as making said 1-column table and using it in foreign-key constraints. Practically, speaking, though, using the table is almost always better, because you can add or remove items from the allowed list easily without changing any of your database's schema. The only time that ENUMs are actually useful is if you are quite certain that the options to choose from will never change; it should also be a relatively small list, the M/F being an example. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Search results
Greg, "Greg Obleshchuk" <[EMAIL PROTECTED]> 26/03/2004 03:29 PM To: <[EMAIL PROTECTED]> cc: Subject:[sqlite] Search results > In the database I will have three columns where I will be searching for matches , my question is what type of method do people/systems use to rate a match in the database? It's difficult to rate exact matches, because all search results returned exactly what was requested. As such, the only way to find a match that is better than other matches is to have the user refine their search. Rated search results are more useful when matches are inexact. A word that is spelled nearly the same as the one you searched for. A result that only contained four out of your five queried words. They can be rated with respect to each other because they match the query to a better or worse extent. For exact matches you really have to come up with another method of deciding how relevant the results were. Google uses information on how popular a web-page is to determine which pages are most likely to be relevant when users search. If you could get feedback on how useful particular search results were, you could make sure the best results were returned for later searches. If you could predict how useful results are likely to be when you enter the records, you could provide a baseline ranking to start from. Anyway, tricky subject :) No quick answers ;) Benjamin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
