[sqlite] Adobe AIR and Sqlite - Adobe could have statically included the FTS module?

[Scott Chapman]

Adobe AIR did not include the FTS module.
I read in this thread 
(https://store1.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=75=697=1417051=y)
 
that they disabled loadable modules.

However, it would have been easy for them to statically include the FTS 
(and maybe other modules) in the build, right?

I will be suggesting that to them in a bug ticket if that's the case but 
wanted to confirm first.

Thanks,
Scott

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

[sqlite] Pending Queue?

[Scott . Chapman]

In Section 7.0, Transaction Control At The SQL Level, at 
http://www.sqlite.org/lockingv3.html, it says: 

"If the SQL COMMIT command turns autocommit on and the autocommit logic then 
tries to commit change but fails because some other process is holding a SHARED 
lock, then autocommit is turned back off automatically. This allows the user to 
retry the COMMIT at a later time after the SHARED lock has had an opportunity 
to clear."

Also in section 3.0 it says:

"Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind 
are allowed to coexist with an EXCLUSIVE lock."
So, if process A has an EXCLUSIVE lock for writing and process B attempts a 
COMMIT, process B immediately receives an SQLITE_BUSY error, and has to start 
over again.

There is no queue then.  Does this create a problem where process B never gets 
to write because process A (C, D, etc.) are continually getting in ahead of B?
B Can't get a PENDING lock to keep it's position in line.

It seems that there should be a queue of Pending locks so processes can keep 
their place in line?
So this could be implemented as a COMMIT and there would be a specified timeout 
as a PRAGMA command?  If the timeout was set to 0 (default), then there would 
be no queue and it would be backwards compatible.  Of course, the Pending queue 
would work against all other lock conditions currently in effect on the 
database, not just EXCLUSIVE's.

Good idea, bad idea, false alarm?

Scott



The information contained in this message and any attachment may be
proprietary, confidential, and privileged or subject to the work
product doctrine and thus protected from disclosure.  If the reader
of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended
recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please notify me
immediately by replying to this message and deleting it and all
copies and backups thereof.  Thank you.

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

[sqlite] Can more than one user connect to an in-memory database?

[Scott Chapman]

I don't see how.  Any clues?

Thanks!
Scott

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

Re: [sqlite] How to select Strict Affinity or No Affinity modes?

[Scott Chapman]

I take it that there's no way to work around this currently?

Scott

Scott Chapman wrote:
> D. Richard Hipp wrote:
>   
>> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:
>>
>>   
>> 
>>> I've looked high and low and can't find a way to invoke the other 2
>>> affinity modes.  Are they available? I'm on 3.5.4.
>>> 
>>>   
>> The concept of "strict" affinity mode was briefly discussed years
>> ago, but we never implemented it, having never seen any benefit
>> for such a thing.  Can you explain why you think strict affinity mode
>> might be beneficial to you?  If somebody can provide a good
>> enough rational to justify strict affinity mode, we might just put it
>> in.
>>   
>> 
> I'm working on a Python adapter that goes on top of APSW.  It will 
> enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and 
> automatically convert these to and from Python's respective data types.
>
> The case I'm dealing with that is not working like I want is the case of 
> NUMERIC column type.  In SQLite, this column type gets an affinity of 
> REAL. If I put in a value to the column as a string literal, say 
> '123.23', it's stored as a REAL even though I specified it as a string 
> in quotes.  I want it to store it as a string.  The only way I've found 
> to fix this is to use a column type of NUMERIC_TEXT.  The presense of 
> "TEXT" in the column type changes the affinity to string.  This is not 
> very elegant and I was looking for any other way to make this work 
> correctly.  "No Affinity" would probably work, if I understand it 
> correctly.
>
> I want to avoid the use of REAL types in this case because they can lead 
> to rounding errors, which is the whole purpose of the NUMERIC type to 
> begin with, in my understanding.  I also would like to be able to make 
> the column type just NUMERIC as that is compilant with the SQL standard.
>
> Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes 
> page.  If there are no plans to implement these, please consider 
> removing them from the docs.
>
> Thanks!
> Scott
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   

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

Re: [sqlite] How to select Strict Affinity or No Affinity modes?

[Scott Chapman]

D. Richard Hipp wrote:
> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote:
>
>   
>> I've looked high and low and can't find a way to invoke the other 2
>> affinity modes.  Are they available? I'm on 3.5.4.
>> 
> The concept of "strict" affinity mode was briefly discussed years
> ago, but we never implemented it, having never seen any benefit
> for such a thing.  Can you explain why you think strict affinity mode
> might be beneficial to you?  If somebody can provide a good
> enough rational to justify strict affinity mode, we might just put it
> in.
>   
I'm working on a Python adapter that goes on top of APSW.  It will 
enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and 
automatically convert these to and from Python's respective data types.

The case I'm dealing with that is not working like I want is the case of 
NUMERIC column type.  In SQLite, this column type gets an affinity of 
REAL. If I put in a value to the column as a string literal, say 
'123.23', it's stored as a REAL even though I specified it as a string 
in quotes.  I want it to store it as a string.  The only way I've found 
to fix this is to use a column type of NUMERIC_TEXT.  The presense of 
"TEXT" in the column type changes the affinity to string.  This is not 
very elegant and I was looking for any other way to make this work 
correctly.  "No Affinity" would probably work, if I understand it 
correctly.

I want to avoid the use of REAL types in this case because they can lead 
to rounding errors, which is the whole purpose of the NUMERIC type to 
begin with, in my understanding.  I also would like to be able to make 
the column type just NUMERIC as that is compilant with the SQL standard.

Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes 
page.  If there are no plans to implement these, please consider 
removing them from the docs.

Thanks!
Scott

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

[sqlite] How to select Strict Affinity or No Affinity modes?

[Scott Chapman]

I've looked high and low and can't find a way to invoke the other 2 
affinity modes.  Are they available? I'm on 3.5.4.

Any pointers would be greatly appreciated.

Thanks!
Scott

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

Re: [sqlite] sqlite with visual basic?

[Scott Chapman]

Thanks Steve.

Anyone have guidance on which of these will work (well) with the latest 
version of VB and SQLite?


Steve O'Hara wrote:

Check out the WIKI, there's a myriad of possibilities.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg]On Behalf Of Scott Chapman
Sent: 11 August 2005 18:43
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite with visual basic?


I am having a look at Visual Basic Express 2005 Beta.  I haven't 
programmed VB since version 3.0 on Windows 3.1 many years ago.  I didn't 
do any database with it then.


All of my programming these days is in Python with Postgresql or SQLite.

Has anyone gotten VB and SQLite working together?

Are there some examples of this available?

Scott

[sqlite] sqlite with visual basic?

[Scott Chapman]

I am having a look at Visual Basic Express 2005 Beta.  I haven't 
programmed VB since version 3.0 on Windows 3.1 many years ago.  I didn't 
do any database with it then.


All of my programming these days is in Python with Postgresql or SQLite.

Has anyone gotten VB and SQLite working together?

Are there some examples of this available?

Scott

Re: [sqlite] thoughts on a web-based front end to sqlite3 db?

[Scott Chapman]

Eli,

I'd highly recommend Python.  I've used Perl, PHP and Python.  Python is 
hands-down the winner.  After getting ahold of the elegance of Python, PHP 
feels like a hack job.  Perl is "executable line noise".  Python is very 
mature and very nice.  It has a far cleaner implementation of just about 
everything, especially OO than Perl and _way_ better than PHP. I would also 
avoid Microsoft specific technology like the plague.

APSW is not DBAPI compliant but it's close enough that migrating to a bigger 
database (PostgreSQL, _not_ MySQL!) would not be difficult at all.  APSW is 
very nice.  I've used it and PySQLite and I'd recommend APSW.  It's 
"thinner".

You should look at www.cherrypy.org for a nice little web server written in 
Python to get you going. It's really slick.  Check out Snakelets also 
(http://snakelets.sourceforge.net).  I prefer CherryPy because Snakelets has 
a templating language that is too-closely bound to the web server.  CherryPy 
2.0 (beta currently but works well) unbound the templating language for the 
2.0 release so you can use your own.  I built my own templating language 
because I couldn't find one that I like out there.  With Python you do have a 
LOT to choose from so it can be daunting.

If you have experience with PHP then you've learned how to embed source code 
in HTML.  This is not a very good idea.  Separation of the logic and the 
presentation make for a cleaner design in my opinion.  Some would debate this 
but it's Ok if they're wrong! :)

I'll be happy to chat with you.  You can find me on Jabber at 
[EMAIL PROTECTED] 

Cordially,
Scott

On Monday 07 March 2005 01:22 pm, Eli Burke wrote:
> I've been working on a project using sqlite3 since last fall. At the time,
> I knew that it would need a web-based front-end eventually. I have a very
> small bit of experience with PHP, and I assumed that PHP would support
> sqlite3 sooner or later. Well, it's later, and as far as I know, PHP
> is still using the 2.x branch.
>
> So, I was wondering if any of the more opinionated among you would care
> to suggest an interface language. It'll be on a Linux box, presumably
> running apache although I'm open to alternatives. The app itself uses
> sqlite3 for scheduling jobs and storing job data, so the web interface
> only needs to be able to insert some data and do visualization
> (pretty standard stuff I think).
>
> Ease of learning is a plus as I need to get something basic up and
> running fairly fast. I've heard good things about Python in that respect.
> Does anyone have alternative suggestions, or if you agree that Python Is
> Good, would you suggest using APSW, pysqlite, or something else?
>
> Thanks,
> Eli

Re: [sqlite] 3.1.3 on Windows with python apsw: malformed database schema - near "AUTOINCREMENT": syntax error

[Scott Chapman]

That was a premature guess because it works to do this in sqlite3 at the 
command prompt on Windows.  
That may mean that the DLL is not right?

Here's what I ran and the output from it on both Windows XP Pro and Linux (Suse 
9.1):

