RE: [sqlite] atomic db replacement
Lothar Märkle <[EMAIL PROTECTED]> writes: >>> Assuming you have a cgi-like application with many processes >>> that just looks up a row, displays and then exits. You can >>> simple use the rename call to atomically replace the db file >>> (see man 2 rename for bordercases) with another and without >>> locking. >>> >>> lothar >> >> but this will lose any update currently begin written by any >> of the active cgi processes - even if they are in the middle >> of a transaction? > > yes, this will only work with a read-only db, e.g. all your data > is pre-calculated once a day to a second db file, and then replace > the primary read-only db with it. > > But with read-only, one should avoid transactions at all. There > will be the risk of a -journal that doesn't correspond to the > db-file. I think this will break if rolled back? > > lothar I need to copy a 'live' master database file and overwrite a read only file for pseudo replication. How do I ensure the database is flushed and acquire an exclusive lock on the master database? Will something like the code below do it? sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, error); copyFiles(); sqlite3_exec(db, "COMMIT", NULL, NULL, error); Also are you sure it is completely safe to replace a read only database while reads are currently being done? Any help much appreciated. lars
Re: [sqlite] atomic db replacement
On Sat, 12 Mar 2005, Andrew Piskorski wrote: On Sat, Mar 12, 2005 at 10:03:25AM -0700, Ara.T.Howard wrote: does anyone have a strategy for doing massive updates to a db and atomicly replacing it in a multi-process situation? Why would you want to do that? SQLite properly supports transactions, so aren't you better of just using those? What scenario are you working with here? Do you have a large number of readers that MUST not block while a massive/slow update is done tot he database, or something like that? precisely - active web site doing both reads and writes: long running update to perform. obviously not too much (but maybe something) will be happening at night. my plan was lock db take copy note mtime release lock perform updates lock db if mtime has been updated retry else perform atomic replacement end perhaps this is simply impossible with sqlite? -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===
RE: [sqlite] Proposal: limit the number of columns in a table to2000.
I'm in the airline game. 379 columns is the widest table that I can find in our production DB2 sub-systems - a highly denormalised table as I'm sure you can imagine. Perhaps someone like FedEx or UPS may have requirements to go real wide for their warehousing apps. Nothing I can find in this big shop goes anywhere near the proposed limits. rayB -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Friday, 18-Mar-2005 02:19 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Proposal: limit the number of columns in a table to2000. On Thu, 2005-03-17 at 09:09 -0600, Fred Williams wrote: > BTW, Most of the "enterprise" database engines I have worked with have > had either published or "stealth" column count limits. All those that I > remember were below 2000. But I must admit I have not worked with any > of the current releases of the "big boys." > I used google to dig up the column count limits on some common database engines: DB2 255 Oracle 1000 SQL Server 1024 PostgreSQL 1600 MySQL 3398 Informix 32767 -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 4:27 PM, Kurt Welgehausen wrote:
Well, I might as well use the substr() function, then ...
Yes, unless you think %m is more expressive than 6,2 or
you want to extract more than one component.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
2 2005-03-17T17:22:30
sqlite> select strftime('On %m/%d at %H:%M', dd) Arrival_time from
...> (select substr(d,1,10)||' '||substr(d,-8,8) dd from datetest) ;
Arrival_time
-
On 03/17 at 16:21
On 03/17 at 17:22
I do think that it's more expressive than 6,2, but not than
"substr(d,1,10)||' '||substr(d,-8,8)".
Regards,
David
[sqlite] It there a roadmap?
Hi, I was wondering there is a roadmap of SQLite since many users are concerning what will the upcoming SQLite look like. :) _ 与世界各地的朋友进行交流，免费下载 MSN Messenger: http://messenger.msn.com/cn
Re: [sqlite] Should Unary + Cast to a Number?
> Well, I might as well use the substr() function, then ...
Yes, unless you think %m is more expressive than 6,2 or
you want to extract more than one component.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
2 2005-03-17T17:22:30
sqlite> select strftime('On %m/%d at %H:%M', dd) Arrival_time from
...> (select substr(d,1,10)||' '||substr(d,-8,8) dd from datetest) ;
Arrival_time
-
On 03/17 at 16:21
On 03/17 at 17:22
Regards
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:59 PM, Kurt Welgehausen wrote:
Sorry, I misunderstood the context.
No problem.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
sqlite> select strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
...> from datetest;
strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
---
03
sqlite> select abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
...> from datetest;
abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
Well, I might as well use the substr() function, then:
sqlite> select abs(substr('2005-03-17T16:21:30', 6, 2));
abs(substr('2005-03-17T16:21:30', 6, 2))
3
Thanks for the implicit hint about using abs() for casting '03' to 3,
though.
Regards,
David
Re: [sqlite] Should Unary + Cast to a Number?
Sorry, I misunderstood the context.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
sqlite> select strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
...> from datetest;
strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
---
03
sqlite> select abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
...> from datetest;
abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
3
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:21 PM, Matt Sergeant wrote: You can force the binding type in DBD::SQLite - see the DBI docs (grep for ":sql_types"). True, but in our application, that would require a lot more book-keeping than we'd like. Adding "+0" seems to do the cast we need to keep up with how DBD::SQLite casts '03'. Thanks, David
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:24 PM, Kurt Welgehausen wrote:
Yes, I know it supports it without the "T" ...
sqlite> select strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime');
strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime')
-
2005-03-17T16:21:30
No:
sqlite> select strftime("%m", '2005-03-17T16:21:30');
strftime("%m", '2005-03-17T16:21:30')
-
You see, I'm storing records with the T in the date string, but
SQLite's strftime() can't parse the date string with the T.
Regards,
David
Re: [sqlite] Should Unary + Cast to a Number?
> Yes, I know it supports it without the "T" ...
sqlite> select strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime');
strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime')
-
2005-03-17T16:21:30
Re: [sqlite] Should Unary + Cast to a Number?
On 17 Mar 2005, at 15:13, David Wheeler wrote:
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for?
Actually the code that was cast was "substr(a, 6, 2)", which was
evaluating to "03", and I wanted it to be just 3, because when I bind
"03" in DBD::SQLite, it changes it to 3.
You can force the binding type in DBD::SQLite - see the DBI docs (grep
for ":sql_types").
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] Is this possible in SQLite?
John O'Neill wrote: Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. John, No you can't combine SELECT and UPDATE directly. However, the insert statements I gave do change the id from its value of 1 in table a or b to a new value of 2 in table acopy or bcopy. INSERT INTO acopy SELECT 2, data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 2, data FROM b WHERE id = 1; Rather than selecting the entire row that matches the old id, I select the new id and the data field from the row in the old table, and then insert them into the new table. This works for your simple example, but it may not be general enough depending upon how the values of the new id are generated. Dennis Cote
RE: [sqlite] Is this possible in SQLite?
Unfortunately, as the syntax on the website points out, this is not supported. As you pointed out previously, it is doable with a few extra commands. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: John O'Neill [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 3:48 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Is this possible in SQLite? Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. I guess this question is can I combine an UPDATE...SET with an INSERT...SELECT command? Thanks, John -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 4:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this possible in SQLite? John O'Neill wrote: >Hello all, > >I have a fairly simple DB with two tables. I'm trying to combine a >SELECT and UPDATE command, if it is possible: > >CREATE TABLE a (id PRIMARY KEY, data INT); >CREATE TABLE b (id INT, data INT); > >INSERT INTO a VALUES( 1, 100 ); >INSERT INTO b VALUES( 1, 101 ); >INSERT INTO b VALUES( 1, 102 ); >INSERT INTO b VALUES( 1, 103 ); >... > >And at some point in the future, two new tables (possibly in a >different database) are created: > >CREATE TABLE acopy (id PRIMARY KEY, data INT); >CREATE TABLE bcopy (id INT, data INT ); > >Is there a way to do the following: > >INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X >); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); > >Instead of doing the following 4 commands or SELECTing a and b into >TEMP tables: > >UPDATE a SET id = X WHERE id = 1; >UPDATE b SET id = X WHERE id = 1; >INSERT INTO acopy SELECT * FROM a; >INSERT INTO bcopy SELECT * FROM b; > >Thanks, >John > > > > > John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
Re: [sqlite] Single quotes are causing misery
On Thu, 2005-03-17 at 12:50 -0500, Peter Jay Salzman wrote:
> Without using sqlite_escape_string, single quotes cause "SQL Logic or
> missing database" errors. So I'm forced to use that function on variables
> set via a form.
>
> But then to avoid the "backslash in the data" problem, I need to use
> stripslashes on the variables I'm about to write to the database.
>
> But just in case a user has magic_quotes_gpc set off, I need to test that
> function and then decide whether to use stripslashes() or not.
>
> Problem solved, but the solution is kind of, well, "icky".
>
In the TCL bindings for SQLite, no quoting of variable contents
is needed. You say say something like this:
db eval {INSERT INTO table1 VALUES($var1,$var2)}
The TCL bindings see the $var1 and $var2 in the SQL code, reach
in to TCL and extract the values of corresponding variables, then
use sqlite3_bind_... to pass those values directly to SQLite
without the need for any escaping or quoting. The technique is
also very fast since it avoids unnecessary copying of the string
text. The whole approach works very very well.
The same idea would, in theory, work with PHP. I suggested as much
to the PHP developers, saying I thought it would make the interface
much simpler. But the idea was rejected.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Should Unary + Cast to a Number?
David Wheeler said: > On Mar 17, 2005, at 1:23 PM, Kurt Welgehausen wrote: > >>> strftime doesn't support the ISO-8601 format ... >> >> I does if you give it the correct format string. > > Yes, I know it supports it without the "T", but ISO-8601 mandates the > presence of the T. strftime(buffer, size, "%Y-%m-%sT%H:%M:%S", now); SOAP uses the same format for date/time information, and I've been neck deep in SOAP for the last few weeks. Clay Dowling -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 1:23 PM, Kurt Welgehausen wrote: strftime doesn't support the ISO-8601 format ... I does if you give it the correct format string. Yes, I know it supports it without the "T", but ISO-8601 mandates the presence of the T. Regards, David
RE: [sqlite] Is this possible in SQLite?
Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. I guess this question is can I combine an UPDATE...SET with an INSERT...SELECT command? Thanks, John -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 4:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this possible in SQLite? John O'Neill wrote: >Hello all, > >I have a fairly simple DB with two tables. I'm trying to combine a >SELECT and UPDATE command, if it is possible: > >CREATE TABLE a (id PRIMARY KEY, data INT); >CREATE TABLE b (id INT, data INT); > >INSERT INTO a VALUES( 1, 100 ); >INSERT INTO b VALUES( 1, 101 ); >INSERT INTO b VALUES( 1, 102 ); >INSERT INTO b VALUES( 1, 103 ); >... > >And at some point in the future, two new tables (possibly in a >different database) are created: > >CREATE TABLE acopy (id PRIMARY KEY, data INT); >CREATE TABLE bcopy (id INT, data INT ); > >Is there a way to do the following: > >INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X >); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); > >Instead of doing the following 4 commands or SELECTing a and b into >TEMP tables: > >UPDATE a SET id = X WHERE id = 1; >UPDATE b SET id = X WHERE id = 1; >INSERT INTO acopy SELECT * FROM a; >INSERT INTO bcopy SELECT * FROM b; > >Thanks, >John > > > > > John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
Re: [sqlite] Is this possible in SQLite?
John O'Neill wrote: Hello all, I have a fairly simple DB with two tables. I'm trying to combine a SELECT and UPDATE command, if it is possible: CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); ... And at some point in the future, two new tables (possibly in a different database) are created: CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); Is there a way to do the following: INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X ); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); Instead of doing the following 4 commands or SELECTing a and b into TEMP tables: UPDATE a SET id = X WHERE id = 1; UPDATE b SET id = X WHERE id = 1; INSERT INTO acopy SELECT * FROM a; INSERT INTO bcopy SELECT * FROM b; Thanks, John John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
Re: [sqlite] Is this possible in SQLite?
> Is there a way to do the following: > > INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X ); > INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); http://www.sqlite.org/lang_insert.html sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) | INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement Regards
Re: [sqlite] Should Unary + Cast to a Number?
> strftime doesn't support the ISO-8601 format ... I does if you give it the correct format string. Regards
Re: [sqlite] Single quotes are causing misery
On Thu 17 Mar 05, 4:10 PM, Reid Thompson <[EMAIL PROTECTED]> said: > > > I read that in a NSP book awhile ago (Linux Cookbook). It's > > faster than "%s/ //g". Very handy. But which problem does this > > address? :) > > > > Pete > > This is some PHP code on Linux. I suspect it was originally written > > > on a Microsoft operating system because when I edit the files, my > > > editor reports the textfiles as being "dos" (they contain > > > carriage-return and linefeeds at the end of each line). > > reid Oh, I was just telling what platform this was executing on. The problem was with quoting, not with what the end of line for textfiles is. I don't think that the internal format for the sourcecode textfile is the problem here. The problem was with unescaped single quotes confusing sqlite. Sorry for the confusion! Peter -- Save Star Trek Enterprise from extinction: http://www.saveenterprise.com GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
RE: [sqlite] Single quotes are causing misery
> I read that in a NSP book awhile ago (Linux Cookbook). It's > faster than "%s/ //g". Very handy. But which problem does this > address? :) > > Pete This is some PHP code on Linux. I suspect it was originally written > > on a Microsoft operating system because when I edit the files, my > > editor reports the textfiles as being "dos" (they contain > > carriage-return and linefeeds at the end of each line). reid
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 11:40 AM, Clark Christensen wrote:
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for?
Actually the code that was cast was "substr(a, 6, 2)", which was
evaluating to "03", and I wanted it to be just 3, because when I bind
"03" in DBD::SQLite, it changes it to 3.
If you were looking for 20050322, SQLite can do that for
you, too. Have a look at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
I wasn't looking for 20050322, but I'm thrilled to have the pointers to
SQLite's date/time functions. I should be able to change "substr(a, 6,
2)" to "strftime("%m", a)" and then not have to cast.
...hrm, nope. "strftime("%m", a)" still produces "03" instead of just
"3". So I have to do "strftime("%m", a)+0". And strftime doesn't
support the ISO-8601 format "-MM-DDTHH:MM:SS" (note the "T") :-(
But aside from that, I'd still like to see unary + be able to cast '03'
to 3.
Thanks,
David
[sqlite] Is this possible in SQLite?
Hello all, I have a fairly simple DB with two tables. I'm trying to combine a SELECT and UPDATE command, if it is possible: CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); ... And at some point in the future, two new tables (possibly in a different database) are created: CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); Is there a way to do the following: INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X ); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); Instead of doing the following 4 commands or SELECTing a and b into TEMP tables: UPDATE a SET id = X WHERE id = 1; UPDATE b SET id = X WHERE id = 1; INSERT INTO acopy SELECT * FROM a; INSERT INTO bcopy SELECT * FROM b; Thanks, John
Re: [sqlite] Should Unary + Cast to a Number?
--- David Wheeler <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> Given the below script (using DBD::SQLite 1.08, which
> uses SQLite
> 3.1.3), the output is just:
>
>+0 Cast: 2005-03-22T00:00:00
>
> I'm wondering, however, if unary + shouldn't also be able
> to cast an
> expression to a number...shouldn't it?
>
> Thanks,
>
> David
>
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> use constant SQLITE_FILE => shift;
>
> my $dbh = DBI->connect_cached(
> 'dbi:SQLite:dbname=' . SQLITE_FILE, '', '', {
> RaiseError => 1,
> PrintError => 0,
> }
> );
>
> END {
> $dbh->disconnect;
> $dbh->rollback;
> }
>
> $dbh->begin_work;
> $dbh->do("CREATE TABLE foo (a TEXT)");
> $dbh->do("INSERT INTO foo
> VALUES('2005-03-22T00:00:00')");
> my $sth = $dbh->prepare("SELECT * FROM foo WHERE
> (substr(a, 6, 2) =
> ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "No Cast: $row->[0]\n";
> }
>
> $sth = $dbh->prepare("SELECT * FROM foo WHERE (+substr(a,
> 6, 2) = ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "Unary Cast: $row->[0]\n";
> }
>
> $sth = $dbh->prepare("SELECT * FROM foo WHERE (substr(a,
> 6, 2)+0 = ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "+0 Cast: $row->[0]\n";
> }
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for? SQLite can give you an offset from the Unix
epoch from a properly formatted date-time string, but I
don't think you could get that using "select * from ...".
If you were looking for 20050322, SQLite can do that for
you, too. Have a look at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
-Clark
Re: [sqlite] Single quotes are causing misery
On Thu 17 Mar 05, 2:26 PM, Reid Thompson <[EMAIL PROTECTED]> said:
> Peter Jay Salzman wrote:
> > Hi Mike,
> >
> > To be perfectly honest, other than being a Microsoft thing, I
> > don't really know what .NET is. Pretty pathetic, huh? :)
> >
> > This is some PHP code on Linux. I suspect it was originally
> > written on a Microsoft operating system because when I edit
> > the files, my editor reports the textfiles as being "dos"
> > (they contain carriage-return and linefeeds at the end of each line).
> >
> > I hear you about the []; I *wish* I could use them. Proper
> > quoting inside of PHP is very painful:
> >
> >
> >$query = "INSERT INTO $database_table
> > (id, day, month, date, year, category, title, body,
> > showpref) VALUES (null,
> > '" . sqlite_escape_string($_POST['the_day']) . "',
> > '" . sqlite_escape_string($_POST['the_month']). "',
> > '" . sqlite_escape_string($_POST['the_date']) . "',
> > (snip)
> >
> > The stuff that looks quoted (the middle section) is actually
> > the stuff outside the language quotes, but inside the quoted
> > quotes. Gruesome.
> >
> > But if I don't use sqlite_escape_string, single quotes cause
> > a "SQL logic or missing database" error.
> >
> > But then, if I use sqlite_escape_string, I have to test
> > get_magic_quotes_gpc and use stripslashes, and Eugene
> > recommended. Hard to believe there isn't a better way of doing this!
> >
> > Pete
> >
> >
> >
> > On Thu 17 Mar 05, 5:59 AM,
> > [EMAIL PROTECTED]
> > <[EMAIL PROTECTED]> said:
> >> Are you using the SQLite .NET provider? Just curious, anyway, SQLite
> >> also supports using [ ] instead of " " and believe me it's a good
> >> thing, using " " as delimiters is a poor choice considering this
> >> conflicts with almost all languages when it comes to string
> >> concatenation. In fact, I recommend use [ ] over " " all of the time,
> >> however, the SQLite .NET managed driver has issues with the [ ]
> >> delimiter style.
> >>
> >>
> >>> I've nearly completed converting Wheatblog to sqlite. It's been
> >>> quite a learning experience! I've come across a problem I haven't
> >>> been able to figure out, though.
> >>>
> >>> Whenever I made a blog post that had a forward quote character (')
> >>> in either the title or the body of the post, I'd get an error.
> >>>
> >>> After a little Googling, I changed my query to:
> >>>
> >>>
> >>> $query = "INSERT INTO $database_table
> >>> (id, day, month, date, year, category, title, body,
> >>> showpref) VALUES (null, '" .
> >>> sqlite_escape_string($_POST['the_day']) . "', '" .
> >>> sqlite_escape_string($_POST['the_month']). "', '" .
> >>> sqlite_escape_string($_POST['the_date']) . "', '" .
> >>> sqlite_escape_string($_POST['the_year']) . "', '" .
> >>> sqlite_escape_string($_POST['the_category']) . "', '" .
> >>> sqlite_escape_string($_POST['the_title']). "', '" .
> >>> sqlite_escape_string($_POST['the_body']) . "', '" .
> >>> sqlite_escape_string($_POST['the_showpref']) . "')";
> >>>
> >>> DB_query($query, $db);
> >>>
> >>> and the definition of DB_query is:
> >>>
> >>>
> >>>function DB_query($cmd, $db)
> >>>{
> >>> $retval = sqlite_query($db, "$cmd")
> >>> or die('Query Error: ' .
> >>> sqlite_error_string(sqlite_last_error($db)));
> >>>
> >>> return $retval;
> >>>}
> >>>
> >>> This works in the sense that forward quotes no longer generate an
> >>> error. However, whenever I print out a blog post, the forward
> >>> quotes are all escaped. So if I post:
> >>>
> >>>This contains a ' character.
> >>>
> >>> The post, when printed looks like:
> >>>
> >>>This contains a \' character.
> >>>
> >>> What's the proper way to ensure that ' characters are properly
> >>> quoted but don't show up in the output?
>
> dos2unix "filename" will remove the trailing carriage returns
>
> reid
I read that in a NSP book awhile ago (Linux Cookbook). It's faster than
"%s/
//g". Very handy. But which problem does this address? :)
Pete
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
RE: [sqlite] Single quotes are causing misery
Peter Jay Salzman wrote:
> Hi Mike,
>
> To be perfectly honest, other than being a Microsoft thing, I
> don't really know what .NET is. Pretty pathetic, huh? :)
>
> This is some PHP code on Linux. I suspect it was originally
> written on a Microsoft operating system because when I edit
> the files, my editor reports the textfiles as being "dos"
> (they contain carriage-return and linefeeds at the end of each line).
>
> I hear you about the []; I *wish* I could use them. Proper
> quoting inside of PHP is very painful:
>
>
>$query = "INSERT INTO $database_table
> (id, day, month, date, year, category, title, body,
> showpref) VALUES (null,
> '" . sqlite_escape_string($_POST['the_day']) . "',
> '" . sqlite_escape_string($_POST['the_month']). "',
> '" . sqlite_escape_string($_POST['the_date']) . "',
> (snip)
>
> The stuff that looks quoted (the middle section) is actually
> the stuff outside the language quotes, but inside the quoted
> quotes. Gruesome.
>
> But if I don't use sqlite_escape_string, single quotes cause
> a "SQL logic or missing database" error.
>
> But then, if I use sqlite_escape_string, I have to test
> get_magic_quotes_gpc and use stripslashes, and Eugene
> recommended. Hard to believe there isn't a better way of doing this!
>
> Pete
>
>
>
> On Thu 17 Mar 05, 5:59 AM,
> [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> said:
>> Are you using the SQLite .NET provider? Just curious, anyway, SQLite
>> also supports using [ ] instead of " " and believe me it's a good
>> thing, using " " as delimiters is a poor choice considering this
>> conflicts with almost all languages when it comes to string
>> concatenation. In fact, I recommend use [ ] over " " all of the time,
>> however, the SQLite .NET managed driver has issues with the [ ]
>> delimiter style.
>>
>>
>>> I've nearly completed converting Wheatblog to sqlite. It's been
>>> quite a learning experience! I've come across a problem I haven't
>>> been able to figure out, though.
>>>
>>> Whenever I made a blog post that had a forward quote character (')
>>> in either the title or the body of the post, I'd get an error.
>>>
>>> After a little Googling, I changed my query to:
>>>
>>>
>>> $query = "INSERT INTO $database_table
>>> (id, day, month, date, year, category, title, body,
>>> showpref) VALUES (null, '" .
>>> sqlite_escape_string($_POST['the_day']) . "', '" .
>>> sqlite_escape_string($_POST['the_month']). "', '" .
>>> sqlite_escape_string($_POST['the_date']) . "', '" .
>>> sqlite_escape_string($_POST['the_year']) . "', '" .
>>> sqlite_escape_string($_POST['the_category']) . "', '" .
>>> sqlite_escape_string($_POST['the_title']). "', '" .
>>> sqlite_escape_string($_POST['the_body']) . "', '" .
>>> sqlite_escape_string($_POST['the_showpref']) . "')";
>>>
>>> DB_query($query, $db);
>>>
>>> and the definition of DB_query is:
>>>
>>>
>>>function DB_query($cmd, $db)
>>>{
>>> $retval = sqlite_query($db, "$cmd")
>>> or die('Query Error: ' .
>>> sqlite_error_string(sqlite_last_error($db)));
>>>
>>> return $retval;
>>>}
>>>
>>> This works in the sense that forward quotes no longer generate an
>>> error. However, whenever I print out a blog post, the forward
>>> quotes are all escaped. So if I post:
>>>
>>>This contains a ' character.
>>>
>>> The post, when printed looks like:
>>>
>>>This contains a \' character.
>>>
>>> What's the proper way to ensure that ' characters are properly
>>> quoted but don't show up in the output?
dos2unix "filename" will remove the trailing carriage returns
reid
Re: [sqlite] Single quotes are causing misery
Well the [ ] aren't a .NET thing, SQLite supports them, using [ ] are a standard supported SQLite feature and they make you're life way, way, easier, see: http://www.sqlite.org/lang_keywords.html [keyword] A keyword enclosed in square brackets is always understood as an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility. While this might not be "standard SQL" using double quotes " as the special character is a very poor choice.
Re: [sqlite] Version 3.1.6
On Thu, 17 Mar 2005 06:56:04 -0500 "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > On Thu, 2005-03-17 at 12:39 +0100, Gerald Dachs wrote: > > Why do you not use my patch? Do you not agree that this is a bug? > > > > No I do not. The arch.png image is no longer used in the > documentation. It was replaced by arch2.gif some time ago. > > > 'make doc' on linux is still broken, attached patch for 3.1.3 still > > works. > > > > The makefile works fine when I run it without your patch. Sorry, noticed only now that bug was at least in 3.1.3, 3.1.4, but has gone in 3.1.5. Because I didn't get answers on my posts, I assumed wrongly that, if the patch applies, then the bug is still there. Sorry again, will be quiet in future. Gerald
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
[EMAIL PROTECTED] wrote: >Darn, I was going to have 500,000 columns in my table. The, each column >would be named like this: Record1, Record2, Record3, and so on up to >Record500. Each column type was going to be varchar and I was going to >store my first record in xml format in my Record1 column of the first row, >and so on down the line. That way I could stored all 500,000 of my records >in a 1 row table, What do you guys think? Please wait April 1st for such jokes... Bertrand Mansion Mamasam
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 17, 2005 10:44 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Proposal: limit the number of columns > in a table to 2000. > > Darn, I was going to have 500,000 columns in my table. The, > each column > would be named like this: Record1, Record2, Record3, and so on up to > Record500. Each column type was going to be varchar and I > was going to > store my first record in xml format in my Record1 column of > the first row, > and so on down the line. That way I could stored all 500,000 > of my records > in a 1 row table, What do you guys think? Definitely needs an index. On each column. In all seriousness, 2000 columns is plenty. Does that ceiling apply to joins as well, such that no more than 2000 columns are returned from a select clause? Or does it only apply per-table? Robert
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
It seems a multidimentional or hash file. Am i right? There are different ways to fisically store the data with gain of simplicity. Cláudio Leopoldino --- [EMAIL PROTECTED] wrote: > Darn, I was going to have 500,000 columns in my > table. The, each column > would be named like this: Record1, Record2, Record3, > and so on up to > Record500. Each column type was going to be > varchar and I was going to > store my first record in xml format in my Record1 > column of the first row, > and so on down the line. That way I could stored all > 500,000 of my records > in a 1 row table, What do you guys think? > > = Sabedoria: "O FRACO PERECERÁ." Sugestão : CAI [www.cai.org.br] = __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Re: [sqlite] Single quotes are causing misery
Hi Eugene,
Yes, this worked great. I just find it hard to believe that it's all
necessary.
Without using sqlite_escape_string, single quotes cause "SQL Logic or
missing database" errors. So I'm forced to use that function on variables
set via a form.
But then to avoid the "backslash in the data" problem, I need to use
stripslashes on the variables I'm about to write to the database.
But just in case a user has magic_quotes_gpc set off, I need to test that
function and then decide whether to use stripslashes() or not.
Problem solved, but the solution is kind of, well, "icky". I love the idea
of a RDBMS that doesn't require a daemon. And I love PHP. They're both so
convenient. But the difficulty of programming with the two taken together
is more than the sum of the "difficultness" of the two individually. :(
Thanks!
Pete
On Thu 17 Mar 05, 1:58 PM, Eugene Wee <[EMAIL PROTECTED]> said:
> Hi,
>
> I think the reason is that sqlite_escape_string() doubles single quotes
> to escape them.
> However, you have magic_quotes_gpc set to 1 in php.ini
> As such, incoming variables are escaped using backslashes.
>
> A solution is to use stripslashes() on the incoming variables if
> get_magic_quotes_gpc() returns 1, since you cant change magic_quotes_gpc
> at runtime.
> Alternatively, you can alter php.ini, but that's usually not practical.
>
> Eugene Wee
>
> Peter Jay Salzman wrote:
> >I've nearly completed converting Wheatblog to sqlite. It's been quite a
> >learning experience! I've come across a problem I haven't been able to
> >figure out, though.
> >
> >Whenever I made a blog post that had a forward quote character (') in
> >either
> >the title or the body of the post, I'd get an error.
> >
> >After a little Googling, I changed my query to:
> >
> >
> > $query = "INSERT INTO $database_table
> > (id, day, month, date, year, category, title, body, showpref)
> > VALUES (null,
> > '" . sqlite_escape_string($_POST['the_day']) . "',
> > '" . sqlite_escape_string($_POST['the_month']). "',
> > '" . sqlite_escape_string($_POST['the_date']) . "',
> > '" . sqlite_escape_string($_POST['the_year']) . "',
> > '" . sqlite_escape_string($_POST['the_category']) . "',
> > '" . sqlite_escape_string($_POST['the_title']). "',
> > '" . sqlite_escape_string($_POST['the_body']) . "',
> > '" . sqlite_escape_string($_POST['the_showpref']) . "')";
> >
> > DB_query($query, $db);
> >
> >and the definition of DB_query is:
> >
> >
> > function DB_query($cmd, $db)
> > {
> > $retval = sqlite_query($db, "$cmd")
> > or die('Query Error: ' .
> > sqlite_error_string(sqlite_last_error($db)));
> >
> > return $retval;
> > }
> >
> >This works in the sense that forward quotes no longer generate an error.
> >However, whenever I print out a blog post, the forward quotes are all
> >escaped. So if I post:
> >
> > This contains a ' character.
> >
> >The post, when printed looks like:
> >
> > This contains a \' character.
> >
> >What's the proper way to ensure that ' characters are properly quoted but
> >don't show up in the output?
> >
> >Thanks!
> >Pete
> >
>
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
Darn, I was going to have 500,000 columns in my table. The, each column would be named like this: Record1, Record2, Record3, and so on up to Record500. Each column type was going to be varchar and I was going to store my first record in xml format in my Record1 column of the first row, and so on down the line. That way I could stored all 500,000 of my records in a 1 row table, What do you guys think?
Re: [sqlite] Single quotes are causing misery
Hi Mike,
To be perfectly honest, other than being a Microsoft thing, I don't really
know what .NET is. Pretty pathetic, huh? :)
This is some PHP code on Linux. I suspect it was originally written on a
Microsoft operating system because when I edit the files, my editor reports
the textfiles as being "dos" (they contain carriage-return and linefeeds at
the end of each line).
I hear you about the []; I *wish* I could use them. Proper quoting inside
of PHP is very painful:
$query = "INSERT INTO $database_table
(id, day, month, date, year, category, title, body, showpref)
VALUES (null,
'" . sqlite_escape_string($_POST['the_day']) . "',
'" . sqlite_escape_string($_POST['the_month']). "',
'" . sqlite_escape_string($_POST['the_date']) . "',
(snip)
The stuff that looks quoted (the middle section) is actually the stuff
outside the language quotes, but inside the quoted quotes. Gruesome.
But if I don't use sqlite_escape_string, single quotes cause a "SQL logic or
missing database" error.
But then, if I use sqlite_escape_string, I have to test get_magic_quotes_gpc
and use stripslashes, and Eugene recommended. Hard to believe there isn't a
better way of doing this!
Pete
On Thu 17 Mar 05, 5:59 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> said:
> Are you using the SQLite .NET provider? Just curious, anyway, SQLite also
> supports using [ ] instead of " " and believe me it's a good thing, using
> " " as delimiters is a poor choice considering this conflicts with almost
> all languages when it comes to string concatenation. In fact, I recommend
> use [ ] over " " all of the time, however, the SQLite .NET managed driver
> has issues with the [ ] delimiter style.
>
>
> > I've nearly completed converting Wheatblog to sqlite. It's been quite a
> > learning experience! I've come across a problem I haven't been able to
> > figure out, though.
> >
> > Whenever I made a blog post that had a forward quote character (') in
> > either
> > the title or the body of the post, I'd get an error.
> >
> > After a little Googling, I changed my query to:
> >
> >
> > $query = "INSERT INTO $database_table
> > (id, day, month, date, year, category, title, body, showpref)
> > VALUES (null,
> > '" . sqlite_escape_string($_POST['the_day']) . "',
> > '" . sqlite_escape_string($_POST['the_month']). "',
> > '" . sqlite_escape_string($_POST['the_date']) . "',
> > '" . sqlite_escape_string($_POST['the_year']) . "',
> > '" . sqlite_escape_string($_POST['the_category']) . "',
> > '" . sqlite_escape_string($_POST['the_title']). "',
> > '" . sqlite_escape_string($_POST['the_body']) . "',
> > '" . sqlite_escape_string($_POST['the_showpref']) . "')";
> >
> > DB_query($query, $db);
> >
> > and the definition of DB_query is:
> >
> >
> >function DB_query($cmd, $db)
> >{
> > $retval = sqlite_query($db, "$cmd")
> > or die('Query Error: ' .
> > sqlite_error_string(sqlite_last_error($db)));
> >
> > return $retval;
> >}
> >
> > This works in the sense that forward quotes no longer generate an error.
> > However, whenever I print out a blog post, the forward quotes are all
> > escaped. So if I post:
> >
> >This contains a ' character.
> >
> > The post, when printed looks like:
> >
> >This contains a \' character.
> >
> > What's the proper way to ensure that ' characters are properly quoted but
> > don't show up in the output?
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
> > > There is code in SQLite that has to deal with the general > > > case of tables with millions or billions of columns. That > > > code can be simplified (and made faster) if we know that > > > the maximum number of columns is some reasonable limit, > > > such as 2000. > > > > Oh. Go for it! The concensus seems to be 1820 is more than > > enough. If anyone uses even a significant fraction of that we have > > list members who will assault them with book bludgeons! > > > Might I suggest "Databases For Dummies." Much more relevant and > significantly heavier. LOL! I have significantly failed in my assault aliteration! I should have said "members who will commit book bludgeon battery". Or maybe some default disuasion by dashing them with "Databases for Dummies"? __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
Re: [sqlite] Version 3.1.6
Hi, Am Donnerstag, 17. März 2005 15:45 schrieb [EMAIL PROTECTED]: > I agree. [snip] I disagree. make doc works fine w/o the patch. Regrads Uwe
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
-Original Message- From: Jay [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 10:32 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposal: limit the number of columns in a table to 2000. > > A stupid question: > > > > Why introduce more code, one more thing to test, > > and possibly bugs? Is there a problem that needs fixing? > > > > There is code in SQLite that has to deal with the general > case of tables with millions or billions of columns. That > code can be simplified (and made faster) if we know that > the maximum number of columns is some reasonable limit, > such as 2000. Oh. Go for it! The concensus seems to be 1820 is more than enough. If anyone uses even a significant fraction of that we have list members who will assault them with book bludgeons! Might I suggest "Databases For Dummies." Much more relevant and significantly heavier. Fred
[sqlite] Compilation Warning
Dear All,
I am compiling the new 3.1.6 version using Devcpp (versione 4.9.9.2 using
MINGW with GCC 3.4.2). Compiling the source code
I got the following warning
gcc.exe -c vdbeaux.c -o objects/vdbeaux.o -I"D:/Dev-Cpp/include" -DNO_TCL
-DNDEBUG=-1 -ansi -fexpensive-optimizations -O3 -march=pentium
vdbeaux.c: In function `sqlite3VdbeChangeP3':
vdbeaux.c:276: warning: passing arg 1 of `sqlite3FreeX' discards qualifiers
from pointer target type
The involved line is
void sqlite3VdbeChangeP3(Vdbe *p, int addr, const char *zP3, int n){
Op *pOp;
assert( p->magic==VDBE_MAGIC_INIT );
if( p==0 || p->aOp==0 ){
if( n==P3_DYNAMIC || n==P3_KEYINFO_HANDOFF ){
sqliteFree(zP3); < HERE!
}
return;
}
I didn't get any warning with the 3.1.5, may be is not a problem but I am
flagging the warning anyway.
I am using the archive
http://www.sqlite.org/sqlite-source-3_1_6.zip
ready for win32 compilation
Regards
Massimo
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
> > A stupid question: > > > > Why introduce more code, one more thing to test, > > and possibly bugs? Is there a problem that needs fixing? > > > > There is code in SQLite that has to deal with the general > case of tables with millions or billions of columns. That > code can be simplified (and made faster) if we know that > the maximum number of columns is some reasonable limit, > such as 2000. Oh. Go for it! The concensus seems to be 1820 is more than enough. If anyone uses even a significant fraction of that we have list members who will assault them with book bludgeons! __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
There is an difference about technical limit and practical. I think that a good number is 2048 or 1024. Are more "binary". Adding information: Using DBase, i found that the limit was 128 columns. Cláudio Leopoldino --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > On Thu, 2005-03-17 at 09:09 -0600, Fred Williams > wrote: > > BTW, Most of the "enterprise" database engines I > have worked with have > > had either published or "stealth" column count > limits. All those that I > > remember were below 2000. But I must admit I have > not worked with any > > of the current releases of the "big boys." > > > > I used google to dig up the column count limits on > some > common database engines: > > DB2 255 > Oracle 1000 > SQL Server 1024 > PostgreSQL 1600 > MySQL 3398 > Informix 32767 > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Yahoo! Mail - Com 250MB de espaço. Abra sua conta! http://mail.yahoo.com.br/
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
I agree, it's like creating one object (C++, Java, C#) in an application with 40,000 methods, you might be using an object oriented language but you aren't using OO techniques. I would hone it down before SQLite developers exploit this and you're stuck supporting it, it could come back to haunt you later. > Off topic, but people creating tables with that number of columns > should really think about normalizing their data structures. > > -- Gerhard >
ODP: [sqlite] Proposal: limit the number of columns in a table to2000.
> I used google to dig up the column count limits on some > common database engines: > > DB2 255 > Oracle 1000 > SQL Server 1024 > PostgreSQL 1600 > MySQL 3398 > Informix 32767 > Sybase ASE 12.5 - 1024 or 254, depending on whether columns are fixed or variable length and locking scheme. Regards, Jarek
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
On Thu, 2005-03-17 at 08:39 -0700, David Fletcher wrote: > > "DRH" == D Richard Hipp <[EMAIL PROTECTED]> writes: > > DRH> On Thu, 2005-03-17 at 06:32 -0500, D. Richard Hipp wrote: > >> I am proposing to limit the value of K to something like 2000. > >> > > DRH> Further study shows that in order to implement the > DRH> optimizations I have in mind, I'll need to limit the > DRH> number of columns in a single table to 1820. > > Out of curiosity, what code is affected? And, how did you arrive at > 1820? > The code in question is VDBE opcodes MakeRecord and Operand found in the vdbe.c source file. These opcodes create and decode rows of data. At the beginning of each row is a variable-length integer which is the length of the "header". The header contains type information for the columns in that row. If I restrict the number of columns to 1820, then the size of the header can never exceed 16383. That value can always be encoded with a length integer of 2 bytes or less. Knowing that this variable-length integer is 2 bytes or less simplifies the encoding and decoding. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
Off topic, but people creating tables with that number of columns should really think about normalizing their data structures. -- Gerhard signature.asc Description: Digital signature
Re: [sqlite] python script example
On Thu, Mar 17, 2005 at 09:47:16AM +0800, cross wind wrote: > Thanks Gehard. Finally, I am seeing the pattern on how the scripts > ought to be run. In the pysqlite2 alpha *source* releases, there's a script included to run tests. Because it's just that single file, you can download it here, directly from the Subversion repository: http://initd.org/svn/initd/pysqlite/trunk/scripts/test-pysqlite -- Gerhard signature.asc Description: Digital signature
Re: [sqlite] python script example
On Wed, Mar 16, 2005 at 06:45:23PM +0800, cross wind wrote:
> [...]
> ### When ran, I get the ff messages:
>
> C:/PYTHON23/pythonw.exe -u "Z:/devpy/test/sqlite.pyw"
> Traceback (most recent call last):
> File "Z:/devpy/test/sqlite.pyw", line 1, in ?
> import sqlite
> File "Z:\devpy\test\sqlite.pyw", line 3, in ?
> cx = sqlite.connect('xyz.db')
> AttributeError: 'module' object has no attribute 'connect'
Don't name your test script like an existing Python module, in this case
sqlite. Because then, your test script imports itself, and not the
sqlite module.
-- Gerhard
signature.asc
Description: Digital signature
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
> "DRH" == D Richard Hipp <[EMAIL PROTECTED]> writes: DRH> On Thu, 2005-03-17 at 06:32 -0500, D. Richard Hipp wrote: >> I am proposing to limit the value of K to something like 2000. >> DRH> Further study shows that in order to implement the DRH> optimizations I have in mind, I'll need to limit the DRH> number of columns in a single table to 1820. Out of curiosity, what code is affected? And, how did you arrive at 1820? Thanks. -- David Fletcher Tuscany Design Automation, Inc.
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
D. Richard Hipp said: > As currently implemented, there is no fixed limit to the number > of columns you can put in a table in SQLite. If the CREATE TABLE > statement will fit in memory, then SQLite will accept it. Call > the number of columns in a table K. I am proposing to limit the > value of K to something like 2000. I have a stated policy of beating anyone with a copy of Joe Celko's "SQL For Smarties" if they create tables with this many columns in a system I have to work on. Anything more than a couple of dozen columns will at least get them threatened. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
On Thu, 2005-03-17 at 06:32 -0500, D. Richard Hipp wrote: > I am proposing to limit the value of K to something like 2000. > Further study shows that in order to implement the optimizations I have in mind, I'll need to limit the number of columns in a single table to 1820. That is still more than 10 times larger than any table I have seen in the wild. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
> So who out there needs a value of K larger than 2000? > What is the largest K that anybody is using? Who would > object if I inserted a limit on K that was in the range > of 1000 or 2000? I have never, in SQLite or any other SQL DB product I've worked with, created or used a table with more than a couple of hundred fields. The largest table I currently work with has less than 100, and that's too many, mostly because of poor design.
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
On Thu, 2005-03-17 at 09:09 -0600, Fred Williams wrote: > BTW, Most of the "enterprise" database engines I have worked with have > had either published or "stealth" column count limits. All those that I > remember were below 2000. But I must admit I have not worked with any > of the current releases of the "big boys." > I used google to dig up the column count limits on some common database engines: DB2 255 Oracle 1000 SQL Server 1024 PostgreSQL 1600 MySQL 3398 Informix 32767 -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
On Thu, 2005-03-17 at 06:52 -0800, Jay wrote: > A stupid question: > > Why introduce more code, one more thing to test, > and possibly bugs? Is there a problem that needs fixing? > There is code in SQLite that has to deal with the general case of tables with millions or billions of columns. That code can be simplified (and made faster) if we know that the maximum number of columns is some reasonable limit, such as 2000.
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
I'd bet there is someone out there using more than 2000 columns. Either they probably won't admit it or will be the first to brag about it :-) I'd say if it relates to performance/footprint the smaller the column count the better as an upper limit. In over thirty years of consulting I've seen very few tables that even exceeded about 256. I don't think I've ever seen a table over a thousand columns wide. Most of those large "tables" were old ISAM files on Old Blue boxes created long before the days of good database design. The parallel table should either solve the problem, force them to redesign, or switch to a "fatter" database engine. BTW, Most of the "enterprise" database engines I have worked with have had either published or "stealth" column count limits. All those that I remember were below 2000. But I must admit I have not worked with any of the current releases of the "big boys." Fred -Original Message- From: Bert Verhees [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 6:38 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposal: limit the number of columns in a table to 2000. I cannot imagine ever needing more then 2000 columns in a table, if I would, I could always create a parallel table > > >>As currently implemented, there is no fixed limit to >>the number >>of columns you can put in a table in SQLite. If the >...
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
A stupid question: Why introduce more code, one more thing to test, and possibly bugs? Is there a problem that needs fixing? It certainly will not cause me any problems. --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > As currently implemented, there is no fixed limit to the number > of columns you can put in a table in SQLite. If the CREATE TABLE > statement will fit in memory, then SQLite will accept it. Call > the number of columns in a table K. I am proposing to limit the > value of K to something like 2000. > > Would this cause anyone any grief? > > Note that SQLite is optimized for a K that is small - a few dozen > at most. There are algorithms in the parser that run in time > O(K*K). These could be changed to O(K) but with K small the > constant of proportionality is such that it isn't worthwhile. > So, even though SQLite will work on a table with a million or > more columns, it is not a practical thing to do, in general. > > The largest value of K I have seen in the wild is in the > low 100s. I thought that I was testing with K values in > the thousands, but I just checked and I think the test > scripts only go as high as K=1000 in one place. > > The reason it would be good to limit K to about 2000 is > that if I do so there are some places where I can increase > the run-time performance some. It would also reduce > code complexity in a few spots. > > So who out there needs a value of K larger than 2000? > What is the largest K that anybody is using? Who would > object if I inserted a limit on K that was in the range > of 1000 or 2000? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Version 3.1.6
I agree. It is almost anoying that here we have a member contributing an easy to implement patch and we have to continue reapplying to to every new version. Regards, [EMAIL PROTECTED] NCCI Boca Raton, Florida 561.893.2415 greetings / avec mes meilleures salutations / Cordialmente mit freundlichen Grüßen / Med vänlig hälsning "Gerald Dachs" <[EMAIL PROTECTED]> To: "D. Richard Hipp" <[EMAIL PROTECTED]> cc: sqlite-users@sqlite.org 03/17/2005 06:39 Subject: Re: [sqlite] Version 3.1.6 AM Please respond to sqlite-users Why do you not use my patch? Do you not agree that this is a bug? 'make doc' on linux is still broken, attached patch for 3.1.3 still works. Gerald --- sqlite-3.1.3/Makefile.in.orig 2005-03-07 22:56:04.833954328 +0100 +++ sqlite-3.1.3/Makefile.in2005-03-07 22:56:41.189427456 +0100 @@ -451,6 +451,9 @@ arch.html: $(TOP)/www/arch.tcl tclsh $(TOP)/www/arch.tcl >arch.html +arch.png: $(TOP)/www/arch.png + cp $(TOP)/www/arch.png . + arch2.gif: $(TOP)/www/arch2.gif cp $(TOP)/www/arch2.gif .
Re: [sqlite] Single quotes are causing misery
I doubt that's the problem though, since the problem pertains to content
inserted, not the identifiers used.
My guess is that PHP is being used, and that my earlier suggestion
should fix it.
Eugene Wee
[EMAIL PROTECTED] wrote:
Are you using the SQLite .NET provider? Just curious, anyway, SQLite also
supports using [ ] instead of " " and believe me it's a good thing, using
" " as delimiters is a poor choice considering this conflicts with almost
all languages when it comes to string concatenation. In fact, I recommend
use [ ] over " " all of the time, however, the SQLite .NET managed driver
has issues with the [ ] delimiter style.
I've nearly completed converting Wheatblog to sqlite. It's been quite a
learning experience! I've come across a problem I haven't been able to
figure out, though.
Whenever I made a blog post that had a forward quote character (') in
either
the title or the body of the post, I'd get an error.
After a little Googling, I changed my query to:
$query = "INSERT INTO $database_table
(id, day, month, date, year, category, title, body, showpref)
VALUES (null,
'" . sqlite_escape_string($_POST['the_day']) . "',
'" . sqlite_escape_string($_POST['the_month']). "',
'" . sqlite_escape_string($_POST['the_date']) . "',
'" . sqlite_escape_string($_POST['the_year']) . "',
'" . sqlite_escape_string($_POST['the_category']) . "',
'" . sqlite_escape_string($_POST['the_title']). "',
'" . sqlite_escape_string($_POST['the_body']) . "',
'" . sqlite_escape_string($_POST['the_showpref']) . "')";
DB_query($query, $db);
and the definition of DB_query is:
function DB_query($cmd, $db)
{
$retval = sqlite_query($db, "$cmd")
or die('Query Error: ' .
sqlite_error_string(sqlite_last_error($db)));
return $retval;
}
This works in the sense that forward quotes no longer generate an error.
However, whenever I print out a blog post, the forward quotes are all
escaped. So if I post:
This contains a ' character.
The post, when printed looks like:
This contains a \' character.
What's the proper way to ensure that ' characters are properly quoted but
don't show up in the output?
Thanks!
Pete
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
Re: [sqlite] Single quotes are causing misery
> What's the proper way to ensure that ' characters are properly quoted but > don't show up in the output? Honestly, we use the SQLite .NET managed driver and pass all data in via parameters, therefore we have no escape issues and more importantly no SQL injection woes, if you're taking data right off the string and passing it into SQLite I could probably enter some text that wipe out all of your data, consider parameters, they solve a whole bunch of problems.
Re: [sqlite] Single quotes are causing misery
Are you using the SQLite .NET provider? Just curious, anyway, SQLite also
supports using [ ] instead of " " and believe me it's a good thing, using
" " as delimiters is a poor choice considering this conflicts with almost
all languages when it comes to string concatenation. In fact, I recommend
use [ ] over " " all of the time, however, the SQLite .NET managed driver
has issues with the [ ] delimiter style.
> I've nearly completed converting Wheatblog to sqlite. It's been quite a
> learning experience! I've come across a problem I haven't been able to
> figure out, though.
>
> Whenever I made a blog post that had a forward quote character (') in
> either
> the title or the body of the post, I'd get an error.
>
> After a little Googling, I changed my query to:
>
>
> $query = "INSERT INTO $database_table
> (id, day, month, date, year, category, title, body, showpref)
> VALUES (null,
> '" . sqlite_escape_string($_POST['the_day']) . "',
> '" . sqlite_escape_string($_POST['the_month']). "',
> '" . sqlite_escape_string($_POST['the_date']) . "',
> '" . sqlite_escape_string($_POST['the_year']) . "',
> '" . sqlite_escape_string($_POST['the_category']) . "',
> '" . sqlite_escape_string($_POST['the_title']). "',
> '" . sqlite_escape_string($_POST['the_body']) . "',
> '" . sqlite_escape_string($_POST['the_showpref']) . "')";
>
> DB_query($query, $db);
>
> and the definition of DB_query is:
>
>
>function DB_query($cmd, $db)
>{
> $retval = sqlite_query($db, "$cmd")
> or die('Query Error: ' .
> sqlite_error_string(sqlite_last_error($db)));
>
> return $retval;
>}
>
> This works in the sense that forward quotes no longer generate an error.
> However, whenever I print out a blog post, the forward quotes are all
> escaped. So if I post:
>
>This contains a ' character.
>
> The post, when printed looks like:
>
>This contains a \' character.
>
> What's the proper way to ensure that ' characters are properly quoted but
> don't show up in the output?
>
> Thanks!
> Pete
>
> --
> Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
>
> GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
>
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
as a suggestion to the list, this issue should be answered only by those that disagree, else we will get 2k messages saying why not..
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
How about a constant that can be changed at compile time? D. Richard Hipp wrote: As currently implemented, there is no fixed limit to the number of columns you can put in a table in SQLite. If the CREATE TABLE statement will fit in memory, then SQLite will accept it. Call the number of columns in a table K. I am proposing to limit the value of K to something like 2000. Would this cause anyone any grief? Note that SQLite is optimized for a K that is small - a few dozen at most. There are algorithms in the parser that run in time O(K*K). These could be changed to O(K) but with K small the constant of proportionality is such that it isn't worthwhile. So, even though SQLite will work on a table with a million or more columns, it is not a practical thing to do, in general. The largest value of K I have seen in the wild is in the low 100s. I thought that I was testing with K values in the thousands, but I just checked and I think the test scripts only go as high as K=1000 in one place. The reason it would be good to limit K to about 2000 is that if I do so there are some places where I can increase the run-time performance some. It would also reduce code complexity in a few spots. So who out there needs a value of K larger than 2000? What is the largest K that anybody is using? Who would object if I inserted a limit on K that was in the range of 1000 or 2000?
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
I cannot imagine ever needing more then 2000 columns in a table, if I would, I could always create a parallel table As currently implemented, there is no fixed limit to the number of columns you can put in a table in SQLite. If the CREATE TABLE statement will fit in memory, then SQLite will accept it. Call the number of columns in a table K. I am proposing to limit the value of K to something like 2000. Would this cause anyone any grief? Note that SQLite is optimized for a K that is small - a few dozen at most. There are algorithms in the parser that run in time O(K*K). These could be changed to O(K) but with K small the constant of proportionality is such that it isn't worthwhile. So, even though SQLite will work on a table with a million or more columns, it is not a practical thing to do, in general. The largest value of K I have seen in the wild is in the low 100s. I thought that I was testing with K values in the thousands, but I just checked and I think the test scripts only go as high as K=1000 in one place. The reason it would be good to limit K to about 2000 is that if I do so there are some places where I can increase the run-time performance some. It would also reduce code complexity in a few spots. So who out there needs a value of K larger than 2000? What is the largest K that anybody is using? Who would object if I inserted a limit on K that was in the range of 1000 or 2000? -- D. Richard Hipp <[EMAIL PROTECTED]> Yahoo! Mail - Com 250MB de espaço. Abra sua conta! http://mail.yahoo.com.br/
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
It's rarely a good idea to use binary numbers for limits such as that - you're exposing yourself to more corner case bugs. Interesting issue... I thing that we may use 2048 instead 2000. It´s an number more "binary". I think that it´s sufficient to 99.99% of the possible applications. That´s a good idea. The DBase accepted 128 collumns and that is my reference. I've need more than this. In a future development this value will be editable? Cláudio Leopoldino > As currently implemented, there is no fixed limit to > the number > of columns you can put in a table in SQLite. If the > CREATE TABLE > statement will fit in memory, then SQLite will > accept it. Call > the number of columns in a table K. I am proposing > to limit the > value of K to something like 2000. > > Would this cause anyone any grief? > > Note that SQLite is optimized for a K that is small > - a few dozen > at most. There are algorithms in the parser that > run in time > O(K*K). These could be changed to O(K) but with K > small the > constant of proportionality is such that it isn't > worthwhile. > So, even though SQLite will work on a table with a > million or > more columns, it is not a practical thing to do, in > general. > > The largest value of K I have seen in the wild is in > the > low 100s. I thought that I was testing with K > values in > the thousands, but I just checked and I think the > test > scripts only go as high as K=1000 in one place. > > The reason it would be good to limit K to about 2000 > is > that if I do so there are some places where I can > increase > the run-time performance some. It would also reduce > code complexity in a few spots. > > So who out there needs a value of K larger than > 2000? > What is the largest K that anybody is using? Who > would > object if I inserted a limit on K that was in the > range > of 1000 or 2000? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Yahoo! Mail - Com 250MB de espaço. Abra sua conta! http://mail.yahoo.com.br/
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
I've got no problem with that. Frankly I think if you have a sqlite table in real-life with that many columns you are probably doing something wrong :) On Thu, 17 Mar 2005 06:32:55 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > As currently implemented, there is no fixed limit to the number > of columns you can put in a table in SQLite. If the CREATE TABLE > statement will fit in memory, then SQLite will accept it. Call > the number of columns in a table K. I am proposing to limit the > value of K to something like 2000. > > Would this cause anyone any grief? > > Note that SQLite is optimized for a K that is small - a few dozen > at most. There are algorithms in the parser that run in time > O(K*K). These could be changed to O(K) but with K small the > constant of proportionality is such that it isn't worthwhile. > So, even though SQLite will work on a table with a million or > more columns, it is not a practical thing to do, in general. > > The largest value of K I have seen in the wild is in the > low 100s. I thought that I was testing with K values in > the thousands, but I just checked and I think the test > scripts only go as high as K=1000 in one place. > > The reason it would be good to limit K to about 2000 is > that if I do so there are some places where I can increase > the run-time performance some. It would also reduce > code complexity in a few spots. > > So who out there needs a value of K larger than 2000? > What is the largest K that anybody is using? Who would > object if I inserted a limit on K that was in the range > of 1000 or 2000? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > -- Cory Nelson http://www.int64.org
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
Interesting issue... I thing that we may use 2048 instead 2000. It´s an number more "binary". I think that it´s sufficient to 99.99% of the possible applications. That´s a good idea. The DBase accepted 128 collumns and that is my reference. I've need more than this. In a future development this value will be editable? Cláudio Leopoldino > As currently implemented, there is no fixed limit to > the number > of columns you can put in a table in SQLite. If the > CREATE TABLE > statement will fit in memory, then SQLite will > accept it. Call > the number of columns in a table K. I am proposing > to limit the > value of K to something like 2000. > > Would this cause anyone any grief? > > Note that SQLite is optimized for a K that is small > - a few dozen > at most. There are algorithms in the parser that > run in time > O(K*K). These could be changed to O(K) but with K > small the > constant of proportionality is such that it isn't > worthwhile. > So, even though SQLite will work on a table with a > million or > more columns, it is not a practical thing to do, in > general. > > The largest value of K I have seen in the wild is in > the > low 100s. I thought that I was testing with K > values in > the thousands, but I just checked and I think the > test > scripts only go as high as K=1000 in one place. > > The reason it would be good to limit K to about 2000 > is > that if I do so there are some places where I can > increase > the run-time performance some. It would also reduce > code complexity in a few spots. > > So who out there needs a value of K larger than > 2000? > What is the largest K that anybody is using? Who > would > object if I inserted a limit on K that was in the > range > of 1000 or 2000? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Yahoo! Mail - Com 250MB de espaço. Abra sua conta! http://mail.yahoo.com.br/
Re: [sqlite] Version 3.1.6
On Thu, 2005-03-17 at 12:39 +0100, Gerald Dachs wrote: > Why do you not use my patch? Do you not agree that this is a bug? > No I do not. The arch.png image is no longer used in the documentation. It was replaced by arch2.gif some time ago. > 'make doc' on linux is still broken, attached patch for 3.1.3 still > works. > The makefile works fine when I run it without your patch. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Version 3.1.6
Why do you not use my patch? Do you not agree that this is a bug? 'make doc' on linux is still broken, attached patch for 3.1.3 still works. Gerald --- sqlite-3.1.3/Makefile.in.orig 2005-03-07 22:56:04.833954328 +0100 +++ sqlite-3.1.3/Makefile.in2005-03-07 22:56:41.189427456 +0100 @@ -451,6 +451,9 @@ arch.html: $(TOP)/www/arch.tcl tclsh $(TOP)/www/arch.tcl >arch.html +arch.png: $(TOP)/www/arch.png + cp $(TOP)/www/arch.png . + arch2.gif: $(TOP)/www/arch2.gif cp $(TOP)/www/arch2.gif .
[sqlite] Proposal: limit the number of columns in a table to 2000.
As currently implemented, there is no fixed limit to the number of columns you can put in a table in SQLite. If the CREATE TABLE statement will fit in memory, then SQLite will accept it. Call the number of columns in a table K. I am proposing to limit the value of K to something like 2000. Would this cause anyone any grief? Note that SQLite is optimized for a K that is small - a few dozen at most. There are algorithms in the parser that run in time O(K*K). These could be changed to O(K) but with K small the constant of proportionality is such that it isn't worthwhile. So, even though SQLite will work on a table with a million or more columns, it is not a practical thing to do, in general. The largest value of K I have seen in the wild is in the low 100s. I thought that I was testing with K values in the thousands, but I just checked and I think the test scripts only go as high as K=1000 in one place. The reason it would be good to limit K to about 2000 is that if I do so there are some places where I can increase the run-time performance some. It would also reduce code complexity in a few spots. So who out there needs a value of K larger than 2000? What is the largest K that anybody is using? Who would object if I inserted a limit on K that was in the range of 1000 or 2000? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Image Hotspots - Urgent
Anirban Sarkar wrote: Hi all, I know that this is a sqlite forum and not the right place to post any TCL related questions. However, I have posted this question on many TCL forums including comp.lang.tcl but unfortunately did not get any respond. So just thought if anyone on this forum can help me out. It is very urgent. I have an area map which is obviously an image. I need to create some hotspots on the map at specific locations. The hotspots should have some backend data bound to them so that whenever a user clicks on a particular hotspot, he/she is redirected to a required location along with the data which was bound to the selected hotspot. Please note that I am scripting in TCL and the backend used is Sqlite. Any guidelines or help will be highly appreciated. Regards, Anirban Sarkar Create an array of hotspot (rectangle recording the position of the hotspot on the map and a key to your data). When the user click, find the corresponding hotspot by scanning the above rectangle. Use the key to retrieve the data. You may store the rectangle in a sqlite table and do a select on user click. Regards -- Noël Frankinet Gistek Software SA http://www.gistek.net
[sqlite] Image Hotspots - Urgent
Hi all, I know that this is a sqlite forum and not the right place to post any TCL related questions. However, I have posted this question on many TCL forums including comp.lang.tcl but unfortunately did not get any respond. So just thought if anyone on this forum can help me out. It is very urgent. I have an area map which is obviously an image. I need to create some hotspots on the map at specific locations. The hotspots should have some backend data bound to them so that whenever a user clicks on a particular hotspot, he/she is redirected to a required location along with the data which was bound to the selected hotspot. Please note that I am scripting in TCL and the backend used is Sqlite. Any guidelines or help will be highly appreciated. Regards, Anirban Sarkar
