Re: [sqlite] command history
Puneet, Puneet Kishor <[EMAIL PROTECTED]> 03/05/2004 05:01 AM To: SQLite <[EMAIL PROTECTED]> cc: Subject:[sqlite] command history > Is there a way to enable some kind of command history in the SQLite > shell? You know, press the up arrow to get the previous command... I > think it is called READLINE support, no? If you're still having trouble, perhaps you could try starting sqlite in a different way. Instead of this: $ sqlite my.db > SELECT ...; > CREATE ...; try: $ sqlite my.db 'SELECT ...;' $ sqlite my.db 'CREATE ...;' This method doesn't allow you to run transactions across multiple lines, but does give the advantage of immediately conforming to the way your shell does its command history :) I like to do things this way with ksh and vi editing keys, myself. Benjamin. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
> -Original Message- > From: Andrew Piskorski [mailto:[EMAIL PROTECTED] > Sent: Sunday, May 02, 2004 12:09 PM > To: Puneet Kishor > Cc: D. Richard Hipp; SQLite > Subject: Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] > correct syntax > for CASE...) > > > On Sun, May 02, 2004 at 01:59:34PM -0500, Puneet Kishor wrote: > > > >Probably you mean the concatenate operator, which in > > >SQL is ||, not &. & is not an SQL operator as far as > > > drats... concat is '+' in Javascript, '.' in Perl, '&' in > Access and > > SQL Server, '||' in SQlite, and, well, 'CONCAT' in Oracle. > Why can't > > the entire world just speak Hindi ;-). > > It is '||' in Oracle, and I believe PostgreSQL as well. 'CONCAT' may > also work there, but if so I've never seen it used. > CONCAT is used in a few DBMS's as it allows one to use a function call instead of an (infix?) operator. E.g. select concat('Hello', ' world') from mytable Obviously many nested uses of concat() can look rather hairy... As a side note SQL Server also uses "+" (I think "&" was just added to keep Access users happy :-p). Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Is the DLL kept in RAM constantly?
One way to insure the highest performance would be to boost the executable's "priority" using the system management tools or service launch params. Won't go into the details. It has been awhile since I wrote that code and it is not immediately at hand. > -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Monday, May 03, 2004 3:31 PM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Is the DLL kept in RAM constantly? > > > At 8:06 PM +0200 5/3/04, Frederic Faure wrote: > >Hi, > >Since the performance of SQLite are so great, my dad and I were > >wondering if SQLite used a Windows API to tell it not to unload > >SQLite from RAM even if no program was still calling it, so that the > >next time a program loaded it, it would still be up and running in > >RAM, with data also loaded? > >Thank you > >Fred. > > I don't think that is or should be necessary. Whether or not a > shared library remains in RAM should be the domain of the operating > system itself. It should be something that the OS just does, perhaps > as a performance feature, and nothing that applications would have to > code for. > > More generally speaking, as I understand it Unixy operating systems > tend to cache executables in memory anyway, as well as share such non > non-changing blocks between multiple running copies of the same > program, rather than load it each time. > > I wouldn't be surprised if the newest Windows didn't do it too, but I > don't know for sure. > > -- Darren Duncan > > - > 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] Is the DLL kept in RAM constantly?
Op maandag 3 mei 2004 22:31, schreef Darren Duncan: > At 8:06 PM +0200 5/3/04, Frederic Faure wrote: > >Hi, > >Since the performance of SQLite are so great, my dad and I were > >wondering if SQLite used a Windows API to tell it not to unload > >SQLite from RAM even if no program was still calling it, so that the > >next time a program loaded it, it would still be up and running in > >RAM, with data also loaded? There is a file, an interface-file in pascal (Delphi), it is called sqlite.pas, somewhere on the internet. I use it a lot. It is very handy, it encapsulated all conversions to PChar, etc, returns strings and stringlist, easy to handle and important: It loads sqlite.dll in the initialisation part, and keeps it loaded, so as long as you do not destroy the sqlite-object in this file, the dll stays loaded. I guess this kind of things are also possible in other development-environments. Bert Verhees - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Is the DLL kept in RAM constantly?
At 8:06 PM +0200 5/3/04, Frederic Faure wrote: Hi, Since the performance of SQLite are so great, my dad and I were wondering if SQLite used a Windows API to tell it not to unload SQLite from RAM even if no program was still calling it, so that the next time a program loaded it, it would still be up and running in RAM, with data also loaded? Thank you Fred. I don't think that is or should be necessary. Whether or not a shared library remains in RAM should be the domain of the operating system itself. It should be something that the OS just does, perhaps as a performance feature, and nothing that applications would have to code for. More generally speaking, as I understand it Unixy operating systems tend to cache executables in memory anyway, as well as share such non non-changing blocks between multiple running copies of the same program, rather than load it each time. I wouldn't be surprised if the newest Windows didn't do it too, but I don't know for sure. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Is the DLL kept in RAM constantly?
Hi, Since the performance of SQLite are so great, my dad and I were wondering if SQLite used a Windows API to tell it not to unload SQLite from RAM even if no program was still calling it, so that the next time a program loaded it, it would still be up and running in RAM, with data also loaded? Thank you Fred. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
On May 2, 2004, at 6:52 PM, Kurt Welgehausen wrote: ... handicapped by not being able to use parens in a FROM clause. It's not that you can't use parens anywhere in a FROM clause; you just can't have the entire table-list enclosed in parens. You can still do something like select * from (t1 left join t2 on ...) join t3 on ... Thanks Kurt, for explaining this patiently. I am looking at my SQL statement... .. FROM (contacts AS c LEFT JOIN contacts_contacttypes AS cct ON c.contact_id = cct.contact_id) LEFT JOIN cities AS ci ON c.city_id = ci.city_id) LEFT JOIN contacttypes AS ct ON cct.contacttype_id = ct.contacttype_id) LEFT JOIN orgs AS o ON c.org_id = o.org_id) LEFT JOIN orgtypes AS ot ON o.orgtype_id = ot.orgtype_id) LEFT JOIN orgsubtypes AS ost ON o.orgsubtype_id = ost.orgsubtype_id As far as I can see, I don't have the entire table list in parens... it is not different from what you are suggesting, and yet, it does not work. SQLite croaks saying error at (. Am I seeing things differently? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] command history
On May 3, 2004, at 3:48 AM, eno wrote: Puneet Kishor wrote: Actually I am on Mac OS X 10.3. I have no idea what I need to do to enable READLINE. I just downloaded the source and did the ./configure, make, make install dance and got no READLINE. It must be somewhere on my system because the behavior I want is very much a part of tcsh. Assuming you have readline-devel installed on your system (watch out for a file "readline.h" somewhere under (for example) /usr/include or /usr/local/include) you might try to edit the Makefile generated from ./configure changing some lines: # Compiler options needed for programs that use the readline() library. # READLINE_FLAGS = -DHAVE_READLINE=1 -I/usr/include/readline # The library that programs using readline() must link against. # LIBREADLINE = -lreadline If you don't have readline devel on your system you might have to install that beforehand, of course. Thanks eno, that was just what I needed. I have updated the SQLite wiki on this subject appropriately. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Autoincrement of primary key
The problem that i have made something like : CREATE TABLE t1( a INTEGER(4) not null unique PRIMARY KEY, b INTEGER); That's why it doesn't accept the NULL value for me. But now it's okay. Thanks Hatem - Original Message - From: "Peter Pistorius" <[EMAIL PROTECTED]> To: "Dynamix" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, May 03, 2004 1:36 PM Subject: RE: [sqlite] Autoincrement of primary key > > SQLITE FAQ: > > > (1) How do I create an AUTOINCREMENT field. > > Short answer: A column declared INTEGER PRIMARY KEY will autoincrement. > > Here is the long answer: Beginning with version SQLite 2.3.4, If you > declare a column of a table to be INTEGER PRIMARY KEY, then whenever you > insert a NULL into that column of the table, the NULL is automatically > converted into an integer which is one greater than the largest value of > that column over all other rows in the table, or 1 if the table is > empty. For example, suppose you have a table like this: > > CREATE TABLE t1( > a INTEGER PRIMARY KEY, > b INTEGER > ); > > With this table, the statement > > INSERT INTO t1 VALUES(NULL,123); > > is logically equivalent to saying: > > INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); > > For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into an > INTEGER PRIMARY KEY column, the NULL will be changed to a unique > integer, but it will a semi-random integer. Unique keys generated this > way will not be sequential. For SQLite version 2.3.4 and beyond, the > unique keys will be sequential until the largest key reaches a value of > 2147483647. That is the largest 32-bit signed integer and cannot be > incremented, so subsequent insert attempts will revert to the > semi-random key generation algorithm of SQLite version 2.3.3 and > earlier. > > Beginning with version 2.2.3, there is a new API function named > sqlite_last_insert_rowid() which will return the integer key for the > most recent insert operation. See the API documentation for details. > > > -Original Message- > From: Dynamix [mailto:[EMAIL PROTECTED] > Sent: Monday, May 03, 2004 12:28 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] Autoincrement of primary key > > Greetings, > > is it possible de have an autoincrement feature ?? i want to have > something like : > > insert into table names (NULL, 'name1'); insert into table names (NULL, > 'name2'); insert into table names (NULL, 'name3'); > > and then id will be 1,2 and 3 ? > > Any help please > > Hatem > > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Autoincrement of primary key
SQLITE FAQ: (1) How do I create an AUTOINCREMENT field. Short answer: A column declared INTEGER PRIMARY KEY will autoincrement. Here is the long answer: Beginning with version SQLite 2.3.4, If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. For example, suppose you have a table like this: CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER ); With this table, the statement INSERT INTO t1 VALUES(NULL,123); is logically equivalent to saying: INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into an INTEGER PRIMARY KEY column, the NULL will be changed to a unique integer, but it will a semi-random integer. Unique keys generated this way will not be sequential. For SQLite version 2.3.4 and beyond, the unique keys will be sequential until the largest key reaches a value of 2147483647. That is the largest 32-bit signed integer and cannot be incremented, so subsequent insert attempts will revert to the semi-random key generation algorithm of SQLite version 2.3.3 and earlier. Beginning with version 2.2.3, there is a new API function named sqlite_last_insert_rowid() which will return the integer key for the most recent insert operation. See the API documentation for details. -Original Message- From: Dynamix [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 12:28 PM To: [EMAIL PROTECTED] Subject: [sqlite] Autoincrement of primary key Greetings, is it possible de have an autoincrement feature ?? i want to have something like : insert into table names (NULL, 'name1'); insert into table names (NULL, 'name2'); insert into table names (NULL, 'name3'); and then id will be 1,2 and 3 ? Any help please Hatem - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Autoincrement of primary key
Greetings, is it possible de have an autoincrement feature ?? i want to have something like : insert into table names (NULL, 'name1'); insert into table names (NULL, 'name2'); insert into table names (NULL, 'name3'); and then id will be 1,2 and 3 ? Any help please Hatem
Re: [sqlite] command history
Puneet Kishor wrote: Actually I am on Mac OS X 10.3. I have no idea what I need to do to enable READLINE. I just downloaded the source and did the ./configure, make, make install dance and got no READLINE. It must be somewhere on my system because the behavior I want is very much a part of tcsh. Assuming you have readline-devel installed on your system (watch out for a file "readline.h" somewhere under (for example) /usr/include or /usr/local/include) you might try to edit the Makefile generated from ./configure changing some lines: # Compiler options needed for programs that use the readline() library. # READLINE_FLAGS = -DHAVE_READLINE=1 -I/usr/include/readline # The library that programs using readline() must link against. # LIBREADLINE = -lreadline If you don't have readline devel on your system you might have to install that beforehand, of course. /eno Note: There might be even an option to ./configure enabling or disabling readline support on that stage. However, I don't know it, but think for a one-time compilation above approach should work ok. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]