import apsw
import os
try:
  os.remove ('test1.db3')
except:
  pass
db = apsw.Connection('test1.db3')
cursor=db.cursor()
cursor.execute ('create table testone (bigid INTEGER PRIMARY KEY 
AUTOINCREMENT,name TEXT)')
cursor.execute('PRAGMA empty_result_callbacks = 1')
sql="select * from testone"
cursor.execute (sql)
description = cursor.getdescription()


OUTPUT ON WINDOWS XP:
>python test_pragma.py
Traceback (most recent call last):
  File "test_pragma.py", line 9, in ?
cursor.execute ('create table testone (bigid INTEGER PRIMARY KEY 
AUTOINCREMENT,name TEXT)')
apsw.SQLError: SQLError: near "AUTOINCREMENT": syntax error

C:\Documents and Settings\Scott Chapman\My Documents\development\misc\sqlite>
C:\Documents and Settings\Scott Chapman\My Documents\development\misc\sqlite>
C:\Documents and Settings\Scott Chapman\My Documents\development\misc\sqlite>
C:\Documents and Settings\Scott Chapman\My Documents\development\misc\sqlite>
C:\Documents and Settings\Scott Chapman\My 
Documents\development\misc\sqlite>del test1.db3

C:\Documents and Settings\Scott Chapman\My 
Documents\development\misc\sqlite>sqlite3 test1.db3
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> create table testone (bigid INTEGER PRIMARY KEY AUTOINCREMENT,name 
TEXT);
sqlite> .quit

