Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
On Tue, Apr 29, 2008 at 10:10 PM, [EMAIL PROTECTED] wrote: P Kishor, http://www.sqlite.org/cvstrac/wiki?p=ManagementTools Oops, that's embarrassing! Do you have a favorite on this page that you would recommend? Thank you, Malcolm tkSQLite at http://reddog.s35.xrea.com/wiki/TkSQLite.html Tcl script as well as standalone executable binary versions for both Linux and Microsoft Windows. BSD license. Updated frequently, current version wraps SQLite 3.5.7. Robert Wishlaw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS search negative term syntax
Fair warning, though: It's not entirely clear that the fts search syntax should aim to hew too closely to consumer-oriented search syntax. Interesting point, too. Up to now, I always perceived the FTS search syntax to be very much consumer-oriented. It it just too similar to major search engines to be primarily machine-oriented. As it stands now, FTS syntax can of course be machine generated, if that is what you are aiming at. I believe that this should remain easy to do. And my suggested minus sign modification would not change this, would it? It's sort of in a strange place, most people would think it a poor idea (indeed, dangerous!) to put user-entered expressions in their WHERE clauses. I am not sure I understand the danger. Say I sqlite3_bind() the FTS match query, do you see this as a serious security risk (FTS injection) or a potential performance jeopardy, or something else? Caveat for the above: I've spent all of five minutes thinking about your posting, and I was interrupted in the middle. But I'll try to factor it in to future thinking. Thanks for your time and your thoughts! Ralf PS: I see little traffic on [EMAIL PROTECTED]. Is this intentional, or should this and simliar topics better be discussed there? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
Thanks Robert! tkSQLite at http://reddog.s35.xrea.com/wiki/TkSQLite.html Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] accented characters upper/lower case in SELECT LIKE statements
Hello I know it has already been discussed, but I could not find a detailed and final answer: in my database there will be accented characters (like the word ação, which means action and I have to make SELECT LIKE statements to which AÇÃO and ação would be met in the same query. I'm using brazilian-portuguese WinXP, (I guess it is unicode, but I'm not sure) and Visual Studio 2005 with the SQLite ADO.net library. Could anyone give me a hint on how to set up the database creation and further openings, and how should be the SELECT query to match both upper and lower case of accented words? Thanks a lot! Francisco -- If you have an apple and I have an apple and we exchange apples then you and I will still each have one apple. But if you have an idea and I have one idea and we exchange these ideas, then each of us will have two ideas. - George Bernard Shaw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
On Wed, 30 Apr 2008 13:23:51 -0400, you wrote: I've had good luck with Mike Cariotoglou's free Sqlite3Explorer http://www.singular.gr/sqlite/ I especially like being able execute just the sql command that I've highlighted. I agree. I played with a few other frontends, but always find myself back in Sqlite3Explorer. Some other interesting features: --Access-ish query designer (if you like that sort of thing) --Integrated with to a report generator module. --Import using regular expression (simple example at http://readlist.com/lists/sqlite.org/sqlite-users/1/9231.html ) I never use them, but the people I distribute my databases + Sqlite3Explorer to may need it. The SQLite Manager add-on for Firefox is nice too, but I didn't find a way to start FireFox with this add-on opening the database I'm interested in from a shell script, or associate my preferred sqlite3 filename extension (.db3) with it. The command (wrapped by mail): path\firefox.exe -chrome chrome://sqlitemanager/content/sqlitemanager.xul mydb.db3 just doesn't do the trick. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
Thanks Kees! I played with a few other frontends, but always find myself back in Sqlite3Explorer. ... find a way to start FireFox with this add-on opening the database I'm interested in from a shell script, or associate my preferred sqlite3 filename extension (.db3) with it. I've been looking for the same. I'll post back to this list if I find a solution. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accented characters upper/lower case in SELECT LIKE statements
On Thu, May 01, 2008 at 10:45:17AM -0300, Francisco Ares scratched on the wall: Hello I know it has already been discussed, but I could not find a detailed and final answer: This is well documented: http://www.sqlite.org/lang_expr.html Read the section on the 'LIKE' operator. It is pretty clear about how LIKE works and that the case-insensitive behavior only applies to 7-bit Latin characters. This is considered a bug, but is likely to be unfixed for a long time. Could anyone give me a hint on how to set up the database creation and further openings, and how should be the SELECT query to match both upper and lower case of accented words? As the document referenced above explains, you can create a user function called like(A,B) that will over-ride the built-in behavior for LIKE. If you're only dealing with one language, such as Brazilian-Portuguese, then you can just custom code the various accented characters used in that specific language. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accented characters upper/lower case in SELECT LIKE statements
Thanks Jay. francisco On Thu, May 1, 2008 at 11:09 AM, Jay A. Kreibich [EMAIL PROTECTED] wrote: On Thu, May 01, 2008 at 10:45:17AM -0300, Francisco Ares scratched on the wall: Hello I know it has already been discussed, but I could not find a detailed and final answer: This is well documented: http://www.sqlite.org/lang_expr.html Read the section on the 'LIKE' operator. It is pretty clear about how LIKE works and that the case-insensitive behavior only applies to 7-bit Latin characters. This is considered a bug, but is likely to be unfixed for a long time. Could anyone give me a hint on how to set up the database creation and further openings, and how should be the SELECT query to match both upper and lower case of accented words? As the document referenced above explains, you can create a user function called like(A,B) that will over-ride the built-in behavior for LIKE. If you're only dealing with one language, such as Brazilian-Portuguese, then you can just custom code the various accented characters used in that specific language. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ 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] In memory data base questions ???
THANKS Igor! On Tue, Apr 29, 2008 at 6:38 PM, Igor Tandetnik [EMAIL PROTECTED] wrote: Alex Katebi [EMAIL PROTECTED] wrote: Just want to make a note that when I say in-memory data base I don't mean the cache memory I mean the :memory: date base. That's how I understood you, yes. Igor Tandetnik ___ 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] Recommended (Windows/Linux) SQLite utilities
Malcolm: I use Firefox and I installed the SQLite add-in. I really like it! Jeff [EMAIL PROTECTED] wrote: Thanks Kees! I played with a few other frontends, but always find myself back in Sqlite3Explorer. ... find a way to start FireFox with this add-on opening the database I'm interested in from a shell script, or associate my preferred sqlite3 filename extension (.db3) with it. I've been looking for the same. I'll post back to this list if I find a solution. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accented characters upper/lower case in SELECT LIKE statements
On May 1, 2008, at 4:09 PM, Jay A. Kreibich wrote: As the document referenced above explains, you can create a user function called like(A,B) that will over-ride the built-in behavior for LIKE. If you're only dealing with one language, such as Brazilian-Portuguese, then you can just custom code the various accented characters used in that specific language. Alternatively, one could normalize (i.e. transliterate) both data and query before hand. E.g. using Sean M. Burke's Unidecode [1] or such [2]: ação - transliterate - acao AÇÃO - transliterate - acao Москва́ - transliterate - moskva Ἀθηνᾶ - transliterate - athena 서울 - transliterate - seoul 北京 - transliterate -beijing For example, searching for 'nino' or any variation thereof, would match 'Niño': http://svr225.stepx.com:3388/search?q=nino http://svr225.stepx.com:3388/el-nino-southern-oscillation Cheers, -- PA. http://alt.textdrive.com/nanoki/ [1] http://interglacial.com/~sburke/tpj/as_html/tpj22.html [2] http://www.gnu.org/software/libiconv/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] numRows undefined function?
On Wed, Apr 30, 2008 at 4:50 PM, Skip Evans [EMAIL PROTECTED] wrote:
But why did so much documentation I found on the
web use numRows()?
What is the difference between the PDOStatement
set of functions and the set to which numRows()
belongs to?
Different versions of SQLite?
Thanks again,
Skip
Which functions are you talking about? These ones?
http://us3.php.net/manual/en/ref.sqlite.php
They are just different ways of accessing the SQLite Database... PDO
(an abraction layer) vs built in SQLite functions (which are probably
more similar to the actual SQLite c libray...).
// Built in functions
$db = new SQLiteDatabase('test.db');
$db-query( 'DROP TABLE not_pdo' );
$db-query( 'CREATE TABLE not_pdo (id, string)' );
$db-query( 'INSERT INTO not_pdo VALUES (1,1)' );
$db-query( 'INSERT INTO not_pdo VALUES (2,2)' );
$db-query( 'INSERT INTO not_pdo VALUES (3,3)' );
$result = $db-query( 'SELECT * FROM not_pdo' );
echo $result-numRows() . \n\n;
versus...
// PDO functions
$db = new PDO('sqlite3:test.db');
$db-exec( 'DROP TABLE pdo' );
$db-exec( 'CREATE TABLE pdo (id, string)' );
$db-exec( 'INSERT INTO pdo VALUES (1,1)' );
$db-exec( 'INSERT INTO pdo VALUES (1,2)' );
$result = $db-query( 'SELECT * FROM pdo' );
echo $result-rowCount() . \n\n;
--
~Ty
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
I use Firefox and I installed the SQLite add-in. I really like it! Thanks Jeff! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended pragmas for new SQLite users to focus on
Hi Donald, I think you're right for general purpose applications. But if you have ETL or data conversion/analysis applications that can be re-run after a failure (using source content), this type of pragma sounds like a reasonable choice. I very much agree with your statement -- I turn it off sometimes myself to good effect. But the webpage will be read by all sorts of users, and there was not a mention of the conditions under which this would be wise or foolhardy. A newbie might well turn off synchronous inappropriately, take an application to production, then lose all her data. It just seems (to me at least) quite unfriendly not to include a warning when suggesting synchronous=OFF. My original question was very general - we are looking at using SQLite in many different scenarios - some like the traditional 'always on' application and some which are more backoffice type batch operations. I agree with concerns about not providing usage context to the discussion of pragmas. I'll make sure I provide this context in future pragma discussions. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS search negative term syntax
On Thu, May 1, 2008 at 1:41 AM, Ralf Junker [EMAIL PROTECTED] wrote: Fair warning, though: It's not entirely clear that the fts search syntax should aim to hew too closely to consumer-oriented search syntax. Interesting point, too. Up to now, I always perceived the FTS search syntax to be very much consumer-oriented. It it just too similar to major search engines to be primarily machine-oriented. As it stands now, FTS syntax can of course be machine generated, if that is what you are aiming at. I believe that this should remain easy to do. And my suggested minus sign modification would not change this, would it? Right now, things are pretty hybrid. Long-term, one of the design inputs I got from interested parties when we were starting the project is that we probably want to have both a user-oriented syntax and a machine-oriented syntax. Then you could easily just slap something together exposing the fts search syntax, and if you wanted something more precise (perhaps to emulate some other system's query language) you could write your own parser and have a well-defined way to generate queries for fts without having to worry about unexpected syntax changes. You're right that the minus-sign mod probably won't matter one way or the other. I was more addressing the notion that Google does it that way, fts should too. Matching Google search certainly does have advantages, since people already know how to work it, and Google seems to have done a reasonable job of not injecting all sorts of crazy syntax that nobody can figure out without a quick-reference card. Hmm. I just thought of an interesting notion. You could have a version of fts which takes a very precise query language, then have another virtual table module which wraps that and converts from a looser language to the more precise language. So a certain extent this is make-work, because you'd have to parse, serialize, then re-parse, rather than just generating the query tree directly, but search queries are generally pretty small so it might not matter much (compared to actually executing the query). It's sort of in a strange place, most people would think it a poor idea (indeed, dangerous!) to put user-entered expressions in their WHERE clauses. I am not sure I understand the danger. Say I sqlite3_bind() the FTS match query, do you see this as a serious security risk (FTS injection) or a potential performance jeopardy, or something else? There definitely should be no security risks, though there is some potential for performance issues. For instance, a user could ask for a prefix search but your code might run in your UI thread because you only ever tested with exact word matches which were fast enough. [All of this isn't merely me trying to avoid work. I also polish off such arguments when dealing with some of the biggest users of fts, many of which sit close enough to me to put these questions directly.] -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended SQLite utilities
Hi Malcolm, Looking for recommendations for 3rd party SQLite utilities for browsing, maintaining, importing/exporting and repairing SQLite database files. I'm open to Mac only products as well. I started a tabulated comparison of various SQLite GUI applications for Mac here: http://www.tandb.com.au/sqlite/compare/ A few of the applications there are cross platform. As you'll see in the comparison table, I am particularly interested in how specific features compare between the applications. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] splite database under version control (subversion)?
In the BIG db I have worked on there is a table that log every insert/update on specific and important tables and a log of every sql statement execute but I haven't ever see a db under version control with svn (or csv or git or any other). -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). On Wed, Apr 30, 2008 at 10:59 PM, [EMAIL PROTECTED] wrote: Dear Sqlite users, I want to put a sqlite database under version control (in my case subversion). As far as I know the sqlite database is a binary file. Always exporting and importing the database to a sql file is quite laborious . What is the standard approach for this ? Is there any filter available for subversion which does the conversion automatically? Thanks Emal -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] ___ 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