>

OUTPUT on LINUX
> python test_pragma.py
Traceback (most recent call last):
  File "test_pragma.py", line 13, in ?
description = cursor.getdescription()
apsw.ExecutionCompleteError: Can't get description for statements that have 
completed execution
>


On Saturday 05 March 2005 09:10 pm, Scott Chapman wrote:
> I'm not able to duplicate this on Linux with fresh compiles of everything.
> That leads me to believe the binary I downloaded for Windows is not built
> on 3.1.3 after all.
>
> Scott
>
> On Saturday 05 March 2005 06:01 pm, Scott Chapman wrote:
> > Windows XP Pro, Python 2.3.4, sqlite 3.1.3
> > I'm getting this wierd error from sqlite.  It only happens when I access
> > this database with Python/APSW.  I can access it with sqlite and
> > sqlite3explorer just fine.
> >
> >  >sqlite3 test1.db3
> >
> > SQLite version 3.1.3
> > Enter ".help" for instructions
> > sqlite> create table test (id INTEGER PRIMARY KEY AUTOINCREMENT,name
> > text); sqlite> .quit
> >
> > C:\Documents and Settings\Scott Chapman\My
> > Documents\development\misc\sqlite>python apsw_pragma.py test1.db3
> > Traceback (most recent call last):
> >   File "apsw_pragma.py", line 37, in ?
> > dosql(sql)
> >   File "apsw_pragma.py", line 19, in dosql
> > cursor.execute(query)
> > apsw.AbortError: AbortError: malformed database schema - near
> > "AUTOINCREMENT": syntax error
> >
> > Cordially,
> > Scott

Re: [sqlite] 3.1.3 on Windows with python apsw: malformed database schema - near "AUTOINCREMENT": syntax error

[Scott Chapman]

I'm not able to duplicate this on Linux with fresh compiles of everything.  
That leads me to believe the binary I downloaded for Windows is not built on 
3.1.3 after all.

Scott

On Saturday 05 March 2005 06:01 pm, Scott Chapman wrote:
> Windows XP Pro, Python 2.3.4, sqlite 3.1.3
> I'm getting this wierd error from sqlite.  It only happens when I access
> this database with Python/APSW.  I can access it with sqlite and
> sqlite3explorer just fine.
>
>  >sqlite3 test1.db3
>
> SQLite version 3.1.3
> Enter ".help" for instructions
> sqlite> create table test (id INTEGER PRIMARY KEY AUTOINCREMENT,name text);
> sqlite> .quit
>
> C:\Documents and Settings\Scott Chapman\My
> Documents\development\misc\sqlite>python apsw_pragma.py test1.db3
> Traceback (most recent call last):
>   File "apsw_pragma.py", line 37, in ?
> dosql(sql)
>   File "apsw_pragma.py", line 19, in dosql
> cursor.execute(query)
> apsw.AbortError: AbortError: malformed database schema - near
> "AUTOINCREMENT": syntax error
>
> Cordially,
> Scott

Re: [sqlite] Does "PRAGMA full_column_names = 1" work with APSW and 3.1.3?

[Scott Chapman]

I'm able to duplicate this on Linux with fresh compiles of Python, apsw, 
sqlite.

Scott

On Saturday 05 March 2005 06:11 pm, Scott Chapman wrote:
> I'm using APSW 3.0.8-r3 on Python Windows XP Pro with Python 2.3.4.
>
> Minimal test-case code:
>
> import apsw
> db = apsw.Connection('test.db3')
> cursor=db.cursor()
> cursor.execute('PRAGMA empty_result_callbacks = 1')
> sql="select * from testnn" # testnn is empty
> cursor.execute (sql)
> description = cursor.getdescription()
>
> Results:
>
> Traceback (most recent call last):
>   File "test_pragma.py", line 7, in ?
> description = cursor.getdescription()
> apsw.ExecutionCompleteError: Can't get description for statements that
> have completed execution
>
> Am I doing something wrong here?  I'm expecting a description even
> though the table is empty because the full_column_names pragma is on.
>
> TIA,
> Scott

[sqlite] Re: make error with 3.1.3 on Suse 9.1

[Scott Chapman]

This was caused by me having two different readline.h files on my server.  
Please disregard!

Thanks!
Scott
On Saturday 05 March 2005 05:55 pm, Scott Chapman wrote:
> When I run make, I get this:
>
> ./libtool --mode=link gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src
> -DNDEBUG -I/usr/include -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1
> -I/usr/include/readline  \
>   -o sqlite3 ./src/shell.c libsqlite3.la -lreadline -lreadline
> gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -I/usr/include
> -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -I/usr/include/readline
> -o .libs/sqlite3 ./src/shell.c  ./.libs/libsqlite3.so -lreadline
> In file included from src/shell.c:42:
> /usr/include/readline/history.h:44: error: redefinition of `struct
> _hist_entry'
> /usr/include/readline/history.h:77: error: conflicting types for
> `add_history' /usr/local/include/readline/readline.h:94: error: previous
> declaration of `add_history'
> make: *** [sqlite3] Error 1

[sqlite] Does "PRAGMA full_column_names = 1" work with APSW and 3.1.3?

[Scott Chapman]

I'm using APSW 3.0.8-r3 on Python Windows XP Pro with Python 2.3.4.
Minimal test-case code:
import apsw
db = apsw.Connection('test.db3')
cursor=db.cursor()
cursor.execute('PRAGMA empty_result_callbacks = 1')
sql="select * from testnn" # testnn is empty
cursor.execute (sql)
description = cursor.getdescription()
Results:
Traceback (most recent call last):
 File "test_pragma.py", line 7, in ?
   description = cursor.getdescription()
apsw.ExecutionCompleteError: Can't get description for statements that 
have completed execution

Am I doing something wrong here?  I'm expecting a description even 
though the table is empty because the full_column_names pragma is on.

TIA,
Scott

[sqlite] 3.1.3 on Windows with python apsw: malformed database schema - near "AUTOINCREMENT": syntax error

[Scott Chapman]

Windows XP Pro, Python 2.3.4, sqlite 3.1.3
I'm getting this wierd error from sqlite.  It only happens when I access 
this database with Python/APSW.  I can access it with sqlite and 
sqlite3explorer just fine.

>sqlite3 test1.db3
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> create table test (id INTEGER PRIMARY KEY AUTOINCREMENT,name text);
sqlite> .quit
C:\Documents and Settings\Scott Chapman\My 
Documents\development\misc\sqlite>python apsw_pragma.py test1.db3
Traceback (most recent call last):
 File "apsw_pragma.py", line 37, in ?
   dosql(sql)
 File "apsw_pragma.py", line 19, in dosql
   cursor.execute(query)
apsw.AbortError: AbortError: malformed database schema - near 
"AUTOINCREMENT": syntax error

Cordially,
Scott

[sqlite] make error with 3.1.3 on Suse 9.1

[Scott Chapman]

When I run make, I get this:

./libtool --mode=link gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src 
-DNDEBUG -I/usr/include -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 
-I/usr/include/readline  \
-o sqlite3 ./src/shell.c libsqlite3.la -lreadline -lreadline 
gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -I/usr/include 
-DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -I/usr/include/readline 
-o .libs/sqlite3 ./src/shell.c  ./.libs/libsqlite3.so -lreadline
In file included from src/shell.c:42:
/usr/include/readline/history.h:44: error: redefinition of `struct 
_hist_entry'
/usr/include/readline/history.h:77: error: conflicting types for `add_history'
/usr/local/include/readline/readline.h:94: error: previous declaration of 
`add_history'
make: *** [sqlite3] Error 1

Re: [sqlite] SQLite Advocacy

[Scott Chapman]

> Let's be careful out there.  I have found rookies tend to blindly
> evangelically tout their first learned tool as the one and only path of
> light to truth and world peace.

Fred, I think you just hit a good part of the reason that PHP and MySQL have 
mind-share out there when they are lousy tools.  I learned PHP, then Perl, 
then Python and I'm looking at PHP again because my current position uses it.  
(Did I mention that Perl and PHP are very simliar!?)

> IMHO, the best approach to any new task is to first study in depth the
> issues, environment, restrictions, and complexities involved, then use
> whatever the cheap [EMAIL PROTECTED]@$#%ds in accounting will let you buy.  
> The
> cheaper the better.  And if the CEO's nephew happens to like it, all the
> better!  ;-)

[Oh! If it was only a matter of getting it past the number crunchers in 
accounting!  ]

I'd add, "study the quality of the tools you're evaluating" but this is 
completely impossible for newbies.  So, they just go with what is popular and 
the "popular <> good quality" problem is perpetuated. One tool may not fit 
all applications but it's nice when a couple can do 90% of everything so you 
can leverage your investment.  SQLLite/Postgres are those two for databases 
and Python for scripting, in my experience.

I think I should add for future readers that Python is not as approachable for 
newbies when it comes to web site development.  PHP is approachable because 
it is doesn't require any choices of which framework/style to use.  You just 
"embed your PHP in your HTML" and that's that.  Newbies looking at Python 
probably have an experience about like I did.  You'll realize one of two 
things first.  Either, that there are multiple frameworks to choose from and 
you've no idea which one is "it", or that Zope is the "Killer App" for Python 
and you're "supposed" to use it.  Then you have a good look at Zope and "run 
away screaming"; it's not very approachable.  There's a project, Subway, at 
http://developer.berlios.de/projects/subway that hopes to make a nice, newbie 
approachable full web stack that may ease this problem.  We'll see how it 
matures.  IMHO, sticking with Python is worth getting over the initial 
confusion.  Python offers a lot of choices, which is great, but you have to 
do more digging initially.

Happy Trails!

Scott

Re: [sqlite] SQLite Advocacy

[Scott Chapman]

On Wednesday 02 February 2005 7:52 am, John Dean wrote:
> At 14:57 02/02/2005, Paul Malcher wrote:
> >Scott Chapman wrote:
> >>Regarding the issue of SQL Server vs. SQLite:
> >>If the choice were between SQL Server and SQLite, and the need came up
> >>that SQLite could not meet, I'd pitch for PostgreSQL (_not_
> >>MySQL).  PostgreSQL version 8.0 has been released and runs natively on
> >> Windows.
> >
> >When it comes to PostgreSQL vs MySQL there is only one answer. OWNED!
> >PostgreSQL has been around for far longer that MySQL its more mature, it
> >can handle the heavy loads without slowing down. Next to PostgreSQL,
> >SQLite is the best choice, both aboslutely kick butt.
>
> Even though I used to work for MySQL I have always regarded PostgreSQL as
> being to better. MySQL is fine for driving data driven web site and that
> it. If you want more then PostgreSQL is the natural choice. Now that I have
> found SQLite my priorities have changed; I no longer use MySQL for anything

I have found that MySQL should not be used for data driven web sites as well.  
It has non-standard SQL (especially enum and set).  If you are designing in 
MySQL, don't use them.  I have to migrate a database that I inherited from 
MySQL to Postgres and I'm fighting with the "set" data type.  PG doesn't 
support it and the tools that are available to do the conversion are 
inadequate.  There is no advantage to using MySQL over Postgres.  It's not 
any great deal easier to implement or administer.

I talked with the author of the GIS extension to MySQL and found that it is a 
poorly written database overall.  PostGIS is much better implemented and much 
more maintainable by the developers.  The docs for Postgres are better as 
well.

Between the incompatible/incomplete SQL, the quality of the code under the 
hood, the maturity of the product (version 5 _finally_ has views!?), etc., I 
will never use it and do not recommend it for others.  Use SQLite for your 
"Lite" lifting and Postgres for everything else.


I feel the same way about PHP.  Use Python instead.  PHP is not a good quality 
language.  It amazes me that MySQL and PHP have mind-share out there when 
they are no where near as good as the other free tools available.  It proves 
over and over again that technical excellence does not gain market share. 
Marketing gains market share.


Scott

Re: [sqlite] SQLite Advocacy

[Scott Chapman]

On Monday 31 January 2005 7:58 am, Downey, Shawn wrote:

> "If anyone can see the source code, then won't we be venerable to
> hackers?"

Here is a very useful paragraph that should be given to anyone who thinks in 
the above terms:

"A common question in the minds of some CEOs and CIOs is, 'If it is open, 
how can it be secure?' " The very question displays a fundamental 
misunderstanding of security. The correct notion was known and set down 
by Auguste Kerckhoffs in the 19th century. David Kahn's classic book, 
_The Codebreakers_, puts it succinctly for codes and ciphers: "security 
must reside solely in the keys." You must assume that the enemy knows the 
algorithm--"security by obscurity" is a delusion and a sham. Indeed, 
proprietary software is a far more likely haven for back doors and 
Trojans, because it is kept secret. 

Regarding the issue of SQL Server vs. SQLite:
If the choice were between SQL Server and SQLite, and the need came up that 
SQLite could not meet, I'd pitch for PostgreSQL (_not_ MySQL).  PostgreSQL 
version 8.0 has been released and runs natively on Windows.

Cordially,
Scott

[sqlite] Can you get a description of a table when the sql results are empty?

[Scott Chapman]

I made a little python code below that fetches a sqlite3 table description 
(using apsw) but it won't work when the table is empty. ÂI made it tell me 
the table is empty in this case. Â

Is there a way to get the columns in a table without having to parse the SQL 
that created the table, when the table is empty?

import re
import sys
import apsw
db = apsw.Connection(sys.argv[1])
cursor = db.cursor()
print
sql="select tbl_name from sqlite_master where type='table'"
cursor.execute(sql)
tables=[]
while True:
  try:
    db_row=cursor.next()
  except StopIteration:
    break
  tables.append(db_row)
print "Tables:"
for table_name in tables:
 print table_name[0]
 print
 table_name=table_name[0]
 print ""
 print "table name: %s" % table_name
 print    Â
 # Get the sql that created table and parse it to determine which is the 
primary key.
 # I don't know of any other way to determine the primary key in sqlite3.
 sql="select sql from sqlite_master where tbl_name='%s';" % table_name
 cursor.execute(sql)
 create_table_sql=cursor.next()[0]
 print "Creation SQL: %s" % create_table_sql
 print
 match=re.search(r'.*[ (](.*?) integer primary 
key',create_table_sql,re.IGNORECASE)
 if match:
   pkey_column = match.group(1)
   print "Primary key: %s" % pkey_column
   print
 
 # Get a row from the table so I can determine the description
 sql="select oid,* from %s limit 1" % table_name
 cursor.execute (sql)
     
 # Get the column names and make the HTML  header row
 try:
   cdes=cursor.getdescription()
 except apsw.ExecutionCompleteError:
   # HERE's where I'd like to be able to go ahead and get the description!
   print 'table: %s is empty!' % table_name Â
 else:
   print "Columns in table %s:" % table_name
   for header,dummy in cdes[1:]:
     print ' Â%s: %s' % (header,dummy)

Scott

[sqlite] sqlite3 accepts create table blah (id integer primarykey) without errors.

[Scott Chapman]

It should be "primary key" not "primarykey".  It doesn't give you any errors 
and it doesn't give you a primary key.

Scott

[sqlite] How do I create a log of SQLite SQL statements, etc?

[Scott Chapman]

I'd like to be able to see all the sql traffic going to/from my sqlite 
database.  Is there a way to run things in a debug mode so that it 
outputs the sql traffic to a log file, similar to how you'd do it in 
Postgres?

Scott


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]